A technical post for a change. We recently noticed a difference in SQL Server 2005 over 2000 around the use of the SCOPE_IDENTITY function. I’ve been used to "SELECT SCOPE_IDENTITY()" returning INT as its data type, as that was the largest data type that was allowed as an IDENTITY column. However, it now seems to be returning DECIMAL.
A quick investigation shows that DECIMAL can indeed be used as an IDENTITY column. There is a restriction that the scale must be zero, i.e. DECIMAL(n,0), so that the field only contains whole numbers.
Exciting? Maybe not. Interesting? Evidently enough for me to blog it.