I'm using SQL Server 2014 with Visual Studio 2013. When I add a new record, identity column (seed 1, increment 1) "ID" increases by 1, and record added to the database.
My table consist of two rows:
ID, set identity, seed=1, increment=1 Column1, int Column2, nvarchar(10), Primary Key
ID Column1 Date 1. 1 300 01.01.2001 2. 2 301 02.01.2001
My query is as follows:
insert into Table values(302,'02.01.2001')
Clearly it gives an error, because 02.01.2001 already exists.
insert into Table values(302,'03.01.2001')
successfully inserts data.
But when I select:
select * from Table
the result is:
ID Column1 Date 1. 1 300 01.01.2001 2. 2 301 02.01.2001 3. 4 302 03.01.2001
Sometimes after this unsuccessful attempts of inserting data, the ID increases but the data doesn't recorded to data, because I have made a mistake. (Like I show above.) Then, after a successful insert query, data records and ID jump to 4 instead of 3.
I have thought of a workaround (it works from my point of view):
declare @maxIDValue int if exists (select ID from NVDB.dbo.V where ID=1) --check if table has its first record. begin if (select count(ID) from NVDB.dbo.V) >= 1 set @maxIDValue = (SELECT MAX(ID) FROM NVDB.dbo.V) end else set @maxIDValue = 1 if not exists (select Date from NVDB.dbo.V where Date='03.10.2010') begin dbcc checkident ('NVDB.dbo.V', RESEED, @maxIDValue) insert into NVDB.dbo.V values(300,'03.10.2010') end SELECT * FROM NVDB.dbo.V
With this piece of code, I want my table in order and after the insertion attempts. It gives no error, and of course ID goes sequentially.
Is this a right approach? What is the best practice? Do we have a simple way to achieve this or did I write so much code?
Note: My table is small, so performance is not an issue. But when it becomes big, like 3000 records, does this code affect performance? (I'll be using these queries in my C# application.)