February 13, 2014 at 8:07 am
I have similar query in SQL2000 and does not fail, but in sql 2008 r2 fail.
But if you put cast(convert(varchar(8),cast(texto as smalldatetime),108)+':00' its ok, or if you use UNION and instead of "like" use "=" it's works.
I put the SET SHOWPLAN_ALL ON and i saw in PLAN_ROW, when fail, sql put first the "convertion smalldatetime" filter and then the "like" filter; and when is ok sql put first the "like" filter and then the "convertion" filter.
Why sql does not follow the order of the instructions as I wrote?
There is a way to make sql 2008 r2 behave normal?
declare @tbl table (abreviatura varchar(5), texto varchar(100))
insert @tbl (abreviatura, texto)
select 'E','hola'
union
select 'hora', '10:30'
union
select 'horaE', '10:30'
declare @HoraDate smalldatetimeset @HoraDate = cast(convert(varchar(5),getdate(), 108) as smalldatetime)
select *
from @tbl
where abreviatura like 'hora%'
and @HoraDate >= cast(convert(varchar(8),cast(texto as smalldatetime),108) as smalldatetime)
February 13, 2014 at 8:28 am
That's the nature of T-SQL. T-SQL is a declarative language. It won't follow the instructions in the order that you write them, it will generate a plan to get the desired result.
The best option to correct this would be to normalize the data. It will save you headaches.
Another option would be to put a validation before the CAST (on 2012 you could use TRY_CAST). To reduce the code, I used the time data type as it seems to be what you need.
declare @tbl table (abreviatura varchar(5), texto varchar(100))
insert @tbl (abreviatura, texto)
select 'E','hola'
union all
select 'hora', '10:30'
union all
select 'horaE', '10:30'
declare @HoraDate time
set @HoraDate = cast('11:30' as time);
select *, CAST(texto AS time) textohora
from @tbl
where abreviatura like 'hora%'
AND @HoraDate >= CAST(CASE WHEN abreviatura like 'hora%' THEN texto END AS time);
February 13, 2014 at 9:35 am
thank you, Luis Cazares
It's much simpler your query, but i think query plan make a bad choice, because if SqlServer do it as it's written, not fail, and its a very simple query, i dont expect write cast to make sure sql don't fail.
This is unexpected for us, because we migrate a large database in sql server 2000 to 2008 r2 a year ago, and it works great, but this kind of errors make you feel doubtful, we had already faced "parameter sniffer" for procedures that naver fail before, and now this...
I hope there is something to correct this without having to be checking all the time to plan execution in the simplest queries or modifying querys fail to prevent
I think the query should be this, without having to place the case, the case its just a prevention.... I know if there is no more alternatives i have to doit the other way.
declare @tbl table (abreviatura varchar(5), texto varchar(100))
insert @tbl (abreviatura, texto)
select 'E','hola'
union all
select 'hora', '10:30'
union all
select 'horaE', '10:30'
declare @HoraDate time
set @HoraDate = cast('11:30' as time);
select *
, CAST(texto AS time) textohora
from @tbl
where abreviatura LIKE 'hora'
AND @HoraDate >= CAST(texto AS time)
February 13, 2014 at 10:39 am
patsipatochoa (2/13/2014)
It's much simpler your query, but i think query plan make a bad choice, because if SqlServer do it as it's written, not fail, and its a very simple query, i dont expect write cast to make sure sql don't fail.
The engines between 2k and 2k8 have changed. Different optimizations, different under the hood processes. As Luis already mentioned, SQL is not a procedural language, it's declaritive. You don't directly control the optimizer. You clean up things under the hood.
This is unexpected for us,
Then you didn't do your due diligence, because there's literally hundreds of articles and blogs about this.
I hope there is something to correct this without having to be checking all the time to plan execution in the simplest queries or modifying querys fail to prevent
Part of your weekly maintenance should be to check the execution histories of your queries, looking for worst offenders and places to optimize your system. This is a standard practice in the industry. Data growth and usage patterns always change in an environment.
I think the query should be this, without having to place the case, the case its just a prevention.... I know if there is no more alternatives i have to doit the other way.
You are storing a time as a varchar field, and then bringing in another time as a time field. You're mixing your data types. Something has to do a conversion. Either you control it or you take your chances with the decisions of the optimizer.
I'm sorry you're distraught about this, but the optimizer has never promised to do things in any particular order. Ever. Not without explicit and direct controls including query hints and using temp tables. That it used to work in the old engine and not in the new one is something that every team should be reviewing during an upgrade. This is why you can't blindly upgrade databases, and I know many companies who still run 2k servers because it simply wasn't worth the expense to upgrade some of their highly stable (and now relatively inert, changewise) custom softwares.
We'll be happy to try to help if you have other poorly performing queries, just post them up with the information in the link in my signature that lists "for help with indexes/tuning" so we have what we need to assist. Unfortunately, though, you're not going to be able to see the same plans you did on the old engine. Even if you switch the compatibility mode on the database, that just means it uses an old syntax checker and a few other items, but it won't revert to the old engine.
Good luck, and sorry to have to deliver the bad news.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
February 13, 2014 at 10:41 am
The method you use was subject to fail even with 2000. The best solution as always is to normalize the data.
As you can imagine, every option will need code changes, but you have several ones.
- Use a persisted filtered view to prevent this errors.
- Create a computed column to have time values.
You don't have to look at the execution plan for this kind of problems, an execution plan is subject to change. You need to prevent this from happening when you code your solutions.
Viewing 5 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply