T-SQL Enhancements in SQL Server 2005

In this tutorial you will learn about T-SQL Enhancements in SQL Server 2005 – Data Definition Language Statements, .NET framework integration, Full Text Search, Index transact SQL Statements, Security Transact SQL Enhancements, Service Broker T-SQL statements, Synonym Transact SQL Statements, Table and Index Partitioning Transact SQL Enhancements, Table Transact SQL Enhancements, Triggers and Event Notification Transact SQL Enhancements.

T-SQL enhancements in SQL Server 2005 range from alternative mechanisms for transaction isolation to declarative support for hierarchical queries. The statement level recompilation also improves existing T-SQL applications that were written before 2005. The improvements to Transact SQL can be divided include the following:

• Data Definition Language Statements
• Data Manipulation Language Statements
• Database Console Commands (DBCC) Statements
• Metadata
• Other Statements
• Replication System Stored Procedures
• Sample Database Enhancements
• Transact-SQL Data Types

Data Definition Language Statements:

DDL statements have been extended to apply to the following categories:

1. Microsoft .NET Framework Transact-SQL Enhancements
2. Full Text Transact SQL Enhancements
3. Index Transact SQL Enhancements
4. Security Transact SQL Enhancements
5. Service Broker Transact SQL Enhancements
6. Synonym Transact SQL Enhancements
7. Table and Index Partitioning Transact SQL Enhancements
8. Table Transact SQL Enhancements
9. Triggers and Event Notification Transact SQL Enhancements.

.NET framework integration:

The greatest advantage of this is the ability to create stored procedures, triggers, functions, aggregate functions and types in managed code. The CREATE ASSEMBLY statement registers a .NET framework assembly as an object inside an instance of SQL server 2005. Common language runtime functions, stored procedures, triggers, user defined aggregates and user defined types can be then created. ALTER ASSEMBLY enables the altering of an assembly by modifying its properties and adding or removing files associated with it. DROP ASSEMBLY removes an assembly and all its associated files from the current database. CREATE AGGREGATE creates a user defined aggregate function in SQL Server and the implementation is defined in a class of an assembly in .NET framework. DROP AGGREGATE removes the user defined aggregate. CREATE TYPE creates a data type in SQL server or a CLR user defined data type in SQL Server. The implementation of it is defined in a class of an assembly in the .NET Framework. DROP TYPE drops the type. EXECUTE AS controls the user accounts in SQL server and validates permissions on database objects that are referenced by a stored procedure or user defined function.

To enhance the integration with the CLR a number of new commands were introduced. CREATE PROCEDURE enables creating of CLR procedures. The EXECUTE AS clause can be added to specify the context in which the stored procedure is executed. ALTER PROCEDURE adds the EXECUTE AS clause to specify the context in which the stored procedure is executed. CREATE FUNCTION enables creating CLR functions and adds EXECUTE AS clause to specify the context in which the function is executed. ALTER FUNCTION adds EXECUTE AS clause to specify the context in which the function is executed. CREATE TRIGGER enables the creating of CLR triggers.

Full Text Search

The full text search features of Transact SQL DDL have been enhanced to implement and manage full text catalogs and indexes. Backward compatibility is ensured by support provided for stored procedures. CREATE FULL TEXT CATALOG creates a full text catalog for the database. ALTER FULL TEXT CATALOG is used for changing the properties of a full text catalog. Catalogs can be rebuilt with or without accent sensitivity, indexes of catalogs can be reorganized or the catalog can be made the default. DROPFULLTEXTCATALOG removes a full text catalog from a database.

CREATE FULL TEXT INDEX creates full text index on one or more columns of a table in a database. ALTER FULL TEXT INDEX alters the properties of a full text index. This statement can be used to enable or disable full text indexing on a table or to add or drop a column of a full text index or start, with full, incremental or update or stop population of a full text index.

Index transact SQL Statements

Relational and XML indexes can be modified using new DDL statements that have come in with SQL Server 2005. The CREATE INDEX statement has been enhanced to support XML index syntax, partitioning and included columns. The ONLINE option has been built to allow concurrent users access to underlying data while operations are being performed on indexes. ALTERINDEX statement modifies an existing statement or view index by disabling, rebuilding or reorganizing the index; or setting options on the index.

Other options are ALLOW_ROW_LOCKS, ALLOW_COLUMN_LOCKS and MAXDOP. DROPINDEX adds new format for specifying index and table names. The MOVE TO clause can be added and this enables moving data from one clustered index to another file group or partition scheme. The options available are ONLINE AND MAXDOP.

Security Transact SQL Enhancements

Access control can be set using the new DDL statements that have been introduced for this purpose. CREATEAPPLICATIONROLE adds a new application role to the current database. ALTERAPPLICATIONROLE changes the properties of an application role. DROPAPPLICATIONROLE removes an application role from the database.

CREATECERTIFICATE add as new certificate to the database.
ALTERCERTIFICATE adds a private key to a certificate or changes the owner of a certificate.
DROPCERTIFICATE removes the certificate from the database.
CREATELOGIN creates a new Microsoft windows or SQL Server login account.
ALTERLOGIN changes the properties of a Microsoft windows or SQL server login account. DROPLOGIN removes a Microsoft Windows or SQL server login account. and
CREATEROLE as the name suggests allows alteration or the creation of roles.
DROPROLE deletes a role. Schemas can be created, altered and dropped using the CREATESCHEMA, ALTERSCHEMA and DROPSCHEMA commands. Similarly
CREATEUSER, ALTERUSER and DROPUSER commands are used to create, alter or drop users in the database.

Service Broker T-SQL statements

Database intensive distributed applications need to be secure, reliable and scalable. The Service Broker technology allows distributed application components send and receive messages or queue the messages securely until they are processed by the receiving component. A number of DDL statements have been introduced to facilitate the use of the Service Broker. CREATE CONTRACT creates a new contract in the database. DROP CONTRACT deletes an existing contract. ALTER MESSAGE TYPE changes the properties of the message type. The complementary CREATE MESSAGE TYPE and DROP MESSAGE TYPE create or delete the message type. Similar commands have been introduced for Queues, remote service, service and routes.

Synonym Transact SQL Statements

Schema scoped objects are also known as synonym. Synonyms allow client applications to use a single part name to reference the base object. CREATE SYNONYM and DROP SYNONYM are two statements that have been introduced to assist in this process.

Table and Index Partitioning Transact SQL Enhancements

The data of partitioned tables and indexes are spread over one or more filegroups in a database. The process makes large tables and indexes more manageable and improves efficiency as subsets of data can be accessed while the integrity of the whole is maintained. Query results are also displayed faster.

The tables and indexes are partitioned horizontally to enable mapping of groups of rows into individual partitions. All queries are then executed as if the partitioned table or index is a single entity.

The new DDL statements introduced by SQL Server 2005 include: CREATE PARTITION FUNCTION- this function is created in the current database and maps the rows of the table or index into partitions based on the values of one or more columns.

ALTER PARTITION FUNCTION enables changes to be made to the partition function and any tables and indexes that are dependent on it. This statement can be used to split a partition of a partitioned table or index into partitions or merge rows of a partitioned table or index into one less partition.

DROPT PARTITION FUNCTION removes a partition function from the current database.

CREATE PARTITION SCHEME is a statement used to create a scheme in the current database that maps the partitions of a partitioned table or index to filegroups.

ALTER PARTITION SCHEME enables alterations to be made to the existing partition scheme. Additional partitions can also be made using this statement.

DROP PARTITION SCHEME removes a partition scheme from the current database.

$PARTITION returns the partition number into which a set of partitioning column values have been mapped for a specified partition function.

In addition to the above set of statements, SQL Server 2005 enhances the partition and index functions by enhancing existing statements. The CREATE TABLE and ALTER TABLE statements now support the creation and alteration of partitioned tables. Similarly the CREATE INDEX ALTER INDEX and DROP INDEX statements support the creation, alteration and dropping of indexes in partitioned tables.

Table Transact SQL Enhancements

Table related DDL statements have been enhanced to cater to ease of database development. CREATE TABLE function now allows for persistent computed columns. The SET NULL and the SET DEFAULT functions can be used with ON UPDATE and ON DELETE clauses. ‘max’ can be specified for storage of varchar, nvarchar and varbinary data. Xml columns can be created with an option to type the column to an xml schema. The ALTER TABLE statement has been enhanced to similarly to support all the features included in the CREATE TABLE statement.

Triggers and Event Notification Transact SQL Enhancements

A trigger is a block of SQL statements that are executed based on the fact that there has been an alteration (INSERT, UPDATE, or DELETE) to a table or on a view. In previous versions of SQL Server, the statements had to be written in T-SQL, but in version 2005, they can also be written using .NET languages. The triggers are fired based on action statements (DML) in the database, but in SQL Server 2005 triggers can be created for DDL statements as well as DML.

A number of new DDL triggers and event notifications have been introduced. Like the standard triggers they execute stored procedures but unlike them they execute them in response to DDL statements. These are mainly used for administrative tasks such as auditing, and regulating database operations.

The syntax for a DDL trigger is as under:

CREATE TRIGGER trigger_name
ON { ALL SERVER | DATABASE }
[ WITH ENCRYPTION ]
{ FOR | AFTER } { event_type [ ,…n ] | DDL_DATABASE_LEVEL_EVENTS }
[ WITH APPEND ]
[ NOT FOR REPLICATION ]
{ AS
{ sql_statement [ …n ] | EXTERNAL NAME < method specifier > }
}
< method_specifier > ::=
assembly_name:class_name[::method_name]

The differences between the DML trigger and the DML trigger needs to be noted.

• The ON clause in a DDL trigger refers to either the scope of the whole database server (ALL SERVER) or the current database (DATABASE).
• A DDL trigger cannot be an INSTEAD OF trigger.
• The event for which the trigger fires is defined in the event_type argument, which for several events is a comma-delimited list. Alternatively, the blanket argument DDL_DATABASE_LEVEL_EVENTS can be used.

The statements that have been enhanced to support new Trigger features of SQL server 2005 are CREATE TRIGGER, ALTER TRIGGER, DROP TRIGGER and event data. The functions create, alter or remove a DDL trigger from the current database. Event data returns information about server and database events and is used inside the body of a DDL trigger or event notification.

Event notifications are executed in response to DDL and DML statements and trace events. They do not execute stored procedures. They send the information about a server event to the Service Broker and can be used to log and review changes or activity occurring on the database. CREATE EVENT NOTIFICATION and DROP EVENT NOTIFICATION are two new event statements that have been introduced. As the names suggest they create or delete the event notification.

In the next section of the tutorial we shall examine the changes that have been introduced in Data Manipulation language.