Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase «««1234»»

T-SQL Expand / Collapse
Author
Message
Posted Tuesday, February 10, 2009 9:09 AM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Today @ 3:58 PM
Points: 22,999, Visits: 31,481
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.




Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Post #653865
Posted Tuesday, February 10, 2009 9:35 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, July 17, 2014 5:17 AM
Points: 159, Visits: 429
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
Post #653900
Posted Tuesday, February 10, 2009 9:41 AM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Today @ 3:58 PM
Points: 22,999, Visits: 31,481
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.




Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Post #653913
Posted Tuesday, February 10, 2009 10:19 AM
Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Today @ 1:54 PM
Points: 3,869, Visits: 3,620
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.
Post #653980
Posted Tuesday, February 10, 2009 10:31 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, July 17, 2014 5:17 AM
Points: 159, Visits: 429
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
Post #653991
Posted Tuesday, February 10, 2009 10:38 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Sunday, April 27, 2014 6:26 AM
Points: 1,521, Visits: 3,036
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!!)
Post #654001
Posted Tuesday, February 10, 2009 10:48 AM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Today @ 3:58 PM
Points: 22,999, Visits: 31,481
James Horsley (2/10/2009)
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


Guess I got to the question after Steve changed it.



Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Post #654013
Posted Tuesday, February 10, 2009 12:17 PM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Today @ 1:13 PM
Points: 3,997, Visits: 6,050
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? -- Stephen Stills
Post #654098
Posted Tuesday, February 10, 2009 12:36 PM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Tuesday, June 19, 2012 10:11 AM
Points: 2,689, Visits: 36
All tables should have primary key, so it should also be created.

Leslaw
Post #654120
Posted Tuesday, February 10, 2009 12:49 PM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Today @ 1:13 PM
Points: 3,997, Visits: 6,050
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? -- Stephen Stills
Post #654127
« Prev Topic | Next Topic »

Add to briefcase «««1234»»

Permissions Expand / Collapse