Auto Increment Field or Identity Field In MSSQLSrv

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Auto Increment Field or Identity Field In MSSQLSrv

Author

Topic

akhil
Starting Member

1 Post

Posted - 2011-03-05 : 00:57:02

Creating a Auto Increment field in SQL Server allows a unique number to be generated when a new record is inserted into a table.

Syntax for creating an Auto Increment field in SQL Server.

CREATE TABLE [dbo].[Company](
[CompanyId] [bigint] IDENTITY(1,1) NOT NULL,
[Name] [nvarchar](200) NOT NULL,
[Address] [nvarchar](max) NOT NULL,
[Phone] [nvarchar](max) NULL,
[autoBackup] [bit] NOT NULL,
[applycreditlimit] [bit] NULL,
[EmailId] [nvarchar](max) NULL,
[salesTaxPercentage] [numeric](18, 2) NULL)
Here, CompanyId has been set as an Auto Increment Id.
A column which has datatype int, big int, tiny int or small int can only be set as an Auto Increment field.
Suppose, you would like to set the AutoIncrement field (CompanyId) at the remote databases to only use the next number free AFTER the 100.

Here, CompanyId has been set as an Auto Increment Id.
A column which has datatype int, big int, tiny int or small int can only be set as an Auto Increment field.
Suppose, you would like to set the AutoIncrement field (CompanyId) at the remote databases to only use the next number free AFTER the 100.

There’s an “Identity Seed” parameter that you can specify (either through SQL or through the table designer in Enterprise Manager) that will let you set the base value for an Identity/AutoNumber field to the value you want. Any new Identity values will be incremented from this base (using the “Identity Increment” value which you can specify in the same place to determine how much to grow the value by).

If you would like to see a video as to how to set the auto increment field in SQL Server, then we have a video tutorial uplaoded at:

http://www.industrialtrainingkolkata.com/?cat=1
[url][/url]

jcelko
Esteemed SQL Purist

547 Posts

Posted - 2011-03-05 : 14:33:53

The design of this example table is wrong in so many ways. Companies are universally identified by their DUNS. We do not use BIT flags in RDBMS; that was assembly language. ISO-11179 rules for data element names were NOT followed. There are no constraints or keys. VARCHAR() columns are all over-sized, inviting garbage data. Why did you use NUMERIC and not DECIMAL? T-SQL treats them alike, but other SQLs do it right and we want to write the most portable code we can.

Here is a quick attempt to patch this thing.

CREATE TABLE Companies
(duns CHAR(10) NOT NULL PRIMARY KEY
CHECK (duns LIKE '[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]'.
company_name VARCHAR(35) NOT NULL,-- postal standard
san CHAR(10) NOT NULL, –- standard address number
phone_nbr CHAR(18), –- international standard
email_addr VARCHAR(255) , –- international standard
salestax_percentage DECIMAL (5, 3) DEFAULT 0.000 NOT NULL) ;

A properly designed table will never use the IDENTITY property; it is not relational or logical. It is a count of PHYSICAL insertion attempts.

--CELKO--
Books in Celko Series for Morgan-Kaufmann Publishing
Analytics and OLAP in SQL
Data and Databases: Concepts in Practice
Data, Measurements and Standards in SQL
SQL for Smarties
SQL Programming Style
SQL Puzzles and Answers
Thinking in Sets
Trees and Hierarchies in SQL

mmarovic
Aged Yak Warrior

518 Posts

Posted - 2011-03-06 : 06:26:04

quote:
Originally posted by jcelko
A properly designed table will never use the IDENTITY property; it is not relational or logical.

Why?

quote:
It is a count of PHYSICAL insertion attempts.

True, anything wrong with that?

Mirko

My blog: http://mirko-marovic-eng.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources