Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 


 
     
Recent Posts
   
Popular Topics
Home   
Search
    Members    Calendar    Who's On

Home » Article Discussions » Article Discussions by Author » Discuss Content Posted by Jeff Moden » Passing Parameters as (almost) 1, 2, and 3...
Add to briefcase
59 posts, Page 2 of 6
««12345»»»

Passing Parameters as (almost) 1, 2, and 3 Dimensional Arrays Expand / Collapse
Rate Topic
Display Mode
Topic Options
Author
Message
Anipaul
Posted Saturday, May 24, 2008 6:52 AM
SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Today @ 12:47 AM
Points: 4,322, Visits: 1,264
This last article, if I had to guess, took me somewhere between 12 and 16 hours to write. A more prolific writer could probably have banged it out in an hour or two and still done a heck of a good job.


Still if you are not profilic writic then we should change the profilic writer's definition................;)


Post #506229
Jeff Moden
Posted Saturday, May 24, 2008 4:17 PM


One Orange Chip

One Orange ChipOne Orange ChipOne Orange ChipOne Orange ChipOne Orange ChipOne Orange ChipOne Orange ChipOne Orange ChipOne Orange ChipOne Orange ChipOne Orange ChipOne Orange Chip

Group: General Forum Members
Last Login: Today @ 11:50 AM
Points: 28,379, Visits: 22,169
Matt Miller (5/23/2008)
The funny clothes I can deal with. The tin foil hat on the other hand...:)


Heh! C'mon... I don't wear the hat in public... and no one can see the lead cods piece...

--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."

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
Post #506281
Matt Miller (#4)
Posted Sunday, May 25, 2008 2:28 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 6:05 PM
Points: 6,652, Visits: 11,718
Jeff Moden (5/24/2008)
Matt Miller (5/23/2008)
The funny clothes I can deal with. The tin foil hat on the other hand...:)


Heh! C'mon... I don't wear the hat in public... and no one can see the lead cods piece...


now there goes a visual I didn't need. I am going to have to double myt alcohol intake tomorrow just to flush that right out...

----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
Post #506345
YeshuaAgapao
Posted Thursday, May 29, 2008 3:53 PM


Say Hey Kid

Say Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey Kid

Group: General Forum Members
Last Login: Tuesday, June 21, 2011 5:26 PM
Points: 701, Visits: 202
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. SQL Server 2000 can fit 620 numbers per page clustered.

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). Never needed the 'big' version yet - a fully packed 3-level clustered index with 240,641,848 numbers (238,328,000 for SQL2000).
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. @MaxPositive and @ClusteredRowsPerPage are the hard-coded controlling parameters.

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. The big version gracefully can handle more than hundreds of thousands of characters because it splices into 8000 character blocks. More code, no longer an inline table-valued function (inline table-valued functions are processed as derived tables / views behind the scenes and are much faster), but it is faster than VarChar(max) and works in SQL Server 2000 (if the string input is text instead of VarChar(max)) and never uses more than 8000 numbers.

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.


Post #508732
Jeff Moden
Posted Friday, May 30, 2008 10:43 PM


One Orange Chip

One Orange ChipOne Orange ChipOne Orange ChipOne Orange ChipOne Orange ChipOne Orange ChipOne Orange ChipOne Orange ChipOne Orange ChipOne Orange ChipOne Orange ChipOne Orange Chip

Group: General Forum Members
Last Login: Today @ 11:50 AM
Points: 28,379, Visits: 22,169
Howdy folks,

Someone who wishes to remain anonymous, send me an email asking the following questions related to this article. I've not worked with SQL Server 2008 (shoot, I'm just getting up to speed on 2k5) so I can't answer them... sure could use some help if you have a minute. Here's the questions I was asked...

1. (concerning 2k8) One feature, I believe I saw, was the ability to pass multidimensional arrays in the procedures. Is it as easy as setting a “@myTable As table” for a passed in parameter to a stored proc?

2. ... since table variables are already used in stored procedures, is this a feature more valuable in Visual Studio and should be discussed in that arena?

3. As a side note: I have heard Microsoft purchased Dundas reports and believe have seen it is bundled in Reporting Services 2008. If I begin working with the community releases of SQL 2008, will those reports be available already?

Thanks for any information you may have on these questions.

--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."

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
Post #509452
Tom Garth
Posted Saturday, May 31, 2008 1:49 PM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Friday, February 04, 2011 7:20 AM
Points: 977, Visits: 1,499
Jeff Moden (5/30/2008)
Howdy folks,

Someone who wishes to remain anonymous, send me an email asking the following questions related to this article. I've not worked with SQL Server 2008 (shoot, I'm just getting up to speed on 2k5) so I can't answer them... sure could use some help if you have a minute. Here's the questions I was asked...

1. (concerning 2k8) One feature, I believe I saw, was the ability to pass multidimensional arrays in the procedures. Is it as easy as setting a “@myTable As table” for a passed in parameter to a stored proc?

2. ... since table variables are already used in stored procedures, is this a feature more valuable in Visual Studio and should be discussed in that arena?

3. As a side note: I have heard Microsoft purchased Dundas reports and believe have seen it is bundled in Reporting Services 2008. If I begin working with the community releases of SQL 2008, will those reports be available already?

Thanks for any information you may have on these questions.


Jeff,

I remember reading about tables as parameters, and my perhaps faulty memory was that they could only be used within T-SQL, not from external calls.

I did a little googling today to try to update my memory, and this TechNet article Table-Valued Parameters (Database Engine) states:
You can create and execute Transact-SQL routines with table-valued parameters, and call them from Transact-SQL code, managed and native clients in any managed language.


Then I found this MSDN Library Item..New Features in SQL Server (ADO.NET) with some sample .NET code.

The coding for creating the parameter is not as straight forward as you would guess, so for sub or nested procedures the temp table will still be easier to use in most cases.

Tom Garth
Vertical Solutions

"There are three kinds of men. The one that learns by reading. The few who learn by observation. The rest of them have to pee on the electric fence for themselves." -- Will Rogers
Post #509503
Jeff Moden
Posted Saturday, May 31, 2008 4:06 PM


One Orange Chip

One Orange ChipOne Orange ChipOne Orange ChipOne Orange ChipOne Orange ChipOne Orange ChipOne Orange ChipOne Orange ChipOne Orange ChipOne Orange ChipOne Orange ChipOne Orange Chip

Group: General Forum Members
Last Login: Today @ 11:50 AM
Points: 28,379, Visits: 22,169
Thanks for the links and info, Tom... I'll take a look.

--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."

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
Post #509526
Gary Noter
Posted Thursday, July 24, 2008 1:01 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, June 02, 2011 4:08 PM
Points: 12, Visits: 52
This example code is a TOTAL waste of my time.

No, wait, if I implement this code, then can't waste my time with writing and waiting for WHILE loops to execute and bill my customers for more time.

Therefore, it is a waste of getting *MORE* billable time (or actually maintaining it)!

Shhh, I'll implement this code and I'll still bill them the time I used to spend waiting for the WHILE loop to execute. I'll call this the "WHILE Loop-hole" in my Billing Secrets blackbook !!!
Post #540489
Jeff Moden
Posted Thursday, July 24, 2008 5:40 PM


One Orange Chip

One Orange ChipOne Orange ChipOne Orange ChipOne Orange ChipOne Orange ChipOne Orange ChipOne Orange ChipOne Orange ChipOne Orange ChipOne Orange ChipOne Orange ChipOne Orange Chip

Group: General Forum Members
Last Login: Today @ 11:50 AM
Points: 28,379, Visits: 22,169
Gary Noter (7/24/2008)
This example code is a TOTAL waste of my time.

No, wait, if I implement this code, then can't waste my time with writing and waiting for WHILE loops to execute and bill my customers for more time.

Therefore, it is a waste of getting *MORE* billable time (or actually maintaining it)!

Shhh, I'll implement this code and I'll still bill them the time I used to spend waiting for the WHILE loop to execute. I'll call this the "WHILE Loop-hole" in my Billing Secrets blackbook !!!


It's totally obvious that you didn't actually read the article. The WHILE loops were an example of how NOT to do it. ;)

--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."

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
Post #540621
Gary Noter
Posted Monday, July 28, 2008 10:40 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, June 02, 2011 4:08 PM
Points: 12, Visits: 52
Jeff Moden (7/24/2008)
Gary Noter (7/24/2008)
This example code is a TOTAL waste of my time.

No, wait, if I implement this code, then can't waste my time with writing and waiting for WHILE loops to execute and bill my customers for more time.

Therefore, it is a waste of getting *MORE* billable time (or actually maintaining it)!

Shhh, I'll implement this code and I'll still bill them the time I used to spend waiting for the WHILE loop to execute. I'll call this the "WHILE Loop-hole" in my Billing Secrets blackbook !!!


It's totally obvious that you didn't actually read the article. The WHILE loops were an example of how NOT to do it. ;)


Uhmm, Jeff, moi post was humor; or did I miss that you didn't miss the humor, or did you miss the humor (or are we now in a WHILE loop [per se]?

Cuz I (now) do use yer Tally table stuff & passing parameters, etc. Thx!!
Post #542043
« Prev Topic | Next Topic »

Add to briefcase
59 posts, Page 2 of 6
««12345»»»

Permissions Expand / Collapse

 
 
Copyright © 2002-2012 Simple Talk Publishing. All Rights Reserved. Privacy Policy. Terms of Use. Report Abuse.