Tag Archives: T-SQL

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

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

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

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

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

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?

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]

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

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