Lesson 4: Microsoft SQL Server

Features of SQL Server

SQL Server includes the following features: stored procedures, Transact-SQL, triggers, and server-side cursors .

Stored Procedures

Stored procedures are commonly used commands, such as queries, that are saved permanently in the database. Because a stored procedure resides in the database, different clients can execute the procedure independently. This also allows you to manage procedures centrally . In addition, stored procedures are partially processed when they are created. This improves the performance of both server and client applications.

Transact-SQL

The Transact-SQL scripting language is used to create stored procedures in SQL Server. Transact-SQL has greater capabilities than the standard Structured Query Language (SQL). Stored procedures written using Transact-SQL can return rows of data, error codes, or calculated values (called output parameters .)

Triggers

A trigger is a special kind of stored procedure that goes into effect when data is modified in a specified table. Triggers often are created to enforce the consistency of a business rule among logically related data in different tables. For example, a trigger can e-mail a purchasing manager whenever a vendor is late with their deliveries.

Triggers are server-side tools that are transparent to the client. Up to three triggers can be associated with each table in a Microsoft SQL Server database. Triggers are automatic, which means that they work no matter what causes the data modification.

Server-side Cursors

SQL Server was the first client/server database to support server-side cursors. When requesting records from a database, the data was traditionally copied to the client machine where local resourcesfor example, RAM and hard drive spacewere used to store the information while the user navigated or modified the records. With server-side cursors, only the records being manipulated by the client are copied from the server. This means that for queries involving large result sets, the data remains on the server. Generally, this improves performance; however, server resources can be exhausted if too many clients create large result sets at the same time.

Lesson Summary

SQL Server is the database component of BackOffice, and is used for developing advanced client/server applications. It allows you to create stored procedures, which are commonly executed commands that can be permanently stored in the database. It also features triggers, a type of stored procedure that executes when data is modified in a database. Triggers enforce the consistency of a business rule among logically related data in different tables. SQL Server also supports server-side cursors.