SQL Server Programmability Objects

A set of objects stored within a database are of particular interest to a database developer. These objects allow coders to add their own capabilities and functionality to a database. These SQL Server 2005 objects offer state-of-the-art functionality (compared to rules and defaults, which are being phased out).

The coding environment in SQL Server 2005 is much more like what programmers expected than that in previous versions of SQL Server. CLR support, which is new to this version, offers techniques through which SQL procedures and functions can be written in languages other than T-SQL.

Stored Procedures

An important execution element that is stored within the context of a database is a stored procedure. A stored procedure is a set of T-SQL statements that can be saved as a database object for future and repeated executions. With stored procedures, you can enable a lot of the development and processing to be performed on the server, producing much more efficient and lightweight front-end applications. Any commands that can be entered via SQL Query tools can be included in a stored procedure.

Many system-stored procedures have already been created and are available when you install SQL Server. Extended stored procedures, which enable DLL files to be accessed from the operating system, are created upon installation and are present in the master database.

Extended stored procedures, like many of the system-stored procedures, are loaded automatically when you install SQL Server. Extended stored procedures access DLL files stored on the machine to enable the calling of the functions contained in the DLLs from within a SQL Server application. You can add to this set of procedures stored in the master database by using the sp_addextendedproc procedure, as shown here:

sp_addextendedproc 'MyFunction', 'MyFunctionSet.DLL' 

Stored procedures and views can both be used as part of a broader security plan.

Creating CHECK Constraints

A CHECK constraint is one of several mechanisms that can be used to prevent incorrect data from entering a system. You can apply restrictions on data entry at the table or column level through the use of a CHECK constraint. You might also apply more than a single check to any one column, in which case the checks are evaluated in the order in which they were created.

A CHECK constraint represents any Boolean expression that is applied to the data to determine whether the data meets the criteria of the check. The advantage of using a check is that it is applied to the data before it enters the system. However, CHECK constraints have less functionality than mechanisms such as stored procedures or triggers.

One use for a CHECK constraint is to ensure that a value entered meets given criteria, based on another value entered. A table-level CHECK constraint is defined at the bottom of the ALTER/CREATE TABLE statement, unlike a COLUMN CHECK constraint, which is defined as part of a column definition. For example, when a due date entered must be at least 30 days beyond an invoice date, you could define a table-level constraint this way:

(DueDate - InvoiceDate) >= 30 

You might use a column-level check to ensure that data is within acceptable ranges, as in the following:

InvoiceAmount >= 1 AND InvoiceAmount <= 25000 

You can also use a check to define the pattern or format in which data values are entered. You might, for example, want an invoice number to have an alphabetic character in the first position, followed by five numeric values, in which case the check might look similar to the following:

InvoiceNumber LIKE '[A-Z][0-9][0-9][0-9][0-9][0-9]' 

Finally, you might want to apply a check when an entry must be from a range of number choices within a list. An inventory item that must be one of a series of category choices might look similar to this:

ProductCategory IN ('HARDWARE', 'SOFTWARE', 'SERVICE') 

A COLUMN CHECK (or other constraint) is stated as a portion of the column definition itself and applies only to the column where it is defined. A TABLE CHECK (or other constraint), on the other hand, is defined independently of any column, can be applied to more than one column, and must be used if more than one column is included in the constraint.

The following is an example of a table definition that is to define restrictions to a single column (for example, minimum quantity ordered is 50), as well as a table constraint (for example, the date on which a part is required must be later than when it is ordered):

CREATE TABLE ProductOrderLine       (ProductLineKey  BigInt,        OrderMatchKey   BigInt,        ProductOrdered  Char(6),        QtyOrdered      BigInt          CONSTRAINT Over50 CHECK (QtyOrdered > 50),        OrderDate       DateTime,        RequiredDate    DateTime,          CONSTRAINT CK_Date CHECK (RequiredDate > OrderDate)) 

Usually, a single table definition provides clauses for key definition, indexing, and other elements that have been left out of the previous definition to focus more closely on the use of CHECK constraints.

As you can see, constraints come in all shapes and sizes, and they control table content, inter-table relationships, and validity of data. The following sections tie up a few loose ends in order to give a full perspective on objects.

Creating Your Own Functions

Microsoft SQL Server 2005 has a variety of types of UDFs. A UDF is a single statement or routine that can accept parameter information, perform a defined process, and return the result of the process. The return value can either be a single scalar value or a result set.

A UDF has a two-part header/body structure. The header of the function defines the function name, input parameter, and return parameter. The body defines the activity that the function is to perform. When you create the function, the header is everything leading up to the AS keyword. The body is the trailing portion of the CREATE FUNTION statement that follows the AS keyword.

The following function definition (which calculates the cubed value for any number that is input) illustrates the components of a function definition:

CREATE FUNCTION dbo.Cubit            -- function name (@Numb int)                          -- input parameter name and data type RETURNS int                          -- return parameter data type AS BEGIN                                -- begin body definition DECLARE @Result int                  -- declaration of any variables SELECT @Result = @Numb*@Numb*@Numb   -- action performed RETURN @Result                       -- Answer returned from call END                                  -- end body definition 

The function call would look like this:

SELECT dbo.Cubit(4) 

A UDF can be scalar valued or table valued:

• Scalar-valued function A scalar-valued function defines a single piece of data in the RETURNS clause. For an inline scalar function, the function body is the result of a single statement. For a multistatement scalar function, the function body is defined in a BEGIN...END block. The data returned from the function cannot be a Text, Ntext, Image, Cursor, or Timestamp value.

• Table-valued function A table-valued function uses the TABLE data type in the RETURNS clause. The function returns a set of records or more than one result line.

English Query Capabilities with Full-Text Catalogs

Full-Text Search is a program that runs as a service to SQL Server. You can use Full-Text Search in conjunction with all sorts of information from all the various Microsoft BackOffice products. Full-text catalogs and indexes are not stored in a SQL Server database; they are stored in separate files managed by the service.

Full-text indexes are special indexes that efficiently track the words you're looking for in a table. They help in enabling special searching functions that differ from regular indexes. Full-text indexes are not automatically updated, and they reside in a storage space called the full-text catalog.

Exam Alert

Full-Text Search questions have always been a favorite topic in Microsoft database exams. Expect to see one or two questions about the use of Full-Text Search and the command language surrounding the feature on the 70-431 exam.

With a full-text index, you can perform wildcard searches (using Full-Text Search) to locate words in close proximity. All full-text indexes are by default placed in a single full-text catalog. Each server, at its apex, can store 256 full-text catalogs.

The full-text catalog files are not recovered during a SQL Server recovery. They also cannot be backed up and restored by using the T-SQL BACKUP and RESTORE statements. The full-text catalogs must be resynchronized separately after a recovery or restore operation. The full-text catalog files are accessible only to the Microsoft search service and the Windows NT or Windows 2000 system administrator.

To enable full-text searches, you can run the Full-Text Indexing Wizard, which enables you to manage and create full-text indexes. Note that you can create full-text indexes only on columns that contain just text. Full-text indexes are not automatically updated, which means you need to automate the process of updating by setting a job or performing a manual administrative task.

Objects with Security Context

Permissions can be granted for object use or denied, for that matter, to anyone who connects to the server. Logins are created or denied at the server level. A login to the server has the level of permission determined by the groups, roles, and permissions allocated to the login. A login gains access to any database that has a user associated with the login, that has the guest user enabled, or that has a role that permits access directly or through an application.

Server roles exist to identify processes at the server level and allow logins to be associated with performing the processes allocated to a role. Individuals or groups can be associated to the roles to privileges associated to the role. Server roles are fixed; that is to say, you cannot create your own roles and role definitions.

Database roles are similar but have scope solely within the database in which they exist. Database roles are not fixed. Roles can be created for any purpose you want. You can create a special kind of database role, the application role, to gain more control over permissions that exist while performing operations through specific applications.

Credentials, new in SQL Server 2005, contain authentication data to connect to a resource outside the server. A credential is usually a Windows login and its associated password. Users who connect using SQL Authentication can use credentials to connect to other resources outside the server that might be needed for some SQL Server processes. A credential can be mapped to one or more SQL Server logins, but a login can be mapped to only one credential.