Can anyone tell me why Microsoft SQL Server stops executing a stored procedure when it is recompiled?  This is dangerous behavior unlike any other software code release system I’ve ever seen.   Through all my years of SQL development this thought never crossed my mind.  In any other development platform, you compile your code to a dll or exe and copy it to where it needs to exist.  If an existing copy is running, the operating system won’t let the file be overwritten.  I’ve always expected a similar behavior in SQL Server.  I thought, if a procedure is executing, SQL Server will continue to use the code that is in memory while a recompile of the procedure would just cause the next invocation to use the new version.  But a few years ago a colleague posed that question right before we deployed a changed procedure.  I didn’t even think twice about it (how preposterous that would be).  I answered, “No way!  That would be retarded!”  Well recently, I was bitten by this exact behavior.  An important process was running and I had a minor update to release.  I went ahead and released my change while the procedure was running and I discovered that only half of the process that was running completed.  Looking through our audit logs I was able to see that execution stopped exactly at the time of the recompile.  I have since been able to reproduce this behavior a few times.  I’m still puzzled as to why the Microsoft’s SQL Server development team would allow such dangerous behavior.