Does SQL uses index in the following select statement

I have the following table structure:
PK_Column1
PK_Column2
IndexedColumn
Column_ABC
Column_XYZ
Does SQL Server 2005 uses the IndexedColumn index to find the MIN and MAX
values in the following select statement:
SELECT MIN(IndexedColumn), MAX(IndexedColumn) FROM MyTable WHERE
PK_Column1=@.MyParam
When I run this statement it works too slow and I see alot of reads in SQL
Server Profiler.
Is there any way to improve the performance in this case?
Message posted via webservertalk.com
http://www.webservertalk.com/Uwe/Forum...amming/200512/1Hi
If Indexed column is clustered, it probably would as it can do a clustered
index range scan.
Else, it may not. It all depends on how up to date the statistics are, the
data types of the columns, how selective the indexes are and the number of
rows.
Show the query plan and we can tell.
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Alex via webservertalk.com" <no@.spam.pls> wrote in message
news:58e799d08a0ce@.uwe...
>I have the following table structure:
> PK_Column1
> PK_Column2
> IndexedColumn
> Column_ABC
> Column_XYZ
> Does SQL Server 2005 uses the IndexedColumn index to find the MIN and MAX
> values in the following select statement:
> SELECT MIN(IndexedColumn), MAX(IndexedColumn) FROM MyTable WHERE
> PK_Column1=@.MyParam
> When I run this statement it works too slow and I see alot of reads in SQL
> Server Profiler.
> Is there any way to improve the performance in this case?
> --
> Message posted via webservertalk.com
> http://www.webservertalk.com/Uwe/Forum...amming/200512/1|||Thank you for your answer.
Here are more details:
PK_Column1 smallint
PK_Column2 int
IndexedColumn DateTime (NON-CLUSTERED and not unique)
Column_ABC varchar
Column_XYZ varbinary(BLOB)
The table has about 4M rows.

>Show the query plan and we can tell.
How can I get it? I am using the Standard edition of SQL Server 2005.
Message posted via webservertalk.com
http://www.webservertalk.com/Uwe/Forum...amming/200512/1|||If there is a clustered index on PK_Column1, then it might do an index
scan on the index of IndexedColumn. However, the query would benefit
more from an index on (PK_Column1, IndexedColumn).
I am not running SQL2K5, but I guess that SET SHOWPLAN_TEXT ON will
probably still work...
HTH,
Gert-Jaqn
"Alex via webservertalk.com" wrote:
> I have the following table structure:
> PK_Column1
> PK_Column2
> IndexedColumn
> Column_ABC
> Column_XYZ
> Does SQL Server 2005 uses the IndexedColumn index to find the MIN and MAX
> values in the following select statement:
> SELECT MIN(IndexedColumn), MAX(IndexedColumn) FROM MyTable WHERE
> PK_Column1=@.MyParam
> When I run this statement it works too slow and I see alot of reads in SQL
> Server Profiler.
> Is there any way to improve the performance in this case?
> --
> Message posted via webservertalk.com
> http://www.webservertalk.com/Uwe/Forum...amming/200512/1

Does SQL Server 2005 Support the List Statement?

We have a comprehensive windows based application which uses a database othe
r
than SQL Server. We have been considering moving our application to SQL
server but have been stymied by limitations in the ANS SQL implementation as
well has hidden restrictions in Decalarative Referential integrity.
One statement we use a lot in reporting is the ANS List statment (a scalar
aggregate function for strings which can return a set of results in a
appropriate delimited string).
Does anyone know if the List statement is implemented fully in SQL Server
2005.
If not, we would be interested in what techniques others use to get around
this limitation.
Thanks> One statement we use a lot in reporting is the ANS List statment (a scalar
> aggregate function for strings which can return a set of results in a
> appropriate delimited string).
> Does anyone know if the List statement is implemented fully in SQL Server
> 2005.
NO, it is not.

> If not, we would be interested in what techniques others use to get around
> this limitation.
http://www.aspfaq.com/2529|||"Glenn" wrote:

> We have a comprehensive windows based application which uses a database ot
her
> than SQL Server. We have been considering moving our application to SQL
> server but have been stymied by limitations in the ANS SQL implementation
as
> well has hidden restrictions in Decalarative Referential integrity.
> One statement we use a lot in reporting is the ANS List statment (a scalar
> aggregate function for strings which can return a set of results in a
> appropriate delimited string).
> Does anyone know if the List statement is implemented fully in SQL Server
> 2005.
> If not, we would be interested in what techniques others use to get around
> this limitation.
> Thanks
When you say "ANS SQL" do you mean ANSI SQL? If so, I think you are
mistaken. The LIST "aggregate" has never been part of standard SQL. It is a
proprietary feature in DB2 I believe (and possibly others).
There are a number of logical and practical problems with the concept of a
"string concatenation aggregate". The problems are to do with the fact that
concatentation implies order, which implies sorting. Furthermore, determinis
m
requires unique sorting. This means that "LIST" is A) potentially expensive
on performance B) difficult to ensure reliable results in queries C) totally
contrary to the way other SQL queries and aggregates work.
The ANSI response to "ordered" functions is the Windowed functions and these
*are* supported by SQL Server 2005. Theoretically you can kludge your own
string aggregate using standard SQL, provided you can set some reasdonable
upper limit to the number of items to be concatenated. Allternatively there
are non-standard workarounds in TSQL as there apparently are in your current
database.
Rather than try to support such a potential kludge in the database I suggest
you look at doing this client-side. In ADO you have the GetString method to
serve that purpose.
David Portas
SQL Server MVP
--|||Thanks very much for your response. This is a bit disappointing. We had so
hoped they would address this item as it is so commonly used in other SQL
implementations.
I took a look at the faq sample - and though helpful - and it looks like it
might add quite a lot of complexity to the SQL in many of our queries. I
think it is unfortunate that Microsoft continues to add many new features to
its SQL implementation, yet seems to have long ignored the ANS standard SQL.
It makes it difficult for developers and ISV's to move their applications
from other vendors to the M/S platform.
Glenn
"Aaron Bertrand [SQL Server MVP]" wrote:

> NO, it is not.
>
> http://www.aspfaq.com/2529
>
>|||> yet seems to have long ignored the ANS standard SQL.
Why do you think LIST is ANSI standard?
A|||Hi, this is pretty helpful, although it was my understanding that the SELECT
LIST command was ANSI standard although there were various vendor extensions
to the feature added for control of concatenation, separators, and order.
We are a bit spoiled by the iAnywhere implementation which works very
efficiently for our application and allows for ordering of the result (an
obvious Vendor extention). We have also tried doing this on the client side
,
and it was much-much slower.
Thanks for your help, looks like we are going to need to engage a SQL port
specialist.
BTW - We utilize quite a few Scalar Subqueries in our application, do you
feel that using these on SQL Server 2005 will introduce any special
performance or implementation problems.
Glenn
"David Portas" wrote:

> "Glenn" wrote:
>
> When you say "ANS SQL" do you mean ANSI SQL? If so, I think you are
> mistaken. The LIST "aggregate" has never been part of standard SQL. It is
a
> proprietary feature in DB2 I believe (and possibly others).
> There are a number of logical and practical problems with the concept of a
> "string concatenation aggregate". The problems are to do with the fact tha
t
> concatentation implies order, which implies sorting. Furthermore, determin
ism
> requires unique sorting. This means that "LIST" is A) potentially expensiv
e
> on performance B) difficult to ensure reliable results in queries C) total
ly
> contrary to the way other SQL queries and aggregates work.
> The ANSI response to "ordered" functions is the Windowed functions and the
se
> *are* supported by SQL Server 2005. Theoretically you can kludge your own
> string aggregate using standard SQL, provided you can set some reasdonable
> upper limit to the number of items to be concatenated. Allternatively ther
e
> are non-standard workarounds in TSQL as there apparently are in your curre
nt
> database.
> Rather than try to support such a potential kludge in the database I sugge
st
> you look at doing this client-side. In ADO you have the GetString method t
o
> serve that purpose.
> --
> David Portas
> SQL Server MVP
> --
>|||In our database documentation it was descibed as ANS 92/99 with optional
vendor extensions.
--
Glenn
"Aaron Bertrand [SQL Server MVP]" wrote:

> Why do you think LIST is ANSI standard?
> A
>
>|||LIST isn't ANSI Standard and never has been - I just checked the
standards docs. The reason is probably related to some of the logical
problems I mentioned. It just doesn't make sense as an "aggregate"
function.
I think SQL Server is actually reasonably good on standard SQL
compliance, and it goes further with 2005 - the SQL1999 OLAP functions
for example -although it certainly could do better.
David Portas
SQL Server MVP
--|||I do not wish to offend anyone, but this is - after all - the 21st century:
consider using XML (e.g. in SQL 2005) to replace the less useful LIST
function, if you really need to move sets of data about on the server.
ML|||Can you give me link to the ANSI SQL specification documents?
Thanks
--
Glenn
"David Portas" wrote:

> LIST isn't ANSI Standard and never has been - I just checked the
> standards docs. The reason is probably related to some of the logical
> problems I mentioned. It just doesn't make sense as an "aggregate"
> function.
> I think SQL Server is actually reasonably good on standard SQL
> compliance, and it goes further with 2005 - the SQL1999 OLAP functions
> for example -although it certainly could do better.
> --
> David Portas
> SQL Server MVP
> --
>

Does SQL permit use of a column alias directly in the CASE statement?

Our Case statements can get very long and complicated and I would like to maintain just a single "Case" and than depending on the type of set I'm processing,
use this generic "Case" statement to properly decode the value. My SQL code is listed below.

Select
'PCAR' as [vtyp]
,Case
When [vtyp]='PCAR' and substring(veh_vin,8,1)= 'A' Then 'Cyl = V-6 Fuel = Gasoline'
When [vtyp]='PTRK' and substring(veh_vin,8,1)= 'A' Then 'Cyl = V-8 Fuel = Diesel'
When [vtyp]='PCAR' and substring(veh_vin,8,1)= 'B' Then 'Cyl = V-6 Fuel = CNG'
Else '?' End as 'Engine_Decode'
From veh_owner
Where substring(veh_vin,10,1) = '3' --Selects on cars

Union All

Select
'PTRK' as [vtyp]
,Case
When [vtyp]='PCAR' and substring(veh_vin,8,1)= 'A' Then 'Cyl = V-6 Fuel = Gasoline'
When [vtyp]='PTRK' and substring(veh_vin,8,1)= 'A' Then 'Cyl = V-8 Fuel = Diesel'
When [vtyp]='PCAR' and substring(veh_vin,8,1)= 'B' Then 'Cyl = V-6 Fuel = CNG'
Else '?' End as 'Engine_Decode'
From veh_owner
Where substring(veh_vin,10,1) = '4' --Selects only Trucks

I would like to deploy the SQL code described above but get the following errors:

Msg 207, Level 16, State 1, Line 18 Invalid column name 'vtyp'

Any ideas, comments or help with this issue will be greatly appreciated.

sfmd:

SQL Server does not have this kind of alias. I am not sure that I understand what you are looking for, is this heading in the right direction?:

select veh_vin,
substring ('1 2 PCARPTRK', 4 * convert(integer, substring (veh_vin,10,1)) - 3, 4) as [vtyp],
case when substring (veh_vin,10,1) = '3' and substring (veh_vin,8,1) = 'A' then 'Cyl = V-6 Fuel = Gasoline'
when substring (veh_vin,10,1) = '4' and substring (veh_vin,8,1) = 'A' then 'Cyl = V-8 Fuel = Diesel'
when substring (veh_vin,10,1) = '3' and substring (veh_vin,8,1) = 'B' then 'Cyl = V-6 Fuel = CNG'
else '?'
end as Engine_Decode
from veh_owner
where substring (veh_vin,10,1) between '3' and '4'

-- - Sample Output: -

-- veh_vin vtyp Engine_Decode
-- -- -
-- 1233567A1373 PCAR Cyl = V-6 Fuel = Gasoline
-- 1243567A1373 PCAR Cyl = V-6 Fuel = Gasoline
-- 1253567B1373 PCAR Cyl = V-6 Fuel = CNG
-- 1263567A1373 PCAR Cyl = V-6 Fuel = Gasoline
-- 1334567A1473 PTRK Cyl = V-8 Fuel = Diesel
-- 1344467A1473 PTRK Cyl = V-8 Fuel = Diesel
-- 2344467B1473 PTRK ?

Dave

|||

One thing to consider is persisting these two characters of the VIN. I realize that a VIN is what it is and you cannot change these "smart coded" columns; nonetheless, these portions of the VIN have a specific meaning and it might be a good idea to give these meanings an existence of themselves rather than only existing as a "sub-column".

Dave

|||

>>One thing to consider is persisting these two characters of the VIN. I realize that a VIN is what it is and you cannot change these "smart coded" columns; nonetheless, these portions of the VIN have a specific meaning and it might be a good idea to give these meanings an existence of themselves rather than only existing as a "sub-column".<<

Absolutely. Anytime you find yourself querying on a substring, you probably ought to reconsider what you are doing :)

|||

Perhaps I was not clear enough with my example

My point with this thread is to find a way to use a generic or multi-purpose "Case" Statement to process similar "sets" of rows. The selection of each processing set, in this case either all cars or all trucks, but never a mixed group of vehicles can be handled a thousand different ways by configuring a "Where" clause.

However the vehicle group is selected, I would like to use a generic "Case" for decoding certain values. I will always know the composition of the set (vtyp=value) again either cars or trucks and need to convey/pass this informational profile to the generic "Case" statement.

Notice that a car "A" in VIN position 10 decodes differently than a truck "A" in VIN position 10, that's the crux of the problem. Sure it's easy to use and maintain 2 different "Case" Statements(1 for car and 1 for trucks), but that's not what I initially would prefer to do. It's sure to get out of hand very quickly.

I hope this helps clarify my issue

|||In answer to your question "no" you cannot do what you want without making it dynamic SQL.|||

How about having UDF on this..

Create Function dbo.VehData(@.Vtype as varchar(10), @.VinId Varchar(10))
returns Table As
Return (Select
@.Vtype as [vtyp]
,Case
When @.Vtype='PCAR' and substring(veh_vin,8,1)= 'A' Then 'Cyl = V-6 Fuel = Gasoline'
When @.Vtype='PTRK' and substring(veh_vin,8,1)= 'A' Then 'Cyl = V-8 Fuel = Diesel'
When @.Vtype='PCAR' and substring(veh_vin,8,1)= 'B' Then 'Cyl = V-6 Fuel = CNG'
Else '?' End as Engine_Decode
From veh_owner
Where substring(veh_vin,10,1) = @.VinId )

Go

Select * From VehData('PCAR',3)
Union All
Select * From VehData('PTRK',4)
Union ALL
..etc

Does 'SELECT INTO' not work in SQL Mobile?

I'm trying to programmatically create a new table that is a copy of another. The SQL statement I'm making is:

"SELECT * INTO sensor_stream_temp FROM sensor_stream"
and various combinations thereof (such as specifying the columns, etc)

The error I keep getting is:
There was an error parsing the query. [Token line number 1, Token line offset 10, , Token in error = INTO ]

Is it not possible to copy a table using this statement? *sigh*
Thanks in advance for any help.
-Dana
OK, so it looks like "INTO" is not allowed in SQL Mobile; how do I make a copy of a table?

|||

Try this:

INSERT INTO sensor_stream_temp (SELECT * FROM sensor_stream);

Note: Both sensor_stream_temp, sensor_stream MUST be of similar.

Does scope_identity or @@identity always return NULL on Subscriber

I have insert sp's that run on a subscriber. After the insert sql statement
I use scope_identity to get the last inserted identity for child rows for
parent child relationships.
After implementing transactional replication with immediate updates, calling
this sp on the subscriber inserts the row properly but both scope_identity
and @.@.identity returns null.
How do I get the identity value of the last inserted row on a subscriber so
I can use that ID for child rows?Ben,
interesting. These identity values are controlled by the publisher rather
than the subscriber. In fact, you should find that on the subscriber there
isn't an identity property on the tables for this reason. Are you're using
SQL Server 2005? If so, you could capture the assigned value using the
OUTPUT clause.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)|||No I'm using SQL Server 2000...i may have to move to 2005 sooner than later.
Its hard to believe this can't work because let's say you have an order with
order details tables. You would first create the order on the order table
and get a new Order ID identity, then have to use that identity for the
parent child relationship to the order detail row. This type of scenario
must've been implemented in sql server 2000 with transactional replication
and immediate updates right? How do you retrieve the new ident from the
subscriber without doing maybe a Select max(ident_column) after the insert?
"Paul Ibison" wrote:
> Ben,
> interesting. These identity values are controlled by the publisher rather
> than the subscriber. In fact, you should find that on the subscriber there
> isn't an identity property on the tables for this reason. Are you're using
> SQL Server 2005? If so, you could capture the assigned value using the
> OUTPUT clause.
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
>
>|||Ben,
I've just retested this and it depends on your replication setup. If you
have the identity column set to 'Yes (Not for Replication)' then there are
problems, but if it is set to just 'Yes' (the recommended way) then
@.@.identity should return the correct value and scope_identity() will not
return anything. Perhaps this is the source of your issue? Alternatively I
was wondering if you have any user triggers in action?
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)|||I have no triggers on these tables other than the ones created by replication.
Paul, I'm assuming you're asking me if the Publisher has the identity column
set to 'Yes (Not for Replication)'. If so, then no its set to simply "Yes".
In your test environment, was the subscriber on the same server? I tested
this with the subscriber on the same server and the @.@.identity did return the
correct value, but when the subscriber was on another sql server it still
returned null.
so to clarify, my publisher has a identity column with "Yes", and my
subscriber has this column replicated as an int (i.e. no ident column)...the
subscriber column shouldn't have an ident right?
"Paul Ibison" wrote:
> Ben,
> I've just retested this and it depends on your replication setup. If you
> have the identity column set to 'Yes (Not for Replication)' then there are
> problems, but if it is set to just 'Yes' (the recommended way) then
> @.@.identity should return the correct value and scope_identity() will not
> return anything. Perhaps this is the source of your issue? Alternatively I
> was wondering if you have any user triggers in action?
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
>
>
>|||Hi Ben,
Yes - I agree with everything you're saying :)
I'm currently testing your scenario on my home PC (2 databases on same
instance) which catches the @.@.identity (without the NFR attribute). I will
set it up accross servers when I get to work tomorrow.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)|||Thank you Paul!!! I've been racking my brain around this for almost 2 days.
My intern worse case scenario is to move to 2005 and use the OUTPUT clause
you mentioned or replacing the "Scope_Identity()" code to a Select
Max(Ident_Column) in the insert sp ...which to me feels more like a hack
thanks again!
"Paul Ibison" wrote:
> Hi Ben,
> Yes - I agree with everything you're saying :)
> I'm currently testing your scenario on my home PC (2 databases on same
> instance) which catches the @.@.identity (without the NFR attribute). I will
> set it up accross servers when I get to work tomorrow.
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
>
>
>
>|||Ben,
Unfortunately we only have 1 instance of SQL Server 2000 on the network and
when I finally got this set up on 2 networked instances of SQL Server 2005
it worked fine - I could pick up both the @.@.identity and the
scope_identity() values. However I noticed that this is implemented
differently and the identity property is on the subscriber in the new
version so this is not really a decent test. I'll try to install SQL Server
2000 on another box later on and test it this way, but I recommend getting a
support engineer (PSS) to test this for you as I won't have the time to set
all this up quickly.
Regards,
Paul Ibison|||thanks for the help Paul. I will do a quick test on 2005 and see if it
works. I'll keep you posted.
"Paul Ibison" wrote:
> Ben,
> Unfortunately we only have 1 instance of SQL Server 2000 on the network and
> when I finally got this set up on 2 networked instances of SQL Server 2005
> it worked fine - I could pick up both the @.@.identity and the
> scope_identity() values. However I noticed that this is implemented
> differently and the identity property is on the subscriber in the new
> version so this is not really a decent test. I'll try to install SQL Server
> 2000 on another box later on and test it this way, but I recommend getting a
> support engineer (PSS) to test this for you as I won't have the time to set
> all this up quickly.
> Regards,
> Paul Ibison
>
>|||Paul, here's my update.
I tried sql server 2005 and Yes Scope_Identity() and @.@.identity worked as
you mentioned but it seems to work differently in 2005.
In 2000 replicated identity columns would be replicated to their base type,
in my case an int. In 2005 it seems to replicate it as an identity column,
which I guess is the reason why the Scope_Identity and @.@.identity work. But
in 2005 it seems like it defaults to Automatic Range Management, but in 2000
I was able to get the identities updating sequentially with no Automatic
Range Management required. Is this feature gone in 2005? I would prefer to
have the immediate updating subscribers get the next identity from the
publisher so that the subscriber and publisher use the same identity
"manager" to get the next identity value (i.e. no identity ranges and no
gaps).
I'm going to do some more testing and keep you up to date.
"Ben Lam" wrote:
> thanks for the help Paul. I will do a quick test on 2005 and see if it
> works. I'll keep you posted.
> "Paul Ibison" wrote:
> > Ben,
> > Unfortunately we only have 1 instance of SQL Server 2000 on the network and
> > when I finally got this set up on 2 networked instances of SQL Server 2005
> > it worked fine - I could pick up both the @.@.identity and the
> > scope_identity() values. However I noticed that this is implemented
> > differently and the identity property is on the subscriber in the new
> > version so this is not really a decent test. I'll try to install SQL Server
> > 2000 on another box later on and test it this way, but I recommend getting a
> > support engineer (PSS) to test this for you as I won't have the time to set
> > all this up quickly.
> > Regards,
> > Paul Ibison
> >
> >
> >

Does It Matter...

MSSQl 2000
When creating a select statement with joins... does it matter where you plac
e additional where-clause criteria.
Considering the two examples below, is it more efficient to place additional
filtering criteria within the join section.? Does it weed out extra rows be
fore joining them? or should I put anything that is not pertinent to the joi
n itself down below in its own where clause?
EXAMPLE 1
SELECT
A.column_1
B.column_2
C.column_3
FROM
table_a A
inner join table_b B ON B.column_1 = A.column_1 and
B.column_2 = 1 and
B.column_3 = 'boys' and
B.column_4 between '01/01/06' and '01/31/06'
inner join table_c C ON C.column_2 = B.column_2
EXAMPLE 2
SELECT
A.column_1
B.column_2
C.column_3
FROM
table_a A
inner join table_b B ON B.column_1 = A.column_1 and
inner join table_c C ON C.column_2 = B.column_2
WHERE
B.column_2 = 1 and
B.column_3 = 'boys' and
B.column_4 between '01/01/06' and '01/31/06'For INNER JOINs, I like to put the JOIN criteria in the ON clause and the
filtering criteria in the WHERE clause. This makes it very clear to anyone
who inherits the code (or myself, when I go senile) which criteria are for
the relationship and which criteria are meant to limit the end result.
For OUTER JOINs, it can certainly matter, but it depends on your desired
result. You may exclude rows by moving criteria from ON to WHERE or vice
versa. I don't know of any situations in INNER JOIN where this is true, but
I bet Itzik or Steve will reproduce one if it exists.
"rmg66" <rgwathney__xXx__primepro.com> wrote in message
news:u8ljCwnfGHA.764@.TK2MSFTNGP03.phx.gbl...
MSSQl 2000
When creating a select statement with joins... does it matter where you
place additional where-clause criteria.
Considering the two examples below, is it more efficient to place additional
filtering criteria within the join section.? Does it weed out extra rows
before joining them? or should I put anything that is not pertinent to the
join itself down below in its own where clause?
EXAMPLE 1
SELECT
A.column_1
B.column_2
C.column_3
FROM
table_a A
inner join table_b B ON B.column_1 = A.column_1 and
B.column_2 = 1 and
B.column_3 = 'boys' and
B.column_4 between '01/01/06' and '01/31/06'
inner join table_c C ON C.column_2 = B.column_2
EXAMPLE 2
SELECT
A.column_1
B.column_2
C.column_3
FROM
table_a A
inner join table_b B ON B.column_1 = A.column_1 and
inner join table_c C ON C.column_2 = B.column_2
WHERE
B.column_2 = 1 and
B.column_3 = 'boys' and
B.column_4 between '01/01/06' and '01/31/06'|||Pre SQL 2000 SP4 I would say "Yes" it does matter.
Post SP4 I would say "No" it doesn't matter.
SP4 has given some huge performance gains at my site.
However, try it for yourself. Use Profiler / view the Execution plan etc.
To establish your version use SELECT @.@.VERSION.
http://www.aspfaq.com/SQL2000Builds.asp
--
HTH. Ryan
"rmg66" <rgwathney__xXx__primepro.com> wrote in message news:u8ljCwnfGHA.764
@.TK2MSFTNGP03.phx.gbl...
MSSQl 2000
When creating a select statement with joins... does it matter where you plac
e additional where-clause criteria.
Considering the two examples below, is it more efficient to place additional
filtering criteria within the join section.? Does it weed out extra rows be
fore joining them? or should I put anything that is not pertinent to the joi
n itself down below in its own where clause?
EXAMPLE 1
SELECT
A.column_1
B.column_2
C.column_3
FROM
table_a A
inner join table_b B ON B.column_1 = A.column_1 and
B.column_2 = 1 and
B.column_3 = 'boys' and
B.column_4 between '01/01/06' and '01/31/06'
inner join table_c C ON C.column_2 = B.column_2
EXAMPLE 2
SELECT
A.column_1
B.column_2
C.column_3
FROM
table_a A
inner join table_b B ON B.column_1 = A.column_1 and
inner join table_c C ON C.column_2 = B.column_2
WHERE
B.column_2 = 1 and
B.column_3 = 'boys' and
B.column_4 between '01/01/06' and '01/31/06'|||Thanks Aaron,
Actually I'm more concerned with performance at this point.
Any thoughts on that...
Robert
"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message news:OUZNW4nfGH
A.2032@.TK2MSFTNGP02.phx.gbl...
> For INNER JOINs, I like to put the JOIN criteria in the ON clause and the
filtering criteria in the WHERE clause. This makes it
> very clear to anyone who inherits the code (or myself, when I go senile) w
hich criteria are for the relationship and which
> criteria are meant to limit the end result.
> For OUTER JOINs, it can certainly matter, but it depends on your desired r
esult. You may exclude rows by moving criteria from ON
> to WHERE or vice versa. I don't know of any situations in INNER JOIN wher
e this is true, but I bet Itzik or Steve will reproduce
> one if it exists.
>
>
> "rmg66" <rgwathney__xXx__primepro.com> wrote in message news:u8ljCwnfGHA.7
64@.TK2MSFTNGP03.phx.gbl...
> MSSQl 2000
> When creating a select statement with joins... does it matter where you pl
ace additional where-clause criteria.
> Considering the two examples below, is it more efficient to place addition
al filtering criteria within the join section.? Does it
> weed out extra rows before joining them? or should I put anything that is
not pertinent to the join itself down below in its own
> where clause?
> EXAMPLE 1
> SELECT
> A.column_1
> B.column_2
> C.column_3
> FROM
> table_a A
> inner join table_b B ON B.column_1 = A.column_1 and
> B.column_2 = 1 and
> B.column_3 = 'boys' and
> B.column_4 between '01/01/06' and '01/31/06'
> inner join table_c C ON C.column_2 = B.column_2
>
> EXAMPLE 2
> SELECT
> A.column_1
> B.column_2
> C.column_3
> FROM
> table_a A
> inner join table_b B ON B.column_1 = A.column_1 and
> inner join table_c C ON C.column_2 = B.column_2
> WHERE
> B.column_2 = 1 and
> B.column_3 = 'boys' and
> B.column_4 between '01/01/06' and '01/31/06'
>
>
>
>|||I think as a rule, the optimizer will do the same thing regardless, although
as Ryan pointed out pre SQL 200 SP 4 it makes a difference. When optimizing
the engine will check only so many possible paths before determining which
one to use, so on larger more complex queries the order of the joins and
criteria can determine which paths get evaluated before it gives up and
chooses one.
I think the bottom line is theoretically it doesn't matter, but the only way
to be totally certain is to test it out both ways. Not just the location of
the criteria, but the order of the tables as well. For simpler queries with
a handful of joins and filter criteria, when the optimizer can afford to
calculate every possibility, it should work the same. If you have dozens of
tables and just as many filters involved, it is worth playing with different
scenarios to see if it makes a difference. With more complex queries the
optimizer can find literally billions of possible execution plans, and
influencing it to look at the right ones can be hit or miss.
"rmg66" <rgwathney__xXx__primepro.com> wrote in message
news:O1pbODofGHA.4776@.TK2MSFTNGP05.phx.gbl...
> Thanks Aaron,
> Actually I'm more concerned with performance at this point.
> Any thoughts on that...
> Robert
>
> "Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in
message news:OUZNW4nfGHA.2032@.TK2MSFTNGP02.phx.gbl...
the filtering criteria in the WHERE clause. This makes it
which criteria are for the relationship and which
result. You may exclude rows by moving criteria from ON
where this is true, but I bet Itzik or Steve will reproduce
news:u8ljCwnfGHA.764@.TK2MSFTNGP03.phx.gbl...
place additional where-clause criteria.
additional filtering criteria within the join section.? Does it
is not pertinent to the join itself down below in its own
'01/31/06'
>|||I just did one of the MS Courses last w which covered this. Whilst the
reality seems to be what the others have said. MS considers is more correct
to place the items on the join itself, as this will help SQL to choose the
best execution plan. The idea is that placing more items on the join, means
that the selected table will return less results, before the Join is
executed.
The blurb says that the Join syntax is evaluated before the Where syntax.
The course is the optimising and tuning course for SQL 2005!
My personal view is to use the Join in preference to the Where clause, I
find that it helps to make the syntax clearer and easier to understand.
Regards
Colin Dawson
www.cjdawson.com
"Jim Underwood" <james.underwoodATfallonclinic.com> wrote in message
news:eqcg3SofGHA.2208@.TK2MSFTNGP05.phx.gbl...
>I think as a rule, the optimizer will do the same thing regardless,
>although
> as Ryan pointed out pre SQL 200 SP 4 it makes a difference. When
> optimizing
> the engine will check only so many possible paths before determining which
> one to use, so on larger more complex queries the order of the joins and
> criteria can determine which paths get evaluated before it gives up and
> chooses one.
> I think the bottom line is theoretically it doesn't matter, but the only
> way
> to be totally certain is to test it out both ways. Not just the location
> of
> the criteria, but the order of the tables as well. For simpler queries
> with
> a handful of joins and filter criteria, when the optimizer can afford to
> calculate every possibility, it should work the same. If you have dozens
> of
> tables and just as many filters involved, it is worth playing with
> different
> scenarios to see if it makes a difference. With more complex queries the
> optimizer can find literally billions of possible execution plans, and
> influencing it to look at the right ones can be hit or miss.
> "rmg66" <rgwathney__xXx__primepro.com> wrote in message
> news:O1pbODofGHA.4776@.TK2MSFTNGP05.phx.gbl...
> message news:OUZNW4nfGHA.2032@.TK2MSFTNGP02.phx.gbl...
> the filtering criteria in the WHERE clause. This makes it
> which criteria are for the relationship and which
> result. You may exclude rows by moving criteria from ON
> where this is true, but I bet Itzik or Steve will reproduce
> news:u8ljCwnfGHA.764@.TK2MSFTNGP03.phx.gbl...
> place additional where-clause criteria.
> additional filtering criteria within the join section.? Does it
> is not pertinent to the join itself down below in its own
> '01/31/06'
>|||> The blurb says that the Join syntax is evaluated before the Where syntax.
That is only the logical order. For inner joins, it doesn't matter, and I do
n't even think that the
optimizer know what join type you expressed (the query is transformed into a
tree structure before
the optimizer gets hold of it). The optimizer is free to transform the query
in any way as long as
it returns the same information as if it executed the query as per the rules
for the logical order.

> The course is the optimizing and tuning course for SQL 2005!
Interesting. Which one? There are two such courses, one for "admins" and one
for "developers". Also,
can you point to the module and perhaps even page number and I'll have a loo
k at how they phrase it.

> My personal view is to use the Join in preference to the Where clause, I f
ind that it helps to
> make the syntax clearer and easier to understand.
I absolutely agree.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Colin Dawson" <newsgroups@.cjdawson.com> wrote in message
news:C1Icg.74278$wl.24621@.text.news.blueyonder.co.uk...
>I just did one of the MS Courses last w which covered this. Whilst the
reality seems to be what
>the others have said. MS considers is more correct to place the items on t
he join itself, as this
>will help SQL to choose the best execution plan. The idea is that placing
more items on the join,
>means that the selected table will return less results, before the Join is
executed.
> The blurb says that the Join syntax is evaluated before the Where syntax.
The course is the
> optimising and tuning course for SQL 2005!
> My personal view is to use the Join in preference to the Where clause, I f
ind that it helps to
> make the syntax clearer and easier to understand.
> Regards
> Colin Dawson
> www.cjdawson.com
>
> "Jim Underwood" <james.underwoodATfallonclinic.com> wrote in message
> news:eqcg3SofGHA.2208@.TK2MSFTNGP05.phx.gbl...
>|||Hello Tibor
The course is 2784A: Tuning and Optimising Database Queries User Microsoft
SQL Server 2005
The bit that I was referring two use in Unit 3. Specifically the Query
logical flow diagram on page 2.
Basically it shows the flow as
From & Join --> Where --> Select -- > .... (lots more stuff)
From experience I do agree that it doesn't seem to matter as the query
optimiser does make changes to the query as typed, into how it wants to
produce the results.
Regards
Colin Dawson
www.cjdawson.com
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:OHntJPpfGHA.4304@.TK2MSFTNGP05.phx.gbl...
> That is only the logical order. For inner joins, it doesn't matter, and I
> don't even think that the optimizer know what join type you expressed (the
> query is transformed into a tree structure before the optimizer gets hold
> of it). The optimizer is free to transform the query in any way as long as
> it returns the same information as if it executed the query as per the
> rules for the logical order.
>
> Interesting. Which one? There are two such courses, one for "admins" and
> one for "developers". Also, can you point to the module and perhaps even
> page number and I'll have a look at how they phrase it.
>
> I absolutely agree.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Colin Dawson" <newsgroups@.cjdawson.com> wrote in message
> news:C1Icg.74278$wl.24621@.text.news.blueyonder.co.uk...
>|||My personal preference is to use the primary key/foreign key relation
columns in the join clause, and all other predicates in the where
clause. This is a very consistent syntax that underscores the table
relations and automatically moves all filters to the where clause.
As mentioned before it is a different story for outer joins...
Gert-Jan
Tibor Karaszi wrote:
>
> That is only the logical order. For inner joins, it doesn't matter, and I
don't even think that the
> optimizer know what join type you expressed (the query is transformed into
a tree structure before
> the optimizer gets hold of it). The optimizer is free to transform the que
ry in any way as long as
> it returns the same information as if it executed the query as per the rul
es for the logical order.
>
> Interesting. Which one? There are two such courses, one for "admins" and o
ne for "developers". Also,
> can you point to the module and perhaps even page number and I'll have a l
ook at how they phrase it.
>
> I absolutely agree.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> "Colin Dawson" <newsgroups@.cjdawson.com> wrote in message
> news:C1Icg.74278$wl.24621@.text.news.blueyonder.co.uk...|||Colin,

> The course is 2784A: Tuning and Optimising Database Queries User Microsoft
SQL Server 2005
> The bit that I was referring two use in Unit 3. Specifically the Query log
ical flow diagram on
> page 2.
Thanks. I had a quick look through the courses after I posted prior reply, a
nd I guessed this was
the one. The important part here is that it is the *logical* flow. Quote fro
m the same page:
"
Note that
although there is a guaranteed logical order, this is not true of the actual
physical order. The
query
processor can process the query in a different order but still ensure the sa
me results, if it can
find a
more efficient method for doing so.
"
If the optimizer had to respect the logical flow, then almost every query wo
uld give us horrendous
performance:
FROM, grab all columns, and even cross join if old style join syntax
WHERE remove the rows that doesn't satisfies the conditions (including the j
oin if old-style join)
GROUP BY
HAVING
SELECT, until now we had all the columns from all the tables
ORDER BY, not until now could we sort the rows
TOP, ouch, all rows had to be sorted until we throw away all but "top n".
:-)
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Colin Dawson" <newsgroups@.cjdawson.com> wrote in message
news:VsIcg.74297$wl.32163@.text.news.blueyonder.co.uk...
> Hello Tibor
> The course is 2784A: Tuning and Optimising Database Queries User Microsoft
SQL Server 2005
> The bit that I was referring two use in Unit 3. Specifically the Query log
ical flow diagram on
> page 2.
> Basically it shows the flow as
> From & Join --> Where --> Select -- > .... (lots more stuff)
>
> From experience I do agree that it doesn't seem to matter as the query opt
imiser does make changes
> to the query as typed, into how it wants to produce the results.
> Regards
> Colin Dawson
> www.cjdawson.com
>
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote i
n message
> news:OHntJPpfGHA.4304@.TK2MSFTNGP05.phx.gbl...
>

Does increasing indexes inclease the amount of memory that SQL server uses?

I was surprised by the above statement as I had assumed that SQL Server
actually performed better if indexes were used on those colums in tables
that are a part of a join, where, or aggregate?
I also assumed that Sql Server would use these indexes which actually
translated to better performance and memory utilization as the index does
not need to be created from scratch and discarded for serving a query?
Am I wrong?
Thanks
SanjayHello Sanjay Pais" spaisatnospammarketlinksolutions.com,
The short answer is no. Since SQL never frees memory unless it has to you
aren't going to be using any more with or without the indexes. You may even
use less if it means you don't have to load the leaf pages of the data and
can instead use the index.
You can also answer this yes if it means that by adding the index to memory
it leaves less space for other things to be put in memory. But this is a
bad argument as if you can use the index correctly it will far outweight
any amount of increased memory.
Aaron Weiker
http://aaronweiker.com/
http://sqlprogrammer.org/

> I was surprised by the above statement as I had assumed that SQL
> Server actually performed better if indexes were used on those colums
> in tables that are a part of a join, where, or aggregate?
> I also assumed that Sql Server would use these indexes which actually
> translated to better performance and memory utilization as the index
> does not need to be created from scratch and discarded for serving a
> query?
> Am I wrong?
> Thanks
> Sanjay
>|||Hee hee.
I knew this would be a yes AND no answer, but I couldn't think of a good way
to say it.
I thin a better question is do indexes allow SQL Server to more efficiently
utilize memory? Properly designed, yes. Badly designed, no.
Darn. Back to yes AND no!
Bob Castleman
DBA Poseur
"Aaron Weiker" <aaron@.sqlprogrammer.org> wrote in message
news:150578632423353497158125@.news.microsoft.com...
> Hello Sanjay Pais" spaisatnospammarketlinksolutions.com,
> The short answer is no. Since SQL never frees memory unless it has to you
> aren't going to be using any more with or without the indexes. You may
> even use less if it means you don't have to load the leaf pages of the
> data and can instead use the index.
> You can also answer this yes if it means that by adding the index to
> memory it leaves less space for other things to be put in memory. But this
> is a bad argument as if you can use the index correctly it will far
> outweight any amount of increased memory.
> --
> Aaron Weiker
> http://aaronweiker.com/
> http://sqlprogrammer.org/
>
>|||Hello Bob,
Think we should bring up the added disk I/O because of the index that needs
to stay in sync?
So anyway. If you wrecklessly add indexes you could significantly increase
the time that it takes to insert/update a record because not only does it
have to modify the leaf table page but it also has to go through and update
all indexes that this row is involved in if the field has changed. This coul
d
then lead to additional page splits.
However if you follow proper indexing guidelines you shouldn't have a proble
m
with this.
Aaron Weiker
http://aaronweiker.com/
http://sqlprogrammer.org/
> Hee hee.
> I knew this would be a yes AND no answer, but I couldn't think of a
> good way to say it.
> I thin a better question is do indexes allow SQL Server to more
> efficiently utilize memory? Properly designed, yes. Badly designed,
> no.
> Darn. Back to yes AND no!
> Bob Castleman
> DBA Poseur
> "Aaron Weiker" <aaron@.sqlprogrammer.org> wrote in message
> news:150578632423353497158125@.news.microsoft.com...
>

Does all of WHERE clause get executed?

Hi there.
I have an update statement that wishes to update certain fields depending on
a column that may or may not contain numeric values (the field is varchar bu
t
may contain '001', '002' etc. For the WHERE clause, I need to convert the
MaybeNumeric field into an integer BUT... I know the whole update statement
will fail if the value is non-numeric (due to the convert(integer,...)
criteria).
The solution I've chosen is to have IsNumeric(MaybeNumeric)=1 as the first
criteria in the WHERE clause - thinking that this will get executed first an
d
will exclude non-numeric rows.
This seems to work ok on my server (those famous words!) but I've seen cases
on another server where the update statement still fails due to non-numeric
values.
Q: Is there some server setting that would dictate whether all criteria in a
WHERE clause will get executed? Or in which order they get executed? I.e: Is
the following code reliable:
update
MyTable
set
MyTable.SomeField = OtherTable.SomeField
from
OtherTable
where
IsNumeric(MyTable.MaybeNumeric) = 1
and
OtherTable.DefinitelyNumeric = convert(integer, MyTable.MaybeNumeric)
*note: this is a simplified version of the statement - I do need the convert
!
I just want to know whether this is down to my code or is I should look for
some other issue.
Any help would be appreciated!IsNumeric will return 1 for some strings that can't be converted to an
integer, but can be converted to other numeric datatypes, like '1E10', which
can be converted to a float, or '200,000.00', which can be converted to
money.
http://www.aspfaq.com/show.asp?id=2390 gives you a workaround.
Jacco Schalkwijk
SQL Server MVP
"len" <len@.discussions.microsoft.com> wrote in message
news:01E35557-6ED2-4824-A721-F4F00A9B820F@.microsoft.com...
> Hi there.
> I have an update statement that wishes to update certain fields depending
> on
> a column that may or may not contain numeric values (the field is varchar
> but
> may contain '001', '002' etc. For the WHERE clause, I need to convert the
> MaybeNumeric field into an integer BUT... I know the whole update
> statement
> will fail if the value is non-numeric (due to the convert(integer,...)
> criteria).
> The solution I've chosen is to have IsNumeric(MaybeNumeric)=1 as the first
> criteria in the WHERE clause - thinking that this will get executed first
> and
> will exclude non-numeric rows.
> This seems to work ok on my server (those famous words!) but I've seen
> cases
> on another server where the update statement still fails due to
> non-numeric
> values.
> Q: Is there some server setting that would dictate whether all criteria in
> a
> WHERE clause will get executed? Or in which order they get executed? I.e:
> Is
> the following code reliable:
>
> update
> MyTable
> set
> MyTable.SomeField = OtherTable.SomeField
> from
> OtherTable
> where
> IsNumeric(MyTable.MaybeNumeric) = 1
> and
> OtherTable.DefinitelyNumeric = convert(integer, MyTable.MaybeNumeric)
> *note: this is a simplified version of the statement - I do need the
> convert!
> I just want to know whether this is down to my code or is I should look
> for
> some other issue.
> Any help would be appreciated!|||Hi
The query processor can choose to execute a query in any way it pleases.
Depending on statistics, indexes, processors and RAM, it might decide to
execute a different query plan.
Have you tried:
update
MyTable
set
MyTable.SomeField = OtherTable.SomeField
FROM
MyTable
INNER JOIN
OtherTable
ON
OtherTable.DefinitelyNumeric = convert(integer, MyTable.MaybeNumeric)
WHERE
IsNumeric(MyTable.MaybeNumeric) = 1
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"len" wrote:

> Hi there.
> I have an update statement that wishes to update certain fields depending
on
> a column that may or may not contain numeric values (the field is varchar
but
> may contain '001', '002' etc. For the WHERE clause, I need to convert the
> MaybeNumeric field into an integer BUT... I know the whole update statemen
t
> will fail if the value is non-numeric (due to the convert(integer,...)
> criteria).
> The solution I've chosen is to have IsNumeric(MaybeNumeric)=1 as the first
> criteria in the WHERE clause - thinking that this will get executed first
and
> will exclude non-numeric rows.
> This seems to work ok on my server (those famous words!) but I've seen cas
es
> on another server where the update statement still fails due to non-numeri
c
> values.
> Q: Is there some server setting that would dictate whether all criteria in
a
> WHERE clause will get executed? Or in which order they get executed? I.e:
Is
> the following code reliable:
>
> update
> MyTable
> set
> MyTable.SomeField = OtherTable.SomeField
> from
> OtherTable
> where
> IsNumeric(MyTable.MaybeNumeric) = 1
> and
> OtherTable.DefinitelyNumeric = convert(integer, MyTable.MaybeNumeric)
> *note: this is a simplified version of the statement - I do need the conve
rt!
> I just want to know whether this is down to my code or is I should look fo
r
> some other issue.
> Any help would be appreciated!|||Optimiser can choose whatever evaluation path it sees fit. Look for "short
circuit" in the page below and see if it helps:
http://msdn.microsoft.com/library/d...
heckitout.asp
--
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"len" <len@.discussions.microsoft.com> wrote in message
news:01E35557-6ED2-4824-A721-F4F00A9B820F@.microsoft.com...
Hi there.
I have an update statement that wishes to update certain fields depending on
a column that may or may not contain numeric values (the field is varchar
but
may contain '001', '002' etc. For the WHERE clause, I need to convert the
MaybeNumeric field into an integer BUT... I know the whole update statement
will fail if the value is non-numeric (due to the convert(integer,...)
criteria).
The solution I've chosen is to have IsNumeric(MaybeNumeric)=1 as the first
criteria in the WHERE clause - thinking that this will get executed first
and
will exclude non-numeric rows.
This seems to work ok on my server (those famous words!) but I've seen cases
on another server where the update statement still fails due to non-numeric
values.
Q: Is there some server setting that would dictate whether all criteria in a
WHERE clause will get executed? Or in which order they get executed? I.e: Is
the following code reliable:
update
MyTable
set
MyTable.SomeField = OtherTable.SomeField
from
OtherTable
where
IsNumeric(MyTable.MaybeNumeric) = 1
and
OtherTable.DefinitelyNumeric = convert(integer, MyTable.MaybeNumeric)
*note: this is a simplified version of the statement - I do need the
convert!
I just want to know whether this is down to my code or is I should look for
some other issue.
Any help would be appreciated!|||A more detailed analysis of the "short circuit" feature in SQL Server
is written in the following article (also by Itzik Ben-Gan):
http://www.windowsitpro.com/Article...?ArticleID=9148
Razvan|||>> I have an update statement that wishes to update certain fields [sic] depending on
a column that may or may not contain numeric values (the field [sic] is varchar b
ut may contain '001', '002' etc. <<
Exactly how did you get this kind of problem in the first place?
You have a huge design problem and need to change your schema, not go
hunting for kludges. One of the reasons that I beat on people about
not calling a "column" a "field" is that a field (a file processing
concept) can hold anything; it gets its meaning from being read by a
host program. A relational column has ONE AND ONLY ONE domain which
has ONE AND ONLY ONE data type. It has meaning in and of itself, it
enforces its own integrity, it does not depend on a host program.
If this data element is used for computations, then it needs to be a
numeric. If it is a tag number, then you can use character types. You
do not mix things like this in an RDBMS.
Also, you might want to stop using the unpredictable proprietary UPDATE
syntax.|||Len's requirement is common enough in an ELT data staging scenario. For
conforming external data I would typically load to an "untyped" table
(NVARCHAR throughout) first, perform the necessary validation, then
load the valid data to another staging table with the correct datatyes.
Any UPDATE against the actual data in the target database utilizes the
correctly typed table. This avoids type conversions and errors and
ensures you maximize the benefit of indexing on the two tables.
David Portas
SQL Server MVP
--|||On Wed, 25 May 2005 04:44:03 -0700, len wrote:
(snip)
>The solution I've chosen is to have IsNumeric(MaybeNumeric)=1 as the first
>criteria in the WHERE clause - thinking that this will get executed first a
nd
>will exclude non-numeric rows.
Hi len,
As others have said: there's no guarantee.
You might wish to try this one:
UPDATE MyTable
SET MyTable.SomeColumn = OtherTable.SomeColumn
FROM OtherTable
WHERE OtherTable.DefinitelyNumeric =
CASE WHEN IsNumeric(MyTable.MaybeNumeric) = 1
THEN CONVERT(integer, MyTable.MaybeNumeric)
ELSE OtherTable.DefinitelyNumeric + 1
END
But do beware the gotchas with IsNumeric (see Jacco's post).
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)

Does a trigger fire once for a set statement

Does a trigger fire once for a set statement, or for every row affected? I
presume that using a client like Access and doing a line by line manual
adjustment, would fire a trigger for each transaction, but does a set
statement (like below) fire the trigger once?
eg
Table_A (column_a, column_b) with these values:
aa, 0.00
aa, 0.00
bb, 0.00
bb, 0.00
Trigger for update, insert on Table_A
if update(column_a)
update Table_A
set
column_b = 1
If I then run a sql statement to update Table_A, will the trigger fire after
each affected row, or simply fire once for the set statement below?
update table_A
set
colum_a = cc
where column_a = 'aa'
Would the trigger update the whole table once, or would it update all of
column_b twice, because of the two aa's.?
I've googled, but not very well as I haven't hit the answer yet...Any
knowledge on this?
Many thanks.
Steve.Steve'o wrote:
> Does a trigger fire once for a set statement, or for every row affected?
The trigger is fired once for each statement. This means that if your
statement affects multiple rows it is still only executed one time.
For more information look at the "CREATE TRIGGER" documentation in Books
Online.
http://msdn.microsoft.com/library/e...reate2_7eeq.asp
Aaron Weiker
http://aaronweiker.com/
http://www.sqlprogrammer.org/|||Hi
Also, look at the virtual tables INSERTED and DELETED to see which rows are
affected by your update.
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Aaron Weiker" <aaron@.sqlprogrammer.org> wrote in message
news:O$vefqgDFHA.3648@.TK2MSFTNGP10.phx.gbl...
> Steve'o wrote:
> The trigger is fired once for each statement. This means that if your
> statement affects multiple rows it is still only executed one time.
> For more information look at the "CREATE TRIGGER" documentation in Books
> Online.
> http://msdn.microsoft.com/library/e...reate2_7eeq.asp
> --
> Aaron Weiker
> http://aaronweiker.com/
> http://www.sqlprogrammer.org/|||Many thanks, I think I may need to use a curosr then...
I have a table which stores a alpha+numeric sequence number. When a column
in another table is set to true, it fires a trigger to retrive the next
sequence number, then update the sequences table.
I kept it in a sequence table because there are several different sequences,
and this allows a bit of central control.
When updating a row at a time, this will work fine, but if I do a mass
update with a update table statement which affects many rows, the trigger
will only fire once... Hence why Im assuming a cursor is the way forward,
a
bit more reading to do ;)
Thanks for the link.
PS. Already using inserted and deleted in some triggers, just wasn't sure if
they fired for every line, thanks again to both replies.
"Aaron Weiker" wrote:

> Steve'o wrote:
> The trigger is fired once for each statement. This means that if your
> statement affects multiple rows it is still only executed one time.
> For more information look at the "CREATE TRIGGER" documentation in Books
> Online.
> http://msdn.microsoft.com/library/e...reate2_7eeq.asp
> --
> Aaron Weiker
> http://aaronweiker.com/
> http://www.sqlprogrammer.org/
>

Does a transaction automatically rollback on error?

When I write code for a multiple statements transaction do I need to check'if @.@.ERROR > 0 ' after each SELECT, INSERT, DELETE or UPDATE statement so that the'rollback tran' statement can be given, or SQL server will automatically rollback the transaction and we don't need to check for @.ERROR > 0 ?

If you start a transaction (with BEGIN TRANSACTION) you must end it with a COMMIT or ROLLBACK. So, yes, you should check the @.@.ERROR value after each pertinent statement and explicitly ROLLBACK the transaction if there is an error. I am not sure if there were any changes in this area for SQL Server 2005.|||In SQL 2005, you could do:

BEGIN TRY
.....
COMMIT
END TRY
BEGIN CATCH
.....
ROLLBACK
END CATCH

The good thing is you could put multiple SQL statementsin the TRY block. But there are some limitations too. check out books on line.

Does a cursor re-execute the select statement everytime it loops?

Does anyone know if a cursor re-executes the select statement averytime it
loops to the next record? The behavior I have seen indicates that this is
happening...
here is my code... (trying to update a work order bit field at the end of
that work orders records - the recordset contains several rows per work orde
r)
CREATE Procedure MC_WO_Task_Fail As
DECLARE
@.WOPK Int,
@.WOReason VarChar(2000),
@.Priority VarChar(25),
@.PriorityDesc VarChar(50),
@.WOAPK Int, -- WO ASSET PK
@.WOAID VarChar(100), -- WO ASSET ID
@.WOANAME VarChar(150), -- WO ASSET NAME
@.UDFB5 VarChar(1),
@.WOTAPK Int, -- WO TASK PK
@.Fail VarChar(1),
@.TaskNo Int,
@.TA VarChar(7000), -- TASK ACTION
@.Reason VarChar(2000),
@.TCom VarChar(7000), -- TASK COMMENTS
@.RCPK Int, -- REPAIR CENTER PK
@.RCID VarChar(25), -- REPAIR CENTER ID
@.RCName VarChar(50), -- REPAIR CENTER NAME
@.PREVWOPK Int
DECLARE WOFAILTASK CURSOR FAST_FORWARD FOR
SELECT wo.WOPK, wo.Reason, wo.Priority, wo.PriorityDesc, wo.AssetPK,
wo.AssetID, wo.AssetName,
wo.UDFBit5, wo.RepairCenterPK, wo.RepairCenterID, wo.RepairCenterName,
wot.AssetPK, wot.Fail,
wot.TaskNo, wot.TaskAction, wot.Comments
FROM WO wo
LEFT JOIN WOTask AS wot ON wot.WOPK = wo.WOPK
WHERE IsOpen = 0
AND Fail = 1
AND (UDFBit5 = 0 OR UDFBit5 IS NULL)
ORDER BY wo.WOPK
FOR READ ONLY
OPEN WOFAILTASK
FETCH NEXT FROM WOFAILTASK INTO @.WOPK, @.WOReason, @.Priority, @.PriorityDesc,
@.WOAPK, @.WOAID, @.WOANAME, @.UDFB5, @.RCPK, @.RCID, @.RCName, @.WOTAPK, @.Fail,
@.TaskNo, @.TA, @.TCom
WHILE @.@.FETCH_STATUS = 0
BEGIN
IF (@.TCom Is Null) OR (@.TCom = '') -- Task Comments
SELECT @.TCom = 'None'
SELECT @.Reason =
'WO Task Failure on WO #' + CAST(@.WOPK AS VarChar) + ' (' + @.WOReason + ' -
' + @.WOANAME + ')' + char(13) + char(10) +
'Task #' + CAST(@.TaskNo AS VarChar) + ' - Task Action: ' + LEFT(@.TA,50) + '
Comments: ' + RTRIM(@.TCom)
IF @.WOTAPK IS NULL
BEGIN
INSERT INTO WO (Reason, Status, StatusDesc, AuthStatus, AuthStatusDesc,
AssetPK, AssetID, AssetName, TargetDate, Priority, PriorityDesc, Type,
TypeDesc, RepairCenterPK, RepairCenterID, RepairCenterName, FollowupFromWOPK
)
VALUES (@.Reason, 'REQUESTED', 'Requested', 'NOTREQUIRED','(Not
Required)', @.WOAPK, @.WOAID, @.WOANAME, GetDate(), @.Priority, @.PriorityDesc,
'FU', 'Follow-up', @.RCPK, @.RCID, @.RCName, @.WOPK)
--UPDATE WO SET UDFBit5 = 1 WHERE WOPK = @.WOPK
END
ELSE
BEGIN
DECLARE @.WOTAID VarChar(100), @.WOTANAME VarChar(150)
SELECT @.WOTAID = AssetID FROM Asset WHERE AssetPK = @.WOTAPK
SELECT @.WOTANAME = AssetName FROM Asset WHERE AssetPK = @.WOTAPK
INSERT INTO WO (Reason, Status, StatusDesc, AuthStatus, AuthStatusDesc,
AssetPK, AssetID, AssetName, TargetDate, Priority, PriorityDesc, Type,
TypeDesc, RepairCenterPK, RepairCenterID, RepairCenterName, FollowupFromWOPK
)
VALUES (@.Reason, 'REQUESTED', 'Requested', 'NOTREQUIRED','(Not
Required)', @.WOtAPK, @.WOtAID, @.WOtANAME, GetDate(), @.Priority, @.PriorityDesc
,
'FU', 'Follow-up', @.RCPK, @.RCID, @.RCName, @.WOPK)
--UPDATE WO SET UDFBit5 = 1 WHERE WOPK = @.WOPK
END
SELECT @.PREVWOPK = @.WOPK
FETCH NEXT FROM WOFAILTASK INTO @.WOPK, @.WOReason, @.Priority,
@.PriorityDesc, @.WOAPK, @.WOAID, @.WOANAME, @.UDFB5, @.RCPK, @.RCID, @.RCName,
@.WOTAPK, @.Fail, @.TaskNo, @.TA, @.TCom
IF @.PREVWOPK <> @.WOPK
UPDATE WO SET UDFBIT5 = 1 WHERE WOPK = @.PREVWOPK
END
CLOSE WOFAILTASK
DEALLOCATE WOFAILTASK
GOI don't believe so. I know cursors are terribly inefficient compared to set
operations, but why do you think it is executing the select every time it
loops through?
"Randy" <Randy@.discussions.microsoft.com> wrote in message
news:8996A83E-4648-45A7-986E-88F2C5211CC5@.microsoft.com...
> Does anyone know if a cursor re-executes the select statement averytime it
> loops to the next record? The behavior I have seen indicates that this is
> happening...
> here is my code... (trying to update a work order bit field at the end of
> that work orders records - the recordset contains several rows per work
order)
> CREATE Procedure MC_WO_Task_Fail As
> DECLARE
> @.WOPK Int,
> @.WOReason VarChar(2000),
> @.Priority VarChar(25),
> @.PriorityDesc VarChar(50),
> @.WOAPK Int, -- WO ASSET PK
> @.WOAID VarChar(100), -- WO ASSET ID
> @.WOANAME VarChar(150), -- WO ASSET NAME
> @.UDFB5 VarChar(1),
> @.WOTAPK Int, -- WO TASK PK
> @.Fail VarChar(1),
> @.TaskNo Int,
> @.TA VarChar(7000), -- TASK ACTION
> @.Reason VarChar(2000),
> @.TCom VarChar(7000), -- TASK COMMENTS
> @.RCPK Int, -- REPAIR CENTER PK
> @.RCID VarChar(25), -- REPAIR CENTER ID
> @.RCName VarChar(50), -- REPAIR CENTER NAME
> @.PREVWOPK Int
> DECLARE WOFAILTASK CURSOR FAST_FORWARD FOR
> SELECT wo.WOPK, wo.Reason, wo.Priority, wo.PriorityDesc, wo.AssetPK,
> wo.AssetID, wo.AssetName,
> wo.UDFBit5, wo.RepairCenterPK, wo.RepairCenterID, wo.RepairCenterName,
> wot.AssetPK, wot.Fail,
> wot.TaskNo, wot.TaskAction, wot.Comments
> FROM WO wo
> LEFT JOIN WOTask AS wot ON wot.WOPK = wo.WOPK
> WHERE IsOpen = 0
> AND Fail = 1
> AND (UDFBit5 = 0 OR UDFBit5 IS NULL)
> ORDER BY wo.WOPK
> FOR READ ONLY
> OPEN WOFAILTASK
> FETCH NEXT FROM WOFAILTASK INTO @.WOPK, @.WOReason, @.Priority,
@.PriorityDesc,
> @.WOAPK, @.WOAID, @.WOANAME, @.UDFB5, @.RCPK, @.RCID, @.RCName, @.WOTAPK, @.Fail,
> @.TaskNo, @.TA, @.TCom
> WHILE @.@.FETCH_STATUS = 0
> BEGIN
> IF (@.TCom Is Null) OR (@.TCom = '') -- Task Comments
> SELECT @.TCom = 'None'
> SELECT @.Reason =
> 'WO Task Failure on WO #' + CAST(@.WOPK AS VarChar) + ' (' + @.WOReason +
' -
> ' + @.WOANAME + ')' + char(13) + char(10) +
> 'Task #' + CAST(@.TaskNo AS VarChar) + ' - Task Action: ' + LEFT(@.TA,50) +
'
> Comments: ' + RTRIM(@.TCom)
> IF @.WOTAPK IS NULL
> BEGIN
> INSERT INTO WO (Reason, Status, StatusDesc, AuthStatus, AuthStatusDesc,
> AssetPK, AssetID, AssetName, TargetDate, Priority, PriorityDesc, Type,
> TypeDesc, RepairCenterPK, RepairCenterID, RepairCenterName,
FollowupFromWOPK)
> VALUES (@.Reason, 'REQUESTED', 'Requested', 'NOTREQUIRED','(Not
> Required)', @.WOAPK, @.WOAID, @.WOANAME, GetDate(), @.Priority, @.PriorityDesc,
> 'FU', 'Follow-up', @.RCPK, @.RCID, @.RCName, @.WOPK)
> --UPDATE WO SET UDFBit5 = 1 WHERE WOPK = @.WOPK
> END
> ELSE
> BEGIN
> DECLARE @.WOTAID VarChar(100), @.WOTANAME VarChar(150)
> SELECT @.WOTAID = AssetID FROM Asset WHERE AssetPK = @.WOTAPK
> SELECT @.WOTANAME = AssetName FROM Asset WHERE AssetPK = @.WOTAPK
> INSERT INTO WO (Reason, Status, StatusDesc, AuthStatus, AuthStatusDesc,
> AssetPK, AssetID, AssetName, TargetDate, Priority, PriorityDesc, Type,
> TypeDesc, RepairCenterPK, RepairCenterID, RepairCenterName,
FollowupFromWOPK)
> VALUES (@.Reason, 'REQUESTED', 'Requested', 'NOTREQUIRED','(Not
> Required)', @.WOtAPK, @.WOtAID, @.WOtANAME, GetDate(), @.Priority,
@.PriorityDesc,
> 'FU', 'Follow-up', @.RCPK, @.RCID, @.RCName, @.WOPK)
> --UPDATE WO SET UDFBit5 = 1 WHERE WOPK = @.WOPK
> END
> SELECT @.PREVWOPK = @.WOPK
> FETCH NEXT FROM WOFAILTASK INTO @.WOPK, @.WOReason, @.Priority,
> @.PriorityDesc, @.WOAPK, @.WOAID, @.WOANAME, @.UDFB5, @.RCPK, @.RCID, @.RCName,
> @.WOTAPK, @.Fail, @.TaskNo, @.TA, @.TCom
> IF @.PREVWOPK <> @.WOPK
> UPDATE WO SET UDFBIT5 = 1 WHERE WOPK = @.PREVWOPK
> END
> CLOSE WOFAILTASK
> DEALLOCATE WOFAILTASK
> GO|||Hi Randy,
Curosrs are by default dynamic (actually KEYSET which means that updates
will be seen but not inserts) unless specified otherwise.
How it does is, it copies the key (to identify the row) data into the tempdb
and fetches from the base tables as you loop through. So the actual read fro
m
the tables happens in the loop. But what to read and where to read from woul
d
have already been stored.
If you don't want the updates to be reflected then use STATIC cursors. Then
the result set is stored in tempdb
And if you need the newly inserted rows which satisfies the where condition
to be included use DYNAMIC cursors.
Hope this helps.|||Thanks Guys. I think Omnibuzz got me to what I needed...
"Randy" wrote:

> Does anyone know if a cursor re-executes the select statement averytime it
> loops to the next record? The behavior I have seen indicates that this is
> happening...
> here is my code... (trying to update a work order bit field at the end of
> that work orders records - the recordset contains several rows per work or
der)
> CREATE Procedure MC_WO_Task_Fail As
> DECLARE
> @.WOPK Int,
> @.WOReason VarChar(2000),
> @.Priority VarChar(25),
> @.PriorityDesc VarChar(50),
> @.WOAPK Int, -- WO ASSET PK
> @.WOAID VarChar(100), -- WO ASSET ID
> @.WOANAME VarChar(150), -- WO ASSET NAME
> @.UDFB5 VarChar(1),
> @.WOTAPK Int, -- WO TASK PK
> @.Fail VarChar(1),
> @.TaskNo Int,
> @.TA VarChar(7000), -- TASK ACTION
> @.Reason VarChar(2000),
> @.TCom VarChar(7000), -- TASK COMMENTS
> @.RCPK Int, -- REPAIR CENTER PK
> @.RCID VarChar(25), -- REPAIR CENTER ID
> @.RCName VarChar(50), -- REPAIR CENTER NAME
> @.PREVWOPK Int
> DECLARE WOFAILTASK CURSOR FAST_FORWARD FOR
> SELECT wo.WOPK, wo.Reason, wo.Priority, wo.PriorityDesc, wo.AssetPK,
> wo.AssetID, wo.AssetName,
> wo.UDFBit5, wo.RepairCenterPK, wo.RepairCenterID, wo.RepairCenterName,
> wot.AssetPK, wot.Fail,
> wot.TaskNo, wot.TaskAction, wot.Comments
> FROM WO wo
> LEFT JOIN WOTask AS wot ON wot.WOPK = wo.WOPK
> WHERE IsOpen = 0
> AND Fail = 1
> AND (UDFBit5 = 0 OR UDFBit5 IS NULL)
> ORDER BY wo.WOPK
> FOR READ ONLY
> OPEN WOFAILTASK
> FETCH NEXT FROM WOFAILTASK INTO @.WOPK, @.WOReason, @.Priority, @.PriorityDesc
,
> @.WOAPK, @.WOAID, @.WOANAME, @.UDFB5, @.RCPK, @.RCID, @.RCName, @.WOTAPK, @.Fail,
> @.TaskNo, @.TA, @.TCom
> WHILE @.@.FETCH_STATUS = 0
> BEGIN
> IF (@.TCom Is Null) OR (@.TCom = '') -- Task Comments
> SELECT @.TCom = 'None'
> SELECT @.Reason =
> 'WO Task Failure on WO #' + CAST(@.WOPK AS VarChar) + ' (' + @.WOReason + '
-
> ' + @.WOANAME + ')' + char(13) + char(10) +
> 'Task #' + CAST(@.TaskNo AS VarChar) + ' - Task Action: ' + LEFT(@.TA,50) +
'
> Comments: ' + RTRIM(@.TCom)
> IF @.WOTAPK IS NULL
> BEGIN
> INSERT INTO WO (Reason, Status, StatusDesc, AuthStatus, AuthStatusDesc,
> AssetPK, AssetID, AssetName, TargetDate, Priority, PriorityDesc, Type,
> TypeDesc, RepairCenterPK, RepairCenterID, RepairCenterName, FollowupFromWO
PK)
> VALUES (@.Reason, 'REQUESTED', 'Requested', 'NOTREQUIRED','(Not
> Required)', @.WOAPK, @.WOAID, @.WOANAME, GetDate(), @.Priority, @.PriorityDesc,
> 'FU', 'Follow-up', @.RCPK, @.RCID, @.RCName, @.WOPK)
> --UPDATE WO SET UDFBit5 = 1 WHERE WOPK = @.WOPK
> END
> ELSE
> BEGIN
> DECLARE @.WOTAID VarChar(100), @.WOTANAME VarChar(150)
> SELECT @.WOTAID = AssetID FROM Asset WHERE AssetPK = @.WOTAPK
> SELECT @.WOTANAME = AssetName FROM Asset WHERE AssetPK = @.WOTAPK
> INSERT INTO WO (Reason, Status, StatusDesc, AuthStatus, AuthStatusDesc,
> AssetPK, AssetID, AssetName, TargetDate, Priority, PriorityDesc, Type,
> TypeDesc, RepairCenterPK, RepairCenterID, RepairCenterName, FollowupFromWO
PK)
> VALUES (@.Reason, 'REQUESTED', 'Requested', 'NOTREQUIRED','(Not
> Required)', @.WOtAPK, @.WOtAID, @.WOtANAME, GetDate(), @.Priority, @.PriorityDe
sc,
> 'FU', 'Follow-up', @.RCPK, @.RCID, @.RCName, @.WOPK)
> --UPDATE WO SET UDFBit5 = 1 WHERE WOPK = @.WOPK
> END
> SELECT @.PREVWOPK = @.WOPK
> FETCH NEXT FROM WOFAILTASK INTO @.WOPK, @.WOReason, @.Priority,
> @.PriorityDesc, @.WOAPK, @.WOAID, @.WOANAME, @.UDFB5, @.RCPK, @.RCID, @.RCName,
> @.WOTAPK, @.Fail, @.TaskNo, @.TA, @.TCom
> IF @.PREVWOPK <> @.WOPK
> UPDATE WO SET UDFBIT5 = 1 WHERE WOPK = @.PREVWOPK
> END
> CLOSE WOFAILTASK
> DEALLOCATE WOFAILTASK
> GO

do you know the its meaning?

In ado.net (using C#). have a statement such as "sqlCommand cmd=new sqlCommand(sqlStatement,stringConnection,sqlTransaction)". do you know the meaning of this bold parameter ? is it its tasks ? thank very much

This is transaction class which you can pass if you would like to sql in transcation, here is an example from .nET help file

The following example creates aSqlConnection and aSqlTransaction. It also demonstrates how to use theBeginTransaction,Commit, andRollback methods. The transaction is rolled back on any error.Try/Catch error handling is used to handle any errors when attempting to commit or roll back the transaction.

private static void ExecuteSqlTransaction(string connectionString){ using (SqlConnection connection = new SqlConnection(connectionString)) { connection.Open(); SqlCommand command = connection.CreateCommand(); SqlTransaction transaction; // Start a local transaction. transaction = connection.BeginTransaction("SampleTransaction"); // Must assign both transaction object and connection // to Command object for a pending local transaction command.Connection = connection; command.Transaction = transaction; try { command.CommandText = "Insert into Region (RegionID, RegionDescription) VALUES (100, 'Description')"; command.ExecuteNonQuery(); command.CommandText = "Insert into Region (RegionID, RegionDescription) VALUES (101, 'Description')"; command.ExecuteNonQuery(); // Attempt to commit the transaction. transaction.Commit(); Console.WriteLine("Both records are written to database."); } catch (Exception ex) { Console.WriteLine("Commit Exception Type: {0}", ex.GetType()); Console.WriteLine(" Message: {0}", ex.Message); // Attempt to roll back the transaction. try { transaction.Rollback(); } catch (Exception ex2) { // This catch block will handle any errors that may have occurred // on the server that would cause the rollback to fail, such as // a closed connection. Console.WriteLine("Rollback Exception Type: {0}", ex2.GetType()); Console.WriteLine(" Message: {0}", ex2.Message); } } }}

Do While Skip in Selecting...

Hello:

I have one table and it contains a column named ID Number, and a column named Date. I have a Do While statement that runs a SQL select statement a few times based on the number of records with the same ID Number. During the Do While statement the information is copied into another table and deleted from the old table. After I look at the results, I see that at the second Do While loop, the data was not selected and the Select statement did not run... so the old variable value from varValue is used again... Any reasons on why?

Here is a code snippet of what is going on:

 Do While varCount < varRecordCount
 conSqlConnect.Open()
 cmdSelect = New SqlCommand ("Select * From temp_records_1 where [id number]=@.idnumber and date<@.date", conSqlConnect)
 cmdSelect.Parameters.Add( "@.accountnumber", "10000" )
 cmdSelect.Parameters.Add( "@.date", dtnow )
 dtrdatareader = cmdSelect.ExecuteReader()
 While dtrdatareader.Read()
 If IsDbNull(dtrdatareader("value")) = false Then
 varValue = dtrdatareader("value")
 End If
 End While
 dtrdatareader.Close()
 conSqlConnect.Close()
'#####The information above is copied to another table here
'#####The record where the information was received is deleted.
 varCount = varCount + 1
 Loop

Any ideas?After playing around with this for a while, I found that the select statement is incorrect. The part where it says date<@.date... this selects more than one row, instead of just selecting one row.

I read articles that discuss using select MAX(column) but that will only return one column... how can I select a row based on a column with the maximum value?|||ok... great got it working now... just post the answer here for future reference... I went ahead and did Select Top 1 * instead of Select *... it solved everything...