Logo

Navigation
  • Home
  • Services
    • ERP Solutions
    • Implementation Solutions
    • Support and Maintenance Solutions
    • Custom Solutions
    • Upgrade Solutions
    • Training and Mentoring
    • Web Solutions
    • Production Support
    • Architecture Designing
    • Independent Validation and Testing Services
    • Infrastructure Management
  • Expertise
    • Microsoft Development Expertise
    • Mobile Development
    • SQL Server Database and BI
    • SAP BI, SAP Hana, SAP BO
    • Oracle and BI
    • Oracle RAC
  • Technical Training
    • Learn Data Management
      • Business Intelligence
      • Data Mining
      • Data Modeling
      • Data Warehousing
      • Disaster Recovery
    • Learn Concepts
      • Application Development
      • Client Server
      • Cloud Computing Tutorials
      • Cluster Computing
      • CRM Tutorial
      • EDI Tutorials
      • ERP Tutorials
      • NLP
      • OOPS
      • Concepts
      • SOA Tutorial
      • Supply Chain
      • Technology Trends
      • UML
      • Virtualization
      • Web 2.0
    • Learn Java
      • JavaScript Tutorial
      • JSP Tutorials
      • J2EE
    • Learn Microsoft
      • MSAS
      • ASP.NET
      • ASP.NET 2.0
      • C Sharp
      • MS Project Training
      • Silverlight
      • SQL Server 2005
      • VB.NET 2005
    • Learn Networking
      • Networking
      • Wireless
    • Learn Oracle
      • Oracle 10g
      • PL/SQL
      • Oracle 11g Tutorials
      • Oracle 9i
      • Oracle Apps
    • Learn Programming
      • Ajax Tutorial
      • C Language
      • C++ Tutorials
      • CSS Tutorial
      • CSS3 Tutorial
      • JavaScript Tutorial
      • jQuery Tutorial
      • MainFrame
      • PHP Tutorial
      • VBScript Tutorial
      • XML Tutorial
    • Learn Software Testing
      • Software Testing Types
      • SQA
      • Testing
  • Career Training
    • Career Improvement
      • Career Articles
      • Certification Articles
      • Conflict Management
      • Core Skills
      • Decision Making
      • Entrepreneurship
      • Goal Setting
      • Life Skills
      • Performance Development
      • Personal Excellence
      • Personality Development
      • Problem Solving
      • Relationship Management
      • Self Confidence
      • Self Supervision
      • Social Networking
      • Strategic Planning
      • Time Management
    • Education Help
      • Career Tracks
      • Essay Writing
      • Internship Tips
      • Online Education
      • Scholarships
      • Student Loans
    • Managerial Skills
      • Business Communication
      • Business Networking
      • Facilitator Skills
      • Managing Change
      • Marketing Management
      • Meeting Management
      • Process Management
      • Project Management
      • Project Management Life Cycle
      • Project Management Process
      • Project Risk Management
      • Relationship Management
      • Task Management
      • Team Building
      • Virtual Team Management
    • Essential Life Skills
      • Anger Management
      • Anxiety Management
      • Attitude Development
      • Coaching and Mentoring
      • Emotional Intelligence
      • Stress Management
      • Positive Thinking
    • Communication Skills
      • Conversation Skills
      • Cross Culture Competence
      • English Vocabulary
      • Listening Skills
      • Public Speaking Skills
      • Questioning Skills
    • Soft Skills
      • Assertive Skills
      • Influence Skills
      • Leadership Skills
      • Memory Skills
      • People Skills
      • Presentation Skills
    • Finding a Job
      • Etiquette Tips
      • Group Discussions
      • HR Interviews
      • Interview Notes
      • Job Search Tips
      • Resume Tips
      • Sample Resumes
 

T-SQL Enhancements in SQL Server 2005

By Exforsys | on October 15, 2005 |
SQL Server 2005

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.

« « Security Features in SQL Server 2005 for the Developer
Data Manipulation Language (DML) in SQL Server 2005 » »

Author Description

Avatar

Editorial Team at Exforsys is a team of IT Consulting and Training team led by Chandra Vennapoosa.

Free Training

RSSSubscribe 394 Followers
  • Popular
  • Recent
  • XML Data Types in SQL Server 2005

    October 26, 2005 - 0 Comment
  • SQL Server 2005 – Using the Database Tuning Advisor

    December 24, 2005 - 0 Comment
  • SQL Server 2005 Installation

    November 23, 2005 - 0 Comment
  • Using SQL Sever Management Studio – Part 1

    December 15, 2005 - 0 Comment
  • Native HTTP Support in SQL Server 2005

    November 1, 2005 - 0 Comment
  • Managing SQL Server 2005 Security

    December 24, 2005 - 0 Comment
  • Planning to Install SQL Server 2005

    November 29, 2005 - 0 Comment
  • Getting started with SQL Server 2005

    August 9, 2005 - 0 Comment
  • Using SQL Sever Management Studio – Part 2

    December 15, 2005 - 0 Comment
  • NET CLR in SQL Server 2005

    November 1, 2005 - 0 Comment
  • SQL Server 2005 – Configuring Replication

    January 11, 2006 - 0 Comment
  • SQL Server 2005 Replication Enhancements

    January 11, 2006 - 0 Comment
  • SQL Server 2005 – Mirror Server

    January 11, 2006 - 0 Comment
  • SQL Server 2005 – Introduction to Data Availability

    January 1, 2006 - 0 Comment
  • SQL Server 2005 – Backing up a Database

    January 1, 2006 - 0 Comment
  • SQL Server 2005 – Using Database Snapshots

    December 26, 2005 - 0 Comment
  • SQL Server 2005 – Disaster Recovery

    December 26, 2005 - 0 Comment
  • SQL Server 2005 – Managing Certificates

    December 26, 2005 - 0 Comment
  • SQL Server 2005 – Managing Permissions

    December 26, 2005 - 0 Comment
  • Managing SQL Server 2005 Security

    December 24, 2005 - 0 Comment

Exforsys e-Newsletter

ebook
 

Related Articles

  • SQL Server 2005 – Configuring Replication
  • SQL Server 2005 Replication Enhancements
  • SQL Server 2005 – Mirror Server
  • SQL Server 2005 – Introduction to Data Availability
  • SQL Server 2005 – Backing up a Database

Latest Articles

  • Project Management Techniques
  • Product Development Best Practices
  • Importance of Quality Data Management
  • How to Maximize Quality Assurance
  • Utilizing Effective Quality Assurance Strategies
  • Sitemap
  • Privacy Policy
  • DMCA
  • Trademark Information
  • Contact Us
© 2023. All Rights Reserved.IT Training and Consulting
This website uses cookies to improve your experience. We'll assume you're ok with this, but you can opt-out if you wish.AcceptReject Read More
Privacy & Cookies Policy

Privacy Overview

This website uses cookies to improve your experience while you navigate through the website. Out of these, the cookies that are categorized as necessary are stored on your browser as they are essential for the working of basic functionalities of the website. We also use third-party cookies that help us analyze and understand how you use this website. These cookies will be stored in your browser only with your consent. You also have the option to opt-out of these cookies. But opting out of some of these cookies may affect your browsing experience.
Necessary
Always Enabled
Necessary cookies are absolutely essential for the website to function properly. This category only includes cookies that ensures basic functionalities and security features of the website. These cookies do not store any personal information.
Non-necessary
Any cookies that may not be particularly necessary for the website to function and is used specifically to collect user personal data via analytics, ads, other embedded contents are termed as non-necessary cookies. It is mandatory to procure user consent prior to running these cookies on your website.
SAVE & ACCEPT