Tag Archives: T-SQL

SQL: Ranking functions & Running totals [Jiří Kanda, HAVIT Vzdělávací okénko, 30.5.2019]

Leave a reply

Záznam ze Vzdělávacího okénka HAVIT ze 30. května 2019, kde Jiří Kanda povídal o Ranking functions a Running totals v T-SQL.

Nahrávka je publikována na našem HAVIT YouTube Channelu.

SQL: Datum poslední aktualizace statistik indexu

Leave a reply

Jednoduchým dotazem se dá zjistit, kdy došlo k poslední aktualizaci statistik indexu (SQL si nepamatuje poslední rebuild indexu, ale tohle je taková odpovídající hodnota):

SELECT
		o.name AS TableName,
		i.name AS IndexName,
		STATS_DATE(i.object_id, i.index_id) AS StatisticsUpdate
	FROM sys.objects o
		INNER JOIN sys.indexes i ON (o.object_id = i.object_id)
	WHERE
		(i.type > 0)
		AND (o.type_desc NOT IN ('INTERNAL_TABLE', 'SYSTEM_TABLE'))
	ORDER BY TableName, IndexName
	-- ORDER BY StatisticsUpdate

Viz též:

T-SQL: Smazání všech tabulek a dalších běžných objektů z DB

Leave a reply

Zdroj: http://stackoverflow.com/questions/536350/drop-all-the-tables-stored-procedures-triggers-constraints-and-all-the-depend

/* Drop all non-system stored procs */
DECLARE @name VARCHAR(128)
DECLARE @SQL VARCHAR(254)

SELECT @name = (SELECT TOP 1 [name] FROM sysobjects WHERE [type] = 'P' AND category = 0 ORDER BY [name])

WHILE @name is not null
BEGIN
    SELECT @SQL = 'DROP PROCEDURE [dbo].[' + RTRIM(@name) +']'
    EXEC (@SQL)
    PRINT 'Dropped Procedure: ' + @name
    SELECT @name = (SELECT TOP 1 [name] FROM sysobjects WHERE [type] = 'P' AND category = 0 AND [name] > @name ORDER BY [name])
END
GO

/* Drop all views */
DECLARE @name VARCHAR(128)
DECLARE @SQL VARCHAR(254)

SELECT @name = (SELECT TOP 1 [name] FROM sysobjects WHERE [type] = 'V' AND category = 0 ORDER BY [name])

WHILE @name IS NOT NULL
BEGIN
    SELECT @SQL = 'DROP VIEW [dbo].[' + RTRIM(@name) +']'
    EXEC (@SQL)
    PRINT 'Dropped View: ' + @name
    SELECT @name = (SELECT TOP 1 [name] FROM sysobjects WHERE [type] = 'V' AND category = 0 AND [name] > @name ORDER BY [name])
END
GO

/* Drop all functions */
DECLARE @name VARCHAR(128)
DECLARE @SQL VARCHAR(254)

SELECT @name = (SELECT TOP 1 [name] FROM sysobjects WHERE [type] IN (N'FN', N'IF', N'TF', N'FS', N'FT') AND category = 0 ORDER BY [name])

WHILE @name IS NOT NULL
BEGIN
    SELECT @SQL = 'DROP FUNCTION [dbo].[' + RTRIM(@name) +']'
    EXEC (@SQL)
    PRINT 'Dropped Function: ' + @name
    SELECT @name = (SELECT TOP 1 [name] FROM sysobjects WHERE [type] IN (N'FN', N'IF', N'TF', N'FS', N'FT') AND category = 0 AND [name] > @name ORDER BY [name])
END
GO

/* Drop all Foreign Key constraints */
DECLARE @name VARCHAR(128)
DECLARE @constraint VARCHAR(254)
DECLARE @SQL VARCHAR(254)

SELECT @name = (SELECT TOP 1 TABLE_NAME FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE constraint_catalog=DB_NAME() AND CONSTRAINT_TYPE = 'FOREIGN KEY' ORDER BY TABLE_NAME)

WHILE @name is not null
BEGIN
    SELECT @constraint = (SELECT TOP 1 CONSTRAINT_NAME FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE constraint_catalog=DB_NAME() AND CONSTRAINT_TYPE = 'FOREIGN KEY' AND TABLE_NAME = @name ORDER BY CONSTRAINT_NAME)
    WHILE @constraint IS NOT NULL
    BEGIN
        SELECT @SQL = 'ALTER TABLE [dbo].[' + RTRIM(@name) +'] DROP CONSTRAINT [' + RTRIM(@constraint) +']'
        EXEC (@SQL)
        PRINT 'Dropped FK Constraint: ' + @constraint + ' on ' + @name
        SELECT @constraint = (SELECT TOP 1 CONSTRAINT_NAME FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE constraint_catalog=DB_NAME() AND CONSTRAINT_TYPE = 'FOREIGN KEY' AND CONSTRAINT_NAME <> @constraint AND TABLE_NAME = @name ORDER BY CONSTRAINT_NAME)
    END
SELECT @name = (SELECT TOP 1 TABLE_NAME FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE constraint_catalog=DB_NAME() AND CONSTRAINT_TYPE = 'FOREIGN KEY' ORDER BY TABLE_NAME)
END
GO

/* Drop all Primary Key constraints */
DECLARE @name VARCHAR(128)
DECLARE @constraint VARCHAR(254)
DECLARE @SQL VARCHAR(254)

SELECT @name = (SELECT TOP 1 TABLE_NAME FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE constraint_catalog=DB_NAME() AND CONSTRAINT_TYPE = 'PRIMARY KEY' ORDER BY TABLE_NAME)

WHILE @name IS NOT NULL
BEGIN
    SELECT @constraint = (SELECT TOP 1 CONSTRAINT_NAME FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE constraint_catalog=DB_NAME() AND CONSTRAINT_TYPE = 'PRIMARY KEY' AND TABLE_NAME = @name ORDER BY CONSTRAINT_NAME)
    WHILE @constraint is not null
    BEGIN
        SELECT @SQL = 'ALTER TABLE [dbo].[' + RTRIM(@name) +'] DROP CONSTRAINT [' + RTRIM(@constraint)+']'
        EXEC (@SQL)
        PRINT 'Dropped PK Constraint: ' + @constraint + ' on ' + @name
        SELECT @constraint = (SELECT TOP 1 CONSTRAINT_NAME FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE constraint_catalog=DB_NAME() AND CONSTRAINT_TYPE = 'PRIMARY KEY' AND CONSTRAINT_NAME <> @constraint AND TABLE_NAME = @name ORDER BY CONSTRAINT_NAME)
    END
SELECT @name = (SELECT TOP 1 TABLE_NAME FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE constraint_catalog=DB_NAME() AND CONSTRAINT_TYPE = 'PRIMARY KEY' ORDER BY TABLE_NAME)
END
GO

/* Drop all tables */
DECLARE @name VARCHAR(128)
DECLARE @SQL VARCHAR(254)

SELECT @name = (SELECT TOP 1 [name] FROM sysobjects WHERE [type] = 'U' AND category = 0 ORDER BY [name])

WHILE @name IS NOT NULL
BEGIN
    SELECT @SQL = 'DROP TABLE [dbo].[' + RTRIM(@name) +']'
    EXEC (@SQL)
    PRINT 'Dropped Table: ' + @name
    SELECT @name = (SELECT TOP 1 [name] FROM sysobjects WHERE [type] = 'U' AND category = 0 AND [name] > @name ORDER BY [name])
END
GO

T-SQL: Seznam tabulek bez IDENTITY

Leave a reply 
SELECT  [schema] = s.name,
        [table] = t.name
	FROM    sys.schemas AS s
			INNER JOIN sys.tables AS t ON s.[schema_id] = t.[schema_id]
	WHERE   NOT EXISTS (SELECT 1
						 FROM   sys.identity_columns
						 WHERE  [object_id] = t.[object_id])
	ORDER BY t.name

sp_MSForeachDB – Vykonání SQL příkazů pro každou DB

Leave a reply

Microsoft SQL Server má nedokumentovanou uloženou proceduru, která usnadní vykonání stejného SQL příkazu pro více DB. Např. nalezení CLR assemblies přes všechny DB:

sp_MSForeachDB 'use [?]; SELECT DB_NAME(), * FROM sys.assemblies WHERE name LIKE ''Havit%'';'

Pokud chceme příkaz vykonat s výjimkou některých DB, můžeme použít IF:

sp_MSForeach 'IF ? NOT IN (''master'', ''msdb'', ''tempdb'', ''model'') USE [?]; -- atp'

(Pozor, že pokud dáte pod IF jenom USE, tak se Vám následný příkaz vykoná pro aktuální DB několikrát, protože neproběhne jenom přepnutí USE na jinou DB.)

SQL: Jsou dvě data (small)datetime ze stejného dne?

Leave a reply

Potíž je v tom, že typ (small)datetime obsahuje mimo údaje data i čas. Můžeme samozřejmě zařídit, aby naše záznamy měly tento čas 00:00, ale stejně občas na tento problém narazíme.

Měli bychom rozlišit dvě situace, porovnávání s pevným dnem, nebo hledání shod ve variabilních datech.

Hledání shod ve variabilních datech

Jsme zde v situaci, kdy obě porovnávané složky jsou variabilní, u obou se musíme vypořádat s ignorováním složky dne.

SQL Server 2008 a novější verze mají datový typ date. Pokud tedy nejsme zrovna na nějaké pravěké verzi SQL, ukazuje se jako nejrychlejší (ač možná překvapivě) tato konverze:

SELECT MyColumn FROM MyTable
	WHERE CONVERT(date, DateColumn1) = CONVERT(date, DateColumn2)

Pro starší verze SQL serveru je použitelných několik o dost méně výhodných (pomalejších) podob. Protože SQL 2005 a starší již dnes považuji za pravěk, nebudu již rozebírat jejich výhody/nevýhody a rychlost:

...
WHERE
   (YEAR(Datum1) = YEAR(Datum2)
   AND (MONTH(Datum1) = MONTH(Datum2)
   AND (DAY(Datum1) = DAY(Datum2)

WHERE 
   (YEAR(Datum1) = YEAR(Datum2))
   AND (DATEPART(dayofyear, Datum1) = DATEPART(dayofyear, Datum2))

WHERE CONVERT(varchar(10), Datum1, 101) = CONVERT(varchar(10), Datum2, 101)

WHERE FLOOR(CONVERT(float, Datum1)) = FLOOR(CONVERT(float, Datum2))

Porovnání s pevným dnem

Trochu jiná situace nastává v okamžiku, kdy jeden z údajů je pevný. Například hledáme záznamy založené určitého dne

DECLARE @DateFilter smalldatetime
SET @DateFilter = '20090305'

SELECT Created FROM MyTable
	WHERE
		(Created >= @DateFilter)
		AND (Created < DATEADD(day, 1, @DateFilter))

…zde s výhodou celý problém převádíme na porovnávání s konstantami, a pokud máme k dispozici vhodný index, dokonce na operaci Index Seek.

Kupodivu úplně identický execution plan (Index Seek) a se stejnými časy dotazu (testováno na obrovských datech na MS SQL Serveru 2012) dostáváme i pro podobu:

DECLARE @DateFilter date
SET @DateFilter = '20090305'

SELECT Created FROM MyTable
	WHERE CONVERT(date, Created) = @DateFilter

Tipy a triky T-SQL z praxe – Slides a dema [TechEd Praha 2010]

Leave a reply

Slides a dema z přednášky na konferenci TechEd Praha 2010:

Záznam z přednášky nebyl pořizován.

Rozdíl mezi COALESCE() a ISNULL()

Leave a reply

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ý!

Leave a reply

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

Leave a reply

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().