| Author |
Topic |
|
samitkumbhani
Starting Member
15 Posts |
 Posted - 2004-10-14 : 12:55:09
|
| Hi forum,I am novice in db design and just wanted to ask how to assign autonumber property for a column in db.In access there is a data type like autonumber am just looking for same alternative in SQL server.Thanks,Samit |
|
|
Seventhnight
Master Smack Fu Yak Hacker
2878 Posts |
Posted - 2004-10-14 : 13:01:05
|
the red starts it at 5... its usually 1 though...Create Table #blah (id int identity(5,1), blah varchar(10))Insert Into #blahSelect 'blah1'Union Select 'blah2'Select * From #blahdrop table #blah Corey |
 |
|
|
eyechart
Master Smack Fu Yak Hacker
3575 Posts |
Posted - 2004-10-14 : 13:48:19
|
| just to clarify corey's post, you are interested in an Identity Column. Check books online for "identity" or "identity column" for all the details.-ec |
|
|
|
samitkumbhani
Starting Member
15 Posts |
Posted - 2004-10-14 : 22:00:06
|
| Hi, thanks for the reply. what i was looking for is when u open a db in design view then how would you declare it as autonumber. I am not very much intersted in the sql way.Please let me knowsamit |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2004-10-14 : 22:05:06
|
| Highlight the column in design view. At the bottom of the screen, you'll see Identity. Type in Yes.Tara |
|
|
|
eyechart
Master Smack Fu Yak Hacker
3575 Posts |
Posted - 2004-10-15 : 01:11:47
|
| btw, It is all there for you in books online (BOL).-ec |
|
|
|
samitkumbhani
Starting Member
15 Posts |
Posted - 2004-10-15 : 08:40:50
|
| Hi,thanks to tara and all others.its working .regards,samit |
|
|
|
spirit1
Cybernetic Yak Master
11752 Posts |
Posted - 2004-10-15 : 10:24:42
|
quote:
Originally posted by samitkumbhaniI am not very much intersted in the sql way.
i just got to ask... WHY aren't you interested in "SQL" way?Go with the flow & have fun! Else fight the flow  |
|
|
|
samitkumbhani
Starting Member
15 Posts |
Posted - 2004-10-15 : 12:01:59
|
| Hi,Well i got the auto numberpart working. But now there is a new problem. Say if i insert a value into the db and delete this row then the value of autonumber field keeps increasing by 1.So now if i had 3 rows in the db and deleted them then again when i try to insert value into first row i get my identity field value as 4.I dont want this ti happen.Please give me a solution to this.samit |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2004-10-15 : 12:07:59
|
| You can use DBCC CHECKIDENT, but I wouldn't use it regularly. If you have some process to fix the identity values, that's going to be a serious performance hit since you'll have to update all of the child tables too. And what is the business reason why you don't want this to happen?Tara |
|
|
|
samitkumbhani
Starting Member
15 Posts |
Posted - 2004-10-16 : 04:24:42
|
| Hi tara,thanks for the prompt rreply. well i am trying to create a db for my unviersity and was just wondering if i could avoid incresing of the values automatically and was wondering if there is a way to stop it.If it is not then thats not a problem.....Thanks anywayssamit |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2004-10-16 : 05:03:16
|
| Is the ID to uniquely reference the record for all time (in which case you won't want it to change), or a Row Number to print on reports?If its a ROW NUMBER it would probably be better to generate it as part of the SELECT that creates the report ...Kristen |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2004-10-18 : 12:10:35
|
| There is no reason to stop it from incrementing.Tara |
|
|
|
samitkumbhani
Starting Member
15 Posts |
Posted - 2004-10-18 : 12:53:07
|
| Hi ,I am perfectly fine and things are working as of now.Thanks to all for the prompt reply.Regards,Samit |
|
|
|
|
auto number field + SQL server