SQL Server 2000General discussion of Microsoft SQL Server -- for topics that don't fit in one of the more specific SQL Server forums. version 2000 only. There's a new forum for SQL Server 2005.
Welcome to the p2p.wrox.com Forums.
You are currently viewing the SQL Server 2000 section of the Wrox Programmer to Programmer discussions. This is a community of tens of thousands of software programmers and website developers including Wrox book authors and readers. As a guest, you can read any forum posting. By joining today you can post your own programming questions, respond to other developers’ questions, and eliminate the ads that are displayed to guests. Registration is fast, simple and absolutely free .
There is autonumber datatype (option etc) available in sql server ?
I want one field which datatype autonumber, it
will automatically insert number when data insert on each record.
1, 2 , 3, 4,.... and so on
just like in ACCESS database there is autonumber datatype in sql server ?
You'd need rather a lot of data to exhaust an int (from bol):-
[u]bigint</u>
Integer (whole number) data from -2^63 (-9223372036854775808) through 2^63-1 (9223372036854775807).
[u]int</u>
Integer (whole number) data from -2^31 (-2,147,483,648) through 2^31 - 1 (2,147,483,647).
Using decimal is a waste of space (literally!) because it uses extra bytes to hold precision and scale information. If you really are going to outgrow the basic int, use bigint instead.
HTH
Chris
There are two secrets to success in this world:
1. Never tell everything you know
quote:Originally posted by rgerald
Not only will the decimal (floating point) numbers require more storage space, they will also process slower (floating point math vs. integer math).
A decimal number requires 1 extra byte to store precision and scale information. The number of bytes required to represent a given value varies with the precision of the number itself and can vary from 5 to 17 bytes (including the 'overhead' byte). Thus, it may very well require less storage space than other representations.
Decimal numbers are also not floating point numbers - they are stored internally as scaled integers and are thus exact representations of a value, unlike floating point. Since they are scaled, though, arithmetic operations require software handling (to manipulate the decimal point), so they are indeed slower than integers, but you shouldn't be doing much with arithmetic operations in the database anyway...
BTW, have you measured the performance of the floating point processor on Intel machines these days? You may be surprised at how few cycles it takes for floating point math (admittedly still far slower than integer operations).
My biggest problem with the decimal datatype is the relatively poor client support for that type.
quote:Originally posted by Canuck
Make your field an "int", and then in the column attributes, make it an identity field (set it to "yes" - no quotes).
Hi,
Sorry, I have one problem on autogenerating ID too. I'm using Microsoft SQL and my IDs are in A001, A002, A003...and so on. How can I auto generate this IDs? Like A001 +1=A002? Please help...
Thanks
Lynn
...And I assume that when you get to A999 and insert one more record, it should roll over to B000, huh?
Ok, in order to keep this from becoming "Death by SQL", here's how to do it... your table should have two column in it that are very similar to the ID and AlphaID columns... like this...
Code:
CREATE TABLE AlphaID
(
ID INT IDENTITY(0,1) PRIMARY KEY CLUSTERED ,
AlphaID AS CHAR(ID/1000+65)+REPLACE(STR(RIGHT(ID,3),3),' ','0'),
SomeOtherCol VARCHAR(20)
)
ALTER TABLE dbo.AlphaID
ADD CONSTRAINT CK_AlphaID_MaxID
CHECK (ID < 26999)
The key here is two things... the ID column and the calculated column called AlphaID. Neat formula, huh?
And, to demo the table in the code above, let's do 10,000 quick little inserts and then select from the table... don't blink or you'll miss it...
Code:
INSERT INTO AlphaID (SomeOtherCol)
SELECT TOP 10000 'TestOnly'
FROM Master.dbo.SysColumns sc1,
Master.dbo.SysColumns sc2
SELECT * FROM AlphaID
--Jeff Moden
p.s. The first number is A000 and the rollover numbers are B000, C000, D000, etc. It would be a fair bit more difficult to generate rollover numbers that were B001, C001, D001, etc.
autonumber in sql server
Linear Mode
