I have an odd issue (mainly revolving around proper index usage.)
For some reason when I use a DATEADD function within a join the between or
>= , <= operators the optimizer ignores the index and performs a full table
scan.
Example
This will NOT use the INDEX
--code start
Select * from Time_Dimension where Date_Number BETWEEN DATEADD(DAY, 0,
DATEDIFF(MONTH, -1, GETDATE())) AND DATEADD(DAY, 0, DATEDIFF(DAY, 0,
GETDATE()))
--code end
Basically the query will return dates today and a 1 month ago. which works
fine but in looking at the query analyzer I find the optimizer is not using
the index where as if i had used a hard coded date or DATEADD(DAY, 0,
DATEDIFF(DAY, 0, GETDATE())) without supplying DATEADD range.
This WILL use the INDEX
Select * from Time_Dimension where Date_Number BETWEEN DATEADD(DAY, 0,
DATEDIFF(DAY, 0, GETDATE())) AND DATEADD(DAY, 0, DATEDIFF(DAY, 0, GETDATE()
))
OR
Select * from Time_Dimension where Date_Number BETWEEN '2/1/2005' AND
'2/28/2005'
OR EVEN.. ( AND THIS ONE IS WEIRD)
Select * from Time_Dimension where Date_Number DATEADD(DAY, 1, DATEDIFF(DAY,
0, GETDATE()))
Any Thoughts
ThanksCheck out what selectivity the optimizer estimates and also if you see an CO
NVERT on the column
side.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Eric" <Eric@.discussions.microsoft.com> wrote in message
news:3036BF48-0920-4A8C-857D-10337A8C593D@.microsoft.com...
>I have an odd issue (mainly revolving around proper index usage.)
> For some reason when I use a DATEADD function within a join the between or
> scan.
> Example
> This will NOT use the INDEX
> --code start
> Select * from Time_Dimension where Date_Number BETWEEN DATEADD(DAY, 0,
> DATEDIFF(MONTH, -1, GETDATE())) AND DATEADD(DAY, 0, DATEDIFF(DAY, 0,
> GETDATE()))
> --code end
> Basically the query will return dates today and a 1 month ago. which works
> fine but in looking at the query analyzer I find the optimizer is not usin
g
> the index where as if i had used a hard coded date or DATEADD(DAY, 0,
> DATEDIFF(DAY, 0, GETDATE())) without supplying DATEADD range.
> This WILL use the INDEX
> Select * from Time_Dimension where Date_Number BETWEEN DATEADD(DAY, 0,
> DATEDIFF(DAY, 0, GETDATE())) AND DATEADD(DAY, 0, DATEDIFF(DAY, 0, GETDATE
()))
> OR
> Select * from Time_Dimension where Date_Number BETWEEN '2/1/2005' AND
> '2/28/2005'
> OR EVEN.. ( AND THIS ONE IS WEIRD)
> Select * from Time_Dimension where Date_Number DATEADD(DAY, 1, DATEDIFF(DA
Y,
> 0, GETDATE()))
> Any Thoughts
> Thanks
>
Showing posts with label datatype. Show all posts
Showing posts with label datatype. Show all posts
Friday, March 9, 2012
Wednesday, March 7, 2012
Does Boolean DataType Exists in SQL server
Does boolean data type exists in Sequel if y how? if N y?
How can i opt for boolean ( nearest ) ?
I've used Bit with not null
i want 2 display column of bit datatype into Checkbox .. Is is possible??
Whenver i bind the datatype of bit for checkbox it is throwing an error??
HELP ME HELP MEBit works fine as a boolean field, I don't have any problems. It is most likely a problem with nulls in your data. You can bind the bit filed to the checked property of a checkbox and you can put that checkbox in a repeater or datagrid using TemplateColumn.
Can you show your code. Or have you already resolved this issue. I know it is a bit old.
Subscribe to:
Posts (Atom)