Sub-Programs in SQL Server  :- 

Named blocks:

  1. Procedures
  2. Functions
  3. Db triggers

procedures & functions are also called “sub-programs”

subprograms means called in main program.

Difference between Anonymous and named:

Anonymous named
  • Block without name
  • anononymous blocks are saved in “operating system file”
  • These blocks are not secured. 
  • These blocks can’t be re-used. 
  • Every time compile & executed.  
  • blocks with name.
  • but the named blocks are saved in “database”
  • but named blocks are secured.
  • but named blocks can be re-used.
  • Named blocks are one time compilation And every time execution

Main advantages of creating procedures and functions :-

(I) Reusability

(II) Pre-compiling