CODEWITHSUNDEEP
sqlsql-serversql-server-2008
Rizwan606
Rizwan606

Reputation: 667

Two identity columns in SQL Server 2008

I am creating a table in SQL Server with three columns. I want the first two columns to increment counts one by one. I used the first column as identity column that automatically increments 1 when row is inserted. I tried to do the same with second column (but SQL Server does not allow two identity columns per table). I found another way i-e using Sequence but since I am using SQL Server 2008, it does not have this feature.

How can I achieve my task?

I am using the first identity column in reports and I reset it when some count is achieved; for example when it reaches 20, I reset it to 1 (Edited). The second incremental column will be used by me for sorting the data and i do not intend to reset it. 

create table tblPersons
(
    IDCol int primary key identity(1,1),
    SortCol int primary key identity(1,1),
    Name nvarchar(50)
)

PS : I cannot copy the values of IDCol to SortCol because when I reset the IDCol to 20 from my code, the SortCol will copy the same values (instead it should continue to 21,22,23 and so on)

Upvotes: 3

Views: 437

Answers (2)

Serkan Arslan
Serkan Arslan

Reputation: 13403

As an alternative solution, you can use Computed Column

create table tblPersons
(
    SortCol int primary key identity(1,1),
    IDCol AS CASE WHEN (SortCol % 20) = 0 THEN 20 ELSE (SortCol % 20) END ,
    Name nvarchar(50)
)

Upvotes: 1

Tim Biegeleisen
Tim Biegeleisen

Reputation: 522741

If you plan on incrementing both columns always at the same time, then one workaround here might be to just use a single auto increment column, but use the remainder of that counter divided by 20 for the second value:

CREATE TABLE tblPersons (
    IDCol int PRIMARY KEY IDENTITY(1,1),
    Name nvarchar(50)
)

SELECT
    IDCol,
    IDCol % 20 AS SortCol    -- this "resets" to zero upon reaching 20
FROM tblPersons;

Upvotes: 3

Related Questions