Shared Flashcard Set

Details

Stored Procedures Lesson 9
This concerns Stored Procedures
33
Computer Science
Undergraduate 4
09/28/2013

Additional Computer Science Flashcards

 


 

Cards

Term
cache
Definition

a part of RAM

 

holds compiled code like T-SQL

of a stored procedure

Term
compile
Definition

convert high level code

to

machine language

Term
stored procedure
Definition

precompiled collection (already ready)

of T-SQL code

Term
System Stored Procedures
Definition

sp_......

 

stored in the Master (not model)

and MSDB databases

Term
User stored procedures
Definition
Term
Extended store procedures
Definition

xp_...

 

implemented as .dll

Term
Stored procedure examples
Definition

sp_add_job

sp_dboption

sp_executesql

sp_help

sp_helpdb

sp_configure

sp_who

sp_xml_preparedocument

xp_cmdshell

xp_sendmail

Term
sp_helptext
Definition
shows you what a store procedure looks like
Term
WITH ENCRYPTION
Definition

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

Term
WITH RECOMPILE
Definition

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"

Term
WITH EXECUTE AS
Definition

This clause

allows sp to be run under any

designated user's security context

 

You only need to give permission to the sp itself

Term

Adding Input Parameters

in sp

Definition

ALTER PROC

or

ALTER PROCEDURE Schema.Name_of_Stored_Procedure

@MinLength int=-1

--this sets default value of -1

Term
Using OUTPUT
Definition

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

Term
Validation Code
Definition

IF (@MinLength < 1)

BEGIN

RAISERROR ('Invalid value', 12, 1)

RETURN 1

END 

 

Term
What needs to be done when you use CLR produre?
Definition

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

Term
What do you need to create a CLR sp?
Definition

A development tool

such as Visual Studio

Term
What does sys.assemblies system view do?
Definition

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

Term
What does enabling the server for CLR Support do?
Definition

it allows you to manage CLR Objects

 

sp_configure 'clr_enabled', 1

reconfigure

Term
BEGIN TRAN
Definition
a transaction specified inside of a TRY block of code
Term
COMMIT TRAN
Definition

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

Term
ROLLBACK TRAN
Definition

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

Term
SET XACT_ABORT ON
Definition

this causes transaction automatically to

roll back

 

The exception is in the CATCH block of code

Term
How do you drop an sp?
Definition
DROP PROC sp_name
Term
How do you change an sp?
Definition
ALTER PROC
Term
The four stages of compilation process
Definition
  • Parsing
  • Normalization
  • Compilation
  • Optimization
Term
Parsing
Definition
checks for syntax errors and prepares optimization
Term
Normalization
Definition
checks all object & column names in the query are correct
Term
Compilation
Definition

builds the execution plan

creates query graphs for use by

Query Optimizer

Term
Optimization
Definition
decides expense of different processing options
Term
recompile
Definition

it is another form of "reoptimization"

 

When your WHERE clause changes 

with each new query

Term
Three options in recompiling
Definition

 

When you examine the Execution Plan in Query Editor or suspect performance deficiency, you have three options:

 

The sp_recompile system stored procedure forces a recompile next time run:

 

Use the WITH RECOMPILE option in the CREATE PROCEDURE statement.

 

Use the WITH RECOMPILE option with the EXECUTE statement:

 

Term
sp_recompile system stored procedure example
Definition

USE AdventureWorks

EXECUTE sp_recompile Production.LargestListPrice

Term

WITH RECOMPILE option

in

EXECUTE statement

example

Definition

USE AdventureWorks

EXEC Production.LargestListPrice WITH RECOMPILE

Supporting users have an ad free experience!