Designing Programming Objects

Introduction

The Designing Database Tables module provides you with the instruction and server hardware to develop your hands on skills in the defined topics. This module includes the following exercises:

1. Design T-SQL stored procedures

2. Design Views

3. Design T-SQL table-valued and scalar functions

4. Design Common Language Runtime (CLR) table-valued and scalar functions

5. Design CLR stored procedures, aggregates, and types

6. Evaluate special programming constructs

Exercise 1 - Design T-SQL stored procedures

In this exercise, you will perform the tasks required to program objects in SQL Server 2008. To understand this technology, please refer to your course material or read the material available at the following link:http://technet.microsoft.com/en-us/library/cc505879.aspx

Exercise 2 - Design Views

Common Table Expressions

The common table expression (CTE) is a temporary named result set that can be referenced within a SELECT, UPDATE, INSERT, or DELETE statement. A CTE can also be used in a CREATE VIEW statement, as a part of the view’s SELECT query. You can also add a CTE to the new MERGE statement.

SQL Server supports two types of CTEs:

• Non-Recursive
• Recursive

CTEs can be defined by adding a WITH clause before your SELECT, INSERT, UPDATE, DELETE, or MERGE statement. One or more CTEs can be included in the WITH clause.

The basic structure of a CTE is very simple:

WITH cte_name (col_a, col_b, col_c)

AS

(-- query definition)

SELECT col_a, col_b,etc, col_z

FROM cte_name

Non-Recursive CTE

A non-recursive CTE does not reference itself within the CTE. Non-recursive CTEs are simpler than recursive CTEs.

Exercise 3 - Design T-SQL table-valued and scalar functions

Inline table-valued functions vs. views

A SQL Server table-valued function is a user-defined function which returns a table data type as output.What makes SQL Server table-valued functions more useful in some situations than views or stored procedures is like every function, a t-sql table-valued function also accepts parameters.Also TVF's (table valued functions) can be referenced in SELECT queries just like a SQL Server database table.These two features plus creating complex t-sql programming logic in user defined functions make a table-valued function powerful than the SQL view and SQL stored procedure objects.

Exercise 4 - Design Common Language Runtime (CLR) table-valued and scalar functions

In this exercise, you will perform the tasks required to deploy CLR-based objects. To understand this task, refer to your course material or read the material available at the following link http://msdn.microsoft.com/en-us/library/ms345099(v=sql.105).aspx

Prior to SQL Server 2005 the SQL Server versions used to support only TSQL language. But with the advent of .Net framework it is now possible to use .Net framework languages to create and retrieve .Net framework based objects from SQL Server. Database objects written in managed code are called SQL Server Common Language Run (CLR) objects.

Exercise 5 - Design CLR stored procedures, aggregates, and types

CLR vs. T-SQL, ordered vs. non-ordered

User Defined Types

User Defined Types (UDT) are used to extend the normal type system in sql server and can only be written in SQL CLR. They allow for storage of CLR types in a database and grant the ability to store multiple data elements and expose various methods to work on and with the data. The normal SQL Server data types do not grant this extra functionality (although some of the out of the box types in SQL Server 2008 are UDTs, such as Hierarchy ID and the GeoSpatial types).

CLR UDT columns can be added to a table, used as a variable, used in a stored procedures, and in functions as you would use a normal column. However, CLR objects need to be created in each database that you would like to use them in, including tempdb.

User Defined Aggregates

User Defined Aggregates allow the aggregation of user defined types or custom aggregation in sql server and can also only be written in SQL CLR. For instance, you can sum an attribute of a UDT, or concatenate a string with input values.

User Defined Aggregates require the registered function to implement the required aggregation contract consisting of the SqlUserDefinedAggregate attribute and the aggregation contract methods (Init, Accumulate, Merge and Terminate).

Stored Procedures

Stored Procedures can be coded in both T-SQL and CLR. The CLR stored procedures themselves can return data in multiple ways, including output parameters, tabular results and messages. When setting the assembly into UNSAFE or EXTERNAL_ACCESS the code can access items that might otherwise be unavailable to SQL Server (barring extended stored procedures, etc).

There are many trade-offs for using CLR in place of T-SQL for stored procedures including the following (These are true for functions as well):

• You will have to measure to see if the procedural code benefits outweigh the data access penalties.
• CLR is usually approximately 2 – 3 times slower for data access than T-SQL.
• Procedural code, i.e. “Data Logic”, is usually faster in CLR.
• CLR Data uses SqlCommand – which uses a string and is thus dynamic. You should use parameterized queries when accessing data through SQL Command.
• CLR Data Access is not subject to dependency tracking
• CLR always breaks ownership chains
• In CLR Stored Procedures, you cannot wrap SQL Statements without direct table access.
• Unless using EXECUTE as OWNER or code signing
• SQLCLR code uses database resources.
• The possibility of the CLR code being moved to the client should be considered if client is local.
• If there is lots of data is to be retrieved the CLR code should reside on the server.
• SQLCLR creates competition between RDBMS and computation… more to keep track of for a DBA.

Assembly PERMISSION_SET

To prevent the code of a CLR assembly adversely affecting or compromising the systemSQL Server allows you to restrict the operationsof the assembly it is able to perform. This is done while registering the assembly in SQL Server by using permission sets.

Three permission sets are available in SQL Server 2008 for CLR assemblies:

• SAFE
• EXTERNALACCESS
• UNSAFE

With SAFE being the most restrictive and UNSAFE being highly restrictive.

Exercise 6 - Evaluate special programming constructs

In this exercise, you will perform the tasks required to program objects in SQL Server 2008. To understand this technology, please refer to your course material or read the material available at the following link:http://technet.microsoft.com/en-us/library/cc505879.aspx

Dynamic vs. prepared SQL (CREATE PROCEDURE… WITH EXECUTE AS) procedure

Dynamic SQL

Dynamic SQL is useful in interacting with data in a fashion that is unknown at the time the application is being developed. Dynamic SQL is built using a command string which is altered to provide the specific criteria for the calling instance . This gives very great flexibility in the data access layer, allowing for only the information that is needed at the time the call is being made to be returned, or only filtering where appropriate. However, Dynamic SQL comes with the cost of opening up the potential for SQL Injection as well as needing to go through the full cycle for query plan composition for every execution.

CREATE PROCEDURE … EXECUTE AS

Note that with Dynamic SQL, Ownership Chaining is broken as the dynamic SQL block is executed as though it were a stored procedure, which does not itself have an owner. When creating a procedure with the EXECUTE AS clause, or Signed Procedures with Certificates, you can provide the proper level of security to the procedure to execute under.

Protecting against SQL injection

One of the most important issues to deal with when administering an SQL server is to protect the application against SQL Injection. First and foremost, the principle of least privileges should be applied. However, that is more of a general rule than anything that is specifically dealing with SQL Injection. Next, you should attempt to validate all user input. You should almost certainly be escaping input with routines such as QUOTENAME and REPLACE. Furthermore, you should use parameterized statements.