Filtered Index on a Temp Table, in a Stored Proc, can result in a Error 602

Question

Filtered Index on a Temp Table, in a Stored Proc, can result in a Error 602

Luis Cazares

SSC Guru

Points: 183706
More actions
September 19, 2017 at 12:07 pm

#332720

Today I got notified that we were getting errors on a stored procedure. Recently, I made some performance improvements to it and included a filtered index for a temp table. Apparently, that started causing an error which is described in here: https://connect.microsoft.com/SQLServer/feedback/details/670808/filtered-index-on-a-temp-table-in-a-stored-proc-can-result-in-a-error-602
Does someone knows if this issue is solved on any hotfix/CU/SP for 2008 R2?
I found that there was a fix for 2012 and 2014, but nothing for previous versions.
Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?
How to post data/code on a forum to get the best help: Option 1 / Option 2

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 1

Dumb question, do you drop the temp table before exiting the procedure or just let it go out of scope? I read the connect item and the code there just lets the temp table go out of scope, unless I missed the drop table.

Luis Cazares SSC Guru Points: 183706 More actions · Answer 2

Lynn Pettis - Tuesday, September 19, 2017 2:53 PM
Dumb question, do you drop the temp table before exiting the procedure or just let it go out of scope? I read the connect item and the code there just lets the temp table go out of scope, unless I missed the drop table.

I just let it go out of scope. I guess it wouldn't hurt to drop it and see if that helps.
Thank you.

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 3

Luis Cazares - Tuesday, September 19, 2017 3:07 PM
Lynn Pettis - Tuesday, September 19, 2017 2:53 PM
Dumb question, do you drop the temp table before exiting the procedure or just let it go out of scope? I read the connect item and the code there just lets the temp table go out of scope, unless I missed the drop table.
I just let it go out of scope. I guess it wouldn't hurt to drop it and see if that helps.
Thank you.

Let us know. I have always tried (may not succeed) to drop temporary tables that I create in a stored procedure. There have been discussions that doing so slows down the procedure but it just makes sense to me to try and do it.

Filtered Index on a Temp Table, in a Stored Proc, can result in a Error 602

Cookies on SQLServerCentral