Presents your 
SQL SERVER E-NEWSLETTER for December 12, 2002

<------------------------------------------->

PREFIX STORED PROCEDURES WITH SP_

System stored procedures are procedures that begin with the prefix sp_
and are located in the master database. Their primary purpose is for 
SQL
Server administration. These procedures are available after the
installation of SQL Server in the master database.

There are three types of stored procedures in SQL Server:

* system stored procedures
* extended stored procedures
* user defined stored procedures

Extended stored procedures extend the functionality of stored 
procedures
by turning methods into DLLs using various programming languages and
services. They are usually prefixed with xp_ or sp_. Two examples that 
come
with SQL Server are:

* xp_cmdshell
* sp_executesql

User defined stored procedures are procedures that can be created by
developers for data manipulation language (DML) queries and 
transactions.
User defined stored procedures are named by the database developer and 
must
adhere to the database object naming rules.

SQL Server automatically looks for procedures that are prefixed with 
sp_
in the master database. If there are user defined stored procedures
created with the same name as a system stored procedure, the 
user-defined
stored procedure will never be executed.

Secondly, SQL Server will look for the procedure based on database
and/or owner qualification qualifiers. SQL Server will also search for 
the
procedure in the current database by dbo when the procedure has no 
database
or owner qualifications.

If you run SQL Profiler against a user defined stored procedure created
in a database other than master and beginning with the prefix sp_, you
will find an additional SP:CacheMiss. This is because SQL Server 
expects to
find the compiled plan from the master database.

----------------------------------------