SCOPE_IDENTITY()

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

Advertisements
This entry was posted in SQL Server. Bookmark the permalink.

One Response to SCOPE_IDENTITY()

  1. Unknown says:

    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:

WordPress.com Logo

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

Twitter picture

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

Facebook photo

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

Google+ photo

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

Connecting to %s