RE: same function from sql 2000 doesn't work on sql 2008 r2

SSC Guru

Points: 186127

May 17, 2013 at 8:28 am

Linda_web (5/16/2013)
We have very strange situation I hope someone can help, we copied a database from sql 2000 sp4 to sql 2008 R2 SP2 , runs with compatibility level 2000, it has table valued function basically returns text column, the statement has select * from mytable where len(convert(varchar(4000), TextCol)) > 25
Same function works in sql 2000, but doesn’t work in sql 2008, simply no result just runs hours and hangs and we have to kill it. Any idea why? And what I need to do in sql 2008,
BTW: this new server has more RAM and CPU than old server, old server on 2003, but this sql on win 2008 r2
Thank you

Please post the code for the function and the query using it. Without it, folks are just guessing.

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden