Does DATEADD change the datatype?

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
>

Documentation about noise words (ignored words)

As my customers are mainly not-English-speaking, I'm used to prepare specific
versions of the file "noise.dat" for all the indexing for neutral language.
Anyway, even though I'm not including the paragraph symbol (§), I've
realized that this character is automatically considered as ignored word.
The questions are:
- Is there a way to control these ignored characters, evetually by excluding
ot including themn in the full-text catalog?
- Where can I find a detailed documentation of all these
language-independent characters that are ignored?
Thanks in advance,
Angelo
There is no real description of the noise word lists or the treatment of
special characters. In general all punctuation marks are ignored with some
exceptions.
Here is a good description of how noise words are indexed.
http://msdn.microsoft.com/library/de...nario_8k4z.asp
Hilary Cotter
Looking for a book on SQL Server replication?
http://www.nwsu.com/0974973602.html
"Angelo B" <Angelo B@.discussions.microsoft.com> wrote in message
news:87641E91-C218-4DFD-9CFF-495F5B6EF3C0@.microsoft.com...
> As my customers are mainly not-English-speaking, I'm used to prepare
specific
> versions of the file "noise.dat" for all the indexing for neutral
language.
> Anyway, even though I'm not including the paragraph symbol (), I've
> realized that this character is automatically considered as ignored word.
> The questions are:
> - Is there a way to control these ignored characters, evetually by
excluding
> ot including themn in the full-text catalog?
> - Where can I find a detailed documentation of all these
> language-independent characters that are ignored?
> Thanks in advance,
> Angelo
|||Hi Hilary
Your answer replied my question but but didn't help the issue.
The issue is that our customers are governative authorities, which make
large use of § (paragraph character) in the documents MSSearch indexes. As
this character is a prefix for law numbers, such as "the article expressed in
the § 12 of law # 2340-124", they pretend that by typing "§ 12" in the search
form they get all documents containing "§ 12", not only "12", otherwise they
get thousands of documents that have nothing to do with "paragraph 12".
I wonder why has MS chosen not to index these characters. If they exist,
they are also used in documents, and they should be searchable.
The noise.dat should give the possibility to treat these characters as
ignored word/character or not.
It's a serious issue...
Are you sure that it's not possible to include/exclude these characters? Is
there a key in the Registry, maybe?
Thanks in advance,
Angelo
"Hilary Cotter" wrote:

> There is no real description of the noise word lists or the treatment of
> special characters. In general all punctuation marks are ignored with some
> exceptions.
> Here is a good description of how noise words are indexed.
> http://msdn.microsoft.com/library/de...nario_8k4z.asp
>
> --
> Hilary Cotter
> Looking for a book on SQL Server replication?
> http://www.nwsu.com/0974973602.html
>
> "Angelo B" <Angelo B@.discussions.microsoft.com> wrote in message
> news:87641E91-C218-4DFD-9CFF-495F5B6EF3C0@.microsoft.com...
> specific
> language.
> excluding
>
>
|||I realize I was not of much help.
Someone approached me some time ago about doing something similar. What I
recommended they do is to replace all the unindexable tokens with another
token that is indexed, ie xxxx. Then when they were searching on this
unindexable character the client would replace it with the xxxx and they
would get the results they were looking for.
They had two columns storing the content, one where the unindexable
character was replaced by a searchable token (xxxx), and the other which
contains the actual content. The one where the unindexable character was
replaced was the column indexed, the other the one returned in searches.
HTH
Hilary Cotter
Looking for a book on SQL Server replication?
http://www.nwsu.com/0974973602.html
"Angelo B" <AngeloB@.discussions.microsoft.com> wrote in message
news:C6573FCA-5B09-45A8-A765-CED8891A1F81@.microsoft.com...
> Hi Hilary
> Your answer replied my question but but didn't help the issue.
> The issue is that our customers are governative authorities, which make
> large use of (paragraph character) in the documents MSSearch indexes. As
> this character is a prefix for law numbers, such as "the article expressed
in
> the 12 of law # 2340-124", they pretend that by typing " 12" in the
search
> form they get all documents containing " 12", not only "12", otherwise
they
> get thousands of documents that have nothing to do with "paragraph 12".
> I wonder why has MS chosen not to index these characters. If they exist,
> they are also used in documents, and they should be searchable.
> The noise.dat should give the possibility to treat these characters as
> ignored word/character or not.
> It's a serious issue...
> Are you sure that it's not possible to include/exclude these characters?
Is[vbcol=seagreen]
> there a key in the Registry, maybe?
> Thanks in advance,
> Angelo
>
> "Hilary Cotter" wrote:
some[vbcol=seagreen]
http://msdn.microsoft.com/library/de...nario_8k4z.asp[vbcol=seagreen]
word.[vbcol=seagreen]