August 24, 2011 at 10:26 am
Hi,
I am experiencing weird behaviour on sql server today. Not sure what has changed.
I have the following query
SELECT G.amount, G.batch, G.batchdate FROM dbo.HistoryTable G JOIN #Posttypes PT
ON G.Posttype = PT.PosttypeVal
JOIN #Periods P ON G.Batchdate = P.Date;
If I run the above query, it doesn't return anything. #Posttypes has only one column with value 'D'. If I comment out the join to #posttypes and specify where G.Posttype = 'D', it works.
Also, If I do LTRIM(RTRIM(G.Posttype)) = LTRIM(RTRIM(PT.Posttype)), it works.
Both G.Posttype and PT.Posttype are char(1) columns. So, I don't understand why it doesn't work. We were running this query for years and we didn't have problem until now.
Thanks,
Sridhar.
August 24, 2011 at 10:29 am
Looks like you have spaces padded in your columns. That is why the rtrim/ltrim is working.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
August 24, 2011 at 11:06 am
It is char(1) column. So, there shouldn't be any spaces.
August 24, 2011 at 11:24 am
Please provide sample data and table create statements so we can test.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
August 24, 2011 at 11:39 am
I figured it out. The table HistoryTable has posttypes "E", "D", "T" etc. Most of the times we query for posttype "E". So, I created a filtered index where posttype = 'E' to speed up those queries. Apparently Sql Server seems to be using that index for all the other queries too even when posttype <> 'E'. Since it doesn't find any data, it is returning nothing.
It worked when I used LTRIM(RTRIM()) because at that time, it didn't use the index.
I deleted the filtered index now and it works fine. But I would like to have a filtered index. Is there a way to do this?
Thanks,
Sridhar.
August 24, 2011 at 11:43 am
That makes sense. Thanks for posting back what you found
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply