SQL Server


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: ,


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.

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s