Главная

Дополнительные таблицы. Numbers

Для многих задач полезно иметь под рукой дополнительные таблицы. Одной из таких таблиц является таблица с последовательным набором чисел Numbers. Ее можно использовать как для создания тестовых таблиц с данными, так и для прикладных задач. Особенно полезны для работы со строками, для их итеративной обработки.

Существует множество способов создания такой таблицы:
  • master..spt_values
  • row_number() с любой таблицей, например sys.all_objects
  • CTE
  • Рекурсивный CTE
  • Цикл

spt_values

Можно ничего не генерировать, а воспользоваться данной стандартной таблицей. Она содержит 2048 последовательных значения:

select number 
from master.dbo.spt_values
where type = 'P'

row_number()

Если необходимо больше значений, можно сделать cross join таблицы master..spt_values и воспользоваться функцией row_number(), ограничивая набор нужным количеством строк

 
select top(1000000) row_number() over(order by s1.number) as number
from master..spt_values s1, master..spt_values s2
option (maxdop 1)

CTE

Следующий вариант генерации последовательного набора чисел -- воспользоваться механизмом табличных выражений CTE. Данная реализация менее компактна, нежели предыдущая.

;with 
t10(n) as (
 select 1 union all select 1 union all select 1 union all 
 select 1 union all select 1 union all select 1 union all 
 select 1 union all select 1 union all select 1 union all select 1
),                          
t1000(n) as (
 select 1 from t10 a, t10 b, t10 c
), 
t1000000(n) as (
 select 1 from t1000 a, t1000 b
) 
select row_number() over(order by n) as number 
from t1000000;


Recursive CTE

Вариант с рекурсивным табличным выражением более компактный, но работает значительно дольше, чем свои конкуренты. Вообще, я стараюсь избегать рекурсивных запросов, особенно на больших таблицах

;with Numbers(num) as
(
 select 1 as num
 union all
 select num + 1 as num from Numbers
 where num < 1000000
)
select num from Numbers
option (maxrecursion 0)

Хоть и по приведенным ниже ссылкам уже производится сравнения, приведу собственные замеры по времени выполнения данных методов. При тестировании результаты запросов записывались во временную таблицу

Таблица Numbers

Для себя я использую следующий скрипт и храню таблицу Numbers под рукой.

if object_id('dbo.Numbers', 'U') is not null
 drop table dbo.Numbers

select top(1000000) n = row_number() over(order by s1.number)
into dbo.Numbers
from master..spt_values s1, master..spt_values s2
option (maxdop 1);

create unique clustered index ix_numbers_n on dbo.Numbers(n)

Пример использования таблицы Numbers

Ссылки:

26 февраля 2015 г.