Programming4us
         
 
 
SQL Server

SQL Server Programmability Objects

10/17/2010 5:50:29 PM
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.


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.

Other -----------------
- SQL Server 2005 : Data Querying and Reporting (part 2)
- SQL Server 2005 : Data Querying and Reporting (part 1)
- Configuring SQL Server 2008 : Instances vs Default Instance
- sp_configure and SQL Server Management Studio
- Configuring SQL Server 2008 : Database Mail
- Configuring SQL Server 2008 : Full-Text Indexing
- SQL Server 2008 : Working with Indexes
- SQL Server 2008 : Working with Constraints
- SQL Server 2008 : Working with Tables and Views
- SQL Server 2008 : Viewing and Modifying Data (part 3) - Creating Functions and Creating Triggers
- SQL Server 2008 : Viewing and Modifying Data (part 2) - Creating Stored Procedures
- SQL Server 2008 : Viewing and Modifying Data (part 1) - Creating Views
 
 
Most View
- SharePoint 2010 : Change the Name, Description, Icon, or URL of a Site
- BizTalk Server 2009 : Service-oriented schema patterns (part 3) - Building and applying reusable schema components
- Developing for Windows Phone and Xbox Live : GameComponents
- Cloud Security and Privacy : Internal Policy Compliance
- Windows Azure : Access Control Service Usage Scenarios (part 2)
- The Art of SEO : Content Optimization (part 2)
- SharePoint Server 2010 Business Intelligence Platform (part 1) - Business Intelligence Web Parts
- Active Directory Domain Services 2008: Apply a Password Settings Object to Users and Security Groups
- SharePoint 2010 : Create a New Survey
- Securing Exchange Server : Configure Message Hygiene Options (part 1) - Battle Unwanted Mail
Top 10
- Implementing Edge Services for an Exchange Server 2007 Environment : Utilizing the Basic Sender and Recipient Connection Filters (part 3) - Configuring Recipient Filtering
- Implementing Edge Services for an Exchange Server 2007 Environment : Utilizing the Basic Sender and Recipient Connection Filters (part 2)
- Implementing Edge Services for an Exchange Server 2007 Environment : Utilizing the Basic Sender and Recipient Connection Filters (part 1)
- Implementing Edge Services for an Exchange Server 2007 Environment : Installing and Configuring the Edge Transport Server Components
- What's New in SharePoint 2013 (part 7) - BCS
- What's New in SharePoint 2013 (part 6) - SEARCH
- What's New in SharePoint 2013 (part 6) - WEB CONTENT MANAGEMENT
- What's New in SharePoint 2013 (part 5) - ENTERPRISE CONTENT MANAGEMENT
- What's New in SharePoint 2013 (part 4) - WORKFLOWS
- What's New in SharePoint 2013 (part 3) - REMOTE EVENTS