Methods of #temp table creation.

Hello,
What is the difference between the two?
(a) Explicitly create a temp table using "create table #tableName...".
After it has been created, populate it using an explicit "insert into
#tableName".
Table creation and population of records take place in 2 t-sql statements.
(b) Let the #tableName get created on the fly when using a "select top 10
cid into #tableName from storesLink".
Table creation and population of records take place in a single t-sql
statement.
Both of them achieve the same result.
But, what is the difference in performance?
Are there any other points that I should keep in mind when adopting any of
the above two approaches ?
Cheers!
SQLCatzThe second one causes locking on some of the system tables and should
be avoided. Use the first one or better yet, use the table variable.
Aramid
On Wed, 6 Apr 2005 23:29:03 -0700, "SQLCatz"
<SQLCatz@.discussions.microsoft.com> wrote:

>Hello,
>What is the difference between the two?
>(a) Explicitly create a temp table using "create table #tableName...".
>After it has been created, populate it using an explicit "insert into
>#tableName".
>Table creation and population of records take place in 2 t-sql statements.
>(b) Let the #tableName get created on the fly when using a "select top 10
>cid into #tableName from storesLink".
>Table creation and population of records take place in a single t-sql
>statement.
>Both of them achieve the same result.
>But, what is the difference in performance?
>Are there any other points that I should keep in mind when adopting any of
>the above two approaches ?
>Cheers!
>SQLCatz
>|||they are about the same in performance if you don't have recompile. For
recompile info, see:
http://support.microsoft.com/defaul...b;en-us;q243586
--
Wei Xiao [MSFT]
SQL Server Storage Engine Development
http://blogs.msdn.com/weix
This posting is provided "AS IS" with no warranties, and confers no rights.
"SQLCatz" <SQLCatz@.discussions.microsoft.com> wrote in message
news:9FBD6D28-361B-460A-B47B-7CBD7DE86430@.microsoft.com...
> Hello,
> What is the difference between the two?
> (a) Explicitly create a temp table using "create table #tableName...".
> After it has been created, populate it using an explicit "insert into
> #tableName".
> Table creation and population of records take place in 2 t-sql statements.
> (b) Let the #tableName get created on the fly when using a "select top 10
> cid into #tableName from storesLink".
> Table creation and population of records take place in a single t-sql
> statement.
> Both of them achieve the same result.
> But, what is the difference in performance?
> Are there any other points that I should keep in mind when adopting any of
> the above two approaches ?
> Cheers!
> SQLCatz
>|||On Wed, 6 Apr 2005 23:29:03 -0700, SQLCatz wrote:
(snip)
>Are there any other points that I should keep in mind when adopting any of
>the above two approaches ?
Hi SQLCatz,
If you use CREATE TABLE, you can add constraints and indexes right away,
or you can chooose to add them after the INSERT. If you use SELECT INTO,
you can only add the constraints and indexes later.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)