which one is faster in performance either storedprocedure or

[sankar.nvss]

HI

which one is faster in performance either storedprocedure or function in SQL server and why.Could any please give me the in depth knowledge.




Thank you very much in advance

[DiskJunky]

I think I remember reading on the msdn that the stored procedure is faster as the caching is optimized for hit but you'd be as well off checking the msdn yourself to verify this - it was some time ago that I came across it

[ModusPonens]

Yes, a stored procedure is faster because the sql has already been run through the optimizer and compiled. Having said that, the optimizer looks at the current state of the tables being used and gives you the best execution; over time that compiled procedure may become out dated. It is a good idea to recompile the stored procs from time to time.

The greatest benefit to stored procs is they add a level of security; the proc accepts parameters which are declared as types which can help stop an injection attack. Plus stored proc can execute multiple routines and check parameters for content before using them.