Sunday, April 30, 2006

Return types of IDENTITY-functions

I ran into some strange inconsistency a couple of days ago when I was reading about identity-columns, -functions and -properties in the SQL Server Books OnLine (BOL).
I was actually only looking for the correct syntax of DBCC CHECKIDENT for reseeding an identity-column of a table, as I was wondering what the datatype of the new_reseed_value has to be.

DBCC CHECKIDENT ('table_name' , RESEED , new_reseed_value)

No big secret though, the datatype of an identity column can be int, bigint, smallint, tinyint, or decimal or numeric with a scale of 0. That explains why the return type for the functions @@IDENTITY and SCOPE_IDENTITY() is numeric, actually numeric(38,0), which is the datatype with the biggest precision of the whole list.
So far so good, but why has the similar function IDENT_CURRENT('table_name') a return type of sql_variant according to the BOL? It just doesn't make sense, certainly because I have no indication that the function actually returns a sql_variant. The way I see it now, IDENT_CURRENT simply returns numeric-data like the other functions, not sql_variant.

USE TempDB
GO
CREATE TABLE tmpTable
(
i NUMERIC(12,0) IDENTITY
)
GO
INSERT tmpTable DEFAULT VALUES
GO
DECLARE @sql_variant SQL_VARIANT

--Determining the datatype, precision and scale
SELECT @sql_variant = SCOPE_IDENTITY()
SELECT
SQL_VARIANT_PROPERTY(@sql_variant, 'BaseType') AS [BaseType],
SQL_VARIANT_PROPERTY(@sql_variant, 'Precision') AS [Precision],
SQL_VARIANT_PROPERTY(@sql_variant, 'Scale') AS [Scale]

--Determining the return type for the identity-functions
SELECT
@@IDENTITY AS [DT_@@IDENTITY],
SCOPE_IDENTITY() AS [DT_SCOPE_IDENTITY],
IDENT_CURRENT('tmpTable') AS [DT_IDENT_CURRENT]
INTO tmpReturnType

EXEC sp_help 'tmpReturnType'
GO
DROP TABLE tmpReturnType
DROP TABLE tmpTable
GO

No comments: