CODEWITHSUNDEEP
c#asp.netsql-servervisual-studio-2010
Ace Troubleshooter
Ace Troubleshooter

Reputation: 1379

How do I auto-increment a column in my table?

I'm building a database with a product instance table in Visual Studio2010 with Sql Server 2008, and I need to make the ProductId column autoincremented, but I cannot find the attribute in the column properties menu. I'm using c# and asp.net, if that is relevant. I've seen the code to create the table and set the column to autoincrement, but as this is my first go-round with coding, I don't know where to put the code. The only way I know to create a new table is through the VS gui, if that makes sense.

Upvotes: 16

Views: 68622

Answers (4)

David
David

Reputation: 73554

Set the Identity specification to yes

enter image description here

Sample SQL:

CREATE TABLE [dbo].[HomePageImages](
    [RecordId] [int] IDENTITY(1,1) NOT NULL,
    [AlternateText] [varchar](100) NOT NULL,
    [ImageName] [varchar](50) NOT NULL,
    [NavigateUrl] [varchar](200) NOT NULL,
    [ImageUrl]  AS ('/content/homepageimages/'+[ImageName]),
    [DisplayFrom] [datetime] NULL,
    [DisplayTo] [datetime] NULL,
 CONSTRAINT [PK_HomePageImages] PRIMARY KEY CLUSTERED 
(
    [RecordId] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

Upvotes: 32

KeithS
KeithS

Reputation: 71565

In SSMS, find your table in the Object Explorer, right/click, and select Design. Select the column you want to autoincrement, and look at the Column Properties section underneath. There should be an Identity Specification item with a [+] sign. Expand it, choose "Yes" for "Is Identity", and if necessary, set the increment (sometimes you may want the new value to be more than one more than the last) and the starting value as the "seed" (1 is fine for an empty table, but if you have existing data, set the seed to be greater than the greatest record).

Upvotes: 1

taylonr
taylonr

Reputation: 10790

In SQL server 08 you want to set the "Identity" property to 'Yes' and define it's initial value (defaults to 1) as well as its increment (also defaults to 1).

This will cause it to increment by 1 on every new record.

Upvotes: 6

James Kyburz
James Kyburz

Reputation: 14453

you need to use the identity yes property in sql.

It's in the property window at the bottom - sorry not running windows.

This will auto increment your id everytime a new record is inserted.

To get the id value you can use sqls SCOPE_IDENTITY() function to retrieve the id created.

Upvotes: 3

Related Questions