Reset auto increment

问题:

My question starts here: How to setup auto increment for Service-Based Database

So if I have to go this way to reset auto increment after deleting a table row:

http://befused.com/mysql/reset-auto-increment

first time I have deal with T-SQL extension and SQL generally. What is wrong here, not sure if I got it right:

CREATE TABLE [dbo].[Tab1] (
    [Id]     INT  IDENTITY (1, 1) NOT NULL,
    [Phrase] TEXT NOT NULL,
    PRIMARY KEY CLUSTERED ([Id] ASC)
);

SELECT MAX( Id ) FROM [Tab1] ;
ALTER TABLE Tab1 AUTO_INCREMENT = number; 

got this errors:

Severity Code Description Project File Line Suppression State Error SQL80001: Incorrect syntax near ''. Expecting '.', ID, or QUOTED_ID. dbo.User 8

and:

SeverityCode Description Project File Line Suppression State Error SQL80001: Incorrect syntax near ''. dbo.User 7

回答1:

MYSQL

CREATE TABLE Tab1 ( 
    Id INT NOT NULL AUTO_INCREMENT, 
    Phrase TEXT NOT NULL, 
    PRIMARY KEY CLUSTERED (Id ASC) 
); 

ALTER TABLE Tab1 MODIFY COLUMN Id INT AUTO_INCREMENT // To set column as auto increment

MSSQL (In case someone needs it)

The create table syntax is OK but in creating auto increment column, you can add it like this

CREATE TABLE [dbo].[User] (
    [Id]     INT NOT NULL AUTO_INCREMENT PRIMARY KEY, // Set column as primary key and auto increment
    [Phrase] TEXT NOT NULL,
    PRIMARY KEY CLUSTERED ([Id] ASC)
);

SELECT MAX( Id ) FROM [User]; // You forgot the brackets in this part,Useris a reserved word in TSQL

回答2:

If you want to reseed to different number you can use below:

dbcc checkident(tab1, reseed, 100)