FREE TRIAL  SQL 2012 PROGRAMMING  SEARCH
SQL Server Scripts SQL 2005 SQL 2008 Articles
SQL JOBS NEWS FORMAT DEV JOBS
How to apply identity for row sequencing?

Execute the following T-SQL example script in Microsoft SQL Server Management Studio Query Editor to demonstrate the use of identity function in select into temporary table.

-- SQL Server identity function  - identity( data type, starting seed, increment)

-- Select into temporary table - SQL autonumber – sequence number

USE pubs;

 

SELECT   EmployeeNumber = emp_id,

         FirstName = fname,

         MiddleInitial = minit,

         LastName = lname,

         JobNumber = IDENTITY(SMALLINT,100,1),

         JobLevel = job_lvl,

         PublicationID = pub_id,

         HireDate = convert(CHAR(10),hire_date,112)

INTO     #Employee

FROM     employee

ORDER BY LastName,

         FirstName

GO

 

SELECT *

FROM   #Employee

GO

/* Partial results

 

EmployeeNumber    FirstName   MiddleInitial     LastName    JobNumber

PMA42628M         Paolo       M                 Accorti     100

PSA89086M         Pedro       S                 Afonso      101

VPA30890F         Victoria    P                 Ashworth    102

H-B39728F         Helen                         Bennett     103

L-B31947F         Lesley                        Brown       104

F-C16315M         Francisco                     Chang       105

PTC11962M         Philip      T                 Cramer      106

A-C71970F         Aria                          Cruz        107

*/

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

Related articles:

IDENTITY (Function) (Transact-SQL)

The behavior of the IDENTITY function when used with SELECT INTO or INSERT .. SELECT queries that contain an ORDER BY clause

 

Exam Prep 70-461
Exam 70-461