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