Auto Increment Option in SQL Server Management Studio Express

Question

Auto Increment Option in SQL Server Management Studio Express

ns22boss

Mr or Mrs. 500

Points: 563
More actions
December 13, 2011 at 7:51 am

#264733

Hi,
I want to auto increment my ID field.. Is there any option Like Auto - Increment in SQL Server Management Studio Express.
Thanks

Viewing 12 posts - 1 through 11 (of 11 total)

You must be logged in to reply to this topic. Login to reply

GSquared SSC Guru Points: 260824 More actions · Answer 1

Are you designing a table in the GUI? If so, set the column to an Identity. You do that in the Column Properties at the bottom of the screen. Expand the Identity Specification and set Is Identity to "Yes".

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

Lowell SSC Guru Points: 323518 More actions · Answer 2

in SQL, that's the IDENTITY() property;

you can script it like this:

CREATE TABLE MYTABLE(

ID int IDENTITY(1,1) NOT NULL PRIMARY KEY,

OtherCollumns varchar(30 )

if you have SSMS for SQL Express, you can design the table and add the property there too.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

ns22boss Mr or Mrs. 500 Points: 563 More actions · Answer 3

ns22boss

Mr or Mrs. 500

Points: 563

December 13, 2011 at 8:04 am

#1420636

Thank u so much Gsquared.. Appreciate that..

GSquared SSC Guru Points: 260824 More actions · Answer 4

You're welcome.

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

ns22boss Mr or Mrs. 500 Points: 563 More actions · Answer 5

Hi,

For the first time when i run the query it is counting from 1 to n..

Next time when i run the console once again and execute the query, my id Value is not counting from 1, It is counting from n(previously where it is stopped) and incrementing thereafter...

How to resolve this one ??

Thanks

Devendrakumar SSC-Forever Points: 42493 More actions · Answer 6

ns22boss (12/14/2011)
Hi,
For the first time when i run the query it is counting from 1 to n..
Next time when i run the console once again and execute the query, my id Value is not counting from 1, It is counting from n(previously where it is stopped) and incrementing thereafter...
How to resolve this one ??
Thanks

SET IDENTITY_INSERT [ database_name . [ schema_name ] . ] table { ON | OFF }

As a side note you should avoid IDENTITY INSERTs. IDs should not be used for sequencing. There is ORDER BY clause for it.

ns22boss Mr or Mrs. 500 Points: 563 More actions · Answer 7

Hi Dev,

Should i write this Query inside Java Source or in SQL query pane when i execure the query ???

Thanks

Devendrakumar SSC-Forever Points: 42493 More actions · Answer 8

ns22boss (12/14/2011)
Hi Dev,
Should i write this Query inside Java Source or in SQL query pane when i execure the query ???
Thanks

I guess you missed the part which I added later.

'As a side note you should avoid IDENTITY INSERTs. IDs should not be used for sequencing. There is ORDER BY clause for it. '

ns22boss Mr or Mrs. 500 Points: 563 More actions · Answer 9

Hi Dev,

But i want the ID column should start from 1 and counts till there is no Record in Excel sheet. Since i am using Server Management Studio Express, by setting the Identity Specification value to 1 and every time i need to change the value, when i run the Java Console.

So i need to get rid of this one..

Pls help on this

s_osborne2 SSCertifiable Points: 6726 More actions · Answer 10

Hi,

If i understand it correctly you want to restart the identity counter?

See this: http://msdn.microsoft.com/en-us/library/ms176057.aspx

You can reseed the table to achieve what you are after.

Thanks,

Simon

MCSE: Data Platform
MCSE: Business Intelligence
Follow me on Twitter: @WazzTheBadger
LinkedIn Profile: Simon Osborne

ns22boss Mr or Mrs. 500 Points: 563 More actions · Answer 11

Hi,

It works fine if i use this query DBCC CHECKIDENT ( <table_name>,RESEED,<value>)

Thanks for all for your repsonse.

sathish.