It is very hardly when we need to retain sequence globally. We can use identity property of column but it is limited to that table only.

SQL Server 2011 has introduced new feature named "SEQUENCE". We can retain the global sequence and can be used in multiple tables or in any queries.
It's really global sequence generator and alternative option of Identity columns.

Let's see the simple example using it.

Creating database and table objects.

-- Creating database
CREATE DATABASE SEQUENCEDB
GO
USE SEQUENCEDB
GO
-- Creating table
IF( OBJECT_ID('SequenceTab') > 0 )
  DROP TABLE SequenceTab
GO
CREATE TABLE SequenceTab
  (
     Seq_Id INT,
     Name   VARCHAR(20)
  )
GO 

Now we will create a Sequence.

-- Creating Sequence

IF EXISTS (SELECT 1 FROM SYS.SEQUENCES WHERE NAME = N'NewSequence')
DROP SEQUENCE NewSequence;
GO

CREATE SEQUENCE [NewSequence] AS INT
START with 1
INCREMENT by 1
MAXVALUE 1000
GO

You can create sequence in Object Explorer,

Let us insert some records in table using sequence and see the data in table.

-- Inserting records using Sequence

INSERT INTO SequenceTab
SELECT next value for [NewSequence], 'Sequence-1'
UNION ALL
SELECT next value for [NewSequence], 'Sequence-2'
UNION ALL
SELECT next value for [NewSequence], 'Sequence-3'
UNION ALL
SELECT next value for [NewSequence], 'Sequence-4'
UNION ALL
SELECT next value for [NewSequence], 'Sequence-5'
UNION ALL
SELECT next value for [NewSequence], 'Sequence-6'
GO

-- Records in table 

SELECT Seq_Id,Name FROM SequenceTab
GO

Now we will alter the sequence and reset to 1 and then review the next sequence to be generated.

-- Reset the sequence , starting from 1

ALTER SEQUENCE [NewSequence]
RESTART WITH 1
GO

Let's see the next values from sequence from first run.

SELECT
next value for [NewSequence] as NeSequence
,*
FROM SYS.DATABASES
GO

Next values for sequence from second run.

SELECT 
next value for [NewSequence] as NeSequence
,* FROM SYS.DATABASES
GO