Server Fault is a question and answer site for system and network administrators. It's 100% free, no registration required.

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

Will the below stored procedure be recompiled on every run, because of the dynamic SQL within it? Are there any serious performance hits with this method?

 DECLARE @column varchar(3)
 SELECT @column = 'd' + CAST(DAY(GETDATE()) AS char)

 EXEC ('SELECT ' + @column + ' FROM t1')
share|improve this question
up vote 1 down vote accepted

It is better to avoid dynamic SQL if possible, is true. For a more detailed discussion, see this SO thread. However, to address specifically your question: the EXEC ('SELECT d21 FROM t1') statement would behave exactly as if a request from the client containing the script SELECT d21 FROM t1 was received by the server. If the plan is already in the cache, it will be executed from the cache (sparing some details like metadata version changes etc etc). If not, it will be compiled, a new plan created, and run. Note that a query like SELECT field FROM Table is what is called a 'trivial plan query', the cost of generating a plan for something like this is completely negligible on most systems.

share|improve this answer

No, the procedure should not be recompiled every run and even if it would, the performance of the call would not be lot worse as the dynamic SQL already slows down enough. This part of the procedure is parsed and executed every run.

share|improve this answer

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.