Difference between Shrinking a DB and a FILE

Hi,

What is the difference between shrinking a DB with that of a file.

When you select a DB to shrink will it not shrink all the files in it.

Because when I select Files to shrink it does not prompt me with
a specific file.

So I am confused as to why SQL server 2K5 asks if you need to shrink DB or FILE

Tnx

Shrink database shrinks all the files in a database, shrink file shrinks one specific file in a database.

The way that data files(.mdfs) and log files(.ldfs) shrink is different, you should read in Books On Line about it.

Difference between SET and SELECT

Hii,
Anybody knows the difference between the SET and SELECT statement while assigning variables

Quote:

Originally Posted by sukeshchand

Hii,
Anybody knows the difference between the SET and SELECT statement while assigning variables

Hi

declare @.i int
set @.i=1
This s used to assign constant values

select @.i=max(column_name)from table_name
for ex.
select @.i=max(emp_id) from table_emp

This abve stmnt assign the max of the column value to the variable @.i|||

Quote:

Originally Posted by davash6

Hi

declare @.i int
set @.i=1
This s used to assign constant values

select @.i=max(column_name)from table_name
for ex.
select @.i=max(emp_id) from table_emp

This abve stmnt assign the max of the column value to the variable @.i

we can also use select statement to assign constants like
Select @.i=1 from tableEmp|||We can assign more than one value by using Select Statement but in SET we cant
Eg:
Select @.i=Mark1, @.j=Mark2 from Marks where SID=12323

but if we use SET statement We must write two different statement to do that
like

SET @.i=Select Mark1 From Marks where SID=12323
SET @.j=Select Mark2 From Marks where SID=12323

so i think the select statement is fast in this case

anybody have any other comments??? then pls post...|||SELECT:
Just selects data from a table, and does not change the values in the actual table.
so you see a view of the data.

select * from table1
see all data is table1

select * from table1
order by col01
see all data in table1 with the data ordered by col01.
so your view is different to the actual table but the table is unchanged.

SET:
this is used to change values in tables etc.

example 1:
update table1
set col01 = '23' where col01 = '21'
So set all instances of col01 to value '23' where it currently = '21'

example 2:
declare @.variable as int
set @.variable = '123'
create a variable called @.variable and store value '123' in it.

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 ?

Difference between nvarchar(4000) and nvarchar(3999) in SQL Server

Hi
Can anybody explain why this select returns different results in SQL 200 and
SQL 2005?
=========
if exists (select * from sysobjects where name = 'test') begin
drop table test
end
GO
if exists(select * from sysobjects where name = 'vw') begin
drop view vw
end
GO
create table test (a int, b int)
GO
create view vw as select * from test
GO
declare @.s nvarchar(4000) set @.s = ' select '
select @.s = @.s + cast(',
'+ cast(c.name as nvarchar(100))+ ' ' + cast(REPLACE(c.name ,'CM_','')
as nvarchar(100)) as nvarchar(4000))
from syscolumns c join sysobjects o on c.id = o.id
where o.name = 'vw'
order by c.colid
print @.s
===========
and if you change nvarchar(4000) to nvarchar(3999) it returns the same on
both 2000 and 2005?
==========
if exists (select * from sysobjects where name = 'test') begin
drop table test
end
GO
if exists(select * from sysobjects where name = 'vw') begin
drop view vw
end
GO
create table test (a int, b int)
GO
create view vw as select * from test
GO
declare @.s nvarchar(4000) set @.s = ' select '
select @.s = @.s + cast(',
'+ cast(c.name as nvarchar(100))+ ' ' + cast(REPLACE(c.name ,'CM_','')
as nvarchar(100)) as nvarchar(3999))
from syscolumns c join sysobjects o on c.id = o.id
where o.name = 'vw'
order by c.colid
print @.sYurity, what do you mean?
Both queries return the same result for me on both platforms. I see no
difference (on either platform) between the 3999 varchar and 4000
varchar version.
The result I get is:
select ,
a a,
b b
However, if the you select from a table with @.localvar = @.localvar +
<some column>, and the selection includes more than one row, then the
result is undefined.
Apart from the result listed above, SQL Server might easily return
select ,
b b,
a a
or even
select ,
a a,
a a,
b b,
b b
In other words: I wouldn't use it.
Gert-Jan
Yuriy wrote:
> Hi
> Can anybody explain why this select returns different results in SQL 200 a
nd
> SQL 2005?
> =========
> if exists (select * from sysobjects where name = 'test') begin
> drop table test
> end
> GO
> if exists(select * from sysobjects where name = 'vw') begin
> drop view vw
> end
> GO
> create table test (a int, b int)
> GO
> create view vw as select * from test
> GO
> declare @.s nvarchar(4000) set @.s = ' select '
> select @.s = @.s + cast(',
> '+ cast(c.name as nvarchar(100))+ ' ' + cast(REPLACE(c.name ,'CM_',''
)
> as nvarchar(100)) as nvarchar(4000))
> from syscolumns c join sysobjects o on c.id = o.id
> where o.name = 'vw'
> order by c.colid
> print @.s
> ===========
> and if you change nvarchar(4000) to nvarchar(3999) it returns the same on
> both 2000 and 2005?
> ==========
> if exists (select * from sysobjects where name = 'test') begin
> drop table test
> end
> GO
> if exists(select * from sysobjects where name = 'vw') begin
> drop view vw
> end
> GO
> create table test (a int, b int)
> GO
> create view vw as select * from test
> GO
> declare @.s nvarchar(4000) set @.s = ' select '
> select @.s = @.s + cast(',
> '+ cast(c.name as nvarchar(100))+ ' ' + cast(REPLACE(c.name ,'CM_',''
)
> as nvarchar(100)) as nvarchar(3999))
> from syscolumns c join sysobjects o on c.id = o.id
> where o.name = 'vw'
> order by c.colid
> print @.s|||Hi
Under SQL server 2005 I get
select ,
b b
(note that "a a" is absent)
in case of 4000 and I get correct result
select ,
a a,
b b
in case of 3999.
Can anyone else reproduce my problem?
Meanwhile, I do not agree with you about unspecified case, because there
is "order by" specifying the order.
Yuriy
> Yurity, what do you mean?
> Both queries return the same result for me on both platforms. I see no
> difference (on either platform) between the 3999 varchar and 4000
> varchar version.
> The result I get is:
> select ,
> a a,
> b b
> However, if the you select from a table with @.localvar = @.localvar +
> <some column>, and the selection includes more than one row, then the
> result is undefined.
> Apart from the result listed above, SQL Server might easily return
> select ,
> b b,
> a a
> or even
> select ,
> a a,
> a a,
> b b,
> b b
> In other words: I wouldn't use it.
> Gert-Jan
> Yuriy wrote:
>|||It is interesting that changing nvarchar(4000) to nvarchar(3999) changes
execution plan. Can you post your execution plan of the select stement here
?
Thank you
> Yurity, what do you mean?
> Both queries return the same result for me on both platforms. I see no
> difference (on either platform) between the 3999 varchar and 4000
> varchar version.
> The result I get is:
> select ,
> a a,
> b b
> However, if the you select from a table with @.localvar = @.localvar +
> <some column>, and the selection includes more than one row, then the
> result is undefined.
> Apart from the result listed above, SQL Server might easily return
> select ,
> b b,
> a a
> or even
> select ,
> a a,
> a a,
> b b,
> b b
> In other words: I wouldn't use it.
> Gert-Jan
> Yuriy wrote:
>|||Hello, Yuriy
I was able to reproduce the problem you mentioned, on SQL Server 2005
Express Edition (9.00.1399), in any database other than master.
However, in the master database, it gives the expected results. It's
not clear to me (yet) what is the difference that makes SQL Server to
execute the query in the usual way in the master database, but not in
the other databases.
However, the result of such a query is undefined, as documented by KB
Article 287515:
"The correct behavior for an aggregate concatenation query is
undefined."
If you insist on using aggregate concatenation, my suggestion is to use
it on a single table (not using any JOIN-s) and not to use any other
expressions (either in the SELECT clause or in the ORDER BY clause).
For example:
[...]
declare @.t TABLE (colid smallint PRIMARY KEY, x nvarchar(4000))
insert into @.t
select colid, cast(',
'+ cast(c.name as nvarchar(100))+ ' ' + cast(REPLACE(c.name
,'CM_','')
as nvarchar(100)) as nvarchar(4000))
from syscolumns c join sysobjects o on c.id = o.id
where o.name = 'vw'
order by c.colid
declare @.s nvarchar(4000) set @.s = ' select '
select @.s = @.s + x FROM @.t order by colid
[...]
Razvan|||Agree with you. Just faced with lots of code which uses the fact that under
SQL 2000 it usually works fine.
Thank you

> Hello, Yuriy
> I was able to reproduce the problem you mentioned, on SQL Server 2005
> Express Edition (9.00.1399), in any database other than master.
> However, in the master database, it gives the expected results. It's
> not clear to me (yet) what is the difference that makes SQL Server to
> execute the query in the usual way in the master database, but not in
> the other databases.
> However, the result of such a query is undefined, as documented by KB
> Article 287515:
> "The correct behavior for an aggregate concatenation query is
> undefined."
> If you insist on using aggregate concatenation, my suggestion is to
> use it on a single table (not using any JOIN-s) and not to use any
> other expressions (either in the SELECT clause or in the ORDER BY
> clause). For example:
> [...]
> declare @.t TABLE (colid smallint PRIMARY KEY, x nvarchar(4000))
> insert into @.t
> select colid, cast(',
> '+ cast(c.name as nvarchar(100))+ ' ' + cast(REPLACE(c.name
> ,'CM_','')
> as nvarchar(100)) as nvarchar(4000))
> from syscolumns c join sysobjects o on c.id = o.id
> where o.name = 'vw'
> order by c.colid
> declare @.s nvarchar(4000) set @.s = ' select '
> select @.s = @.s + x FROM @.t order by colid [...]
> Razvan
>|||Yes, that must be a real disappointment. I would never have guessed that
the latest version of SQL Server would only use the last row...
Maybe it is an option to lower the compatibility level. Maybe if you run
in "SQL 2000" mode the behavior is the same on SQL 2005.
Gert-Jan
Yuriy Solodkyy wrote:
> Agree with you. Just faced with lots of code which uses the fact that und
er
> SQL 2000 it usually works fine.
> Thank you
>|||It was initially found in level 8 DB. Option (fast 1) helps in this case,
but anyway it is necessary to stop using such selects. Meanwhile, the same
code is promoted by Ken Handerson's book "The Guru's Guide to Transact-SQL".
> Yes, that must be a real disappointment. I would never have guessed
> that the latest version of SQL Server would only use the last row...
> Maybe it is an option to lower the compatibility level. Maybe if you
> run in "SQL 2000" mode the behavior is the same on SQL 2005.
> Gert-Jan
> Yuriy Solodkyy wrote:
>

difference between COUNT(*) & COUNT(1)

Hi,
1 - select count(*) from tablename
2 - select count(1) from tablename
query 1 and 2 return the same value as output.
what is the difference between count(*) and count(1) ?
which is efficient?
Please advise
Thanks,
Soura
do
set statistics io on , client statistics ,server statistics and
execution plan in query analyzer.
you will no major different for your both queries, What you need to have
proper cluster index on the table to improve count performancae.
Regards
Amish
*** Sent via Developersdex http://www.codecomments.com ***
|||SouRa wrote:
> Hi,
> 1 - select count(*) from tablename
> 2 - select count(1) from tablename
> query 1 and 2 return the same value as output.
> what is the difference between count(*) and count(1) ?
> which is efficient?
> Please advise
> Thanks,
> Soura
No difference. If you check the execution plans you should find that
they are identical.
David Portas
SQL Server MVP
|||> you will no major different for your both queries, What you need to have
> proper cluster index on the table to improve count performancae.
Actually, a non-clustered index will be better. A non-clustered index on any column will cover that
query, so SQL Server can scan the leaf-level in that index instead of scanning the leaf-level in the
clustered index (which are the data pages). The more narrow the non-clustered index, the better.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Amish Shah" <shahamishm@.gmail.com> wrote in message news:%23ulIsi2EGHA.1508@.TK2MSFTNGP15.phx.gbl...
> do
> set statistics io on , client statistics ,server statistics and
> execution plan in query analyzer.
> you will no major different for your both queries, What you need to have
> proper cluster index on the table to improve count performancae.
>
> Regards
> Amish
> *** Sent via Developersdex http://www.codecomments.com ***
|||It's just a matter of what you prefer - which can result in discussions of
religious fervour.
Just stick to count(*) being correct and count(1) being stupid.
I think of count(*) as counting the number of rows but I have heard people
liking count(1) as meaning count where the existence of the row is true (1
meaning true).
I believe there are (or were) lesser databases where count(1) gives better
performance.
In sql server it doesn't matter for performance - just avoid count(fld)
(unless that's what you want.
"SouRa" wrote:

> Hi,
> 1 - select count(*) from tablename
> 2 - select count(1) from tablename
> query 1 and 2 return the same value as output.
> what is the difference between count(*) and count(1) ?
> which is efficient?
> Please advise
> Thanks,
> Soura
>

difference between COUNT(*) & COUNT(1)

Hi,
1 - select count(*) from tablename
2 - select count(1) from tablename
query 1 and 2 return the same value as output.
what is the difference between count(*) and count(1) ?
which is efficient?
Please advise
Thanks,
Sourado
set statistics io on , client statistics ,server statistics and
execution plan in query analyzer.
you will no major different for your both queries, What you need to have
proper cluster index on the table to improve count performancae.
Regards
Amish
*** Sent via Developersdex http://www.codecomments.com ***|||SouRa wrote:
> Hi,
> 1 - select count(*) from tablename
> 2 - select count(1) from tablename
> query 1 and 2 return the same value as output.
> what is the difference between count(*) and count(1) ?
> which is efficient?
> Please advise
> Thanks,
> Soura
No difference. If you check the execution plans you should find that
they are identical.
David Portas
SQL Server MVP
--|||> you will no major different for your both queries, What you need to have
> proper cluster index on the table to improve count performancae.
Actually, a non-clustered index will be better. A non-clustered index on any
column will cover that
query, so SQL Server can scan the leaf-level in that index instead of scanni
ng the leaf-level in the
clustered index (which are the data pages). The more narrow the non-clustere
d index, the better.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Amish Shah" <shahamishm@.gmail.com> wrote in message news:%23ulIsi2EGHA.1508@.TK2MSFTNGP15.ph
x.gbl...
> do
> set statistics io on , client statistics ,server statistics and
> execution plan in query analyzer.
> you will no major different for your both queries, What you need to have
> proper cluster index on the table to improve count performancae.
>
> Regards
> Amish
> *** Sent via Developersdex http://www.codecomments.com ***|||It's just a matter of what you prefer - which can result in discussions of
religious fervour.
Just stick to count(*) being correct and count(1) being stupid.
I think of count(*) as counting the number of rows but I have heard people
liking count(1) as meaning count where the existence of the row is true (1
meaning true).
I believe there are (or were) lesser databases where count(1) gives better
performance.
In sql server it doesn't matter for performance - just avoid count(fld)
(unless that's what you want.
"SouRa" wrote:

> Hi,
> 1 - select count(*) from tablename
> 2 - select count(1) from tablename
> query 1 and 2 return the same value as output.
> what is the difference between count(*) and count(1) ?
> which is efficient?
> Please advise
> Thanks,
> Soura
>

difference between COUNT(*) & COUNT(1)

Hi,
1 - select count(*) from tablename
2 - select count(1) from tablename
query 1 and 2 return the same value as output.
what is the difference between count(*) and count(1) ?
which is efficient?
Please advise
Thanks,
Sourado
set statistics io on , client statistics ,server statistics and
execution plan in query analyzer.
you will no major different for your both queries, What you need to have
proper cluster index on the table to improve count performancae.
Regards
Amish
*** Sent via Developersdex http://www.developersdex.com ***|||SouRa wrote:
> Hi,
> 1 - select count(*) from tablename
> 2 - select count(1) from tablename
> query 1 and 2 return the same value as output.
> what is the difference between count(*) and count(1) ?
> which is efficient?
> Please advise
> Thanks,
> Soura
No difference. If you check the execution plans you should find that
they are identical.
--
David Portas
SQL Server MVP
--|||> you will no major different for your both queries, What you need to have
> proper cluster index on the table to improve count performancae.
Actually, a non-clustered index will be better. A non-clustered index on any column will cover that
query, so SQL Server can scan the leaf-level in that index instead of scanning the leaf-level in the
clustered index (which are the data pages). The more narrow the non-clustered index, the better.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Amish Shah" <shahamishm@.gmail.com> wrote in message news:%23ulIsi2EGHA.1508@.TK2MSFTNGP15.phx.gbl...
> do
> set statistics io on , client statistics ,server statistics and
> execution plan in query analyzer.
> you will no major different for your both queries, What you need to have
> proper cluster index on the table to improve count performancae.
>
> Regards
> Amish
> *** Sent via Developersdex http://www.developersdex.com ***|||It's just a matter of what you prefer - which can result in discussions of
religious fervour.
Just stick to count(*) being correct and count(1) being stupid.
I think of count(*) as counting the number of rows but I have heard people
liking count(1) as meaning count where the existence of the row is true (1
meaning true).
I believe there are (or were) lesser databases where count(1) gives better
performance.
In sql server it doesn't matter for performance - just avoid count(fld)
(unless that's what you want.
"SouRa" wrote:
> Hi,
> 1 - select count(*) from tablename
> 2 - select count(1) from tablename
> query 1 and 2 return the same value as output.
> what is the difference between count(*) and count(1) ?
> which is efficient?
> Please advise
> Thanks,
> Soura
>

Difference between #table and # # Table?

If i create a ##table using my stored procedure, I can do the select * from ##table

but when i created the #table using the stored procedure and tried to do the select * from #table

I am getting the error message invalid object #table

that means it is going out of scope.

But if i create the ##table, will that table going to remain in the database even until i drop the ##table or it gets dropped when i close the database connection.

Thank you very much.
-RajFrom SQL Server Books OnLine:

There are two types of temporary tables:

Local temporary tables
The names of these tables begin with one number sign (#). These tables are visible only to the connection that created them.

Global temporary tables
The names of these tables begin with two number signs (##). These tables are visible to all connections. If the tables are not dropped explicitly before the connection that created them disconnects, they are dropped as soon as all other tasks stop referencing them. No new tasks can reference a global temporary table after the connection that created it disconnects. The association between a task and a table is always dropped when the current statement completes executing; therefore, global temporary tables are usually dropped soon after the connection that created them disconnects.

Difference b/w SPs & Functions

1. cannot hv tran in functions
2. cannot include SPs in select queries or in from clause whereas functions
can be included. (exception using OPENQUERY for including SP in from clause)
What other differences?Hi Rakesh
Difference between Stored Procedure and UDF:
Returning Parameters:
UDF Should return a parameter
Stored Procedure need not return any value
UDF Returns only one parameter
Stored Procedures can return more than one parameters
UDF can return a table
Stored Procedure returns a resultset
Usage
UDFs can be used a inline functions and can also be executed explicitly
eg: inline function: SELECT getdate()
Stored Procedures cannot be used as inline functions
Hope this helps you.
Please let me know if u have any questions
best Regards,
Chandra
http://chanduas.blogspot.com/
http://www.SQLResource.com/
---
"Rakesh" wrote:

> 1. cannot hv tran in functions
> 2. cannot include SPs in select queries or in from clause whereas function
s
> can be included. (exception using OPENQUERY for including SP in from claus
e)
>
> What other differences?|||You cannot use INSERT/UPDATE/DELETE on base tables.
You cannot Use NonDeterministic functions inside UDF
A scalar valued user defined function can be used Anywhere a value is
expected, as in WHERE clause, check contraint etc.
Roji. P. Thomas
Net Asset Management
http://toponewithties.blogspot.com
"Rakesh" <Rakesh@.discussions.microsoft.com> wrote in message
news:8DDCFBA3-C85D-47A4-A2CE-CDC22EEA364F@.microsoft.com...
> 1. cannot hv tran in functions
> 2. cannot include SPs in select queries or in from clause whereas
> functions
> can be included. (exception using OPENQUERY for including SP in from
> clause)
>
> What other differences?|||Rakesh
Visit at http://www.sql-server-performance.com/
"Rakesh" <Rakesh@.discussions.microsoft.com> wrote in message
news:8DDCFBA3-C85D-47A4-A2CE-CDC22EEA364F@.microsoft.com...
> 1. cannot hv tran in functions
> 2. cannot include SPs in select queries or in from clause whereas
> functions
> can be included. (exception using OPENQUERY for including SP in from
> clause)
>
> What other differences?|||"Chandra" wrote in message

> Difference between Stored Procedure and UDF:
> Returning Parameters:
> UDF Should return a parameter
> Stored Procedure need not return any value
Stored Procedures RETURN a value, even if you dont explicitly specify (0 by
default)

> UDF Returns only one parameter
> Stored Procedures can return more than one parameters
Stored Procedure can RETURN one one value and that must be integer.
You can use Output Parameters to get other datatypes back from the SP

> UDF can return a table
> Stored Procedure returns a resultset
Stored Procs can return multiple recordsets, UDF can return only one (table
valued udfs)

> UDFs can be used a inline functions and can also be executed explicitly
> eg: inline function: SELECT getdate()
What you mean by inline?

> Stored Procedures cannot be used as inline functions
EXEC yourvar= ProcName <ParamList>
Just plain Nitpicking :)
Regards
Roji. P. Thomas
http://toponewithties.blogspot.com

difference among them

Hi everyone,
What is the real difference among them ?
CREATE PROCEDURE sp_devamsizlik
@.ADI CHAR(20)
AS
SELECT *
FROM OGRENCI
WHERE ADI=@.ADI

GO
--

CREATE FUNCTION sp_devamsizlik
(@.ADI

CHAR(20) )
RETURN TABLE
BEGIN
RETURN( SELECT * FROM OGRENCI WHERE ADI=@.ADI )
END

The short answer could be something like; they are both tools in your toolbox, one is a hammer and the other is a screwdriver. They are used to build with, but at different times for different purposes. Which is best depends on if you're looking at a nail or a screw.

The 'real' difference could fill many many pages, so I'd like to point you to BOL first. There's a lot in there that explains about procedures and functions. The one thing that may not be so obvious in BOL, is that functions aren't always the magic wand that one could be led to believe. UDF's should be used with caution, but used right, they can serve you very well.

/Kenneth

|||

Hello

The First one is Storde Procedure and the Second one is User Define function.

Search for Diffrence between SP and UF in detail

- You can'nt call a UF in SP while in SP u can
-In a Query u can call UF but u Can'nt Call SP
-

|||

Akbar Khan wrote:

- You can'nt call a UF in SP while in SP u can
-In a Query u can call UF but u Can'nt Call SP
-

Hi, would you mind being more clear ?|||

Hi

Sorry there is an error in the posted syntex: actually

In Stored Procedure you can call a User Define Function Like If write the follwing query in Stored Procedure then it will run with out any error.
Suppose we have a GetName User Define Function which returns Student Name and accept the Student Id as a aparameter.

Select dbo.GetName(std_id) FROM STUDENT

Here you can also see that a User Define function is called in SQL Select Query.

But you cann't call the follwing syentex in User Define function

Exec SP_DoSomething

SP_DoSomeThing is a stored procedure.

The Vest way to Understand Code it....

Differ bet WHERE clause & INNER JOIN?

In simple terms, if possible, what is the difference between using the WHERE clause in a SELECT statement vs an INNER JOIN? According to Rob Viera's book the WHERE is "inclusive" in nature, meaning that all records that meet the WHERE condition are included in the result set. The text further stated that an INNER JOIN is "exclusive" in nature meaning that data not meeting the JOIN condition is excluded from the result set.

In layman's terms, what is the difference? Any examples? Thanks in advance.

ddaveSo, I saw the question about Inner Join vs Where in Queries. I'm using all the "Joins" in clause where, is there problem to do it or I should be using Inner Join ?|||Linking tables should be done using a JOIN clause. Filtering on individual data elements should be done with WHERE clauses.

For simple queries, the SQL Server Optimizer will convert a statement like

select * from a, b where a.key = b.key

into

select * from a inner join b on a.key = b.key

...and will make optimium use of the indexes on the two tables to create an efficient execution plan. For more complicated queries the optimizer may not be able to make this conversion.

The JOIN syntax leads to better organized and more easily readable and debuggable code, because it clearly establishes the relationships between tables.|||Originally posted by blindman
...and will make optimium use of the indexes on the two tables to create an efficient execution plan. For more complicated queries the optimizer may not be able to make this conversion.


Ya think? Not so sure...but youcan test it and check out the plan the optimizer chooses by looking at the query plan

The JOIN syntax leads to better organized and more easily readable and debuggable code, because it clearly establishes the relationships between tables.

100%|||Not so sure it will reevalutate the simple query, or not so sure it won't be able to evaluate a complex query?|||I'm not sure that it will give you 2 different plans...have you seen this?|||It won't for simple queries. I've heard that it might not be able to for complex queries. At some point of complexity there must be a statement that it is beyond its capacity to reduce to simple join clauses. I mean, complexity is theoretically infinite, right? I suspect that it may have difficulty reevaluating WHERE clauses containing conditional syntax, but I haven't formally tested it.sql

Diff. performance in Query Analyzer than when using stored procedure

Hi group,

I have a select statement that if run against a 1 million record
database directly in query analyzer takes less than 1 second.
However, if I execute the select statement in a stored procedure
instead, calling the stored proc from query analyzer, then it takes
12-17 seconds.

Here is what I execute in Query Analyzer when bypassing the stored
procedure:

USE Verizon
GO
DECLARE @.phonenumber varchar(15)
SELECT @.phonenumber = '6317898493'
SELECT Source_Identifier,
BADD_Sequence_Number,
Record_Type,
BAID ,
Social_Security_Number ,
Billing_Name,
Billing_Address_1,
Billing_Address_2,
Billing_Address_3,
Billing_Address_4,
Service_Connection_Date,
Disconnect_Date,
Date_Final_Bill,
Behavior_Score,
Account_Group,
Diconnect_Reason,
Treatment_History,
Perm_Temp,
Balance_Due,
Regulated_Balance_Due,
Toll_Balance_Due,
Deregulated_Balance_Due,
Directory_Balance_Due,
Other_Category_Balance

FROM BadDebt
WHERE (Telephone_Number = @.phonenumber) OR (Telephone_Number_Redef =
@.phonenumber)
order by Service_Connection_Date desc

RETURN
GO

Here is what I execute in Query Analyzer when calling the stored
procedure:

DECLARE @.phonenumber varchar(15)
SELECT @.phonenumber = '6317898493'
EXEC Verizon.dbo.baddebt_phonelookup @.phonenumber

Here is the script that created the stored procedure itself:

CREATE PROCEDURE dbo.baddebt_phonelookup @.phonenumber varchar(15)
AS

SELECT Source_Identifier,
BADD_Sequence_Number,
Record_Type,
BAID ,
Social_Security_Number ,
Billing_Name,
Billing_Address_1,
Billing_Address_2,
Billing_Address_3,
Billing_Address_4,
Service_Connection_Date,
Disconnect_Date,
Date_Final_Bill,
Behavior_Score,
Account_Group,
Diconnect_Reason,
Treatment_History,
Perm_Temp,
Balance_Due,
Regulated_Balance_Due,
Toll_Balance_Due,
Deregulated_Balance_Due,
Directory_Balance_Due,
Other_Category_Balance

FROM BadDebt
WHERE (Telephone_Number = @.phonenumber) OR (Telephone_Number_Redef =
@.phonenumber)
order by Service_Connection_Date desc

RETURN
GO

Using SQL Profiler, I also have the execution trees for each of these
two different ways of running the same query.

Here is the Execution tree when running the whole query in the
analyzer, bypassing the stored procedure:

------------
Sort(ORDER BY:([BadDebt].[Service_Connection_Date] DESC))
|--Bookmark Lookup(BOOKMARK:([Bmk1000]),
OBJECT:([Verizon].[dbo].[BadDebt]))
|--Sort(DISTINCT ORDER BY:([Bmk1000] ASC))
|--Concatenation
|--Index
Seek(OBJECT:([Verizon].[dbo].[BadDebt].[Telephone_Index]),
SEEK:([BadDebt].[Telephone_Number]=[@.phonenumber]) ORDERED FORWARD)
|--Index
Seek(OBJECT:([Verizon].[dbo].[BadDebt].[Telephone_Redef_Index]),
SEEK:([BadDebt].[Telephone_Number_Redef]=[@.phonenumber]) ORDERED
FORWARD)
------------

Finally, here is the execution tree when calling the stored procedure:

------------
Sort(ORDER BY:([BadDebt].[Service_Connection_Date] DESC))
|--Filter(WHERE:([BadDebt].[Telephone_Number]=[@.phonenumber] OR
[BadDebt].[Telephone_Number_Redef]=[@.phonenumber]))
|--Compute Scalar(DEFINE:([BadDebt].[Telephone_Number_Redef]=substring(Convert([BadDebt].[Telephone_Number]),
1, 10)))
|--Table Scan(OBJECT:([Verizon].[dbo].[BadDebt]))
------------

Thanks for any help on my path to optimizing this query for our
production environment.

Regards,

Warren Wright
Scorex Development Teamwarren.wright@.us.scorex.com (Warren Wright) wrote in message news:<8497c269.0308051401.2e65bb80@.posting.google.com>...
> Hi group,
> I have a select statement that if run against a 1 million record
> database directly in query analyzer takes less than 1 second.
> However, if I execute the select statement in a stored procedure
> instead, calling the stored proc from query analyzer, then it takes
> 12-17 seconds.

<snip
One possible reason is parameter sniffing - see here:

http://groups.google.com/groups?sel...7&output=gplain

Simon|||sql@.hayes.ch (Simon Hayes) wrote in message news:<60cd0137.0308060118.46c12f2e@.posting.google.com>...
> One possible reason is parameter sniffing - see here:
> http://groups.google.com/groups?sel...7&output=gplain
> Simon

Wow. Thats a bit of an eye opener. It makes me wonder how best to
make sure a decent plan is chosen by SQL Server, and the answer seems
to be to make it recompile the stored procedure every time ?

or is there a way to make SQL simply use the index at all times? I'd
hate to spend a lot of time on my dev machine getting the stored
procedure to run correctly on a million record table, only to port it
to my production machine and have it take forever on the 33 million
record database because of some magically crafted execution plan :-)

Thanks,

Warren|||Here is something else that I don't understand. The stored procedure
I listed above compares a phone number that is passed in against a
Telephone_Number column that is 15 digits long, and against a computed
column (Telephone_Number_Redef), that is the left 10 digits of the
Telephone_Number column.

This is because sometimes our client passes in a 10 digit number, and
sometimes a 15 digit version that includes some check digits on the
end (Don't ask).

Anyway, in the execution plan for when the stored proc is executing, I
see the following:

----------
Sort(ORDER BY:([BadDebt].[Service_Connection_Date] DESC))
|--Filter(WHERE:([BadDebt].[Telephone_Number]=[@.phonenumber] OR
[BadDebt].[Telephone_Number_Redef]=[@.phonenumber]))
|--Compute Scalar(DEFINE:([BadDebt].[Telephone_Number_Redef]=substring(Convert([BadDebt].[Telephone_Number]),
1, 10)))
|--Table Scan(OBJECT:([Verizon].[dbo].[BadDebt]))
----------

It appears to be recomputing the Telephone_Number_Redef column values
on the fly, instead of using the values already present. The
Telephone_Number_Redef column is indexed specifically to allow that
second comparison in the WHERE statement to be a SARG, but it seems
this is being ignored.

Is it being ignored because SQL had already decided to do a table
scan, and so though it might as well speed things up by not scanning
both columns? or is SQL doing a table scan because it thinks it needs
to re-compute the values for Telephone_Number_Redef on the fly?

Argh.

Thanks,

Warren Wright
Scorex Development Team
Dallas|||More follow-up on this issue, to help you experts analyze what's going
on here.

I've spent the day trying various things, with no success. I tried
using hints to suggest that the index on Telephone_Number_Redef be
used, which results in an error stating the stored procedure couldn't
be executed due to an unworkable hint.

I've tried declaring a new variable in the stored proc with a value
set equal to the @.phonenumber input, so SQL couldn't optimize based on
the actual value being passed in.

I've tried changing the index for the computed column to be a
clustered index.

I only wish I could simply tell SQL to use the same execution plan it
uses when I run the query from the analyzer!! All problems would be
solved!

No matter what, if I run the query from query analyzer, the response
time is a few milliseconds. If I run the stored procedure, the
response time is at least 17 seconds due to a completely suboptimal
execution plan (where the Telephone_Number_Redef's index isn't used at
all).

Introducing the new version of the stored proc with the OR statement
that checks against the computed column as well bogs down the
production server, and results in timeouts and app errors for our
client.

Frustrated,

Warren|||[posted and mailed, please reply in news]

Warren Wright (warren.wright@.us.scorex.com) writes:
> Here is something else that I don't understand. The stored procedure
> I listed above compares a phone number that is passed in against a
> Telephone_Number column that is 15 digits long, and against a computed
> column (Telephone_Number_Redef), that is the left 10 digits of the
> Telephone_Number column.

Computed column? Which you have an index on? Aha!

While Bart's article on parameter sniffing is good reading it is not
the answer here. Index on computed columns (as well on views) can
only be used if these SET options are ON: ANSI_NULLS, QUOTED_IDENTIFIER,
ANSI_WARNINGS, ARITHABORT, ANSI_PADDING and CONCAT_NULLS_YIELDS_NULL.
And NUMERIC_ROUNDABORT be OFF.

The killer here is usually QUOTED_IDENTIFIER. That option, together
with ANSI_NULLS is saved with the procedure, so that the run-time
setting does not apply, but the setting saved with the procedure.
QUOTED_IDENTIFIER is ON by default with ODBC and OLE DB, as well
with Query Analyzer. But OSQL and Enterprise Manager turns it off.
So you need to make sure that the procedure is created with
QUOTED_IDENTIFIER on.

You can review the current setting with

select objectproperty(object_id('your_sp'), 'IsQuotedIdentOn')

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.aspsql

Diff Between Select & Set

Hi,
I am having a simple question.
Select @.test = 'test'
Set @.test = 'test'
Can anyone give me the difference between the above
two statements..
Selva
:)Effectively there is no difference. Either will assign the variable a value. Personally, I prefer to use SET whenever I'm just assigning one variable a value and use SELECT when I'm returning a resultset.|||not a lot.

you can only set one variable with set but can set multiple ones with select and have to use a subquery to get a value from a table with set but I think set has less overhead.

Diff between "LEFT OUTER JOIN" and " *= " when condition

Using SqlServer :

Query 1 :

SELECT def.lID as IdDefinition,
TDC_AUneValeur.VALEURDERETOUR as ValeurDeRetour
FROM serveur.Data_tblDEFINITIONTABLEDECODES def,
serveur.Data_tblTABLEDECODEAUNEVALEUR TDC_AUneValeur
where def.TYPEDETABLEDECODES = 4
and TDC_AUneValeur.PERIODE_ANNEEFISCALE_ID = 2
and def.lID *= TDC_AUneValeur.DEFINITIONTABLEDECODES_DEFINITION_I D

Query 2 :

SELECT def.lID as IdDefinition,
TDC_AUneValeur.VALEURDERETOUR as ValeurDeRetour
FROM serveur.Data_tblDEFINITIONTABLEDECODES def LEFT OUTER JOIN
serveur.Data_tblTABLEDECODEAUNEVALEUR TDC_AUneValeur
ON def.lID = TDC_AUneValeur.DEFINITIONTABLEDECODES_DEFINITION_I D
where def.TYPEDETABLEDECODES = 4
and TDC_AUneValeur.PERIODE_ANNEEFISCALE_ID = 2

The query 1 returns :
IdDefinition ValeurDeRetour
23 null
24 null
25 null
29 36

The query 2 returns :
IdDefinition ValeurDeRetour
29 36

The first result is the good one.
How is it that the second query doesn't return the same resultSet ?
I've been told about problems comparing NULL ?

What is the solution ?
Thanks a lot.

DamienThere's a big difference between referencing the unpreserved table
(TDC_AUneValeur) in the ON clause versus the WHERE clause. Try:

SELECT def.lID as IdDefinition,
TDC_AUneValeur.VALEURDERETOUR as ValeurDeRetour
FROM serveur.Data_tblDEFINITIONTABLEDECODES def LEFT OUTER JOIN
serveur.Data_tblTABLEDECODEAUNEVALEUR TDC_AUneValeur
ON def.lID = TDC_AUneValeur.DEFINITIONTABLEDECODES_DEFINITION_I D
and TDC_AUneValeur.PERIODE_ANNEEFI*SCALE_ID = 2
where def.TYPEDETABLEDECODES = 4 ;

--
David Portas
SQL Server MVP
--|||Thank you very much.
It's exactly the solution.

Bye|||Here is how OUTER JOINs work in SQL-92. Assume you are given:

Table1 Table2
a b a c
====== ======
1 w 1 r
2 x 2 s
3 y 3 t
4 z

and the outer join expression:

Table1
LEFT OUTER JOIN
Table2
ON Table1.a = Table2.a <== join condition
AND Table2.c = 't'; <== single table condition

We call Table1 the "preserved table" and Table2 the "unpreserved table"
in the query. What I am going to give you is a little different, but
equivalent to the ANSI/ISO standards.

1) We build the CROSS JOIN of the two tables. Scan each row in the
result set.

2) If the predicate tests TRUE for that row, then you keep it. You also
remove all rows derived from it from the CROSS JOIN

3) If the predicate tests FALSE or UNKNOWN for that row, then keep the
columns from the preserved table, convert all the columns from the
unpreserved table to NULLs and remove the duplicates.

So let us execute this by hand:

Let @. = passed the first predicate
Let * = passed the second predicate

Table1 CROSS JOIN Table2
a b a c
=========================
1 w 1 r @.
1 w 2 s
1 w 3 t *
2 x 1 r
2 x 2 s @.
2 x 3 t *
3 y 1 r
3 y 2 s
3 y 3 t @.* <== the TRUE set
4 z 1 r
4 z 2 s
4 z 3 t *

Table1 LEFT OUTER JOIN Table2
a b a c
=========================
3 y 3 t <= only TRUE row
--------
1 w NULL NULL Sets of duplicates
1 w NULL NULL
1 w NULL NULL
--------
2 x NULL NULL
2 x NULL NULL
2 x NULL NULL
3 y NULL NULL <== derived from the TRUE set - Remove
3 y NULL NULL
--------
4 z NULL NULL
4 z NULL NULL
4 z NULL NULL

the final results:

Table1 LEFT OUTER JOIN Table2
a b a c
=========================
1 w NULL NULL
2 x NULL NULL
3 y 3 t
4 z NULL NULL

The basic rule is that every row in the preserved table is represented
in the results in at least one result row.

There are limitations and very serious problems with the extended
equality version of an outer join used in some diseased mutant
products. Consider the two Chris Date tables

Suppliers SupParts
supno supno partno qty
========= ==============
S1 S1 P1 100
S2 S1 P2 250
S3 S2 P1 100
S2 P2 250

and let's do an extended equality outer join like this:

SELECT *
FROM Supplier, SupParts
WHERE Supplier.supno *= SupParts.supno
AND qty < 200;

If I do the outer first, I get:

Suppliers LOJ SupParts
supno supno partno qty
=======================
S1 S1 P1 100
S1 S1 P2 250
S2 S2 P1 100
S2 S2 P2 250
S3 NULL NULL NULL

Then I apply the (qty < 200) predicate and get

Suppliers LOJ SupParts
supno supno partno qty
===================
S1 S1 P1 100
S2 S2 P1 100

Doing it in the opposite order

Suppliers LOJ SupParts
supno supno partno qty
===================
S1 S1 P1 100
S2 S2 P1 100
S3 NULL NULL NULL

Sybase does it one way, Oracle does it the other and Centura (nee
Gupta) lets you pick which one -- the worst of both non-standard
worlds! In SQL-92, you have a choice and can force the order of
execution. Either do the predicates after the join ...

SELECT *
FROM Supplier
LEFT OUTER JOIN
SupParts
ON Supplier.supno = SupParts.supno
WHERE qty < 200;

... or do it in the joining:

SELECT *
FROM Supplier
LEFT OUTER JOIN
SupParts
ON Supplier.supno = SupParts.supno
AND qty < 200;

Another problem is that you cannot show the same table as preserved and
unpreserved in the extended equality version, but it is easy in SQL-92.
For example to find the students who have taken Math 101 and might
have taken Math 102:

SELECT C1.student, C1.math, C2.math
FROM (SELECT * FROM Courses WHERE math = 101) AS C1
LEFT OUTER JOIN
(SELECT * FROM Courses WHERE math = 102) AS C2
ON C1.student = C2.student;

diferent way to a query

is theres a way to return the same result of this query without making the inner joins?

Code Snippet

create view missEeCuts
as
select distinct e.*
from events e
inner join (
select eventid, sum(px) as sum_px
from isolatedLeptons
group by eventid
) l
on e.idevent=l.eventid
inner join (
select eventid, sum(py) as sum_py
from isolatedLeptons
group by eventid
) l2
on e.idevent=l2.eventid
where
dbo.module(e.PxMiss,e.PyMiss)>=40 AND
dbo.effectiveMass(e.PxMiss,e.PyMiss,l.sum_px,l2.sum_py)<= 150.0

GO

Maybe this, Luis:

Code Snippet

selectdistinct e.*

from events e

where

dbo.module(e.PxMiss,e.PyMiss)>=40 AND

dbo.effectiveMass(e.PxMiss,e.PyMiss,l.sum_px,l2.sum_py)<= 150.0

ANDEXISTS(

select eventid,sum(px)as sum_px

from isolatedLeptons l

where e.idevent=l.eventid

groupby eventid

)

ANDEXISTS(

select eventid,sum(py)as sum_py

from isolatedLeptons l2

where e.idevent=l2.eventid

groupby eventid

)

Why do you not want the joins?

|||

Luis:

Why do you take "sum_px" and "sum_py" from different joins in:

Code Snippet

inner join (
select eventid, sum(px) as sum_px
from isolatedLeptons
group by eventid
) l
on e.idevent=l.eventid
inner join (
select eventid, sum(py) as sum_py
from isolatedLeptons
group by eventid
) l2
on e.idevent=l2.eventid

Can these two sums potentially be computed in a single join such as:

Code Snippet

inner join (
select eventid, sum(px) as sum_px, sum(py) as sum_py
from isolatedLeptons
group by eventid
) l
on e.idevent=l.eventid

If so, you might be able to eliminate the last INNER JOIN

|||

Dale:

Are "l.sum_px" "l2.sum_py" in scope in this line?

dbo.effectiveMass(e.PxMiss,e.PyMiss,l.sum_px,l2.sum_py)<= 150.0

I thought these would be out of scope

|||

Ah man. I completely missed that.

Still early...where'd that coffee pot go?

Thanks Kent

Determining unique children

Hi,

I need to be able to identify if a parent has the same child names as another parent.

Have a look at my data (in pic)

Can you think of a SELECT that will count for each parent, the number of other parents including themselves with all the same children's names?

So you would get something like:

Bob | 2
Bill | 1
Glen | 2

thanks,
david.That's some interesting homework!

CREATE TABLE #patp (
parent VARCHAR(20)
, child VARCHAR(20)
)

INSERT INTO #patp (parent, child)
SELECT 'Bob', 'Jen' UNION
SELECT 'Bob', 'Jill' UNION
SELECT 'Bob', 'Mike' UNION
SELECT 'Bill', 'David' UNION
SELECT 'Bill', 'Steve' UNION
SELECT 'Glen', 'Jen' UNION
SELECT 'Glen', 'Jill' UNION
SELECT 'Glen', 'Mike'

SELECT DISTINCT a.parent
, (SELECT Count(*)
FROM (SELECT b.parent AS p1, c.parent AS p2
FROM #patp AS b
FULL JOIN #patp AS c
ON (c.child = b.child)
WHERE c.parent = a.parent
GROUP BY b.parent, c.parent
HAVING Count(*) = Count(b.parent)
AND Count(*) = Count(c.parent)) AS z)
FROM #patp AS a

DROP TABLE #patp
-PatP|||OK now, David...tell us why that works,now.

and show your work!!! ;)|||C'mon Pat. This is not the first time this guy has posted his homework for someone else to do. Reviewing his code for errors is one thing, but writing code for him is helping him cheat.|||let's hope he turns it in using the #patp table name, at least ;)

...and not even a "thank you, you can have half of my class credits"|||but writing code for him is helping him cheat.Only if he's incredibly gutsy and not very bright. I think he's smarter than that.

...and not even a "thank you, you can have half of my class credits"Why only half? If he retypes it for presentation, I'd give him 20%, but that's about all.

As TallCowboy0614 pointed out earlier, the real fun will come when David gets to explain that code for the class!

-PatP|||As TallCowboy0614 pointed out earlier, the real fun will come when David gets to explain that code for the class!

I'd be hard pressed....

working on it|||thanks for the code Pat!

I'm not a student, and that's not a homework assignment. (Been a while since then). I'm actually going to be using it (obviously re-worked to my own data) as an intergrity check for data Im pulling from a .xls file manually entered by a business user.

I was going to end up creating a function, but I knew someone here would have the guru-ishness to do it with a straight SELECT...obviously advantageous.

For simplicity I provided an example that I suppose looked like a homework assignment...LOL...the real scenario just would have been really annoying and difficult to explain. (and would have been one of those posts that people skip over)

as for explaining the code...cripes...i thought my SQL was pretty good. Lots more to learn. Although, I will be able to re-use what you provided Pat.

cheers.