July 22, 2011 at 4:46 pm
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.
July 22, 2011 at 6:35 pm
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
July 22, 2011 at 6:53 pm
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.
July 22, 2011 at 9:57 pm
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
July 22, 2011 at 10:12 pm
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
July 22, 2011 at 10:49 pm
For future reference:
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
July 23, 2011 at 11:06 am
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.
July 23, 2011 at 12:34 pm
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
Change is inevitable... Change for the better is not.
July 23, 2011 at 3:00 pm
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
Change is inevitable... Change for the better is not.
July 23, 2011 at 3:09 pm
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
July 23, 2011 at 3:18 pm
Bah! SELECT INTO! 😀
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
July 23, 2011 at 3:37 pm
opc.three (7/23/2011)
Bah! SELECT INTO! 😀
😛
--Jeff Moden
Change is inevitable... Change for the better is not.
July 23, 2011 at 3:44 pm
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
July 23, 2011 at 5:20 pm
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
Change is inevitable... Change for the better is not.
July 23, 2011 at 6:08 pm
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