July 25, 2012 at 7:55 am
This is 2005 specific so no merge statements allowed. I read or heard a while back that having a delete and an insert statement in one sproc causes a recompile or is not optimized. Searching the web this AM I had a very hard time proving that point. Am I right or wrong and does anyone have an article to reference?
Thanks
July 25, 2012 at 10:47 am
That assumption is entirely to generic to have any basis on fact. Why would a delete and an insert cause a recompile? There is nothing about having two statements in a sproc that causes it to be optimized or not. That is a direct result of the quality of the statements, not the number of them. I can't think of an article that would address this. This sounds like a sql myth but one I have not heard before.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
July 25, 2012 at 10:48 am
Not automatically it won't. Nothing special about those two commands together.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
July 25, 2012 at 11:06 am
Thanks I want to say it was something I heard from SQL Saturday from Kevin Kline. Maybe I misunderstood. If you have a create table # or @ does that cause a recompile in an SP?
July 25, 2012 at 11:15 am
Temp table, yes. Table variable, no.
It's not a full recompile, just any statements that use the newly created or altered table
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply