Tag Archives: T-SQL

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

sp_who2 – nedokumentovaná procedura MSSQL

Kromě klasické sp_who má MSSQL server (tuším 7.0+) ještě nedokumentovanou procedurusp_who2, která kromě základní informací z sp_who (ne všech!) přidává ještě několik dalších podrobností o aktuální uživatelské aktivitě.

Jako nedokumentovaná procedura je celkem nepoužitelná do produkčního prostředí, ale při vývoji a ladění se může hodit. Například je tam šikovný údaj BlkBy (Blocked By), který udává, na jaký jiný proces se čeká, aby se mohlo pokračovat.

TRUNCATE TABLE vs. DELETE

Příkaz TRUNCATE TABLE vyprázdní tabulku a má tak efekt podobný příkazu DELETE (bez omezujících podmínek). Ve skutečnosti je však jejich funkce dosti odlišná.

TRUNCATE TABLE je ale rychlejší, používá méně systémových prostředků a méně prostředků transakčního logu, než DELETE (neloguje mazání každé jednotlivé řádky).

TRUNCATE TABLE navíc vyresetuje identity counter na počáteční hodnotu!!!

TRUNCATE TABLE neaktivuje TRIGGER !!!

DELETE vymaže řádky po jednom a do transaction-logu se zaznamená každé jednotlivé vymazání samostatně. Naproti tomu TRUNCATE TABLE vymaže data dealokací (uvolněním) datových stránek (data pages) použitých pro tabulku a do transaction-logu jsou zaznamenány jen tyto dealokace.

Uložení výsledku stored procedury pomocí SELECT INTO

Když neznáme přesnou strukturu výsledku stored procedury, nemůžeme použít CREATE TABLE a INSERT … EXEC. Dá se to obejít pomocí OPENQUERY:

exec sp_serveroption [SERVER_NAME] , 'data access', 'true'

SELECT * INTO #W FROM OPENQUERY([SERVER_NAME], 'exec sp_who')
SELECT * FROM #W

SQL: Doplňování (padding) čísel nulami zleva na pevný počet míst (15 -> 00015)

Možná to jde lépe, ale co třeba takto:

= REPLACE(STR(15, 5, 0), ' ', '0')

Funkce STR(num, length, decimals) převede číslo na řetězec určené délky (doplněný zleva mezerami) a s daným počtem desetinných míst.

Nebo takto?

= RIGHT('00000' + cislo, 5)

Většinou si formátování řešíme až v prezentační vrstvě, ale někdy se to může hodit…

SQL: Určení věku osoby z data narození

Vypadá to jako triviální problém, nicméně triviální řešení zde nejsou správná.

Nejprve tedy správné řešení:

/*
   Vrátí věk ke vztažnému datu určený dle data narození.
*/
ALTER FUNCTION dbo.Age 
(
   @DatumNarozeni smalldatetime,
   @VztazneDatum smalldatetime
)
RETURNS tinyint
AS
BEGIN
   RETURN DATEDIFF(year, @DatumNarozeni, @VztazneDatum)
      - (CASE WHEN (100 * MONTH(@VztazneDatum) + DAY(@VztazneDatum)) < (100 * MONTH(@DatumNarozeni) + DAY(@DatumNarozeni))
              THEN 1 ELSE 0 END)
END

A pár ukázek špatných řešení:

DATEDIFF(year, @birthdate, @enddate)

SELECT DATEDIFF (year, @birthdate, @endDate)
  - CASE WHEN DATEPART(dy, @birthdate) <= DATEPART(dy, @endDate) THEN 1 ELSE 0 END

První pokus od sebe jen odečte letošní letopočet od letopočtu narození.
Druhý pokus zase nefunguje korektně s přestupnými roky, protože pak není počet dnů od 1.1. stejný.