February 20, 2008 at 9:59 pm
Comments posted to this topic are about the item Changing a Non-IDENTITY column to IDENTITY and vice versa
February 21, 2008 at 4:24 am
Good tip 🙂 I must admit that I'll still probably stick with letting the GUI tools do all the ugly scripting for me as most of our customers don't have SQL Enterprise (and obviously aren't running developer).
February 21, 2008 at 5:57 am
I am on the business analyst side.....are you speaking of de-ideintification or masking.....?
February 21, 2008 at 7:23 am
Clever use of partition switching!
Note that BOL warns that the transfer could introduce duplication in the identity column and recommends running DBCC CHECKIDENT (presumably after the switch).
I recommend further reading BOL's article "Transferring Data Efficiently by Using Partition Switching" also available online in
Technet: http://technet.microsoft.com/en-us/library/ms191160.aspx
February 21, 2008 at 9:42 am
I appreciated the idea in this article.
I thought I would also share another option. I routinely re-create my databases from scratch when I need to make major schema changes to the database. This identity change would count as such a change. The steps are:
1) make changes to the data model as needed
2) forward engineer a new database that is just like the original db only it has the schema changes you want.
3) run all the code (stored procs, triggers, etc.) on new db
4) run some code that automatically creates the code for a stored proc which copies table data from the source database into the new database
5) tweak the copy code as needed and run it
>> At this point, the new database is identical to the old database except for the changes you have deliberately made. So:
6) backup the new database and restore over the original database.
The advantages are that you follow the same set of steps every time. You don't have to say look for the set of indexes and foreign keys that are specifically attached to the one table you are working with. Also, you can make a whole bunch of minor and major changes to your database at once with the same set of steps. One of my favorite advantages is that you make sure your database matches your data model, and you know that your source code (stored procs, functions, etc.) are all up to date. In other words, you know that you can re-create the schema for the production database as needed at any time. The parallel is like always having a fully working build at your fingertips as application developers try to do.
All of the steps above have to be done when no one is using the database. Thus, this option may not be practical for larger databases and longer up-times. I have relatively small databases (under a gigabite) and up-time is only 7-6 on weekdays. So, it is easy for me to have "down time" and work on weekends. For other agencies in my situation, this is an approach to think about for any type of significant schema change--and it works in any version of SQL Server.
February 21, 2008 at 4:43 pm
Thomas,
I like the technique with partition switching. Thank you.
BTW, for your method 2, you will need
SET IDENTITY_INSERT Destination_TB ON;
before the insert.
\Sean
February 21, 2008 at 4:58 pm
Hi Thomas,
isn't just opening 'design table' from SQL management and edit the column to have identity or not, works as well?
or it might be just me missing the point.
Cheers,
DK
February 25, 2008 at 12:09 am
Hi,
Can we have two identity columns on a single table?
If not then how can one insert an identity column in a table having an identity column?
Regards,
Ahmad
Regards,
[font="Verdana"]Sqlfrenzy[/font]
February 25, 2008 at 5:05 pm
Daniel R. Kusnadi (2/21/2008)
Hi Thomas,isn't just opening 'design table' from SQL management and edit the column to have identity or not, works as well?
or it might be just me missing the point.
Cheers,
DK
I have to agree with Daniel. The article is well written and well thought out, but in most case where there are no complicating factors it seems easiest just to use design table. Now, as can be seen by generating the change script, what this does in effect is very similar to method 2 listed, but in most cases it makes sense to let the gui handle that.
---
Timothy A Wiseman
SQL Blog: http://timothyawiseman.wordpress.com/
September 15, 2008 at 4:50 pm
doesnot work with me nothing affect the identity of the table dont get off
April 10, 2009 at 8:07 am
SQL Frenzy (2/25/2008)
Hi,Can we have two identity columns on a single table?
If not then how can one insert an identity column in a table having an identity column?
Regards,
Ahmad
The answer is simple: you can only have 1 identity column per table. If what you want with the second part of your question is to move the identity attribute from one column to the other, personally I don't see why one would do such a thing as you already have an identity, then first you have to turn off the identity on your original column and then add another column with identity attribute.
As per article, the best way to go is in the Enterprise Manager or Management Studio in design mode on the table, switch on the identity attribute on the column, copy the SQL that was generated and then save it in a file.
Don't just give the hungry man a fish, teach him how to catch it as well.
the sqlist
April 10, 2009 at 9:15 am
I have to agree with the previous posters, using the GUI to generate the change script is the best approach. The column being changed stays in the same ordinal position, the script runs inside a transaction, and preserves all the indexes, constraints etc.
You can't have users in the db when you're making this type of change, so you can't avoid having at least some downtime.
April 13, 2009 at 2:00 am
Good tip.
"Keep Trying"
April 13, 2009 at 7:36 am
Thank you very much Mr.Thomas. I came across the same issue ie non- identity to identity type change and your article has helped a me a lot.
But when I have tried it in a temp database it shows the error
Msg 245, Level 16, State 1, Line 1
Conversion failed when converting the varchar value 'TMP' to data type int.
But again I have tried in another database its working fine.
--drop table tmp
create table tmp
(s_noint,
namevarchar(10)
)
insert into tmp
values
(12,'name1')
insert into tmp
values
(13,'name2')
insert into tmp
values
(14,'name3')
ALTER TABLE tmp ADD s_no_II INT IDENTITY(1,1)
ALTER TABLE tmp DROP COLUMN s_no
EXEC sp_rename
'tmp.s_no_II','s_no', 'COLUMN'
Can u brief and how the error can be overcome?
Thanks in advance.
April 14, 2009 at 2:31 pm
I have to respectfully disagree with the posters that are saying that using the GUI is the best approach. If by best you mean easiest, then that would be true. But if you look at what is involved, the article really dives into an interesting and super-efficient way to accomplish the task. If you had a 120gb table (as I have had), and you do this through the GUI (or script what the GUI would do and run it), you would find your users very displeased at the amount of downtime, assuming you dont run out of diskspace while your query creates a copy of the table with a new identity column. This is what the GUI does, if you wanted to add an identity to a table without one (in 2005)...
BEGIN TRANSACTION
GO
CREATE TABLE dbo.Tmp_SourceTable
(
Id int NOT NULL IDENTITY (1, 1),
Column1 varchar(50) NULL,
Column2 varchar(50) NULL
) ON [PRIMARY]
GO
SET IDENTITY_INSERT dbo.Tmp_SourceTable OFF
GO
IF EXISTS(SELECT * FROM dbo.SourceTable)
EXEC('INSERT INTO dbo.Tmp_SourceTable (Column1, Column2)
SELECT Column1, Column2 FROM dbo.SourceTable WITH (HOLDLOCK TABLOCKX)')
GO
DROP TABLE dbo.SourceTable
GO
EXECUTE sp_rename N'dbo.Tmp_SourceTable', N'SourceTable', 'OBJECT'
GO
COMMIT
If i didnt have another 120gb on my disk, i would not be able to do the above based on diskspace alone.
Very nice and very interesting article.
Viewing 15 posts - 1 through 15 (of 27 total)
You must be logged in to reply to this topic. Login to reply