Sql server® 2012 t-sql fundamentals

 

CHAPTER 8

 

Data Modification

 

 259

If you don’t need to generate the new sequence value before using it, you can specify the NEXT 

VALUE FOR function directly as part of your INSERT statement, like this.

INSERT INTO dbo.T1(keycol, datacol) 

  VALUES(NEXT VALUE FOR dbo.SeqOrderIDs, 'b'); 

 

SELECT * FROM dbo.T1;

This code returns the following output.

keycol      datacol 

----------- ---------- 

2           a 

3           b

Unlike with identity, you can generate new sequence values in an UPDATE statement, like this.

UPDATE dbo.T1 

  SET keycol = NEXT VALUE FOR dbo.SeqOrderIDs; 

 

SELECT * FROM dbo.T1;

This code returns the following output.

keycol      datacol 

----------- ---------- 

4           a 

5           b

To get information about your sequences, query a view called sys.sequences. For example, to find 

the current sequence value in the SeqOrderIDs sequence, you would use the following code.

SELECT current_value 

FROM sys.sequences 

WHERE OBJECT_ID = OBJECT_ID('dbo.SeqOrderIDs');

This code generates the following output.

current_value 

-------------- 

5

SQL Server extends its support for the sequence option with capabilities beyond what the com-

petitors and the standard currently support. One of the extensions enables you to control the order of 

the assigned sequence values in a multi-row insert by using an OVER clause similar to the one window 

functions use. Here’s an example.

INSERT INTO dbo.T1(keycol, datacol) 

  SELECT 

    NEXT VALUE FOR dbo.SeqOrderIDs OVER(ORDER BY hiredate), 

    LEFT(firstname, 1) + LEFT(lastname, 1) 

  FROM HR.Employees; 

 

SELECT * FROM dbo.T1;

www.it-ebooks.info

---

260  

Microsoft SQL Server 2012 T-SQL Fundamentals

This code returns the following output.

keycol      datacol 

----------- ---------- 

4           a 

5           b 

6           JL 

7           SD 

8           DF 

9           YP 

10          SB 

11          PS 

12          RK 

13          MC 

14          ZD

Another extension allows the use of the NEXT VALUE FOR function in a default constraint. Here’s 

an example.

ALTER TABLE dbo.T1 

  ADD CONSTRAINT DFT_T1_keycol 

    DEFAULT (NEXT VALUE FOR dbo.SeqOrderIDs) 

    FOR keycol;

Now when you insert rows into the table, you don’t have to indicate a value for keycol.

INSERT INTO dbo.T1(datacol) VALUES('c'); 

 

SELECT * FROM dbo.T1;

This code returns the following output.

keycol      datacol 

----------- ---------- 

4           a 

5           b 

6           JL 

7           SD 

8           DF 

9           YP 

10          SB 

11          PS 

12          RK 

13          MC 

14          ZD 

15          C

This is a great advantage over identity—you can add a default constraint to an existing table and 

remove it from an existing table as well.

Finally, another extension allows you to allocate a whole range of sequence values at once by using 

a stored procedure called sp_sequence_get_range. The idea is that if the application needs to assign a 

range of sequence values, it is easiest to update the sequence only once, incrementing it by the size of 

www.it-ebooks.info

---