row_number vs with. Практика.

T-SQL.
Есть две даты как параметры, между ними не бывает большого диапазона, ну скажем не более месяца...


Задача: запросом вернуть список дат между двумя заданными, вспомогательных табличек нет.

Два варианта.
1. Используя row_number




declare @d1 datetime, @d2 datetime, @li int

set @d1 ='20121201'
set @d2='20121212'

set @li=datediff(d, @d1, @d2)

select top(@li) dateadd(d, (row_number() over(order by s1.id))-1, @d1)
from sys.sysobjects s1


2. Используя WITH


declare @d1 datetime, @d2 DATETIME, @di1 INT, @di2 INT

set @d1 ='20121201';
set @d2='20121212';
SET @di1 = CAST (@d1 AS INT);
SET @di2 = CAST (@d2 AS INT);

WITH sq(num) AS 
(
 SELECT CAST(@di1 AS DATETIME)
 UNION ALL
    SELECT CAST(num + 1 AS DATETIME) FROM sq WHERE num < @di2
)
SELECT * FROM sq



Второй вариант работает чуть быстрее (милисекунды) чем первый на малом диапазоне, поэтому на малом диапазоне выбирайте, что душе угодно.
А вот на больших диапазонах выигрыш второго варианта существенный.
Потестируйте -)

DISTINCT

Абсолютно одинаковые планы запросов на MS SQL Express,  но версия с DISTINCT будет отличаться на MS SQL Enterprise. Потому что в план запроса включится Parallel процесс.

SELECT COUNT(DISTINCT Color)

FROM [Production].[Product] pp

WHERE Color is not null

select COUNT(*)

from (

SELECT Color

FROM [Production].[Product] pp

where Color is not null

group by pp.Color) a

T-SQL. Правила.

1. В SELECT используйте (NOLOCK) для предотвращения таймаутов и задержек при чтении данных.

2. Всегда пишите схему данных (dbo.table1)

3. Всегда используйте псевдонимы для таблиц

EXISTS vs COUNT

Временные таблицы (#table), табличные переменные(@table), обобщенные табличные выражения CTE (WITH ...))

NOLOCK

INSERT, UPDATE, DELETE templates

UPDATE t1

SET t1.id =

FROM dbo.t1

INNER JOIN dbo.t2 ON t1.id=t2.id

WHERE t2.var = 'condition'

LEFT JOIN vs IN

Когда нужно исключить данные в выборке по условию, то зачастую используют ключевое слово IN. Но мало кто знает что это не вызывает проблем только на небольших порциях данных. План выполнения IN не отличается от LEFT JOIN на небольших порциях, но кардинально отличается на больших. И IN начинает жутко тормозить.

Можно:

WHERE id NOT IN ('T', 'C')

короткая коллекция

Не следует:

WHERE id NOT IN ( SELECT id FROM table (NOLOCK))

table может быть сколь угодно большим

Следует:

SELECT s.id FROM source s (NOLOCK)

LEFT OUTER JOIN table t (NOLOCK) on t.id=s.id

WHERE t.id IS NULL

Блог посвящен заметкам по MS SQL, T-SQL и Business Intelligence (SSIS, SSAS, SSRS)