Create Table with Identity in SQL Server

In this article I will show you how to create a table with Identity Column.

Let’s create and examine the STUDENT table.

CREATE TABLE STUDENT
( STUDENT_ID INT IDENTITY(1,1),
FIRST_NAME VARCHAR(30),
LAST_NAME VARCHAR(30),
SCHOOL_NAME VARCHAR(30)
)

If you look at the CREATE TABLE statement, just besides to the Datatype column i specified IDENTITY word with 1,1.

The general syntax of identity is
IDENTITY(Start_Num,Increment_Val).

When we insert the data, It starts with value 1 and increment it by 1 for every insert.

Its very easy. isn't it :)

Use the below SQL to Find all the IDENTITY columns with in a database:

Select table_name , column_name
From information_schema.columns
where
columnproperty (object_id (table_name), column_name, 'IsIdentity') = 1

In my next article I will get into more details….until then keep watching..DotNetVJ Chanel….