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/default.aspx?scid=kb;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)
Showing posts with label creation. Show all posts
Showing posts with label creation. Show all posts
Friday, March 9, 2012
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!
SQLCatz
The 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/default...;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)
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
The 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/default...;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)
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)
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)
Wednesday, March 7, 2012
Metadata driven SSIS
I am looking for some pointers on Metadata driven SSIS. Specifically, how to automate creation of a large number of packages in SSIS using metadata. I am looking at this as a solution to deploy multiple packages on multiple servers. Any other ideas on this would be highly appreciated.
Thanks
Vishal Verma
regards,
ash|||Well, I am not sure either. I read some posts by Microsoft guys (Ash why dont u chat with ur boss Kamal or Donald) and what I infered is that we might get something in near future.|||Ash is correct as usual. There are no confirmed plans for work in the SP. You may have interpreted what Kamal or Donald said as an assertion of forthcoming features. That is not correct. The planning for SPs and other releases is still under way and will not be announced for some time.
K|||By the way, I think what Suresh may be talking about here is that we're doing some work to identify and promulgate best practices around metadata for IS that will be released as either a KB article or whitepaper. That is supposed to be available around the time IS is released. But, no new metadata features, per se.
Subscribe to:
Posts (Atom)