Since this is more or less a copy of a previous article, I chose to copy my comment from that article:

First of all, I don't think this article is comprehensive enough, and as pointed out, it does not explain when to use (or not use) sequences. I've seen test showing that identity is outperforming sequence, so you should obviously not use a sequence when you simply could use an identity column in your table. However, identity is limited to a single table, whereas sequences can be shared across multiple tables. There are numerous cases where you want a unique identifier across tables in the same database. Prior to SQL Server 2012 you would have to manage this yourself, use an identity column in a shared "identity-table", or use uniqueidentifiers (GUIDs), which have several drawbacks. In these cases, a sequence is a far better choice in my opinion.

What is not mentioned in the article, but the MSDN article, is that you could use the next value for statement as default value in a table. The following simple code shows two tables sharing a sequence:

use tempdb;

go

create sequence MyIDs as int

start with 1

increment by 1

no cycle;

go

create table MyTable (

id int default next value for MyIDs,

value varchar(50)

);

create table MySecondTable (

id int default next value for MyIDs,

value varchar(50)

);

insert into MyTable (value) values ('Test');

insert into MySecondTable (value) values ('Test2');

select * from MyTable

select * from MySecondTable

go

drop table MyTable;

drop table MySecondTable;

drop sequence MyIDs

output:

id value

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

1 Test

id value

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

2 Test2