Reset auto increment

Question

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

Are you actually running `ALTER TABLE User AUTO_INCREMENT = number;` or you're replacing "number" by the value you've found from the first query (the MAX(id) one)? — Kinetic, Aug 26 '16 at 01:35
Not every SQL is the same. Be sure to [read the documentation on MySQL](http://dev.mysql.com/doc/refman/5.7/en/) before trying things randomly and praying they'll work. — tadman, Aug 26 '16 at 01:42
Resetting the auto-increment after every row deletion sounds like a bad idea to me. — Kinetic, Aug 26 '16 at 01:47
If you're resetting the auto-increment after every row deletion, you're doing something wrong. If you don't need an auto-increment column, don't use an auto-increment column. If you do need it, stop managing it yourself and let the DB do it (that's what the *auto* in the name means). *Auto-increment* does **not** mean *a value I manage myself in my code*; it means *a value that the DB manages automatically when inserting a row*. — Ken White, Aug 26 '16 at 02:39
@Ken White yes but if farther I want use index of row with described desired order rule for some calculations to read line from my database, is there any way to figure out without index, if I need numbers sequence? — , Aug 26 '16 at 02:45
You're looking for a sequence number, which is not an auto-increment column. As I said, if you want to manage the number yourself, don't use an auto-increment column and do the management yourself. You've done nothing but put a lot of work onto the server just to make the server do it over and over and over again for no reason. You need to read again what I wrote about the meaning of **auto**. — Ken White, Aug 26 '16 at 02:47
Think about this: You have a million rows in your DB, and you delete row 999,000. You're making the DB server renumber rows starting with row 1 and continuing through 999,999 to reset the autoinc value. You then delete row 999,000 again, and the server has to go through 999,998 rows again to renumber. This is absolutely wrong. — Ken White, Aug 26 '16 at 02:51
@Ken White yes seems wrong as a huge boulder for one row. can you give me some direction or example how to get sequence number working this way and must be attached to the taken line — , Aug 26 '16 at 03:06
No, because that's not what your question asks. If you have a new question, create a new post and ask it there. (A quick take: Get the seq # of the row you're deleting. Delete it. Update all rows with a number > the seq you saved with their current value - 1. Done. On an insert, you do the inverse: Find the position where you need to insert. Increase the seq # for that row and all that follow by updating with their current value + 1. Insert the new row and give it the proper seq #. Stored procedures can do all of this for you.) — Ken White, Aug 26 '16 at 03:07
Please fix your tags: "t-sql" is the SQL version of SqlServer (see tag description on hover), but you also talk about MySql - which is it? Also "C#" is nowhere in your question. — Hans Keﬆing, Aug 26 '16 at 11:26
The link you are reading is about MySQL but the syntax you are using suggest that you are using SQL Server which is something completely different. — , Aug 26 '16 at 11:41

Anonymous Duck · Answer 1 · 2016-08-26T03:46:39.527

0

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

edited Aug 26 '16 at 03:46

answered Aug 26 '16 at 01:35

Anonymous Duck

2,942
1
12
35

TSQL? The question is about MySql. – Kinetic Aug 26 '16 at 01:38
@Kinetic The original question is very confused about what it is. – tadman Aug 26 '16 at 01:41
the create table syntax is basically the same – Anonymous Duck Aug 26 '16 at 01:42
It is a confusing question. Still not sure if he's trying to create a new table or just reset the auto increment on an existing one. – Kinetic Aug 26 '16 at 01:45
@Sherlock I’ve tried, but seems like need different syntax… I I’ve tried also this way, but same errors: `CREATE TABLE [dbo].[Tab1] ( [Id] INT IDENTITY (1, 1) NOT NULL, [Phrase] TEXT NOT NULL, PRIMARY KEY CLUSTERED ([Id] ASC) ); GO SELECT MAX( Id ) FROM [Tab1] ; ALTER TABLE Tab1 AUTO_INCREMENT = number;` – Aug 26 '16 at 02:37
@Kinetic yes confusing it is because table name is also user, so I have edited above. I don’t want create new table, just want get this result http://stackoverflow.com/questions/39155743/how-to-setup-auto-increment-for-service-based-database – Aug 26 '16 at 02:37
@Tadman Yes, because starts here: http://stackoverflow.com/questions/39155743/how-to-setup-auto-increment-for-service-based-database – Aug 26 '16 at 02:38
@MolC I think your database is not MYSQL, Because you are using `IDENTITY(1,1)` so this is specific for `MSSQL` and the equivalent `MYSQL` command for this is `AUTO_INCREMENT`. For MSSQL see the MSSQL (In case someone needs it) – Anonymous Duck Aug 26 '16 at 03:47
1

@MolC "MSSQL" refers to "Microsoft SQL Server". "MySQL" is another incompatible database. It's not from "Mycrosoft". They have different commands, syntax, and a lot of other differences that are very important. Which one are you using? The error you're getting suggests Microsoft SQL Server. – tadman Aug 26 '16 at 05:00
yes it is a T-SQL, but this way I got error SQL80001: `CREATE TABLE [dbo].[User] ( [Id] INT NOT NULL AUTO_INCREMENT PRIMARY KEY, [Phrase] TEXT NOT NULL, PRIMARY KEY CLUSTERED ([Id] ASC) );` – Aug 26 '16 at 11:35
@MolC It's "Microsoft SQL Server". T-SQL is the dialect of SQL used by that product. Try to tag your questions as narrowly as possible to avoid confusion. – tadman Aug 26 '16 at 18:33

score 0 · Answer 2 · answered Aug 26 '16 at 18:04

0

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

dbcc checkident(tab1, reseed, 100)

answered Aug 26 '16 at 18:04

Kannan Kandasamy

13,405
3
25
38

Reset auto increment

2 Answers2

Linked