Sql Public Role: March 2012

Friday, March 30, 2012

Q Statistics & Query Performance

In the last couple of w

s, my SQL server has been occasionally
bogging down during the day. I noticed that it was when a stored
procedure that usually takes 2 seconds to run timed out.
I ran sp_updatestats in Query Analyzer and everything went back to
normal.
AUTO_UPDATE_STATISTICS is set to ON in the database
As part of my database maintenance, I do the following:
- Update Statistics at 05:30am on w

days
- Perform a complete backup nightly at 03:00am
- Perform a transaction log backup every half an hour between 9am and
7pm on w

days
My first hunch is that I happened to be running the sp when the logs
were being backed up. However, the logs take less than a couple of
seconds to back up. Shouldn't things go back to normal after the
back-up is done?
The load on the server is pretty low, at most 10 users hitting the web
app front end at one time.
Any suggestions or tips? Thank You(george.durzi@.gmail.com) writes:
> In the last couple of w

s, my SQL server has been occasionally
> bogging down during the day. I noticed that it was when a stored
> procedure that usually takes 2 seconds to run timed out.
> I ran sp_updatestats in Query Analyzer and everything went back to
> normal.
> AUTO_UPDATE_STATISTICS is set to ON in the database
> As part of my database maintenance, I do the following:
> - Update Statistics at 05:30am on w

days
> - Perform a complete backup nightly at 03:00am
> - Perform a transaction log backup every half an hour between 9am and
> 7pm on w

days
> My first hunch is that I happened to be running the sp when the logs
> were being backed up. However, the logs take less than a couple of
> seconds to back up. Shouldn't things go back to normal after the
> back-up is done?
> The load on the server is pretty low, at most 10 users hitting the web
> app front end at one time.
It's difficult to say with this little amount of information. But I
would guess that parameter sniffing is part of the plot. When SQL Server
builds the query plan for a stored procedure, it looks at the parameter
values, and uses these as guidance when building the plan. This means
that if the procedure is initially called with some odd value, you
may be stuck with a plan that is not good for regular values. Here
is a brief example:
CREATE PROCEDURE get_data @.last_key int = 0 AS
IF @.last_key = 0
SELECT ... FROM tbl
ELSE
SELECT ... FRON tbl WHERE keycol > @.last-key
Assume that this procedure is called in the morning to do an initial
load of a screen of some sort, and is then called repeatedly during
the day too update that screen. Assume further that the index on last_key
is non-clustered. If there is no plan cached in the morning, the optimzer
will use a table scan for both cases, as the NC index is not good
for reading all values.
When you run sp_updatestats, the optimizer might notice that statistcs
have changed and recompile the procedure with the currently value,
for which the index is useful.
This is a bit of speculation on my part. You might be able to get some
more cluse, if you start to inspect query plans. You can also use
DBCC SHOW_STATISTCS before and after to see whether are any significant
changes in statistics.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Erland,
Thanks for your tip, I'll look into this further

Q on joining tables with nullable fields

Question.
I have a new table that I am adding to a script that I wrote. This
table has 3 fields, the first 2 fields are used in the on statement as
being = other fields in the script.

The first field always has data in it, but the 2nd field is sometimes
null.

So my problem is if both fields have data in them and they both match
to the data in the fields that I am linking them to, then it returns
the 3rd field without a problem. However if the 2nd field is null then
it is returning a null for the 3rd field. I have checked and the field
that I am linking to is null also.

So if I have

select t1.field1, t1.field2, t2.field1, t2.field2, t2.field3
from table1 t1
join table2 t2
on t1.field1=t2.field1 and t1.field2=t2.field2

with 2 records in each table
table1: record1: data, data
record2: data, null
table2: record1: data,data,data
record2: data,null,data

what I get from the script is
record1: data, data,data,data,data
record2: data,null,data,null,null

I would expect
record2: data,null,data,null,data

I hope this makes sense, I didn't want to post the entire actual script
as it is about 150 lines long.

Thanks in advance.mike wrote:

Quote:

Originally Posted by

I have a new table that I am adding to a script that I wrote. This
table has 3 fields, the first 2 fields are used in the on statement as
being = other fields in the script.
>
The first field always has data in it, but the 2nd field is sometimes
null.
>
So my problem is if both fields have data in them and they both match
to the data in the fields that I am linking them to, then it returns
the 3rd field without a problem. However if the 2nd field is null then
it is returning a null for the 3rd field. I have checked and the field
that I am linking to is null also.
>
So if I have
>
select t1.field1, t1.field2, t2.field1, t2.field2, t2.field3
from table1 t1
join table2 t2
on t1.field1=t2.field1 and t1.field2=t2.field2
>
with 2 records in each table
table1: record1: data, data
record2: data, null
table2: record1: data,data,data
record2: data,null,data
>
what I get from the script is
record1: data, data,data,data,data
record2: data,null,data,null,null
>
>
I would expect
record2: data,null,data,null,data

Please use sample data like 'A', 'B', 'C', etc., instead of "data" which
is much more confusing.

Run some sanity checks on the data:

select * from table1 where field2 is null
select * from table1 where field2 = 'NULL'

select * from table2 where field2 is null
select * from table2 where field2 = 'NULL'

What tool are you using to pull the data? Some clients (e.g. Crystal
Reports) have a "convert nulls to empty values" option.|||Yes using select statements like that does return the data where field2
is null.
This is just using query analyzer. This is part of a view that I am
making some changes to.
Also I have tried using the SET ANSI_NULLS ON and SET ANSI_NULLS OFF
and it made no difference.

Ed Murphy wrote:

Quote:

Originally Posted by

mike wrote:
>

Quote:

Originally Posted by

I have a new table that I am adding to a script that I wrote. This
table has 3 fields, the first 2 fields are used in the on statement as
being = other fields in the script.

The first field always has data in it, but the 2nd field is sometimes
null.

So if I have

select t1.field1, t1.field2, t2.field1, t2.field2, t2.field3
from table1 t1
join table2 t2
on t1.field1=t2.field1 and t1.field2=t2.field2

with 2 records in each table
table1: record1: data, data
record2: data, null
table2: record1: data,data,data
record2: data,null,data

what I get from the script is
record1: data, data,data,data,data
record2: data,null,data,null,null

I would expect
record2: data,null,data,null,data

>
Please use sample data like 'A', 'B', 'C', etc., instead of "data" which
is much more confusing.
>
Run some sanity checks on the data:
>
select * from table1 where field2 is null
select * from table1 where field2 = 'NULL'
>
select * from table2 where field2 is null
select * from table2 where field2 = 'NULL'
>
What tool are you using to pull the data? Some clients (e.g. Crystal
Reports) have a "convert nulls to empty values" option.

|||Oh yea, and one thing I forgot is that my join is a Left Outer, if I
make it an inner join the records with the NULL just don't show at all.
So the issue is within the linking.

mike wrote:

Quote:

Originally Posted by

Yes using select statements like that does return the data where field2
is null.
This is just using query analyzer. This is part of a view that I am
making some changes to.
Also I have tried using the SET ANSI_NULLS ON and SET ANSI_NULLS OFF
and it made no difference.
>
Ed Murphy wrote:

Quote:

Originally Posted by

mike wrote:

Quote:

Originally Posted by

Please use sample data like 'A', 'B', 'C', etc., instead of "data" which
is much more confusing.

Run some sanity checks on the data:

select * from table1 where field2 is null
select * from table1 where field2 = 'NULL'

select * from table2 where field2 is null
select * from table2 where field2 = 'NULL'

What tool are you using to pull the data? Some clients (e.g. Crystal
Reports) have a "convert nulls to empty values" option.

|||Ok I finally got it. I still don't know why that didn't work. But
here is what I did instead and it worked.
On the join I changed it to the following.

On t1.field1=t2.field1 and (case when t1.field2 is null then '' else
t1.field2 end)=(case when t2.field2 is null then '' else t2.field2 end)

mike wrote:

Quote:

Originally Posted by

Oh yea, and one thing I forgot is that my join is a Left Outer, if I
make it an inner join the records with the NULL just don't show at all.
So the issue is within the linking.
>
mike wrote:

Quote:

Originally Posted by

Ed Murphy wrote:

Quote:

Originally Posted by

mike wrote:
>
I have a new table that I am adding to a script that I wrote. This
table has 3 fields, the first 2 fields are used in the on statement as
being = other fields in the script.

The first field always has data in it, but the 2nd field is sometimes
null.

So my problem is if both fields have data in them and they both match
to the data in the fields that I am linking them to, then it returns
the 3rd field without a problem. However if the 2nd field is null then
it is returning a null for the 3rd field. I have checked and the field
that I am linking to is null also.

So if I have

select t1.field1, t1.field2, t2.field1, t2.field2, t2.field3
from table1 t1
join table2 t2
on t1.field1=t2.field1 and t1.field2=t2.field2

with 2 records in each table
table1: record1: data, data
record2: data, null
table2: record1: data,data,data
record2: data,null,data

what I get from the script is
record1: data, data,data,data,data
record2: data,null,data,null,null

I would expect
record2: data,null,data,null,data
>
Please use sample data like 'A', 'B', 'C', etc., instead of "data" which
is much more confusing.
>
Run some sanity checks on the data:
>
select * from table1 where field2 is null
select * from table1 where field2 = 'NULL'
>
select * from table2 where field2 is null
select * from table2 where field2 = 'NULL'
>
What tool are you using to pull the data? Some clients (e.g. Crystal
Reports) have a "convert nulls to empty values" option.

|||mike wrote:

Quote:

Originally Posted by

Ok I finally got it. I still don't know why that didn't work.

Nulls are not considered equal to anything, not even other nulls.

http://en.wikipedia.org/wiki/Null_(SQL)

Quote:

Originally Posted by

But here is what I did instead and it worked.
On the join I changed it to the following.
>
On t1.field1=t2.field1 and (case when t1.field2 is null then '' else
t1.field2 end)=(case when t2.field2 is null then '' else t2.field2 end)

Equivalent and shorter:

on t1.field1 = t2.field1
and coalesce(t1.field2,'') = coalesce(t2.field2,'')

coalesce() is a function that takes one or more argument and returns the
first non-null value among them, or null if they're all null.

Q on Clustered and Nonclustered Index files to be on Raid

I have SAN mapped as Raid 5 and Raid 0. I have to place Clustered Index file
(contains all clustered indexes) and Nonclustered Index file file (contains
all nonclustered indexes). Which Raid level is the best and recommended for
clustered and none cluster
ed index files to have a better performance? It is sql 2k.
Thank you,
KtfI would suggest that this is not enough information. The standard safe answ
er will be that raid 10 will give you better performance.
But if you are running a database on a EMC clarion SAN, it is possible that
you will never overflow the cache, or that the writes across 168 drives of r
aid 5 will provide excellent performance.|||Ken,
Yes it is EMC but not that many drivers. It was raid 5 and 0 already mapped
by Dell (builtin that way). I know about raid 10 but my real question is wha
t would be the best raid for clustered and noneclustered index files?
Thank you,
ktf

Q on Clustered and Nonclustered Index files to be on Raid

I have SAN mapped as Raid 5 and Raid 0. I have to place Clustered Index file (contains all clustered indexes) and Nonclustered Index file file (contains all nonclustered indexes). Which Raid level is the best and recommended for clustered and none cluster
ed index files to have a better performance? It is sql 2k.
Thank you,
Ktf
I would suggest that this is not enough information. The standard safe answer will be that raid 10 will give you better performance.
But if you are running a database on a EMC clarion SAN, it is possible that you will never overflow the cache, or that the writes across 168 drives of raid 5 will provide excellent performance.
|||Ken,
Yes it is EMC but not that many drivers. It was raid 5 and 0 already mapped by Dell (builtin that way). I know about raid 10 but my real question is what would be the best raid for clustered and noneclustered index files?
Thank you,
ktf

Q any group or freeze function

Hello,
I have a rectangle and a few textbox in it, now when I run report, the
textbox goes out of rectangle, Is there any way to group and freeze?Jim,
It sounds like the text box isn't actually IN the rectangle.
Select the textbox and cut it with Ctrl-X
Select the rectangle then paste with Ctrl-V
The textbox should now be inside the rectangle, try dragging it around,
it should be bound by the rectangle now.
Chris
JIM.H. wrote:
> Hello,
> I have a rectangle and a few textbox in it, now when I run report,
> the textbox goes out of rectangle, Is there any way to group and
> freeze?

Q : Does MSDE have an evaluation Period

Hi All,
Questions : I would like to know if MSDE has an evaluation period ? How long is the evaluation period ?
Thanking you in advance
Vikash
On Thu, 6 May 2004 01:36:04 -0700, vikash m tulsi wrote:

>Hi All,
>Questions : I would like to know if MSDE has an evaluation period ? How long is the evaluation period ?
>Thanking you in advance
>Vikash
Hi Vikash,
Since MSDE is free, you can use it as long as you like.
Best, Hugo
(Remove _NO_ and _SPAM_ to get my e-mail address)

Q - Adding a database file

I am looking to add datafiles to a somewhat large db (40gb). If I add the
file - how does it get populated? Or does SQL Server start adding data from
that point forward to the file without balancing it?Data is added to new files going forward. SQL does not rebalance data
within data files.
--
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
"frankm" <frankm@.nospam.postalias> wrote in message
news:usm87CAoEHA.3520@.TK2MSFTNGP11.phx.gbl...
> I am looking to add datafiles to a somewhat large db (40gb). If I add the
> file - how does it get populated? Or does SQL Server start adding data
from
> that point forward to the file without balancing it?
>|||Thanks ... that helps
"Geoff N. Hiten" <SRDBA@.Careerbuilder.com> wrote in message
news:OqkaWMAoEHA.132@.TK2MSFTNGP14.phx.gbl...
> Data is added to new files going forward. SQL does not rebalance data
> within data files.
> --
> Geoff N. Hiten
> Microsoft SQL Server MVP
> Senior Database Administrator
> Careerbuilder.com
> I support the Professional Association for SQL Server
> www.sqlpass.org
> "frankm" <frankm@.nospam.postalias> wrote in message
> news:usm87CAoEHA.3520@.TK2MSFTNGP11.phx.gbl...
> > I am looking to add datafiles to a somewhat large db (40gb). If I add
the
> > file - how does it get populated? Or does SQL Server start adding data
> from
> > that point forward to the file without balancing it?
> >
> >
>|||One more thing -
Are there any problems with doing an "add file" to a publisher or subscriber
database?
"frankm" <frankm@.nospam.postalias> wrote in message
news:emSYVPAoEHA.2684@.TK2MSFTNGP11.phx.gbl...
> Thanks ... that helps
>
> "Geoff N. Hiten" <SRDBA@.Careerbuilder.com> wrote in message
> news:OqkaWMAoEHA.132@.TK2MSFTNGP14.phx.gbl...
> > Data is added to new files going forward. SQL does not rebalance data
> > within data files.
> >
> > --
> > Geoff N. Hiten
> > Microsoft SQL Server MVP
> > Senior Database Administrator
> > Careerbuilder.com
> >
> > I support the Professional Association for SQL Server
> > www.sqlpass.org
> >
> > "frankm" <frankm@.nospam.postalias> wrote in message
> > news:usm87CAoEHA.3520@.TK2MSFTNGP11.phx.gbl...
> > > I am looking to add datafiles to a somewhat large db (40gb). If I add
> the
> > > file - how does it get populated? Or does SQL Server start adding data
> > from
> > > that point forward to the file without balancing it?
> > >
> > >
> >
> >
>|||No problems. Replication abstracts everything at the database\filegroup
level. It doesn't care whether a filegroup has 1 or 10 underlying files.
--
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
"frankm" <frankm@.nospam.postalias> wrote in message
news:ehYPgkKoEHA.3712@.TK2MSFTNGP15.phx.gbl...
> One more thing -
> Are there any problems with doing an "add file" to a publisher or
subscriber
> database?
>
> "frankm" <frankm@.nospam.postalias> wrote in message
> news:emSYVPAoEHA.2684@.TK2MSFTNGP11.phx.gbl...
> > Thanks ... that helps
> >
> >
> >
> > "Geoff N. Hiten" <SRDBA@.Careerbuilder.com> wrote in message
> > news:OqkaWMAoEHA.132@.TK2MSFTNGP14.phx.gbl...
> > > Data is added to new files going forward. SQL does not rebalance data
> > > within data files.
> > >
> > > --
> > > Geoff N. Hiten
> > > Microsoft SQL Server MVP
> > > Senior Database Administrator
> > > Careerbuilder.com
> > >
> > > I support the Professional Association for SQL Server
> > > www.sqlpass.org
> > >
> > > "frankm" <frankm@.nospam.postalias> wrote in message
> > > news:usm87CAoEHA.3520@.TK2MSFTNGP11.phx.gbl...
> > > > I am looking to add datafiles to a somewhat large db (40gb). If I
add
> > the
> > > > file - how does it get populated? Or does SQL Server start adding
data
> > > from
> > > > that point forward to the file without balancing it?
> > > >
> > > >
> > >
> > >
> >
> >
>|||Thank you sir...
"Geoff N. Hiten" <SRDBA@.Careerbuilder.com> wrote in message
news:eu650yKoEHA.1300@.TK2MSFTNGP12.phx.gbl...
> No problems. Replication abstracts everything at the database\filegroup
> level. It doesn't care whether a filegroup has 1 or 10 underlying files.
> --
> Geoff N. Hiten
> Microsoft SQL Server MVP
> Senior Database Administrator
> Careerbuilder.com
> I support the Professional Association for SQL Server
> www.sqlpass.org
> "frankm" <frankm@.nospam.postalias> wrote in message
> news:ehYPgkKoEHA.3712@.TK2MSFTNGP15.phx.gbl...
> > One more thing -
> > Are there any problems with doing an "add file" to a publisher or
> subscriber
> > database?
> >
> >
> > "frankm" <frankm@.nospam.postalias> wrote in message
> > news:emSYVPAoEHA.2684@.TK2MSFTNGP11.phx.gbl...
> > > Thanks ... that helps
> > >
> > >
> > >
> > > "Geoff N. Hiten" <SRDBA@.Careerbuilder.com> wrote in message
> > > news:OqkaWMAoEHA.132@.TK2MSFTNGP14.phx.gbl...
> > > > Data is added to new files going forward. SQL does not rebalance
data
> > > > within data files.
> > > >
> > > > --
> > > > Geoff N. Hiten
> > > > Microsoft SQL Server MVP
> > > > Senior Database Administrator
> > > > Careerbuilder.com
> > > >
> > > > I support the Professional Association for SQL Server
> > > > www.sqlpass.org
> > > >
> > > > "frankm" <frankm@.nospam.postalias> wrote in message
> > > > news:usm87CAoEHA.3520@.TK2MSFTNGP11.phx.gbl...
> > > > > I am looking to add datafiles to a somewhat large db (40gb). If I
> add
> > > the
> > > > > file - how does it get populated? Or does SQL Server start adding
> data
> > > > from
> > > > > that point forward to the file without balancing it?
> > > > >
> > > > >
> > > >
> > > >
> > >
> > >
> >
> >
>sql

Q - Adding a database file

Q - Add file to a Publisher or subscriber database

Does anyone see any problems with do an "Add File" to a publisher or
subscriber database, while replication is running?
There is no impact per se. Depending on the size of the file there may be
some impact which the file is being allocated/initialized.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
"frankm" <frankm@.nospam.postalias> wrote in message
news:uxbuu0KoEHA.2900@.TK2MSFTNGP09.phx.gbl...
> Does anyone see any problems with do an "Add File" to a publisher or
> subscriber database, while replication is running?
>

q

CREATE TABLE U(col1 INT);
INSERT INTO U VALUES(2);
INSERT INTO U VALUES(7);
INSERT INTO U VALUES(9);
CREATE TABLE V(col1 INT);
INSERT INTO V VALUES(3);
INSERT INTO V VALUES(7);
INSERT INTO V VALUES(NULL);
SELECT * FROM U WHERE
col1 NOT IN(SELECT col1 FROM V);
i expected it to return 2 and 9 but it returned nothing. can anyone explain?This is because of NULL value in the V table. Comparing to NULL, we don't
know whether the known values are equal or different, so we have to make an
agreement how to deal in such situations. In your case, you see the result.
If you want to get the values you are mentioning, change the query to
SELECT * FROM U WHERE
U.col1 NOT IN (SELECT V.col1 FROM V WHERE V.col1 IS NOT NULL);
Dejan Sarka, SQL Server MVP
Associate Mentor
www.SolidQualityLearning.com
"ichor" <ichor@.hotmail.com> wrote in message
news:OqZWh2FrFHA.2076@.TK2MSFTNGP14.phx.gbl...
> CREATE TABLE U(col1 INT);
> INSERT INTO U VALUES(2);
> INSERT INTO U VALUES(7);
> INSERT INTO U VALUES(9);
> CREATE TABLE V(col1 INT);
> INSERT INTO V VALUES(3);
> INSERT INTO V VALUES(7);
> INSERT INTO V VALUES(NULL);
>
> SELECT * FROM U WHERE
> col1 NOT IN(SELECT col1 FROM V);
> i expected it to return 2 and 9 but it returned nothing. can anyone
> explain?
>|||If you are using the NOT IN Opereator with a subquery and the subquery
contains any NULL values, the subquery will return NULL!. This can be
dangerous, and this is not the case if you use IN.
http://toponewithties.blogspot.com/...es.blogspot.com
"ichor" <ichor@.hotmail.com> wrote in message
news:OqZWh2FrFHA.2076@.TK2MSFTNGP14.phx.gbl...
> CREATE TABLE U(col1 INT);
> INSERT INTO U VALUES(2);
> INSERT INTO U VALUES(7);
> INSERT INTO U VALUES(9);
> CREATE TABLE V(col1 INT);
> INSERT INTO V VALUES(3);
> INSERT INTO V VALUES(7);
> INSERT INTO V VALUES(NULL);
>
> SELECT * FROM U WHERE
> col1 NOT IN(SELECT col1 FROM V);
> i expected it to return 2 and 9 but it returned nothing. can anyone
> explain?
>|||That exact example is given in Itzik Ben-Gan's T-SQL Black Belt column
this month in SQLMag (Don't Avoid the UNKNOWN
<http://www.windowsitpro.com/Article...47010.html?Ad=1> ).
If you want a fuller explanation, you should read his article.
*mike hodgson*
blog: http://sqlnerd.blogspot.com
Dejan Sarka wrote:

>This is because of NULL value in the V table. Comparing to NULL, we don't
>know whether the known values are equal or different, so we have to make an
>agreement how to deal in such situations. In your case, you see the result.
>If you want to get the values you are mentioning, change the query to
>SELECT * FROM U WHERE
> U.col1 NOT IN (SELECT V.col1 FROM V WHERE V.col1 IS NOT NULL);
>
>|||this is where i took the code from. i have subscribed to sqlmag but cant see
the entire article.
"Mike Hodgson" <mike.hodgson@.mallesons.nospam.com> wrote in message news:OA$
QFNGrFHA.3096@.TK2MSFTNGP15.phx.gbl...
That exact example is given in Itzik Ben-Gan's T-SQL Black Belt column this
month in SQLMag (Don't Avoid the UNKNOWN). If you want a fuller explanation
, you should read his article.
mike hodgson
blog: http://sqlnerd.blogspot.com
Dejan Sarka wrote:
This is because of NULL value in the V table. Comparing to NULL, we don't
know whether the known values are equal or different, so we have to make an
agreement how to deal in such situations. In your case, you see the result.
If you want to get the values you are mentioning, change the query to
SELECT * FROM U WHERE
U.col1 NOT IN (SELECT V.col1 FROM V WHERE V.col1 IS NOT NULL);

q

upgraded one of our servers to sql 2005 SP1 a week ago and we have been troubleshooting performance problems ever since.

so far we have narrowed it down to 2 queries.

this one spikes our 8 900MHz CPU Compaq server to 100% for 10 minutes at a time.

runs in seconds on sql 2000 on much older and slower hardware. sql 2k execution plan is all index seeks on the right indexes and sql 2005 is an index scan on the PK.

MS is saying it's by design, but i've never seen an index scan do this.

they said that they changed the optimizer to do this to avoid stack overflows at high usage.

select ordUidID,ordDtmUpdated,ordDtmCreated,ordUidParentOrder,ordCdeOrderType,ordUidCustomer,ordYnNeedsReview,ordYnCanArchive,ordCdeLEC,ordVchPON,

ordVchVersion,ordCdeTranClass,ordVchTranType,ordVchLastTran,ordTnBTN,ordTnWTN,ordSetSynchronization,ordCdeFinalResult,ordCdeStatus,ordVchFileName,

ordDtmOrdered,ordDtmClosed,ordDteDueDateRequested,ordDteDueDateAssigned,ordDteInstalled,ordIntConnectionUsed,ordVchLastMessage,ordDtmMiscDate0,

ordDtmMiscDate1,ordDtmMiscDate2,ordDtmMiscDate3,ordCdeMiscCode0,ordCdeMiscCode1,ordCdeMiscCode2,ordCdeMiscCode3,ordVchMiscData0,ordVchMiscData1,

ordVchMiscData2,ordVchMiscData3,ordVchMiscData4,ordVchMiscData5,ordVchMiscData6,ordVchMiscData7,ordVchUserID,ordCdeUserType,ordUIDIncidentID,

ordVchIncidentType,ordIntRecordUpdateCount,ordIntRecordProcessedCount,ordVchSubTrans,ordUidResellerId from tblLECOrder where ( ordTnBTN in ('000WLK0000','2122283937','2122344229','2122810515','2122818210','2122837617','2122858679','2122893175','2123162861','2123693789','2124107190','2124263225',

'2124278119','2125295465','2125343989','2125447195','2125671073','2125677808','2125678098','2125680722','2125683939','2125684463','2125687396','2125687508','

2125689745','2125689820','2126638480','2126784974','2126904871','2127228356','2127243395','2127813907','2127816515','2127817636','2127955370','2127955862',

'2127957834','2129239577','2129270685','2129273060','2129274728','2129275933','2129276137','2129276468','2129276845','2129277476','2129278126','2129279249',

'2129424067','2129873669','2129879069','2152210772','2152280240','2152321190','2152711041','2152882261','2152910690','2152919646','2153240243','2153333119',

'2154235057','2154235142','2154239175','2154239616','2154250293','2154266674','2154267635','2154268312','2154269081','2154271883','2154571323','2154572414','

2154688652','2156340253','2156342734','2157393712','2157441452','3155364195','5162920290','5163331988','5163778609','5164884993','5165055606','5165360859',

'5165381376','5165384630','5165466810','5165617488','5166239609','5168295375','5184894605','5184922523','5184991914','6102688232','6102728614','6102775286',

'6103721456','6103723790','6103724604','6103729470','6103732079','6103732752','6103735651','6103742625','6103754045','6103765831','6103766395','6104324238',

'6104325988','6104327258','6104335863','6104346703','6104372198','6104391609','6104445140','6104448194','6107749810','6108207582','6108612952','6108662412',

'6108697936','6108699214','6109250219','6109250316','6313852195','6314211097','6314231374','6314778227','6314778291','6315491328','6315493804','6315494720',

'6315498235','6316736552','6316738351','6317544591','6318594984','7168530368','7168562635','7182200643','7182201079','7182203949','7182206032','7182206124',

'7182209706','7182252502','7182353598','7182381315','7182383716','7182520828','7182560894','7182630419','7182711657','7182743419','7182770168','7182772714',

'7182774379','7182774726','7182775216','7182930531','7182931421','7182932314','7182934615','7182935547','7182936379','7182936489','7182944188','7182950607',

'7182953419','7182954264','7182955065','7182955913','7182957192','7182957980','7182967315','7183222109','7183287582','7183293615','7183299730','7183330763',

'7183493722','7183533459','7183645849','7183646794','7183647088','7183647571','7183657547','7183664851','7183672666','7183679345','7183780195','7183783144',

'7183784405','7183785086','7183786437','7183795271','7183820524','7183833545','7183860947','7183863787','7183971152','7184100414','7184102691','7184181232',

'7184184534','7184186657','7184188365','7184188485','7184189682','7184244219','7184260613','7184263054','7184297079','7184352078','7184415451','7184431673',

'7184431978','7184438072','7184452145','7184461815','7184531441','7184533981','7184534089','7184538743','7184553469','7184563259','7184563916','7184763834',

'7184865457','7184866489','7184920615','7184922149','7184971269','7184971876','7184975709','7184977375','7185058171','7185196413','7185196550','7185233417',

'7185238042','7185258323','7185263859','7185265019','7185266713','7185271890','7185280091','7185291314','7185339073','7185372582','7185378203','7185381866',

'7185382967','7185383271','7185383694','7185383794','7185385027','7185387693','7185388697','7185389418','7185392568','7185421930','7185423664','7185426571',

'7185427232','7185428637','7185428742','7185428953','7185430852','7185450174','7185470274','7185475970','7185476028','7185478451','7185491560','7185610521',

'7185611519','7185613654','7185615696','7185615863','7185617340','7185618094','7185618219','7185618486','7185621326','7185621640','7185629364','7185629692',

'7185632531','7185633531','7185733564','7185746632','7185844011','7185844746','7185844836','7185881074','7185886683','7185889899','7185890125','7185894623',

'7185896208','7185900541','7185900828','7185904091','7185904858','7185994193','7186090497','7186171937','7186172711','7186205203','7186332470','7186347884',

'7186392938','7186451907','7186472645','7186472713','7186477636','7186521715','7186522908','7186552248','7186558625','7186682396','7186683433','7186686857',

'7186689161','7186689547','7186711542','7186720486','7186721229','7186721434','7186721469','7186721952','7186722551','7186722592','7186722870','7186723055',

'7186723233','7186724169','7186724835','7186724975','7186725062','7186725799','7186725920','7186726040','7186726621','7186726740','7186726948','7186728309',

'7186728420','7186729408','7186729697','7186756401','7186756779','7186756942','7186771445','7186773330','7186777645','7186778437','7186779455','7186800316',

'7186802749','7186803348','7186803405','7186804038','7186805162','7186805387','7186805459','7186805590','7186806846','7186807136','7186808476','7186808816',

'7186810934','7186811454','7186811648','7186812053','7186812065','7186813478','7186814281','7186815360','7186815361','7186815575','7186816570','7186818268',

'7186818325','7186818403','7186819353','7186861065','7186861543','7186862969','7186866117','7186866617','7186866937','7186866947','7186867179','7186867334',

'7186920497','7186922776','7186927257','7186932954','7186937158','7186949409','7186980457','7186980920','7186980940','7186981811','7186984233','7186986257',

'7186986612','7186987684','7186990841','7186990905','7186991184','7186991329','7186992379','7186992734','7186992876','7186994389','7186995289','7186995341',

'7186997162','7186997361','7186999147','7187034172','7187067578','7187068410','7187068465','7187068471','7187141153','7187144035','7187144291','7187146328',

'7187146758','7187146810','7187149845','7187161933','7187161941','7187162663','7187165245','7187165380','7187206825','7187206887','7187207200','7187207345',

'7187207350','7187210139','7187210350','7187210458','7187210608','7187210643','7187210949','7187211269','7187211638','7187211805','7187211810','7187211904',

'7187211938','7187212099','7187212723','7187214662','7187214693','7187214974','7187215018','7187215202','7187215260','7187215726','7187216790','7187217078',

'7187217415','7187217652','7187230780','7187260513','7187261057','7187261364','7187261847','7187262398','7187262519','7187262573','7187262870','7187264887',

'7187265185','7187265424','7187265551','7187266259','7187266489','7187268008','7187271050','7187277766','7187278594','7187280271','7187280664','7187281954',

'7187282837','7187282877','7187283342','7187284730','7187285104','7187285215','7187285669','7187286478','7187287353','7187287542','7187287593','7187287825',

'7187288005','7187288372','7187288686','7187288808','7187290106','7187291778','7187293062','7187293310','7187293841','7187295612','7187295869','7187297627',

'7187299179','7187299911','7187310861','7187312426','7187313202','7187314421','7187317164','7187317694','7187331410','7187331609','7187332806','7187333146',

'7187333536','7187333765','7187334323','7187334420','7187335228','7187337393','7187337642','7187338725','7187382564','7187382750','7187383647','7187384962',

'7187386413','7187387307','7187388430','7187389395','7187393233','7187397938','7187400244','7187400271','7187401751','7187422142','7187424409','7187426079',

'7187429032','7187429353','7187430854','7187431763','7187431944','7187432321','7187432587','7187432612','7187432646','7187433542','7187433646','7187434553',

'7187435268','7187435814','7187436289','7187436603','7187436874','7187437597','7187438465','7187438527','7187439834','7187450063','7187450844','7187451504',

'7187451728','7187451821','7187453029','7187453212','7187453629','7187453861','7187453964','7187454062','7187454287','7187454354','7187454674','7187455407',

'7187455579','7187456854','7187456970','7187457167','7187457320','7187457946','7187458410','7187460042','7187460871','7187460962','7187461205','7187461252',

'7187463048','7187463553','7187464128','7187464172','7187464384','7187465007','7187465669','7187465760','7187466080','7187468291','7187469464','7187473042',

'7187475607','7187476117','7187476139','7187480944','7187481208','7187482091','7187482464','7187483068','7187483147','7187483659','7187483796','7187483952',

'7187485133','7187486602','7187489896','7187520567','7187521815','7187529750','7187568064','7187590580','7187590925','7187591230','7187599425','7187599662',

'7187600238','7187600614','7187600733','7187600849','7187600873','7187601138','7187601334','7187601527','7187601549','7187601895','7187602377','7187602868',

'7187603285','7187603550','7187603876','7187604804','7187604845','7187605728','7187607406','7187607473','7187608939','7187613931','7187614612','7187615683',

'7187615717','7187616184','7187617711','7187618557','7187620539','7187622317','7187625542','7187628146','7187633771','7187635291','7187650203','7187650597',

'7187670343','7187670370','7187671309','7187671376','7187671516','7187672036','7187672363','7187672473','7187672773','7187672896','7187673849','7187674365',

'7187676549','7187679158','7187681055','7187682238','7187682254','7187687428','7187690619','7187690849','7187691545','7187692238','7187692502','7187693026',

'7187693125','7187693569','7187693707','7187693771','7187694311','7187694751','7187697063','7187697380','7187697880','7187698011','7187698379','7187698981',

'7187699471','7187714204','7187719081','7187730463','7187740128','7187762371','7187770041','7187770758','7187771640','7187771692','7187772414','7187772781',

'7187772868','7187773933','7187775504','7187775560','7187775724','7187775866','7187777576','7187780477','7187781954','7187790469','7187790618','7187790847',

'7187790865','7187791095','7187792267','7187792367','7187792948','7187793777','7187793872','7187794198','7187794582','7187794615','7187794960','7187795132',

'7187795222','7187795508','7187796043','7187796415','7187796844','7187797622','7187797681','7187797758','7187797835','7187798578','7187798794','7187798952',

'7187799293','7187820677','7187820716','7187820757','7187821174','7187821493','7187822103','7187822479','7187822492','7187822540','7187822702','7187823024',

'7187823204','7187823405','7187823581','7187823668','7187823989','7187824599','7187824778','7187825558','7187826351','7187826894','7187826983','7187827057',

'7187827815','7187827862','7187828313','7187829749','7187829884','7187830214','7187830643','7187838148','7187841041','7187842714','7187842877','7187860154',

'7187860348','7187861638','7187861863','7187861990','7187863444','7187864397','7187868894','7187870959','7187871492','7187880717','7187883321','7187884417',

'7187887402','7187889092','7187892980','7187895061','7187895240','7187895769','7187923459','7187924158','7187924204','7187926134','7187926760','7187929594',

'7187930707','7187933729','7187935623','7187935874','7187936547','7187939188','7187949210','7187960476','7187962195','7187968979','7187970769','7187971374',

'7187975362','7187983651','7187983994','7187984130','7187984495','7187989773','7188030981','7188031494','7188031811','7188032538','7188032905','7188033271',

'7188033606','7188033753','7188038130','7188050721','7188051083','7188051246','7188051305','7188051359','7188051875','7188054625','7188055706','7188057438',

'7188057519','7188150474','7188159197','7188161327','7188165502','7188167467','7188169894','7188180836','7188200638','7188211316','7188211496','7188211845',

'7188211878','7188211913','7188212220','7188212592','7188213779','7188214285','7188214610','7188214615','7188215109','7188215236','7188215646','7188216180',

'7188216483','7188216485','7188218034','7188218915','7188219076','7188220092','7188222805','7188225839','7188226323','7188230537','7188234855','7188236617',

'7188240437','7188241859','7188245305','7188260628','7188262346','7188263856','7188270971','7188271302','7188272895','7188273061','7188273870','7188274792',

'7188275066','7188275605','7188275716','7188276649','7188277330','7188277350','7188278145','7188278293','7188278323','7188278492','7188280950','7188285516',

'7188285973','7188288197','7188292735','7188293942','7188297135','7188298399','7188299337','7188300715','7188320402','7188321706','7188321916','7188321926',

'7188322075','7188322262','7188323006','7188323319','7188323354','7188324683','7188324870','7188325182','7188326340','7188329048','7188331311','7188331714',

'7188331730','7188331742','7188332505','7188333556','7188333610','7188333736','7188334762','7188336049','7188336374','7188338980','7188346173','7188351089',

'7188352162','7188352666','7188353017','7188354168','7188360337','7188360435','7188360917','7188361221','7188361278','7188361587','7188364811','7188367087',

'7188367642','7188368733','7188369328','7188369418','7188370515','7188371222','7188371429','7188371963','7188372318','7188372461','7188373207','7188374629',

'7188375153','7188375708','7188375788','7188378334','7188378631','7188378678','7188379737','7188379850','7188420088','7188420108','7188420487','7188421012',

'7188421940','7188422392','7188423238','7188423841','7188424418','7188425904','7188426274','7188427455','7188428037','7188428341','7188433641','7188433675',

'7188435308','7188435869','7188436064','7188436720','7188439150','7188451248','7188452660','7188452896','7188453751','7188455377','7188457478','7188458214',

'7188460012','7188460153','7188460335','7188460478','7188460793','7188462324','7188462862','7188463861','7188463905','7188464068','7188464123','7188464589',

'7188464839','7188464912','7188464983','7188465257','7188465355','7188465383','7188465390','7188465479','7188467469','7188467561','7188467943','7188470216',

'7188470403','7188470427','7188471608','7188472683','7188472734','7188472842','7188473326','7188474837','7188475347','7188477441','7188478426','7188478513',

'7188478664','7188478881','7188479756','7188479790','7188479921','7188479966','7188480420','7188481534','7188483836','7188491672','7188491721','7188492080',

'7188492801','7188492812','7188492904','7188494752','7188495512','7188497501','7188499599','7188500283','7188501571','7188502036','7188502046','7188502162','7188502915','7188503409','7188503490','7188506568','7188507608','7188508397','7188510494','7188512384','7188512517','7188512752','7188512775','7188513642','7188513659','7188513966','7188514081','7188514201','7188514567','7188514652','7188514756','7188516488','7188517267','7188520478','7188522314','7188523971','7188524628','7188525619','7188526316','7188527171','7188527903','7188528148','7188530195','7188530207','7188530519','7188531095','7188531825','7188532481','7188533620','7188533809','7188534256','7188534346','7188535269','7188535693','7188535846','7188536243','7188536264','7188536374','7188536626','7188536787','7188537175','7188537215','7188538059','7188538090','7188538206','7188538298','7188538749','7188538787','7188538804','7188538870','7188539238','7188539773','7188540693','7188540809','7188540910','7188540972','7188541032','7188541275','7188541328','7188541434','7188542399','7188543016','7188543290','7188543545','7188543699','7188543961','7188544302','7188544428','7188544567','7188544745','7188546417','7188547687','7188548831','7188549408','7188552037','7188555438','7188560453','7188561870','7188563263','7188566710','7188568218','7188568343','7188568547','7188571368','7188590157','7188592193','7188592547','7188592974','7188593932','7188595748','7188597452','7188597627','7188599351','7188599774','7188600033','7188600721','7188600815','7188601038','7188601292','7188604393','7188604940','7188606172','7188606349','7188606724','7188606919','7188611747','7188611982','7188613718','7188614261','7188618091','7188618488','7188618711','7188619607','7188630365','7188630429','7188630857','7188632954','7188635415','7188636764','7188637062','7188637288','7188637596','7188637637','7188638440','7188639551','7188680267','7188710727','7188710817','7188711820','7188712965','7188713314','7188713874','7188716101','7188716296','7188717109','7188717438','7188718310','7188753424','7188754140','7188757634','7188760654','7188765210','7188765337','7188810912','7188813925','7188818175','7188824340','7188826326','7188841344','7188842157','7188845525','7188860274','7188860707','7188861957','7188862130','7188862730','7188863048','7188865992','7188869528','7188880096','7188889015','7188911470','7188911724','7188912513','7188913924','7188914378','7188914489','7188914526','7188915048','7188915266','7188916510','7188917352','7188918005','7188918473','7188918491','7188919282','7188919420','7188919452','7188920591','7188922806','7188922867','7188925345','7188929381','7188930361','7188930765','7188930769','7188930781','7188931739','7188934587','7188936794','7188936844','7188936920','7188937595','7188937987','7188939204','7188940023','7188940245','7188940583','7188941210','7188942021','7188942576','7188942739','7188944359','7188944923','7188945419','7188945542','7188947098','7188962071','7188964074','7188965090','7188965674','7188968370','7188971265','7188973159','7188973325','7188973396','7188973472','7188973791','7188973798','7188974660','7188974948','7188976532','7188979451','7188980374','7188980469','7188980983','7188981086','7188981279','7188982197','7188982843','7188983040','7188983432','7188983705','7188983752','7188985279','7188985767','7188986009','7188986040','7188986610','7188987079','7188987865','7188988085','7188988198','7188988573','7188988864','7188989665','7188990103','7188990119','7188990156','7188990751','7188991615','7188992085','7188993414','7188994676','7188995413','7188996403','7188997089','7188997463','7188998036','7188998062','7188999089','7188999457','7188999616','7189010817','7189010869','7189010950','7189011842','7189012184','7189013604','7189015201','7189015670','7189040568','7189180251','7189190849','7189191573','7189191811','7189192171','7189192762','7189193202','7189193619','7189195840','7189198817','7189211632','7189213149','7189215906','7189216319','7189221817','7189311472','7189312569','7189314304','7189314578','7189314783','7189315109','7189320273','7189320839','7189321812','7189321813','7189321823','7189323181','7189323583','7189323947','7189324076','7189324711','7189325322','7189325424','7189325629','7189327421','7189329017','7189330115','7189330291','7189333740','7189336140','7189336406','7189337682','7189338394','7189339274','7189339377','7189340348','7189340996','7189341326','7189341345','7189341549','7189342218','7189342516','7189342833','7189342979','7189343378','7189344788','7189346184','7189346884','7189347947','7189348049','7189348614','7189371547','7189371984','7189372573','7189374692','7189376179','7189390029','7189390987','7189391505','7189393244','7189393318','7189395509','7189395651','7189396250','7189396727','7189400327','7189400688','7189401746','7189402512','7189404481','7189404650','7189412860','7189414356','7189414503','7189417093','7189417241','7189422285','7189422525','7189422650','7189430175','7189430196','7189430199','7189430288','7189430314','7189430371','7189430372','7189435996','7189436410','7189437530','7189450538','7189451102','7189451257','7189451832','7189453608','7189453710','7189455023','7189455172','7189455865','7189456189','7189457131','7189457501','7189457510','7189459661','7189459708','7189460560','7189461476','7189462124','7189462189','7189462805','7189463256','7189463486','7189463656','7189464256','7189464494','7189464791','7189466104','7189467038','7189467307','7189468229','7189470546','7189470582','7189470585','7189470597','7189470605','7189470607','7189470613','7189470615','7189470628','7189470642','7189470644','7189470658','7189470669','7189470675','7189470682','7189470698','7189470710','7189470721','7189470727','7189470728','7189470730','7189470732','7189470733','7189470749','7189470751','7189470752','7189470757','7189470758','7189470760','7189470762','7189470767','7189470780','7189470783','7189470786','7189470794','7189470796','7189470797','7189470807','7189470826','7189470828','7189470830','7189470834','7189470838','7189470840','7189470841','7189470843','7189470845','7189470873','7189470876','7189470884','7189470887','7189470889','7189470907','7189470916','7189470925','7189470926','7189470928','7189470932','7189470936','7189470939','7189470957','7189470963','7189470964','7189470969','7189471052','7189471057','7189471058','7189471059','7189471082','7189471098','7189471105','7189471138','7189471145','7189471151','7189471154','7189471244','7189471251','7189471255','7189471261','7189471262','7189471275','7189471278','7189471282','7189471292','7189471328','7189471362','7189471363','7189471364','7189471365','7189471386','7189471392','7189471393','7189471422','7189471433','7189471461','7189471464','7189471491','7189471494','7189471570','7189471606','7189471765','7189485548','7189486706','7189492489','7189494656','7189511069','7189512276','7189512277','7189517838','7189518087','7189518760','7189518884','7189518984','7189519303','7189519836','7189530975','7189560093','7189560231','7189560238','7189560320','7189560503','7189561005','7189561047','7189561048','7189561272','7189561348','7189561556','7189561794','7189562168','7189563981','7189564479','7189564486','7189564528','7189565627','7189565739','7189565788','7189565843','7189566347','7189566542','7189567186','7189567558','7189568321','7189568330','7189568564','7189569490','7189610502','7189612956','7189619150','7189630168','7189630788','7189631570','7189632013','7189632074','7189632976','7189633192','7189633751','7189633822','7189634004','7189639794','7189650315','7189651103','7189651884','7189652083','7189661111','7189662418','7189665849','7189667528','7189687057','7189688964','7189689587','7189690185','7189690885','7189692709','7189693115','7189693601','7189699311','7189721139','7189721219','7189721390','7189721450','7189721459','7189722897','7189722954','7189722960','7189723002','7189723146','7189723182','7189723779','7189723819','7189723829','7189724498','7189724815','7189725504','7189726549','7189727009','7189727643','7189728304','7189792450','7189793350','7189794053','7189794743','7189798380','7189800198','7189800622','7189800792','7189805913','7189811154','7189812789','7189813418','7189815888','7189820166','7189825750','7189827859','7189828035','7189829217','7189831741','7189838329','7189838380','7189841062','7189842845','7189846914','7189848552','7189870577','7189874293','7189875789','7189876222','7189879213','7189879681','7189911639','7189912579','7189913073','7189913094','7189913873','7189915321','7189915984','7189916941','7189918551','7189919151','7189920641','7189920735','7189921121','7189921509','7189921734','7189921834','7189922143','7189923580','7189924081','7189925319','7189925891','7189925905','7189928280','7189929503','7189930243','7189930975','7189933109','7189934464','7189934547','7189934572','7189935734','7189937486','7189944713','7189946524','7189960318','7189960545','7189960549','7189960625','7189960729','7189961235','7189961586','7189962444','7189962713','7189963977','7189964152','7189964676','7189965292','7189965389','7189966035','7189966784','7189967140','7189968101','7189968183','7189968213','7189968741','7189970170','7189971087','7189976952','7189977314','7189979058','7189979624','7189981124','7189982008','7189982855','7189982959','7189986214','7189988389','7189988460','8452259089','8452468943','8452791399','8452925045','8453521785','8453522864','8453523813','8453524211','8453524484','8453527924','8453528034','8453528172','8453562773','8453565029','8453567444','8453580273','8453580760','8453585911','8453712440','8453712620','8453716119','8454250613','8454251421','8454254079','8454254784','8454254854','8454261269','8454262599','8454266457','8454267459','8454291796','8454340773','8454341805','8454344789','8454542436','8454625953','8454837658','8455284094','8455343415','8455616294','8455651283','8455666970','8455690205','8455697032','8455730144','8456213863','8456265009','8456265515','8456271377','8456473742','8456474340','8457271173','8457277745','8457532472','8457866080','8457867348','8457886780','8457916472','8457918483','8457940405','8458964868','8459477094','9142321846','9142351023','9142355342','9142356043','9142378590','9142411651','9142412108','9142412336','9142412908','9142414382','9142421759','9142500167','9142500271','9142500272','9142713418','9142767666','9142767667','9143321390','9143324572','9143324785','9143328172','9143371090','9143472664','9143750640','9143750681','9143752606','9143752878','9143752980','9143755930','9143756064','9143759018','9143761319','9143761966','9143763954','9143765804','9143765899','9143767153','9143769526','9143789171','9143817430','9144222671','9144233363','9144233917','9144234068','9144234593','9144234721','9144234761','9144237450','9144237869','9144725032','9144725297','9144763768','9144764828','9144769317','9144769580','9144769844','9144780514','9144783532','9144785083','9144787557','9144791298','9145240559','9145280867','9145286518','9145762553','9145763182','9145763890','9145763975','9145764004','9145765023','9145765828','9145768225','9145769186','9145919586','9146311708','9146322303','9146324887','9146328092','9146329447','9146331515','9146333927','9146360297','9146541848','9146632612','9146643301','9146650671','9146657046','9146678126','9146684242','9146684762','9146830825','9146841842','9146847041','9146869786','9146901033','9146909817','9146931130','9146931542','9146982426','9146987134','9147090115','9147120857','9147349202','9147362948','9147367630','9147369786','9147371431','9147378154','9147379064','9147382217','9147388319','9147391652','9147398481','9147472024','9147473258','9147477573','9147612357','9147620328','9147623299','9147631377','9147632100','9147693136','9147773590','9147885645','9148332108','9149210097','9149229331','9149237593','9149238364','9149340694','9149342309','9149349245','9149375812','9149379796','9149390280','9149391344','9149399174','9149399237','9149417746','9149447059','9149464715','9149485639','9149492004','9149493137','9149615666','9149629652','9149632051','9149636257','9149650453','9149650712','9149652521','9149653445','9149663621','9149680803','9149682051','9149682130','9149682392','9149684868','9149687023','9149690418','9149690661','9149693021','9149693852','9149695716','9173268901','9174922134','9174929446','9175211440','9175212118','9175212147','9175217228','9175217252','NEW1007818','NEW1009805','NEW1009942','NEW1009946','NEW1010040','NEW1010090') or ordUidCustomer = 277173336)

I have seen similar performance problems in 2005 when using huge lists of IN values in the where clause. I am not able to change the SQL or the length of the list because it is generated by a report writer and I have no control over what the user selects.

Try 2005 SP2 (on a test machine) and see if that helps.
|||

Maybe there could be some performance gains by putting that huge "IN" list into a temporary table, and then indexing the temporary table.

Dan

|||SQL2005 do have performance problem for IN and NOT IN. Here is another thread talking about this issue.

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=990564&SiteID=1

You can use left join or right join to implement same behavior with IN and NOT IN. That will improve your query performance.
|||

>>Maybe there could be some performance gains by putting that huge "IN" list into a temporary table, and then indexing the temporary table.<<

This is what I was thinking. I have a query like this but it passes it as a delimited an I run a split function on it like this:

Code Snippet

declare @.listIds varchar(max)
set @.listIds = '1,2,3,4,5,6,7,8,9'

;with digits as(
select 1 as i union all select 2 as i union all select 3 union all
select 4 union all select 5 union all select 6 union all select 7 union all
select 8 union all select 9 union all select 0),

sequence as (
SELECT distinct D1.i + (10*D2.i) + (100*D3.i) + (1000*D4.i) + (10000*D5.i) + (100000*D6.i) as i
FROM digits AS D1, digits AS D2,
digits AS D3,digits as D4, digits as D5, digits as D6
),
list as (
SELECT substring(',' + @.listIds + ',', i + 1,
charindex(',', ',' + @.listIds + ',', i + 1) - i - 1) AS responseActivityWorkId
FROM sequence
WHERE i <= len(',' + @.listIds + ',') - 1
AND substring(',' + @.listIds + ',', i, 1) = ',')
select *
from list

The select * from list would just be replaced by your query with a join to this list CTE, and remove the IN (big old list). Then format the list as 'value,value,value' and go for it. I support 999999 as the max length of the parameter, but just adding a D7 (following the pattern) and you could go even higher. (I would like to do your data, but the rotation around the screen would take me a long time to reformat

If you have CTE performance issues, just put the results of the list query into a temp table, add an index and try that as a join or IN clause. Then it should be as fast as you can get then.

If you would like to read more about the subject of arrays and lists in SQL Server, please try the excellent article: http://www.sommarskog.se/arrays-in-sql-2005.html

|||

they got around this by using temp tables. i tried this on some new 64 bit servers we just bought with 20GB RAM and same result. 100% CPU spike but execution time is around 30 seconds instead of 10 minutes

pwdencrypt vs sql server 2005

Hi,
I store application passwords in a sql server 2000 table. The passwords are
encrypt with the pwdencrypt function.
I have to migrate the database to sql server 2005. I would like to use the
new crypt function in sql server 2005
(EncryptByPassPhrase/DecryptByPassPhrase).
How can I decrypt old password (crypt with pwdencrypt) and recrypt them with
the new sql server 2005 function ? I know that pwdencrypt is still support i
n
sql server 2005, but for how long ?
Thanks"shwac" <shwac@.discussions.microsoft.com> wrote in message
news:8C9DDD58-17B0-4E9C-A2DC-D72CBBB4ECD0@.microsoft.com...
> Hi,
> I store application passwords in a sql server 2000 table. The passwords
> are
> encrypt with the pwdencrypt function.
> I have to migrate the database to sql server 2005. I would like to use the
> new crypt function in sql server 2005
> (EncryptByPassPhrase/DecryptByPassPhrase).
> How can I decrypt old password (crypt with pwdencrypt) and recrypt them
> with
> the new sql server 2005 function ? I know that pwdencrypt is still support
> in
> sql server 2005, but for how long ?
> Thanks
>
You cannot decrypt the output of pwdencrypt because, despite the name, it
isn't an encryption function at all - it's a hash function. The
complementary function is pwdcompare which compares a hash with the hash of
a password string you supply. "Supported" isn't quite right either. These
functions are undocumented so Microsoft is under no obligation to support
them. I believe that breaking changes have applied to these functions in the
past and may well do so again.
Passwords should generally be hashed rather than encrypted. Use the
HashBytes function rather than the Encrypt functions and implement policies
for password length, quality and expiry. That's assuming you need to create
your own password authentication mechanism. If you can use integrated
security, certificates or other mechanisms then do so.
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--sql

pwdencrypt and Case sensitive

Hi there
I know how to use pwdencrypt for my password and know how to use collation,
but how can I merge it together to make my password case sensitive and
pwdencrypt working, because collation only not support varbinary and my
pwdencrypt is saved on varbinary data type.
Any one has idea how to to this?
Thanks
Tony
I believe pwdencrypt behavior depends on the instance default collation. In
any case, you shouldn't use the undocumented pwdencrypt function in your
code. This may change or be removed in future SQL Server service packs or
versions.
Hope this helps.
Dan Guzman
SQL Server MVP
"Tony Dong" <tony.dong@.envoytrading.com> wrote in message
news:Oy07MZsRFHA.996@.TK2MSFTNGP09.phx.gbl...
> Hi there
> I know how to use pwdencrypt for my password and know how to use
> collation, but how can I merge it together to make my password case
> sensitive and pwdencrypt working, because collation only not support
> varbinary and my pwdencrypt is saved on varbinary data type.
> Any one has idea how to to this?
> Thanks
> Tony
>
|||> I know how to use pwdencrypt for my password
If you knew that then you wouldn't be using it. ;-) Don't.
..NET provides classees for generating password hashes. They are more secure
than pwdencrypt and they are also properly documented and supported whereas
pwdencrypt is likely to break or disappear in future releases.
David Portas
SQL Server MVP
|||I am Tony's coworker and using .NET to encrypt the password is not an
attractive option because it means that we cannot operate the database
without .NET. It makes the business tier and database tier tightly
coupled or to use some of my old C++ jargon "It breaks encapsulation!"
If we were using Yukon, then .NET would be the way to go.
Cheers
Ted
*** Sent via Developersdex http://www.codecomments.com ***

pwdencrypt and Case sensitive

Hi there
I know how to use pwdencrypt for my password and know how to use collation,
but how can I merge it together to make my password case sensitive and
pwdencrypt working, because collation only not support varbinary and my
pwdencrypt is saved on varbinary data type.
Any one has idea how to to this?
Thanks
TonyI believe pwdencrypt behavior depends on the instance default collation. In
any case, you shouldn't use the undocumented pwdencrypt function in your
code. This may change or be removed in future SQL Server service packs or
versions.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Tony Dong" <tony.dong@.envoytrading.com> wrote in message
news:Oy07MZsRFHA.996@.TK2MSFTNGP09.phx.gbl...
> Hi there
> I know how to use pwdencrypt for my password and know how to use
> collation, but how can I merge it together to make my password case
> sensitive and pwdencrypt working, because collation only not support
> varbinary and my pwdencrypt is saved on varbinary data type.
> Any one has idea how to to this?
> Thanks
> Tony
>|||> I know how to use pwdencrypt for my password
If you knew that then you wouldn't be using it. ;-) Don't.
.NET provides classees for generating password hashes. They are more secure
than pwdencrypt and they are also properly documented and supported whereas
pwdencrypt is likely to break or disappear in future releases.
--
David Portas
SQL Server MVP
--

pwdencrypt and Case sensitive

Hi there
I know how to use pwdencrypt for my password and know how to use collation,
but how can I merge it together to make my password case sensitive and
pwdencrypt working, because collation only not support varbinary and my
pwdencrypt is saved on varbinary data type.
Any one has idea how to to this?
Thanks
TonyI believe pwdencrypt behavior depends on the instance default collation. In
any case, you shouldn't use the undocumented pwdencrypt function in your
code. This may change or be removed in future SQL Server service packs or
versions.
Hope this helps.
Dan Guzman
SQL Server MVP
"Tony Dong" <tony.dong@.envoytrading.com> wrote in message
news:Oy07MZsRFHA.996@.TK2MSFTNGP09.phx.gbl...
> Hi there
> I know how to use pwdencrypt for my password and know how to use
> collation, but how can I merge it together to make my password case
> sensitive and pwdencrypt working, because collation only not support
> varbinary and my pwdencrypt is saved on varbinary data type.
> Any one has idea how to to this?
> Thanks
> Tony
>|||> I know how to use pwdencrypt for my password
If you knew that then you wouldn't be using it. ;-) Don't.
.NET provides classees for generating password hashes. They are more secure
than pwdencrypt and they are also properly documented and supported whereas
pwdencrypt is likely to break or disappear in future releases.
David Portas
SQL Server MVP
--|||I am Tony's coworker and using .NET to encrypt the password is not an
attractive option because it means that we cannot operate the database
without .NET. It makes the business tier and database tier tightly
coupled or to use some of my old C++ jargon "It breaks encapsulation!"
If we were using Yukon, then .NET would be the way to go.
Cheers
Ted
*** Sent via Developersdex http://www.codecomments.com ***

Pwdcompare()

I have a Oracle view which has user-info : user-name , passwords. Passwords in this view are encrypted. I exported this view to SQL Server. Can i use PWDCOMPARE(<plaintext>, <encryptedtext>) function on exported table to compare the Passwords?I don't think this is going to resolve your issue. Encryption done by Oracle is *proprietary* to Oracle. Pdwcompare() is proprietary to SQL. I also want to note that the use of this undocumented function is not supported by MS and they could change its behavior at anytime.|||With pwd.. functions and large strings, ANY user can crash pre-SP3 sql server!|||So how can i secure the user-info table which has "user-name & password" in SQL Server. Is thier a way that i can hide the password?

Thanks|||Do not save passwords in open format, even encrypted.
If your table is used to authorize users, store hash of password,
for example SHA1. Try http://www.activecrypt.com.

Good luck !|||Hi ispaleny,

Thank you for the link. It was really helpful. I just went through the link. It defines some encrytion algorithms. I am using SQL Server 7, and it doesn't allow "CREATE FUNCTION". Is their any other alternative?

Thanks|||Use stored procedure to encrypt row by row.|||I am able to use the stored procedure to encrypt each row one by one. But i am facing problem comparing the password.

I tried using "exec <storedprocedure name> " in the select statement and it doesn't allow me to do so. i want to use the return value of the stored procedure in the SELECT statement. I wrote stored procedure with two input variable and one output variable. I want to use the value of this output variable in the select statement.

Thanks|||Is this what you want ?

declare @.Ret bit
exec usp_YourSP '&^%','@.$#%^^$^#',@.Ret OUTPUT
select * from YourTable where YourCol=@.Ret|||No I was trying to use something like this

Select * from table1 where col1 = exec <storedprocedure> @.Ret OUTPUT.

I wanted call this stored procedure in my servlet program. I got that working, by using CallableStatement. Link you posted http://www.activecrypt.com. was very helpful. Thank you.

Puzzling Primary Key Problem

Hi,

I wonder if anybody encountered before with tables which has records with some of the primary keys as null values?

Currently I've found a number of records which isn't suppose to be inserted in the first place from a table.

My table size is over a few millions and currently using SQL Server Standard version

Seems like the primary key got corrupted or something...

Anybody encountered this before??

-deb-If the primary key of the table is autogenerated then you can use

DBCC CHECKIDENT

for Checking the current identity value for the specified table and, if needed, corrects the identity value.|||Thanks but I don't think that's the case because it happens to a non identity column.
By Default even before inserting it SQL Server should have prompted a Primary Key Violation Error but in this case it doesn't and proceed to insert the value into the column as null.|||Can you execute a DBCC Checktable statement on the table and verify the results.|||Will try and see and let you know as the db is at customer's site.
Thanks
:)|||do the users provide their own values for example could they press the spacebar or does your front end add characters using a ascii function like char(xx)

here is what [BOL] has to say on the matter

"When a PRIMARY KEY constraint is added to an existing column or columns in the table, Microsoft SQL Server 2000 checks the existing data in the columns to ensure that the existing data follows the rules for primary keys:

No null values
No duplicate values

If a PRIMARY KEY constraint is added to a column that has duplicate or null values, SQL Server returns an error and does not add the constraint. It is not possible to add a PRIMARY KEY constraint that violates these rules.
"

so i tried this

create table nulltesttable
(
nullcolumn varchar(10) not null
)

and added three rows

insert nulltesttable values (char(9))
insert nulltesttable values (char(13))
insert nulltesttable values (char(32))

then i applied the pk with nocheck and it applied as long as i didnt duplicate the ascii chars in the columns

i cant say what your issue is but if you try this query it could at least lead you to eliminate this as a problem

select Char(nullcolumn) as 'Null column'
from nulltesttable

there are a very limited ascii chars that are invisible so if you have a large # of rows that appear to be null then this is probably not your issue either way thanks for the exercise...|||Thanks... actually that was what I suspect also due to the reason the values are from barcode and sometimes the reader can return some rubbish that is not visible. There are a few records which this rubbish data.

But still I need to look for other possibilities. For my case if I were to select out using a select statement

select * from table
where column is null

It'll return records. :)

That's the very funny thing*sigh*

Originally posted by Ruprect
do the users provide their own values for example could they press the spacebar or does your front end add characters using a ascii function like char(xx)

here is what [BOL] has to say on the matter

"When a PRIMARY KEY constraint is added to an existing column or columns in the table, Microsoft SQL Server 2000 checks the existing data in the columns to ensure that the existing data follows the rules for primary keys:

No null values
No duplicate values

If a PRIMARY KEY constraint is added to a column that has duplicate or null values, SQL Server returns an error and does not add the constraint. It is not possible to add a PRIMARY KEY constraint that violates these rules.
"

so i tried this

create table nulltesttable
(
nullcolumn varchar(10) not null
)

and added three rows

insert nulltesttable values (char(9))
insert nulltesttable values (char(13))
insert nulltesttable values (char(32))

then i applied the pk with nocheck and it applied as long as i didnt duplicate the ascii chars in the columns

i cant say what your issue is but if you try this query it could at least lead you to eliminate this as a problem

select Char(nullcolumn) as 'Null column'
from nulltesttable

there are a very limited ascii chars that are invisible so if you have a large # of rows that appear to be null then this is probably not your issue either way thanks for the exercise...sql

Puzzling "NOT EXISTS" behavior

CASE CLOSED: removing the FROM clause noted below handles the problem ...
I've used "NOT EXISTS" in inserts before and thought I understood how they work, butI'm puzzled why I get three rows from the following sub-query, which I want to use to prevent errors from duplicate keys. Since it gives me three rows, it will actually try to insert three duplicate records and cause a primary-key fault, which is a twin to the very thing I'm trying to avoid. (of course, there are three records already in the table, none having the key of 20050810)
The sub-query is acting like it's using "WHERE DemoDate <> 20050810" instead of a "NOT EXISTS".
Attempts to use this statement causes a termination with no records inserted.
DemoDate is the primary key and an INT field in SQL Server. Four other int columns for this table have default values.
<code>
INSERT INTO DemoStats (DemoDate)
SELECT 20050810 AS Expr1
FROM DemoStats <-- remove this line so the effective table has only one row, when the NOT EXISTS is TRUE
WHERE (NOT EXISTS
(SELECT *
FROM DemoStats
WHERE DemoDate = 20050810))
</code>I read that as saying...
For every row in DemoStats that != 20050810 please insert 20050810
So surely you'd get loads inserts and therefore duplicate key bangs?
Why not simply
<code>
If not exists(xxxxx) insert <yyyy>
</code>
granted it introduces a condition but it's a lot easier to understand your intentions, plus it works ;)

|||I don't think that will work, since this is a web-based app, so someone else could easily insert the record between checking for exists and doing the insert, thus potentially causing the error anyway. This needs to be handled within a single sql statement.
I found the following (and several other examples that maybe suggest that EXISTS should work a little more like I'm thinking). In this case I wondering if "dual" is a reserved word with a special function, which I haven't seen before.
<code>

If you wanted to insert a single record, you could use the following statement:

INSERT INTO clients
(client_id, client_name, client_type)
SELECT 10345, 'IBM', 'advertising'
FROM dual
WHERE not exists (select * from clients
where clients.client_id = 10345);

The use of thedual table allows you to enter your values in a select statement, even though the values are not currently stored in a table.

</code>|||

Edited:
If you really want to use your approach you could do simply like this :
INSERT INTO clients
(client_id, client_name, client_type)
SELECT 10345, 'IBM', 'advertising'
WHERE not exists (select * from clients
where clients.client_id = 10345);
without dual.

|||My theory (I'd love to have someone confirm or deny this): I think it's a bug in SQL Server or maybe even SQL specs. As I understand it, EXISTS is supposed to be a single-valued boolean: TRUE ifone or more rows satisfy the sub-query, and FALSE ifno rows satsify the sub-query. I think the bug depends on the fact that the INSERT, the SELECT, and the sub-query all deal with the same table. I have other UPDATES and INSERTS that use NOT EXISTS, and they're working correctly, but they don't have the same table in the INSERT, SELECT, and sub-query. If I'm wrong I need to go back and re-examine them, but need some confidence of knowing what is right.
When I add DISTINCT below it adds just one record (doesn't try to add three identical-key records), so I'm out of trouble, but I don't know why it otherwise tries to insert three records and triggers an error and termination.
<code>
INSERT INTO DemoStats (DemoDate)
SELECT DISTINCT 20050810 AS Expr1
FROM DemoStats
WHERE (NOT EXISTS
(SELECT *
FROM DemoStats
WHERE DemoDate = 20050810))
</code>
|||

BBradshaw wrote:
I don't think that will work, since this is a web-based app, so someone else could easily insert the record between checking for exists and doing the insert,

That's what isolation levels are for or you'll probably get away with just sticking it a transaction.

Puzzled by Query Plan

I'm hoping somebody can explain exactly what's going on here - I can't
find it documented anywhere.

Go to the Northwind database, and run the following SQL:

create index IX_UnitPrice on [order details](unitprice)

Now, turn on SHOWPLAN (either graphical or text, it doesn't matter),
and run the following query:

select * from [order details]
where unitprice = 2

Output:

StmtText
|--Index Seek(OBJECT: ([Northwind].[dbo].[Order
Details].[IX_UnitPrice]), SEEK: ([Order
Details].[UnitPrice]=Convert([@.1])) ORDERED FORWARD)

Now, alter the SARG slightly by making it a float:

select unitprice from [order details]
where unitprice = 2.000

Output:

StmtText
|--Nested Loops(Inner Join, OUTER REFERENCES: ([Expr1003], [Expr1004],
[Expr1005]))
|--Compute Scalar(DEFINE: ([Expr1003]=Convert(Convert([@.1]))-1.00,
[Expr1004]=Convert(Convert([@.1]))+1.00, [Expr1005]=If
(Convert(Convert([@.1]))-1.00=NULL) then 0 else 6|If
(Convert(Convert([@.1]))+1.00=NULL) then 0 else 10))
| |--Constant Scan
|--Index Seek(OBJECT: ([Northwind].[dbo].[Order
Details].[IX_UnitPrice]), SEEK: ([Order Details].[UnitPrice] >
[Expr1003] AND [Order Details].[UnitPrice] < [Expr1004]), WHERE:
(Convert([Order Details].[UnitPrice])=Convert([@.1])) ORDERED FORWARD)

Right. I understand that in both cases the SARG datatype is different
from the column datatype (which is money), and that in the first
example the SARG constant gets implicitly converted from int -> money
(following the datatype hierarchy rules), and so the index can still
be used.

In the second example, the datatype hierarchy dictates that money is
lower than float, so the table column gets implicitly converted from
money -> float, which strictly speaking disallows the use of the index
on that column.

What I DON'T understand is what exactly all that gubbins about the
expressions (especially the definition of [Expr1005] is all about; how
does that statement decide whether Expr1005 is going to be NULL, 6, or
10?

I'm soon going to be giving some worked tutorials on index selection
and use of Showplan to our developers, and being a bolshi lot they're
bound to want to know exactly what all that output means. I'd rather
be able to tell them than to say I don't actually know!

How about it someone?

Thanks,

Phil"Philip Yale" wrote:

<snip
> select unitprice from [order details]
> where unitprice = 2.000
> Output:
> StmtText
> |--Nested Loops(Inner Join, OUTER REFERENCES: ([Expr1003], [Expr1004],
> [Expr1005]))
> |--Compute Scalar(DEFINE: ([Expr1003]=Convert(Convert([@.1]))-1.00,
> [Expr1004]=Convert(Convert([@.1]))+1.00, [Expr1005]=If
> (Convert(Convert([@.1]))-1.00=NULL) then 0 else 6|If
> (Convert(Convert([@.1]))+1.00=NULL) then 0 else 10))
> | |--Constant Scan
> |--Index Seek(OBJECT: ([Northwind].[dbo].[Order
> Details].[IX_UnitPrice]), SEEK: ([Order Details].[UnitPrice] >
> [Expr1003] AND [Order Details].[UnitPrice] < [Expr1004]), WHERE:
> (Convert([Order Details].[UnitPrice])=Convert([@.1])) ORDERED FORWARD)
>
> Right. I understand that in both cases the SARG datatype is different
> from the column datatype (which is money), and that in the first
> example the SARG constant gets implicitly converted from int -> money
> (following the datatype hierarchy rules), and so the index can still
> be used.
> In the second example, the datatype hierarchy dictates that money is
> lower than float, so the table column gets implicitly converted from
> money -> float, which strictly speaking disallows the use of the index
> on that column.
> What I DON'T understand is what exactly all that gubbins about the
> expressions (especially the definition of [Expr1005] is all about; how
> does that statement decide whether Expr1005 is going to be NULL, 6, or
> 10?

<snip
Phil,

It appears that SQL Server is converting your float SARG to 2 money scalars
(SARG - 1 and SARG + 1) so that it can perform an index seek with money
types and still handle loss of precision and floating point rounding. The
second part of the seek (listed as the WHERE) then converts the index values
to float: this way it doesn't have to convert the table column until after a
seek has been performed. Pretty smart if you ask me...

[Expr1005] use a bitwise or so that Expr1005 has a unique value for the
various NULL states of the other two calcuated values

6 = 0110 in binary
10 = 1010 in binary

SARG-1 SARG+1 Result
NOT NULL NOT NULL 6 | 10 = 1110
NOT NULL NULL 6 | 0 = 0110
NULL NOT NULL 0 | 10 = 1010
NULL NULL 0 | 0 = 0000

So you have a 4-bit value where you can examine bit 1 to see if you have any
non-null value, bit 2 to check SARG-1 for a non-null value, and bit 3 to
check SARG+1 for a non-null value. Bit 0 tells you nothing: I don't know
why unless it has something to do with the internal representation of null's
or float's or something else.

What I'm not clear on is exactly how this bit mask is used in the nested
loop join unless a row is rejected out of hand for a zero value for the
expression. Perhaps someone else can shed light on this...

Craig|||"Craig Kelly" <cnkelly.nospam@.nospam.net> wrote in message news:<L8ydd.14900$OD2.132@.bgtnsc05-news.ops.worldnet.att.net>...
> "Philip Yale" wrote:
> <snip>
> > select unitprice from [order details]
> > where unitprice = 2.000
> > Output:
> > StmtText
> > |--Nested Loops(Inner Join, OUTER REFERENCES: ([Expr1003], [Expr1004],
> [Expr1005]))
> > |--Compute Scalar(DEFINE: ([Expr1003]=Convert(Convert([@.1]))-1.00,
> > [Expr1004]=Convert(Convert([@.1]))+1.00, [Expr1005]=If
> > (Convert(Convert([@.1]))-1.00=NULL) then 0 else 6|If
> > (Convert(Convert([@.1]))+1.00=NULL) then 0 else 10))
> > | |--Constant Scan
> > |--Index Seek(OBJECT: ([Northwind].[dbo].[Order
> > Details].[IX_UnitPrice]), SEEK: ([Order Details].[UnitPrice] >
> > [Expr1003] AND [Order Details].[UnitPrice] < [Expr1004]), WHERE:
> > (Convert([Order Details].[UnitPrice])=Convert([@.1])) ORDERED FORWARD)
> > Right. I understand that in both cases the SARG datatype is different
> > from the column datatype (which is money), and that in the first
> > example the SARG constant gets implicitly converted from int -> money
> > (following the datatype hierarchy rules), and so the index can still
> > be used.
> > In the second example, the datatype hierarchy dictates that money is
> > lower than float, so the table column gets implicitly converted from
> > money -> float, which strictly speaking disallows the use of the index
> > on that column.
> > What I DON'T understand is what exactly all that gubbins about the
> > expressions (especially the definition of [Expr1005] is all about; how
> > does that statement decide whether Expr1005 is going to be NULL, 6, or
> > 10?
> <snip>
> Phil,
> It appears that SQL Server is converting your float SARG to 2 money scalars
> (SARG - 1 and SARG + 1) so that it can perform an index seek with money
> types and still handle loss of precision and floating point rounding. The
> second part of the seek (listed as the WHERE) then converts the index values
> to float: this way it doesn't have to convert the table column until after a
> seek has been performed. Pretty smart if you ask me...
> [Expr1005] use a bitwise or so that Expr1005 has a unique value for the
> various NULL states of the other two calcuated values
> 6 = 0110 in binary
> 10 = 1010 in binary
> SARG-1 SARG+1 Result
> NOT NULL NOT NULL 6 | 10 = 1110
> NOT NULL NULL 6 | 0 = 0110
> NULL NOT NULL 0 | 10 = 1010
> NULL NULL 0 | 0 = 0000
> So you have a 4-bit value where you can examine bit 1 to see if you have any
> non-null value, bit 2 to check SARG-1 for a non-null value, and bit 3 to
> check SARG+1 for a non-null value. Bit 0 tells you nothing: I don't know
> why unless it has something to do with the internal representation of null's
> or float's or something else.
> What I'm not clear on is exactly how this bit mask is used in the nested
> loop join unless a row is rejected out of hand for a zero value for the
> expression. Perhaps someone else can shed light on this...
> Craig

Craig,

Thanks very much for that - an extremely well-explained and detailed
answer. I'm intrigued to know how you knew all that stuff - or did you
just deduce it? (Don't want a job do you? :-) )

I must confess I never thought that the values might be bitmaps. Like
you say, it's all pretty smart. All we need to know now is just where
Expr1005 is actually used.

Phil|||"Craig Kelly" <cnkelly.nospam@.nospam.net> wrote in message news:<L8ydd.14900$OD2.132@.bgtnsc05-news.ops.worldnet.att.net>...
> "Philip Yale" wrote:
> <snip>
> > select unitprice from [order details]
> > where unitprice = 2.000
> > Output:
> > StmtText
> > |--Nested Loops(Inner Join, OUTER REFERENCES: ([Expr1003], [Expr1004],
> [Expr1005]))
> > |--Compute Scalar(DEFINE: ([Expr1003]=Convert(Convert([@.1]))-1.00,
> > [Expr1004]=Convert(Convert([@.1]))+1.00, [Expr1005]=If
> > (Convert(Convert([@.1]))-1.00=NULL) then 0 else 6|If
> > (Convert(Convert([@.1]))+1.00=NULL) then 0 else 10))
> > | |--Constant Scan
> > |--Index Seek(OBJECT: ([Northwind].[dbo].[Order
> > Details].[IX_UnitPrice]), SEEK: ([Order Details].[UnitPrice] >
> > [Expr1003] AND [Order Details].[UnitPrice] < [Expr1004]), WHERE:
> > (Convert([Order Details].[UnitPrice])=Convert([@.1])) ORDERED FORWARD)
> > Right. I understand that in both cases the SARG datatype is different
> > from the column datatype (which is money), and that in the first
> > example the SARG constant gets implicitly converted from int -> money
> > (following the datatype hierarchy rules), and so the index can still
> > be used.
> > In the second example, the datatype hierarchy dictates that money is
> > lower than float, so the table column gets implicitly converted from
> > money -> float, which strictly speaking disallows the use of the index
> > on that column.
> > What I DON'T understand is what exactly all that gubbins about the
> > expressions (especially the definition of [Expr1005] is all about; how
> > does that statement decide whether Expr1005 is going to be NULL, 6, or
> > 10?
> <snip>
> Phil,
> It appears that SQL Server is converting your float SARG to 2 money scalars
> (SARG - 1 and SARG + 1) so that it can perform an index seek with money
> types and still handle loss of precision and floating point rounding. The
> second part of the seek (listed as the WHERE) then converts the index values
> to float: this way it doesn't have to convert the table column until after a
> seek has been performed. Pretty smart if you ask me...
> [Expr1005] use a bitwise or so that Expr1005 has a unique value for the
> various NULL states of the other two calcuated values
> 6 = 0110 in binary
> 10 = 1010 in binary
> SARG-1 SARG+1 Result
> NOT NULL NOT NULL 6 | 10 = 1110
> NOT NULL NULL 6 | 0 = 0110
> NULL NOT NULL 0 | 10 = 1010
> NULL NULL 0 | 0 = 0000
> So you have a 4-bit value where you can examine bit 1 to see if you have any
> non-null value, bit 2 to check SARG-1 for a non-null value, and bit 3 to
> check SARG+1 for a non-null value. Bit 0 tells you nothing: I don't know
> why unless it has something to do with the internal representation of null's
> or float's or something else.
> What I'm not clear on is exactly how this bit mask is used in the nested
> loop join unless a row is rejected out of hand for a zero value for the
> expression. Perhaps someone else can shed light on this...
> Craig

Craig,

Thanks very much for that - an extremely well-explained and detailed
answer. I'm intrigued to know how you knew all that stuff - or did you
just deduce it? (Don't want a job do you? :-) )

I must confess I never thought that the values might be bitmaps. Like
you say, it's all pretty smart. All we need to know now is just where
Expr1005 is actually used.

Phil|||"Philip Yale" wrote:

> Craig,
> Thanks very much for that - an extremely well-explained and detailed
> answer. I'm intrigued to know how you knew all that stuff - or did you
> just deduce it? (Don't want a job do you? :-) )
> I must confess I never thought that the values might be bitmaps. Like
> you say, it's all pretty smart. All we need to know now is just where
> Expr1005 is actually used.
> Phil

Phil,

Thank you for the multiple (and very flattering) compliments!

I make it a habit to examine query plans and make sure I understand them
when unit testing stored procedures, so they aren't totally unfamiliar to
me; however, I don't consider myself a query plan expert by any means. I
was mostly able to deduce what was going on fairly quickly because that kind
of handling of floating point values is one of two fairly common idioms in
C/C++ (especially since portable floating point handling can be, ahem,
challenging given the differing implementations out there). But I'm still
very curious as to where the bitmap is used...

As far as employment is concerned, I'm fairly happy where I'm at but obscene
piles of money are always a great inducement ;)

Craig

Friday, March 30, 2012

Q Statistics & Query Performance

Q on joining tables with nullable fields

Q on Clustered and Nonclustered Index files to be on Raid

Q on Clustered and Nonclustered Index files to be on Raid

Q any group or freeze function

Q : Does MSDE have an evaluation Period

Q - Adding a database file

Q - Adding a database file

Q - Add file to a Publisher or subscriber database

q

q

pwdencrypt vs sql server 2005

pwdencrypt and Case sensitive

pwdencrypt and Case sensitive

pwdencrypt and Case sensitive

Pwdcompare()

Puzzling Primary Key Problem

Puzzling "NOT EXISTS" behavior

Puzzled by Query Plan

Sql Public Role

Blog Archive

About Me