04 - SP (DDL)

Hi,

 

Here we'll cover below –

 

-          Table

-          Constraints

-          View

-          SP

-          Functions

-          Index

-          Triggers

 

 

Stored Procedures

 

·          Produce faster execution times than ad hoc queries

·          The key to a stored procedure's good performance is SQL Server's ability to store a Pre-Compiled Execution Plan for commands used within the stored procedure

·          Increased Network performance -  As the Transact-SQL statements from the stored procedure are invoked on the server itself, reducing Transact-SQL calls over the network. This leaves only the stored procedure call and parameters and returned results (when applicable) to be transferred over the network.

·          Force a Stored Procedure to Recompile - When table data referenced from within a stored procedure undergoes significant changes, or new indexes are added to the referenced tables.

 

CREATE PROCEDURE <procedure_name> [ ; number] <@param1> <datatype_for_param1> [VARYING] = <default_value_for_param1> [OUTPUT], <@param_N> <datatype_for_param_N> [VARYING] = <default_value_for_param_N> [OUTPUT]   [ WITH { RECOMPILE | ENCRYPTION | RECOMPILE, ENCRYPTION } ] [ FOR REPLICATION ] AS <SQL_commands>

CREATE PROCEDURE pr_Insert_Books @BookName varchar(255), @AuthorId int AS INSERT Books (vchBookName, iAuthorId) VALUES (@BookName, @AuthorId) GO

 

 

-          WITH RECOMPILE specifies that the execution plan for the procedure is recompiled each time it is executed.

-          WITH ENCRYPTION encrypts the stored procedure definition, so it cannot be read in Enterprise Manager or within syscomments.

 

 

Extended Stored Procedures

 

·          Allows to extend the functionality of SQL Server by referencing functionality from dynamic link libraries (DLLs).

·          Extended stored procedures are defined by referencing a DLL file and are not defined with Transact-SQL statements like a regular stored procedure.

·          Because a DLL is loaded and used from within SQL Server, there is an increased risk of memory leaks or performance issues (depending on the DLL). If a DLL misbehaves, SQL Server can crash.

 

sp_addextendedproc @functname = <'procedurename'>,

@dllname = <'dll_name'>

 

            e.g. adds the extended stored procedure xp_scrubdata

           

USE master

EXEC sp_addextendedproc xp_scrubdata, 'scrubdata.dll'

 

 

·          To reference the extended stored procedure, call it as you would a regular stored procedure, including any parameters that may be required:

 

EXEC master.dbo.xp_scrubdata

 

·          When an extended stored procedure is executed, the DLL file is loaded into memory, and is not removed until the SQL Server instance is restarted. To force the DLL from memory without restarting the SQL Server instance, use the DBCC <dllname> (FREE) command.

 

DBCC xp_subdirs (FREE)

 

 

 

Tips on Stored Procedures

 

·          Use SET NOCOUNT ON at the beginning of your stored procedure to minimize the number of messages that are sent back to the client that called the stored procedure.

·          Put all temporary table definitions at the beginning of your procedure, instead of interspersing throughout the procedure. This spreading out of DDL and DML is called interleaving. When a temporary object

·          is referenced for the first time, SQL Server recompiles the stored procedure. Hence by placing all DDL definitions at the beginning of the procedure, this first recompile will capture all temporary tables that

·          will be created for the entire procedure, rather than recompiling each time a new temporary procedure is encountered.

 

 

Mark a Stored Procedure to Recompile

 

·          When table data referenced from within a stored procedure undergoes significant changes, or new indexes are added to the referenced tables, your current stored procedure does not executes effieciently.

·          By recompiling a stored procedure, you optimize the execution plan for the current database state.

·          You can use the system stored procedure sp_recompile to mark a stored procedure for recompilation the next time someone executes it.

 

EXEC sp_recompile @objname = 'pr_Calcdate'

 

 

User-Defined Functions

 

·          Were introduced in SQL Server 2000.

·          Types of UDFs

o         Scalar UDFs –

§          Return one value, and cannot return table data types.

 

o         Inline UDFs

§          Return a table data type, and do not explicitly define the returned table, but rather use a single SELECT statement for defining the returned rows and columns

 

o         Multistatement UDFs

§           

 

 

Stored Procedures	User-Defined Functions
Accept parameters	Same
Can contain output parameters	Cannot contain output parameters
Optional	Must always return a scalar value or a table
No	Can be used within your queries (Select, Where & From)  and DML statements.   Used as a substitute for a sub-query.
	A function call can also be joined with other tables.

 

 

Hope this helps.

 

Thanks & Regards,

Arun Manglick || Tech Lead