February 10, 2009 at 8:44 am
OK, I learned about the IDENTITY function used with SELECT INTO.
But if I wanted to copy a table, except as a temporary hack, I'd still use 'Create Script..', edit, etc.
Derek
February 10, 2009 at 8:51 am
Lynn Pettis (2/10/2009)
I find if you don't read too much into these questions that they are usually fairly easy to answer.
Hi Lynnn,
I would usually agree. Not in this case, though. When I read the question and saw the answers, I immediately knew that two of the answers could have been correct.
Either the author of the question wrote this question to educate people about the existance of the IDENTITY() function, in which case SELECT INTO would be the expected answer. Or the author expected the readers to know about this and wrote the question to warn about some less published and less well-known side effects, in which case SELECT INTO would obviously have been wrong and CREATE TABLE ... INSERT SELECT would have been right.
Since there is no way to read the mind of the author of the question, I was faced with a 50/50 chance of getting my answer right. I took my chance ... and lost.
And then I took the opportunity to evangelize about those side effects anyway! 😀
February 10, 2009 at 8:54 am
I agree Lynn. Many times the discussion is as or more informative than the question. The point is to learn from the exchange.
February 10, 2009 at 9:05 am
It's not that they add extra constraints, it's often authors are thinking of one thing. One small area they are trying to show and they don't have the same frame of reference that others do.
If you need a quick copy of a table, do you worry about constraints? Some people do, some always would script that, some would never think about it since there wouldn't be a security issue.
There's also a language issue. How do you try to get someone to think in a particular way. I could see why "best" was chosen, since for this person, it might eb the "best way" with the least amount of effort to do this. Might never have caused an issue.
It's good for people like Hugo to raise the debate. It's also good that the questions are here to get you to think about or learn about some new feature in SQL Server.
February 10, 2009 at 9:07 am
Cliff Jones (2/10/2009)
Good change. Since a select into can cause some undesirable locking behavior it probably wouldn't be the best way to accomplish the task.
Not since 6.5. One of those myths that keep hanging around.
February 10, 2009 at 9:09 am
Question stated "Now I need a copy of this table with an identity column for some temporary work." This is the phrase that pointed me to select "Use an identity function with a SELECT INTO" as the answer. No need for the constraints etc from the source table, just a quick and dirty copy for temporary work.
The part of the question that Steve updated didn't even play a part in my decision making process.
February 10, 2009 at 9:35 am
SELECT INTO loses all your original indexes, constraints etc - much better to copy the structure of original table by scripting it, add the Identity column, rename old table, build new one and copy rows across
James Horsley
Workflow Consulting Limited
February 10, 2009 at 9:41 am
And if you don't need those constraints and indexes for the temporary work, why go through the process of creating them when the SELECT INTO works just fine.
February 10, 2009 at 10:19 am
Ninja's_RGR'us (2/10/2009)
Cliff Jones (2/10/2009)
Good change. Since a select into can cause some undesirable locking behavior it probably wouldn't be the best way to accomplish the task.Not since 6.5. One of those myths that keep hanging around.
That is very useful information. Actually I went researching my own post to see if this was still valid and was wondering if the locking of system tables was still a problem. I guess I am behind the times on this point. Thanks for the clarification. Was worth the price of admission.
February 10, 2009 at 10:31 am
Actually original question (that I was answering) was
I have a table XYZ with 3 INT columns. There are 3000 records already in this table. Now I need a copy of this table with an identity column. What is the best way to do this?
No mention of "temporary".
And even then it depends what "temporary" means - if it is to "temporarily" have an IDENTITY on a column while I insert the true 200,000,000 records from a live database then you may well want to still have all data domain checks in place
James Horsley
Workflow Consulting Limited
February 10, 2009 at 10:38 am
Since there's no such thing as an "identity fuction", I went with the create/select-into answer.... 😉
Seriously, though, I appreciate both the reminder of the identity fuNction and Hugo's immediate and thorough discussion of the topic. (Steve, give that guy some more points!!)
February 10, 2009 at 10:48 am
James Horsley (2/10/2009)
Actually original question (that I was answering) wasI have a table XYZ with 3 INT columns. There are 3000 records already in this table. Now I need a copy of this table with an identity column. What is the best way to do this?
No mention of "temporary".
And even then it depends what "temporary" means - if it is to "temporarily" have an IDENTITY on a column while I insert the true 200,000,000 records from a live database then you may well want to still have all data domain checks in place
Guess I got to the question after Steve changed it.
February 10, 2009 at 12:17 pm
I started out in Hugo's camp. I'm always glad to have learned a new function, but one thing I can't get away from is that, in the real world, if I'm creating a unique row identifier, then I'm probably going to come back and look for it later and for that I will almost always want an index. So that makes it a choice between the two alternatives below. It looks like six of one and a half dozen of the other.
Before stopping to think about which was "better", I would have called it a draw. (And being superstitious, I still am in the habit of avoiding SELECT... INTO....)
However, populating a table before building the index means that using SELECT...INTO with the IDENTITY function (followed by a CREATE INDEX) is going to run FASTER than creating a table with a primary key predefined and then doing an INSERT INTO ... SELECT. (A couple of scripts follow at the bottom if anyone wants to test the difference at a million rows.)
This isn't going to make me go back and change all of our existing stored procedures, but it is something to be remembered going forward.
---------------------------------------------
-- first thought: who cares?
---------------------------------------------
create table #temp(ID int identity(1,1) PRIMARY KEY, foo1 varchar (20), foo2 int)
insert into #temp
select foo1, foo2
from SomeOtherTable
-- vs --
SELECT IDENTITY(int, 1,1) AS ID_Num, foo1, foo2
into #temp
from SomeOtherTable
PLUS
ALTER TABLE .........
or
CREATE INDEX
-----------------------------------------
-- test scripts
-----------------------------------------
-- using IDENTITY function and creating index afterwards
SELECT IDENTITY(int, 1,1) as ID , getdate() + N as Futureday
into #future
from tally
create unique clustered index PK_Future on #future (ID)
select count(*), max(Futureday) from #future
drop table #future
go
-- predefining table with an IDENTITY column
create table #future (ID int identity(1,1) primary key, Futureday datetime)
insert into #future
select getdate() + N as Futureday
from tally
select count(*), max(Futureday) from #future
drop table #future
go
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
February 10, 2009 at 12:36 pm
All tables should have primary key, so it should also be created.
Leslaw
February 10, 2009 at 12:49 pm
leslaw (2/10/2009)
All tables should have primary key, so it should also be created.Leslaw
Cardinal Moden will call me a heretic, yet I beg to differ with respect to the word "ALL".
I agree that any non-temporary table should have a unique primary key, but oftentimes a temporary table is built within a stored procedure and then returned with a final select statement. These tables do not survive the ending of the stored procedure. By the time you see the results, the table itself no longer exists.
Sometimes the logic of the stored procedure involves a need to go back and retrieve or update values of particular rows within that table, and in such cases an index is definitely a good idea. But, in the absence of any need to go back and retrieve or update the results from a particular row, what purpose is served by imposing an arbitrary primary key?
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
Viewing 15 posts - 16 through 30 (of 36 total)
You must be logged in to reply to this topic. Login to reply