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)

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

identitygaps SQL SERVER   Reset the Identity SEED After ROLLBACK or ERROR

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.

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

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.

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

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)

SQL SERVER – NuoDB in Sixty Seconds – SQL in Sixty Seconds #053

Earlier this week, I have done five part blog series on NuoDB and it was very well received by audienceNuoDB is an elastically scalable SQL database that can run on local hostdatacenter and cloud-based resources. t is an operational NewSQL database built on a patented emergent architecture with full support for SQL and ACID guarantees. In this blog post, I will explore how one can download and install NuoDB database.

In this video I explain how one can install NuoDB in very few seconds and set up the entire environment in additional few seconds. One can get going with installation of NuoDB and sample database in total of less than 60 seconds.

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

You can Download NuoDB 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)

SQL SERVER – Add Identity Column to Table Based on Order of Another Column

After reading my earlier article on Identity Column, I received a very interesting question. The reason, I like to call it interesting is though, I have provided answers to him, I believe there should be another better alternative to this problem. Let us see the question first in his own words.

“Hi Pinal,

I already have existing table and the table already have fewer columns. The table does not have identity column and I would like to add an identity column to this table. The problem is that every time when I try to add an identity column to the table, it adds the value based on the default order of the table. I would like to add the identity value based on the order sequence of another column from the table. Do you have any alternative to it. To illustrate my problem here is the simple script based on my table schema. My table also do not have any index as of now.

USE tempdb
GO
-- Create Table
CREATE TABLE TestTable (Col1 INT, Col2 VARCHAR(100))
GO
-- Insert Data
INSERT INTO TestTable (Col1, Col2)
VALUES (33, 'Pinal');
INSERT INTO TestTable (Col1, Col2)
VALUES (22, 'Nupur');
INSERT INTO TestTable (Col1, Col2)
VALUES (11, 'Shaivi');
GO
-- Select Data
SELECT *
FROM TestTable
GO
-- Add Identity Column
ALTER TABLE TestTable
ADD ID INT IDENTITY(1, 1)
GO
-- Select Data
SELECT *
FROM TestTable
GO
-- Clean up
DROP TABLE TestTable
GO

Here is the result set of the query above. Currently the result is ordered by the column Col1 DESC but ideally I would like to get the result ordered by Col1 but in ASC order.

identity order SQL SERVER   Add Identity Column to Table Based on Order of Another Column

Is there any workaround to do the same?”

As I said I find this question very interesting and I was able to come up with the solution as well fairly quickly as the user had not created an index on the table. I quickly created clustered index in ASC order on Col1 and it ordered the table as expected and later added the identity column there. Let us see the script to get the desired result.

USE tempdb
GO
-- Create Table
CREATE TABLE TestTable (Col1 INT, Col2 VARCHAR(100))
GO
-- Insert Data
INSERT INTO TestTable (Col1, Col2)
VALUES (33, 'Pinal');
INSERT INTO TestTable (Col1, Col2)
VALUES (22, 'Nupur');
INSERT INTO TestTable (Col1, Col2)
VALUES (11, 'Shaivi');
GO
-- Select Data
SELECT *
FROM TestTable
GO
-- Create Clustered Index on Column ID
CREATE CLUSTERED INDEX IX_TestTable ON dbo.TestTable
(Col1 ASC)
GO
-- Add Identity Column
ALTER TABLE TestTable
ADD ID INT IDENTITY(1, 1)
GO
-- Select Data
SELECT *
FROM TestTable
GO
-- Clean up
DROP TABLE TestTable
GO

The above script will produce the following answer which user is expecting:

identity order1 SQL SERVER   Add Identity Column to Table Based on Order of Another Column

Now here is my question back to, this was fairly simple for me to do as there was no index created on the table. I was able to create clustered index on the column and get the desired result. However, what will be the alternative solution to this question if the clustered index is already created on the table and there was no option to modify the same. Another alternative solution would be to drop the table and do processing but that is never a good solution as well, it is not possible if there are foreign keys exists on the table.

What would you do in the situation mentioned above?

Click to Download Scripts

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

SQL SERVER – Puzzle SET ANSI_NULLS and Resultset – SQL in Sixty Seconds #052

Earlier I have posted a puzzle where I was receiving different results when I executed two different queries. I encourage all of you to read the original puzzle here, the puzzle had received many fantastic responses and I have later blogged about the solution of the puzzle over here.

Now I have decided to extend the same puzzle and take it to the next level. In earlier puzzle I had value of the ANSI_NULLS was set to ON. Now in this puzzle let us set the value of the ANSI_NULLS to OFF. When the value of ANSI_NULLS was off at that time, the result of the both the queries is almost identical. The key reason behind this behavior is ANSI_NULLS setting.

However, the puzzle is to answer that why the different queries are producing almost same results when there is a different setting of ANSI_NULL.

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

Here is the script used in this episode:

-- Original Puzzle
http://bit.ly/sql-puzzle-ansi
-- Script
SET ANSI_NULLS ON;
-- Query1
SELECT 'SQLAuthority' AS Statement11
WHERE 'Authority' IN ('S','Q', 'L', 'Authority', NULL);
-- Query 2
SELECT 'SQLAuthority' AS Statement12
WHERE 'Authority' NOT IN ('S','Q', 'L', NULL);
-- Puzzle Solution
http://bit.ly/sql-puzzle-ansi-sol
------------------------------------------------------
-- New puzzle
SET ANSI_NULLS OFF;
-- Query3
SELECT 'SQLAuthority' AS Statement11
WHERE 'Authority' IN ('S','Q', 'L', 'Authority', NULL);
-- Query 4
SELECT 'SQLAuthority' AS Statement12
WHERE 'Authority' NOT IN ('S','Q', 'L', NULL);

Related Tips in SQL in Sixty Seconds:

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

Click to Download Scripts

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