none
generate_series RRS feed

  • Question

  • generate_series(1,count::integer) , please tell me how can use this function in sql server
    Thursday, October 6, 2016 9:13 PM

Answers

All replies

  • generate_series(1,count::integer) as poid this function is used in postgresql , please tell me how can use this function
    Thursday, October 6, 2016 8:03 PM
  • Please post your question to a Postgres forum. Tsql, the sql dialect used by sql server, does not have this function.
    • Marked as answer by Farhan Tariq Thursday, October 6, 2016 8:52 PM
    • Unmarked as answer by Farhan Tariq Thursday, October 6, 2016 8:52 PM
    Thursday, October 6, 2016 8:38 PM
    Answerer
  • Sorry , how can i use this function in sql server

    Thursday, October 6, 2016 8:52 PM
  • generate_series(1,count::integer) , please tell me how can use this function in sql server

    You can't, because there is no such function - but it would have been great if it there was! I happened to see this function last night when I was browsing the manual for PostgreSQL.

    That is actually an item on my SQL Server Wishlist,
    http://www.sommarskog.se/wishlist.html#tblnumbers

    Thursday, October 6, 2016 9:42 PM
    Moderator
  • Sorry , how can i use this function in sql server

    Good day,

    Let me ask you a question: Can someone that live in US and never heard Japanese will be able to do a commend that is given to him in Japanese?

    As you already been told generate_series is a function that used by languages like Psql and it is not used by languages that SQL Server "understand". You can simply create a function that behave the same.

    generate_series generate a series of values, from "start" value to the "stop" value. For example for integers you can use this solution:

    1. create table with all the values available - for our sample I will create table with any number from 1 to 1 million:

    create table NumTbl (N int not null)
    GO
    insert NumTbl 
    select top 1000000 ROW_NUMBER() OVER (order by (select null))
    from sys.all_objects t1
    cross join sys.all_objects t2
    GO
    CREATE CLUSTERED INDEX IX_NumTbl_N
        ON dbo.NumTbl (N);   
    GO  

    2. Now we can create the function and use it

    CREATE FUNCTION dbo.generate_series (@start int, @stop int) RETURNS TABLE  
    AS RETURN ( 
    	 select N from NumTbl
    	 where N >= @start and N<= @stop
    )
    GO  
    
    SELECT * FROM dbo.generate_series(10,20)
    GO

    Same way you can create tables and functions for other types, or you can use the numbers table in order to create functions for other types.


    signature   Ronen Ariely
     [Personal Site]    [Blog]    [Facebook]    [Linkedin]

    Thursday, October 6, 2016 11:19 PM
    Moderator
  • Hi Ronen

    Long time ago Itzik Ben-Gan has posted more efficient way generating sequence tables

    CREATE FUNCTION GenerateSequence (@start INT, @end INT)
    RETURNS TABLE
    AS
    RETURN
    WITH
    Num1 (n) AS (SELECT 1 UNION ALL SELECT 1),
    Num2 (n) AS (SELECT 1 FROM Num1 AS X, Num1 AS Y),
    Num3 (n) AS (SELECT 1 FROM Num2 AS X, Num2 AS Y),
    Num4 (n) AS (SELECT 1 FROM Num3 AS X, Num3 AS Y),
    Num5 (n) AS (SELECT 1 FROM Num4 AS X, Num4 AS Y),
    Num6 (n) AS (SELECT 1 FROM Num5 AS X, Num5 AS Y),
    Nums (n) AS
    (SELECT ROW_NUMBER() OVER(ORDER BY n)
      FROM Num6)
    SELECT n FROM Nums
    WHERE n BETWEEN @start AND @end;

    GO

    SELECT n FROM GenerateSequence(1, 4);


    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Consulting: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance
    SQL Server Integration Services: Business Intelligence

    Friday, October 7, 2016 5:31 AM
    Moderator
  • Thanks Uri,

    This is great solution

    I am not sure if you mean execute the query directly without creating the numbers table. Since it does not use IO. Regarding the query it self here a simple test that anyone can execute and choose

    * Tested on very week development machine using:
    Microsoft SQL Server 2016 (RTM) - 13.0.1601.5 (X64)   Apr 29 2016 23:23:58   Copyright (c) Microsoft Corporation  Enterprise Edition: Core-based Licensing (64-bit) on Windows Server 2012 R2 Standard 6.3 <X64> 

    * In my simple testing your query uses 2-3 times more CPU Time. it did execute a bit faster first time but differences in elapsed time is negligible if any and almost the same time. I tested several times to confirm the result.

    -- test the direct queries time
    SET STATISTICS TIME ON
    GO
    
    select top 1000000 ROW_NUMBER() OVER (order by (select null))
    from sys.all_objects t1
    cross join sys.all_objects t2
    GO
    /*
    SQL Server parse and compile time: 
       CPU time = 0 ms, elapsed time = 0 ms.
    
    (1000000 row(s) affected)
    
     SQL Server Execution Times:
       CPU time = 188 ms,  elapsed time = 2450 ms.
    */
    ;With 
    	Num1 (n) AS (SELECT 1 UNION ALL SELECT 1),
    	Num2 (n) AS (SELECT 1 FROM Num1 AS X, Num1 AS Y),
    	Num3 (n) AS (SELECT 1 FROM Num2 AS X, Num2 AS Y),
    	Num4 (n) AS (SELECT 1 FROM Num3 AS X, Num3 AS Y),
    	Num5 (n) AS (SELECT 1 FROM Num4 AS X, Num4 AS Y),
    	Num6 (n) AS (SELECT 1 FROM Num5 AS X, Num5 AS Y),
    	Nums (n) AS (SELECT ROW_NUMBER() OVER(ORDER BY n) FROM Num6)
    SELECT top 1000000 n FROM Nums
    GO
    /*
    SQL Server parse and compile time: 
       CPU time = 0 ms, elapsed time = 0 ms.
    
    (1000000 row(s) affected)
    
     SQL Server Execution Times:
       CPU time = 467 ms,  elapsed time = 2418 ms.
    */



    signature   Ronen Ariely
     [Personal Site]    [Blog]    [Facebook]    [Linkedin]


    Friday, October 7, 2016 10:15 PM
    Moderator