Difference between SELECT INTO AND INSERT INTO

Hi,
I would appreciate if any one can tell me what exactly is the difference
between
1) select * into # abc from table1
2) Insert into #abd select * from table1
assuming tables are indexed properly what is the performance benefit, how
locks are placed in this, how locks will be escalated and duration.
is second statement better than first then why ?
Sanjay
It is easy to test it by yourself ,does not?
> 1) select * into # abc from table1
You don't need to create a table before this command , the SELECT INTO
statement creates a new table and populates it with the result set of the
SELECT.
It is possible that you'll see some perfomance improvment by using this
method as well as possible locks that may occur.

> 2) Insert into #abd select * from table1
You will have to issue CREATE TABLE #Table (col INT,......) before the
statement
I'd prefer the second one , but you did not mention about a table variable
you can use as well
"Sanjay" <Sanjay@.discussions.microsoft.com> wrote in message
news:967886AB-7697-4C36-AC0B-0D9B1D0EBB39@.microsoft.com...
> Hi,
> I would appreciate if any one can tell me what exactly is the difference
> between
> 1) select * into # abc from table1
> 2) Insert into #abd select * from table1
> assuming tables are indexed properly what is the performance benefit, how
> locks are placed in this, how locks will be escalated and duration.
> is second statement better than first then why ?
|||Hi Sanjay
The main deifference between the 2 queries is creation of the table:
SELECT * INTO .. tries to creates a new table each time its executed.
The table is created in the first run, and from next time if the table
exist, the query is not executed
INSERT INTO <TABLE> needs to have a <table> to proceed sucessfully.
INSERT INTO does'nt create a new table
Please let me know if u have any questions
best Regards,
Chandra
http://chanduas.blogspot.com/
http://groups.msn.com/SQLResource/
"Sanjay" wrote:

> Hi,
> I would appreciate if any one can tell me what exactly is the difference
> between
> 1) select * into # abc from table1
> 2) Insert into #abd select * from table1
> assuming tables are indexed properly what is the performance benefit, how
> locks are placed in this, how locks will be escalated and duration.
> is second statement better than first then why ?
|||Thanks for reply, but can you tell me how locking architecture in first
scenario.
1) what type of locks will be placed on table1 when used with SELECT INTO,
will it be shared lock on table1 till the records get inserted into temporary
table.
and what locking will takes place when i do insert into #tab select * from
table1.
assuming i have some 10000 records to insert.
thanks for all the help
Sanjay
"Uri Dimant" wrote:

> Sanjay
> It is easy to test it by yourself ,does not?
> You don't need to create a table before this command , the SELECT INTO
> statement creates a new table and populates it with the result set of the
> SELECT.
> It is possible that you'll see some perfomance improvment by using this
> method as well as possible locks that may occur.
>
> You will have to issue CREATE TABLE #Table (col INT,......) before the
> statement
>
> I'd prefer the second one , but you did not mention about a table variable
> you can use as well
>
>
> "Sanjay" <Sanjay@.discussions.microsoft.com> wrote in message
> news:967886AB-7697-4C36-AC0B-0D9B1D0EBB39@.microsoft.com...
>
>
|||Sanjay
By deafult SQL Server implements ROW LOCK but it depends on load of data
and many other things
If persist to use SELECT INTO command run SELECT * INTO #Test FROM Table
WHERE 1=2 to get a structure of the table and then perfom INSERT INTO
command
"Sanjay" <Sanjay@.discussions.microsoft.com> wrote in message
news:680473F9-4933-409E-B3E5-FADEDEE9EAE4@.microsoft.com...[vbcol=seagreen]
> Thanks for reply, but can you tell me how locking architecture in first
> scenario.
> 1) what type of locks will be placed on table1 when used with SELECT INTO,
> will it be shared lock on table1 till the records get inserted into
> temporary
> table.
> and what locking will takes place when i do insert into #tab select * from
> table1.
> assuming i have some 10000 records to insert.
> thanks for all the help
> --
> Sanjay
>
> "Uri Dimant" wrote:
|||=?Utf-8?B?Q2hhbmRyYQ==?= (chandra@.discussions.microsoft.com) writes:
> The main deifference between the 2 queries is creation of the table:
> SELECT * INTO .. tries to creates a new table each time its executed.
> The table is created in the first run, and from next time if the table
> exist, the query is not executed
In fact you get an error if the table does already exist.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp
|||=?Utf-8?B?U2FuamF5?= (Sanjay@.discussions.microsoft.com) writes:
> Thanks for reply, but can you tell me how locking architecture in first
> scenario.
> 1) what type of locks will be placed on table1 when used with SELECT
> INTO, will it be shared lock on table1 till the records get inserted
> into temporary table.
> and what locking will takes place when i do insert into #tab select * from
> table1.
> assuming i have some 10000 records to insert.
The locks on table1 should be the same in both cases.
If you run will full recovery, there is little difference between SELECT
INTO and INSERT, but see below for a war story.
If you run with bulk-logged recovery, SELECT INTO is minimally logged.
Instead of logging each row, SQL Server logs only the page allocation. I'm
uncertain of what happens when you have simple recovery. Now, since
simple recovery is what you have in tempdb, this is what applies. My guess
goes for minimally logged. Thus, with SELECT INTO you write fewer log
records, and you can therefor get better performance.
On the other hand, it takes more resources to create the table. I once
tried to track down a performance problem, and was running Profiler and
all that. I had basically given up on the main problem, but decided that I
should look at a trigger where there was some non-low numbers (they were
not exceedingly high.) What I had in that trigger was
SELECT * INTO #inserted FROM inserted
The point with this is that the virtual table "inserted" is slow to
work with. Now, this function I was looking into performed a loop, so
there were many updates on that table within that loop - and the loop
was one big transaction. So that table #inserted was created each time.
This created lots of locks in tempdb, both on the system tables and
locks on the extents that no longer were in use. I realised that the
SELECT INTO was a recent change into that trigger, and the performance
problem was new. I reverted to the old version without the temp table -
and the main performance problem that I had had was gone.
When I later researched this a bit more, I found that using CREATE TABLE
instead of SELECT INTO took less amount of locking resources, although
there still were a few.
The solution in this particular case is to use a table variable.
If I am to give a recommendation, is to use CREATE TABLE, unless you
have a very good reason to use SELECT INTO.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp
|||Erland
Very useful info, thanks
I read recently that in SQL Server 2005 virtual tables (deleted and
insreted) are 'real' work table and not 'virtual' managed in memory tables.
I that truth?
"Erland Sommarskog" <esquel@.sommarskog.se> wrote in message
news:Xns96AC80ED12727Yazorman@.127.0.0.1...
> =?Utf-8?B?U2FuamF5?= (Sanjay@.discussions.microsoft.com) writes:
> The locks on table1 should be the same in both cases.
> If you run will full recovery, there is little difference between SELECT
> INTO and INSERT, but see below for a war story.
> If you run with bulk-logged recovery, SELECT INTO is minimally logged.
> Instead of logging each row, SQL Server logs only the page allocation. I'm
> uncertain of what happens when you have simple recovery. Now, since
> simple recovery is what you have in tempdb, this is what applies. My guess
> goes for minimally logged. Thus, with SELECT INTO you write fewer log
> records, and you can therefor get better performance.
> On the other hand, it takes more resources to create the table. I once
> tried to track down a performance problem, and was running Profiler and
> all that. I had basically given up on the main problem, but decided that I
> should look at a trigger where there was some non-low numbers (they were
> not exceedingly high.) What I had in that trigger was
> SELECT * INTO #inserted FROM inserted
> The point with this is that the virtual table "inserted" is slow to
> work with. Now, this function I was looking into performed a loop, so
> there were many updates on that table within that loop - and the loop
> was one big transaction. So that table #inserted was created each time.
> This created lots of locks in tempdb, both on the system tables and
> locks on the extents that no longer were in use. I realised that the
> SELECT INTO was a recent change into that trigger, and the performance
> problem was new. I reverted to the old version without the temp table -
> and the main performance problem that I had had was gone.
> When I later researched this a bit more, I found that using CREATE TABLE
> instead of SELECT INTO took less amount of locking resources, although
> there still were a few.
> The solution in this particular case is to use a table variable.
> If I am to give a recommendation, is to use CREATE TABLE, unless you
> have a very good reason to use SELECT INTO.
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server SP3 at
> http://www.microsoft.com/sql/techinf...2000/books.asp
>
|||Thanks , this has answers my most of the questions.
Sanjay
"Erland Sommarskog" wrote:

> =?Utf-8?B?U2FuamF5?= (Sanjay@.discussions.microsoft.com) writes:
> The locks on table1 should be the same in both cases.
> If you run will full recovery, there is little difference between SELECT
> INTO and INSERT, but see below for a war story.
> If you run with bulk-logged recovery, SELECT INTO is minimally logged.
> Instead of logging each row, SQL Server logs only the page allocation. I'm
> uncertain of what happens when you have simple recovery. Now, since
> simple recovery is what you have in tempdb, this is what applies. My guess
> goes for minimally logged. Thus, with SELECT INTO you write fewer log
> records, and you can therefor get better performance.
> On the other hand, it takes more resources to create the table. I once
> tried to track down a performance problem, and was running Profiler and
> all that. I had basically given up on the main problem, but decided that I
> should look at a trigger where there was some non-low numbers (they were
> not exceedingly high.) What I had in that trigger was
> SELECT * INTO #inserted FROM inserted
> The point with this is that the virtual table "inserted" is slow to
> work with. Now, this function I was looking into performed a loop, so
> there were many updates on that table within that loop - and the loop
> was one big transaction. So that table #inserted was created each time.
> This created lots of locks in tempdb, both on the system tables and
> locks on the extents that no longer were in use. I realised that the
> SELECT INTO was a recent change into that trigger, and the performance
> problem was new. I reverted to the old version without the temp table -
> and the main performance problem that I had had was gone.
> When I later researched this a bit more, I found that using CREATE TABLE
> instead of SELECT INTO took less amount of locking resources, although
> there still were a few.
> The solution in this particular case is to use a table variable.
> If I am to give a recommendation, is to use CREATE TABLE, unless you
> have a very good reason to use SELECT INTO.
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server SP3 at
> http://www.microsoft.com/sql/techinf...2000/books.asp
>
|||Uri Dimant (urid@.iscar.co.il) writes:
> Very useful info, thanks
> I read recently that in SQL Server 2005 virtual tables (deleted and
> insreted) are 'real' work table and not 'virtual' managed in memory
> tables. I that truth?
I don't know of any changes to inserted/deleted, but that does not mean
that there are not any. There are tons of new features in SQL 2005, and
I have certainly missed more than one. So I can neither confirm nor deny.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

Difference between SELECT INTO AND INSERT INTO

Hi,
I would appreciate if any one can tell me what exactly is the difference
between
1) select * into # abc from table1
2) Insert into #abd select * from table1
assuming tables are indexed properly what is the performance benefit, how
locks are placed in this, how locks will be escalated and duration.
is second statement better than first then why ?Sanjay
It is easy to test it by yourself ,does not?
> 1) select * into # abc from table1
You don't need to create a table before this command , the SELECT INTO
statement creates a new table and populates it with the result set of the
SELECT.
It is possible that you'll see some perfomance improvment by using this
method as well as possible locks that may occur.

> 2) Insert into #abd select * from table1
You will have to issue CREATE TABLE #Table (col INT,......) before the
statement
I'd prefer the second one , but you did not mention about a table variable
you can use as well
"Sanjay" <Sanjay@.discussions.microsoft.com> wrote in message
news:967886AB-7697-4C36-AC0B-0D9B1D0EBB39@.microsoft.com...
> Hi,
> I would appreciate if any one can tell me what exactly is the difference
> between
> 1) select * into # abc from table1
> 2) Insert into #abd select * from table1
> assuming tables are indexed properly what is the performance benefit, how
> locks are placed in this, how locks will be escalated and duration.
> is second statement better than first then why ?|||Hi Sanjay
The main deifference between the 2 queries is creation of the table:
SELECT * INTO .. tries to creates a new table each time its executed.
The table is created in the first run, and from next time if the table
exist, the query is not executed
INSERT INTO <TABLE> needs to have a <table> to proceed sucessfully.
INSERT INTO does'nt create a new table
Please let me know if u have any questions
best Regards,
Chandra
http://chanduas.blogspot.com/
http://groups.msn.com/SQLResource/
---
"Sanjay" wrote:

> Hi,
> I would appreciate if any one can tell me what exactly is the difference
> between
> 1) select * into # abc from table1
> 2) Insert into #abd select * from table1
> assuming tables are indexed properly what is the performance benefit, how
> locks are placed in this, how locks will be escalated and duration.
> is second statement better than first then why ?|||Thanks for reply, but can you tell me how locking architecture in first
scenario.
1) what type of locks will be placed on table1 when used with SELECT INTO,
will it be shared lock on table1 till the records get inserted into temporar
y
table.
and what locking will takes place when i do insert into #tab select * from
table1.
assuming i have some 10000 records to insert.
thanks for all the help
--
Sanjay
"Uri Dimant" wrote:

> Sanjay
> It is easy to test it by yourself ,does not?
> You don't need to create a table before this command , the SELECT INTO
> statement creates a new table and populates it with the result set of the
> SELECT.
> It is possible that you'll see some perfomance improvment by using this
> method as well as possible locks that may occur.
>
> You will have to issue CREATE TABLE #Table (col INT,......) before the
> statement
>
> I'd prefer the second one , but you did not mention about a table variable
> you can use as well
>
>
> "Sanjay" <Sanjay@.discussions.microsoft.com> wrote in message
> news:967886AB-7697-4C36-AC0B-0D9B1D0EBB39@.microsoft.com...
>
>|||Sanjay
By deafult SQL Server implements ROW LOCK but it depends on load of data
and many other things
If persist to use SELECT INTO command run SELECT * INTO #Test FROM Table
WHERE 1=2 to get a structure of the table and then perfom INSERT INTO
command
"Sanjay" <Sanjay@.discussions.microsoft.com> wrote in message
news:680473F9-4933-409E-B3E5-FADEDEE9EAE4@.microsoft.com...[vbcol=seagreen]
> Thanks for reply, but can you tell me how locking architecture in first
> scenario.
> 1) what type of locks will be placed on table1 when used with SELECT INTO,
> will it be shared lock on table1 till the records get inserted into
> temporary
> table.
> and what locking will takes place when i do insert into #tab select * from
> table1.
> assuming i have some 10000 records to insert.
> thanks for all the help
> --
> Sanjay
>
> "Uri Dimant" wrote:
>|||examnotes (chandra@.discussions.microsoft.com) writes:
> The main deifference between the 2 queries is creation of the table:
> SELECT * INTO .. tries to creates a new table each time its executed.
> The table is created in the first run, and from next time if the table
> exist, the query is not executed
In fact you get an error if the table does already exist.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||examnotes (Sanjay@.discussions.microsoft.com) writes:
> Thanks for reply, but can you tell me how locking architecture in first
> scenario.
> 1) what type of locks will be placed on table1 when used with SELECT
> INTO, will it be shared lock on table1 till the records get inserted
> into temporary table.
> and what locking will takes place when i do insert into #tab select * from
> table1.
> assuming i have some 10000 records to insert.
The locks on table1 should be the same in both cases.
If you run will full recovery, there is little difference between SELECT
INTO and INSERT, but see below for a war story.
If you run with bulk-logged recovery, SELECT INTO is minimally logged.
Instead of logging each row, SQL Server logs only the page allocation. I'm
uncertain of what happens when you have simple recovery. Now, since
simple recovery is what you have in tempdb, this is what applies. My guess
goes for minimally logged. Thus, with SELECT INTO you write fewer log
records, and you can therefor get better performance.
On the other hand, it takes more resources to create the table. I once
tried to track down a performance problem, and was running Profiler and
all that. I had basically given up on the main problem, but decided that I
should look at a trigger where there was some non-low numbers (they were
not exceedingly high.) What I had in that trigger was
SELECT * INTO #inserted FROM inserted
The point with this is that the virtual table "inserted" is slow to
work with. Now, this function I was looking into performed a loop, so
there were many updates on that table within that loop - and the loop
was one big transaction. So that table #inserted was created each time.
This created lots of locks in tempdb, both on the system tables and
locks on the extents that no longer were in use. I realised that the
SELECT INTO was a recent change into that trigger, and the performance
problem was new. I reverted to the old version without the temp table -
and the main performance problem that I had had was gone.
When I later researched this a bit more, I found that using CREATE TABLE
instead of SELECT INTO took less amount of locking resources, although
there still were a few.
The solution in this particular case is to use a table variable.
If I am to give a recommendation, is to use CREATE TABLE, unless you
have a very good reason to use SELECT INTO.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Erland
Very useful info, thanks
I read recently that in SQL Server 2005 virtual tables (deleted and
insreted) are 'real' work table and not 'virtual' managed in memory tables.
I that truth?
"Erland Sommarskog" <esquel@.sommarskog.se> wrote in message
news:Xns96AC80ED12727Yazorman@.127.0.0.1...
> examnotes (Sanjay@.discussions.microsoft.com) writes:
> The locks on table1 should be the same in both cases.
> If you run will full recovery, there is little difference between SELECT
> INTO and INSERT, but see below for a war story.
> If you run with bulk-logged recovery, SELECT INTO is minimally logged.
> Instead of logging each row, SQL Server logs only the page allocation. I'm
> uncertain of what happens when you have simple recovery. Now, since
> simple recovery is what you have in tempdb, this is what applies. My guess
> goes for minimally logged. Thus, with SELECT INTO you write fewer log
> records, and you can therefor get better performance.
> On the other hand, it takes more resources to create the table. I once
> tried to track down a performance problem, and was running Profiler and
> all that. I had basically given up on the main problem, but decided that I
> should look at a trigger where there was some non-low numbers (they were
> not exceedingly high.) What I had in that trigger was
> SELECT * INTO #inserted FROM inserted
> The point with this is that the virtual table "inserted" is slow to
> work with. Now, this function I was looking into performed a loop, so
> there were many updates on that table within that loop - and the loop
> was one big transaction. So that table #inserted was created each time.
> This created lots of locks in tempdb, both on the system tables and
> locks on the extents that no longer were in use. I realised that the
> SELECT INTO was a recent change into that trigger, and the performance
> problem was new. I reverted to the old version without the temp table -
> and the main performance problem that I had had was gone.
> When I later researched this a bit more, I found that using CREATE TABLE
> instead of SELECT INTO took less amount of locking resources, although
> there still were a few.
> The solution in this particular case is to use a table variable.
> If I am to give a recommendation, is to use CREATE TABLE, unless you
> have a very good reason to use SELECT INTO.
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server SP3 at
> http://www.microsoft.com/sql/techin.../2000/books.asp
>|||Thanks , this has answers my most of the questions.
--
Sanjay
"Erland Sommarskog" wrote:

> examnotes (Sanjay@.discussions.microsoft.com) writes:
> The locks on table1 should be the same in both cases.
> If you run will full recovery, there is little difference between SELECT
> INTO and INSERT, but see below for a war story.
> If you run with bulk-logged recovery, SELECT INTO is minimally logged.
> Instead of logging each row, SQL Server logs only the page allocation. I'm
> uncertain of what happens when you have simple recovery. Now, since
> simple recovery is what you have in tempdb, this is what applies. My guess
> goes for minimally logged. Thus, with SELECT INTO you write fewer log
> records, and you can therefor get better performance.
> On the other hand, it takes more resources to create the table. I once
> tried to track down a performance problem, and was running Profiler and
> all that. I had basically given up on the main problem, but decided that I
> should look at a trigger where there was some non-low numbers (they were
> not exceedingly high.) What I had in that trigger was
> SELECT * INTO #inserted FROM inserted
> The point with this is that the virtual table "inserted" is slow to
> work with. Now, this function I was looking into performed a loop, so
> there were many updates on that table within that loop - and the loop
> was one big transaction. So that table #inserted was created each time.
> This created lots of locks in tempdb, both on the system tables and
> locks on the extents that no longer were in use. I realised that the
> SELECT INTO was a recent change into that trigger, and the performance
> problem was new. I reverted to the old version without the temp table -
> and the main performance problem that I had had was gone.
> When I later researched this a bit more, I found that using CREATE TABLE
> instead of SELECT INTO took less amount of locking resources, although
> there still were a few.
> The solution in this particular case is to use a table variable.
> If I am to give a recommendation, is to use CREATE TABLE, unless you
> have a very good reason to use SELECT INTO.
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server SP3 at
> http://www.microsoft.com/sql/techin.../2000/books.asp
>|||Uri Dimant (urid@.iscar.co.il) writes:
> Very useful info, thanks
> I read recently that in SQL Server 2005 virtual tables (deleted and
> insreted) are 'real' work table and not 'virtual' managed in memory
> tables. I that truth?
I don't know of any changes to inserted/deleted, but that does not mean
that there are not any. There are tons of new features in SQL 2005, and
I have certainly missed more than one. So I can neither confirm nor deny.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

Difference between SELECT INTO AND INSERT INTO

Hi,
I would appreciate if any one can tell me what exactly is the difference
between
1) select * into # abc from table1
2) Insert into #abd select * from table1
assuming tables are indexed properly what is the performance benefit, how
locks are placed in this, how locks will be escalated and duration.
is second statement better than first then why ?Sanjay
It is easy to test it by yourself ,does not?
> 1) select * into # abc from table1
You don't need to create a table before this command , the SELECT INTO
statement creates a new table and populates it with the result set of the
SELECT.
It is possible that you'll see some perfomance improvment by using this
method as well as possible locks that may occur.
> 2) Insert into #abd select * from table1
You will have to issue CREATE TABLE #Table (col INT,......) before the
statement
I'd prefer the second one , but you did not mention about a table variable
you can use as well
"Sanjay" <Sanjay@.discussions.microsoft.com> wrote in message
news:967886AB-7697-4C36-AC0B-0D9B1D0EBB39@.microsoft.com...
> Hi,
> I would appreciate if any one can tell me what exactly is the difference
> between
> 1) select * into # abc from table1
> 2) Insert into #abd select * from table1
> assuming tables are indexed properly what is the performance benefit, how
> locks are placed in this, how locks will be escalated and duration.
> is second statement better than first then why ?|||Hi Sanjay
The main deifference between the 2 queries is creation of the table:
SELECT * INTO .. tries to creates a new table each time its executed.
The table is created in the first run, and from next time if the table
exist, the query is not executed
INSERT INTO <TABLE> needs to have a <table> to proceed sucessfully.
INSERT INTO does'nt create a new table
Please let me know if u have any questions
best Regards,
Chandra
http://chanduas.blogspot.com/
http://groups.msn.com/SQLResource/
---
"Sanjay" wrote:
> Hi,
> I would appreciate if any one can tell me what exactly is the difference
> between
> 1) select * into # abc from table1
> 2) Insert into #abd select * from table1
> assuming tables are indexed properly what is the performance benefit, how
> locks are placed in this, how locks will be escalated and duration.
> is second statement better than first then why ?|||Thanks for reply, but can you tell me how locking architecture in first
scenario.
1) what type of locks will be placed on table1 when used with SELECT INTO,
will it be shared lock on table1 till the records get inserted into temporary
table.
and what locking will takes place when i do insert into #tab select * from
table1.
assuming i have some 10000 records to insert.
thanks for all the help
--
Sanjay
"Uri Dimant" wrote:
> Sanjay
> It is easy to test it by yourself ,does not?
> > 1) select * into # abc from table1
> You don't need to create a table before this command , the SELECT INTO
> statement creates a new table and populates it with the result set of the
> SELECT.
> It is possible that you'll see some perfomance improvment by using this
> method as well as possible locks that may occur.
> > 2) Insert into #abd select * from table1
> You will have to issue CREATE TABLE #Table (col INT,......) before the
> statement
>
> I'd prefer the second one , but you did not mention about a table variable
> you can use as well
>
>
> "Sanjay" <Sanjay@.discussions.microsoft.com> wrote in message
> news:967886AB-7697-4C36-AC0B-0D9B1D0EBB39@.microsoft.com...
> > Hi,
> > I would appreciate if any one can tell me what exactly is the difference
> > between
> >
> > 1) select * into # abc from table1
> > 2) Insert into #abd select * from table1
> > assuming tables are indexed properly what is the performance benefit, how
> > locks are placed in this, how locks will be escalated and duration.
> >
> > is second statement better than first then why ?
>
>|||Sanjay
By deafult SQL Server implements ROW LOCK but it depends on load of data
and many other things
If persist to use SELECT INTO command run SELECT * INTO #Test FROM Table
WHERE 1=2 to get a structure of the table and then perfom INSERT INTO
command
"Sanjay" <Sanjay@.discussions.microsoft.com> wrote in message
news:680473F9-4933-409E-B3E5-FADEDEE9EAE4@.microsoft.com...
> Thanks for reply, but can you tell me how locking architecture in first
> scenario.
> 1) what type of locks will be placed on table1 when used with SELECT INTO,
> will it be shared lock on table1 till the records get inserted into
> temporary
> table.
> and what locking will takes place when i do insert into #tab select * from
> table1.
> assuming i have some 10000 records to insert.
> thanks for all the help
> --
> Sanjay
>
> "Uri Dimant" wrote:
>> Sanjay
>> It is easy to test it by yourself ,does not?
>> > 1) select * into # abc from table1
>> You don't need to create a table before this command , the SELECT INTO
>> statement creates a new table and populates it with the result set of the
>> SELECT.
>> It is possible that you'll see some perfomance improvment by using this
>> method as well as possible locks that may occur.
>> > 2) Insert into #abd select * from table1
>> You will have to issue CREATE TABLE #Table (col INT,......) before the
>> statement
>>
>> I'd prefer the second one , but you did not mention about a table
>> variable
>> you can use as well
>>
>>
>> "Sanjay" <Sanjay@.discussions.microsoft.com> wrote in message
>> news:967886AB-7697-4C36-AC0B-0D9B1D0EBB39@.microsoft.com...
>> > Hi,
>> > I would appreciate if any one can tell me what exactly is the
>> > difference
>> > between
>> >
>> > 1) select * into # abc from table1
>> > 2) Insert into #abd select * from table1
>> > assuming tables are indexed properly what is the performance benefit,
>> > how
>> > locks are placed in this, how locks will be escalated and duration.
>> >
>> > is second statement better than first then why ?
>>|||=?Utf-8?B?Q2hhbmRyYQ==?= (chandra@.discussions.microsoft.com) writes:
> The main deifference between the 2 queries is creation of the table:
> SELECT * INTO .. tries to creates a new table each time its executed.
> The table is created in the first run, and from next time if the table
> exist, the query is not executed
In fact you get an error if the table does already exist.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp|||=?Utf-8?B?U2FuamF5?= (Sanjay@.discussions.microsoft.com) writes:
> Thanks for reply, but can you tell me how locking architecture in first
> scenario.
> 1) what type of locks will be placed on table1 when used with SELECT
> INTO, will it be shared lock on table1 till the records get inserted
> into temporary table.
> and what locking will takes place when i do insert into #tab select * from
> table1.
> assuming i have some 10000 records to insert.
The locks on table1 should be the same in both cases.
If you run will full recovery, there is little difference between SELECT
INTO and INSERT, but see below for a war story.
If you run with bulk-logged recovery, SELECT INTO is minimally logged.
Instead of logging each row, SQL Server logs only the page allocation. I'm
uncertain of what happens when you have simple recovery. Now, since
simple recovery is what you have in tempdb, this is what applies. My guess
goes for minimally logged. Thus, with SELECT INTO you write fewer log
records, and you can therefor get better performance.
On the other hand, it takes more resources to create the table. I once
tried to track down a performance problem, and was running Profiler and
all that. I had basically given up on the main problem, but decided that I
should look at a trigger where there was some non-low numbers (they were
not exceedingly high.) What I had in that trigger was
SELECT * INTO #inserted FROM inserted
The point with this is that the virtual table "inserted" is slow to
work with. Now, this function I was looking into performed a loop, so
there were many updates on that table within that loop - and the loop
was one big transaction. So that table #inserted was created each time.
This created lots of locks in tempdb, both on the system tables and
locks on the extents that no longer were in use. I realised that the
SELECT INTO was a recent change into that trigger, and the performance
problem was new. I reverted to the old version without the temp table -
and the main performance problem that I had had was gone.
When I later researched this a bit more, I found that using CREATE TABLE
instead of SELECT INTO took less amount of locking resources, although
there still were a few.
The solution in this particular case is to use a table variable.
If I am to give a recommendation, is to use CREATE TABLE, unless you
have a very good reason to use SELECT INTO.
--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp|||Erland
Very useful info, thanks
I read recently that in SQL Server 2005 virtual tables (deleted and
insreted) are 'real' work table and not 'virtual' managed in memory tables.
I that truth?
"Erland Sommarskog" <esquel@.sommarskog.se> wrote in message
news:Xns96AC80ED12727Yazorman@.127.0.0.1...
> =?Utf-8?B?U2FuamF5?= (Sanjay@.discussions.microsoft.com) writes:
>> Thanks for reply, but can you tell me how locking architecture in first
>> scenario.
>> 1) what type of locks will be placed on table1 when used with SELECT
>> INTO, will it be shared lock on table1 till the records get inserted
>> into temporary table.
>> and what locking will takes place when i do insert into #tab select *
>> from
>> table1.
>> assuming i have some 10000 records to insert.
> The locks on table1 should be the same in both cases.
> If you run will full recovery, there is little difference between SELECT
> INTO and INSERT, but see below for a war story.
> If you run with bulk-logged recovery, SELECT INTO is minimally logged.
> Instead of logging each row, SQL Server logs only the page allocation. I'm
> uncertain of what happens when you have simple recovery. Now, since
> simple recovery is what you have in tempdb, this is what applies. My guess
> goes for minimally logged. Thus, with SELECT INTO you write fewer log
> records, and you can therefor get better performance.
> On the other hand, it takes more resources to create the table. I once
> tried to track down a performance problem, and was running Profiler and
> all that. I had basically given up on the main problem, but decided that I
> should look at a trigger where there was some non-low numbers (they were
> not exceedingly high.) What I had in that trigger was
> SELECT * INTO #inserted FROM inserted
> The point with this is that the virtual table "inserted" is slow to
> work with. Now, this function I was looking into performed a loop, so
> there were many updates on that table within that loop - and the loop
> was one big transaction. So that table #inserted was created each time.
> This created lots of locks in tempdb, both on the system tables and
> locks on the extents that no longer were in use. I realised that the
> SELECT INTO was a recent change into that trigger, and the performance
> problem was new. I reverted to the old version without the temp table -
> and the main performance problem that I had had was gone.
> When I later researched this a bit more, I found that using CREATE TABLE
> instead of SELECT INTO took less amount of locking resources, although
> there still were a few.
> The solution in this particular case is to use a table variable.
> If I am to give a recommendation, is to use CREATE TABLE, unless you
> have a very good reason to use SELECT INTO.
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server SP3 at
> http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp
>|||Thanks , this has answers my most of the questions.
--
Sanjay
"Erland Sommarskog" wrote:
> =?Utf-8?B?U2FuamF5?= (Sanjay@.discussions.microsoft.com) writes:
> > Thanks for reply, but can you tell me how locking architecture in first
> > scenario.
> > 1) what type of locks will be placed on table1 when used with SELECT
> > INTO, will it be shared lock on table1 till the records get inserted
> > into temporary table.
> >
> > and what locking will takes place when i do insert into #tab select * from
> > table1.
> > assuming i have some 10000 records to insert.
> The locks on table1 should be the same in both cases.
> If you run will full recovery, there is little difference between SELECT
> INTO and INSERT, but see below for a war story.
> If you run with bulk-logged recovery, SELECT INTO is minimally logged.
> Instead of logging each row, SQL Server logs only the page allocation. I'm
> uncertain of what happens when you have simple recovery. Now, since
> simple recovery is what you have in tempdb, this is what applies. My guess
> goes for minimally logged. Thus, with SELECT INTO you write fewer log
> records, and you can therefor get better performance.
> On the other hand, it takes more resources to create the table. I once
> tried to track down a performance problem, and was running Profiler and
> all that. I had basically given up on the main problem, but decided that I
> should look at a trigger where there was some non-low numbers (they were
> not exceedingly high.) What I had in that trigger was
> SELECT * INTO #inserted FROM inserted
> The point with this is that the virtual table "inserted" is slow to
> work with. Now, this function I was looking into performed a loop, so
> there were many updates on that table within that loop - and the loop
> was one big transaction. So that table #inserted was created each time.
> This created lots of locks in tempdb, both on the system tables and
> locks on the extents that no longer were in use. I realised that the
> SELECT INTO was a recent change into that trigger, and the performance
> problem was new. I reverted to the old version without the temp table -
> and the main performance problem that I had had was gone.
> When I later researched this a bit more, I found that using CREATE TABLE
> instead of SELECT INTO took less amount of locking resources, although
> there still were a few.
> The solution in this particular case is to use a table variable.
> If I am to give a recommendation, is to use CREATE TABLE, unless you
> have a very good reason to use SELECT INTO.
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server SP3 at
> http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp
>|||Uri Dimant (urid@.iscar.co.il) writes:
> Very useful info, thanks
> I read recently that in SQL Server 2005 virtual tables (deleted and
> insreted) are 'real' work table and not 'virtual' managed in memory
> tables. I that truth?
I don't know of any changes to inserted/deleted, but that does not mean
that there are not any. There are tons of new features in SQL 2005, and
I have certainly missed more than one. So I can neither confirm nor deny.
--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp|||In addition to the other replies: it is my experience that SELECT ..
INTO causes a Checkpoint to occur. I am running simple recovery mode
(don't know if that is the cause). Usually, this checkpoint slows down
the transaction considerably, because my server is pretty busy and will
require a lot of writes before the checkpoint is finished.
Gert-Jan
Sanjay wrote:
> Hi,
> I would appreciate if any one can tell me what exactly is the difference
> between
> 1) select * into # abc from table1
> 2) Insert into #abd select * from table1
> assuming tables are indexed properly what is the performance benefit, how
> locks are placed in this, how locks will be escalated and duration.
> is second statement better than first then why ?

Development, Staging, Production - best practice

Hi,
I would appreciate your advice in deploying enterprise BI solution. The task
at hand is this: I need to get the data from DW (SQL 2005), process into SSAS
and present with SSRS. I may also need to offer a third party OLAP browser as
a part this of solution. My initial thoughts about the setup for this are:
- SRV1: Development server, with all SQL 2005 components, IIS, and any third
party OLAP tools that we might pick.
-SRV2: Staging/Processing. Schedule and run SSIS to get data from DW, and
refresh and process SSAS cubes.
-SRV3: Production Reporting/Web server: This server would hold processed
cubes from SRV2 (archived and restored on SRV3), SSRS, IIS any any third
party OLAP browser that we may pick.
Initially there will be 2-3 developers, 15-20 cubes and 30-40 reports. We
are targeting to server 20-30 Report users.
Does the above setup make sense? Do I need dedicated web server with or
without SSRS? Please share thoughts or advise of any best practice articles.
Thank you.
ZoranHello Zoran,
The server setup need to be decide with that how large your cube is and how
many data your cube stored.
Yes, you need to seperate a stand alone server to process all the Cube data
and use the Reporting Services as the Front End Server.
I don't think you need to dedicate the web server with Reporting Services
because the Reporting Services is also a web application in the web front
end. Also, considering your report users, your report web quest will not at
a high level.
Hope this helps.
Sincerely,
Wei Lu
Microsoft Online Community Support
==================================================
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
==================================================This posting is provided "AS IS" with no warranties, and confers no rights.|||Thank you Wei,
Currently all our cubes are MOLAP, with database sizes ranging from 30 MB to
300 MB. I read that one of the most efficient ways to move from Staging to
Production is backup and retore. But, is it good practice to keep production
cubes on the same server as SSRS? Technically, there would be no processing
on that server, cubes would only sit there for user's queries...
Zoran
"Wei Lu [MSFT]" wrote:
> Hello Zoran,
> The server setup need to be decide with that how large your cube is and how
> many data your cube stored.
> Yes, you need to seperate a stand alone server to process all the Cube data
> and use the Reporting Services as the Front End Server.
> I don't think you need to dedicate the web server with Reporting Services
> because the Reporting Services is also a web application in the web front
> end. Also, considering your report users, your report web quest will not at
> a high level.
> Hope this helps.
> Sincerely,
> Wei Lu
> Microsoft Online Community Support
> ==================================================> When responding to posts, please "Reply to Group" via your newsreader so
> that others may learn and benefit from your issue.
> ==================================================> This posting is provided "AS IS" with no warranties, and confers no rights.
>|||Hello Zoran,
Whether putting the SSRS together with the Cube depends on the size and how
system resource used by the SSAS.
In your scenario, I think it is OK for you to put the SSRS on the same
server at this time. But if your cube grows, you may need to seperate the
SSAS with SSRS.
Sincerely,
Wei Lu
Microsoft Online Community Support
==================================================
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
==================================================This posting is provided "AS IS" with no warranties, and confers no rights.|||Hi ,
How is everything going? Please feel free to let me know if you need any
assistance.
Sincerely,
Wei Lu
Microsoft Online Community Support
==================================================
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
==================================================This posting is provided "AS IS" with no warranties, and confers no rights.|||Thank you Wei.
In your previous post you you mentioned "depends how system resources
(are)used by SSAS". In my scenario, they would be used for user queries
(direct and via SSRS). There would be no processing done on this server.
I can certainly see the number of cubes grow in near future. Is one server
for SSRS and SSAS going to work, or am I setting myself up for upgrade in
near future?
Regards,
Zoran Knezic
"Wei Lu [MSFT]" wrote:
> Hi ,
> How is everything going? Please feel free to let me know if you need any
> assistance.
> Sincerely,
> Wei Lu
> Microsoft Online Community Support
> ==================================================> When responding to posts, please "Reply to Group" via your newsreader so
> that others may learn and benefit from your issue.
> ==================================================> This posting is provided "AS IS" with no warranties, and confers no rights.
>|||RS 2005 does all rendering in RAM. As long as you have enough RAM I think
you will be OK. I would suggest starting off on one server. Perhaps let
management know that you will need to monitor the usage and there is a
chance they will need to be split onto separate servers. RS 2008 is going to
be much smarter and with how it renders.
You said the following: Initially there will be 2-3 developers, 15-20 cubes
and 30-40 reports. We
are targeting to server 20-30 Report users.
This is not that many users. I do not have cubes but I have a datamart which
includes a table with 150 million rows (small rows admittedly) plus several
tables that have several million rows. RS 2005 and the datamart are on the
same server without difficulty. Similar number of users (more reports). My
server is old (4 years old) and ready for replacement. I have 4
processors(2.4 GHz), 4 Gigs of Ram (so this is not a huge powerful box by
any means). Performance is very very good for me. Running SQL 2005 Standard
edition.
Since you are just getting started. If you are able to plan on using RS 2008
I would do so. RS 2008 should be able to use a SQL 2005 db for its
metadata/object caching so it is a licensing issue.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"zk_" <zk_@.newsgroup.nospam> wrote in message
news:EB03F7C3-13D3-40D2-8991-7FAF755EEFCE@.microsoft.com...
> Thank you Wei.
> In your previous post you you mentioned "depends how system resources
> (are)used by SSAS". In my scenario, they would be used for user queries
> (direct and via SSRS). There would be no processing done on this server.
> I can certainly see the number of cubes grow in near future. Is one server
> for SSRS and SSAS going to work, or am I setting myself up for upgrade in
> near future?
> Regards,
> Zoran Knezic
> "Wei Lu [MSFT]" wrote:
>> Hi ,
>> How is everything going? Please feel free to let me know if you need any
>> assistance.
>> Sincerely,
>> Wei Lu
>> Microsoft Online Community Support
>> ==================================================>> When responding to posts, please "Reply to Group" via your newsreader so
>> that others may learn and benefit from your issue.
>> ==================================================>> This posting is provided "AS IS" with no warranties, and confers no
>> rights.
>>|||Thank you Bruce.
It is good to hear a first hand experience with DB and SSRS on the same
server.
I am sure that SSAS creates bigger overhead then DB, but judging by your
experience, I might be OK.
My IMIT colleagues came up with 2x 2.8 GHz server, with 16 Gb of RAM.
Alternatively I might be able to suggest two servers, 2x2.8 GHz 8GB of RAM
each.
Once again, thank you for your time.
Zoran Knezic
"Bruce L-C [MVP]" wrote:
> RS 2005 does all rendering in RAM. As long as you have enough RAM I think
> you will be OK. I would suggest starting off on one server. Perhaps let
> management know that you will need to monitor the usage and there is a
> chance they will need to be split onto separate servers. RS 2008 is going to
> be much smarter and with how it renders.
> You said the following: Initially there will be 2-3 developers, 15-20 cubes
> and 30-40 reports. We
> are targeting to server 20-30 Report users.
> This is not that many users. I do not have cubes but I have a datamart which
> includes a table with 150 million rows (small rows admittedly) plus several
> tables that have several million rows. RS 2005 and the datamart are on the
> same server without difficulty. Similar number of users (more reports). My
> server is old (4 years old) and ready for replacement. I have 4
> processors(2.4 GHz), 4 Gigs of Ram (so this is not a huge powerful box by
> any means). Performance is very very good for me. Running SQL 2005 Standard
> edition.
> Since you are just getting started. If you are able to plan on using RS 2008
> I would do so. RS 2008 should be able to use a SQL 2005 db for its
> metadata/object caching so it is a licensing issue.
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
>
> "zk_" <zk_@.newsgroup.nospam> wrote in message
> news:EB03F7C3-13D3-40D2-8991-7FAF755EEFCE@.microsoft.com...
> > Thank you Wei.
> > In your previous post you you mentioned "depends how system resources
> > (are)used by SSAS". In my scenario, they would be used for user queries
> > (direct and via SSRS). There would be no processing done on this server.
> > I can certainly see the number of cubes grow in near future. Is one server
> > for SSRS and SSAS going to work, or am I setting myself up for upgrade in
> > near future?
> >
> > Regards,
> > Zoran Knezic
> >
> > "Wei Lu [MSFT]" wrote:
> >
> >> Hi ,
> >>
> >> How is everything going? Please feel free to let me know if you need any
> >> assistance.
> >>
> >> Sincerely,
> >>
> >> Wei Lu
> >> Microsoft Online Community Support
> >>
> >> ==================================================> >>
> >> When responding to posts, please "Reply to Group" via your newsreader so
> >> that others may learn and benefit from your issue.
> >>
> >> ==================================================> >> This posting is provided "AS IS" with no warranties, and confers no
> >> rights.
> >>
> >>
>
>