Tag Archives: T-SQL

Rozdíl mezi COALESCE() a ISNULL()

1. COALESCE() je z ANSI/ISO standardu SQL32, kdežto ISNULL() je jen T-SQL rozšíření (Microsoft SQL Serveru).

2. ISNULL() má vlastní interpretaci, kdežto COALESCE() je pouze zkrácený zápis pro CASE strukturu a i se tak provádí:

CASE
    WHEN (expression1 IS NOT NULL) THEN expression1
    ...
    WHEN (expressionN IS NOT NULL) THEN expressionN
    ELSE NULL
END

3. Typ výsledku ISNULL() je vždy dle prvního parametru, typ výsledku COALESCE() odpovídá CASE struktuře, tj. pokouší se o maximální záběr ze všech parametrů (SQL Server Books Online: „Returns the highest precedence type from the set of types in result_expressions and the optional else_result_expression.„).

DECLARE @Test char(2)
SET @Test = NULL
SELECT ISNULL(@Test, 'abcde'), COALESCE(@Test, 'abcde')

Např. výše uvedený test vrací ‚ab‘, ‚abcde‘.

4. Vzhledem k převodu COALESCE() na CASE oproti vlastnímu provádění, je ISNULL() obvykle rychlejší.

5. ISNULL() pochopitelně bere pouze dva parametry, kdežto COALESCE() víc.

Pozor na transakce – rollback není vždy automatický!

Nezkušeného vývojáře, ale jak jsem se bohužel na vlastní oči mnohdy přesvědčil – mnohdy i velmi zkušeného vývojáře, dokáže zdrcujícím způsobem překvapit výsledek následujícího jednoduchého příkladu:

CREATE TABLE TransactionTestTable
(
    TransactionTestID int IDENTITY PRIMARY KEY,
    TransactionText nvarchar(100)
)
GO

CREATE PROCEDURE TransactionTest
AS
    BEGIN TRANSACTION
        
        -- Korektní SQL statement uvnitř transakce
        INSERT INTO TransactionTestTable(TransactionText) VALUES('První insert')

        -- Simulace chyby uvnitř transakce (do identity-column nelze zapisovat)
        INSERT INTO TransactionTestTable(TransactionTestID, TransactionText) VALUES(1, 'Druhý insert')

        -- Korektní SQL statement uvnitř transakce, po chybě
        INSERT INTO TransactionTestTable(TransactionText) VALUES('Třetí insert')

    COMMIT TRANSACTION
GO

EXEC TransactionTest
SELECT * FROM TransactionTestTable
GO

…spuštěná transakce (zde pouze pro formu zabalená do procedury TransactionTest) selže – druhý INSERT zfailuje, protože nemůže zapisovat přímo do identity-column. To zde není podstatné, je to pouze simulace pro zfailování statementu uvnitř transakce.

Velkým překvapením mnohých však je, že v tabulce TransactionTestTable budou po skončení transakce následující data:

TransactionTestID TransactionText
1 První insert
2 Třetí insert

Zfailováním druhého statementu nedojde k automatickému rollbacku celé transakce!
Run-time chyby statementů rollbackují automaticky pouze statement, který chybu způsobil!

Pokud chceme, aby run-time chyba automaticky zrollbackovala celou transakci, musíme nastavit SET XACT_ABORT ON:

CREATE PROCEDURE TransactionTest
AS
    SET XACT_ABORT ON
    BEGIN TRANSACTION
        
        -- Korektní SQL statement uvnitř transakce
        INSERT INTO TransactionTestTable(TransactionName) VALUES('První insert')

        -- Chybný SQL statement uvnitř transakce, do identity-column nelze zapisovat
        INSERT INTO TransactionTestTable(TransactionTestID, TransactionName) VALUES(1, 'Druhý insert')

        -- Korektní SQL statement uvnitř transakce, po chybě
        INSERT INTO TransactionTestTable(TransactionName) VALUES('Třetí insert')

    COMMIT TRANSACTION
GO

Jemněji než automatický rollback pomocí SET XACT_ABORT ON lze v praxi použít ošetření chyb pomocí TRY..CATCH (i když i to má svá úskalí):

USE AdventureWorks;
GO
BEGIN TRANSACTION;

BEGIN TRY
   -- Generate a constraint violation error.
   DELETE FROM Production.Product
      WHERE ProductID = 980;
END TRY
BEGIN CATCH
   SELECT 
      ERROR_NUMBER() AS ErrorNumber,
      ERROR_SEVERITY() AS ErrorSeverity,
      ERROR_STATE() as ErrorState,
      ERROR_PROCEDURE() as ErrorProcedure,
      ERROR_LINE() as ErrorLine,
      ERROR_MESSAGE() as ErrorMessage;

   IF @@TRANCOUNT > 0
      ROLLBACK TRANSACTION;
END CATCH;

IF @@TRANCOUNT > 0
   COMMIT TRANSACTION;
GO

…případně „postaru“ testovat @@ERROR <> 0 po každém statementu.

T-SQL: Výběr náhodného záznamu z tabulky

Celkem jednoduchý fígl pro výběr náhodného záznamu z tabulky

SELECT TOP 1 Sloupec
   FROM Tabulka
   ORDER BY NEWID()

Generátor NEWID() nám dává přiměřenou náhodnost. Funkci RAND() nelze použít, protože její opakované volání dává stejné hodnoty, např.

SELECT RAND(100), RAND(), RAND()
SELECT RAND(100), RAND(), RAND()

…oba řádky dají stejné hodnoty.

Nevalně bychom dopadli i s použitím GETDATE().

CREATE .. EXTERNAL NAME – Incorrect syntax

Pekelně dlouho jsem se kdysi namordoval s hláškou „Incorrect syntax near ‚[Jmeno.Assembly]‘.“, kterou mi SQL2005 server hlásil na příkaz

CREATE TYPE dbo.JmenoTypu
EXTERNAL NAME [Jmeno.Assembly].[Namespace.Namespace.Type]

Protože jsem to tehdy dělal poprvé, zkoušel jsem snad všechny možné i nemožné podoby, abych opravil syntaxi.

Problém byl úplně jiný! Databáze, na které jsem to zkoušel, byla přenesena z SQL2000 serveru a v byla v módu SQL2000.

Stačí pomocí Management Studia přepnout databázi do SQL2005 módu a vše funguje (Database – Properties ~ Options).

Když jsem to zjistil, zuřil jsem ještě víc, protože nejenomže je ta hláška dost zavádějící, ale navíc mi předtím server klidně povolil CREATE ASSEMBLY a dokonce bylo v Management Studiu assembly i vidět!

SQL: Stránkování záznamů pomocí ROW_NUMBER()

Pokud potřebujeme stránkovat záznamy na straně SQL, můžeme to udělat pomocí jedné z nových funkcí SQL2005:

USE AdventureWorks
WITH Rows AS
(
   SELECT *, ROW_NUMBER() OVER(ORDER BY ProductID) AS RowNumber FROM Production.Product
)
SELECT * FROM Rows WHERE RowNumber BETWEEN 101 AND 200

Bohužel se mi to nepodařilo přímo bez CTE v prvním WHERE, tyhle nové funkce jdou zřejmě jenom jako sloupce nebo v ORDER BY.
Další funkce ze skupiny ranking jsou RANK(), DENSE_RANK(), ROW_NUMBER() a NTILE().

Komu se nelíbí Common Table Expression (CTE) s WITH, tak to samozřejmě jde i jako vnořený SELECT:

SELECT  Description, Date
   FROM (SELECT  ROW_NUMBER() OVER (ORDER BY Date DESC) AS Row,
            Description, Date FROM LOG) AS LogWithRowNumbers
   WHERE  Row >= 1 AND Row <= 10

Počet znaků ntext položky – DATALENGTH()

Na pole referenčních typů ntext, text, apod. nejde použít řetězcovou funkci LEN(expr), funguje však

 DATALENGTH(expr)

které vrátí velikost v bytech (u Unicode položek je to dvojnásobek počtu znaků).

Mimochodem DATALENGTH() se dá použít i jako takové SQL String.IsNullOrEmpty():

WHERE DATALENGTH(MyColumn) = 0