We are in the middle of optimizing a big staging database that contains literally billions of uniqueidentifier-integer pairs over several tables. The database is used in a process to convert the nice little GUIDs of our production environment into keys that the reporting environment on Oracle of our enterprise can more easily deal with. Don't ask why we do it - I guess we lost that strategic battle :(
Anyway, when I was looking at some of the generic legacy code to identify the ROWGUID of a table, I remembered something about $ROWGUID. This automatically returns the ROWGUIDCOL of that table. Sadly, it's new in SQL Server 2005 and because we haven't had the chance to migrate that database, we still need to use some looping to investigate the COLUMNPROPERTY to finally get the name of the column that can be used in dynamic SQL to mimic the behavior of $ROWGUID:
USE TempDB
GO
CREATE TABLE SomeTable
(
ID uniqueidentifier ROWGUIDCOL
)
GO
SELECT $ROWGUID FROM SomeTable
GO
DROP TABLE SomeTable
