Counter Table (table of numbers) Setter-Upper for SQL Server 2000
Counter Table (table of numbers) Setter-Upper for SQL Server 2000
By Jesse Roberge - YeshuaAgapao@gmail.com
CounterSmall - completely fills a single data page (two logical reads per seek or scan). 620 numbers.
Counter - completely fills a two-level clustered index (two logical reads per seek). 384,400 numbers.
CounterBig - completely fills a three-level clustered index (three logical reads per seek). 238,328,000 numbers.
CounterSmall and Counter populate in 2-4 seconds, including statistics and index rebuilds.
CounterBig is commented as it usually isn't needed. It takes a bit to populate this and pigs a lot of space. Un-comment it only if you need it.
Copyright:
Licensed under the L-GPL - a weak copyleft license - you are permitted to use this as a component of a proprietary database and call this from proprietary software.
Copyleft lets you do anything you want except plagarize, conceal the source, or prohibit copying & re-distribution of this script/proc.
This program is free software: you can redistribute it and/or modify
it under the terms of the GNU Lesser General Public License as
published by the Free Software Foundation, either version 3 of the
License, or (at your option) any later version.
This program is distributed in the hope that it will be useful,
but WITHOUT ANY WARRANTY; without even the implied warranty of
MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
GNU Lesser General Public License for more details.
see <http://www.fsf.org/licensing/licenses/lgpl.html> for the license text.
SET ANSI_NULLS ON
SET ANSI_PADDING ON
SET ANSI_WARNINGS ON
SET ARITHABORT ON
SET CONCAT_NULL_YIELDS_NULL ON
SET NUMERIC_ROUNDABORT OFF
SET QUOTED_IDENTIFIER ON
SET NOCOUNT ON
GO
--*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=
/*
Counter Table (table of numbers) Setter-Upper for SQL Server 2000
By Jesse Roberge - YeshuaAgapao@gmail.com
CounterSmall - completely fills a single data page (two logical reads per seek or scan). 620 numbers.
Counter - completely fills a two-level clustered index (two logical reads per seek). 384,400 numbers.
CounterBig - completely fills a three-level clustered index (three logical reads per seek). 238,328,000 numbers.
CounterSmall and Counter populate in 2-4 seconds, including statistics and index rebuilds.
CounterBig is commented as it usually isn't needed. It takes a bit to populate this and pigs a lot of space. Un-comment it only if you need it.
Copyright:
Licensed under the L-GPL - a weak copyleft license - you are permitted to use this as a component of a proprietary database and call this from proprietary software.
Copyleft lets you do anything you want except plagarize, conceal the source, or prohibit copying & re-distribution of this script/proc.
This program is free software: you can redistribute it and/or modify
it under the terms of the GNU Lesser General Public License as
published by the Free Software Foundation, either version 3 of the
License, or (at your option) any later version.
This program is distributed in the hope that it will be useful,
but WITHOUT ANY WARRANTY; without even the implied warranty of
MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
GNU Lesser General Public License for more details.
see <http://www.fsf.org/licensing/licenses/lgpl.html> for the license text.
*/--*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=
--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
*/
--*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=