selecting dataset with row numbers

  • There are functions in newer versions that would solve my problem, but unfortunately we are still using sql 2000. Below is a sample table.

    create table MyList ( customerID int, fullname varchar(40), phone varchar(16) )

    insert into myList (customerID, fullname, phone) values (111, 'john doe', '713-555-5555')

    insert into myList (customerID, fullname, phone) values (121, 'jim garcia', '713-666-5555')

    insert into myList (customerID, fullname, phone) values (221, 'jane smith', '713-777-5555')

    insert into myList (customerID, fullname, phone) values (231, 'mary harris', '713-888-5555')

    insert into myList (customerID, fullname, phone) values (341, 'julie jones', '713-999-5555')

    insert into myList (customerID, fullname, phone) values (345, 'harry ochocinco', '713-999-5555')

    Is there a good way to query the data out of the table to include a row number? An identity column will not really serve my purpose, as I'd like the row number to be dynamic as data is selected out.

    For example the following query :

    select customerID, fullname, phone, [some operation] as rownumber

    from myList

    where customerID > 231

    would return:

    customerID fullname phone rownumber

    ------------- ---------------- ------- ------------

    341 'julie jones' '713-999-5555' 1

    345 'harry ochocinco' '713-999-5555' 2

    I've been poring over the tally table posts but don't see something to help. Seems like this would be common but I can't find anything. Thanks in advance.

  • SELECT customerID,

    fullname,

    phone,

    ROW_NUMBER() OVER ( ORDER BY customerID ) AS rownumber

    FROM myList

    WHERE customerID > 231 ;

    http://technet.microsoft.com/en-us/library/ms186734.aspx

    There are no special teachers of virtue, because virtue is taught by the whole community.
    --Plato

  • thank you but row_number isn't available in sql 2000. I think it was introduced in 2005.

    Continuing to search, I see previous sqlservercentral topics, and the prominent answer is inserting into a temp table or table variable with an identity field. I was well aware that was a possibility but it just seems stupid that that's the best way to do it. The question is mostly academic - I can work around it, but it's nice to do something better.

  • Ahh...you posted in a SQL 2008 forum and I completely breezed by the first line of your post and went straight to your sample data and the later statements in your post. I have a few things I would like to try...I'll look into this and be back in a few with something for you.

    There are no special teachers of virtue, because virtue is taught by the whole community.
    --Plato

  • I would try the identity column method you mentioned...but use a table variable instead of a traditional temp table. I did some quick testing and inserting 400,000 rows into the table variable was about 20% faster that inserting the same rows into a traditional temp table.

    Here is how it would work with your test table:

    DECLARE @tbl TABLE

    (

    customerID INT,

    fullname VARCHAR(40),

    phone VARCHAR(16),

    rownumber INT IDENTITY(1, 1)

    NOT NULL

    ) ;

    INSERT INTO @tbl

    (

    customerID,

    fullname,

    phone

    )

    SELECT customerID,

    fullname,

    phone

    FROM myList

    WHERE customerID > 231

    ORDER BY customerID ;

    SELECT *

    FROM @tbl ;

    There are no special teachers of virtue, because virtue is taught by the whole community.
    --Plato

  • For future reference:

    SQL 2000 T-SQL Forum

    There are no special teachers of virtue, because virtue is taught by the whole community.
    --Plato

  • Oops. Sorry for posting in wrong forum. I did not notice the forum groups. I'll be more careful. Thank you for the suggestion with the table variable. It looks like that makes the most sense.

  • opc.three (7/22/2011)


    I did some quick testing and inserting 400,000 rows into the table variable was about 20% faster that inserting the same rows into a traditional temp table.

    Heh... you know things like that will always catch my eye, Orlando. Where did you get the test data from? If you generated the test data, you should post it so others can verify your claims. 😉

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • I don't know what others may get, but I'm seeing that a Temp Table solution is about half the CPU intensity and about 2/3'rds the duration of a Table Variable solution. Here's the code I used:

    --====================================================================

    -- Create 400,000 rows of test data

    -- This is NOT a part of the solution. We're just setting up to

    -- test.

    --====================================================================

    --===== Do this in a nice, safe place that everyone has.

    USE TempDB

    ;

    --===== Conditionally drop the test table to make reruns easier

    IF OBJECT_ID('dbo.MyList','U') IS NOT NULL

    DROP TABLE dbo.MyList

    ;

    SELECT TOP 400000

    CustomerID = IDENTITY(INT,1,1),

    FullName = RIGHT(NEWID(),ABS(CHECKSUM(NEWID())) % 34 + 2),

    Phone = SUBSTRING(CAST(NEWID() AS CHAR(36)),11,12)

    INTO dbo.MyList

    FROM sys.all_columns ac1

    CROSS JOIN sys.all_columns ac2

    ;

    --====================================================================

    -- Test the Temp Table and Table Variable solutions

    --====================================================================

    PRINT '========== Temp Table Solution ==========';

    SET STATISTICS TIME ON;

    SELECT CustomerID = CAST(customerID AS INT), --Overrides IDENTITY property of CustomerID

    fullname,

    phone,

    RowNumber = IDENTITY(INT,1,1)

    INTO #PerfTest

    FROM dbo.myList

    WHERE customerID > 231

    ORDER BY customerID

    ;

    SET STATISTICS TIME OFF;

    PRINT '========== Table Variable Solution ==========';

    SET STATISTICS TIME ON;

    DECLARE @tbl TABLE

    (

    customerID INT,

    fullname VARCHAR(40),

    phone VARCHAR(16),

    rownumber INT IDENTITY(1, 1)

    NOT NULL

    ) ;

    INSERT INTO @tbl

    (

    customerID,

    fullname,

    phone

    )

    SELECT customerID,

    fullname,

    phone

    FROM myList

    WHERE customerID > 231

    ORDER BY customerID ;

    --SELECT *

    --FROM @tbl ;

    SET STATISTICS TIME OFF;

    Here's the output from my 9 year old, single 1.8 GHz CPU desktop...

    (400000 row(s) affected)

    ========== Temp Table Solution ==========

    SQL Server Execution Times:

    CPU time = 2625 ms, elapsed time = 4868 ms.

    (399769 row(s) affected)

    ========== Table Variable Solution ==========

    SQL Server Execution Times:

    CPU time = 5968 ms, elapsed time = 6595 ms.

    (399769 row(s) affected)

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • Not a problem Jeff. Here is how I got there:

    Build a Tally table to facilitate building of MyList with 400K rows:

    USE tempdb

    GO

    -----------------------------------------------------------------

    -- Build Tally table.

    -- Script credit: http://www.sqlservercentral.com/scripts/Advanced+SQL/62486/

    IF EXISTS ( SELECT *

    FROM sysobjects

    WHERE id = OBJECT_ID(N'dbo.Tally')

    AND type IN ( N'U' ) )

    DROP TABLE dbo.Tally;

    GO

    --===== Create and populate the Tally table on the fly

    SELECT TOP 100000

    IDENTITY(INT,1,1) AS N

    INTO dbo.Tally

    FROM Master.dbo.SysColumns sc1,

    Master.dbo.SysColumns sc2

    --===== Add a Primary Key to maximize performance

    ALTER TABLE dbo.Tally

    ADD CONSTRAINT PK_Tally_N

    PRIMARY KEY CLUSTERED (N) WITH FILLFACTOR = 100

    --===== Allow the general public to use it

    GRANT SELECT ON dbo.Tally TO PUBLIC

    GO

    -----------------------------------------------------------------

    -- Build dbo.MyList and populate for testing

    IF EXISTS ( SELECT *

    FROM sysobjects

    WHERE id = OBJECT_ID(N'dbo.MyList')

    AND type IN ( N'U' ) )

    DROP TABLE dbo.MyList;

    GO

    CREATE TABLE dbo.MyList

    (

    customerID INT,

    fullname VARCHAR(40),

    phone VARCHAR(16)

    )

    -- add 400K rows to MyList

    INSERT INTO dbo.MyList

    (

    customerID,

    fullname,

    phone

    )

    SELECT TOP 400000

    t.N,

    CAST(c.ContactName AS VARCHAR(40)),

    CAST(c.Phone AS VARCHAR(16))

    FROM Northwind.dbo.Customers c

    CROSS JOIN dbo.Tally t

    WHERE t.N BETWEEN 100 AND 5000 ;

    GO

    Now run the test to insert 400K rows into both a temp table and a table variable with stats time on:

    SET NOCOUNT ON;

    USE tempdb

    SET STATISTICS TIME OFF ;

    PRINT '------------------------------------------------------------------

    temp table'

    IF OBJECT_ID(N'tempdb..#tbl') IS NOT NULL

    DROP TABLE #tbl ;

    SET STATISTICS TIME ON ;

    CREATE TABLE #tbl

    (

    customerID INT,

    fullname VARCHAR(40),

    phone VARCHAR(16),

    rownumber INT IDENTITY(1, 1)

    NOT NULL

    ) ;

    INSERT INTO #tbl

    (

    customerID,

    fullname,

    phone

    )

    SELECT customerID,

    fullname,

    phone

    FROM dbo.MyList

    ORDER BY customerID ;

    SET STATISTICS TIME OFF ;

    PRINT '

    ------------------------------------------------------------------

    table variable'

    SET STATISTICS TIME ON ;

    DECLARE @tbl TABLE

    (

    customerID INT,

    fullname VARCHAR(40),

    phone VARCHAR(16),

    rownumber INT IDENTITY(1, 1)

    NOT NULL

    ) ;

    INSERT INTO @tbl

    (

    customerID,

    fullname,

    phone

    )

    SELECT customerID,

    fullname,

    phone

    FROM myList

    ORDER BY customerID ;

    SET STATISTICS TIME OFF

    Typical results:

    ------------------------------------------------------------------

    temp table

    SQL Server Execution Times:

    CPU time = 0 ms, elapsed time = 0 ms.

    SQL Server Execution Times:

    CPU time = 0 ms, elapsed time = 0 ms.

    SQL Server Execution Times:

    CPU time = 1388 ms, elapsed time = 2626 ms.

    ------------------------------------------------------------------

    table variable

    SQL Server Execution Times:

    CPU time = 0 ms, elapsed time = 0 ms.

    SQL Server Execution Times:

    CPU time = 1420 ms, elapsed time = 2253 ms.

    Some particulars:

    2.4GHz Dual Core CPU

    SQL 2000 Developer Edition - 8.00.194

    There are no special teachers of virtue, because virtue is taught by the whole community.
    --Plato

  • Bah! SELECT INTO! 😀

    There are no special teachers of virtue, because virtue is taught by the whole community.
    --Plato

  • opc.three (7/23/2011)


    Bah! SELECT INTO! 😀

    😛

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • Jeff, I think you may have finally ended the long run my old [default] habit was on once and for all 😉

    Thanks as always!

    There are no special teachers of virtue, because virtue is taught by the whole community.
    --Plato

  • Would you do me a favor then? Would you run my code example and post the results? I'm always curious as to what any given piece of code does on different machines because of how much simple differences in hardware can make. Thanks, Orlando.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • Jeff Moden (7/23/2011)


    Would you do me a favor then? Would you run my code example and post the results? I'm always curious as to what any given piece of code does on different machines because of how much simple differences in hardware can make. Thanks, Orlando.

    Sure. I did change a couple things in your code:

    1. sys.all_columns (2005+) to master.dbo.syscolumns, required for running on SQL 2000

    2. FullName VARCHAR(8000), Phone VARCHAR(12) changed to FullName VARCHAR(40), Phone VARCHAR(16) on MyList creation, this was done to avoid penalty on table-variable insert for mismatched data types

    --====================================================================

    -- Create 400,000 rows of test data

    -- This is NOT a part of the solution. We're just setting up to

    -- test.

    --====================================================================

    --===== Do this in a nice, safe place that everyone has.

    USE TempDB

    ;

    --===== Conditionally drop the test table to make reruns easier

    IF OBJECT_ID('dbo.MyList','U') IS NOT NULL

    DROP TABLE dbo.MyList

    ;

    IF OBJECT_ID(N'tempdb..#PerfTest') IS NOT NULL

    DROP TABLE #PerfTest;

    GO

    SELECT TOP 400000

    CustomerID = IDENTITY(INT,1,1),

    FullName = CAST(RIGHT(NEWID(),ABS(CHECKSUM(NEWID())) % 34 + 2) AS VARCHAR(40)),

    Phone = SUBSTRING(CAST(NEWID() AS CHAR(36)),11,16)

    INTO dbo.MyList

    FROM master.dbo.syscolumns ac1

    CROSS JOIN master.dbo.syscolumns ac2

    ;

    --====================================================================

    -- Test the Temp Table and Table Variable solutions

    --====================================================================

    PRINT '========== Temp Table Solution ==========';

    SET STATISTICS TIME ON;

    SELECT CustomerID = CAST(customerID AS INT), --Overrides IDENTITY property of CustomerID

    fullname,

    phone,

    RowNumber = IDENTITY(INT,1,1)

    INTO #PerfTest

    FROM dbo.myList

    WHERE customerID > 231

    ORDER BY customerID

    ;

    SET STATISTICS TIME OFF;

    PRINT '========== Table Variable Solution ==========';

    SET STATISTICS TIME ON;

    DECLARE @tbl TABLE

    (

    customerID INT,

    fullname VARCHAR(40),

    phone VARCHAR(16),

    rownumber INT IDENTITY(1, 1)

    NOT NULL

    ) ;

    INSERT INTO @tbl

    (

    customerID,

    fullname,

    phone

    )

    SELECT customerID,

    fullname,

    phone

    FROM myList

    WHERE customerID > 231

    ORDER BY customerID ;

    --SELECT *

    --FROM @tbl ;

    SET STATISTICS TIME OFF;

    Typical results:

    (400000 row(s) affected)

    ========== Temp Table Solution ==========

    SQL Server Execution Times:

    CPU time = 0 ms, elapsed time = 0 ms.

    SQL Server Execution Times:

    CPU time = 1263 ms, elapsed time = 2424 ms.

    (399769 row(s) affected)

    ========== Table Variable Solution ==========

    SQL Server Execution Times:

    CPU time = 0 ms, elapsed time = 0 ms.

    SQL Server Execution Times:

    CPU time = 1358 ms, elapsed time = 3614 ms.

    (399769 row(s) affected)

    Particulars:

    - Dual Core 2.4GHz CPU

    - SQL 2000 Developer Edition

    There are no special teachers of virtue, because virtue is taught by the whole community.
    --Plato

Viewing 15 posts - 1 through 15 (of 16 total)

You must be logged in to reply to this topic. Login to reply