SQL Server : create a column as an Identity that r

问题:

In SQL Server 2008 R2 I know how to create a primary key column that keeps my lines unique (column3 in my example below), but I also need a column that auto-fills a repeating set of integers (column2 in my example below).

I don't know how to create a column like this. Searches suggest it can be done with "reseeding", but not sure how to actually a create this column or if its even possible to automatically do this.

I'm looking for something like this, with Column2 being auto-filled every time a new line is created:

Column1   Column2  Column3
name1     1        1
name2     2        2
name3     3        3
name4     4        4
name5     5        5
name6     1        6
name7     2        7
name8     3        8
name9     4        9
name10    5        10
name11    1        11
name12    2        12

回答1:

You could use a persisted column by doing something along these lines:

ALTER TABLE Table_Name ADD Column2 AS (Column3 % 5)

While I think this will give you 0-4 instead of 1-5, it should put you on the right track to do some mathyness on Column3 to create the column you are looking for.

For a constantly updating answer that updates if rows are removed, you could do something like this:

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE FUNCTION dbo.GetTableValue
(
    @Index int
)
RETURNS int
AS
BEGIN
    DECLARE @Result int
    SELECT @Result = COUNT(*) % 5
        FROM dbo.TABLE
        WHERE PrimaryKey < @Index

    RETURN @Result
END
GO

ALTER TABLE dbo.TableName ADD CalcValue AS (dbo.GetTableValue(PrimaryKey));

That query isn't fully tested but should put you on the right path. Once again, this will hurt performance in a larger database due to the query executing on every read from the DB.

回答2:

If you are using SQL Server 2012 or 2014, you can (and should) use a SEQUENCE:

CREATE SEQUENCE dbo.FiveCount
  AS tinyint
  START WITH 1
  INCREMENT BY 1
  CYCLE
  MINVALUE 1
  MAXVALUE 5
;

Then define the next value of the SEQUENCE as the default value of the column:

CREATE TABLE test_table
    ([Column1] varchar(6), 
     [Column2] int CONSTRAINT DF_testTable_col2 DEFAULT NEXT VALUE FOR FiveCount, 
     [Column3] int)
;

Here is a SQLFiddle of this SEQUENCE being created, followed by a series of inserts, then a select all to show the results of the insertions.

EDIT: Note that inserting the results into the table like this means that the sequence will be broken if a row is deleted.

A more dynamic approach might be something like the following (assuming the sequence is already in place):

CREATE TABLE test_table (
     [Column1] varchar(6), 
     [Column3] int
);

Then, when you want to select from the table:

SELECT
  Column1,
  NEXT VALUE FOR FiveCount AS Column2,
  Column3
FROM test_table

This query would ensure that you always get an unbroken sequence, regardless of the state of test_table

If it is important that the SEQUENCE always begin with 1, you can RESTART it like so:

ALTER SEQUENCE dbo.FiveCount RESTART WITH 1

Here's a SQLFiddle of the SEQUENCE being restarted, followed by the select query, followed by a deletion from test_table, followed by another instance of the restart and select query.

回答3:

Instead of persisting the sequence, can you set the sequence when you query the data? This would likely be much better for server health if your table is heavily used. This would also allow you to always have consistent sequences in your data.

If you need a 1-5 sequence, use a case statement along with your modulus:

Select
    Column1
    ,Case When Row_number() Over(order by Column1)%5 =0 
          Then 5 
          Else Row_number() Over(order by Column1)%5 
    End as Column2
    ,Row_number() Over(order by ID) as Column3
From TableName