Sunday, June 29, 2014

What is the Equivalent of SCOPE_IDENTITY for SEQUENCE object?

The OUTPUT clause can be used. Is there any other choice? Thanks.



CREATE SEQUENCE dbo.NextCustomerID
as BIGINT
START WITH 1
INCREMENT BY 1;
GO

-- Get the next available ID
CREATE TABLE Customer(
CustomerID BIGINT DEFAULT (NEXT VALUE FOR dbo.NextCustomerID),
Name nvarchar(50) DEFAULT (SPACE(0)),
ModifiedDate datetime default (CURRENT_TIMESTAMP));
GO

SET NOCOUNT ON;

INSERT Customer DEFAULT VALUES;
GO 100

SELECT * FROM Customer;
GO
/*
CustomerID Name ModifiedDate
1 2014-06-29 11:53:51.317
2 2014-06-29 11:53:51.320
3 2014-06-29 11:53:51.320
4 2014-06-29 11:53:51.320
5 2014-06-29 11:53:51.320
6 2014-06-29 11:53:51.320
7 2014-06-29 11:53:51.320
...
*/





Kalman Toth Database & OLAP Architect SQL Server 2014 Design & Programming

New Book / Kindle: Exam 70-461 Bootcamp: Querying Microsoft SQL Server 2012















No comments:

Post a Comment