March 24, 2005 at 9:24 pm
Previously, I'm using MySql as my database server.
Now, I need to change it to MS SQL server. But noticed that MS SQL does not have the AUTO_INCREMENT function as it's using IDENTITY.
But I need have the IDENTITY to auto number the secondary column for my table primary key as shown below.
How can I get MS SQL to perform the same function as it's in MySQL??
--------
For MyISAM and BDB tables you can specify AUTO_INCREMENT on a secondary column in a multiple-column index. In this case, the generated value for the AUTO_INCREMENT column is calculated as MAX(auto_increment_column)+1 WHERE prefix=given-prefix. This is useful when you want to put data into ordered groups.
INSERT INTO animals (grp,name) VALUES('mammal','dog'),('mammal','cat'),
('bird','penguin'),('fish','lax'),('mammal','whale'),
('bird','ostrich');
SELECT * FROM animals ORDER BY grp,id;
Which returns:
+--------+----+---------+| grp | id | name |+--------+----+---------+| fish | 1 | lax || mammal | 1 | dog || mammal | 2 | cat || mammal | 3 | whale || bird | 1 | penguin || bird | 2 | ostrich |+--------+----+---------+
March 25, 2005 at 7:03 am
MS SQL uses an INDENTITY "property" on an INT field for Auto_Increment
So it should look something like this when you create the field
Fieldname INT IDENTITY(1,1) -- this sets it to Auto Increment starting at 1 and icrementing by 1.
good luck
March 25, 2005 at 12:38 pm
DSP, Thanks for the help. But what I need is the auto increment of the secondary column.
I tried the method you suggested, but the value of the field keep incrementing regardless of the primary column.
eg. Both LID and SID are set as primary key. SID values are auto increment based on LID. i.e. If LID is the same, SID will increment. When LID changed, SID will reset back to 1.
+--LID--+--SID--+
+ 123 + 1 +
+ 123 + 2 +
+ 124 + 1 +
+ 124 + 2 +
+ 125 + 1 +
Anyone know the syntax to do it??
March 26, 2005 at 4:32 am
SQL Server has no built in facility to perform what you are after. You'll have to have table updates done through a stored procedure to ensure your rules are enforced, or create a trigger to perform the data calculations.
Some SQL code such as
select max(SID) + 1 from [myTable] where LID = @myLID
But, ensure that you wrap it up in some sort of serialised access. You can use the SET TRANSACTION ISOLATION LEVEL command (see your books online) or, in the above select statement, add a
with (holdlock)
to ensure that you lock the column as soon as you read it.
In any case, you should also create a single index (possibly clustered) on the both the SID and then the LID columns (in that order) to speed up the max calculation and presumably any lookups that you'll do on the data in future.
Hope this helps!
Ian
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply