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.
Technorati tags: SQL Server 2005, SQL Server
Josh's Space 
One reply on “SCOPE_IDENTITY()”
Thanks for the post. It helped confirm a problem we are having. We have some old classic ASP code that is using ADO. The programmer who wrote the code was using SCOPE_IDENTITY to get the IDENTITY used on a previous insert. He was basically issuing a dynamic SQL statement and putting the result back into a ADO recordset. This method worked for years on SQL 2000. We just recently migrated this app to SQL 2005 and this broke. The problem was the recordset was now getting a NULL value. It is our thinking that the change in SCOPE_IDENTITY in 2005 doesn\’t mashup with old ADO that well. So, rather than doing
rsSave.Open "SELECT SCOPE_IDENTITY() AS \’EntryID\’ ",cnTime,adOpenStatic,adLockReadOnly
We are now doing
rsSave.Open "DECLARE @RecID int; SET @RecID = SCOPE_IDENTITY(); SELECT @RecID AS \’EntryID\’ ",cnTime,adOpenStatic,adLockReadOnly
This is now getting the recordset populated with the IDENTITY. Interestingly enough, if we did a CAST to INT on the original statement, that did not help.