Field Selection

I'm pretty new to Crystal and I'm stumped at the following issue:

I need to select a sub-set of a dataset. In this case I need to select fields with a status of "Requested", from a list of fields that include "Accepted", "Requested", "Declined". This part is easy, but it gets complicated (for me):

Then I have to use those fields filtered by "Requested" to find, within the last 180 days, if any clients have "Accepted" and/or "Requested" and/or "Declined" (i.e. status = any).

This is a two staged selection process, and I'm fine with the code for the 180 days selection. It almost seems to be a contradiction in that I need to filter by "Requested" in the first intance, and in the second I need to then show returns (for all Status types), but the initial filter means I can not do this.

Another way of looking at it is:

1. I want to select X1 from X(1,2,3,4,5), then
2. I want to use X1 to determine if there have been cases of X(1,2,3,4,5) in the last 6 months.

Any help is muchly appreciated.

Cheers.

Mat.any ideas? I'm desperate.

Field name as a parameter to a stored procedure?

Hi,
Is it possible to pass a field name as a parameter to a stored procedure?
The user wants to select either Dollar or Euro in the UI. The field I want
to be variable is a float & is used in a simple calculation...
(a.A_Tot_Rev_For / e.USDollar)
So I want e.USDollar to be able to change to e.Euro for example when my
report needs euro values.
thanks in advance,
jpi think a UDF is a better option

Field by Position

Hi, Nobody have idea to get a field in "select" statement by its position (without name then)?

My scope was to duplicate a row into a table with identity column getting all row except the identity one, that i want to provided by constant expression.

Bye and thanks...

:D :DYou will need to use dynamic SQL (constructing your SQL Statements at runtime) by referencing the database Schema or the sysobjects and syscolumns tables.|||Uhmmm...why do you need to do this...?|||Often I have duplicate row in table with a lot of columns ... and i wanted a statement to avoid to call each time columns by name ...

The real scope was to find a statement to say : "Get all the columns except primary key ... that I provide by constant expression ...."

Maybe the unique solution is to prepare a program to write statement how i need

Thanks|||why not just write the query with all the column names except the primary key?

that would've taken no more than a minute or two, a lot faster than writing a query to go against the system tables, and a lot faster than writing a program to do that

field <long text>

I have a table with the column type [ntext].
I store in this column the contains of an xml file.
When I run the select for this table on "sqlserver enterprise manager", the
value of this column is <long text>.
how can I see the exact contains of this column ?
I have to use another tools ?
thanks
ft> I have a table with the column type [ntext].
> I store in this column the contains of an xml file.
> When I run the select for this table on "sqlserver enterprise manager",
the
> value of this column is <long text>.
> how can I see the exact contains of this column ?
Use Query Analyzer. Enterprise Manager is primarily for system management,
not data viewing/manipulation.
http://www.aspfaq.com/2455

field <long text>

I have a table with the column type [ntext].
I store in this column the contains of an xml file.
When I run the select for this table on "sqlserver enterprise manager", the
value of this column is <long text>.
how can I see the exact contains of this column ?
I have to use another tools ?
thanks
ft
> I have a table with the column type [ntext].
> I store in this column the contains of an xml file.
> When I run the select for this table on "sqlserver enterprise manager",
the
> value of this column is <long text>.
> how can I see the exact contains of this column ?
Use Query Analyzer. Enterprise Manager is primarily for system management,
not data viewing/manipulation.
http://www.aspfaq.com/2455

field <long text>

I have a table with the column type [ntext].
I store in this column the contains of an xml file.
When I run the select for this table on "sqlserver enterprise manager", the
value of this column is <long text>.
how can I see the exact contains of this column ?
I have to use another tools ?
thanks
ft> I have a table with the column type [ntext].
> I store in this column the contains of an xml file.
> When I run the select for this table on "sqlserver enterprise manager",
the
> value of this column is <long text>.
> how can I see the exact contains of this column ?
Use Query Analyzer. Enterprise Manager is primarily for system management,
not data viewing/manipulation.
http://www.aspfaq.com/2455

Feeding an "IN" Clause

I know I can supply an explicit set to an IN clause, or I can feed it with a select statement. What other ways in TSQL 2005 can I feed it?

One thing I'd like to do is feed an IN clause by calling a stored procedure. I'm new to TSQL, so I don't know if I can do that. I'd also like to know if there is a way to pass a variadic list of values as a parameter to a stored procedure, so that they can be used in an "IN" clause found in that stored procedure. I think the way to do that last item is by having a recordset parameter. Is that correct? Any other way to do it?

You can use a static list of values for that:

IN (3,4,5)

HTh, Jens K. Suessmeyer.

http://www.sqlserver2005.de|||Yes, that syntax I already know, and is what I was calling an "explicit set." Its principally in the area of forming that set parametrically that I was after...such as through the "how to's" of stored procedures.|||

Based on another question you asked, you could try passing a comma delimited list of values to your stored procedure, and then use the LIKE functionality within a Case/When statement to ultimately achieve your goals.

Something like this...

Create Procedure IsValueIn(@.Value int, @.List VarChar(8000))
As
SET NOCOUNT ON

Select Case When ',' + @.List + ',' LIKE '%' + Convert(VarChar(20), @.Value) + '%'
Then 1
Else 0
End As ValueIn

Test it like this...

IsValueIn 10, '10,20,30'

IsValueIn 20, '10,20,30'

IsValueIn 30, '10,20,30'

IsValueIn 11, '10,20,30'

|||

My apologies for making a mistake. The method I presented in my previous post should work, but the implementation is a little off. The corrected code should be...

Alter Procedure IsValueIn(@.Value int, @.List VarChar(8000))
As
SET NOCOUNT ON

Select Case When ',' + @.List + ',' LIKE '%,' + Convert(VarChar(20), @.Value) + ',%'
Then 1
Else 0
End As ValueIn

Notice the extra commas added to the like compare. These commas are important because the code I posted earlier would return true for:

IsValueIn 1, '10,20,30'

With the code in this reply, the stored procedure correctly returns false. Again, my apologies for misleading you with my previous response.

|||

I would suggest using a user defined function. You can pass whatever you want, and break it up into a set of data that can be used like:

select *
from table
where tableId in (select value from dbo.myFunction(@.parameter))

A good resource for this sort of thing is Erland's article: http://www.sommarskog.se/arrays-in-sql.html

sql

Feed stored procedure with SELECT resultset

I have two SQL Server stored procedures, PROC1 and PROC2. PROC1 has
about 50 input parameters. PROC2 is the main procedure that does some
data modifications and afterwards calls PROC1 using an EXECUTE
statement.

The input parameter values for PROC1 are stored in a table in my
database. What I like to do is passing those values to PROC1 using a
SELECT statement. Currently, all 50 parameters are read and stored in
a variable, and afterwards they are passed to PROC1 using:

EXEC spPROC1 @.var1, @.var2, @.var3, ... , @.var50

Since it is a lot of code declaring and assigning 50 variables, I was
wondering if there is a possibility to run a statement like:

EXEC spPROC1 (SELECT * FROM myTable WHERE id = 2)

Any help on this is greatly appreciated!On 21 Oct 2004 07:19:02 -0700, Dieter Gasser wrote:

> I have two SQL Server stored procedures, PROC1 and PROC2. PROC1 has
> about 50 input parameters. PROC2 is the main procedure that does some
> data modifications and afterwards calls PROC1 using an EXECUTE
> statement.
> The input parameter values for PROC1 are stored in a table in my
> database. What I like to do is passing those values to PROC1 using a
> SELECT statement. Currently, all 50 parameters are read and stored in
> a variable, and afterwards they are passed to PROC1 using:
> EXEC spPROC1 @.var1, @.var2, @.var3, ... , @.var50
> Since it is a lot of code declaring and assigning 50 variables, I was
> wondering if there is a possibility to run a statement like:
> EXEC spPROC1 (SELECT * FROM myTable WHERE id = 2)
> Any help on this is greatly appreciated!

You could build up a dynamic SQL statement and execute it that way, but I
think it would be vastly better if your spPROC1 read its inputs to be in a
table, rather than as parameters.

Features in SQLCE 3.5 BETA 2: TOP, DISTINCT, SUB QUERIES?

Are these implemented in Beta 2:

TOP

DISTINCT

sub queries in SELECT clause?

sub queries in FROM clause? (as in ... FROM (SELECT ...)

From the beta 2 doc:

SQL Server Compact 3.5 support for transact-SQL statements has been extended as follows:

Nested query in SELECT FROM clause

TOP

fault with configure the select stament

Goodday all

I,m having a problem with something that worked well just recently. maybe I changed a setting by mistake.

I have a Database grid view on my page. When I configure my data sourse and get to "configure the select statment " on the wizard and press advanced to open the wizard with two check boxes " generate INSERT, UPDATE etc and the other check box "Use optimistic concurrency " this input box of the wizard is greyed out (non responsive)

Does anyone know how to rectify this?

Regards

Rob

Insufficient information to be sure. Did you indicate to generate insert, update, or delete statements?

The optimistic concurrency does not mean anything unless you created at least one of those statements so it is likely disabled.

fatch the data with two different sqlserver.

Can i fatch the data with to different sqlserver.
i want a query i.e select query fatch the data this different database and diff.table.
i.e. sqlserver to sqlserver and
oracle to sqlserverDTS Should be able to help you send data from one server to another server, as well as oracle to sql. If you can't use DTS you could always go the custom app route but as far as i know DTS would be the easiest route to take.
Hth,
|||Use linked Servers.
Mathias

Fatal error 625 - Connection broken problem (Help needed)

Hello everyone,
When I execute the following statement in the query analyzer on a msSQL2000
database...
delete from DVD_I_Have where id in(select top 1 id from DVD_I_Have where
Userid=78 and DVDID=8 and onhold=0);
I get the following error...
Server: Msg 21, Level 20, State 1, Line 1
Warning: Fatal error 625 occurred at Jan 30 2007 2:15PM
Connection Broken
If I execute a simple SELECT statement, or any other statement, it runs ok
and doesn't cause an error.
Is there anything wrong with the above statement? and how can it be causing
that error message?
Thanks in advance for all help received!
Regards, RobertI'd run DBCC CHECKDB immediately.
Linchi
"ROBinBrampton" wrote:
> Hello everyone,
> When I execute the following statement in the query analyzer on a msSQL2000
> database...
> delete from DVD_I_Have where id in(select top 1 id from DVD_I_Have where
> Userid=78 and DVDID=8 and onhold=0);
> I get the following error...
> Server: Msg 21, Level 20, State 1, Line 1
> Warning: Fatal error 625 occurred at Jan 30 2007 2:15PM
> Connection Broken
> If I execute a simple SELECT statement, or any other statement, it runs ok
> and doesn't cause an error.
> Is there anything wrong with the above statement? and how can it be causing
> that error message?
> Thanks in advance for all help received!
> Regards, Robert
>

Fatal error 625 - Connection broken problem (Help needed)

Hello everyone,
When I execute the following statement in the query analyzer on a msSQL2000
database...
delete from DVD_I_Have where id in(select top 1 id from DVD_I_Have where
Userid=78 and DVDID=8 and onhold=0);
I get the following error...
Server: Msg 21, Level 20, State 1, Line 1
Warning: Fatal error 625 occurred at Jan 30 2007 2:15PM
Connection Broken
If I execute a simple SELECT statement, or any other statement, it runs ok
and doesn't cause an error.
Is there anything wrong with the above statement? and how can it be causing
that error message?
Thanks in advance for all help received!
Regards, Robert
I'd run DBCC CHECKDB immediately.
Linchi
"ROBinBrampton" wrote:

> Hello everyone,
> When I execute the following statement in the query analyzer on a msSQL2000
> database...
> delete from DVD_I_Have where id in(select top 1 id from DVD_I_Have where
> Userid=78 and DVDID=8 and onhold=0);
> I get the following error...
> Server: Msg 21, Level 20, State 1, Line 1
> Warning: Fatal error 625 occurred at Jan 30 2007 2:15PM
> Connection Broken
> If I execute a simple SELECT statement, or any other statement, it runs ok
> and doesn't cause an error.
> Is there anything wrong with the above statement? and how can it be causing
> that error message?
> Thanks in advance for all help received!
> Regards, Robert
>

Fatal error 625 - Connection broken problem (Help needed)

Hello everyone,
When I execute the following statement in the query analyzer on a msSQL2000
database...
delete from DVD_I_Have where id in(select top 1 id from DVD_I_Have where
Userid=78 and DVDID=8 and onhold=0);
I get the following error...
Server: Msg 21, Level 20, State 1, Line 1
Warning: Fatal error 625 occurred at Jan 30 2007 2:15PM
Connection Broken
If I execute a simple SELECT statement, or any other statement, it runs ok
and doesn't cause an error.
Is there anything wrong with the above statement? and how can it be causing
that error message?
Thanks in advance for all help received!
Regards, RobertI'd run DBCC CHECKDB immediately.
Linchi
"ROBinBrampton" wrote:

> Hello everyone,
> When I execute the following statement in the query analyzer on a msSQL200
0
> database...
> delete from DVD_I_Have where id in(select top 1 id from DVD_I_Have where
> Userid=78 and DVDID=8 and onhold=0);
> I get the following error...
> Server: Msg 21, Level 20, State 1, Line 1
> Warning: Fatal error 625 occurred at Jan 30 2007 2:15PM
> Connection Broken
> If I execute a simple SELECT statement, or any other statement, it runs ok
> and doesn't cause an error.
> Is there anything wrong with the above statement? and how can it be causin
g
> that error message?
> Thanks in advance for all help received!
> Regards, Robert
>

Fastest way to insert?

Hi, I'm trying to figure out if doing an INSERT INTO.. a table with
indexes is faster (or slower) than doing a SELECT INTO and then creating
the indexes on the table created.
The table in question contains around a million records.
Thanks
*** Sent via Developersdex http://www.codecomments.com ***The only way to know is to test it both ways in the exact conditions and
hardware etc. that you will be using.
Andrew J. Kelly SQL MVP
"Joe Grizzly" <grizzlyjoe@.campcool.com> wrote in message
news:%23OYYT%23zWHHA.4252@.TK2MSFTNGP06.phx.gbl...
> Hi, I'm trying to figure out if doing an INSERT INTO.. a table with
> indexes is faster (or slower) than doing a SELECT INTO and then creating
> the indexes on the table created.
> The table in question contains around a million records.
> Thanks
>
> *** Sent via Developersdex http://www.codecomments.com ***|||Joe
Most likely that SELECT * INTO will win. BTW ,1 million rows is not so big
nowadays
"Joe Grizzly" <grizzlyjoe@.campcool.com> wrote in message
news:%23OYYT%23zWHHA.4252@.TK2MSFTNGP06.phx.gbl...
> Hi, I'm trying to figure out if doing an INSERT INTO.. a table with
> indexes is faster (or slower) than doing a SELECT INTO and then creating
> the indexes on the table created.
> The table in question contains around a million records.
> Thanks
>
> *** Sent via Developersdex http://www.codecomments.com ***|||And how many are you inserting? How many indexes and how large. How much
time does it need to create indexes? hardware specs? Columns specs?
It would probably best to drop indexes and recreate them if you can, but I
dont see the problem even if you dont drop them. Only, try to see if you
need to defragment them after that...
MC
"Joe Grizzly" <grizzlyjoe@.campcool.com> wrote in message
news:%23OYYT%23zWHHA.4252@.TK2MSFTNGP06.phx.gbl...
> Hi, I'm trying to figure out if doing an INSERT INTO.. a table with
> indexes is faster (or slower) than doing a SELECT INTO and then creating
> the indexes on the table created.
> The table in question contains around a million records.
> Thanks
>
> *** Sent via Developersdex http://www.codecomments.com ***|||Hello,
If it is a production server I will suggest you to create the table and
indexes and then insert the data into new table using BCP IN or BULK INSERT
in batch commit mode with BULK_INSERT recovery model. If you are creating a
table with SELECT * INTO and this will create locks in sysobjects table
which
is not a good idea.
Thanks
Hari
"Joe Grizzly" <grizzlyjoe@.campcool.com> wrote in message
news:%23OYYT%23zWHHA.4252@.TK2MSFTNGP06.phx.gbl...
> Hi, I'm trying to figure out if doing an INSERT INTO.. a table with
> indexes is faster (or slower) than doing a SELECT INTO and then creating
> the indexes on the table created.
> The table in question contains around a million records.
> Thanks
>
> *** Sent via Developersdex http://www.codecomments.com ***|||Thanks for the opinions. I'll definitely investigate.
*** Sent via Developersdex http://www.codecomments.com ***|||Can anybody explain to me exactly what BULK_LOGGED recovery does?
I gather it has less overhead and less recovery options than FULL, but
the BOL description is pretty vague.
Thanks.
Josh
On Wed, 28 Feb 2007 08:05:51 -0600, "Hari Prasad"
<hari_prasad_k@.hotmail.com> wrote:

>Hello,
>If it is a production server I will suggest you to create the table and
>indexes and then insert the data into new table using BCP IN or BULK INSERT
>in batch commit mode with BULK_INSERT recovery model. If you are creating a
>table with SELECT * INTO and this will create locks in sysobjects table
>which
> is not a good idea.
>Thanks
>Hari
>"Joe Grizzly" <grizzlyjoe@.campcool.com> wrote in message
>news:%23OYYT%23zWHHA.4252@.TK2MSFTNGP06.phx.gbl...
>|||BOL has some good info but here are some basics. First off it is no
different than FULL recovery until you do an operation that can take
advantage of a minimally logged operation. These would be things like:
CREATE INDEX
SELECT INTO
BULK INSERT
etc. (See BOL for more details)
When you do execute one of these in Bulk_Logged or Simple recovery mode only
the ID of the extent that was modified during that operation is logged in
the tran log. So if you did a bulk insert of 1 million rows and it filled up
1000 extents you would only log the ID's for those 1000 extents not the
actual data that would normally be logged in FULL recovey mode.There are
several implications of this. One is that you can no longer do point in time
recovery until you issue another full backup to start the proper logging
again. You can restore the entire log file though. The second is that when
you backup the tran log it will go and get all the data for those 1000
extents and place them in the log backup file. So the backup will take the
hit that normally would have occured if you did not do a minimally logged
load.
Andrew J. Kelly SQL MVP
"JXStern" <JXSternChangeX2R@.gte.net> wrote in message
news:7skgu25thlm2vjher8dfrl29scbpj894bv@.
4ax.com...
> Can anybody explain to me exactly what BULK_LOGGED recovery does?
> I gather it has less overhead and less recovery options than FULL, but
> the BOL description is pretty vague.
> Thanks.
> Josh
>
> On Wed, 28 Feb 2007 08:05:51 -0600, "Hari Prasad"
> <hari_prasad_k@.hotmail.com> wrote:
>
>|||Andrew,
Thanks!
So, bulk_logged is not faster or cheaper than simple, even for those
operations.
We have some large tables that are recreated daily, and it has
occurred to us to move them out to a separate database we can run on
whatever lightweight logging we can find. I guess simple is the
simple answer! Also make sure it's on RAID10 space rather than RAID5.
Also, the point about backup taking a hit, is good to know. I guess
another option might be to use bulk_logged for a little extra safety
and then if nothing goes wrong, just truncate the log instead of
backing it up to tape.
Josh
On Fri, 2 Mar 2007 13:40:41 -0500, "Andrew J. Kelly"
<sqlmvpnooospam@.shadhawk.com> wrote:

>BOL has some good info but here are some basics. First off it is no
>different than FULL recovery until you do an operation that can take
>advantage of a minimally logged operation. These would be things like:
>CREATE INDEX
>SELECT INTO
>BULK INSERT
>etc. (See BOL for more details)
>When you do execute one of these in Bulk_Logged or Simple recovery mode onl
y
>the ID of the extent that was modified during that operation is logged in
>the tran log. So if you did a bulk insert of 1 million rows and it filled u
p
>1000 extents you would only log the ID's for those 1000 extents not the
>actual data that would normally be logged in FULL recovey mode.There are
>several implications of this. One is that you can no longer do point in tim
e
>recovery until you issue another full backup to start the proper logging
>again. You can restore the entire log file though. The second is that when
>you backup the tran log it will go and get all the data for those 1000
>extents and place them in the log backup file. So the backup will take the
>hit that normally would have occured if you did not do a minimally logged
>load.|||If it is a minimally logged operation (must meet all conditions listed
below) then you ge the same logging and performance from Bulk-Logged and
Simple cerocery models. The key difference with Simple is that you can't do
a log restore at all. Yes Raid 5 is bad for heavy writes. One more note. If
you do use a seperate db you still need valid FULL backups before you start
the operation.
a.. The recovery model is simple or bulk-logged.
a.. The target table is not being replicated.
a.. The target table does not have any triggers.
a.. The target table has either 0 rows or no indexes.
a.. The TABLOCK hint is specified. For more information,
Andrew J. Kelly SQL MVP
"JXStern" <JXSternChangeX2R@.gte.net> wrote in message
news:623ju250u00obhg2c3v4v3ell4guds7sm6@.
4ax.com...
> Andrew,
> Thanks!
> So, bulk_logged is not faster or cheaper than simple, even for those
> operations.
> We have some large tables that are recreated daily, and it has
> occurred to us to move them out to a separate database we can run on
> whatever lightweight logging we can find. I guess simple is the
> simple answer! Also make sure it's on RAID10 space rather than RAID5.
> Also, the point about backup taking a hit, is good to know. I guess
> another option might be to use bulk_logged for a little extra safety
> and then if nothing goes wrong, just truncate the log instead of
> backing it up to tape.
> Josh
>
> On Fri, 2 Mar 2007 13:40:41 -0500, "Andrew J. Kelly"
> <sqlmvpnooospam@.shadhawk.com> wrote:
>
>

Fastest way to insert?

Hi, I'm trying to figure out if doing an INSERT INTO.. a table with
indexes is faster (or slower) than doing a SELECT INTO and then creating
the indexes on the table created.
The table in question contains around a million records.
Thanks
*** Sent via Developersdex http://www.codecomments.com ***
The only way to know is to test it both ways in the exact conditions and
hardware etc. that you will be using.
Andrew J. Kelly SQL MVP
"Joe Grizzly" <grizzlyjoe@.campcool.com> wrote in message
news:%23OYYT%23zWHHA.4252@.TK2MSFTNGP06.phx.gbl...
> Hi, I'm trying to figure out if doing an INSERT INTO.. a table with
> indexes is faster (or slower) than doing a SELECT INTO and then creating
> the indexes on the table created.
> The table in question contains around a million records.
> Thanks
>
> *** Sent via Developersdex http://www.codecomments.com ***
|||Joe
Most likely that SELECT * INTO will win. BTW ,1 million rows is not so big
nowadays
"Joe Grizzly" <grizzlyjoe@.campcool.com> wrote in message
news:%23OYYT%23zWHHA.4252@.TK2MSFTNGP06.phx.gbl...
> Hi, I'm trying to figure out if doing an INSERT INTO.. a table with
> indexes is faster (or slower) than doing a SELECT INTO and then creating
> the indexes on the table created.
> The table in question contains around a million records.
> Thanks
>
> *** Sent via Developersdex http://www.codecomments.com ***
|||Hello,
If it is a production server I will suggest you to create the table and
indexes and then insert the data into new table using BCP IN or BULK INSERT
in batch commit mode with BULK_INSERT recovery model. If you are creating a
table with SELECT * INTO and this will create locks in sysobjects table
which
is not a good idea.
Thanks
Hari
"Joe Grizzly" <grizzlyjoe@.campcool.com> wrote in message
news:%23OYYT%23zWHHA.4252@.TK2MSFTNGP06.phx.gbl...
> Hi, I'm trying to figure out if doing an INSERT INTO.. a table with
> indexes is faster (or slower) than doing a SELECT INTO and then creating
> the indexes on the table created.
> The table in question contains around a million records.
> Thanks
>
> *** Sent via Developersdex http://www.codecomments.com ***
|||Thanks for the opinions. I'll definitely investigate.
*** Sent via Developersdex http://www.codecomments.com ***
|||Can anybody explain to me exactly what BULK_LOGGED recovery does?
I gather it has less overhead and less recovery options than FULL, but
the BOL description is pretty vague.
Thanks.
Josh
On Wed, 28 Feb 2007 08:05:51 -0600, "Hari Prasad"
<hari_prasad_k@.hotmail.com> wrote:

>Hello,
>If it is a production server I will suggest you to create the table and
>indexes and then insert the data into new table using BCP IN or BULK INSERT
>in batch commit mode with BULK_INSERT recovery model. If you are creating a
>table with SELECT * INTO and this will create locks in sysobjects table
>which
> is not a good idea.
>Thanks
>Hari
>"Joe Grizzly" <grizzlyjoe@.campcool.com> wrote in message
>news:%23OYYT%23zWHHA.4252@.TK2MSFTNGP06.phx.gbl...
>
|||BOL has some good info but here are some basics. First off it is no
different than FULL recovery until you do an operation that can take
advantage of a minimally logged operation. These would be things like:
CREATE INDEX
SELECT INTO
BULK INSERT
etc. (See BOL for more details)
When you do execute one of these in Bulk_Logged or Simple recovery mode only
the ID of the extent that was modified during that operation is logged in
the tran log. So if you did a bulk insert of 1 million rows and it filled up
1000 extents you would only log the ID's for those 1000 extents not the
actual data that would normally be logged in FULL recovey mode.There are
several implications of this. One is that you can no longer do point in time
recovery until you issue another full backup to start the proper logging
again. You can restore the entire log file though. The second is that when
you backup the tran log it will go and get all the data for those 1000
extents and place them in the log backup file. So the backup will take the
hit that normally would have occured if you did not do a minimally logged
load.
Andrew J. Kelly SQL MVP
"JXStern" <JXSternChangeX2R@.gte.net> wrote in message
news:7skgu25thlm2vjher8dfrl29scbpj894bv@.4ax.com...
> Can anybody explain to me exactly what BULK_LOGGED recovery does?
> I gather it has less overhead and less recovery options than FULL, but
> the BOL description is pretty vague.
> Thanks.
> Josh
>
> On Wed, 28 Feb 2007 08:05:51 -0600, "Hari Prasad"
> <hari_prasad_k@.hotmail.com> wrote:
>
|||Andrew,
Thanks!
So, bulk_logged is not faster or cheaper than simple, even for those
operations.
We have some large tables that are recreated daily, and it has
occurred to us to move them out to a separate database we can run on
whatever lightweight logging we can find. I guess simple is the
simple answer! Also make sure it's on RAID10 space rather than RAID5.
Also, the point about backup taking a hit, is good to know. I guess
another option might be to use bulk_logged for a little extra safety
and then if nothing goes wrong, just truncate the log instead of
backing it up to tape.
Josh
On Fri, 2 Mar 2007 13:40:41 -0500, "Andrew J. Kelly"
<sqlmvpnooospam@.shadhawk.com> wrote:

>BOL has some good info but here are some basics. First off it is no
>different than FULL recovery until you do an operation that can take
>advantage of a minimally logged operation. These would be things like:
>CREATE INDEX
>SELECT INTO
>BULK INSERT
>etc. (See BOL for more details)
>When you do execute one of these in Bulk_Logged or Simple recovery mode only
>the ID of the extent that was modified during that operation is logged in
>the tran log. So if you did a bulk insert of 1 million rows and it filled up
>1000 extents you would only log the ID's for those 1000 extents not the
>actual data that would normally be logged in FULL recovey mode.There are
>several implications of this. One is that you can no longer do point in time
>recovery until you issue another full backup to start the proper logging
>again. You can restore the entire log file though. The second is that when
>you backup the tran log it will go and get all the data for those 1000
>extents and place them in the log backup file. So the backup will take the
>hit that normally would have occured if you did not do a minimally logged
>load.
|||If it is a minimally logged operation (must meet all conditions listed
below) then you ge the same logging and performance from Bulk-Logged and
Simple cerocery models. The key difference with Simple is that you can't do
a log restore at all. Yes Raid 5 is bad for heavy writes. One more note. If
you do use a seperate db you still need valid FULL backups before you start
the operation.
a.. The recovery model is simple or bulk-logged.
a.. The target table is not being replicated.
a.. The target table does not have any triggers.
a.. The target table has either 0 rows or no indexes.
a.. The TABLOCK hint is specified. For more information,
Andrew J. Kelly SQL MVP
"JXStern" <JXSternChangeX2R@.gte.net> wrote in message
news:623ju250u00obhg2c3v4v3ell4guds7sm6@.4ax.com...
> Andrew,
> Thanks!
> So, bulk_logged is not faster or cheaper than simple, even for those
> operations.
> We have some large tables that are recreated daily, and it has
> occurred to us to move them out to a separate database we can run on
> whatever lightweight logging we can find. I guess simple is the
> simple answer! Also make sure it's on RAID10 space rather than RAID5.
> Also, the point about backup taking a hit, is good to know. I guess
> another option might be to use bulk_logged for a little extra safety
> and then if nothing goes wrong, just truncate the log instead of
> backing it up to tape.
> Josh
>
> On Fri, 2 Mar 2007 13:40:41 -0500, "Andrew J. Kelly"
> <sqlmvpnooospam@.shadhawk.com> wrote:
>

Fastest way to insert?

Hi, I'm trying to figure out if doing an INSERT INTO.. a table with
indexes is faster (or slower) than doing a SELECT INTO and then creating
the indexes on the table created.
The table in question contains around a million records.
Thanks
*** Sent via Developersdex http://www.developersdex.com ***The only way to know is to test it both ways in the exact conditions and
hardware etc. that you will be using.
--
Andrew J. Kelly SQL MVP
"Joe Grizzly" <grizzlyjoe@.campcool.com> wrote in message
news:%23OYYT%23zWHHA.4252@.TK2MSFTNGP06.phx.gbl...
> Hi, I'm trying to figure out if doing an INSERT INTO.. a table with
> indexes is faster (or slower) than doing a SELECT INTO and then creating
> the indexes on the table created.
> The table in question contains around a million records.
> Thanks
>
> *** Sent via Developersdex http://www.developersdex.com ***|||And how many are you inserting? How many indexes and how large. How much
time does it need to create indexes? Hardware specs? Columns specs?
It would probably best to drop indexes and recreate them if you can, but I
dont see the problem even if you dont drop them. Only, try to see if you
need to defragment them after that...
MC
"Joe Grizzly" <grizzlyjoe@.campcool.com> wrote in message
news:%23OYYT%23zWHHA.4252@.TK2MSFTNGP06.phx.gbl...
> Hi, I'm trying to figure out if doing an INSERT INTO.. a table with
> indexes is faster (or slower) than doing a SELECT INTO and then creating
> the indexes on the table created.
> The table in question contains around a million records.
> Thanks
>
> *** Sent via Developersdex http://www.developersdex.com ***|||Joe
Most likely that SELECT * INTO will win. BTW ,1 million rows is not so big
nowadays
"Joe Grizzly" <grizzlyjoe@.campcool.com> wrote in message
news:%23OYYT%23zWHHA.4252@.TK2MSFTNGP06.phx.gbl...
> Hi, I'm trying to figure out if doing an INSERT INTO.. a table with
> indexes is faster (or slower) than doing a SELECT INTO and then creating
> the indexes on the table created.
> The table in question contains around a million records.
> Thanks
>
> *** Sent via Developersdex http://www.developersdex.com ***|||Hello,
If it is a production server I will suggest you to create the table and
indexes and then insert the data into new table using BCP IN or BULK INSERT
in batch commit mode with BULK_INSERT recovery model. If you are creating a
table with SELECT * INTO and this will create locks in sysobjects table
which
is not a good idea.
Thanks
Hari
"Joe Grizzly" <grizzlyjoe@.campcool.com> wrote in message
news:%23OYYT%23zWHHA.4252@.TK2MSFTNGP06.phx.gbl...
> Hi, I'm trying to figure out if doing an INSERT INTO.. a table with
> indexes is faster (or slower) than doing a SELECT INTO and then creating
> the indexes on the table created.
> The table in question contains around a million records.
> Thanks
>
> *** Sent via Developersdex http://www.developersdex.com ***|||Can anybody explain to me exactly what BULK_LOGGED recovery does?
I gather it has less overhead and less recovery options than FULL, but
the BOL description is pretty vague.
Thanks.
Josh
On Wed, 28 Feb 2007 08:05:51 -0600, "Hari Prasad"
<hari_prasad_k@.hotmail.com> wrote:
>Hello,
>If it is a production server I will suggest you to create the table and
>indexes and then insert the data into new table using BCP IN or BULK INSERT
>in batch commit mode with BULK_INSERT recovery model. If you are creating a
>table with SELECT * INTO and this will create locks in sysobjects table
>which
> is not a good idea.
>Thanks
>Hari
>"Joe Grizzly" <grizzlyjoe@.campcool.com> wrote in message
>news:%23OYYT%23zWHHA.4252@.TK2MSFTNGP06.phx.gbl...
>> Hi, I'm trying to figure out if doing an INSERT INTO.. a table with
>> indexes is faster (or slower) than doing a SELECT INTO and then creating
>> the indexes on the table created.
>> The table in question contains around a million records.
>> Thanks
>>
>> *** Sent via Developersdex http://www.developersdex.com ***
>|||BOL has some good info but here are some basics. First off it is no
different than FULL recovery until you do an operation that can take
advantage of a minimally logged operation. These would be things like:
CREATE INDEX
SELECT INTO
BULK INSERT
etc. (See BOL for more details)
When you do execute one of these in Bulk_Logged or Simple recovery mode only
the ID of the extent that was modified during that operation is logged in
the tran log. So if you did a bulk insert of 1 million rows and it filled up
1000 extents you would only log the ID's for those 1000 extents not the
actual data that would normally be logged in FULL recovey mode.There are
several implications of this. One is that you can no longer do point in time
recovery until you issue another full backup to start the proper logging
again. You can restore the entire log file though. The second is that when
you backup the tran log it will go and get all the data for those 1000
extents and place them in the log backup file. So the backup will take the
hit that normally would have occured if you did not do a minimally logged
load.
--
Andrew J. Kelly SQL MVP
"JXStern" <JXSternChangeX2R@.gte.net> wrote in message
news:7skgu25thlm2vjher8dfrl29scbpj894bv@.4ax.com...
> Can anybody explain to me exactly what BULK_LOGGED recovery does?
> I gather it has less overhead and less recovery options than FULL, but
> the BOL description is pretty vague.
> Thanks.
> Josh
>
> On Wed, 28 Feb 2007 08:05:51 -0600, "Hari Prasad"
> <hari_prasad_k@.hotmail.com> wrote:
>>Hello,
>>If it is a production server I will suggest you to create the table and
>>indexes and then insert the data into new table using BCP IN or BULK
>>INSERT
>>in batch commit mode with BULK_INSERT recovery model. If you are creating
>>a
>>table with SELECT * INTO and this will create locks in sysobjects table
>>which
>> is not a good idea.
>>Thanks
>>Hari
>>"Joe Grizzly" <grizzlyjoe@.campcool.com> wrote in message
>>news:%23OYYT%23zWHHA.4252@.TK2MSFTNGP06.phx.gbl...
>> Hi, I'm trying to figure out if doing an INSERT INTO.. a table with
>> indexes is faster (or slower) than doing a SELECT INTO and then creating
>> the indexes on the table created.
>> The table in question contains around a million records.
>> Thanks
>>
>> *** Sent via Developersdex http://www.developersdex.com ***
>|||Andrew,
Thanks!
So, bulk_logged is not faster or cheaper than simple, even for those
operations.
We have some large tables that are recreated daily, and it has
occurred to us to move them out to a separate database we can run on
whatever lightweight logging we can find. I guess simple is the
simple answer! Also make sure it's on RAID10 space rather than RAID5.
Also, the point about backup taking a hit, is good to know. I guess
another option might be to use bulk_logged for a little extra safety
and then if nothing goes wrong, just truncate the log instead of
backing it up to tape.
Josh
On Fri, 2 Mar 2007 13:40:41 -0500, "Andrew J. Kelly"
<sqlmvpnooospam@.shadhawk.com> wrote:
>BOL has some good info but here are some basics. First off it is no
>different than FULL recovery until you do an operation that can take
>advantage of a minimally logged operation. These would be things like:
>CREATE INDEX
>SELECT INTO
>BULK INSERT
>etc. (See BOL for more details)
>When you do execute one of these in Bulk_Logged or Simple recovery mode only
>the ID of the extent that was modified during that operation is logged in
>the tran log. So if you did a bulk insert of 1 million rows and it filled up
>1000 extents you would only log the ID's for those 1000 extents not the
>actual data that would normally be logged in FULL recovey mode.There are
>several implications of this. One is that you can no longer do point in time
>recovery until you issue another full backup to start the proper logging
>again. You can restore the entire log file though. The second is that when
>you backup the tran log it will go and get all the data for those 1000
>extents and place them in the log backup file. So the backup will take the
>hit that normally would have occured if you did not do a minimally logged
>load.|||If it is a minimally logged operation (must meet all conditions listed
below) then you ge the same logging and performance from Bulk-Logged and
Simple cerocery models. The key difference with Simple is that you can't do
a log restore at all. Yes Raid 5 is bad for heavy writes. One more note. If
you do use a seperate db you still need valid FULL backups before you start
the operation.
a.. The recovery model is simple or bulk-logged.
a.. The target table is not being replicated.
a.. The target table does not have any triggers.
a.. The target table has either 0 rows or no indexes.
a.. The TABLOCK hint is specified. For more information,
Andrew J. Kelly SQL MVP
"JXStern" <JXSternChangeX2R@.gte.net> wrote in message
news:623ju250u00obhg2c3v4v3ell4guds7sm6@.4ax.com...
> Andrew,
> Thanks!
> So, bulk_logged is not faster or cheaper than simple, even for those
> operations.
> We have some large tables that are recreated daily, and it has
> occurred to us to move them out to a separate database we can run on
> whatever lightweight logging we can find. I guess simple is the
> simple answer! Also make sure it's on RAID10 space rather than RAID5.
> Also, the point about backup taking a hit, is good to know. I guess
> another option might be to use bulk_logged for a little extra safety
> and then if nothing goes wrong, just truncate the log instead of
> backing it up to tape.
> Josh
>
> On Fri, 2 Mar 2007 13:40:41 -0500, "Andrew J. Kelly"
> <sqlmvpnooospam@.shadhawk.com> wrote:
>>BOL has some good info but here are some basics. First off it is no
>>different than FULL recovery until you do an operation that can take
>>advantage of a minimally logged operation. These would be things like:
>>CREATE INDEX
>>SELECT INTO
>>BULK INSERT
>>etc. (See BOL for more details)
>>When you do execute one of these in Bulk_Logged or Simple recovery mode
>>only
>>the ID of the extent that was modified during that operation is logged in
>>the tran log. So if you did a bulk insert of 1 million rows and it filled
>>up
>>1000 extents you would only log the ID's for those 1000 extents not the
>>actual data that would normally be logged in FULL recovey mode.There are
>>several implications of this. One is that you can no longer do point in
>>time
>>recovery until you issue another full backup to start the proper logging
>>again. You can restore the entire log file though. The second is that when
>>you backup the tran log it will go and get all the data for those 1000
>>extents and place them in the log backup file. So the backup will take the
>>hit that normally would have occured if you did not do a minimally logged
>>load.
>

faster?

Hi
what is faster - LEFT JOIN or subselect
example
SELECT T.TITLE,S.ID FROM TITLES T LEFT JOIN SINDEXES S ON T.ID=S.TITLE_ID
or
SELECT T.TITLE,(SELECT ID FROM SINDEXES WHERE T.ID=TITLE_ID)
Mex"Meelis Lilbok" <meelis.lilbok@.deltmar.ee> wrote in message
news:ul%23QIbyWHHA.1396@.TK2MSFTNGP05.phx.gbl...
> Hi
> what is faster - LEFT JOIN or subselect
>
> example
> SELECT T.TITLE,S.ID FROM TITLES T LEFT JOIN SINDEXES S ON T.ID=S.TITLE_ID
> or
> SELECT T.TITLE,(SELECT ID FROM SINDEXES WHERE T.ID=TITLE_ID)
>
> Mex
>
Best bet, run this in query analyzer and look at the execution plans. The
optimizer may end up doing the same thing in either case. If not, then you
can see which is faster and why.
Greg Moore
SQL Server DBA Consulting
sql (at) greenms.com http://www.greenms.com|||Meelis
This is invalid in terms of syntax statement. Post the valid statements and
regarding to the question LEFT JOIN has nothing to do with SUBSELECT
"Meelis Lilbok" <meelis.lilbok@.deltmar.ee> wrote in message
news:ul%23QIbyWHHA.1396@.TK2MSFTNGP05.phx.gbl...
> Hi
> what is faster - LEFT JOIN or subselect
>
> example
> SELECT T.TITLE,S.ID FROM TITLES T LEFT JOIN SINDEXES S ON T.ID=S.TITLE_ID
> or
> SELECT T.TITLE,(SELECT ID FROM SINDEXES WHERE T.ID=TITLE_ID)
>
> Mex
>|||"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:uxnf0zyWHHA.4668@.TK2MSFTNGP04.phx.gbl...
> Meelis
> This is invalid in terms of syntax statement. Post the valid statements
> and regarding to the question LEFT JOIN has nothing to do with SUBSELECT
Hmm, good point I didn't even actually look at what he was trying to do
there. :-)
Well I'm pretty sure the syntax error will "return" faster than the left
join, so I guess the subselect is technically faster. :-)

>
>
> "Meelis Lilbok" <meelis.lilbok@.deltmar.ee> wrote in message
> news:ul%23QIbyWHHA.1396@.TK2MSFTNGP05.phx.gbl...
>
--
Greg Moore
SQL Server DBA Consulting
sql (at) greenms.com http://www.greenms.com|||SELECT T.TITLE,S.ID FROM TITLES T LEFT JOIN SINDEXES S ON T.ID=S.TITLE_ID
or
SELECT T.TITLE,(SELECT ID FROM SINDEXES WHERE T.ID=TITLE_ID)
FROM TITLES T
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:uxnf0zyWHHA.4668@.TK2MSFTNGP04.phx.gbl...
> Meelis
> This is invalid in terms of syntax statement. Post the valid statements
> and regarding to the question LEFT JOIN has nothing to do with SUBSELECT
>
>
> "Meelis Lilbok" <meelis.lilbok@.deltmar.ee> wrote in message
> news:ul%23QIbyWHHA.1396@.TK2MSFTNGP05.phx.gbl...
>|||Meekis
Again , these queries gave different results
use Nortwind
go
SELECT o.orderid,c.customerid FROM Customers c LEFT JOIN Orders o
ON c.Customerid=o.Customerid
SELECT o.orderid,(SELECT customerid FROM Customers WHERE
o.Customerid=Customerid)
FROM Orders o
Also there are diffrerences in execution plan show that the LEFT JOIN
performed better
"Meelis Lilbok" <meelis.lilbok@.deltmar.ee> wrote in message
news:%23dNqr4yWHHA.4624@.TK2MSFTNGP03.phx.gbl...
> SELECT T.TITLE,S.ID FROM TITLES T LEFT JOIN SINDEXES S ON T.ID=S.TITLE_ID
> or
> SELECT T.TITLE,(SELECT ID FROM SINDEXES WHERE T.ID=TITLE_ID)
> FROM TITLES T
>
>
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:uxnf0zyWHHA.4668@.TK2MSFTNGP04.phx.gbl...
>|||hmm
thats because orders MUST have CustomerID
but in my sample
SELECT T.TITLE,S.ID FROM TITLES T LEFT JOIN SINDEXES S ON T.ID=S.TITLE_ID
SINDEXES MAY have TITLE_ID
Meelis
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:uvCWU$yWHHA.4240@.TK2MSFTNGP06.phx.gbl...
> Meekis
> Again , these queries gave different results
> use Nortwind
> go
> SELECT o.orderid,c.customerid FROM Customers c LEFT JOIN Orders o
> ON c.Customerid=o.Customerid
> SELECT o.orderid,(SELECT customerid FROM Customers WHERE
> o.Customerid=Customerid)
> FROM Orders o
>
> Also there are diffrerences in execution plan show that the LEFT JOIN
> performed better
>
>
> "Meelis Lilbok" <meelis.lilbok@.deltmar.ee> wrote in message
> news:%23dNqr4yWHHA.4624@.TK2MSFTNGP03.phx.gbl...
>|||...I'd say from the face of it the first option would be faster, the second
would be running a correlated subquery for each row returned... ...but as
Greg said check the execution plan, these things can vary based on the numbe
r
of rows returned, available indexes, etc - don't underestimate the optimizer
;-)
"Meelis Lilbok" wrote:

> SELECT T.TITLE,S.ID FROM TITLES T LEFT JOIN SINDEXES S ON T.ID=S.TITLE_ID
> or
> SELECT T.TITLE,(SELECT ID FROM SINDEXES WHERE T.ID=TITLE_ID)
> FROM TITLES T
>
>
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:uxnf0zyWHHA.4668@.TK2MSFTNGP04.phx.gbl...
>
>|||As other have posted, SQL Server will most likely select the fastest
query plan itself, whether you have used syntax 1 or 2.
Whether loop join, merge join or hash join is fastest will depend on the
index depth, relative table size and data distribution. Of course, this
all assumes that both tables are properly indexed.
Gert-Jan
Meelis Lilbok wrote:[vbcol=seagreen]
> SELECT T.TITLE,S.ID FROM TITLES T LEFT JOIN SINDEXES S ON T.ID=S.TITLE_ID
> or
> SELECT T.TITLE,(SELECT ID FROM SINDEXES WHERE T.ID=TITLE_ID)
> FROM TITLES T
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:uxnf0zyWHHA.4668@.TK2MSFTNGP04.phx.gbl...

faster?

Hi
what is faster - LEFT JOIN or subselect
example
SELECT T.TITLE,S.ID FROM TITLES T LEFT JOIN SINDEXES S ON T.ID=S.TITLE_ID
or
SELECT T.TITLE,(SELECT ID FROM SINDEXES WHERE T.ID=TITLE_ID)
Mex
"Meelis Lilbok" <meelis.lilbok@.deltmar.ee> wrote in message
news:ul%23QIbyWHHA.1396@.TK2MSFTNGP05.phx.gbl...
> Hi
> what is faster - LEFT JOIN or subselect
>
> example
> SELECT T.TITLE,S.ID FROM TITLES T LEFT JOIN SINDEXES S ON T.ID=S.TITLE_ID
> or
> SELECT T.TITLE,(SELECT ID FROM SINDEXES WHERE T.ID=TITLE_ID)
>
> Mex
>
Best bet, run this in query analyzer and look at the execution plans. The
optimizer may end up doing the same thing in either case. If not, then you
can see which is faster and why.
Greg Moore
SQL Server DBA Consulting
sql (at) greenms.com http://www.greenms.com
|||Meelis
This is invalid in terms of syntax statement. Post the valid statements and
regarding to the question LEFT JOIN has nothing to do with SUBSELECT
"Meelis Lilbok" <meelis.lilbok@.deltmar.ee> wrote in message
news:ul%23QIbyWHHA.1396@.TK2MSFTNGP05.phx.gbl...
> Hi
> what is faster - LEFT JOIN or subselect
>
> example
> SELECT T.TITLE,S.ID FROM TITLES T LEFT JOIN SINDEXES S ON T.ID=S.TITLE_ID
> or
> SELECT T.TITLE,(SELECT ID FROM SINDEXES WHERE T.ID=TITLE_ID)
>
> Mex
>
|||"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:uxnf0zyWHHA.4668@.TK2MSFTNGP04.phx.gbl...
> Meelis
> This is invalid in terms of syntax statement. Post the valid statements
> and regarding to the question LEFT JOIN has nothing to do with SUBSELECT
Hmm, good point I didn't even actually look at what he was trying to do
there. :-)
Well I'm pretty sure the syntax error will "return" faster than the left
join, so I guess the subselect is technically faster. :-)

>
>
> "Meelis Lilbok" <meelis.lilbok@.deltmar.ee> wrote in message
> news:ul%23QIbyWHHA.1396@.TK2MSFTNGP05.phx.gbl...
>
Greg Moore
SQL Server DBA Consulting
sql (at) greenms.com http://www.greenms.com
|||SELECT T.TITLE,S.ID FROM TITLES T LEFT JOIN SINDEXES S ON T.ID=S.TITLE_ID
or
SELECT T.TITLE,(SELECT ID FROM SINDEXES WHERE T.ID=TITLE_ID)
FROM TITLES T
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:uxnf0zyWHHA.4668@.TK2MSFTNGP04.phx.gbl...
> Meelis
> This is invalid in terms of syntax statement. Post the valid statements
> and regarding to the question LEFT JOIN has nothing to do with SUBSELECT
>
>
> "Meelis Lilbok" <meelis.lilbok@.deltmar.ee> wrote in message
> news:ul%23QIbyWHHA.1396@.TK2MSFTNGP05.phx.gbl...
>
|||Meekis
Again , these queries gave different results
use Nortwind
go
SELECT o.orderid,c.customerid FROM Customers c LEFT JOIN Orders o
ON c.Customerid=o.Customerid
SELECT o.orderid,(SELECT customerid FROM Customers WHERE
o.Customerid=Customerid)
FROM Orders o
Also there are diffrerences in execution plan show that the LEFT JOIN
performed better
"Meelis Lilbok" <meelis.lilbok@.deltmar.ee> wrote in message
news:%23dNqr4yWHHA.4624@.TK2MSFTNGP03.phx.gbl...
> SELECT T.TITLE,S.ID FROM TITLES T LEFT JOIN SINDEXES S ON T.ID=S.TITLE_ID
> or
> SELECT T.TITLE,(SELECT ID FROM SINDEXES WHERE T.ID=TITLE_ID)
> FROM TITLES T
>
>
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:uxnf0zyWHHA.4668@.TK2MSFTNGP04.phx.gbl...
>
|||hmm
thats because orders MUST have CustomerID
but in my sample
SELECT T.TITLE,S.ID FROM TITLES T LEFT JOIN SINDEXES S ON T.ID=S.TITLE_ID
SINDEXES MAY have TITLE_ID
Meelis
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:uvCWU$yWHHA.4240@.TK2MSFTNGP06.phx.gbl...
> Meekis
> Again , these queries gave different results
> use Nortwind
> go
> SELECT o.orderid,c.customerid FROM Customers c LEFT JOIN Orders o
> ON c.Customerid=o.Customerid
> SELECT o.orderid,(SELECT customerid FROM Customers WHERE
> o.Customerid=Customerid)
> FROM Orders o
>
> Also there are diffrerences in execution plan show that the LEFT JOIN
> performed better
>
>
> "Meelis Lilbok" <meelis.lilbok@.deltmar.ee> wrote in message
> news:%23dNqr4yWHHA.4624@.TK2MSFTNGP03.phx.gbl...
>

faster?

Hi
what is faster - LEFT JOIN or subselect
example
SELECT T.TITLE,S.ID FROM TITLES T LEFT JOIN SINDEXES S ON T.ID=S.TITLE_ID
or
SELECT T.TITLE,(SELECT ID FROM SINDEXES WHERE T.ID=TITLE_ID)
Mex"Meelis Lilbok" <meelis.lilbok@.deltmar.ee> wrote in message
news:ul%23QIbyWHHA.1396@.TK2MSFTNGP05.phx.gbl...
> Hi
> what is faster - LEFT JOIN or subselect
>
> example
> SELECT T.TITLE,S.ID FROM TITLES T LEFT JOIN SINDEXES S ON T.ID=S.TITLE_ID
> or
> SELECT T.TITLE,(SELECT ID FROM SINDEXES WHERE T.ID=TITLE_ID)
>
> Mex
>
Best bet, run this in query analyzer and look at the execution plans. The
optimizer may end up doing the same thing in either case. If not, then you
can see which is faster and why.
--
Greg Moore
SQL Server DBA Consulting
sql (at) greenms.com http://www.greenms.com|||Meelis
This is invalid in terms of syntax statement. Post the valid statements and
regarding to the question LEFT JOIN has nothing to do with SUBSELECT
"Meelis Lilbok" <meelis.lilbok@.deltmar.ee> wrote in message
news:ul%23QIbyWHHA.1396@.TK2MSFTNGP05.phx.gbl...
> Hi
> what is faster - LEFT JOIN or subselect
>
> example
> SELECT T.TITLE,S.ID FROM TITLES T LEFT JOIN SINDEXES S ON T.ID=S.TITLE_ID
> or
> SELECT T.TITLE,(SELECT ID FROM SINDEXES WHERE T.ID=TITLE_ID)
>
> Mex
>|||"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:uxnf0zyWHHA.4668@.TK2MSFTNGP04.phx.gbl...
> Meelis
> This is invalid in terms of syntax statement. Post the valid statements
> and regarding to the question LEFT JOIN has nothing to do with SUBSELECT
Hmm, good point I didn't even actually look at what he was trying to do
there. :-)
Well I'm pretty sure the syntax error will "return" faster than the left
join, so I guess the subselect is technically faster. :-)
>
>
> "Meelis Lilbok" <meelis.lilbok@.deltmar.ee> wrote in message
> news:ul%23QIbyWHHA.1396@.TK2MSFTNGP05.phx.gbl...
>> Hi
>> what is faster - LEFT JOIN or subselect
>>
>> example
>> SELECT T.TITLE,S.ID FROM TITLES T LEFT JOIN SINDEXES S ON T.ID=S.TITLE_ID
>> or
>> SELECT T.TITLE,(SELECT ID FROM SINDEXES WHERE T.ID=TITLE_ID)
>>
>> Mex
>
--
Greg Moore
SQL Server DBA Consulting
sql (at) greenms.com http://www.greenms.com|||SELECT T.TITLE,S.ID FROM TITLES T LEFT JOIN SINDEXES S ON T.ID=S.TITLE_ID
or
SELECT T.TITLE,(SELECT ID FROM SINDEXES WHERE T.ID=TITLE_ID)
FROM TITLES T
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:uxnf0zyWHHA.4668@.TK2MSFTNGP04.phx.gbl...
> Meelis
> This is invalid in terms of syntax statement. Post the valid statements
> and regarding to the question LEFT JOIN has nothing to do with SUBSELECT
>
>
> "Meelis Lilbok" <meelis.lilbok@.deltmar.ee> wrote in message
> news:ul%23QIbyWHHA.1396@.TK2MSFTNGP05.phx.gbl...
>> Hi
>> what is faster - LEFT JOIN or subselect
>>
>> example
>> SELECT T.TITLE,S.ID FROM TITLES T LEFT JOIN SINDEXES S ON T.ID=S.TITLE_ID
>> or
>> SELECT T.TITLE,(SELECT ID FROM SINDEXES WHERE T.ID=TITLE_ID)
>>
>> Mex
>|||Meekis
Again , these queries gave different results
use Nortwind
go
SELECT o.orderid,c.customerid FROM Customers c LEFT JOIN Orders o
ON c.Customerid=o.Customerid
SELECT o.orderid,(SELECT customerid FROM Customers WHERE
o.Customerid=Customerid)
FROM Orders o
Also there are diffrerences in execution plan show that the LEFT JOIN
performed better
"Meelis Lilbok" <meelis.lilbok@.deltmar.ee> wrote in message
news:%23dNqr4yWHHA.4624@.TK2MSFTNGP03.phx.gbl...
> SELECT T.TITLE,S.ID FROM TITLES T LEFT JOIN SINDEXES S ON T.ID=S.TITLE_ID
> or
> SELECT T.TITLE,(SELECT ID FROM SINDEXES WHERE T.ID=TITLE_ID)
> FROM TITLES T
>
>
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:uxnf0zyWHHA.4668@.TK2MSFTNGP04.phx.gbl...
>> Meelis
>> This is invalid in terms of syntax statement. Post the valid statements
>> and regarding to the question LEFT JOIN has nothing to do with SUBSELECT
>>
>>
>> "Meelis Lilbok" <meelis.lilbok@.deltmar.ee> wrote in message
>> news:ul%23QIbyWHHA.1396@.TK2MSFTNGP05.phx.gbl...
>> Hi
>> what is faster - LEFT JOIN or subselect
>>
>> example
>> SELECT T.TITLE,S.ID FROM TITLES T LEFT JOIN SINDEXES S ON
>> T.ID=S.TITLE_ID
>> or
>> SELECT T.TITLE,(SELECT ID FROM SINDEXES WHERE T.ID=TITLE_ID)
>>
>> Mex
>>
>|||hmm
thats because orders MUST have CustomerID
but in my sample
SELECT T.TITLE,S.ID FROM TITLES T LEFT JOIN SINDEXES S ON T.ID=S.TITLE_ID
SINDEXES MAY have TITLE_ID
Meelis
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:uvCWU$yWHHA.4240@.TK2MSFTNGP06.phx.gbl...
> Meekis
> Again , these queries gave different results
> use Nortwind
> go
> SELECT o.orderid,c.customerid FROM Customers c LEFT JOIN Orders o
> ON c.Customerid=o.Customerid
> SELECT o.orderid,(SELECT customerid FROM Customers WHERE
> o.Customerid=Customerid)
> FROM Orders o
>
> Also there are diffrerences in execution plan show that the LEFT JOIN
> performed better
>
>
> "Meelis Lilbok" <meelis.lilbok@.deltmar.ee> wrote in message
> news:%23dNqr4yWHHA.4624@.TK2MSFTNGP03.phx.gbl...
>> SELECT T.TITLE,S.ID FROM TITLES T LEFT JOIN SINDEXES S ON T.ID=S.TITLE_ID
>> or
>> SELECT T.TITLE,(SELECT ID FROM SINDEXES WHERE T.ID=TITLE_ID)
>> FROM TITLES T
>>
>>
>> "Uri Dimant" <urid@.iscar.co.il> wrote in message
>> news:uxnf0zyWHHA.4668@.TK2MSFTNGP04.phx.gbl...
>> Meelis
>> This is invalid in terms of syntax statement. Post the valid statements
>> and regarding to the question LEFT JOIN has nothing to do with SUBSELECT
>>
>>
>> "Meelis Lilbok" <meelis.lilbok@.deltmar.ee> wrote in message
>> news:ul%23QIbyWHHA.1396@.TK2MSFTNGP05.phx.gbl...
>> Hi
>> what is faster - LEFT JOIN or subselect
>>
>> example
>> SELECT T.TITLE,S.ID FROM TITLES T LEFT JOIN SINDEXES S ON
>> T.ID=S.TITLE_ID
>> or
>> SELECT T.TITLE,(SELECT ID FROM SINDEXES WHERE T.ID=TITLE_ID)
>>
>> Mex
>>
>>
>|||...I'd say from the face of it the first option would be faster, the second
would be running a correlated subquery for each row returned... ...but as
Greg said check the execution plan, these things can vary based on the number
of rows returned, available indexes, etc - don't underestimate the optimizer
;-)
"Meelis Lilbok" wrote:
> SELECT T.TITLE,S.ID FROM TITLES T LEFT JOIN SINDEXES S ON T.ID=S.TITLE_ID
> or
> SELECT T.TITLE,(SELECT ID FROM SINDEXES WHERE T.ID=TITLE_ID)
> FROM TITLES T
>
>
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:uxnf0zyWHHA.4668@.TK2MSFTNGP04.phx.gbl...
> > Meelis
> >
> > This is invalid in terms of syntax statement. Post the valid statements
> > and regarding to the question LEFT JOIN has nothing to do with SUBSELECT
> >
> >
> >
> >
> > "Meelis Lilbok" <meelis.lilbok@.deltmar.ee> wrote in message
> > news:ul%23QIbyWHHA.1396@.TK2MSFTNGP05.phx.gbl...
> >> Hi
> >>
> >> what is faster - LEFT JOIN or subselect
> >>
> >>
> >> example
> >>
> >> SELECT T.TITLE,S.ID FROM TITLES T LEFT JOIN SINDEXES S ON T.ID=S.TITLE_ID
> >> or
> >>
> >> SELECT T.TITLE,(SELECT ID FROM SINDEXES WHERE T.ID=TITLE_ID)
> >>
> >>
> >>
> >> Mex
> >>
> >
> >
>
>|||As other have posted, SQL Server will most likely select the fastest
query plan itself, whether you have used syntax 1 or 2.
Whether loop join, merge join or hash join is fastest will depend on the
index depth, relative table size and data distribution. Of course, this
all assumes that both tables are properly indexed.
Gert-Jan
Meelis Lilbok wrote:
> SELECT T.TITLE,S.ID FROM TITLES T LEFT JOIN SINDEXES S ON T.ID=S.TITLE_ID
> or
> SELECT T.TITLE,(SELECT ID FROM SINDEXES WHERE T.ID=TITLE_ID)
> FROM TITLES T
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:uxnf0zyWHHA.4668@.TK2MSFTNGP04.phx.gbl...
> > Meelis
> >
> > This is invalid in terms of syntax statement. Post the valid statements
> > and regarding to the question LEFT JOIN has nothing to do with SUBSELECT
> >
> >
> >
> >
> > "Meelis Lilbok" <meelis.lilbok@.deltmar.ee> wrote in message
> > news:ul%23QIbyWHHA.1396@.TK2MSFTNGP05.phx.gbl...
> >> Hi
> >>
> >> what is faster - LEFT JOIN or subselect
> >>
> >>
> >> example
> >>
> >> SELECT T.TITLE,S.ID FROM TITLES T LEFT JOIN SINDEXES S ON T.ID=S.TITLE_ID
> >> or
> >>
> >> SELECT T.TITLE,(SELECT ID FROM SINDEXES WHERE T.ID=TITLE_ID)
> >>
> >>
> >>
> >> Mex
> >>
> >
> >