Parsing 1D and 2D Delimited Strings (Arrays) in SQL Server 2005 and 2000
Friday, May 30th, 2008I’m re-posting a post I put at SQLServerCentral.com
********************************************************************************
This 2D stuff is excellent for normalizing 1NF (first normal form) violations like ‘123^12|456^45′|789^12|945^34′
2D array parsing without table variables or temp tables! It is using Itzik Ben-Gan’s parsing algorithm that relies on a table of numbers (counter / tally / nums). My version of the 2D enhancement uses CROSS APPLY so it doesn’t work in SQL Server 2000.
2D ‘Table’ version - outputs vertical-ized data only; faster but not very useful on 2D data:
--Normal VarChar version CREATE FUNCTION dbo.fn_DelimitToTable_2D( @String VarChar(8000), @Delimiter1 VarChar(1), @Delimiter2 VarChar(1) ) RETURNS TABLE AS RETURN ( SELECT Counter2nd.Value AS Value FROM ( SELECT SUBSTRING(@String+@Delimiter1, PK_CountID, CHARINDEX(@Delimiter1, @String+@Delimiter1, PK_CountID)-PK_CountID) AS Value FROM dbo.counter WHERE PK_CountID >0 AND PK_CountID<LEN(@String)+LEN(@Delimiter1) AND SubString(@Delimiter1 + @String + @Delimiter1, PK_CountID, 1)=@Delimiter1 ) AS Counter1st CROSS APPLY ( SELECT SUBSTRING(Counter1st.Value+@Delimiter2, PK_CountID, CHARINDEX(@Delimiter2, Counter1st.Value+@Delimiter2, PK_CountID)-PK_CountID) AS Value FROM dbo.counter WHERE PK_CountID >0 AND PK_CountID<LEN(Counter1st.Value)+LEN(@Delimiter2) AND SubString(@Delimiter2 + Counter1st.Value + @Delimiter2, PK_CountID, 1)=@Delimiter2 ) AS Counter2nd ) GO –Integer casting version when output is used to join to integer PK/FK columns. CREATE FUNCTION dbo.fn_DelimitToIntTable_2D ( @String VarChar(8000), @Delimiter1 VarChar(1), @Delimiter2 VarChar(1) ) RETURNS TABLE AS RETURN ( SELECT CONVERT(int, Counter2nd.Value) AS PK_IntID FROM ( SELECT SUBSTRING(@String+@Delimiter1, PK_CountID, CHARINDEX(@Delimiter1, @String+@Delimiter1, PK_CountID)-PK_CountID) AS Value FROM dbo.counter WHERE PK_CountID >0 AND PK_CountID<LEN(@String)+LEN(@Delimiter1) AND SubString(@Delimiter1 + @String + @Delimiter1, PK_CountID, 1)=@Delimiter1 ) AS Counter1st CROSS APPLY ( SELECT SUBSTRING(Counter1st.Value+@Delimiter2, PK_CountID, CHARINDEX(@Delimiter2, Counter1st.Value+@Delimiter2, PK_CountID)-PK_CountID) AS Value FROM dbo.counter WHERE PK_CountID >0 AND PK_CountID<LEN(Counter1st.Value)+LEN(@Delimiter2) AND SubString(@Delimiter2 + Counter1st.Value + @Delimiter2, PK_CountID, 1)=@Delimiter2 ) AS Counter2nd ) GO
‘Array’ version - outputs indexer also (more overhead):
--Normal VarChar version CREATE FUNCTION dbo.fn_DelimitToArray_2D( @String VarChar(8000), @Delimiter1 VarChar(1), @Delimiter2 VarChar(1) ) RETURNS TABLE AS RETURN ( SELECT Counter1st.Pos AS RowPos, Counter2nd.Pos AS ColPos, Counter2nd.Value AS Value FROM ( SELECT PK_CountID - LEN(REPLACE(LEFT(@String, PK_CountID-1), @Delimiter1, ‘’)) AS Pos, SUBSTRING(@String+@Delimiter1, PK_CountID, CHARINDEX(@Delimiter1, @String+@Delimiter1, PK_CountID)-PK_CountID) AS Value FROM dbo.counter WHERE PK_CountID >0 AND PK_CountID<LEN(@String)+LEN(@Delimiter1) AND SubString(@Delimiter1 + @String + @Delimiter1, PK_CountID, 1)=@Delimiter1 ) AS Counter1st CROSS APPLY ( SELECT PK_CountID - LEN(REPLACE(LEFT(Counter1st.Value, PK_CountID-1), @Delimiter2, ‘’)) AS Pos, SUBSTRING(Counter1st.Value+@Delimiter2, PK_CountID, CHARINDEX(@Delimiter2, Counter1st.Value+@Delimiter2, PK_CountID)-PK_CountID) AS Value FROM dbo.counter WHERE PK_CountID >0 AND PK_CountID<LEN(Counter1st.Value)+LEN(@Delimiter2) AND SubString(@Delimiter2 + Counter1st.Value + @Delimiter2, PK_CountID, 1)=@Delimiter2 ) AS Counter2nd ) GO –Integer casting version when output is used to join to integer PK/FK columns. CREATE FUNCTION dbo.fn_DelimitToIntArray_2D ( @String VarChar(8000), @Delimiter1 VarChar(1), @Delimiter2 VarChar(1) ) RETURNS TABLE AS RETURN ( SELECT Counter1st.Pos AS RowPos, Counter2nd.Pos AS ColPos, CONVERT(int, Counter2nd.value) AS PK_IntID FROM ( SELECT PK_CountID - LEN(REPLACE(LEFT(@String, PK_CountID-1), @Delimiter1, ‘’)) AS Pos, SUBSTRING(@String+@Delimiter1, PK_CountID, CHARINDEX(@Delimiter1, @String+@Delimiter1, PK_CountID)-PK_CountID) AS value FROM dbo.counter WHERE PK_CountID >0 AND PK_CountID<LEN(@String)+LEN(@Delimiter1) AND SubString(@Delimiter1 + @String + @Delimiter1, PK_CountID, 1)=@Delimiter1 ) AS Counter1st CROSS APPLY ( SELECT PK_CountID - LEN(REPLACE(LEFT(Counter1st.value, PK_CountID-1), @Delimiter2, ‘’)) AS Pos, SUBSTRING(Counter1st.value+@Delimiter2, PK_CountID, CHARINDEX(@Delimiter2, Counter1st.Value+@Delimiter2, PK_CountID)-PK_CountID) AS value FROM dbo.counter WHERE PK_CountID >0 AND PK_CountID<LEN(Counter1st.value)+LEN(@Delimiter2) AND SubString(@Delimiter2 + Counter1st.value + @Delimiter2, PK_CountID, 1)=@Delimiter2 ) AS Counter2nd ) GO
For those of you who don’t have Itzik Ben-Gan’s Inside SQL Server 2005 T-SQL books or been to any of his conference sessions (the books are a lot cheaper), here are 1D versions:
‘Table’ version - ordinal postion stripped out for speed; Great for stored-procedure-izing IN() clauses - WHERE id IN (1,2,3,4):
--Normal VarChar version CREATE FUNCTION dbo.fn_DelimitToTable( @String VarChar(8000), @Delimiter VarChar(1) ) RETURNS TABLE AS RETURN ( SELECT SUBSTRING(@String+@Delimiter, PK_CountID, CHARINDEX(@Delimiter, @String+@Delimiter, PK_CountID)-PK_CountID) AS Value FROM dbo.counter WHERE PK_CountID >0 AND PK_CountID<LEN(@String)+LEN(@Delimiter) AND SubString(@Delimiter + @String + @Delimiter, PK_CountID, 1)=@Delimiter ) GO –Integer casting version when output is used to join to integer PK/FK columns. CREATE FUNCTION dbo.fn_DelimitToIntTable ( @String VarChar(8000), @Delimiter VarChar(1) ) RETURNS TABLE AS RETURN ( SELECT CONVERT(int, SUBSTRING(@String+@Delimiter, PK_CountID, CHARINDEX(@Delimiter, @String+@Delimiter, PK_CountID)-PK_CountID)) AS PK_IntID FROM dbo.counter WHERE PK_CountID >0 AND PK_CountID<LEN(@String)+LEN(@Delimiter) AND SubString(@Delimiter + @String + @Delimiter, PK_CountID, 1)=@Delimiter ) GO
‘Array’ version - with position indexer - good for index change scripts where column-order matters:
--Normal VarChar version CREATE FUNCTION dbo.fn_DelimitToArray( @String VarChar(8000), @Delimiter VarChar(1) ) RETURNS TABLE AS RETURN ( SELECT PK_CountID - LEN(REPLACE(LEFT(@String, PK_CountID-1), @Delimiter, ‘’)) AS Pos, SUBSTRING(@String+@Delimiter, PK_CountID, CHARINDEX(@Delimiter, @String+@Delimiter, PK_CountID)-PK_CountID) AS Value FROM dbo.counter WHERE PK_CountID >0 AND PK_CountID<LEN(@String)+LEN(@Delimiter) AND SubString(@Delimiter + @String + @Delimiter, PK_CountID, 1)=@Delimiter ) GO –Integer casting version when output is used to join to integer PK/FK columns. CREATE FUNCTION dbo.fn_DelimitToIntArray ( @String VarChar(8000), @Delimiter VarChar(1) ) RETURNS TABLE AS RETURN ( SELECT PK_CountID - LEN(REPLACE(LEFT(@String, PK_CountID-1), @Delimiter, ‘’)) AS Pos, CONVERT(int, SUBSTRING(@String+@Delimiter, PK_CountID, CHARINDEX(@Delimiter, @String+@Delimiter, PK_CountID)-PK_CountID)) AS PK_IntID FROM dbo.counter WHERE PK_CountID >0 AND PK_CountID<LEN(@String)+LEN(@Delimiter) AND SubString(@Delimiter + @String + @Delimiter, PK_CountID, 1)=@Delimiter )
As for logical reads on the nums / tally / counter table:
SQL server 2005 can fit 622 numbers per page if it is clustered. That drops to 299 if it is a heap.
1 I/O per hit guaranteed: 299-number heap (seek or scan; only tested in 2005)
2 I/Os per hit guaranteed (seek or scan): 622 number clustered (620 for 2000)
Fully packed 2-level clustered index for a 2 I/O minimum per seek: 386,884 numbers (384,400 for 2000)
Make sure you use a 100% fill facter (the data shouldn’t ever change), and after populating the tables with data, you do a rebuild:
ALTER INDEX ALL ON Counter REBUILD WITH (FillFactor=100) for SQL Server 2005
DBCC DBREINDEX (Counter,’PK_C_IX__Counter__CountID’,100) for SQL Server 2000
I usually use both a ’small’ version and a ’standard’ version of the table of numbers (counter / nums / tally).
Here is my counter table building script for SQL Server 2005 and 2000; it runs in 4 seconds and allows or having a portion of your numbers being negative.
1-Level, 2-Level, and 3-Level (commented) Counter / Tally / Nums table builder SQL Server 2005:
--*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*= --DDL --*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*= SET NOCOUNT ON --*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*= IF EXISTS (SELECT * FROM sys.tables WHERE name='CounterSmall' AND schema_id=1) DROP TABLE dbo.CounterSmall IF EXISTS (SELECT * FROM sys.tables WHERE name='Counter' AND schema_id=1) DROP TABLE dbo.Counter --IF EXISTS (SELECT * FROM sys.tables WHERE name='CounterBig' AND schema_id=1) DROP TABLE dbo.CounterBig GO --*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*= CREATE TABLE dbo.CounterSmall (PK_CountID int NOT NULL, CONSTRAINT PK_C_IX__CounterSmall__CountID PRIMARY KEY CLUSTERED (PK_CountID) WITH FILLFACTOR=100 ) CREATE TABLE dbo.Counter ( PK_CountID int NOT NULL, CONSTRAINT PK_C_IX__Counter__CountID PRIMARY KEY CLUSTERED (PK_CountID) WITH FILLFACTOR=100 ) /* CREATE TABLE dbo.CounterBig ( PK_CountID int NOT NULL, CONSTRAINT PK_C_IX__CounterBig__CountID PRIMARY KEY CLUSTERED (PK_CountID) WITH FILLFACTOR=100 ) */ GO –*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*= –Counter SQL 2005 –*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*= DECLARE @Power int DECLARE @HeapRowsPerPage int DECLARE @ClusteredRowsPerPage int DECLARE @MaxRows int DECLARE @MaxPositive int DECLARE @MaxNegative int DECLARE @OldMaxNegative int SET @ClusteredRowsPerPage=622 SET @HeapRowsPerPage=299 SET @MaxPositive=621 –*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*= SET @MaxRows=@ClusteredRowsPerPage SET @MaxPositive=@MaxPositive-1 SET @OldMaxNegative=0 SET @MaxNegative=@MaxRows-@MaxPositive-@OldMaxNegative SET @Power=1 PRINT ‘CounterSmall: ‘ + CONVERT(VarChar(10), @MaxNegative*-1+1) + ‘ to ‘ + CONVERT(VarChar(10), @MaxPositive) + ‘ - ‘ + CONVERT(VarChar(10), @MaxRows) + ‘ Rows - 1-Level Clustered Index’ –SELECT @MaxNegative AS MaxNegative, @MaxPositive AS MaxPositive, @OldMaxNegative AS OldMaxNegative, @Power AS Power, @MaxRows AS MaxRows TRUNCATE TABLE CounterSmall BEGIN TRANSACTION /* INSERT INTO CounterSmall WITH (TABLOCKX) (PK_CountID) SELECT PK_CountID-@MaxNegative FROM dbo.fn_Numbers(@MaxRows) */ INSERT INTO CounterSmall WITH (TABLOCKX) (PK_CountID) VALUES (1-@MaxNegative) WHILE @Power<=@MaxRows BEGIN INSERT INTO CounterSmall WITH (TABLOCKX) (PK_CountID) SELECT @Power+PK_CountID FROM CounterSmall WHERE @Power+PK_CountID<=@MaxPositive SET @Power=@Power*2 END COMMIT ALTER INDEX ALL ON CounterSmall REBUILD WITH (FillFactor=100) UPDATE STATISTICS CounterSmall WITH FULLSCAN –SELECT * FROM CounterSmall –*=*=*=*=*=*=*=*=*=*= SET @Power=@ClusteredRowsPerPage SET @MaxRows=@Power*@ClusteredRowsPerPage SET @OldMaxNegative=@MaxNegative+@OldMaxNegative SET @MaxPositive=(@MaxPositive+1)*@ClusteredRowsPerPage SET @MaxNegative=@MaxRows-@MaxPositive-@OldMaxNegative PRINT ‘Counter: ‘ + CONVERT(VarChar(10), @MaxNegative*-1-@OldMaxNegative+1) + ‘ to ‘ + CONVERT(VarChar(10), @MaxPositive) + ‘ - ‘ + CONVERT(VarChar(10), @MaxRows) + ‘ Rows - 2-Level Clustered Index’ –SELECT @MaxNegative AS MaxNegative, @MaxPositive AS MaxPositive, @OldMaxNegative AS OldMaxNegative, @Power AS Power, @MaxRows AS MaxRows TRUNCATE TABLE Counter BEGIN TRANSACTION INSERT INTO Counter WITH (TABLOCKX) (PK_CountID) SELECT PK_CountID-@MaxNegative FROM CounterSmall WHILE @Power<=@MaxRows BEGIN INSERT INTO Counter WITH (TABLOCKX) (PK_CountID) SELECT @Power+PK_CountID FROM Counter WHERE @Power+PK_CountID<=@MaxPositive SET @Power=@Power*2 END COMMIT ALTER INDEX ALL ON Counter REBUILD WITH (FillFactor=100) UPDATE STATISTICS Counter WITH FULLSCAN –SELECT * FROM Counter ORDER BY PK_CountID –*=*=*=*=*=*=*=*=*=*= /* SET @Power=@ClusteredRowsPerPage*@ClusteredRowsPerPage SET @MaxRows=@Power*(@ClusteredRowsPerPage-2) SET @OldMaxNegative=@MaxNegative+@OldMaxNegative SET @MaxPositive=(@MaxPositive+1)*@ClusteredRowsPerPage SET @MaxNegative=@MaxRows-@MaxPositive-@OldMaxNegative PRINT ‘CounterBig: ‘ + CONVERT(VarChar(10), @MaxNegative*-1-@OldMaxNegative+1) + ‘ to ‘ + CONVERT(VarChar(10), @MaxPositive) + ‘ - ‘ + CONVERT(VarChar(10), @MaxRows) + ‘ Rows - 3-Level Clustered Index’ –SELECT @MaxNegative AS MaxNegative, @MaxPositive AS MaxPositive, @OldMaxNegative AS OldMaxNegative, @Power AS Power, @MaxRows AS MaxRows TRUNCATE TABLE CounterBig UPDATE STATISTICS CounterBig WITH FULLSCAN, NORECOMPUTE BEGIN TRANSACTION INSERT INTO CounterBig WITH (TABLOCKX) (PK_CountID) SELECT PK_CountID-@MaxNegative FROM Counter WHILE @Power<=@MaxRows BEGIN INSERT INTO CounterBig WITH (TABLOCKX) (PK_CountID) SELECT @Power+PK_CountID FROM CounterBig WHERE @Power+PK_CountID<=@MaxPositive SET @Power=@Power*2 END COMMIT ALTER INDEX ALL ON CounterBig REBUILD WITH (FillFactor=100) UPDATE STATISTICS CounterBig WITH FULLSCAN –SELECT * FROM CounterBig ORDER BY PK_CountID */ –*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*= SELECT * FROM sys.dm_db_index_physical_stats (DB_ID(), OBJECT_ID(’CounterSmall’), NULL, NULL, ‘DETAILED’) SELECT * FROM sys.dm_db_index_physical_stats (DB_ID(), OBJECT_ID(’Counter’), NULL, NULL, ‘DETAILED’) –SELECT * FROM sys.dm_db_index_physical_stats (DB_ID(), OBJECT_ID(’CounterBig’), NULL, NULL, ‘DETAILED’) –*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*= GO
1-Level, 2-Level, and 3-Level (commented) Counter / Tally / Nums table builder SQL Server 2000:
--*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*= --DDL --*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*= SET NOCOUNT ON --*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*= IF EXISTS (SELECT * FROM sysobjects WHERE name='CounterSmall' AND uid=1 AND xtype='u') DROP TABLE dbo.CounterSmall IF EXISTS (SELECT * FROM sysobjects WHERE name='Counter' AND uid=1 AND xtype='u') DROP TABLE dbo.Counter --IF EXISTS (SELECT * FROM sysobjects WHERE name='CounterBig' AND uid=1 AND xtype='u') DROP TABLE dbo.CounterBig --*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*= CREATE TABLE dbo.CounterSmall (PK_CountID int NOT NULL, CONSTRAINT PK_C_IX__CounterSmall__CountID PRIMARY KEY CLUSTERED (PK_CountID) WITH FILLFACTOR=100 ) CREATE TABLE dbo.Counter ( PK_CountID int NOT NULL, CONSTRAINT PK_C_IX__Counter__CountID PRIMARY KEY CLUSTERED (PK_CountID) WITH FILLFACTOR=100 ) /* CREATE TABLE dbo.CounterBig ( PK_CountID int NOT NULL, CONSTRAINT PK_C_IX__CounterBig__CountID PRIMARY KEY CLUSTERED (PK_CountID) WITH FILLFACTOR=100 ) */ –*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*= –Counter SQL 2000 –*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*= DECLARE @Power int DECLARE @HeapRowsPerPage int DECLARE @ClusteredRowsPerPage int DECLARE @MaxRows int DECLARE @MaxPositive int DECLARE @MaxNegative int DECLARE @OldMaxNegative int SET @ClusteredRowsPerPage=620 SET @HeapRowsPerPage=299 SET @MaxPositive=619 –*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*= SET @MaxRows=@ClusteredRowsPerPage SET @MaxPositive=@MaxPositive-1 SET @OldMaxNegative=0 SET @MaxNegative=@MaxRows-@MaxPositive-@OldMaxNegative SET @Power=1 PRINT ‘CounterSmall: ‘ + CONVERT(VarChar(10), @MaxNegative*-1+1) + ‘ to ‘ + CONVERT(VarChar(10), @MaxPositive) + ‘ - ‘ + CONVERT(VarChar(10), @MaxRows) + ‘ Rows - 1-Level Clustered Index’ –SELECT @MaxNegative AS MaxNegative, @MaxPositive AS MaxPositive, @OldMaxNegative AS OldMaxNegative, @Power AS Power, @MaxRows AS MaxRows TRUNCATE TABLE CounterSmall BEGIN TRANSACTION INSERT INTO CounterSmall WITH (TABLOCKX) (PK_CountID) VALUES (1-@MaxNegative) WHILE @Power<=@MaxRows BEGIN INSERT INTO CounterSmall WITH (TABLOCKX) (PK_CountID) SELECT @Power+PK_CountID FROM CounterSmall WHERE @Power+PK_CountID<=@MaxPositive SET @Power=@Power*2 END COMMIT DBCC DBREINDEX (CounterSmall,’PK_C_IX__CounterSmall__CountID’,100) UPDATE STATISTICS CounterSmall WITH FULLSCAN –SELECT * FROM CounterSmall –*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*= SET @Power=@ClusteredRowsPerPage SET @MaxRows=@Power*@ClusteredRowsPerPage SET @OldMaxNegative=@MaxNegative+@OldMaxNegative SET @MaxPositive=(@MaxPositive+1)*@ClusteredRowsPerPage SET @MaxNegative=@MaxRows-@MaxPositive-@OldMaxNegative PRINT ‘Counter: ‘ + CONVERT(VarChar(10), @MaxNegative*-1-@OldMaxNegative+1) + ‘ to ‘ + CONVERT(VarChar(10), @MaxPositive) + ‘ - ‘ + CONVERT(VarChar(10), @MaxRows) + ‘ Rows - 2-Level Clustered Index’ –SELECT @MaxNegative AS MaxNegative, @MaxPositive AS MaxPositive, @OldMaxNegative AS OldMaxNegative, @Power AS Power, @MaxRows AS MaxRows TRUNCATE TABLE Counter BEGIN TRANSACTION INSERT INTO Counter WITH (TABLOCKX) (PK_CountID) SELECT PK_CountID-@MaxNegative FROM CounterSmall WHILE @Power<=@MaxRows BEGIN INSERT INTO Counter WITH (TABLOCKX) (PK_CountID) SELECT @Power+PK_CountID FROM Counter WHERE @Power+PK_CountID<=@MaxPositive SET @Power=@Power*2 END COMMIT DBCC DBREINDEX (Counter,’PK_C_IX__Counter__CountID’,100) UPDATE STATISTICS Counter WITH FULLSCAN –SELECT * FROM Counter ORDER BY PK_CountID –*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*= /* SET @Power=@ClusteredRowsPerPage*@ClusteredRowsPerPage SET @MaxRows=@Power*(@ClusteredRowsPerPage-2) SET @OldMaxNegative=@MaxNegative+@OldMaxNegative SET @MaxPositive=(@MaxPositive+1)*@ClusteredRowsPerPage SET @MaxNegative=@MaxRows-@MaxPositive-@OldMaxNegative PRINT ‘CounterBig: ‘ + CONVERT(VarChar(10), @MaxNegative*-1-@OldMaxNegative+1) + ‘ to ‘ + CONVERT(VarChar(10), @MaxPositive) + ‘ - ‘ + CONVERT(VarChar(10), @MaxRows) + ‘ Rows - 3-Level Clustered Index’ –SELECT @MaxNegative AS MaxNegative, @MaxPositive AS MaxPositive, @OldMaxNegative AS OldMaxNegative, @Power AS Power, @MaxRows AS MaxRows TRUNCATE TABLE CounterBig UPDATE STATISTICS CounterBig WITH FULLSCAN, NORECOMPUTE BEGIN TRANSACTION INSERT INTO CounterBig WITH (TABLOCKX) (PK_CountID) SELECT PK_CountID-@MaxNegative FROM Counter WHILE @Power<=@MaxRows BEGIN INSERT INTO CounterBig WITH (TABLOCKX) (PK_CountID) SELECT @Power+PK_CountID FROM CounterBig WHERE @Power+PK_CountID<=@MaxPositive SET @Power=@Power*2 END COMMIT DBCC DBREINDEX (Counter,’PK_C_IX__CounterBig__CountID’,100) UPDATE STATISTICS CounterBig WITH FULLSCAN –SELECT * FROM CounterBig ORDER BY PK_CountID */ –*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*= DBCC SHOWCONTIG (CounterSmall) WITH ALL_LEVELS, TABLERESULTS DBCC SHOWCONTIG (Counter) WITH ALL_LEVELS, TABLERESULTS –DBCC SHOWCONTIG (CounterBig) WITH ALL_LEVELS, TABLERESULTS –*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=
I have big versions and two-column versions as well, but the post is already too big.
I have had other uses for a table of numbers, particularly reporting involving date-ranges and you want to show a date-range-block even if there is no data with a date within that date-range block.
