SQL, Вспомогательная таблица чисел

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

Каков оптимальный способ создания такой функции?

14.08.2008 09:01:37
Не могли бы вы объяснить, почему вы делаете это, а не используете таблицу, предварительно заполненную числами?
jammus 14.08.2008 09:39:26
Для заполнения такой таблицы, например.
vzczc 27.05.2009 10:57:40
Не все приложения DBA и / или сторонние приложения позволяют добавлять постоянную таблицу.
JeffO 25.02.2013 20:54:10
Проголосуйте за встроенную функцию таблицы виртуальных чисел, которая не тратит память и ввод-вывод, на https://feedback.azure.com/forums/908035-sql-server/suggestions/32890519-add-a-built-in-table- чисел
Louis Somers 9.04.2019 11:56:50
7 ОТВЕТОВ
РЕШЕНИЕ

Хех ... извините, я так поздно отвечаю на старый пост. И да, я должен был ответить, потому что самый популярный ответ (в то время, ответ Recursive CTE со ссылкой на 14 различных методов) в этой теме, ммм ... производительность ставится под сомнение в лучшем случае.

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

«Предложения относительно эффективности и производительности часто носят субъективный характер. Независимо от того, как используется запрос, физическая реализация определяет эффективность запроса. Поэтому, вместо того, чтобы полагаться на предвзятые рекомендации, необходимо протестировать запрос и определить, какой из них работает лучше ".

По иронии судьбы, сама статья содержит много субъективных утверждений и «предвзятых указаний», таких как «рекурсивный CTE может довольно эффективно генерировать список чисел » и «Это эффективный метод использования цикла WHILE из публикации в группе новостей Ицик Бен-Ген» ( я уверен, что он выложил только для сравнения). Давай, ребята ... Просто упоминание доброго имени Ицик может привести к тому, что какой-то плохой мужик действительно использует этот ужасный метод. Автор должен практиковать то, что он проповедует, и должен немного протестировать производительность, прежде чем делать такие смехотворно неверные заявления, особенно перед лицом любой масштабируемости.

С мыслью о том, что нужно провести некоторое тестирование, прежде чем делать какие-либо субъективные заявления о том, что делает какой-либо код или что кому-то «нравится», вот код, с которым вы можете провести свое собственное тестирование. Настройте профилировщик для SPID, с которого вы запускаете тест, и проверьте его сами ... просто выполните "Search'n'Replace" с номером 1000000 для своего "любимого" номера и посмотрите ...

--===== Test for 1000000 rows ==================================
GO
--===== Traditional RECURSIVE CTE method
   WITH Tally (N) AS 
        ( 
         SELECT 1 UNION ALL 
         SELECT 1 + N FROM Tally WHERE N < 1000000 
        ) 
 SELECT N 
   INTO #Tally1 
   FROM Tally 
 OPTION (MAXRECURSION 0);
GO
--===== Traditional WHILE LOOP method
 CREATE TABLE #Tally2 (N INT);
    SET NOCOUNT ON;
DECLARE @Index INT;
    SET @Index = 1;
  WHILE @Index <= 1000000 
  BEGIN 
         INSERT #Tally2 (N) 
         VALUES (@Index);
            SET @Index = @Index + 1;
    END;
GO
--===== Traditional CROSS JOIN table method
 SELECT TOP (1000000)
        ROW_NUMBER() OVER (ORDER BY (SELECT 1)) AS N
   INTO #Tally3
   FROM Master.sys.All_Columns ac1
  CROSS JOIN Master.sys.ALL_Columns ac2;
GO
--===== Itzik's CROSS JOINED CTE method
   WITH E00(N) AS (SELECT 1 UNION ALL SELECT 1),
        E02(N) AS (SELECT 1 FROM E00 a, E00 b),
        E04(N) AS (SELECT 1 FROM E02 a, E02 b),
        E08(N) AS (SELECT 1 FROM E04 a, E04 b),
        E16(N) AS (SELECT 1 FROM E08 a, E08 b),
        E32(N) AS (SELECT 1 FROM E16 a, E16 b),
   cteTally(N) AS (SELECT ROW_NUMBER() OVER (ORDER BY N) FROM E32)
 SELECT N
   INTO #Tally4
   FROM cteTally
  WHERE N <= 1000000;
GO
--===== Housekeeping
   DROP TABLE #Tally1, #Tally2, #Tally3, #Tally4;
GO

Пока мы на этом, вот цифры, которые я получаю из SQL Profiler для значений 100, 1000, 10000, 100000 и 1000000 ...

SPID TextData                                 Dur(ms) CPU   Reads   Writes
---- ---------------------------------------- ------- ----- ------- ------
  51 --===== Test for 100 rows ==============       8     0       0      0
  51 --===== Traditional RECURSIVE CTE method      16     0     868      0
  51 --===== Traditional WHILE LOOP method CR      73    16     175      2
  51 --===== Traditional CROSS JOIN table met      11     0      80      0
  51 --===== Itzik's CROSS JOINED CTE method        6     0      63      0
  51 --===== Housekeeping   DROP TABLE #Tally      35    31     401      0

  51 --===== Test for 1000 rows =============       0     0       0      0
  51 --===== Traditional RECURSIVE CTE method      47    47    8074      0
  51 --===== Traditional WHILE LOOP method CR      80    78    1085      0
  51 --===== Traditional CROSS JOIN table met       5     0      98      0
  51 --===== Itzik's CROSS JOINED CTE method        2     0      83      0
  51 --===== Housekeeping   DROP TABLE #Tally       6    15     426      0

  51 --===== Test for 10000 rows ============       0     0       0      0
  51 --===== Traditional RECURSIVE CTE method     434   344   80230     10
  51 --===== Traditional WHILE LOOP method CR     671   563   10240      9
  51 --===== Traditional CROSS JOIN table met      25    31     302     15
  51 --===== Itzik's CROSS JOINED CTE method       24     0     192     15
  51 --===== Housekeeping   DROP TABLE #Tally       7    15     531      0

  51 --===== Test for 100000 rows ===========       0     0       0      0
  51 --===== Traditional RECURSIVE CTE method    4143  3813  800260    154
  51 --===== Traditional WHILE LOOP method CR    5820  5547  101380    161
  51 --===== Traditional CROSS JOIN table met     160   140     479    211
  51 --===== Itzik's CROSS JOINED CTE method      153   141     276    204
  51 --===== Housekeeping   DROP TABLE #Tally      10    15     761      0

  51 --===== Test for 1000000 rows ==========       0     0       0      0
  51 --===== Traditional RECURSIVE CTE method   41349 37437 8001048   1601
  51 --===== Traditional WHILE LOOP method CR   59138 56141 1012785   1682
  51 --===== Traditional CROSS JOIN table met    1224  1219    2429   2101
  51 --===== Itzik's CROSS JOINED CTE method     1448  1328    1217   2095
  51 --===== Housekeeping   DROP TABLE #Tally       8     0     415      0

Как вы можете видеть, метод рекурсивного CTE является вторым худшим после цикла Loop по длительности и процессору и имеет в 8 раз большую нагрузку на память в виде логических операций чтения, чем цикл Loop . Это RBAR на стероидах, и его следует избегать любой ценой для любых вычислений в одной строке, так же как следует избегать циклического цикла. Есть места, где рекурсия весьма ценна, но это НЕ одно из них .

В качестве боковой панели г-н Денни абсолютно на ... постоянный номер правильных размеров или стол Tally - подход к большинству вещей. Что означает правильный размер? Ну, большинство людей используют таблицу Tally для генерации дат или разбивки на VARCHAR (8000). Если вы создадите таблицу Tally из 11 000 строк с правильным кластеризованным индексом «N», у вас будет достаточно строк для создания дат на более чем 30 лет (я работаю с ипотечными кредитами довольно много, поэтому 30 лет - это ключевой показатель для меня ) и, конечно, достаточно, чтобы справиться с разделением VARCHAR (8000). Почему «правильный размер» так важен? Если таблица Tally используется часто, она легко помещается в кэш, что делает его невероятно быстрым без какого-либо большого давления на память.

И последнее, но не менее важное: каждый знает, что если вы создаете постоянную таблицу Tally, то не имеет значения, какой метод вы используете для ее построения, потому что 1) это будет сделано только один раз и 2) если это что-то вроде строки в 11 000 Таблица, все методы будут работать "достаточно хорошо". Так почему же все с моей стороны, о том, какой метод использовать ???

Ответ заключается в том, что какой-то бедный парень / девчонка, который не знает ничего лучше и просто нуждается в выполнении своей работы, может увидеть что-то вроде метода рекурсивного CTE и решит использовать его для чего-то гораздо большего и гораздо более часто используемого, чем создание постоянная таблица Tally, и я пытаюсь защитить этих людей, серверы, на которых работает их код, и компанию, которой принадлежат данные на этих серверах . Да ... это так важно. Так должно быть и для всех остальных. Научите правильно делать вещи, а не «достаточно хорошо». Проведите некоторое тестирование, прежде чем публиковать или использовать что-то из поста или книги ... жизнь, которую вы спасете, может, на самом деле, быть вашей, особенно если вы думаете, что рекурсивный CTE - это путь для чего-то подобного. ;-)

Спасибо за прослушивание...

109
18.04.2010 17:53:56
Я действительно очень хочу, чтобы у большего количества людей было ваше чувство социальной ответственности. Говорят , что и помимо one'd необходимости один раз , чтобы заполнить таблицу Numbers для всех видов вещей, если потребность в какой - то причине, кажется , SELECT INTOж / IDENTITYбыстрее , чем КТР .
Andre Figueiredo 16.07.2017 03:09:30
Спасибо за очень добрый отзыв, Андре.
Jeff Moden 23.07.2017 00:31:55

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

11
2.09.2008 09:48:19
Я думаю, что это зависит от вашей ситуации. Между двумя наиболее эффективными вариантами вы можете торговать между IO и ценой процессора, в зависимости от того, что для вас дороже.
Rbjz 18.06.2013 10:19:57
IO почти всегда будет дешевле, чем CPU, тем более что эта таблица будет маленькой и, вероятно, уже в budferpool.
mrdenny 18.06.2013 11:53:44
@mrdenny I / O всегда путь дороже и медленнее , чем процессор. SSD несколько изменили это в последние годы, но в большинстве производственных архитектур эти SSD имеют сетевое соединение между ними и процессорами. Единственные базы данных, которые я вижу, которые действительно связаны с процессором, - это запуск ненастроенных приложений только для ORM или тяжелое машинное обучение.
rmalayter 21.11.2017 04:25:43
@rmalayter, за исключением случаев, когда таблица используется достаточно часто, чтобы мы могли о ней позаботиться, она почти наверняка будет в памяти, а память дешевле в обновлении и обычно не влияет на лицензирование, как добавление ядер ЦП. Выпуск SQL Server Enterprise будет состоять из 5 цифр на ядро, т. Е. Добавление ядер, вероятно, будет стоить вам дороже только в рамках лицензирования, чем полная стоимость, связанная с добавлением большего количества оперативной памяти на сервер.
Dogs 14.02.2019 16:31:10

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

предложения относительно эффективности и производительности часто субъективны. Независимо от того, как используется запрос, физическая реализация определяет эффективность запроса. Поэтому, вместо того чтобы полагаться на предвзятые рекомендации, необходимо протестировать запрос и определить, какой из них работает лучше.

Мне лично понравилось:

WITH Nbrs ( n ) AS (
    SELECT 1 UNION ALL
    SELECT 1 + n FROM Nbrs WHERE n < 500 )
SELECT n FROM Nbrs
OPTION ( MAXRECURSION 500 )
5
6.04.2020 12:04:15
Доказано неправильно принятым ответом? Это не «оптимально», хотя выглядит красиво.
Rbjz 18.06.2013 10:17:56

редактировать: см. комментарий Конрада ниже.

Ответ Джеффа Модена великолепен ... но я обнаружил на Postgres, что метод Ицик не работает, если вы не удалите строку E32.

Немного быстрее на postgres (40 мс против 100 мс) - другой метод, который я нашел здесь, адаптированный для postgres:

WITH 
    E00 (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 ),
    E01 (N) AS (SELECT a.N FROM E00 a CROSS JOIN E00 b),
    E02 (N) AS (SELECT a.N FROM E01 a CROSS JOIN E01 b ),
    E03 (N) AS (SELECT a.N FROM E02 a CROSS JOIN E02 b 
        LIMIT 11000  -- end record  11,000 good for 30 yrs dates
    ), -- max is 100,000,000, starts slowing e.g. 1 million 1.5 secs, 2 mil 2.5 secs, 3 mill 4 secs
    Tally (N) as (SELECT row_number() OVER (ORDER BY a.N) FROM E03 a)

SELECT N
FROM Tally

Когда я перехожу с SQL Server на мир Postgres, возможно, упустил лучший способ составления таблиц на этой платформе ... INTEGER ()? ПОСЛЕДОВАТЕЛЬНОСТЬ()?

0
24.06.2013 14:55:46
возможно, пропустили лучший способ подсчета таблиц на [postgres] Да, вы создали generate_series
Conrad Frix 24.01.2013 21:07:31
@Conrad Frix, извиняюсь за очень поздний вопрос (более чем на 5 лет позже), но провели ли вы какое-либо тестирование производительности, чтобы сравнить этот замечательный встроенный инструмент с другими методами?
Jeff Moden 5.09.2018 18:02:20
@JeffModen Извините, нет, но это легко проверить. Возьмите запрос Раскина и сравните его, чтобы вызвать серию.
Conrad Frix 5.09.2018 20:06:06
@Conrad Frix, так как вы заявили о своей производительности и имеете доступ к обеим средам (а я нет), и вы также утверждаете, что это легко проверить, я надеялся, что вы потратите время на его тестирование. ;-)
Jeff Moden 6.09.2018 22:39:47
@Conrad Frix, хе ... у тебя уже есть настройки, и ты не можешь потратить 5 минут, чтобы проверить свои собственные заявления о производительности. NP. Двигаемся дальше
Jeff Moden 8.09.2018 00:31:04

Это представление очень быстрое и содержит все положительные intзначения.

CREATE VIEW dbo.Numbers
WITH SCHEMABINDING
AS
    WITH Int1(z) AS (SELECT 0 UNION ALL SELECT 0)
    , Int2(z) AS (SELECT 0 FROM Int1 a CROSS JOIN Int1 b)
    , Int4(z) AS (SELECT 0 FROM Int2 a CROSS JOIN Int2 b)
    , Int8(z) AS (SELECT 0 FROM Int4 a CROSS JOIN Int4 b)
    , Int16(z) AS (SELECT 0 FROM Int8 a CROSS JOIN Int8 b)
    , Int32(z) AS (SELECT TOP 2147483647 0 FROM Int16 a CROSS JOIN Int16 b)
    SELECT ROW_NUMBER() OVER (ORDER BY z) AS n
    FROM Int32
GO
3
4.07.2011 12:24:12
0часто полезно. И я, вероятно, преобразовал бы последний столбец в int. Также вы должны знать, что в основном метод включается в принятый ответ (без 0преобразования или в intлюбой из них) по имени метода Ицика CROSS JOINED CTE .
Andriy M 4.07.2011 21:02:02
Любая конкретная причина, чтобы добавить WITH SCHEMABINDINGв представлении?
ca9163d9 27.02.2012 22:12:21
Добавление «С SCHEMABINDING» может сделать запросы быстрее. Это помогает оптимизатору узнать, что данные не доступны. (См. Blogs.msdn.com/b/sqlprogrammability/archive/2006/05/12/… )
Anthony Faull 28.02.2012 08:07:34
Интересно, может ли @AnthonyFaull подтвердить это некоторыми измерениями.
Rbjz 18.06.2013 10:22:13

Еще намного позже я хотел бы добавить немного другой «традиционный» CTE (не касается базовых таблиц, чтобы получить объем строк):

--===== Hans CROSS JOINED CTE method
WITH Numbers_CTE (Digit)
AS
(SELECT 0 UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9)
SELECT HundredThousand.Digit * 100000 + TenThousand.Digit * 10000 + Thousand.Digit * 1000 + Hundred.Digit * 100 + Ten.Digit * 10 + One.Digit AS Number
INTO #Tally5
FROM Numbers_CTE AS One CROSS JOIN Numbers_CTE AS Ten CROSS JOIN Numbers_CTE AS Hundred CROSS JOIN Numbers_CTE AS Thousand CROSS JOIN Numbers_CTE AS TenThousand CROSS JOIN Numbers_CTE AS HundredThousand

Этот CTE выполняет больше READs, чем CTE Ицик, но меньше, чем традиционный CTE. Тем не менее, он последовательно выполняет меньше ЗАПИСЕЙ, чем другие запросы. Как вы знаете, Writes значительно дороже, чем Reads.

Продолжительность сильно зависит от количества ядер (MAXDOP), но на моем 8-ядерном процессоре выполняется стабильно быстрее (меньше длительности в мс), чем другие запросы.

Я использую:

Microsoft SQL Server 2012 - 11.0.5058.0 (X64) 
May 14 2014 18:34:29 
Copyright (c) Microsoft Corporation
Enterprise Edition (64-bit) on Windows NT 6.3 <X64> (Build 9600: )

на Windows Server 2012 R2, 32 ГБ, Xeon X3450 @ 2,67 ГГц, 4-ядерный HT включен.

0
22.10.2014 10:06:40

Используя SQL Server 2016+для генерации таблицы чисел вы можете использовать OPENJSON:

-- range from 0 to @max - 1
DECLARE @max INT = 40000;

SELECT rn = CAST([key] AS INT) 
FROM OPENJSON(CONCAT('[1', REPLICATE(CAST(',1' AS VARCHAR(MAX)),@max-1),']'));

LiveDemo


Идея взята из Как мы можем использовать OPENJSON для генерации серии чисел?

1
2.05.2016 18:10:40
Приятно. Я думаю, можно было бы использовать XML аналогично этому, если position()бы он полностью поддерживался в SQL Server XQuery.
Andriy M 2.05.2016 18:06:27
Извините за поздний комментарий, но этот код использует в 11,4 раза больше ЦП и бесконечно больше логических операций чтения (2000,023), чем каскадный метод CTE Итика.
Jeff Moden 5.09.2018 18:01:24