What Happens To Auto Increment, If we delete a row in SQL

Question

this is a beginner question. As i dont have access to Microsoft SQL Server right now. My Question is regarding the Auto Increment Feature.

http://s10.postimg.org/lev8tvu7t/image.jpg

Just take a look at the above table. Assume that the id field has given an Auto Increment option in Microsoft SQL Server. What happens if i delete the third row ?? I mean what happens to the numbering ? Will the ID 103 will be automatically changed to 102 ?? or it will stay the same, ommiting the '102' number ?

Also i have another question too...

http://s30.postimg.org/7exbjw7r5/image.jpg

In the above table, just assume that i have truncated all the fields. After that i inserted another set of values. What will happens to the ID (Auto Increment ON) field. What will be the ID of newly entered details ?? Will it be 105, 106 and so on or it will start again from 1 ?? (In the example shown the ID starts from 100, just think it starts from 1).

I also want to know what happens on both conditions.(ie, 1. on truncating table and 2. deleting all table details).

Answer 1

The rows you already inserted will remain the same. You could try it:

CREATE TABLE dbo.floob(id INT IDENTITY(1,1), name NVARCHAR(32));

INSERT dbo.floob(name) VALUES(N'bob'),(N'frank'),(N'KRIZTE');

DELETE dbo.floob WHERE id = 2;

SELECT id, name FROM dbo.floob;

Results:

id   name
--   ------
1    bob
3    KRIZTE

Now, second question, not quite sure what you mean by "truncated all the fields" - if you DELETE from the table, the IDENTITY sequence is maintained. e.g.:

DELETE dbo.floob;

INSERT dbo.floob(name) SELECT N'aaron';

SELECT id, name FROM dbo.floob;

Results:

id    name
--    -----
4     aaron

However, if you issue TRUNCATE TABLE, the IDENTITY sequence is reset.

TRUNCATE TABLE dbo.floob;

INSERT dbo.floob(name) SELECT N'bert';

SELECT id, name FROM dbo.floob;

Results:

id    name
--    ----
1     bert

For further info, please see the documentation and Google.