Skip Navigation Links
Home
Articles
SQL Server 2012
SQL Server 2014
SQL Server 2016
FAQ
Forums
Practice Test
Bookstore
Tip of the Day : Transact-SQL Set Operators (UNION, EXCEPT and INTERSECT)
Error Messages
Home > SQL Server Error Messages > Msg 302 - 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.
SQL Server Error Messages - Msg 302 - 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.

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
Related Articles :