Results 1 to 10 of 10
Thread: Auto Increment MSSQL
-
08-03-2005, 02:53 PM #1WHT Addict
- Join Date
- Sep 2002
- Posts
- 115
Auto Increment MSSQL
I am stuck and need the expertise of you folks. I am trying to have one of my rows inside a table to auto increment the default value that I would like it to start off as is: 0001 to 9999
Should the data type for this row be an int or numeric?
Any help would be greatly appreciated.
-
08-03-2005, 03:03 PM #2Disabled
- Join Date
- Jul 2005
- Posts
- 141
I'm not sure that would work, and if it doesn't you can always use PHP to parse it like that.
Try using varchar for that.
-
08-03-2005, 03:17 PM #3WHT Addict
- Join Date
- Sep 2002
- Posts
- 115
Sadly this application is ASP based. And I don't want to muck around too much with it.
-
08-03-2005, 04:00 PM #4Disabled
- Join Date
- Jul 2005
- Posts
- 141
Oh, sorry about that. I misread your title. Best of luck with that though.
-
08-03-2005, 04:45 PM #5WHT Addict
- Join Date
- Mar 2004
- Location
- california
- Posts
- 162
If the column doesn't exists, just use this sql:
alter table <table> add <column name> int identity(1,1) not null
If it exists, just do:
alter table <table> drop column <column name>
Then use the alter table code shown above
-
08-04-2005, 07:15 AM #6WHT Addict
- Join Date
- Sep 2002
- Posts
- 115
I will give that a try, thanks unlucky1
-
08-04-2005, 09:41 AM #7Disabled
- Join Date
- Feb 2005
- Location
- Galveston, TX USA
- Posts
- 115
The data type of MSSQL identity field can be Int, BigInt and Decimal. In term of performance and storage, Int data type is better than others. You can get the difference from http://www.sqlteam.com/item.asp?ItemID=4123 .
-
08-04-2005, 10:04 AM #8WHT Addict
- Join Date
- Sep 2002
- Posts
- 115
I think I have it working, but I need the values to be: 0001-9999
When I add int identity(1,1) it starts at 1 and increments by one. What value should I use so it will use 0001?
identity(-0001, 1) brings it back to 1 when I save.
Thanks.
-
08-04-2005, 10:24 AM #9Disabled
- Join Date
- Feb 2005
- Location
- Galveston, TX USA
- Posts
- 115
There is no easy way to achieve this.
1) Use char or varchar data type as your key field, you need to automatically increment the value at your ASP code or use SQL Server triger.
2) Use identity(1, 1) in SQL database. But you can always display "1" as "0001".
3) Create another computed column and format your identity(1, 1) as "0001 - 9999".
ComputedColumn = Right('0000' + Cast(IdentityColumn As varchar), 4).
-
08-04-2005, 10:40 AM #10WHT Addict
- Join Date
- Sep 2002
- Posts
- 115
I'll give that a try, thanks. I am starting to loathe Accpac CRM more and more as the days go by.