Optimizing Clustered Index Scan

I am trying to tune a query that has as its execution output as a Clustered
Index Scan. There is a Clustered Index on field [dt], which is a datetime
field. The query is structured as such:
IF @.date < '1/1/1901'
--...then make it NULL, so that it works in the query
SET @.date = NULL
SELECT
t1.dt_UID
FROM Table1 AS t1
WHERE ( ( @.date IS NULL )
OR ( t1.dt BETWEEN @.date AND DateAdd(d, 1, @.date) ) )
When I remove the ( @.date IS NULL ) portion from the WHERE clause I get a
Clustered Index Seek. Is there any way to rework the WHERE clause to where it
would always be a seek?
--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200604/1Why are you setting @.date to null if @.date is < '1/1/1901'?
--
MG
"cbrichards" wrote:
> I am trying to tune a query that has as its execution output as a Clustered
> Index Scan. There is a Clustered Index on field [dt], which is a datetime
> field. The query is structured as such:
>
> IF @.date < '1/1/1901'
> --...then make it NULL, so that it works in the query
> SET @.date = NULL
> SELECT
> t1.dt_UID
> FROM Table1 AS t1
> WHERE ( ( @.date IS NULL )
> OR ( t1.dt BETWEEN @.date AND DateAdd(d, 1, @.date) ) )
> When I remove the ( @.date IS NULL ) portion from the WHERE clause I get a
> Clustered Index Seek. Is there any way to rework the WHERE clause to where it
> would always be a seek?
> --
> Message posted via SQLMonster.com
> http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200604/1
>|||Try,
IF @.date < '1/1/1901'
--...then make it NULL, so that it works in the query
SET @.date = NULL
SELECT
t1.dt_UID
FROM
Table1 AS t1
WHERE
t1.dt BETWEEN isnull(@.date, '17530101') AND isnull(DateAdd(d, 1, @.date),
'9999-12-30T23:59:59')
go
Dynamic Search Conditions in T-SQL
http://www.sommarskog.se/dyn-search.html
AMB
"cbrichards" wrote:
> I am trying to tune a query that has as its execution output as a Clustered
> Index Scan. There is a Clustered Index on field [dt], which is a datetime
> field. The query is structured as such:
>
> IF @.date < '1/1/1901'
> --...then make it NULL, so that it works in the query
> SET @.date = NULL
> SELECT
> t1.dt_UID
> FROM Table1 AS t1
> WHERE ( ( @.date IS NULL )
> OR ( t1.dt BETWEEN @.date AND DateAdd(d, 1, @.date) ) )
> When I remove the ( @.date IS NULL ) portion from the WHERE clause I get a
> Clustered Index Seek. Is there any way to rework the WHERE clause to where it
> would always be a seek?
> --
> Message posted via SQLMonster.com
> http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200604/1
>|||"cbrichards" <u3288@.uwe> wrote in message news:5f760d81fa01c@.uwe...
>I am trying to tune a query that has as its execution output as a Clustered
> Index Scan. There is a Clustered Index on field [dt], which is a datetime
> field. The query is structured as such:
>
> IF @.date < '1/1/1901'
> --...then make it NULL, so that it works in the query
> SET @.date = NULL
> SELECT
> t1.dt_UID
> FROM Table1 AS t1
> WHERE ( ( @.date IS NULL )
> OR ( t1.dt BETWEEN @.date AND DateAdd(d, 1, @.date) ) )
> When I remove the ( @.date IS NULL ) portion from the WHERE clause I get a
> Clustered Index Seek. Is there any way to rework the WHERE clause to where
> it
> would always be a seek?
>
This is a classic case of pseudo-dynamic SQL. THis is really two different
queries crammed into one. With this query you must do a scan because the
same plan is used whether or not @.date is null. If it is, obviously only a
clustered index scan will do.
IF @.date < '1/1/1901'
BEGIN
SELECT
t1.dt_UID
FROM Table1 AS t1
END
ELSE
BEGIN
SELECT
t1.dt_UID
FROM Table1 AS t1
WHERE t1.dt BETWEEN @.date AND DateAdd(d, 1, @.date)
END
david|||David,
Thanks for your response. I ran this in test and (true enough!) the same plan
is used whether or not @.date is null. At this point, I am trying to
understand why the same plan is used. Could you explain further, please,
specifically why the same plan is used?
David Browne wrote:
>>I am trying to tune a query that has as its execution output as a Clustered
>> Index Scan. There is a Clustered Index on field [dt], which is a datetime
>[quoted text clipped - 14 lines]
>> it
>> would always be a seek?
>This is a classic case of pseudo-dynamic SQL. THis is really two different
>queries crammed into one. With this query you must do a scan because the
>same plan is used whether or not @.date is null. If it is, obviously only a
>clustered index scan will do.
>IF @.date < '1/1/1901'
>BEGIN
> SELECT
> t1.dt_UID
> FROM Table1 AS t1
>END
>ELSE
>BEGIN
> SELECT
> t1.dt_UID
> FROM Table1 AS t1
> WHERE t1.dt BETWEEN @.date AND DateAdd(d, 1, @.date)
>END
>david
--
Message posted via http://www.sqlmonster.com|||"cbrichards via SQLMonster.com" <u3288@.uwe> wrote in message
news:5f9b39b4a6681@.uwe...
> David,
> Thanks for your response. I ran this in test and (true enough!) the same
> plan
> is used whether or not @.date is null. At this point, I am trying to
> understand why the same plan is used. Could you explain further, please,
> specifically why the same plan is used?
>
Each query gets only one plan. The values you happen to bind to the
parameters don't change that. So whatever the plan is for the query, it has
to work for all possible values of the parameters.
Read:
Batch Compilation, Recompilation, and Plan Caching Issues in SQL Server 2005
http://www.microsoft.com/technet/prodtechnol/sql/2005/recomp.mspx
David|||Thanks Alejandro. That appears to resolve the scan regardless of the value of
@.date. Much appreciated. Thanks for the link too.
Alejandro Mesa wrote:
>Try,
>IF @.date < '1/1/1901'
> --...then make it NULL, so that it works in the query
> SET @.date = NULL
>SELECT
> t1.dt_UID
>FROM
> Table1 AS t1
>WHERE
> t1.dt BETWEEN isnull(@.date, '17530101') AND isnull(DateAdd(d, 1, @.date),
>'9999-12-30T23:59:59')
>go
>Dynamic Search Conditions in T-SQL
>http://www.sommarskog.se/dyn-search.html
>AMB
>> I am trying to tune a query that has as its execution output as a Clustered
>> Index Scan. There is a Clustered Index on field [dt], which is a datetime
>[quoted text clipped - 13 lines]
>> Clustered Index Seek. Is there any way to rework the WHERE clause to where it
>> would always be a seek?
--
Message posted via http://www.sqlmonster.com|||David,
I read through the article you provided as a link. I understand better what
you mean by "each query gets only one plan" and "parameters do not change
that."
I am trying to internalize this knowledge and understand it within the
context of my attached statement, which contains the OR condition. As you
originally mentioned, I really have "two different queries crammed into one."
As I originally noted, when I remove the ( @.date IS NULL ) portion from the
WHERE clause I get a Clustered Index Seek. Does this mean, that when the plan
is created, that in order for the optimizer to cache only one plan (in this
case, using my pseudo dynamic SQL), the optimizer, in essence, will go with a
plan that will satisfy both conditions, and the plan that satisfies both
conditions is an Index Scan? Is my understanding correct?
David Browne wrote:
>> David,
>> Thanks for your response. I ran this in test and (true enough!) the same
>> plan
>> is used whether or not @.date is null. At this point, I am trying to
>> understand why the same plan is used. Could you explain further, please,
>> specifically why the same plan is used?
>Each query gets only one plan. The values you happen to bind to the
>parameters don't change that. So whatever the plan is for the query, it has
>to work for all possible values of the parameters.
>Read:
>Batch Compilation, Recompilation, and Plan Caching Issues in SQL Server 2005
>http://www.microsoft.com/technet/prodtechnol/sql/2005/recomp.mspx
>David
--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200605/1|||"cbrichards via SQLMonster.com" <u3288@.uwe> wrote in message
news:5f9e9495ccc86@.uwe...
> David,
> I read through the article you provided as a link. I understand better
> what
> you mean by "each query gets only one plan" and "parameters do not change
> that."
> I am trying to internalize this knowledge and understand it within the
> context of my attached statement, which contains the OR condition. As you
> originally mentioned, I really have "two different queries crammed into
> one."
> As I originally noted, when I remove the ( @.date IS NULL ) portion from
> the
> WHERE clause I get a Clustered Index Seek. Does this mean, that when the
> plan
> is created, that in order for the optimizer to cache only one plan (in
> this
> case, using my pseudo dynamic SQL), the optimizer, in essence, will go
> with a
> plan that will satisfy both conditions, and the plan that satisfies both
> conditions is an Index Scan? Is my understanding correct?
>
Yes. That is it, exactly.
David|||you would have a LOT better luck if you added anohter variable, and did
the dateadd before the select statement.
The way you wrote it, the system has to calculate the dateadd for each
and every row, and it might not be obvious to the engine how simple
your querty could be.
set @.date2 =DateAdd(d, 1, @.date)
SELECT
t1.dt_UID
FROM Table1 AS t1
WHERE ( t1.dt BETWEEN @.date AND @.date2)
OR ( @.date IS NULL )
sounds weird, but sometimes moving the null part around encourages or
discourages the use of hte index. Depends on how that particular engine
parses - you want it to parse and sue the index first.
Worst case you can use a hint to force the engine to use the index.

Operator is not valid for type 'Date' and type 'Date'

I was wondering if someone could assist me in writing an experession that
would subtract 2 DateTime's. I have 2 DateTime fields and want to display
both in a report and in a third column the difference between the 2 values.
However, I get an error:
"Operator is not valid for type 'Date' and type 'Date'"
Many thanks,
SimonYou can get the difference from SQL by trying something like this in your
store proc or query
SELECT date1, date2, DATEDIFF(d, date1, date2) AS date3
hope it helps.
- David
"Simon Dingley" wrote:
> I was wondering if someone could assist me in writing an experession that
> would subtract 2 DateTime's. I have 2 DateTime fields and want to display
> both in a report and in a third column the difference between the 2 values.
> However, I get an error:
> "Operator is not valid for type 'Date' and type 'Date'"
> Many thanks,
> Simon
>
>|||Assuming you have two DateTime fields and you want to determine the
difference in total seconds in the report rather than the query, you can use
an expression like this:
=Datediff("s", Fields!End_Time.Value, Fields!Start_Time.Value)
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"David Nevarez" <DavidNevarez@.discussions.microsoft.com> wrote in message
news:33B5D244-F9D5-4ECB-99CB-F9B1B7D9E445@.microsoft.com...
> You can get the difference from SQL by trying something like this in your
> store proc or query
> SELECT date1, date2, DATEDIFF(d, date1, date2) AS date3
> hope it helps.
> - David
> "Simon Dingley" wrote:
> > I was wondering if someone could assist me in writing an experession
that
> > would subtract 2 DateTime's. I have 2 DateTime fields and want to
display
> > both in a report and in a third column the difference between the 2
values.
> > However, I get an error:
> >
> > "Operator is not valid for type 'Date' and type 'Date'"
> >
> > Many thanks,
> >
> > Simon
> >
> >
> >|||Thank You for the replies. I opted for the in-report solution as opposed to
the SQL solution but thanks for both. What I want to return is the
difference in days and hours but can seem to do it I changed the format
string to "d" instead of "s" to return the number of days but when I tried
to use "d h" i get "#error".
Thanks for the help.
Simon
"Robert Bruckner [MSFT]" <robruc@.online.microsoft.com> wrote in message
news:eVK4OnNsEHA.832@.TK2MSFTNGP10.phx.gbl...
> Assuming you have two DateTime fields and you want to determine the
> difference in total seconds in the report rather than the query, you can
use
> an expression like this:
> =Datediff("s", Fields!End_Time.Value, Fields!Start_Time.Value)
> --
> This posting is provided "AS IS" with no warranties, and confers no
rights.

Operation on DateTime

Hi there
Presently I have a table with a DateTime column as the following:
Col1
2003/12/01 00:05:00
2003/12/01 00:10:00
2003/12/01 00:15:00
2003/12/01 00:20:00
What I want to do is to separate the date and time into two columns,
so it becomes:
Col1 Col2 Col3
2003/12/01 00:05:00 2003/12/01 00:05:00
2003/12/01 00:10:00 2003/12/01 00:10:00
2003/12/01 00:15:00 2003/12/01 00:15:00
2003/12/01 00:20:00 2003/12/01 00:20:00
.
.
..
I am wondering which way is easier - making a program to do this (e.g. in
C#)
or using SQL queries. Thanks for any suggestions.
YuelinDepends where you need the values, you can use the RIGHT Function for that,
just use aa format that also represents the time format and Cut of the 8
right Characters from it, therell you be.
Or you go by:
Select Getdate(),
Convert(varchar(10),getdate(),111),Conve
rt(varchar(10),getdate(),114)
But IMHO formatting should be a client work.
--
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
--
"Yuelin Liang" <yuelinliang@.hotmail.com> schrieb im Newsbeitrag
news:uacpnCsWFHA.3184@.TK2MSFTNGP15.phx.gbl...
> Hi there
> Presently I have a table with a DateTime column as the following:
> Col1
> 2003/12/01 00:05:00
> 2003/12/01 00:10:00
> 2003/12/01 00:15:00
> 2003/12/01 00:20:00
>
> What I want to do is to separate the date and time into two columns,
> so it becomes:
> Col1 Col2 Col3
> 2003/12/01 00:05:00 2003/12/01 00:05:00
> 2003/12/01 00:10:00 2003/12/01 00:10:00
> 2003/12/01 00:15:00 2003/12/01 00:15:00
> 2003/12/01 00:20:00 2003/12/01 00:20:00
> .
> .
> ..
> I am wondering which way is easier - making a program to do this (e.g. in
> C#)
> or using SQL queries. Thanks for any suggestions.
> Yuelin
>|||Hi
You can write the query this way
UPDATE <TABLE>
SET
col2 = convert(varchar(10), col1,102),
col3 = convert(varchar(8), col1,108)
best Regards,
Chandra
http://chanduas.blogspot.com/
http://groups.msn.com/SQLResource/
---
"Yuelin Liang" wrote:

> Hi there
> Presently I have a table with a DateTime column as the following:
> Col1
> 2003/12/01 00:05:00
> 2003/12/01 00:10:00
> 2003/12/01 00:15:00
> 2003/12/01 00:20:00
>
> What I want to do is to separate the date and time into two columns,
> so it becomes:
> Col1 Col2 Col3
> 2003/12/01 00:05:00 2003/12/01 00:05:00
> 2003/12/01 00:10:00 2003/12/01 00:10:00
> 2003/12/01 00:15:00 2003/12/01 00:15:00
> 2003/12/01 00:20:00 2003/12/01 00:20:00
> ..
> ..
> ...
> I am wondering which way is easier - making a program to do this (e.g. in
> C#)
> or using SQL queries. Thanks for any suggestions.
> Yuelin
>
>|||update <table>
set
col2 = convert(varchar(10),col1,111),
col3 = convert(varchar(10),col1,108)
best Regards,
Chandra
http://chanduas.blogspot.com/
http://groups.msn.com/SQLResource/
---
"Yuelin Liang" wrote:

> Hi there
> Presently I have a table with a DateTime column as the following:
> Col1
> 2003/12/01 00:05:00
> 2003/12/01 00:10:00
> 2003/12/01 00:15:00
> 2003/12/01 00:20:00
>
> What I want to do is to separate the date and time into two columns,
> so it becomes:
> Col1 Col2 Col3
> 2003/12/01 00:05:00 2003/12/01 00:05:00
> 2003/12/01 00:10:00 2003/12/01 00:10:00
> 2003/12/01 00:15:00 2003/12/01 00:15:00
> 2003/12/01 00:20:00 2003/12/01 00:20:00
> ..
> ..
> ...
> I am wondering which way is easier - making a program to do this (e.g. in
> C#)
> or using SQL queries. Thanks for any suggestions.
> Yuelin
>
>|||Thanks for the advice.
The original table I have doesn't have the column 2 and column 3, I need to
populate the two columns based on the datetime data in the column 1.
How to do this in T-SQL?
Thanks
Yuelin
"Chandra" <Chandra@.discussions.microsoft.com> wrote in message
news:24C0B6EE-354B-41EA-9AD6-7CB11AC3B52C@.microsoft.com...
> update <table>
> set
> col2 = convert(varchar(10),col1,111),
> col3 = convert(varchar(10),col1,108)
> --
> best Regards,
> Chandra
> http://chanduas.blogspot.com/
> http://groups.msn.com/SQLResource/
> ---
>
> "Yuelin Liang" wrote:
>|||Then you either have to extend the table before updating then table, or you
insert the new data in a new table via:
Select
col1,
convert(varchar(10),col1,111) as col2,
convert(varchar(10),col1,108) as col3
Into NewTable
From Yourtable
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
--
"Yuelin Liang" <yuelinliang@.hotmail.com> schrieb im Newsbeitrag
news:OYPTy0sWFHA.1508@.tk2msftngp13.phx.gbl...
> Thanks for the advice.
> The original table I have doesn't have the column 2 and column 3, I need
> to
> populate the two columns based on the datetime data in the column 1.
> How to do this in T-SQL?
> Thanks
> Yuelin
>
> "Chandra" <Chandra@.discussions.microsoft.com> wrote in message
> news:24C0B6EE-354B-41EA-9AD6-7CB11AC3B52C@.microsoft.com...
>|||Hi,
Then u can use this way
SELECT col1, convert(varchar(10),col1,111) col2,
convert(varchar(10),col1,108) col3
FROM <TABLE>
best Regards,
Chandra
http://chanduas.blogspot.com/
http://groups.msn.com/SQLResource/
---
"Yuelin Liang" wrote:

> Thanks for the advice.
> The original table I have doesn't have the column 2 and column 3, I need t
o
> populate the two columns based on the datetime data in the column 1.
> How to do this in T-SQL?
> Thanks
> Yuelin
>
> "Chandra" <Chandra@.discussions.microsoft.com> wrote in message
> news:24C0B6EE-354B-41EA-9AD6-7CB11AC3B52C@.microsoft.com...
>
>|||The problem with changing the structure of a physical table to meet some
presentation requirement is that different applications or users have
different requirements. For example, what if one user wants YYYY/MM/DD and
another wants MM/DD/YYYY? Also, the transformed representation of a value
is ususally not as storage efficient as the native data format. Rather than
modify your existing physical table, leave the date as datetime in the table
and implement the transformations as Views. The convert function can be used
to represent datetime as a string in various formats.
"Yuelin Liang" <yuelinliang@.hotmail.com> wrote in message
news:uacpnCsWFHA.3184@.TK2MSFTNGP15.phx.gbl...
> Hi there
> Presently I have a table with a DateTime column as the following:
> Col1
> 2003/12/01 00:05:00
> 2003/12/01 00:10:00
> 2003/12/01 00:15:00
> 2003/12/01 00:20:00
>
> What I want to do is to separate the date and time into two columns,
> so it becomes:
> Col1 Col2 Col3
> 2003/12/01 00:05:00 2003/12/01 00:05:00
> 2003/12/01 00:10:00 2003/12/01 00:10:00
> 2003/12/01 00:15:00 2003/12/01 00:15:00
> 2003/12/01 00:20:00 2003/12/01 00:20:00
> .
> .
> ..
> I am wondering which way is easier - making a program to do this (e.g. in
> C#)
> or using SQL queries. Thanks for any suggestions.
> Yuelin
>

Operand type clash: datetime is incompatible with text

I am getting this error on my insert statement, do I need to do something
for my datetime fields?
Here is the statement
INSERT INTO tblCalendar (
adjName, DispDueDate, ClaimNumber, Juris, HearingDate, HearingType,
ClaimantName, Location, HearingTime, HearingPart, WCB#, Counsel,
DateRecd)
VALUES (
@.Adjuster, @.newDispositionDueDate, @.ClaimNumber, @.Juris, @.newHearingDate,
@.HearingType, @.ClaimantName, @.HearingLocation, @.newHearingTime,
@.HearingPartRoom, @.WCBClaimNumber, @.AssignedCounsel, @.newNoticeReceived)You might want to check the datatypes of each column in tblCalendar table
and make sure they are compatible with the variables in the VALUES() clause.
If you have difficulty, post the exact CREATE TABLE statements and the
declared types and assigned values for each variables in the INSERT
statement.
Anith