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

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

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

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

Как ни странно, сама статья содержит много субъективных утверждений и "предвзятых рекомендаций", таких как "рекурсивный CTE может генерировать список чисел довольно эффективно" и "Это эффективный метод использования цикла WHILE из сообщения Ицика Бен-Гена" (которое, я уверен, он разместил просто для сравнения). C'mon folks... Одно упоминание доброго имени Ицика может привести к тому, что какой-нибудь бедняга начнет использовать этот ужасный метод. Автору следует практиковать то, что он проповедует, и провести небольшое тестирование производительности, прежде чем делать такие смехотворно неверные заявления, особенно перед лицом любой масштабируемости.

С мыслью о том, чтобы действительно провести тестирование, прежде чем делать субъективные заявления о том, что делает любой код или что кому-то "нравится", вот'некоторый код, с которым вы можете провести собственное тестирование. Настройте профайлер для SPID, с которого вы запускаете тест, и проверьте его сами... просто выполните "Поиск&quot-, "поиск&quot- и "замену" числа 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 занимает второе место после цикла While Loop по длительности и CPU и оказывает в 8 раз большее давление на память в виде логических чтений, чем цикл While Loop. Это RBAR на стероидах, и его следует избегать любой ценой для любых вычислений в одной строке так же, как и цикла While Loop. Есть места, где рекурсия весьма полезна, но это не одно из них.

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

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

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

Спасибо за внимание...

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

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

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

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

WITH Nbrs ( n ) AS (
    SELECT 1 UNION ALL
    SELECT 1 + n FROM Nbrs WHERE n < 500 )
SELECT n FROM Nbrs
OPTION ( MAXRECURSION 500 )

Это представление супер быстро и содержит все положительные 'международные' ценности.

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

Используя 'SQL-сервер 2016 +', чтобы произвести стол чисел Вы могли использовать 'ОПЕНДЖСОНА':

-- 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),']'));

отредактируйте: см. Conrad' s комментируют ниже.

Джефф Moden' s ответ большое..., но я нахожу на Пост-ГРЭС, что метод Itzik терпит неудачу, если Вы не удаляете ряд E32.

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

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-сервера до мира Пост-ГРЭС, возможно, пропустил лучший способ сделать столы счета на той платформе... ЦЕЛОЕ ЧИСЛО ()? ПОСЛЕДОВАТЕЛЬНОСТЬ ()?

Еще намного позже, I' d нравится вносить немного отличающийся ' traditional' 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 выступает, больше ЧИТАЕТ тогда Itzik' s CTE, но меньше тогда Традиционный CTE. Однако это последовательно выступает, меньше ПИШЕТ тогда другие вопросы. Как Вы знаете, Пишет, последовательно вполне намного более дорогие, тогда Читает.

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

Я использую:

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.67Ghz, 4 ядрах позволен HT.