SQL SERVER – Add Auto Incremental Identity Column to Table After Creating Table

Question: Is it possible to add an auto incremental identity column to any table in SQL Server after creating a table.

Answer: There are two answers – No and Yes. Let us see them one by one.

Answer No – If you have an integer column in your table and you want to convert that column to identity table. It is not possible with the help of SQL Server. You will just have to add a new column.

Answer Yes – If you want to add new column to the table, it is totally possible to do so with the help of following a script.

ALTER TABLE YourTable ADD IDCol INT IDENTITY(1,1)

If you want to convert your old column to int column, may be you can drop that first and add a new column identity right after that with the help of following a script.
ALTER TABLE YourTable DROP COLUMN IDCol
ALTER TABLE YourTable ADD IDCol INT IDENTITY(1,1)

Let me know if you have any other work around besides SSMS (as that option just drops table and recreates it).

Reference: Pinal Dave (http://blog.sqlauthority.com)

About these ads

SQL SERVER – Reset the Identity SEED After ROLLBACK or ERROR

I have been blogging for almost 8 years now. I believe I have seen pretty much every single kind of email and comments I can receive from users. However, there are sometimes few which just are so interesting that I feel like blogging about it. This blog post is written based on the earlier blog post which I have written over here How to Catch Errors While Inserting Values in Table. I suggest you read that blog post before continuing this post. Now in the original blog post I have mentioned how one can catch the error with the help of TRY…CATCH. In reply to this blog post, I received an email which I have reproduced after removing few of the unimportant details.

“Hi Pinal,

We read your blog post How to Catch Errors While Inserting Values in Table and we have found one error in your blog post. The error is that when we execute your query displayed, it automatically increases the identity value of your table. In another word here is what is happening.

Current Identity: 1
 Execute your code which inserts two rows: It errors out.
 Check Identity again: 3

We believe that when errors are caught, it should be not increase the identity of the table.

We immediately removed the TRY…CATCH error and our result was the same. That means TRY…CATCH is just to display the error gracefully. After carefully reading your blog post we realized that you had mentioned the same there. However, we still believe the identity should not be incremented as there was no real insert.

After carefully thinking we decided to use ROLLBACK. However, the ROLLBACK even does not have any impact on the IDENTITY. I think this is indeed not a good thing.

We finally researched a lot on web and found ROLLBACK does not impact identity. Finally, we decided to take up this challenge and resolve the problem. We came up with following code.

This works beautifully and resets the identity upon error or ROLLBACK. Please review the code and give us your opinion about the same.

Please post this on your blog as I believe this will be useful to many who are facing similar issues.

Thanks,

DBCore Group of (CompanyName Removed)”

Here is the code which was included along with the email.

CREATE TABLE SampleTable (ID INT IDENTITY(1,1), Col VARCHAR(10))
GO
-- Select Identity
SELECT IDENT_CURRENT('SampleTable')
GO
-- Reset Identity Code
BEGIN TRY
DECLARE @IdentityValue BIGINT
SELECT @IdentityValue = IDENT_CURRENT('SampleTable')
INSERT INTO SampleTable (Col)
SELECT 'FourthRow'
UNION ALL
SELECT 'FifthRow---------'
END TRY
BEGIN CATCH
SELECT
ERROR_NUMBER() AS ErrorNumber
,ERROR_MESSAGE() AS ErrorMessage;
DBCC CHECKIDENT ('SampleTable', RESEED, @IdentityValue);
END
CATCH
GO
-- Select Identity
SELECT IDENT_CURRENT('SampleTable')
GO
-- Clean up
DROP TABLE SampleTable
GO

Very interesting email indeed. First of all, I really appreciated the email composed by the DBCore Team. They did some real research on the subject and attempted a solution. Here is my comment about the above code.

Comment1: First of all, above code may have issue with concurrency. That means, after you have retrieved identity value from the table, it is quite possible that in any other process with similar or different code the identity would have been updated and when you reset the identity at that time, you may reset it incorrect value and eventually forfeiting the original purpose of the identity. If you have Primary Key or any other kind of Unique Key, you may start facing error as well and your data integrity would have been compromised.

Here you may come up with the solution that when you enter this transaction you put the lock on the table, but that will additionally complicate the things and your performance will degrade big time. This code will work in the case, when you have single transaction at any point of time accessing the code and there is no issue with concurrency and performance.

Comment2: There should be never any dependance on the identity column of the table. If you need a serial number generated for display purpose, just create a column with int or bigint dataype and increment it at every insert. If you are using an identity column value for display, you should be very much aware of the situation that you will have gaps in that value and you should be able to write business logic around it. For example, you should not use identity columns as order number and if there is a gap in the order number, your business should just accept it. If you are using identity column value as invoice number, I think it is a bad idea as a general rule of accounting says that invoice number should be in sequence.

Well, I guess that is what I wanted to add the code which is displayed above. I would personally follow my both the comments above and will not use identity columns for any display purpose or will not depend on sequence of it. However, I truly appreciate the efforts of DBCore group.

Reference: Pinal Dave (http://blog.SQLAuthority.com)

SQL SERVER – Query to Find Seed Values, Increment Values and Current Identity Column Value of the Table with Max Value of Datatype – Part 2

Seven years ago, I wrote a blog post about – Query to Find Seed Values, Increment Values and Current Identity Column value of the table. It is quite a popular blog post and lots of people like it as it gives immediate details about Identity Values. SQL expert Mark Hickin has further improved this query and posted an interesting modification where the query also display upper limits of the data type used in the query. You can read the blog post over Query to Find Seed Values, Increment Values and Current Identity Column Value of the Table with Max Value of Datatype.

However, this query has a small limitation that it only works in those cases when increment value is 1 and seed is 1. However, if you have any other value as identity or seed, you will need a bit more modification in the script. SQL Server Expert and Guru Harsh has provided amazing script where he has provided query with the said adjustment.

SELECT Seed,Increment,CurrentIdentity,TABLE_NAME,DataType,MaxPosValue ,
FLOOR((MaxPosValue -CurrentIdentity)/Increment) AS Remaining,
100-100*((CurrentIdentity-Seed)/Increment+1) / FLOOR((MaxPosValue - Seed) /Increment+1) AS PercentUnAllocated
FROM (
SELECT IDENT_SEED(TABLE_SCHEMA + '.' + TABLE_NAME) AS Seed ,
IDENT_INCR(TABLE_SCHEMA + '.' + TABLE_NAME) AS Increment ,
IDENT_CURRENT(TABLE_SCHEMA + '.' + TABLE_NAME) AS CurrentIdentity ,
TABLE_SCHEMA + '.' + TABLE_NAME AS TABLE_NAME ,
UPPER(c.DATA_TYPE) AS DataType ,
FLOOR(t.MaxPosValue/IDENT_INCR(TABLE_SCHEMA + '.' + TABLE_NAME)) * IDENT_INCR(TABLE_SCHEMA + '.' + TABLE_NAME) AS MaxPosValue
FROM INFORMATION_SCHEMA.COLUMNS AS c
INNER JOIN ( SELECT name AS Data_Type ,
POWER(CAST(2 AS VARCHAR), ( max_length * 8 ) - 1) AS MaxPosValue
FROM sys.types
WHERE name LIKE '%Int'
) t ON c.DATA_TYPE = t.Data_Type
WHERE COLUMNPROPERTY(OBJECT_ID(TABLE_SCHEMA + '.' + TABLE_NAME), COLUMN_NAME,
'IsIdentity') = 1
)T1
ORDER BY PercentUnAllocated ASC

Here is the screenshot of the image. Thanks Harsh- a very nice query.

Reference: Pinal Dave (http://blog.SQLAuthority.com)

SQL SERVER – Query to Find Seed Values, Increment Values and Current Identity Column Value of the Table with Max Value of Datatype

Seven years ago, I wrote a blog post about – Query to Find Seed Values, Increment Values and Current Identity Column value of the table. It is quite a popular blog post and lots of people like it as it gives immediate details about Identity Values. SQL expert Mark Hickin has further improved this query and posted an interesting modification where the query also display upper limits of the data type used in the query.

SELECT IDENT_SEED(TABLE_SCHEMA + '.' + TABLE_NAME) AS Seed ,
IDENT_INCR(TABLE_SCHEMA + '.' + TABLE_NAME) AS Increment ,
IDENT_CURRENT(TABLE_SCHEMA + '.' + TABLE_NAME) AS CurrentIdentity ,
TABLE_SCHEMA + '.' + TABLE_NAME ,
UPPER(c.DATA_TYPE) AS DataType ,
t.MaxPosValue,
t.MaxPosValue -IDENT_CURRENT(TABLE_SCHEMA + '.' + TABLE_NAME) AS Remaining,
((
t.MaxPosValue -IDENT_CURRENT(TABLE_SCHEMA + '.' + TABLE_NAME))/t.MaxPosValue) *100 AS PercentUnAllocated
FROM INFORMATION_SCHEMA.COLUMNS AS c
INNER JOIN ( SELECT name AS Data_Type ,
POWER(CAST(2 AS VARCHAR), ( max_length * 8 ) - 1) AS MaxPosValue
FROM sys.types
WHERE name LIKE '%Int'
) t ON c.DATA_TYPE = t.Data_Type
WHERE COLUMNPROPERTY(OBJECT_ID(TABLE_SCHEMA + '.' + TABLE_NAME), COLUMN_NAME,
'IsIdentity') = 1
ORDER BY PercentUnAllocated ASC

Here is the screenshot of the image. Thanks Mark – a very nice query.

Reference: Pinal Dave (http://blog.SQLAuthority.com)

SQL SERVER – How to an Add Identity Column to Table in SQL Server

Here is the question I received on SQLAuthority Fan Page.

“How do I add an identity column to Table in SQL Server? “

Sometime the questions are very very simple but the answer is not easy to find.

Scenario 1:

If you are table does not have identity column, you can simply add the identity column by executing following script:

ALTER TABLE MyTable
  ADD ID INT IDENTITY(1,1) NOT NULL

Scenario 2:

If your table already has a column which you want to convert to identity column, you can’t do that directly. There is a workaround for the same which I have discussed in depth over the article Add or Remove Identity Property on Column.

Scenario 3:

If your table has already identity column and you can want to add another identity column for any reason – that is not possible. A table can have only one identity column. If you try to have multiple identity column your table, it will give following error.

Msg 2744, Level 16, State 2, Line 2
Multiple identity columns specified for table ‘MyTable‘. Only one identity column per table is allowed.

Leave a comment if you have any suggestion.

Reference : Pinal Dave (http://blog.SQLAuthority.com)

SQL SERVER – Delay Command in SQL Server – SQL in Sixty Seconds #055

Have you ever needed WAIT or DELAY function in SQL Server?  Well, I personally have never needed it but I see lots of people asking for the same. It seems the need of the function is when developers are working with asynchronous applications or programs. When they are working with an application where user have to wait for a while for another application to complete the processing.

If you are programming language developer, it is very easy for you to make the application wait for command however, in SQL I personally have rarely used this feature.  However, I have seen lots of developers asking for this feature in SQL Server, hence I have decided to build this quick video on the same subject.

We can use WAITFOR DELAY ‘timepart‘ to create a SQL Statement to wait.

Let us see the same concept in following SQL in Sixty Seconds Video:

Related Tips in SQL in Sixty Seconds:

What would you like to see in the next SQL in Sixty Seconds video?

Reference: Pinal Dave (http://blog.sqlauthority.com)

SQL SERVER – Maximize Database Performance with DB Optimizer – SQL in Sixty Seconds #054

Performance tuning is an interesting concept and everybody evaluates it differently. Every developer and DBA have different opinion about how one can do performance tuning. I personally believe performance tuning is a three step process

  1. Understanding the Query
  2. Identifying the Bottleneck
  3. Implementing the Fix

While, we are working with large database application and it suddenly starts to slow down. We are all under stress about how we can get back the database back to normal speed. Most of the time we do not have enough time to do deep analysis of what is going wrong as well what will fix the problem. Our primary goal at that time is to just fix the database problem as fast as we can. However, here is one very important thing which we need to keep in our mind is that when we do quick fix, it should not create any further issue with other parts of the system.

When time is essence and we want to do deep analysis of our system to give us the best solution we often tend to make mistakes. Sometimes we make mistakes as we do not have proper time to analysis the entire system. Here is what I do when I face such a situation – I take the help of DB Optimizer. It is a fantastic tool and does superlative performance tuning of the system.

Everytime when I talk about performance tuning tool, the initial reaction of the people is that they do not want to try this as they believe it requires lots of the learning of the tool before they use it. It is absolutely not true with the case of the DB optimizer. It is a very easy to use and self intuitive tool. Once can get going with the product, in no time. Here is a quick video I have build where I demonstrate how we can identify what index is missing for query and how we can quickly create the index.

Entire three steps of the query tuning are completed in less than 60 seconds. If you are into performance tuning and query optimization you should download DB Optimizer and give it a go.

Let us see the same concept in following SQL in Sixty Seconds Video:

You can Download DB Optimizer and reproduce the same Sixty Seconds experience.

Related Tips in SQL in Sixty Seconds:

What would you like to see in the next SQL in Sixty Seconds video?

Reference: Pinal Dave (http://blog.sqlauthority.com)