SQL Server Magazine

Recently, I was pondering the complexities of IDENTITY values, and I ran across a user-defined function (UDF) trick that Zoltán Kovács, a SQL Server MCT from Hungary, created. His use of UDFs prompted me to write this article, which shows you how SQL Server 2000's new UDF feature can provide an alternative to the IDENTITY property.

Using the IDENTITY property to create IDENTITY columns lets you fill those columns with automatically incremented values without user intervention. However, if many DELETE and INSERT statements modify the database, the final IDENTITY column values won't be consecutive because when you delete a row, SQL Server doesn't reuse its IDENTITY value. The standard data type for IDENTITY columns is int, usually unsigned, which gives you values up to 2,147,483,647. However, if you expect a table to have more than 2 billion insertions in its lifetime, you can select a different data type (such as numeric) for the IDENTITY column. You might use SQL Server 2000's new bigint data type to increase the limit to 9,223,372,036,854,775,807 INSERT operations. But bigint uses 8 bytes of storage space, making it more expensive than the int data type. If you don't expect to have more than 2 billion insertions into the table, you might apply a technique to automatically reuse lost IDENTITY values. Or, you can use UDFs instead of IDENTITY.

Providing Default Numeric Values
Any SQL Server built-in function can be a default value for a particular field. You can extend SQL Server 2000's functionality by defining your own scalar UDFs. In this way, you encapsulate complex operations in an easy-to-use function, and invoke your own UDF wherever SQL Server allows an expression. (For more information about UDFs, see Robert Vieira, "User-Defined Functions," November 2000.) You can use a UDF to provide a default value for a primary key column in the same way that you might use the NEWID() function or the IDENTITY property to provide a default value. For example, let's look at an IDTest table with two columns: ID and value.

CREATE TABLE IDTest(
	ID int PRIMARY KEY CLUSTERED,
	value varchar(40) NULL
)
GO

To use the IDTest table, you must supply values for the ID column because it isn't an IDENTITY column. Let's create a UDF called fn_GetNewID() to return unique autonumbered values for the ID column:

CREATE FUNCTION dbo.fn_GetNewID()
  RETURNS int
AS
BEGIN
DECLARE @newid AS int
IF EXISTS (SELECT * FROM IDTest WHERE ID = 1)
  SELECT @newid = MIN(ID) + 1 FROM IDTest AS A
WHERE NOT EXISTS(SELECT * FROM IDTest 
  WHERE ID = A.ID + 1)
ELSE
  SET @newid = 1
RETURN @newid
END

The fn_GetNewID() function returns the first available out-of-sequence ID value. Whenever you insert a new row, fn_GetNewID() searches the IDTest table for the first nonconsecutive entry and returns that ID value.

Before you apply fn_GetNewID() to generate the IDTest table's default ID column values, let's try to predict the function's performance. Consider the following query:

SELECT @newid = MIN(ID) + 1 FROM IDTest AS A
WHERE NOT EXISTS(SELECT * FROM IDTest 
WHERE ID = A.ID + 1)

   Prev. page   [1] 2 3 4 5     next page