March 3, 2011 at 11:47 pm
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:
March 4, 2011 at 12:25 am
while inserting dont use the column which is identity specific.
for eg: here company id is specified as auto-incremental.
insert into tablename (companyname,Companyaddress) VALUES(@cname,@cadd).
This will auto increment a number in the company id column.
In update scenarios, we can refer the identity column for updations.
March 4, 2011 at 12:34 am
akhil 46199 (3/3/2011)
A column which has datatype int, big int, tiny int or small int can only be set as an Auto Increment field.
Quoting BOL directly:
The IDENTITY property can be assigned to tinyint, smallint, int, bigint, decimal(p,0), or numeric(p,0) columns
Furthermore, the IDENTITY property is strictly speaking not an Auto Increment field, as you can set the increment value to a negative value, making it in fact an Auto Decrement field. Auto Increment field is MS Access terminology.
I also checked your advertisement-filled website, where you suggest people to use the @@identity function (function, not keyword) to retrieve the last inserted identity value. What you've forget to mention is that @@identity is linked to the current session, but not to the current scope. This can lead to erroneous results on high concurrency systems. Therefore it is prefered to use SCOPE_IDENTITY instead.
There is no harm in trying to educate people, kudos for that, but at least get the facts straight.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply