THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - The SQL Server blog spot on the web Sign in | |
in Search

Aaron Bertrand

Aaron is a Product Evangelist for SentryOne, makers of Plan Explorer and a performance monitoring and event management platform for the Microsoft Data Platform and VMware. He has been blogging here at sqlblog.com since 2006, focusing on manageability, performance, and new features, and also blogs at blogs.sentryone.com and SQLPerformance.com; has been a Microsoft MVP since 1997; tweets as @AaronBertrand; and speaks frequently at major conferences, user group meetings, and SQL Saturday events worldwide.

SQL Server v.Next (Denali) : Using SEQUENCE

SQL Server 2011 introduces a feature that Oracle professionals will certainly recognize: SEQUENCE.  SEQUENCE allows you to define a central place where SQL Server will maintain a counter of values for you.  The last-used value is stored in memory and so no storage is required.  Here is an example:

CREATE SEQUENCE dbo.OrderIDs
    
AS INT
MINVALUE
1
   
NO MAXVALUE
START
WITH 1;

SET NOCOUNT ON;

SELECT NextOrderID = NEXT VALUE FOR dbo.OrderIDs
UNION ALL SELECT NEXT VALUE FOR dbo.OrderIDs
UNION ALL SELECT NEXT VALUE FOR dbo.OrderIDs;

Results:

NextOrderID
-----------
1
2
3

Notice I did not have to create any tables or worry about persisting information to disk in any way.  What about reseeding?  This is a common problem with IDENTITY columns.  You can "reseed" a sequence using ALTER and RESTART WITH:

ALTER SEQUENCE dbo.foo
RESTART WITH 20;

SET NOCOUNT ON
;

SELECT NextOrderID = NEXT VALUE FOR dbo.OrderIDs
UNION ALL SELECT NEXT VALUE FOR dbo.OrderIDs
UNION ALL SELECT NEXT VALUE FOR dbo.OrderIDs;

Results:

NextOrderID
-----------
20
21
22

And if you want to roll back the clock or re-use an existing sequence, you can use a lower number with RESTART WITH as well - though I'd be careful with this depending on how you are using the values.

Want to increment by a value other than 1 (the default)?  You can use INCREMENT BY:

ALTER SEQUENCE dbo.foo
RESTART WITH 1
INCREMENT BY 5;

SET NOCOUNT ON
;

SELECT NextOrderID = NEXT VALUE FOR dbo.OrderIDs
UNION ALL SELECT NEXT VALUE FOR dbo.OrderIDs
UNION ALL SELECT NEXT VALUE FOR dbo.OrderIDs;

Results:

NextOrderID
-----------
1
6
11

You can start a sequence at a value greater than 1 simply by changing the MINVALUE.  You can also start at the low end of the data range, e.g. to use all 4 billion values for INT instead of just the 2 billion positive values. And you can even use INCREMENT BY to count down instead of up; just use a negative value.

You can also ensure that the sequence doesn't use all the possible values, for example if you wanted to have a maximum of 20 values in a TINYINT sequence, you could use the following code to produce a max of 20 values instead of all 255:

CREATE SEQUENCE dbo.foo
    
AS TINYINT
MINVALUE
1
   
MAXVALUE 20
START
WITH 1;
GO

When you create such a sequence, you will get the following warning message (note that it is not an error):

The sequence object 'foo' cache size is greater than the number of available values; the cache size has been automatically set to accommodate the remaining sequence values.

And if you actually try to go past the first 20 values:

SELECT NEXT VALUE FOR dbo.foo;
GO 21

You will get this error message:

Msg 11728, Level 16, State 1, Line 2
The sequence object 'foo' has reached its minimum or maximum value. Restart the sequence object to allow new values to be generated.

You can restart the sequence using ALTER SEQUENCE ... RESTART WITH.  If you want to use up all of the values and then loop around and start over, you can use the CYCLE option.

Surprisingly, if you simply overflow the datatype, you get the same sequence-related error message as opposed to the more familiar Msg 220 (arithmetic overflow) error:

SELECT NEXT VALUE FOR dbo.foo;
GO 256

It is important to note that SEQUENCE will not provide you a transactionally consistent and gap-free stream of values.  For example, just like IDENTITY, if your transaction rolls back, the next value is still taken:

CREATE SEQUENCE dbo.bar
AS INT
MINVALUE
1
NO MAXVALUE
START WITH
1;
GO

BEGIN TRANSACTION
;
SELECT
NextBar = NEXT VALUE FOR dbo.bar;
ROLLBACK TRANSACTION;
SELECT NextBar = NEXT VALUE FOR dbo.bar;

Results:

NextBar
-----------
1

NextBar
-----------
2

And if you want to check the current value of the sequence without actually using up a value (like checking IDENT_CURRENT() or DBCC CHECKIDENT), you can check the new catalog view, sys.sequences:

SELECT current_value 
    FROM sys.sequences
    WHERE name = 'OrderIDs';

You can also check several other properties of the sequence this way (don't bother trying to use sp_help - all it will tell you is that it is a sequence object).

Want to get a range of SEQUENCE values?  Don't use a loop; there's a stored procedure for that: sys.sp_sequence_get_range.  Here is a sample:

DECLARE @fv SQL_VARIANT, @lv SQL_VARIANT;

EXEC sys.sp_sequence_get_range
    @sequence_name = 'OrderIDs',
    @range_size = 20,
    @range_first_value = @fv OUTPUT,
    @range_last_value = @lv OUTPUT;

SELECT
fv = CONVERT(INT, @fv),
lv = CONVERT(INT, @lv),
next = NEXT VALUE FOR dbo.OrderIDs;

Results:

fv   lv   next
---- ---- ----
 5 24 25


But how does it perform?

I can see significant performance improvements over using a central identity table or a custom increment table (two typical ways this feature has been implemented).  In the following example, I show five different ways to manage a central value generator.  The first is a custom increment table, a popular method which uses a quirky update and is certainly far from standard.  Next there are two methods using an IDENTITY column: one where the old rows are deleted before the next value is generated (for no other reason other than to save space), and the other that doesn't bother to do the delete, letting the table grow as new values are generated.  Finally, we use the sequence, once with a procedure wrapper to simplify the changes to existing scripts, and once with the new NEXT VALUE syntax inserted into the code:

USE [master];
GO

-- drop the database if it exists, and the #timing temp table
-- then create the database

IF DB_ID('BookStore') > 0
BEGIN
   DROP DATABASE
[BookStore];
END
GO

IF OBJECT_ID('tempdb..#timing') IS NOT NULL
BEGIN
   DROP TABLE
#timing;
END
GO

CREATE DATABASE [BookStore];
GO

USE [BookStore];
GO

SET NOCOUNT ON;
GO

-- create a table with an IDENTITY column

CREATE TABLE dbo.CentralIdentity
(
    
ID BIGINT IDENTITY(1,1) PRIMARY KEY
);

-- create a "roll your own" increment table and seed initial value

CREATE TABLE
dbo.CentralIncrement
(
    
ID BIGINT PRIMARY KEY
);
GO
INSERT dbo.CentralIncrement(ID) SELECT 0;
GO

-- create a sequence with the same seed and increment properties

CREATE SEQUENCE dbo.CentralSequence
    
AS BIGINT
MINVALUE 1
NO MAXVALUE
START
WITH 1;
GO

-- stored procedure for increment, which uses quirky update

CREATE PROCEDURE dbo.Central_CreateIncrement
  
@NextID BIGINT OUTPUT
AS
BEGIN
   SET NOCOUNT ON
;

  
UPDATE dbo.CentralIncrement
      
SET @NextID = ID = ID + 1;
END
GO

-- stored procedure for increasing the IDENTITY column
-- with a parameter for deleting the existing value(s) first

CREATE PROCEDURE dbo.Central_CreateIdentity
  
@NextID BIGINT OUTPUT,
  
@Delete BIT = 0
AS
BEGIN
   SET NOCOUNT ON
;

  
IF @Delete = 1
  
BEGIN
       DELETE
dbo.CentralIdentity;
  
END

   INSERT
dbo.CentralIdentity DEFAULT VALUES;
  
SELECT @NextID = SCOPE_IDENTITY();
END
GO

-- stored procedure for handling incrementing the SEQUENCE

CREATE PROCEDURE dbo.Central_CreateSequence
  
@NextID BIGINT OUTPUT
AS
BEGIN
   SET NOCOUNT ON
;

  
SELECT @NextID = NEXT VALUE FOR dbo.CentralSequence;
END
GO

-- simple table that will be the recipient of key values

CREATE TABLE dbo.Orders
(
  
OrderID INT PRIMARY KEY
);
GO

-- simple table for tracking timings

CREATE TABLE #timing
(
  
Step    VARCHAR(64),
  
dt      DATETIME2(7) NOT NULL DEFAULT SYSUTCDATETIME()
);
GO

-- start each step with a blank orders table
-- this step is handling the quirky increment update

TRUNCATE TABLE dbo.Orders;
GO

INSERT #timing(Step) SELECT 'Starting increment';
GO

DECLARE
  
@NextID BIGINT;

EXEC dbo.Central_CreateIncrement
  
@NextID = @NextID OUTPUT;

INSERT dbo.Orders(OrderID) SELECT @NextID;
GO 100000

INSERT #timing(Step) SELECT 'Finished increment';
GO

-- this step is handling the identity with the delete

TRUNCATE TABLE dbo.Orders;
GO

INSERT #timing(Step) SELECT 'Starting identity + delete';
GO

DECLARE
  
@NextID BIGINT;

EXEC dbo.Central_CreateIdentity
  
@NextID = @NextID OUTPUT,
  
@Delete = 1;

INSERT dbo.Orders(OrderID) SELECT @NextID;
GO 100000

INSERT #timing(Step) SELECT 'Finished identity + delete';
GO

-- this step is handling the identity without the delete

TRUNCATE TABLE dbo.Orders;
GO

INSERT #timing(Step) SELECT 'Starting identity without delete';
GO

DECLARE
  
@NextID BIGINT;

EXEC dbo.Central_CreateIdentity
  
@NextID = @NextID OUTPUT,
  
@Delete = 1;

INSERT dbo.Orders(OrderID) SELECT @NextID;
GO 100000

INSERT #timing(Step) SELECT 'Finished identity without delete';
GO

-- this step handles the SEQUENCE with the procedure wrapper

TRUNCATE TABLE dbo.Orders;
GO

INSERT #timing(Step) SELECT 'Starting sequence with PROC';
GO

DECLARE
  
@NextID BIGINT;

EXEC dbo.Central_CreateSequence
  
@NextID = @NextID OUTPUT;

INSERT dbo.Orders(OrderID) SELECT @NextID;
GO 100000

INSERT #timing(Step) SELECT 'Finished sequence with PROC';
GO

-- this step handles the SEQUENCE directly

TRUNCATE TABLE dbo.Orders;
GO

INSERT #timing(Step) SELECT 'Starting sequence without PROC';
GO

INSERT dbo.Orders(OrderID) SELECT NEXT VALUE FOR dbo.CentralSequence;
GO 100000

INSERT #timing(Step) SELECT 'Finished sequence without PROC';
GO

-- ok, now we can run a query to measure timings

;WITH t AS
(
  
SELECT
      
Step,
      
dt,
      
rn = ROW_NUMBER() OVER (ORDER BY dt)
  
FROM
      
#timing
)
SELECT
  
t1.Step,
   dur_s  = DATEDIFF(SECOND, t1.dt, t2.dt)
FROM t AS t1
INNER JOIN t AS t2
ON t1.rn = t2.rn - 1
WHERE t1.rn % 2 = 1
AND t2.rn % 2 = 0;
GO

The results, keeping in mind that this was in a VM on a laptop:


The clear winner is obviously the SEQUENCE operator, since it doesn't have to write the data to disk in order to persist the current value.  Next I ran the same tests, but this time turned page compression on for the increment and identity tables, to see if the reduced I/O might let the existing solutions come a little closer.  The only change to the above script is in bold:

CREATE TABLE dbo.CentralIdentity
(
    
ID BIGINT IDENTITY(1,1) PRIMARY KEY
)
WITH (DATA_COMPRESSION = PAGE);

CREATE TABLE dbo.CentralIncrement
(
    
ID BIGINT PRIMARY KEY
)
WITH (DATA_COMPRESSION = PAGE);

The results were indistinguishable.  In fact, from run to run, one method would gain a second, and another would lose two; on the next run, the first one would lose three seconds and another would gain one. 

I also tried putting a NEXT VALUE FOR command in a user-defined function, to see if there was an easier way to inline the syntax, and see if the UDF overhead would impact the observed benefit.  However if you try to create a function with a reference to a sequence object, here is the error you will get (which will also point out a few other places where you shouldn't bother trying to use them - it's a pretty long laundry list):

Msg 11719, Level 15, State 1, Procedure Central_CreateSequence, Line 6
NEXT VALUE FOR function is not allowed in check constraints, default objects, computed columns, views, user-defined functions, user-defined aggregates, sub-queries, common table expressions, or derived tables.

Note also that you can use NEXT VALUE FOR in a UNION ALL, but not in a UNION, EXCEPT, INTERSECT, or with DISTINCT:

Msg 11721, Level 15, State 1, Line 9
NEXT VALUE FOR function cannot be used directly in a statement that uses a DISTINCT, UNION (except UNION ALL), EXCEPT or INTERSECT operator.

If you look in sys.messages, you'll see there are several other limitations in the initial implementation of SEQUENCE. Some of these error messages are quite logical, but others are quite interesting.  For example, it is okay to reference a sequence object in another database, but not when defined as a default constraint.

Msg 11700 The increment for sequence object '%.*ls' cannot be zero.
Msg 11701 The absolute value of the increment for sequence object '%.*ls' must be less than or equal to the difference between the minimum and maximum value of the sequence object.
Msg 11702 The sequence object '%.*ls' must be of data type int, bigint, smallint, tinyint, or decimal or numeric with a scale of 0, or any user-defined data type that is based on one of the above integer data types.
Msg 11703 The start value for sequence object '%.*ls' must be between the minimum and maximum value of the sequence object.
Msg 11704 The current value '%.*ls' for sequence object '%.*ls' must be between the minimum and maximum value of the sequence object.
Msg 11705 The minimum value for sequence object '%.*ls' must be less than its maximum value.
Msg 11706 The cache size for sequence object '%.*ls' must be greater than 0.
Msg 11707 The cache size for sequence object '%.*ls' has been set to NO CACHE.
Msg 11709 The 'RESTART WITH' argument cannot be used in a CREATE SEQUENCE statement.
Msg 11710 Argument 'START WITH' cannot be used in an ALTER SEQUENCE statement.
Msg 11711 Argument 'AS' cannot be used in an ALTER SEQUENCE statement.
Msg 11714 Invalid sequence name '%.*ls'.
Msg 11715 No properties specified for ALTER SEQUENCE.
Msg 11716 NEXT VALUE FOR function does not support the PARTITION BY clause.
Msg 11717 NEXT VALUE FOR function does not support the OVER clause in default constraints, UPDATE statements, or MERGE statements.
Msg 11718 NEXT VALUE FOR function does not support an empty OVER clause.
Msg 11719 NEXT VALUE FOR function is not allowed in check constraints, default objects, computed columns, views, user-defined functions, user-defined aggregates, sub-queries, common table expressions, or derived tables.
Msg 11720 NEXT VALUE FOR function is not allowed in the TOP, OVER, OUTPUT, ON, WHERE, GROUP BY, HAVING, ORDER BY, COMPUTE, or COMPUTE BY clauses.
Msg 11721 NEXT VALUE FOR function cannot be used directly in a statement that uses a DISTINCT, UNION (except UNION ALL), EXCEPT or INTERSECT operator.
Msg 11722 NEXT VALUE FOR function is not allowed in the WHEN MATCHED clause, the WHEN NOT MATCHED clause, or the WHEN NOT MATCHED BY SOURCE clause of a merge statement.
Msg 11723 NEXT VALUE FOR function cannot be used directly in a statement that contains an ORDER BY clause unless the OVER clause is specified.
Msg 11724 An expression that contains a NEXT VALUE FOR function cannot be passed as an argument to a table-valued function.
Msg 11725 An expression that contains a NEXT VALUE FOR function cannot be passed as an argument to an aggregate.
Msg 11726 Object '%.*ls' is not a sequence object.
Msg 11727 NEXT VALUE FOR functions for a given sequence object must have exactly the same OVER clause definition.
Msg 11728 The sequence object '%.*ls' has reached its minimum or maximum value. Restart the sequence object to allow new values to be generated.
Msg 11729 The sequence object '%.*ls' cache size is greater than the number of available values; the cache size has been automatically set to accommodate the remaining sequence values.
Msg 11730 Database name cannot be specified for the sequence object in default constraints.
Msg 11731 A column that uses a sequence object in the default constraint must be present in the target columns list, if the same sequence object appears in either a row constructor or under UNION ALL.
Msg 11732 The requested range for sequence object '%.*ls' exceeds the maximum or minimum limit. Retry with a smaller range.

Hope that gives a good taste of SEQUENCE.  As you start contemplating the move to the next version of SQL Server, you may want to consider this feature if you are currently using a central identity or increment table - as long as you can get along with its limitations.  If you want more official information on SEQUENCE, including a great description on how memory is used to cache sequence values and avoid I/O, you can start at this Books Online topic, Creating and Using Sequence Numbers.

 

Published Thursday, November 11, 2010 2:04 PM by AaronBertrand

Comments

 

unclebiguns said:

The one question I have, is there a way to use SEQUENCE in a set-based manner?

November 11, 2010 3:12 PM
 

AaronBertrand said:

Jack, what do you mean?  Like you want it to replace a numbers table?

November 11, 2010 3:57 PM
 

Regina said:

PostgreSQL has the same feature.  Has had it since the beginning of time.

You can create it as part of the definition of a table using serial.

CREATE TABLE sometable(someid serial PRIMARY KEY etc.);

and it increments much like the SQL Server identity does.

But what is cool is you can also create it apart from the table and use it as an incrementor of multiple tables or manually increment it.

the serial is really a fictitious type for convenience that behinds the scenes creates a sequence object and sets the default value of the column to the next value of the sequence

CREATE SEQUENCE sometable_someid_seq

 INCREMENT 1

 MINVALUE 1

 MAXVALUE 9223372036854775807

 START 1

 CACHE 1;

then you can use nextval, curval etc functions to push it forward etc or use nextval as a default for a table

November 15, 2010 4:12 AM
 

Jorge Segarra said:

Thanks for this great writeup! I was exploring Denali on the plane and came across SEQUENCE but didn't have books online installed so this new keyword's usage remained a mystery for me until now.

November 15, 2010 7:14 AM
 

Dugi said:

Yep nice explanation and thanks for sharing!

November 15, 2010 7:35 AM
 

SQLAJ said:

Aaron,

This will be nice to use especially since I have worked in Oracle and used this function regularly. Always wished SQL Server had it.

Thanks for the information.

Cheers!

November 15, 2010 12:17 PM
 

Gints Plivna said:

I'd also like to know is there any possibility whether we can use SQL Server sequences for multiple rows e.g. INSERT INTO aTable SELECT colmns, including sequence FROM anotherTable

And if possible could you measure timings here? Because when using procedures tooo much time is wasted for running t-sql instead of just plain set based SQL statement.

December 11, 2010 2:45 PM
 

AaronBertrand said:

Gints, for the first comment, there is no NEXT n VALUES FOR.  It would be nice if there was a better construct to grab multiple rows from a sequence.  I'll play with some experiments though and perhaps post another entry because I think there are some workarounds.

As for your second comment, not sure what you mean.  Do you mean measure the time it takes to write code that uses sequence compared to a plain set-based SQL statement? What plain set-based SQL statement? Or do you mean the time it takes to wrap "CREATE PROCEDURE" around your SQL statement? I did measure timings above, comparing sequence to other methods (identity, homemade increment).  You need to be more descriptive.

December 11, 2010 3:08 PM
 

Aaron Bertrand said:

One complaint about SEQUENCE is that there is no simple construct such as NEXT (@n) VALUES FOR so that

December 13, 2010 7:58 AM
 

Gints Plivna said:

If there isn't any possibility to get sequence value for each inserted row in an insert statement, which inserts many rows in one go then there isn't much sense, but if it is possible then I would like to know is there any difference between these two (and I mean just execution time):

INSERT INTO tableX SELECT seq.nextval, tableYcolumns FROM tableY

vs

INSERT INTO tableX SELECT tableYcolumns FROM tableY (and identity column is populated automatically).

Because when testing using a procedure, which for example opens cursor to select rows from tableY and then inserts row by row into tableX it takes too much time to process cursor and loop and sequence weakness or strength is lost into this row by row (or rather we can say slow by slow :) approach.

December 25, 2010 11:49 AM
 

AaronBertrand said:

Gints, I'm not sure why you are opening a cursor to insert rows into another table one by one, but I suspect that the difference between a sequence and identity is not going to magically make this fast.

December 25, 2010 12:05 PM
 

Mark Freeman said:

I would be curious as to the performance or other advantages to SEQUENCE vs. IDENTITY when used for the same purpose as IDENTITY. In other words, why would someone want to switch an IDENTITY column to one populated using a SEQUENCE (including the default constraint that I assume would be needed)?

April 21, 2011 12:11 PM
 

Chris Testa-O'Neill said:

I am at the beginning of touring user groups around the UK so on train journeys back from a user group

May 19, 2011 9:04 AM
 

sqljunkieshare said:

how to implement, managing sequences and performance which is better identity or sequences please use the below link

http://sqljunkieshare.com/2011/12/11/sequences-in-sql-server-2012-implementingmanaging-performance/

December 11, 2011 10:58 AM
 

Richard Collette said:

Another benefit of sequences vs. the incrementing method is that the incrementing methods causes transactions to become serialized.  If you're doing any kind of batch update, the sequence table is going to be locked until that batch finishes and everything else waits.  Sequences don't affect transactions in this manner.   Your performanced analysis doesn't show the benefits when there are multiple simultaneous transactions occurring.  In this case the performance difference is huge.

January 13, 2012 2:40 PM
 

AaronBertrand said:

Thanks Richard, it's a good point. I wanted to keep the initial performance analysis simple, especially since this was a very early beta. I do have a more thorough blog post planned for RTM or something much closer to it.

January 13, 2012 2:53 PM
 

Timo Partanen said:

Thanks for the great post. Does someone know when SQL Azure Database will support sequences?

February 3, 2012 5:53 AM
 

Bala said:

Very Nice writeup for SEQUENCE

February 8, 2012 12:05 AM
 

Speedbird186 said:

It seems like SQL Server 2012 RTM no longer supports UNION or UNION ALL with the NEXT VALUE FOR function:

Msg 11721, Level 15, State 1, Line 3

NEXT VALUE FOR function cannot be used directly in a statement that uses a DISTINCT, UNION, UNION ALL, EXCEPT or INTERSECT operator.

April 30, 2012 11:10 PM
 

AaronBertrand said:

Speedbird186 yes, this is true, some of the syntax that was allowed in previous betas was cut between RC0 and RTM. You can see the final list of restrictions in the updated BOL docs.

http://msdn.microsoft.com/en-us/library/ff878370.aspx

April 30, 2012 11:19 PM
 

Bill Anton said:

@Mark Freeman - "I would be curious as to the performance or other advantages to SEQUENCE vs. IDENTITY when used for the same purpose as IDENTITY. In other words, why would someone want to switch an IDENTITY column to one populated using a SEQUENCE"

In the use case you describe, IDENTITY does appear to outperform sequence...at least in my test cases (http://byobi.com/blog/2012/09/sequence-vs-identity-performance-comparison/)...so the flexibility provided by a sequence object comes at a (slight) performance cost.

September 26, 2012 3:38 PM
 

Ed Lawton said:

If you simplify how you use it, you'll find the SEQUENCE object is significantly faster. John talks about it here:

http://www.sqlnotes.info/2011/11/18/sql-server-sequence-internal/

Have you tried setting a default of the id column with "next value for XX"?

August 13, 2013 5:29 AM
 

Khourshed said:

I have tried to assess performance with a table, a bit different than yours:

CREATE TABLE [dbo].[cms_tblTest](

[ID] [bigint] NOT NULL,

[TEXT] [nvarchar](250) NULL,

CONSTRAINT [PK_cms_tblTest] PRIMARY KEY CLUSTERED

(

[ID] ASC

)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

) ON [PRIMARY]

GO

/****** Object:  Sequence [dbo].[cms_sqTest]    Script Date: 06.01.2014 8:52:04 ******/

CREATE SEQUENCE [dbo].[cms_sqTest]

AS [bigint]

START WITH 0

INCREMENT BY 1

MINVALUE 0

MAXVALUE 9223372036854775807

CACHE  20

GO

against identity table:

CREATE TABLE [dbo].[cms_tblTestClassic](

[ID] [bigint] IDENTITY(1,1) NOT NULL,

[TEXT] [nvarchar](250) NULL,

CONSTRAINT [PK_cms_tblTestClassic] PRIMARY KEY CLUSTERED

(

[ID] ASC

)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

) ON [PRIMARY]

GO

testing scripts:

for seq table:

declare

@pI int =0,

@pId bigint,

@pText nvarchar(250),

@pDate datetime = getdate()

while @pI<100000

begin

select @pId = next value for dbo.cms_sqTest

set @pText = 'Text'+convert(nvarchar(100),@pI)

insert into cms_tblTest

([ID],[TEXT])

values

(@pId,@pText)

set @pI =@pI+1

end

select convert(time,getdate()-@pDate)

for identity table:

declare

@pI int =0,

@pText nvarchar(250),

@pDate datetime = getdate()

while @pI<100000

begin

set @pText = 'Text'+convert(nvarchar(100),@pI)

insert into cms_tblTestClassic

([TEXT])

values

(@pText)

set @pI =@pI+1

end

select convert(time,getdate()-@pDate)

and results are:

Identity table: 00:00:24.7830000

seq table:00:00:25.1330000

January 5, 2014 11:58 PM
New Comments to this post are disabled

About AaronBertrand

...about me...

This Blog

Syndication

Archives

Privacy Statement