quote:

---

Originally posted by nr

I always wonder why people create cursors for things like this.
Table names should be sysname or at least 128 bytes
You might have a look at the information schema views too.

declare @tablename @nvarchar(128) ,
@maxtablename @nvarchar(128) ,
@cmd nvarchar(1000)


select @tablename = '', @maxtablename = max(name) from sysobjects where xtype='u'
while @tablename < @maxtablename
begin
select @tablename = min((name) from sysobjects where xtype='u' and name > @tablename
set @cmd='exec sp_spaceused['+@tablename+']'
insert into #SpaceUsed exec sp_executesql @cmd
end


==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

---

USE Northwind
GO

CREATE TABLE #SpaceUsed (				-- The Actual output sizes of sp_spaceused
							-- Anything else is a waste
			  [name] 	varchar(20)
			, [rows] 	char(11)
			, [reserved] 	varchar(15)
			, [data] 	varchar(15)
			, [index_size] 	varchar(15)
			, [unused] 	varchar(15)
)
GO

DECLARE   @tablename nvarchar(128)
	, @maxtablename nvarchar(128)
	, @cmd nvarchar(1000) 

SET NOCOUNT ON

 SELECT   @tablename = ''
	, @maxtablename = MAX(name) 
   FROM   sysobjects 
  WHERE   xtype='u'

WHILE @tablename < @maxtablename 
	BEGIN
		SELECT @tablename = MIN(name) 
		  FROM sysobjects 
		 WHERE xtype='u' and name > @tablename
	 
		   SET @cmd='exec sp_spaceused['+@tablename+']'
		INSERT INTO #SpaceUsed EXEC sp_executesql @cmd
	END

SELECT * FROM #SpaceUsed

SET NOCOUNT OFF
GO

DROP TABLE #SpaceUSed
GO

quote:

---

Originally posted by ehorn

I believe the singleton always outperforms the cursor.

---

quote:

---

Originally posted by ehorn

A CURSOR requires an OPEN, FETCH, and CLOSE to retrieve one row

---

quote:

---

Originally posted by ehorn

Sound like a lot of overhead to me.

It seems logical that if there is more overhead in retrieving one row, than there will be more overhead in retreiving pages of data.

---

quote:

---

You don't consider a million singleton selects (ok 250,000) alot of overhead?

---

quote:

---

Originally posted by ehorn

quote:

---

You don't consider a million singleton selects (ok 250,000) alot of overhead?

---

OK.. OK...

More overhead than 250,000 cursor fetches?

Time to bust out the showplan.

---

quote:

---

Originally posted by Stoad

LOL, ehorn... don't be hypnotized in the cursor theme. One sample for you:
suppose you have two tables. Both these tables store coordinates (x, y) of
points. And in the 1st table there are say 5 mln. rows, and in the 2nd - about
say 500 rows.

Now you need to find for each point from the small table the nearest to it point
from the huge table...

You can't even imagine how many times a cursor solution will be faster than
ANY non-cursor one!!

OK. Don't be lazy and just check it out.

---

quote:

---

How many singleton selects have to be performed until they get outweighed(out-performed) by a cursor?

---

quote:

---

Originally posted by Stoad

LOL, ehorn... don't be hypnotized in the cursor theme. One sample for you:
suppose you have two tables. Both these tables store coordinates (x, y) of
points. And in the 1st table there are say 5 mln. rows, and in the 2nd - about
say 500 rows.

Now you need to find for each point from the small table the nearest to it point
from the huge table...

You can't even imagine how many times a cursor solution will be faster than
ANY non-cursor one!!

OK. Don't be lazy and just check it out.

---

/*----------------------------------------------------------
 Sample Data
---------------------------------------------------------*/

CREATE TABLE TestData (n INT IDENTITY(0,1) PRIMARY KEY, data CHAR(400) NOT NULL )
GO

SET NOCOUNT ON
INSERT INTO TestData (data) SELECT REPLICATE('BLAH', 100)
FROM Numbers
WHERE n < 10000
GO

CREATE TABLE OutData (n INT, data CHAR(400) )
GO

/*----------------------------------------------------------
 Cursor Insert
---------------------------------------------------------*/
SET NOCOUNT ON

DECLARE @MaxRows int
SET @MaxRows = 10000

DECLARE @n int,@data char(400)

DECLARE number_cursor CURSOR --FAST_FORWARD
FOR
SELECT n FROM Numbers WHERE n <= @MaxRows

OPEN number_cursor

FETCH NEXT FROM number_cursor INTO @n  

WHILE @@FETCH_STATUS = 0
BEGIN
	INSERT INTO OutData 
	SELECT n,data FROM TestData WHERE n = @n

	FETCH NEXT FROM number_cursor INTO @n
END

CLOSE number_cursor
DEALLOCATE number_cursor
GO

DELETE OutData
GO


/*----------------------------------------------------------
 Singleton Insert
---------------------------------------------------------*/
SET NOCOUNT ON

DECLARE @MaxRows int
SET @MaxRows = 10000

DECLARE @n INT,@data CHAR(400)
SET @n = 0
WHILE @n <= @MaxRows
BEGIN
	INSERT INTO OutData 
	SELECT n,data FROM TestData WHERE n = @n

	SET @n = @n+1
END
GO

DROP TABLE TestData
DROP TABLE OutData

/*
----------------------------------------------------------------------------------------------------------------------------------------
			Estimate Rows	 Estimate IO	Estimate CPU	  AvgRowSize	TTLSubTreCost	    Duration
----------------------------------------------------------------------------------------------------------------------------------------
Cursor			30003.3010000	0.1787737479	0.1101602000	438.0000000000	0.3121736240  		1:03     
----------------------------------------------------------------------------------------------------------------------------------------

  |--Dynamic Cursor(number_cursor, Optimistic)
       |--Fetch Query(Fetch Query)
       |    |--Table Scan(OBJECT:([Common].[dbo].[Numbers]), WHERE:([Numbers].[n]<=10000))
       |--Refresh Query(Refresh Query)
 OPEN number_cursor
 FETCH NEXT FROM number_cursor INTO @n
 WHILE @@FETCH_STATUS = 0
   BEGIN   INSERT INTO OutData    SELECT n,data FROM TestData WHERE n = @n
       |--Table Insert(OBJECT:([Common].[dbo].[OutData]), SET:([OutData].[data]=[TestData].[data], [OutData].[n]=[TestData].[n]))
            |--Clustered Index Seek(OBJECT:([Common].[dbo].[TestData].[PK__TestData__50FB042B]), SEEK:([TestData].[n]=[@n]) ORDERED FORWARD)
   FETCH NEXT FROM number_cursor INTO @n
 END    CLOSE number_cursor
 DEALLOCATE number_cursor

----------------------------------------------------------------------------------------------------------------------------------------
----------------------------------------------------------------------------------------------------------------------------------------
----------------------------------------------------------------------------------------------------------------------------------------
			Estimate Rows	 Estimate IO	Estimate CPU	  AvgRowSize	TTLSubTreCost	    Duration
----------------------------------------------------------------------------------------------------------------------------------------
Singleton 		3.0000000000	0.0167507979	0.0000806000	415.0000000000	0.0400710940	      1:01
----------------------------------------------------------------------------------------------------------------------------------------

DECLARE @n INT,@data CHAR(400)  SET @n = 0
 WHILE @n <= 10000
   BEGIN   INSERT INTO OutData    SELECT n,data FROM TestData WHERE n = @n     SET
       |--Table Insert(OBJECT:([Common].[dbo].[OutData]), SET:([OutData].[data]=[TestData].[data], [OutData].[n]=[TestData].[n]))
            |--Clustered Index Seek(OBJECT:([Common].[dbo].[TestData].[PK__TestData__50FB042B]), SEEK:([TestData].[n]=[@n]) ORDERED FORWARD)
   @n = @n+1
 END

*/