Cursor in Stored Procedure

Question

Cursor in Stored Procedure

Forum Etiquette: How to post Reporting Services problems

[/url]

Forum Etiquette: How to post data/code on a forum to get the best help - by Jeff Moden
[/url]

How to Post Performance Problems - by Gail Shaw
[/url]

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 1

opc.three (4/1/2013)
I use a slightly different method to avoid some of the entitization problems FOR XML brings with it. Technically it is an undocumented method that I know of but I have not had trouble with it since SQL 2000 and I know of some other techniques that leverage it without trouble too:
DECLARE @SQLCmd NVARCHAR(MAX) = N'';
-- instead of using FOR XML just append the conjured string from each row to the same variable
SELECT @SQLCmd += 'EXEC sys.sp_rename ' + --
'@objname = N''' + SCHEMA_NAME(tab.schema_id) + '.' + tab.name + ''', ' + --
'@newname = N''' + tab.name + '_' + CONVERT(VARCHAR(10), GETDATE(), 112) + ''', ' + --
'@objtype = N''OBJECT'';' + -- OBJECT
NCHAR(13) + NCHAR(10)
FROM sys.tables tab;
-- show the entire variable contents as an XML document...no truncation like with PRINT
SELECT @SQLCmd AS [processing-instruction(query)]
FOR XML PATH(''),
TYPE;
-- when ready, uncomment
--EXEC sys.sp_executesql @SQLCmd;

Yea, but I think I'll stick with the FOR XML Version, the following was done against a database with 5147 tables in sys.tables:

---------- opc.three verison ----------

Beginning execution loop

CPU(ms): 73015 Logical Reads: 45463611 Elapsed(ms): 73495 Reads: 3790 Writes: 481191

CPU(ms): 72468 Logical Reads: 45460066 Elapsed(ms): 73294 Reads: 3983 Writes: 483049

CPU(ms): 73015 Logical Reads: 45458216 Elapsed(ms): 73864 Reads: 3886 Writes: 483049

CPU(ms): 72453 Logical Reads: 45458185 Elapsed(ms): 73498 Reads: 4068 Writes: 483050

CPU(ms): 72437 Logical Reads: 45461626 Elapsed(ms): 73262 Reads: 3731 Writes: 483049

Batch execution completed 5 times.

---------- FOR XML PATH Version ----------

Beginning execution loop

CPU(ms): 187 Logical Reads: 79425 Elapsed(ms): 531 Reads: 3477 Writes: 1201

CPU(ms): 187 Logical Reads: 79425 Elapsed(ms): 251 Reads: 3470 Writes: 1201

CPU(ms): 203 Logical Reads: 79425 Elapsed(ms): 414 Reads: 3475 Writes: 1201

CPU(ms): 219 Logical Reads: 79425 Elapsed(ms): 262 Reads: 3466 Writes: 1201

CPU(ms): 172 Logical Reads: 79425 Elapsed(ms): 317 Reads: 3457 Writes: 1201

Batch execution completed 5 times.

Test harness:

PRINT '---------- opc.three verison ----------';

go

set nocount on;

--===== Create the timer and bit-bucket variables and start the timer.

DECLARE @Bitbucket sysname; --< change or add variables needed here to eliminate display distortion

Declare @cpu_ int,

@lreads_ bigint,

@eMsec_ bigint,

@Reads_ bigint,

@Writes_ bigint;

declare @CpuMs int,

@LogRds bigint,

@Elapsed bigint,

@Reads bigint,

@Writes bigint;

dbcc freeproccache with no_infomsgs;

dbcc freesystemcache('ALL') with no_infomsgs;

dbcc dropcleanbuffers with no_infomsgs;

Select

@cpu_ = cpu_time

, @lreads_ = logical_reads

, @eMsec_ = total_elapsed_time

, @Reads_ = reads

, @Writes_ = writes

From

sys.dm_exec_requests

Where

session_id = @@spid;

---

DECLARE @SQLCmd NVARCHAR(MAX) = N'';

-- instead of using FOR XML just append the conjured string from each row to the same variable

SELECT @SQLCmd += 'EXEC sys.sp_rename ' + --

'@objname = N''' + SCHEMA_NAME(tab.schema_id) + '.' + tab.name + ''', ' + --

'@newname = N''' + tab.name + '_' + CONVERT(VARCHAR(10), GETDATE(), 112) + ''', ' + --

'@objtype = N''OBJECT'';' + -- OBJECT

NCHAR(13) + NCHAR(10)

FROM sys.tables tab;

---

Select

@CpuMs = cpu_time - @cpu_

, @LogRds = logical_reads - @lreads_

, @Elapsed = total_elapsed_time - @eMsec_

, @Reads = reads - @Reads_

, @Writes = writes - @Writes_

From

sys.dm_exec_requests

Where

session_id = @@spid;

--===== Display the duration

PRINT 'CPU(ms): ' + right(' ' + cast(@CpuMs as varchar(10)),10) +

' Logical Reads: ' + right(' ' + cast(@LogRds as varchar(10)),10) +

' Elapsed(ms): ' + right(' ' + cast(@Elapsed as varchar(10)),10) +

' Reads: ' + right(' ' + cast(@Reads as varchar(10)),10) +

' Writes: ' + right(' ' + cast(@Writes as varchar(10)),10);

set nocount off;

GO 5

set nocount on;

PRINT '---------- FOR XML PATH Version ----------';

go

--===== Create the timer and bit-bucket variables and start the timer.

DECLARE @Bitbucket sysname; --< change or add variables needed here to eliminate display distortion

Declare @cpu_ int,

@lreads_ bigint,

@eMsec_ bigint,

@Reads_ bigint,

@Writes_ bigint;

declare @CpuMs int,

@LogRds bigint,

@Elapsed bigint,

@Reads bigint,

@Writes bigint;

dbcc freeproccache with no_infomsgs;

dbcc freesystemcache('ALL') with no_infomsgs;

dbcc dropcleanbuffers with no_infomsgs;

Select

@cpu_ = cpu_time

, @lreads_ = logical_reads

, @eMsec_ = total_elapsed_time

, @Reads_ = reads

, @Writes_ = writes

From

sys.dm_exec_requests

Where

session_id = @@spid;

---

declare @SQLCmd nvarchar(max);

select

@SQLCmd = stuff((select char(13) + char(10) +

'exec sp_rename @objname = N''' + schema_name(tab.schema_id) + '.' + tab.name + ''', @newname = N''' + schema_name(tab.schema_id) + '.' + tab.name + '_' + convert(varchar(10), getdate(), 112) + ''', @objtype = N''TABLE'''

from sys.tables tab

for xml path(''),TYPE).value('.','nvarchar(max)'),1,2,'');

---

Select

@CpuMs = cpu_time - @cpu_

, @LogRds = logical_reads - @lreads_

, @Elapsed = total_elapsed_time - @eMsec_

, @Reads = reads - @Reads_

, @Writes = writes - @Writes_

From

sys.dm_exec_requests

Where

session_id = @@spid;

--===== Display the duration

PRINT 'CPU(ms): ' + right(' ' + cast(@CpuMs as varchar(10)),10) +

' Logical Reads: ' + right(' ' + cast(@LogRds as varchar(10)),10) +

' Elapsed(ms): ' + right(' ' + cast(@Elapsed as varchar(10)),10) +

' Reads: ' + right(' ' + cast(@Reads as varchar(10)),10) +

' Writes: ' + right(' ' + cast(@Writes as varchar(10)),10);

set nocount off;

GO 5

Jeff Moden SSC Guru Points: 1003869 More actions · Answer 2

AndrewSQLDBA (4/1/2013)
SQL Server does not Loop thru rows. SQL Server is Set Based.
Please do not indicate that a database loops thru rows.
Andrew SQLDBA

Certainly not in the classical sense, but SQL Server is like any other file server. Behind the scenes, it loops like made. R.Barry Young coined the phrase "Pseudo Cursor" for these types of things and it's actually what is responsible for things like the Tally Table working.

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

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

mister.magoo SSC-Forever Points: 47068 More actions · Answer 3

Lynn Pettis (4/1/2013)
Yea, but I think I'll stick with the FOR XML Version, the following was done against a database with 5147 tables in sys.tables:
---------- opc.three verison ----------
Beginning execution loop
CPU(ms): 73015 Logical Reads: 45463611 Elapsed(ms): 73495 Reads: 3790 Writes: 481191
CPU(ms): 72468 Logical Reads: 45460066 Elapsed(ms): 73294 Reads: 3983 Writes: 483049
CPU(ms): 73015 Logical Reads: 45458216 Elapsed(ms): 73864 Reads: 3886 Writes: 483049
CPU(ms): 72453 Logical Reads: 45458185 Elapsed(ms): 73498 Reads: 4068 Writes: 483050
CPU(ms): 72437 Logical Reads: 45461626 Elapsed(ms): 73262 Reads: 3731 Writes: 483049
Batch execution completed 5 times.
---------- FOR XML PATH Version ----------
Beginning execution loop
CPU(ms): 187 Logical Reads: 79425 Elapsed(ms): 531 Reads: 3477 Writes: 1201
CPU(ms): 187 Logical Reads: 79425 Elapsed(ms): 251 Reads: 3470 Writes: 1201
CPU(ms): 203 Logical Reads: 79425 Elapsed(ms): 414 Reads: 3475 Writes: 1201
CPU(ms): 219 Logical Reads: 79425 Elapsed(ms): 262 Reads: 3466 Writes: 1201
CPU(ms): 172 Logical Reads: 79425 Elapsed(ms): 317 Reads: 3457 Writes: 1201
Batch execution completed 5 times.

Well those poor stats will be improved a bit by reversing the concatenation:

SELECT @SQLCmd [highlight="#ffff11"]=[/highlight] N'EXEC sys.sp_rename ' + --

'@objname = N''' + SCHEMA_NAME(tab.schema_id) + '.' + tab.name + ''', ' + --

'@newname = N''' + tab.name + '_' + CONVERT(VARCHAR(10), GETDATE(), 112) + ''', ' + --

'@objtype = N''OBJECT'';' + -- OBJECT

NCHAR(13) + NCHAR(10) [highlight="#ffff11"]+ @SQLCmd[/highlight]

FROM sys.tables tab;

I don't have a suitably large database to test on , but I am sure you will see some improvement if you test it that way...

MM

select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);

Orlando Colamatteo SSC Guru Points: 182276 More actions · Answer 4

That seems to be a function of continually updating an NVARCHAR(MAX) which does not scale linearly. It gets truncated so it's worthless to use it but if you run the same code with an NVARCHAR(4000) it keeps up.

There is no question the XML method offers a nice performance improvement Lynn but I think the measurements are off. When I run this on a DB with ~600 tables my query finishes in 70ms and yours in 5ms, yet your measurement shows 47901ms and there is no way that's correct given that my machine only has 8 CPUs.

I find the variable-method a little easier to read (personal pref) and am not going to worry all that much about how long one of these kinds of queries is going to run mainly because performance working with DDL like this has never been too much of a concern for me. If a cursor is easier in this context it's one of the few places where I would use one, not for performance or readbility though, for the row-by-row error-handling option it offers that Erin mentioned. To each their own.

A couple things I tweaked in the original code that I thought should be fixed in the other samples, just in case someone tried to copy it and run off to use it, so I'll call them out here since I think they went unnoticed:

1. This snip:

@newname = N''' + schema_name(tab.schema_id) + '.' + tab.name

should have the schema name removed from the new name else you end up with tables named like [dbo].[dbo.table_20130401]

2. @objtype = N'TABLE' should be changed to @objtype = N'OBJECT'

There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato

Orlando Colamatteo SSC Guru Points: 182276 More actions · Answer 5

mister.magoo (4/1/2013)
I don't have a suitably large database to test on , but I am sure you will see some improvement if you test it that way...

You have done it again...have I told you that you are incredible lately MM?

I am guessing that appending is more efficient than prepending but never really ran into a performance issue that made me think about it like that...can you confirm the reason?

I created a little test bed, because it doesn't really matter what's in the tables or really how many columns. Just change the WHILE loop to create more or less tables and make sure really do not have a DB named [test] on your instance before running. Note that I am using SET STATISTICS ON instead of reading sys.dm_exec_sessions until Lynn can explain the other metrics.

SET STATISTICS TIME OFF;

USE master

DROP DATABASE test

CREATE DATABASE test

USE test

DECLARE @C INT = 10,

@sql NVARCHAR(MAX);

WHILE @C < 5000

BEGIN

SET @sql = 'use test; create table dbo.test' + CAST(@c AS CHAR(10)) + ' ( id int)';

--PRINT @sql

EXEC (@sql)

SET @C += 1

END

GO

PRINT '---------- opc.three verison ----------';

go

set nocount on;

--===== Create the timer and bit-bucket variables and start the timer.

dbcc freeproccache with no_infomsgs;

dbcc freesystemcache('ALL') with no_infomsgs;

dbcc dropcleanbuffers with no_infomsgs;

---

DECLARE @SQLCmd NVARCHAR(MAX),

@dt NVARCHAR(10) = CONVERT(NVARCHAR(10), GETDATE(), 112);

SET STATISTICS TIME ON;

-- instead of using FOR XML just append the conjured string from each row to the same variable

SELECT @SQLCmd = 'EXEC sys.sp_rename ' + --

'@objname = N''' + s.name + '.' + tab.name + ''', ' + --

'@newname = N''' + tab.name + '_' + @dt + ''', ' + --

'@objtype = N''OBJECT'';' + NCHAR(13) + NCHAR(10) + @SQLCmd

FROM sys.schemas s

JOIN sys.tables tab ON s.schema_id = tab.schema_id;

---

SET STATISTICS TIME OFF;

set nocount off;

GO

set nocount on;

PRINT '---------- FOR XML PATH Version ----------';

go

--===== Create the timer and bit-bucket variables and start the timer.

dbcc freeproccache with no_infomsgs;

dbcc freesystemcache('ALL') with no_infomsgs;

dbcc dropcleanbuffers with no_infomsgs;

---

declare @SQLCmd nvarchar(max);

SET STATISTICS TIME ON;

select

@SQLCmd = stuff((select char(13) + char(10) +

'sp_rename @objname = N''' + schema_name(tab.schema_id) + '.' + tab.name + ''', @newname = N''' + schema_name(tab.schema_id) + '.' + tab.name + '_' + convert(varchar(10), getdate(), 112) + ''', @objtype = N''TABLE'''

from sys.tables tab

for xml path(''),TYPE).value('.','nvarchar(max)'),1,2,'');

---

SET STATISTICS TIME OFF;

set nocount off;

GO

For 5000 tables on my machine:

---------- opc.three verison ----------

SQL Server Execution Times:

CPU time = 9719 ms, elapsed time = 9776 ms.

---------- FOR XML PATH Version ----------

SQL Server Execution Times:

CPU time = 32 ms, elapsed time = 51 ms.

edit: posted results for 500, not 5000. the above results are corrected for 5000 tables. ~10s to ~50ms

There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 6

mister.magoo (4/1/2013)
Lynn Pettis (4/1/2013)
Yea, but I think I'll stick with the FOR XML Version, the following was done against a database with 5147 tables in sys.tables:
---------- opc.three verison ----------
Beginning execution loop
CPU(ms): 73015 Logical Reads: 45463611 Elapsed(ms): 73495 Reads: 3790 Writes: 481191
CPU(ms): 72468 Logical Reads: 45460066 Elapsed(ms): 73294 Reads: 3983 Writes: 483049
CPU(ms): 73015 Logical Reads: 45458216 Elapsed(ms): 73864 Reads: 3886 Writes: 483049
CPU(ms): 72453 Logical Reads: 45458185 Elapsed(ms): 73498 Reads: 4068 Writes: 483050
CPU(ms): 72437 Logical Reads: 45461626 Elapsed(ms): 73262 Reads: 3731 Writes: 483049
Batch execution completed 5 times.
---------- FOR XML PATH Version ----------
Beginning execution loop
CPU(ms): 187 Logical Reads: 79425 Elapsed(ms): 531 Reads: 3477 Writes: 1201
CPU(ms): 187 Logical Reads: 79425 Elapsed(ms): 251 Reads: 3470 Writes: 1201
CPU(ms): 203 Logical Reads: 79425 Elapsed(ms): 414 Reads: 3475 Writes: 1201
CPU(ms): 219 Logical Reads: 79425 Elapsed(ms): 262 Reads: 3466 Writes: 1201
CPU(ms): 172 Logical Reads: 79425 Elapsed(ms): 317 Reads: 3457 Writes: 1201
Batch execution completed 5 times.
Well those poor stats will be improved a bit by reversing the concatenation:
SELECT @SQLCmd [highlight="#ffff11"]=[/highlight] N'EXEC sys.sp_rename ' + --
'@objname = N''' + SCHEMA_NAME(tab.schema_id) + '.' + tab.name + ''', ' + --
'@newname = N''' + tab.name + '_' + CONVERT(VARCHAR(10), GETDATE(), 112) + ''', ' + --
'@objtype = N''OBJECT'';' + -- OBJECT
NCHAR(13) + NCHAR(10) [highlight="#ffff11"]+ @SQLCmd[/highlight]
FROM sys.tables tab;
I don't have a suitably large database to test on , but I am sure you will see some improvement if you test it that way...

Test Harness:

PRINT '---------- mister.magoo verison ----------';

go

set nocount on;

--===== Create the timer and bit-bucket variables and start the timer.

DECLARE @Bitbucket sysname; --< change or add variables needed here to eliminate display distortion

Declare @cpu_ int,

@lreads_ bigint,

@eMsec_ bigint,

@Reads_ bigint,

@Writes_ bigint;

declare @CpuMs int,

@LogRds bigint,

@Elapsed bigint,

@Reads bigint,

@Writes bigint;

dbcc freeproccache with no_infomsgs;

dbcc freesystemcache('ALL') with no_infomsgs;

dbcc dropcleanbuffers with no_infomsgs;

Select

@cpu_ = cpu_time

, @lreads_ = logical_reads

, @eMsec_ = total_elapsed_time

, @Reads_ = reads

, @Writes_ = writes

From

sys.dm_exec_requests

Where

session_id = @@spid;

---

DECLARE @SQLCmd NVARCHAR(MAX) = N'';

-- instead of using FOR XML just append the conjured string from each row to the same variable

SELECT @SQLCmd = N'EXEC sys.sp_rename ' + --

'@objname = N''' + SCHEMA_NAME(tab.schema_id) + '.' + tab.name + ''', ' + --

'@newname = N''' + tab.name + '_' + CONVERT(VARCHAR(10), GETDATE(), 112) + ''', ' + --

'@objtype = N''OBJECT'';' + -- OBJECT

NCHAR(13) + NCHAR(10) + @SQLCmd

FROM sys.tables tab;

---

Select

@CpuMs = cpu_time - @cpu_

, @LogRds = logical_reads - @lreads_

, @Elapsed = total_elapsed_time - @eMsec_

, @Reads = reads - @Reads_

, @Writes = writes - @Writes_

From

sys.dm_exec_requests

Where

session_id = @@spid;

--===== Display the duration

PRINT 'CPU(ms): ' + right(' ' + cast(@CpuMs as varchar(10)),10) +

' Logical Reads: ' + right(' ' + cast(@LogRds as varchar(10)),10) +

' Elapsed(ms): ' + right(' ' + cast(@Elapsed as varchar(10)),10) +

' Reads: ' + right(' ' + cast(@Reads as varchar(10)),10) +

' Writes: ' + right(' ' + cast(@Writes as varchar(10)),10);

set nocount off;

GO 5

set nocount on;

PRINT '---------- FOR XML PATH Version ----------';

go

--===== Create the timer and bit-bucket variables and start the timer.

DECLARE @Bitbucket sysname; --< change or add variables needed here to eliminate display distortion

Declare @cpu_ int,

@lreads_ bigint,

@eMsec_ bigint,

@Reads_ bigint,

@Writes_ bigint;

declare @CpuMs int,

@LogRds bigint,

@Elapsed bigint,

@Reads bigint,

@Writes bigint;

dbcc freeproccache with no_infomsgs;

dbcc freesystemcache('ALL') with no_infomsgs;

dbcc dropcleanbuffers with no_infomsgs;

Select

@cpu_ = cpu_time

, @lreads_ = logical_reads

, @eMsec_ = total_elapsed_time

, @Reads_ = reads

, @Writes_ = writes

From

sys.dm_exec_requests

Where

session_id = @@spid;

---

declare @SQLCmd nvarchar(max);

select

@SQLCmd = stuff((select char(13) + char(10) +

'exec sp_rename @objname = N''' + schema_name(tab.schema_id) +

'.' + tab.name + ''', @newname = N''' + schema_name(tab.schema_id) +

'.' + tab.name + '_' + convert(varchar(10), getdate(), 112) + ''', @objtype = N''TABLE'''

from sys.tables tab

for xml path(''),TYPE).value('.','nvarchar(max)'),1,2,'');

---

Select

@CpuMs = cpu_time - @cpu_

, @LogRds = logical_reads - @lreads_

, @Elapsed = total_elapsed_time - @eMsec_

, @Reads = reads - @Reads_

, @Writes = writes - @Writes_

From

sys.dm_exec_requests

Where

session_id = @@spid;

--===== Display the duration

PRINT 'CPU(ms): ' + right(' ' + cast(@CpuMs as varchar(10)),10) +

' Logical Reads: ' + right(' ' + cast(@LogRds as varchar(10)),10) +

' Elapsed(ms): ' + right(' ' + cast(@Elapsed as varchar(10)),10) +

' Reads: ' + right(' ' + cast(@Reads as varchar(10)),10) +

' Writes: ' + right(' ' + cast(@Writes as varchar(10)),10);

set nocount off;

GO 5

Results:

---------- mister.magoo verison ----------

Beginning execution loop

CPU(ms): 72125 Logical Reads: 45047659 Elapsed(ms): 72511 Reads: 4053 Writes: 483074

CPU(ms): 72953 Logical Reads: 45048223 Elapsed(ms): 73248 Reads: 3830 Writes: 483074

CPU(ms): 72781 Logical Reads: 45047945 Elapsed(ms): 73238 Reads: 3993 Writes: 483074

CPU(ms): 72250 Logical Reads: 45049884 Elapsed(ms): 72838 Reads: 4254 Writes: 483075

CPU(ms): 72312 Logical Reads: 45051235 Elapsed(ms): 72734 Reads: 3727 Writes: 483075

Batch execution completed 5 times.

---------- FOR XML PATH Version ----------

Beginning execution loop

CPU(ms): 203 Logical Reads: 80643 Elapsed(ms): 381 Reads: 3464 Writes: 1238

CPU(ms): 203 Logical Reads: 80641 Elapsed(ms): 271 Reads: 3464 Writes: 1240

CPU(ms): 203 Logical Reads: 80639 Elapsed(ms): 267 Reads: 3464 Writes: 1238

CPU(ms): 187 Logical Reads: 80637 Elapsed(ms): 275 Reads: 3464 Writes: 1240

CPU(ms): 219 Logical Reads: 80637 Elapsed(ms): 269 Reads: 3464 Writes: 1240

Batch execution completed 5 times.

Is this what you expected?

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 7

opc.three (4/1/2013)
That seems to be a function of continually updating an NVARCHAR(MAX) which does not scale linearly. It gets truncated so it's worthless to use it but if you run the same code with an NVARCHAR(4000) it keeps up.
There is no question the XML method offers a nice performance improvement Lynn but I think the measurements are off. When I run this on a DB with ~600 tables my query finishes in 70ms and yours in 5ms, yet your measurement shows 47901ms and there is no way that's correct given that my machine only has 8 CPUs.
I find the variable-method a little easier to read (personal pref) and am not going to worry all that much about how long one of these kinds of queries is going to run mainly because performance working with DDL like this has never been too much of a concern for me. If a cursor is easier in this context it's one of the few places where I would use one, not for performance or readbility though, for the row-by-row error-handling option it offers that Erin mentioned. To each their own.
A couple things I tweaked in the original code that I thought should be fixed in the other samples, just in case someone tried to copy it and run off to use it, so I'll call them out here since I think they went unnoticed:
1. This snip:
@newname = N''' + schema_name(tab.schema_id) + '.' + tab.name
should have the schema name removed from the new name else you end up with tables named like [dbo].[dbo.table_20130401]
2. @objtype = N'TABLE' should be changed to @objtype = N'OBJECT'

I'll give you the @objtype change as something to be corrected, and the second as I just reread ap_rename. Now, how to deal with multiple schemas??

Without testing, does the new table name keep the old table name's schema?

mister.magoo SSC-Forever Points: 47068 More actions · Answer 8

Lynn, Nope that pretty much sucks too! :hehe:

MM

select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);

Orlando Colamatteo SSC Guru Points: 182276 More actions · Answer 9

I'll give you the @objtype change as something to be corrected, and the second as I just reread ap_rename. Now, how to deal with multiple schemas??
Without testing, does the new table name keep the old table name's schema?

Yes. Schemas are handled the way mine was written. No need to re-qualify anything. The @objname finds the object, and the @newname is the new name of the object within that schema.

There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato

Orlando Colamatteo SSC Guru Points: 182276 More actions · Answer 10

mister.magoo (4/1/2013)
Lynn, Nope that pretty much sucks too! :hehe:

Lynn's numbers seem off to me. I got ~10s for your method and ~50ms for Lynns. But Lynn's numbers make it look waaaay worse. I am wondering Lynn if your numbers are in nanoseconds?

There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 11

opc.three (4/1/2013)
I'll give you the @objtype change as something to be corrected, and the second as I just reread ap_rename. Now, how to deal with multiple schemas??
Without testing, does the new table name keep the old table name's schema?
Yes. Schemas are handled the way mine was written. No need to re-qualify anything. The @objname finds the object, and the @newname is the new name of the object within that schema.

Good. I don't use sp_rename often and actually everything in our databases here resides in the dbo schema.

Now, just hit myself over the head a few times with this information and perhaps it will reach the long term memory where I will have no trouble recalling.

mister.magoo SSC-Forever Points: 47068 More actions · Answer 12

opc.three (4/1/2013)
mister.magoo (4/1/2013)
Lynn, Nope that pretty much sucks too! :hehe:
Lynn's numbers seem off to me. I got ~10s for your method and ~50ms for Lynns. But Lynn's numbers make it look waaaay worse. I am wondering Lynn if your numbers are in nanoseconds?

No, they seem about right in terms of scale - I have seen massive improvements in the past from appending the MAX variable to the calculated string, rather than appending the string to the MAX variable, but not in this case. Celery.

MM

select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 13

opc.three (4/1/2013)
mister.magoo (4/1/2013)
Lynn, Nope that pretty much sucks too! :hehe:
Lynn's numbers seem off to me. I got ~10s for your method and ~50ms for Lynns. But Lynn's numbers make it look waaaay worse. I am wondering Lynn if your numbers are in nanoseconds?

CPU time and Elapsed time are in milliseconds. Plus, sitting there watching the code run, it is pretty accurate.

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 14

opc.three (4/1/2013)
mister.magoo (4/1/2013)
Lynn, Nope that pretty much sucks too! :hehe:
Lynn's numbers seem off to me. I got ~10s for your method and ~50ms for Lynns. But Lynn's numbers make it look waaaay worse. I am wondering Lynn if your numbers are in nanoseconds?

Also, the difference could be the size of the string as it is concatenated. The table names in the database I used can get a bit long in the tooth.

Orlando Colamatteo SSC Guru Points: 182276 More actions · Answer 15

Just for fun I wanted to see how a CLR might stack up so I ran it through GROUP_CONCAT against 5000 tables with an average length of ~115 characters:

Version using XML with a couple of the discussed tweaks:

SELECT @SQLCmd = STUFF((SELECT CHAR(13) + CHAR(10) + --

'EXEC sys.sp_rename @objname = N''' + SCHEMA_NAME(tab.schema_id) + '.' + tab.name + ''', ' + --

'@newname = N''' + tab.name + '_' + CONVERT(VARCHAR(10), GETDATE(), 112) + ''', ' + --

'@objtype = N''OBJECT'';'

FROM sys.tables tab

FOR XML PATH(''),

TYPE).value('.', 'nvarchar(max)'), 1, 2, '');

Version using CLR:

SELECT @SQLCmd = GroupConcatTest.dbo.group_concat_d(N'EXEC sys.sp_rename ' + --

N'@objname = N''' + SCHEMA_NAME(tab.schema_id) + N'.' + tab.name + N''', ' + --

N'@newname = N''' + tab.name + N'_' + @dt + N''', ' + --

N'@objtype = N''OBJECT''', N';' + NCHAR(13) + NCHAR(10))

FROM sys.tables tab;

---------- opc.three CLR version ----------

SQL Server Execution Times:

CPU time = 140 ms, elapsed time = 240 ms.

---------- FOR XML PATH Version ----------

SQL Server Execution Times:

CPU time = 78 ms, elapsed time = 108 ms.

There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato