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);