a part of RAM

holds compiled code like T-SQL

of a stored procedure

convert high level code

to

machine language

precompiled collection (already ready)

of T-SQL code

sp_......

stored in the Master (not model)

and MSDB databases

xp_...

implemented as .dll

sp_add_job

sp_dboption

sp_executesql

sp_help

sp_helpdb

sp_configure

sp_who

sp_xml_preparedocument

xp_cmdshell

xp_sendmail

When you use the stored procedure for help, sp_HelpText system stored procedure

and you wish to guard your

Intellectual Property (IP)

use this option

This option indicates you don't want

the execution plan

cached in memory

Once it is called this will make the sp already compiled

"recompiled when it is called"

This clause

allows sp to be run under any

designated user's security context

You only need to give permission to the sp itself

Adding Input Parameters

in sp

ALTER PROC

or

ALTER PROCEDURE Schema.Name_of_Stored_Procedure

@MinLength int=-1

--this sets default value of -1

CREATE PROCEDURE HumanResources.AddDepartment

@Name nvarchar(30), @Groupname nvarchar(30),

@DeptID smallint OUTPUT

AS

....

This permits any changes to parameter from sp's execution to be retained even after

sp finishes its execution

This is used in CREATE PROCEDURE and EXECUTE statements

IF (@MinLength < 1)

BEGIN

RAISERROR ('Invalid value', 12, 1)

RETURN 1

END 

You have to get its assembly cataloged in SQL Server

CREATE ASSEMBLY

The method within assembly needs

to be exposed to

SQL Server sp

CREATE PROCEDURE

A development tool

such as Visual Studio

When you deploy a project to SQL Server from CLR that you wrote, the assembly or .dll file will be cataloged in the SQL Database

They become objects and become displayable by Querying this view: sys.assemblies

it allows you to manage CLR Objects

sp_configure 'clr_enabled', 1

reconfigure

Used inside of TRY Block

and if an error results

this is skipped

and then goes to CATCH which must immediately follow a TRY block of code

This is placed inside CATCH block of code

it is used to maintain integrity of the data

if an error takes place in a TRY block of code having  BEGIN TRAN and COMMIT TRAN

this causes transaction automatically to

roll back

The exception is in the CATCH block of code

• Parsing
• Normalization
• Compilation
• Optimization

builds the execution plan

creates query graphs for use by

Query Optimizer

it is another form of "reoptimization"

When your WHERE clause changes 

with each new query

USE AdventureWorks

EXECUTE sp_recompile Production.LargestListPrice

WITH RECOMPILE option

in

EXECUTE statement

example

USE AdventureWorks

EXEC Production.LargestListPrice WITH RECOMPILE