|
|
Error Message
Server: Msg 302, Level 16, State 0, Line 1
The newsequentialid() built-in function can only be used
in a DEFAULT expression for a column of type
'uniqueidentifier' in a CREATE TABLE or ALTER TABLE
statement. It cannot be combined with other operators
to form a complex scalar expression.
The built-in system function NEWSEQUENTIALID, introduced in SQL Server 2005, creates a GUID that is greater than any GUID previously generated by this function on a specified computer since Windows was started.
|
After restarting Windows, the GUID generated by the NEWSEQUENTIALID system function starts again from a lower range, but will still be globally unique.
The NEWSEQUENTIALID system function can be used to generate GUIDs to reduce page contention at the leaf level of indexes. Each GUID generated is unique on a computer and are also unique across multiple computers only if the source computer has a network card. When a GUID column is used as a row identifier, using NEWSEQUENTIALID can be faster than using the NEWID function because the NEWID function causes random activity and uses fewer cached data pages. Using NEWSEQUENTIALID also helps to completely fill the data and index pages.
One restriction of the NEWSEQUENTIALID system function is that it can only be used with a DEFAULT constraint on table columns of type UNIQUEIDENTIFIER. The NEWSEQUENTIALID cannot be reference in queries, otherwise this error will be encountered. Here’s a couple of queries where it calls the NEWSEQUENTIALID not within a DEFAULT constraint, and therefore generating this error message:
SELECT NEWSEQUENTIALID() AS [UniqueID]
Msg 302, Level 16, State 0, Line 1
The newsequentialid() built-in function can only be used in a DEFAULT expression for a column of type
'uniqueidentifier' in a CREATE TABLE or ALTER TABLE statement. It cannot be combined with other
operators to form a complex scalar expression.
DECLARE @UniqueIdentifier UNIQUEIDENTIFIER
SET @UniqueIdentifier = NEWSEQUENTIALID()
Msg 302, Level 16, State 0, Line 3
The newsequentialid() built-in function can only be used in a DEFAULT expression for a column of type
'uniqueidentifier' in a CREATE TABLE or ALTER TABLE statement. It cannot be combined with other
operators to form a complex scalar expression.
Solution / Work Around
Given that the NEWSEQUENTIALID built-in system function can only be used in a DEFAULT constraint on table columns of type UNIQUEIDENTIFIER, then to avoid this error message, the system function should not be used anywhere else except in a DEFAULT constraint. Here’s an example of how to use the NEWSEQUENTIALID function when creating a table:
CREATE TABLE [dbo].[User] (
[UserID] INT NOT NULL IDENTITY(1, 1),
[UserName] VARCHAR(20) NOT NULL,
[UserUniqueKey] UNIQUEIDENTIFIER DEFAULT ( NEWSEQUENTIALID() )
)
If the table already exists and a default constraint is to be assigned to a column of UNIQUEIDENTIFIER, here’s how the SQL statement will look like, assigning the NEWSEQUENTIALID() function as the default value:
ALTER TABLE [dbo].[User]
ADD DEFAULT ( NEWSEQUENTIALID() ) FOR [UserUniqueKey]
To determine the UNIQUEIDENTIFIER value of a newly inserted record from a table where the default value is set by the NEWSEQUENTIALID built-in system function, the following statements can be used:
CREATE TABLE [dbo].[User] (
[UserID] INT NOT NULL IDENTITY(1, 1),
[UserName] VARCHAR(20),
[UserUniqueKey] UNIQUEIDENTIFIER DEFAULT( NEWSEQUENTIALID() )
)
DECLARE @UserUniqueKey UNIQUEIDENTIFIER
INSERT INTO @User ( [UserName] )
VALUES ( 'SQLServerHelper' )
SELECT @UserUniqueKey = [UserUniqueKey]
FROM [dbo].[User]
WHERE [UserName] = 'SQLServerHelper'
SELECT @UserUniqueKey
|