what is sql transaction....?

Posted by Technalogy Information at 7:30 AM Monday, January 31, 2011 Labels: SQL 0 comments

SQL-Transaction Statements
SQL-Transaction Statements control transactions in database access. This subset of SQL is also called the Data Control Language for SQL (SQL DCL).
There are 2 SQL-Transaction Statements:

COMMIT Statement -- commit (make persistent) all changes for the current transaction
ROLLBACK Statement -- roll back (rescind) all changes for the current transaction
Transaction Overview
A database transaction is a larger unit that frames multiple SQL statements. A transaction ensures that the action of the framed statements is atomic with respect to recovery.
A SQL Modification Statement has limited effect. A given statement can only directly modify the contents of a single table (Referential Integrity effects may cause indirect modification of other tables.) The upshot is that operations which require modification of several tables must involve multiple modification statements. A classic example is a bank operation that transfers funds from one type of account to another, requiring updates to 2 tables. Transactions provide a way to group these multiple statements in one atomic unit.

In SQL92, there is no BEGIN TRANSACTION statement. A transaction begins with the execution of a SQL-Data statement when there is no current transaction. All subsequent SQL-Data statements until COMMIT or ROLLBACK become part of the transaction. Execution of a COMMIT Statement or ROLLBACK Statement completes the current transaction. A subsequent SQL-Data statement starts a new transaction.

In terms of direct effect on the database, it is the SQL Modification Statements that are the main consideration since they change data. The total set of changes to the database by the modification statements in a transaction are treated as an atomic unit through the actions of the transaction.

.........................................................................................................................................................

A transaction is a sequence of operations performed as a single logical unit of work. A logical unit of work must exhibit four properties, called the ACID (Atomicity, Consistency, Isolation, and Durability) properties, to qualify as a transaction:

Atomicity: A transaction must be an atomic unit of work; either all of its data modifications are performed or none of them is performed.

Consistency: When completed, a transaction must leave all data in a consistent state. In a relational database, all rules must be applied to the transaction's modifications to maintain all data integrity. All internal data structures, such as B-tree indexes or doubly-linked lists, must be correct at the end of the transaction.

Isolation: Modifications made by concurrent transactions must be isolated from the modifications made by any other concurrent transactions. A transaction either sees data in the state it was in before another concurrent transaction modified it, or it sees the data after the second transaction has completed, but it does not see an intermediate state. This is referred to as serializability because it results in the ability to reload the starting data and replay a series of transactions to end up with the data in the same state it was in after the original transactions were performed.

Durability: After a transaction has completed, its effects are permanently in place in the system. The modifications persist even in the event of a system failure.

..............................................................................................................................................................

SQL-Transaction Statements

SQL-Transaction Statements control transactions in database access. This subset of SQL is also called the Data Control Language for SQL (SQL DCL). There are 2 SQL-Transaction Statements:

COMMIT Statement -- commit (make persistent) all changes for the current transaction
ROLLBACK Statement -- roll back (rescind) all changes for the current transaction

Transaction Overview

A database transaction is a larger unit that frames multiple SQL statements. A transaction ensures that the action of the framed statements is atomic with respect to recovery. A SQL Modification Statement has limited effect. A given statement can only directly modify the contents of a single table (Referential Integrity effects may cause indirect modification of other tables.) The upshot is that operations which require modification of several tables must involve multiple modification statements. A classic example is a bank operation that transfers funds from one type of account to another, requiring updates to 2 tables. Transactions provide a way to group these multiple statements in one atomic unit.
In SQL92, there is no BEGIN TRANSACTION statement. A transaction begins with the execution of a SQL-Data statement when there is no current transaction. All subsequent SQL-Data statements until COMMIT or ROLLBACK become part of the transaction. Execution of a COMMIT Statement or ROLLBACK Statement completes the current transaction. A subsequent SQL-Data statement starts a new transaction.
In terms of direct effect on the database, it is the SQL Modification Statements that are the main consideration since they change data. The total set of changes to the database by the modification statements in a transaction are treated as an atomic unit through the actions of the transaction. The set of changes either:

Is made fully persistent in the database through the action of the COMMIT Statement, or
Has no persistent effect whatever on the database, through:
- the action of the ROLLBACK Statement,
- abnormal termination of the client requesting the transaction, or
- abnormal termination of the transaction by the DBMS. This may be an action by the system (deadlock resolution) or by an administrative agent, or it may be an abnormal termination of the DBMS itself. In the latter case, the DBMS must roll back any active transactions during recovery.

The DBMS must ensure that the effect of a transaction is not partial. All changes in a transaction must be made persistent, or no changes from the transaction must be made persistent.

Transaction Isolation

In most cases, transactions are executed under a client connection to the DBMS. Multiple client connections can initiate transactions at the same time. This is known as concurrent transactions. In the relational model, each transaction is completely isolated from other active transactions. After initiation, a transaction can only see changes to the database made by transactions committed prior to starting the new transaction. Changes made by concurrent transactions are not seen by SQL DML query and modification statements. This is known as full isolation or Serializable transactions.
SQL92 defines Serializable for transactions. However, fully serialized transactions can impact performance. For this reason, SQL92 allows additional isolation modes that reduce the isolation between concurrent transactions. SQL92 defines 3 other isolation modes, but support by existing DBMSs is often incomplete and doesn't always match the SQL92 modes. Check the documentation of your DBMS for more details.
Transaction isolation controls the visibility of changes between transactions in different sessions (connections). It determines if queries in one session can see changes made by a transaction in another session. There are 4 levels of transaction isolation. The level providing the greatest isolation from other transactions is Serializable.
At transaction isolation level Serializable, a transaction is fully isolated from changes made by other sessions. Queries issued under Serializable transactions cannot see any subsequent changes, committed or not, from other transactions. The effect is the same as if transactions were serial, that is, each transaction completing before another one is begun.
At the opposite end of the spectrum is Read Uncommitted. It is the lowest level of isolation. With Read Uncommitted, a session can read (query) subsequent changes made by other sessions, either committed or uncommitted. Read uncommitted transactions have the following characteristics:

Dirty Read -- a session can read rows changed by transactions in other sessions that have not been committed. If the other session then rolls back its transaction, subsequent reads of the same rows will find column values returned to previous values, deleted rows reappearing and rows inserted by the other transaction missing.
Non-repeatable Read -- a session can read a row in a transaction. Another session then changes the row (UPDATE or DELETE) and commits its transaction. If the first session subsequently re-reads the row in the same transaction, it will see the change.
Phantoms -- a session can read a set of rows in a transaction that satisfies a search condition (which might be all rows). Another session then generates a row (INSERT) that satisfies the search condition and commits its transaction. If the first session subsequently repeats the search in the same transaction, it will see the new row.

The other transaction levels -- Read Committed, Repeatable Read and Serializable, will not read uncommitted changes. Dirty reads are not possible. The next level above Read Uncommitted is Read Committed, and the next above that is Repeatable Read. In Read Committed isolation level, Dirty Reads are not possible, but Non-repeatable Reads and Phantoms are possible. In Repeatable Read isolation level, Dirty Reads and Non-repeatable Reads are not possible but Phantoms are. In Serializable, Dirty Reads, Non-repeatable Reads, and Phantoms are not possible.
The isolation provided by each transaction isolation level is summarized below:

	Dirty Reads	Non-repeatable Reads	Phantoms
Read Uncommitted	Y	Y	Y
Read Committed	N	Y	Y
Repeatable Read	N	N	Y
Serializable	N	N	N

Note: SQL92 defines the SET TRANSACTION statement to set the transaction isolation level for a session, but most DBMSs support a function/method in the Client API as an alternative.

SQL-Schema Statements in Transactions

The 3rd type of SQL Statements - SQL-Schema Statements, may participate in the transaction mechanism. SQL-Schema statements can either be:

included in a transaction along with SQL-Data statements,
required to be in separate transactions, or
ignored by the transaction mechanism (can't be rolled back).

SQL92 leaves the choice up to the individual DBMS. It is implementation defined behavior.

COMMIT Statement

The COMMIT Statement terminates the current transaction and makes all changes under the transaction persistent. It commits the changes to the database. The COMMIT statement has the following general format:

COMMIT [WORK]

WORK is an optional keyword that does not change the semantics of COMMIT.

ROLLBACK Statement

The ROLLBACK Statement terminates the current transaction and rescinds all changes made under the transaction. It rolls back the changes to the database. The ROLLBACK statement has the following general format:

ROLLBACK [WORK]

WORK is an optional keyword that does not change the semantics of ROLLBACK.

............................................................................................................................................................................................

SqlTransaction Class

.NET Framework 4

Other Versions

Represents a Transact-SQL transaction to be made in a SQL Server database. This class cannot be inherited.

Inheritance Hierarchy

System.Object
  System.MarshalByRefObject
    System.Data.Common.DbTransaction
      System.Data.SqlClient.SqlTransaction

Namespace: System.Data.SqlClient
Assembly: System.Data (in System.Data.dll)

Syntax

C++

JScript

Copy

'Declaration

Public NotInheritable Class SqlTransaction _
 Inherits DbTransaction

The SqlTransaction type exposes the following members.

Properties

	Name	Description
	Connection	Gets the SqlConnection object associated with the transaction, or Nothing if the transaction is no longer valid.
	DbConnection	Specifies the DbConnection object associated with the transaction. (Inherited from DbTransaction.)
	IsolationLevel	Specifies the IsolationLevel for this transaction. (Overrides DbTransaction.IsolationLevel.)

Top

Methods

	Name	Description
	Commit	Commits the database transaction. (Overrides DbTransaction.Commit.)
	CreateObjRef	Creates an object that contains all the relevant information required to generate a proxy used to communicate with a remote object. (Inherited from MarshalByRefObject.)
	Dispose	Releases the unmanaged resources used by the DbTransaction. (Inherited from DbTransaction.)
	Dispose(Boolean)	Releases the unmanaged resources used by the DbTransaction and optionally releases the managed resources. (Inherited from DbTransaction.)
	Equals(Object)	Determines whether the specified Object is equal to the current Object. (Inherited from Object.)
	Finalize	Allows an object to try to free resources and perform other cleanup operations before it is reclaimed by garbage collection. (Inherited from Object.)
	GetHashCode	Serves as a hash function for a particular type. (Inherited from Object.)
	GetLifetimeService	Retrieves the current lifetime service object that controls the lifetime policy for this instance. (Inherited from MarshalByRefObject.)
	GetType	Gets the Type of the current instance. (Inherited from Object.)
	InitializeLifetimeService	Obtains a lifetime service object to control the lifetime policy for this instance. (Inherited from MarshalByRefObject.)
	MemberwiseClone	Creates a shallow copy of the current Object. (Inherited from Object.)
	MemberwiseClone(Boolean)	Creates a shallow copy of the current MarshalByRefObject object. (Inherited from MarshalByRefObject.)
	Rollback	Rolls back a transaction from a pending state. (Overrides DbTransaction.Rollback.)
	Rollback(String)	Rolls back a transaction from a pending state, and specifies the transaction or savepoint name.
	Save	Creates a savepoint in the transaction that can be used to roll back a part of the transaction, and specifies the savepoint name.
	ToString	Returns a string that represents the current object. (Inherited from Object.)

Top

Explicit Interface Implementations

	Name	Description
	IDbTransaction.Connection	Gets the DbConnection object associated with the transaction, or a null reference if the transaction is no longer valid. (Inherited from DbTransaction.)

Top

Remarks

The application creates a SqlTransaction object by calling BeginTransaction on the SqlConnection object. All subsequent operations associated with the transaction (for example, committing or aborting the transaction), are performed on the SqlTransaction object.

Note
Try /Catch exception handling should always be used when committing or rolling back a SqlTransaction. Both Commit and Rollback generate an InvalidOperationException if the connection is terminated or if the transaction has already been rolled back on the server.

For more information on SQL Server transactions, see "Explicit Transactions" and "Coding Efficient Transactions" in SQL Server 2005 Books Online.

Examples

The following example creates a SqlConnection and a SqlTransaction. It also demonstrates how to use the BeginTransaction, Commit, and Rollback methods. The transaction is rolled back on any error. Try/Catch error handling is used to handle any errors when attempting to commit or roll back the transaction.

C++

JScript

Copy

Private Sub ExecuteSqlTransaction(ByVal connectionString As String)
    Using connection As New SqlConnection(connectionString)
        connection.Open()

        Dim command As SqlCommand = connection.CreateCommand()
        Dim transaction As SqlTransaction

        ' Start a local transaction
        transaction = connection.BeginTransaction("SampleTransaction")

        ' Must assign both transaction object and connection
        ' to Command object for a pending local transaction.
        command.Connection = connection
        command.Transaction = transaction

        Try
            command.CommandText = _
              "Insert into Region (RegionID, RegionDescription) VALUES (100, 'Description')"
            command.ExecuteNonQuery()
            command.CommandText = _
              "Insert into Region (RegionID, RegionDescription) VALUES (101, 'Description')"

            command.ExecuteNonQuery()

            ' Attempt to commit the transaction.
            transaction.Commit()
            Console.WriteLine("Both records are written to database.")

        Catch ex As Exception
            Console.WriteLine("Commit Exception Type: {0}", ex.GetType())
            Console.WriteLine("  Message: {0}", ex.Message)

            ' Attempt to roll back the transaction.
            Try
                transaction.Rollback()

            Catch ex2 As Exception
                ' This catch block will handle any errors that may have occurred
                ' on the server that would cause the rollback to fail, such as
                ' a closed connection.
                Console.WriteLine("Rollback Exception Type: {0}", ex2.GetType())
                Console.WriteLine("  Message: {0}", ex2.Message)
            End Try
        End Try
    End Using
End Sub

Version Information

.NET Framework

Supported in: 4, 3.5, 3.0, 2.0, 1.1, 1.0

.NET Framework Client Profile

Supported in: 4, 3.5 SP1

Platforms

Windows 7, Windows Vista SP1 or later, Windows XP SP3, Windows XP SP2 x64 Edition, Windows Server 2008 (Server Core Role not supported), Windows Server 2008 R2 (Server Core Role not supported), Windows Server 2003 SP2

The .NET Framework does not support all versions of every platform. For a list of the supported versions, see .NET Framework System Requirements.

Thread Safety

Any public static (Shared in Visual Basic) members of this type are thread safe. Any instance members are not guaranteed to be thread safe.

SQL triggers

Posted by Technalogy Information at 7:20 AM Labels: SQL 0 comments

Database trigger

From Wikipedia, the free encyclopedia

Jump to: navigation, search

A database trigger is procedural code that is automatically executed in response to certain events on a particular table or view in a database. The trigger is mostly used for keeping the integrity of the information on the database. For example, when a new record (representing a new worker) is added to the employees table, new records should be created also in the tables of the taxes, vacations, and salaries.

[hide]

[edit] The need and the usage

Triggers are commonly used to:

prevent changes (e.g. prevent an invoice from being changed after it's been mailed out)
log changes (e.g. keep a copy of the old data)
audit changes (e.g. keep a log of the users and roles involved in changes)
enhance changes (e.g. ensure that every change to a record is time-stamped by the server's clock, not the )
enforce business rules (e.g. require that every invoice have at least one line item)
execute business rules (e.g. notify a manager every time an employee's bank account number changes)
replicate data (e.g. store a record of every change, to be shipped to another database later)
enhance performance (e.g. update the account balance after every detail transaction, for faster queries)

The examples above are called Data Manipulation Language (DML) triggers because the triggers are defined as part of the Data Manipulation Language and are executed at the time the data are manipulated. Some systems also support non-data triggers, which fire in response to Data Definition Language (DDL) events such as creating tables, or runtime or and events such as logon, commit, and rollback. Such DDL triggers can be used for auditing purposes.
The following are major features of database triggers and their effects:

triggers do not accept parameters or arguments (but may store affected-data in temporary tables)
triggers cannot perform commit or rollback operations because they are part of the triggering SQL statement (only through autonomous transactions)

[edit] Triggers in Oracle

In addition to triggers that fire when data is modified, Oracle 9i supports triggers that fire when schema objects (that is, tables) are modified and when user logon or logoff events occur. These trigger types are referred to as "Schema-level triggers".

[edit] Schema-level triggers

After Creation
Before Alter
After Alter
Before Drop
After Drop
Before Logoff
After Logon

The four main types of triggers are:

Row Level Trigger: This gets executed before or after any column value of a row changes
Column Level Trigger: This gets executed before or after the specified column changes
For Each Row Type: This trigger gets executed once for each row of the result set caused by insert/update/delete
For Each Statement Type: This trigger gets executed only once for the entire result set, but fires each time the statement is executed.

[edit] Mutating tables

When a single SQL statement modifies several rows of a table at once, the order of the operations is not well-defined; there is no "order by" clause on "update" statements, for example. Row-level triggers are executed as each row is modified, so the order in which trigger code is run is also not well-defined. Oracle protects the programmer from this uncertainty by preventing row-level triggers from modifying other rows in the same table – this is the "mutating table" in the error message. Side-effects on other tables are allowed, however.
One solution is to have row-level triggers place information into a temporary table indicating what further changes need to be made, and then have a statement-level trigger fire just once, at the end, to perform the requested changes and clean up the temporary table.
Because a foreign key's referential actions are implemented via implied triggers, they are similarly restricted. This may become a problem when defining a self-referential foreign key, or a cyclical set of such constraints, or some other combination of triggers and CASCADE rules (e.g. user deletes a record from table A, CASCADE rule on table A deletes a record from table B, trigger on table B attempts to SELECT from table A, error occurs.)

[edit] Triggers in Microsoft SQL Server

Microsoft SQL Server supports triggers either after or instead of an insert, update, or delete operation. They can be set on tables and views with the constraint that a view can be referenced only by an INSTEAD OF trigger.
Microsoft SQL Server 2005 introduced support for Data Definition Language (DDL) triggers, which can fire in reaction to a very wide range of events, including:

A full list is available on MSDN.
Performing conditional actions in triggers (or testing data following modification) is done through accessing the temporary Inserted and Deleted tables.

[edit] Triggers in PostgreSQL

PostgreSQL introduced support for triggers in 1997. The following functionality in SQL:2003 is not implemented in PostgreSQL:

SQL allows triggers to fire on updates to specific columns; As of version 9.0 of PostgreSQL this feature is also implemented in PostgreSQL.
The standard allows the execution of a number of SQL statements other than SELECT, INSERT, UPDATE, such as CREATE TABLE as the triggered action. This can be done through creating a stored procedure to call CREATE TABLE.^[1]

Synopsis:

SELECT * FROM MY_TABLE;
 UPDATE MY_TABLE SET A = 5;
 INSERT INTO MY_TABLE VALUES (3, 'aaa');

[edit] Triggers in Firebird

Firebird supports multiple row-level, BEFORE or AFTER, INSERT, UPDATE, DELETE (or any combination thereof) triggers per table, where they are always "in addition to" the default table changes, and the order of the triggers relative to each other can be specified where it would otherwise be ambiguous (POSITION clause.) Triggers may also exist on views, where they are always "instead of" triggers, replacing the default updatable view logic. (Before version 2.1, triggers on views deemed updatable would run in addition to the default logic.)
Firebird does not raise mutating table exceptions (like Oracle), and triggers will by default both nest and recurse as required (SQL Server allows nesting but not recursion, by default.) Firebird's triggers use NEW and OLD context variables (not Inserted and Deleted tables,) and provide UPDATING, INSERTING, and DELETING flags to indicate the current usage of the trigger.

{CREATE | RECREATE | CREATE OR ALTER} TRIGGER name FOR {TABLE name | VIEW name}
 [ACTIVE | INACTIVE]
 {BEFORE | AFTER}
 {INSERT [OR UPDATE] [OR DELETE] | UPDATE [OR INSERT] [OR DELETE] | DELETE [OR UPDATE] [OR INSERT] }
 [POSITION n] AS
BEGIN
 .....
END

As of version 2.1, Firebird additionally supports the following database-level triggers:

CONNECT (exceptions raised here prevent the connection from completing)
DISCONNECT
TRANSACTION START
TRANSACTION COMMIT (exceptions raised here prevent the transaction from committing, or preparing if a two-phase commit is involved)
TRANSACTION ROLLBACK

Database-level triggers can help enforce multi-table constraints, or emulate materialized views. If an exception is raised in a TRANSACTION COMMIT trigger, the changes made by the trigger so far are rolled back and the client application is notified, but the transaction remains active as if COMMIT had never been requested; the client application can continue to make changes and re-request COMMIT.
Syntax for database triggers:

{CREATE | RECREATE | CREATE OR ALTER} TRIGGER name
 [ACTIVE | INACTIVE] ON
 {CONNECT | DISCONNECT | TRANSACTION START | TRANSACTION COMMIT | TRANSACTION ROLLBACK}
 [POSITION n] AS
BEGIN
 .....
END

[edit] Triggers in MySQL

MySQL 5.0.2 introduced support for triggers. Some of the triggers MySQL supports are

Insert Trigger
Update Trigger
Delete Trigger

The SQL:2003 standard mandates that triggers give programmers access to record variables by means of a syntax such as REFERENCING NEW AS n. For example, if a trigger is monitoring for changes to a salary column one could write a trigger like the following:

CREATE TRIGGER salary_trigger
    BEFORE UPDATE ON employee_table
    REFERENCING NEW ROW AS n, OLD ROW AS o
    FOR EACH ROW
    IF n.salary <> o.salary THEN
 
    END IF;
;

[edit] Triggers in IBM DB2 LUW

IBM DB2 for distributed systems known as DB2 for LUW (LUW means Linux Unix Windows) supports 3 trigger types: Before trigger, After trigger and Instead of trigger. Both statement level and row level triggers are supported. If there are more triggers for same operation on table then firing order is determined by trigger creation data. Since version 9.7 IBM DB2 supports autonomous transactions [1].
Before trigger is for checking data and deciding if operation should be permitted. If exception is thrown from before trigger then operation is aborted and no data are changed. In DB2 before triggers are read only — you cant modify data in before triggers. After triggers are designed for post processing after requested change was performed. After triggers can write data into tables and unlike some other databases you can write into any table including table on which trigger operates. Instead of triggers are for making views writeable.
Triggers are usually programmed in SQL PL language.

[edit] Triggers in SQLite

CREATE [TEMP | TEMPORARY] TRIGGER [IF NOT EXISTS] [database_name .] trigger_name
   [BEFORE | AFTER | INSTEAD OF] {DELETE | INSERT | UPDATE [OF column_name [, column_name]...]} ON {table_name | view_name}
   [FOR EACH ROW] [WHEN condition]
BEGIN
   ...
END

SQLite only supports row-level triggers, not statement-level triggers.
Updateable views, which are not supported in SQLite, can be emulated with INSTEAD OF triggers.

[edit] Triggers in XML databases

An example of implementation of triggers in non-relational database can be Sedna, that provides support for triggers based on XQuery. Triggers in Sedna were designed to be analogous to SQL:2003 triggers, but natively base on XML query and update languages (XPath, XQuery and XML update language).
A trigger in Sedna is set on any nodes of an XML document stored in database. When these nodes are updated, the trigger automatically executes XQuery queries and updates specified in its body. For example, the following trigger cancels person node deletion if there are any open auctions referenced by this person:

CREATE TRIGGER "trigger3"  
    BEFORE DELETE  
    ON doc("auction")/site//person  
    FOR EACH NODE  
    DO  
    {  
       if(exists($WHERE//open_auction/bidder/personref/@person=$OLD/@id))  
       then ( )  
       else $OLD;  
    }

...................................................................................

An Introduction to Triggers -- Part I

By Garth Wells on 30 April 2001 | 6 Comments | Tags: Triggers

This article, submitted by Garth , covers the basics of using triggers. "A trigger is a database object that is attached to a table. In many aspects it is similar to a stored procedure." If you're a developer and not familiar with triggers this article is a great starting point.

A trigger is a database object that is attached to a table. In many aspects it is similar to a stored procedure. As a matter of fact, triggers are often referred to as a "special kind of stored procedure." The main difference between a trigger and a stored procedure is that the former is attached to a table and is only fired when an INSERT, UPDATE or DELETE occurs. You specify the modification action(s) that fire the trigger when it is created. The following shows how to create a trigger that displays the current system time when a row is inserted into the table to which it is attached.

SET NOCOUNT ON

CREATE TABLE Source (Sou_ID int IDENTITY, Sou_Desc varchar(10))
go
CREATE TRIGGER tr_Source_INSERT
ON Source
FOR INSERT
AS
PRINT GETDATE()
go
INSERT Source (Sou_Desc) VALUES ('Test 1')

-- Results --

Apr 28 2001  9:56AM

This example is shown for illustrative purposes only. I'll cover an example later in the article that shows a real-world world use of triggers.

When to Use Triggers

There are more than a handful of developers who are not real clear when triggers should be used. I only use them when I need to perform a certain action as a result of an INSERT, UPDATE or DELETE and ad hoc SQL (aka SQL Passthrough) is used. I implement most of my data manipulation code via stored procedures and when you do this the trigger functionality can be moved into the procedure. For example, let's say you want to send an email to the Sales Manager when an order is entered whose priority is high. When ad hoc SQL is used to insert the Orders row, a trigger is used to determine the OrderPriority and send the email when the criteria is met. The following shows a partial code listing of what this looks like.

CREATE TABLE Orders (Ord_ID int IDENTITY, Ord_Priority varchar(10))
go
CREATE TRIGGER tr_Orders_INSERT
ON Orders
FOR INSERT
AS
IF (SELECT COUNT(*) FROM inserted WHERE Ord_Priority = 'High') = 1
 BEGIN
  PRINT 'Email Code Goes Here'
 END
go
INSERT Orders (Ord_Priority) VALUES ('High')

-- Results --

Email Code Goes Here

When the stored procedure approach is used you can move the trigger code into the procedure and it looks like this.

CREATE PROCEDURE ps_Orders_INSERT
@Ord_Priority varchar(10)
AS
BEGIN TRANSACTION
 INSERT Orders (Ord_Priority) VALUES (@Ord_Priority)

 IF @@ERROR <> 0
  GOTO ErrorCode

 IF @Ord_Priority = 'High'
  PRINT 'Email Code Goes Here'

COMMIT TRANSACTION

ErrorCode:
 IF @@TRANCOUNT <> 0
  PRINT 'Error Code'
go

Let's take a look at the trigger example. The first thing you probably noticed is that the SELECT references a table called inserted. Triggers make use of two special tables called inserted and deleted. The inserted table contains the data referenced in an INSERT before it is actually committed to the database. The deleted table contains the data in the underlying table referenced in a DELETE before it is actually removed from the database. When an UPDATE is issued both tables are used. More specifically, the new data referenced in the UPDATE statement is contained in inserted and the data that is being updated is contained in deleted.
The example makes an assumption about how data is going to be added to the table. The IF statement is looking for a count of 1. This means the trigger assumes only one row will be added to the table at a time. If more than one row is added to the table in a single statement you may miss an order with a High priority because the trigger only fires once for each associated statement. I realize this may sound a little confusing so let's take a look at two more examples. The following shows that the trigger fires for each INSERT statement executed.

INSERT Orders (Ord_Priority) VALUES ('High')
INSERT Orders (Ord_Priority) VALUES ('High')

-- Results --

Email Code Goes Here

Email Code Goes Here

Now we have three rows in Orders whose Ord_Priority is High. Let's insert new rows based on the current contents of Orders to show how a trigger behaves when a multi-row statement is executed.

INSERT Orders
SELECT Ord_Priority FROM Orders

The 'Email Code Here' message is not displayed even though three new rows were added with a priority of High because the IF statement criteria was not satisfied. A trigger fires only once per statement, so the actual COUNT(*) associated with the INSERT is 3. The following shows how to modify the code to handle a multi-row INSERT.

ALTER TRIGGER tr_Orders_INSERT
ON Orders
FOR INSERT
AS
IF EXISTS (SELECT * FROM inserted WHERE Ord_Priority = 'High')
 BEGIN
  DECLARE @Count tinyint
  SET @Count = (SELECT COUNT(*) FROM inserted WHERE Ord_Priority = 'High')
  PRINT CAST(@Count as varchar(3))+' row(s) with a priority of High were entered'
 END
go

We can test the code using the same INSERT with a SELECT as follows.

INSERT Orders
SELECT Ord_Priority FROM Orders

-- Results --

12 row(s) with a priority of High were entered

A Real-World Example

Those of you familiar with web site management know that counting the traffic on a site is key in determining which areas of the site are being used. Internet Information Server (IIS) has logging capabilities that tracks a number of attributes associated with each visitor. For example, every time a visitor accesses a page on a site that page and the user's information is logged. By default the data is logged in a text file, but you can alter the default behavior and log the data to an ODBC-compliant data store.
I used this approach for a client a while back because they wanted a simple way to track the activity for each major area of their site. A major area was defined as the sections listed on the site's main navigation bar (e.g., Home, About Us, Services, ...). The goal was to produce a report that showed the number of visits to each of the main areas of the site on a per month basis. A few of you may be wondering why a trigger is needed to implement this solution. After all, a SELECT with a WHERE clause to filter the date range and GROUP BY to count the instances per page will do the trick and no triggers are needed.
The reason I decided to use a trigger-based solution had to do with the unacceptable execution time of the report. Even on a low-traffic site the number of rows in the logging table grows at a staggering rate. For every page accessed by a visitor, there is at least one row added to the table. When a page contains a reference to a graphic (e.g., .gifs or .jpgs), there is another row created. If a page contains five references to graphics, there are six rows created in the logging table every time it is accessed.
The bottom-line is that because of the size of the table the report took too long to execute. In order to reduce the time it took to execute the report I decided to use a summary (aka aggregate) table to count the page views as they were entered into the logging table. Since there were only eight main areas on the site, the summary table contained eight rows and the report ran in less than one second.
The following uses a dummied-down schema to show how this technique works. For the sake of brevity, I will only use two main areas of the site.

CREATE TABLE InetLog (ClientHost varchar(255), LogTime datetime, Target 
varchar(255))
go
CREATE TABLE LogSummary (LogSum_Category varchar(30), LogSum_Count int)
go
INSERT LogSummary VALUES ('About Us',0)
INSERT LogSummary VALUES ('Services',0)

InetLog is the main logging table and LogSummary is the summary table. The two main areas of the site are About Us and Services. The goal of the trigger is to update the value in LogSum_Count every time the AboutUs.htm and Services.htm pages are accessed. The trigger used to do this is shown here.

CREATE TRIGGER tr_InetLog_INSERT
ON InetLog
FOR INSERT
AS

IF EXISTS (SELECT * FROM inserted WHERE Target = 'AboutUs.htm')
 BEGIN
  UPDATE LogSummary
  SET LogSum_Count = (SELECT COUNT(*) FROM InetLog WHERE Target = 'AboutUs.htm')
  WHERE LogSum_Category = 'About Us'
 END

IF EXISTS (SELECT * FROM inserted WHERE Target = 'Services.htm')
 BEGIN
  UPDATE LogSummary
  SET LogSum_Count = (SELECT COUNT(*) FROM InetLog WHERE Target = 'Services.htm')
  WHERE LogSum_Category = 'Services'
 END
go

The trigger simply extends on the examples presented earlier and when the IF criteria is met the associated row in LogSummary is updated. The following shows the trigger works as expected.

INSERT InetLog VALUES ('111.111.111.111', '4/1/29 12:00:50','Default.htm')
INSERT InetLog VALUES ('111.111.111.111', '4/1/29 12:01:01','AboutUs.htm')
INSERT InetLog VALUES ('111.111.111.111', '4/1/29 12:02:01','Services.htm')
INSERT InetLog VALUES ('111.111.111.111', '4/1/29 12:03:01','Products.htm')
INSERT InetLog VALUES ('111.111.111.111', '4/1/29 12:04:50','Default.htm')
INSERT InetLog VALUES ('111.111.111.111', '4/1/29 12:05:01','AboutUs.htm')
INSERT InetLog VALUES ('111.111.111.111', '4/1/29 12:06:01','Services.htm')
INSERT InetLog VALUES ('111.111.111.111', '4/1/29 12:07:01','Products.htm')
go
SELECT * FROM LogSummary

-- Results --

LogSum_Category                LogSum_Count
------------------------------ ------------
About Us                       2
Services                       2

Before I leave this section I must mention that this homemade solution is not the preferred way to monitor web site traffic. I certainly had fun researching ODBC-logging and writing the code, but I actually suggested the client buy a commercial software package like WebTrends to implement this functionality. WebTrends is a great product and allows you to perform detailed analysis of a site's traffic. The client did not want to spend any money on real software, but instead on me:))

Part II

In the next installment of this article I will discuss UPDATE, DELETE and INSTEAD OF Triggers. For those of you who have not worked with SQL Server 2000, you probably have not heard of INSTEAD OF triggers. They were added in the latest version of product to help with updatable views. Part II is posted!

An Introduction to Triggers -- Part II

By Garth Wells on 04 November 2001 | 1 Comment | Tags: Triggers

A few months ago I wrote an article for SQLTeam called An Introduction to Triggers -- Part I. The article covered trigger fundamentals--the most important being the way the inserted and deleted virtual tables work. I also showed an example of how to use an INSERT trigger to log activity on a web site. In this article I want to show how to use an UPDATE and DELETE trigger and introduce INSTEAD OF triggers. If you are not familiar with the inserted and deleted tables please read this article before continuing.

UPDATE Trigger

An UPDATE trigger is used to perform an action after an update is made on a table. The example shown here is based on the table used in Part I of this article.

CREATE TRIGGER tr_Orders_UPDATE
ON Orders
AFTER UPDATE
AS

--Make sure Priority was changed
IF NOT UPDATE(Ord_Priority)
 RETURN

--Determine if Priority was changed to high
IF EXISTS (SELECT *
           FROM inserted a
    JOIN deleted b ON a.Ord_ID=b.Ord_ID
    WHERE b.Ord_Priority <> 'High' AND
    a.Ord_Priority = 'High')
 BEGIN
  DECLARE @Count tinyint
  SET @Count = (SELECT COUNT(*)
                FROM inserted a
  JOIN deleted b ON a.Ord_ID=b.Ord_ID
  WHERE b.Ord_Priority <> 'High' AND
  a.Ord_Priority = 'High')
  PRINT CAST(@Count as varchar(3))+' row(s) where changed to a priority of High'
 END
go

In Part I the INSERT trigger watched for orders with a priority of 'High.' The UPDATE trigger watches for orders whose priority are changed from something else to High.
The IF statement checks to see if the Ord_Priority value was changed. If not we can save some time by exiting the trigger immediately.
The deleted table holds the pre-UPDATE values and inserted table holds the new values. When the tables are joined it is easy to tell when the priority changes from something else to High.

DELETE Trigger

Once you understand how an UPDATE trigger works a DELETE trigger is easy to implement. In the example shown here it simply counts the number of rows in the deleted table to see how many had a priority of high.

CREATE TRIGGER tr_Orders_DELETE
ON Orders
AFTER DELETE
AS

--Determine if Order with a Priority of High was deleted
IF EXISTS (SELECT * FROM deleted WHERE Ord_Priority = 'High')
 BEGIN
  DECLARE @Count tinyint
  SET @Count = (SELECT * FROM deleted WHERE Ord_Priority = 'High')
  PRINT CAST(@Count as varchar(3))+' row(s) where deleted whose priority was High'
 END
go

INSTEAD OF Triggers

INSTEAD OF triggers are new to SQL Server 2000. The main reason they were introduced is to facilitate updating Views. I am going to show you how one works, but I am not going to bother with updating a View. Quite frankly I think updating Views is poor application design. I was once told by one of my editors that some of my views on Views reflect my "insular" experience, so if you do not agree with me on this point rest assured you are not alone.
An INSTEAD OF Trigger is used to perform an action instead of the one that caused the trigger to be fired. This sounds like double-talk, so I will explain a little more. Let's say you have an INSTEAD OF INSERT trigger defined on a table and an INSERT is executed. A row is not added to the table, but the code in the trigger is fired. The following shows what this looks like.

CREATE TRIGGER tr_Orders_INSERT_InsteadOf
ON Orders
INSTEAD OF INSERT
AS
PRINT 'Updateable Views are Messy'
go

The following INSERT produces the message shown, but the row is not added to the table.

INSERT Orders (Ord_Priority) VALUES ('High')

-- Results --

Updateable Views are Messy

Feel free to experiment with this type of trigger, but unless you are trying to update a View that is based on multiple tables I do not think it will be of much practical value.

Need More Information on Views?

If you would like more information on Views you can read a free chapter I have listed on my web site. Go to www.SQLBook.com and click on the Sample Chapters section. After reading the chapter you will really know how I feel about this pathetic excuse for a database object (just kidding).

...............................................................................................................................................................

Introduction to SQL Triggers

SQL trigger is an SQL statements or a set of SQL statements which is stored to be activated or fired when an event associating with a database table occurs. The event can be any event including INSERT, UPDATE and DELETE.

Sometimes a trigger is referred as a special kind of stored procedure in term of procedural code inside its body. The difference between a trigger and a stored procedure is that a trigger is activated or called when an event happens in a database table, a stored procedure must be called explicitly. For example you can have some business logic to do before or after inserting a new record in a database table.

Before applying trigger in your database project, you should know its pros and cons to use it properly.

Advantages of using SQL trigger

SQL Trigger provides an alternative way to check integrity.
SQL trigger can catch the errors in business logic in the database level.
SQL trigger provides an alternative way to run scheduled tasks. With SQL trigger, you don’t have to wait to run the scheduled tasks. You can handle those tasks before or after changes being made to database tables.
SQL trigger is very useful when you use it to audit the changes of data in a database table.

Disadvantages of using SQL trigger

SQL trigger only can provide extended validation and cannot replace all the validations. Some simple validations can be done in the application level. For example, you can validate input check in the client side by using javascript or in the server side by server script using PHP or ASP.NET.
SQL Triggers executes invisibly from client-application which connects to the database server so it is difficult to figure out what happen underlying database layer.
SQL Triggers run every updates made to the table therefore it adds workload to the database and cause system runs slower.

Triggers or stored procedures? It depends on the the situation but it is practical that if you have no way to get the work done with stored procedure, think about triggers.

.............................................................................................................................................................

SQL Server 2005 - Defining Indexes

Posted by Technalogy Information at 7:16 AM Labels: SQL 0 comments

SQL Server 2005 - Defining Indexes

In this tutorial you will learn about Defining Indexes in SQL Server 2005 - clustered and non clustered indexex, The Query Optimizer, to create an index, To create a unique index, To create a clustered index, To create full-text indexes, To change index properties, To rename an index, to delete an index, To specify a fill factor for an index, To create an XML index and To delete XML Indexes.

Sponsored Links

When data volumes increase, organizations are faced with problems relating to data retrieval and posting. They feel the need for a mechanism that will increase the speed of data access. An index, like the index of a book, enables the database retrieve and present data to the end user with ease. An index can be defined as a mechanism for providing fast access to table rows and for enforcing constraints.

An index can be created by selecting one or more columns in a table that is being searched. It is a kind of ‘on disk’ structure associated with the table or view and contains keys that are built from one or more of the columns in the table or view. This structure known as B-Tree helps the SQL Server find the row or rows associated with the key values. Indexes can be created on computed columns or xml columns also.

Indexes can be clustered or non clustered. A clustered index stores data rows in the table based on their key values. Each table can have only one clustered index as the key values in the data rows are unique and the index is built on the unique key column. When a table has a clustered index, it is known as a clustered table. Non-Clustered indexes have structures that are different from the data rows. A non clustered index key value is used to point to data rows that contain the key value. This value is known as row locator. The structure of the row locator is determined on the basis of the type of storage of the data pages. If the data page is stored as a heap, a row locator becomes a pointer to a row. If the data page is stored in a clustered table the row locator is a clustered index key. Clustered and Non clustered indexes can be unique and indexes are automatically maintained for a table or view whenever the data table is modified.

SQL Server 2005 permits users add non-key columns to leaf level of the non clustered index for by passing existing index key limits and to execute fully covered index queries.

When the primary key and unique constraints of a table column are defined an automatic index is created.

The Query Optimizer uses indexes to reduce disk I/O operations and use of system resources while querying on data. Queries which contain SELECT, UPDATE or DELETE statements require indexes for optimal performance. When a query is executed, each available method is evaluated for retrieving data and the most efficient one is selected by the Query optimizer. The methodology used may be table scans or index scans. In table scans I/O operations are many and resource intensive as all rows in a table are scanned to find the relevant ones. Index scans are used to search the index key columns to find the storage location of rows needed by the query and as the Index contains very few columns, the query executes faster.

SQL Server 2005 provides the user with a new Transact-SQL DDL statement for modifying relational and XML indexes. The CREATE INDEX statement is enhanced to support XML index syntax, partitioning and the included columns. A number of new index options have been added including the ONLINE option that allows for concurrent user access to underlying data during index operations.

To create an index

1. In Object Explorer, right-click the table for which you want to create an index and click Modify.

2. The table opens in Table Designer.

3. From the Table Designer menu, click Indexes/Keys.

4. In the Indexes/Keys dialog box, click Add.

5. Select the new index in the Selected Primary/Unique Key or Index list and set properties for the index in the grid to the right.

6. Specify any other settings for the index and click Close.

7. The index is created in the database when you save the table.

SQL Server allows users create unique indexes on unique columns such as the identity number of the employee or student or whatever is the unique key by which the component data are identified. A set of columns also can be used to create a unique index. The DBA can set the option of ignoring duplicate keys in a unique index if required. The default is No.

To create a unique index

In Object Explorer, right-click the table and click Modify.
The table opens in Table Designer.
From the Table Designer menu, click Indexes/Keys.
Click Add. The Selected Primary/Unique Key or Index list displays the system-assigned name of the new index.

5. In the grid, click Type.

6. Choose Index from the drop-down list to the right of the property.

7. Under Column name, select the columns you want to index. You can select up to 16 columns. For optimal performance, select only one or two columns per index. For each column you select, indicate whether the index arranges values of this column in ascending or descending order.

8. In the grid, click Is Unique.

9. Choose Yes from the drop-down list to the right of the property.

10. Select the Ignore duplicate keys option if you want to ignore new or updated data that would create a duplicate key in the index (with the INSERT or UPDATE statement).

11. The index is created in the database when you save the table or diagram.

Please note that unique indexes cannot be created on a single column if the column contains NULL in more than one row. Similarly indexes cannot be created on multiple columns if the combination of the columns contains NULL in some rows. The NULL values are treated as duplicate values.

Clustered indexes can be created in SQL Server databases. In such cases the logical order of the index key values will be the same as the physical order of rows in the table. A table can have only one clustered index.

To create a clustered index

1. In Object Explorer, right-click the table for which you want to create a clustered index and click Modify.
2. The table opens in Table Designer.
3. From the Table Designer menu, click Indexes/Keys.
4. In the Indexes/Keys dialog box, click Add.
5. Select the new index in the Selected Primary/Unique Key or Index list.
6. In the grid, select Create as Clustered, and choose Yes from the drop-down list to the right of the property.

7. The index is created in the database when you save the table.

A full text index is used when a full text search is required to be performed on all the text based columns of the database. This index relies on a regular index which has to be created before a full text index is created. The regular index is created on a single, non null column. Usually a column with small values is selected for the indexation in a regular index. Often a Catalog is created using an external tool such as SQL Server Management Studio. Textual data from different text file formats are to be stored as image type files before Full text search can be done on the data.

To create full-text indexes

In Object Explorer, right-click the table for which you want to create a full-text index and click Modify.
The table opens in Table Designer.
From the Table Designer menu, click Fulltext Index.

4. The Full-text Index dialog box opens. If the database is not enabled for full text indexing the dialog box will have the add button disabled. To enable full text indexing for the database, right click the database>Click properties and check the Full text indexing check box.

5. Then create a catalog by right clicking on Storage>Full Text Catalog and creating a new Catalog and entering the required information in the dialog box that opens.

6. Now open the Full Text Index property dialog box by clicking on it in the Table Designer menu.

7. Click Add.
8. Select the new index in the Selected Full-text Index list and set properties for the index in the grid to the right.
9. Your index is automatically saved in the database when you save your table in Table Designer. The index is available for modification as soon as you create it.

To change index properties

1. In Object Explorer, right-click the table you want to open and click Modify.
2. From the Table Designer menu, click Indexes/Keys.
3. Select the index from the Selected Primary/Unique Key or Index list.
4. Change properties in the grid.
5. The changes are saved to the database when you save the table.

System defined names are assigned to indexes based on the database file name. If multiple indexes are created on a table the index names are incremented numerically with _1, _2 etc. An index can be renamed to be unique to a table. Since the automatically created index bears the same name as the primary key or unique constraint in a table, another index cannot be renamed later to match the primary key or unique constraint.

To rename an index

1. In Object Explorer, right-click the table with the index you want to rename and click Modify.
2. From the Table Designer menu, click Indexes/Keys.
3. Select the index from the Selected Primary/Unique Key or Index list.
4. In the grid, click Name and type a new name into the text box.

5. The changes are saved to the database when you save the table.

Indexes can be deleted. Usually an index is considered for deletion when the performance of the INSERT,UPDATE and DELETE operations are hindered by the Index.

To delete an index

1. In Object Explorer, right-click the table with indexes you want to delete and click Modify.
2. From the Table Designer menu, click Indexes/Keys.
3. In the Indexes/Keys dialog box, select the index you want to delete.
4. Click Delete.
5. The index is deleted from the database when the table is saved.
6. A similar procedure can be followed for deleting a full text index by selecting Full text index from the Table Designer and selecting the index name and clicking delete button.

Microsoft SQL Server database uses a fill factor to specify how full each index page can be. The percentage of free space allotted to an index is defined as the fill factor. This is an important aspect of indexing as the amount of space to be filled by an index has to be determined by the DBA so that performance is not retarded.

To specify a fill factor for an index

1. In Object Explorer, right-click the table with an index for which you want to specify a fill factor and click Modify.
2. The table opens in Table Designer.
3. From the Table Designer menu, click Indexes/Keys.
4. The Indexes/Keys dialog box opens.
5. Select the index in the Selected Primary/Unique Key or Index list.
6. In the Fill Factor box, type a number from 0 to 100. The value of 100 implies that the index will fill up completely and the storage space required will be minimal. This setting is recommended only for cases where data is unlikely to change. If data is likely to undergo addition and modification, it is better to set a lower value. Storage space required would be in proportion to the value set.

XML indexes cannot be created using the Index/Keys dialog box. One or more XML indexes can be created for xml data type columns on the basis of a primary xml index. Deleting the primary xml index will result in the deletion of all indexes created on the base of the primary index.

To create an XML index

1. In Object Explorer, right-click the table for which you want to create an XML index and click Modify. 2. The table opens in Table Designer.
3. Select the xml column for the index.
4. From the Table Designer menu, click XML Index.

5. In the XML Indexes dialog box, click Add.

Sponsored Links

6. Select the new index in the Selected XML Index list and set properties for the index in the grid to the right.

To delete XML Indexes

1. In Object Explorer, right-click the table with the XML index you want to delete and click Modify.
2. The table opens in Table Designer.
3. From the Table Designer menu, click XML Index.
4. The XML Index dialog box opens.
5. Click the index you want to delete in the Selected XML Index column.
6. Click Delete.

..............................................................................................................................................................

CREATE INDEX (Transact-SQL)

SQL Server 2008 R2

Other Versions

Creates a relational index on a specified table or view on a specified table. An index can be created before there is data in the table. Relational indexes can be created on tables or views in another database by specifying a qualified database name.

Note
For information about how to create an XML index, see CREATE XML INDEX (Transact-SQL). For information about how to create a spatial index, see CREATE SPATIAL INDEX (Transact-SQL).

Transact-SQL Syntax Conventions

Syntax

Copy

Create Relational Index 
CREATE [ UNIQUE ] [ CLUSTERED | NONCLUSTERED ] INDEX index_name 
    ON <object> (column [ ASC | DESC ] [ ,...n ] ) 
    [ INCLUDE (column_name [ ,...n ] ) ]
    [ WHERE <filter_predicate> ]
    [ WITH ( <relational_index_option> [ ,...n ] ) ]
    [ ON { partition_scheme_name (column_name) 
         | filegroup_name 
         | default 
         }
    ]
    [ FILESTREAM_ON { filestream_filegroup_name | partition_scheme_name | "NULL" } ]

[ ; ]

<object> ::=
{
    [ database_name. [ schema_name ] . | schema_name. ] 
    table_or_view_name
}

<relational_index_option> ::=
{
    PAD_INDEX = { ON | OFF }
  | FILLFACTOR =fillfactor
  | SORT_IN_TEMPDB = { ON | OFF }
  | IGNORE_DUP_KEY = { ON | OFF }
  | STATISTICS_NORECOMPUTE = { ON | OFF }
  | DROP_EXISTING = { ON | OFF }
  | ONLINE = { ON | OFF }
  | ALLOW_ROW_LOCKS = { ON | OFF }
  | ALLOW_PAGE_LOCKS = { ON | OFF }
  | MAXDOP =max_degree_of_parallelism
  | DATA_COMPRESSION = { NONE | ROW | PAGE} 
     [ ON PARTITIONS ( { <partition_number_expression> | <range> } 
     [ , ...n ] ) ]
}

<filter_predicate> ::= 
    <conjunct> [ AND <conjunct> ]

<conjunct> ::=
    <disjunct> | <comparison>

<disjunct> ::=column_name IN (constant ,...n)

<comparison> ::=column_name <comparison_op> constant<comparison_op> ::=
    { IS | IS NOT | = | <> | != | > | >= | !> | < | <= | !< }

<range> ::= 
<partition_number_expression> TO <partition_number_expression>


Backward Compatible Relational IndexImportant   The backward compatible relational index syntax structure will be removed in a future version of SQL Server. Avoid using this syntax structure in new development work, and plan to modify applications that currently use the feature. Use the syntax structure specified in <relational_index_option> instead.

CREATE [ UNIQUE ] [ CLUSTERED | NONCLUSTERED ] INDEX index_name 
    ON <object> (column_name [ ASC | DESC ] [ ,...n ] ) 
    [ WITH <backward_compatible_index_option> [ ,...n ] ]
    [ ON { filegroup_name | "default" } ]

<object> ::=
{
    [ database_name. [ owner_name ] . | owner_name. ] 
    table_or_view_name
}

<backward_compatible_index_option> ::=
{ 
    PAD_INDEX
  | FILLFACTOR = fillfactor
  | SORT_IN_TEMPDB
  | IGNORE_DUP_KEY
  | STATISTICS_NORECOMPUTE 
  | DROP_EXISTING 
}

Arguments

UNIQUE

Creates a unique index on a table or view. A unique index is one in which no two rows are permitted to have the same index key value. A clustered index on a view must be unique.The Database Engine does not allow creating a unique index on columns that already include duplicate values, whether or not IGNORE_DUP_KEY is set to ON. If this is tried, the Database Engine displays an error message. Duplicate values must be removed before a unique index can be created on the column or columns. Columns that are used in a unique index should be set to NOT NULL, because multiple null values are considered duplicates when a unique index is created.

CLUSTERED

Creates an index in which the logical order of the key values determines the physical order of the corresponding rows in a table. The bottom, or leaf, level of the clustered index contains the actual data rows of the table. A table or view is allowed one clustered index at a time. For more information, see Clustered Index Structures.A view with a unique clustered index is called an indexed view. Creating a unique clustered index on a view physically materializes the view. A unique clustered index must be created on a view before any other indexes can be defined on the same view. For more information, see Designing Indexed Views.Create the clustered index before creating any nonclustered indexes. Existing nonclustered indexes on tables are rebuilt when a clustered index is created. If CLUSTERED is not specified, a nonclustered index is created.

Note
Because the leaf level of a clustered index and the data pages are the same by definition, creating a clustered index and using the ON partition_scheme_name or ON filegroup_name clause effectively moves a table from the filegroup on which the table was created to the new partition scheme or filegroup. Before creating tables or indexes on specific filegroups, verify which filegroups are available and that they have enough empty space for the index. For more information, see Determining Index Disk Space Requirements.

Note

Because the leaf level of a clustered index and the data pages are the same by definition, creating a clustered index and using the ON partition_scheme_name or ON filegroup_name clause effectively moves a table from the filegroup on which the table was created to the new partition scheme or filegroup. Before creating tables or indexes on specific filegroups, verify which filegroups are available and that they have enough empty space for the index. For more information, see Determining Index Disk Space Requirements.

NONCLUSTERED

Creates an index that specifies the logical ordering of a table. With a nonclustered index, the physical order of the data rows is independent of their indexed order. For more information, see Nonclustered Index Structures.Each table can have up to 999 nonclustered indexes, regardless of how the indexes are created: either implicitly with PRIMARY KEY and UNIQUE constraints, or explicitly with CREATE INDEX.For indexed views, nonclustered indexes can be created only on a view that has a unique clustered index already defined. The default is NONCLUSTERED.

index_name

Is the name of the index. Index names must be unique within a table or view but do not have to be unique within a database. Index names must follow the rules of identifiers.

column

Is the column or columns on which the index is based. Specify two or more column names to create a composite index on the combined values in the specified columns. List the columns to be included in the composite index, in sort-priority order, inside the parentheses after table_or_view_name. Up to 16 columns can be combined into a single composite index key. All the columns in a composite index key must be in the same table or view. The maximum allowable size of the combined index values is 900 bytes.Columns that are of the large object (LOB) data types ntext, text, varchar(max), nvarchar(max), varbinary(max), xml, or image cannot be specified as key columns for an index. Also, a view definition cannot include ntext, text, or image columns, even if they are not referenced in the CREATE INDEX statement. You can create indexes on CLR user-defined type columns if the type supports binary ordering. You can also create indexes on computed columns that are defined as method invocations off a user-defined type column, as long as the methods are marked deterministic and do not perform data access operations. For more information about indexing CLR user-defined type columns, see CLR User-defined Types.

[ ASC | DESC ]

Determines the ascending or descending sort direction for the particular index column. The default is ASC.

INCLUDE (column [ ,... n ] )

Specifies the non-key columns to be added to the leaf level of the nonclustered index. The nonclustered index can be unique or non-unique. Column names cannot be repeated in the INCLUDE list and cannot be used simultaneously as both key and non-key columns. Nonclustered indexes always contain the clustered index columns if a clustered index is defined on the table. For more information, see Index with Included Columns. All data types are allowed except text, ntext, and image. The index must be created or rebuilt offline (ONLINE = OFF) if any one of the specified non-key columns are varchar(max), nvarchar(max), or varbinary(max) data types. Computed columns that are deterministic and either precise or imprecise can be included columns. Computed columns derived from image, ntext, text, varchar(max), nvarchar(max), varbinary(max), and xml data types can be included in non-key columns as long as the computed column data types is allowable as an included column. For more information, see Creating Indexes on Computed Columns. For information on creating an XML index, see CREATE XML INDEX (Transact-SQL).

WHERE <filter_predicate>

Creates a filtered index by specifying which rows to include in the index. The filtered index must be a nonclustered index on a table. Creates filtered statistics for the data rows in the filtered index.The filter predicate uses simple comparison logic and cannot reference a computed column, a UDT column, a spatial data type column, or a hierarchyID data type column. Comparisons using NULL literals are not allowed with the comparison operators. Use the IS NULL and IS NOT NULL operators instead. Here are some examples of filter predicates for the Production.BillOfMaterials table:WHERE StartDate > '20040101' AND EndDate <= '20040630' WHERE ComponentID IN (533, 324, 753) WHERE StartDate IN ('20040404', '20040905') AND EndDate IS NOT NULL Filtered indexes do not apply to XML indexes and full-text indexes. For UNIQUE indexes, only the selected rows must have unique index values. Filtered indexes do not allow the IGNORE_DUP_KEY option.

ON partition_scheme_name(column_name)

Specifies the partition scheme that defines the filegroups onto which the partitions of a partitioned index will be mapped. The partition scheme must exist within the database by executing either CREATE PARTITION SCHEME or ALTER PARTITION SCHEME. column_name specifies the column against which a partitioned index will be partitioned. This column must match the data type, length, and precision of the argument of the partition function that partition_scheme_name is using. column_name is not restricted to the columns in the index definition. Any column in the base table can be specified, except when partitioning a UNIQUE index, column_name must be chosen from among those used as the unique key. This restriction allows the Database Engine to verify uniqueness of key values within a single partition only.

Note
When you partition a non-unique, clustered index, the Database Engine by default adds the partitioning column to the list of clustered index keys, if it is not already specified. When partitioning a non-unique, nonclustered index, the Database Engine adds the partitioning column as a non-key (included) column of the index, if it is not already specified.

If partition_scheme_name or filegroup is not specified and the table is partitioned, the index is placed in the same partition scheme, using the same partitioning column, as the underlying table.

Note
You cannot specify a partitioning scheme on an XML index. If the base table is partitioned, the XML index uses the same partition scheme as the table. For information on creating an XML index, see CREATE XML INDEX (Transact-SQL).

For more information about partitioning indexes, see Special Guidelines for Partitioned Indexes.

ON filegroup_name

Creates the specified index on the specified filegroup. If no location is specified and the table or view is not partitioned, the index uses the same filegroup as the underlying table or view. The filegroup must already exist.

ON "default"

Creates the specified index on the default filegroup. The term default, in this context, is not a keyword. It is an identifier for the default filegroup and must be delimited, as in ON "default" or ON [default]. If "default" is specified, the QUOTED_IDENTIFIER option must be ON for the current session. This is the default setting. For more information, see SET QUOTED_IDENTIFIER (Transact-SQL).

[ FILESTREAM_ON { filestream_filegroup_name | partition_scheme_name | "NULL" } ]

Specifies the placement of FILESTREAM data for the table when a clustered index is created. The FILESTREAM_ON clause allows FILESTREAM data to be moved to a different FILESTREAM filegroup or partition scheme. filestream_filegroup_name is the name of a FILESTREAM filegroup. The filegroup must have one file defined for the filegroup by using a CREATE DATABASE or ALTER DATABASE statement; otherwise, an error is raised.If the table is partitioned, the FILESTREAM_ON clause must be included and must specify a partition scheme of FILESTREAM filegroups that uses the same partition function and partition columns as the partition scheme for the table. Otherwise, an error is raised. If the table is not partitioned, the FILESTREAM column cannot be partitioned. FILESTREAM data for the table must be stored in a single filegroup that is specified in the FILESTREAM_ON clause.FILESTREAM_ON NULL can be specified in a CREATE INDEX statement if a clustered index is being created and the table does not contain a FILESTREAM column.For a list of FILESTREAM topics, see Designing and Implementing FILESTREAM Storage.

database_name: Is the name of the database.
schema_name: Is the name of the schema to which the table or view belongs.
table_or_view_name: Is the name of the table or view to be indexed.The view must be defined with SCHEMABINDING to create an index on it. A unique clustered index must be created on a view before any nonclustered index is created. For more information about indexed views, see the Remarks section.

<relational_index_option>::=
Specifies the options to use when you create the index.

PAD_INDEX = { ON | OFF }

Specifies index padding. The default is OFF.

ON: The percentage of free space that is specified by fillfactor is applied to the intermediate-level pages of the index.
OFF or fillfactor is not specified: The intermediate-level pages are filled to near capacity, leaving sufficient space for at least one row of the maximum size the index can have, considering the set of keys on the intermediate pages.

The PAD_INDEX option is useful only when FILLFACTOR is specified, because PAD_INDEX uses the percentage specified by FILLFACTOR. If the percentage specified for FILLFACTOR is not large enough to allow for one row, the Database Engine internally overrides the percentage to allow for the minimum. The number of rows on an intermediate index page is never less than two, regardless of how low the value of fillfactor.
In backward compatible syntax, WITH PAD_INDEX is equivalent to WITH PAD_INDEX = ON.

FILLFACTOR =fillfactor

Specifies a percentage that indicates how full the Database Engine should make the leaf level of each index page during index creation or rebuild. fillfactor must be an integer value from 1 to 100. The default is 0. If fillfactor is 100 or 0, the Database Engine creates indexes with leaf pages filled to capacity.

Note
Fill factor values 0 and 100 are the same in all respects.

The FILLFACTOR setting applies only when the index is created or rebuilt. The Database Engine does not dynamically keep the specified percentage of empty space in the pages. To view the fill factor setting, use the sys.indexes catalog view.

Important
Creating a clustered index with a FILLFACTOR less than 100 affects the amount of storage space the data occupies because the Database Engine redistributes the data when it creates the clustered index.

For more information, see Fill Factor.

SORT_IN_TEMPDB = { ON | OFF }

Specifies whether to store temporary sort results in tempdb. The default is OFF.

ON: The intermediate sort results that are used to build the index are stored in tempdb. This may reduce the time required to create an index if tempdb is on a different set of disks than the user database. However, this increases the amount of disk space that is used during the index build.
OFF: The intermediate sort results are stored in the same database as the index.

In addition to the space required in the user database to create the index, tempdb must have about the same amount of additional space to hold the intermediate sort results. For more information, see tempdb and Index Creation.
In backward compatible syntax, WITH SORT_IN_TEMPDB is equivalent to WITH SORT_IN_TEMPDB = ON.

IGNORE_DUP_KEY = { ON | OFF }

Specifies the error response when an insert operation attempts to insert duplicate key values into a unique index. The IGNORE_DUP_KEY option applies only to insert operations after the index is created or rebuilt. The option has no effect when executing CREATE INDEX, ALTER INDEX, or UPDATE. The default is OFF.

ON: A warning message will occur when duplicate key values are inserted into a unique index. Only the rows violating the uniqueness constraint will fail.
OFF: An error message will occur when duplicate key values are inserted into a unique index. The entire INSERT operation will be rolled back.

IGNORE_DUP_KEY cannot be set to ON for indexes created on a view, non-unique indexes, XML indexes, spatial indexes, and filtered indexes.
To view IGNORE_DUP_KEY, use sys.indexes.
In backward compatible syntax, WITH IGNORE_DUP_KEY is equivalent to WITH IGNORE_DUP_KEY = ON.

STATISTICS_NORECOMPUTE = { ON | OFF}

Specifies whether distribution statistics are recomputed. The default is OFF.

ON: Out-of-date statistics are not automatically recomputed.
OFF: Automatic statistics updating are enabled.

To restore automatic statistics updating, set the STATISTICS_NORECOMPUTE to OFF, or execute UPDATE STATISTICS without the NORECOMPUTE clause.

Important
Disabling automatic recomputation of distribution statistics may prevent the query optimizer from picking optimal execution plans for queries involving the table.

In backward compatible syntax, WITH STATISTICS_NORECOMPUTE is equivalent to WITH STATISTICS_NORECOMPUTE = ON.

DROP_EXISTING = { ON | OFF }

Specifies that the named, preexisting clustered, or nonclustered is dropped and rebuilt. The default is OFF.

ON: The existing index is dropped and rebuilt. The index name specified must be the same as a currently existing index; however, the index definition can be modified. For example, you can specify different columns, sort order, partition scheme, or index options.
OFF: An error is displayed if the specified index name already exists.

The index type cannot be changed by using DROP_EXISTING.
In backward compatible syntax, WITH DROP_EXISTING is equivalent to WITH DROP_EXISTING = ON.

ONLINE = { ON | OFF }

Specifies whether underlying tables and associated indexes are available for queries and data modification during the index operation. The default is OFF.

Note
Online index operations are available only in SQL Server Enterprise, Developer, and Evaluation editions.

ON: Long-term table locks are not held for the duration of the index operation. During the main phase of the index operation, only an Intent Share (IS) lock is held on the source table. This enables queries or updates to the underlying table and indexes to proceed. At the start of the operation, a Shared (S) lock is held on the source object for a very short period of time. At the end of the operation, for a short period of time, an S (Shared) lock is acquired on the source if a nonclustered index is being created; or an SCH-M (Schema Modification) lock is acquired when a clustered index is created or dropped online and when a clustered or nonclustered index is being rebuilt. ONLINE cannot be set to ON when an index is being created on a local temporary table.
OFF: Table locks are applied for the duration of the index operation. An offline index operation that creates, rebuilds, or drops a clustered index, or rebuilds or drops a nonclustered index, acquires a Schema modification (Sch-M) lock on the table. This prevents all user access to the underlying table for the duration of the operation. An offline index operation that creates a nonclustered index acquires a Shared (S) lock on the table. This prevents updates to the underlying table but allows read operations, such as SELECT statements.

For more information, see How Online Index Operations Work. For more information about locks, see Lock Modes.
Indexes, including indexes on global temp tables, can be created online with the following exceptions:

XML index.
Index on a local temp table.
Initial unique clustered index on a view.
Disabled clustered indexes.
Clustered index if the underlying table contains LOB data types: image, ntext, text, varchar(max), nvarchar(max), varbinary(max), and xml.

Nonclustered index defined with LOB data type columns.

Note
A non-unique nonclustered index can be created online if the table contains LOB data types but none of these columns are used in the index definition as either key or non-key (included) columns.

For more information, see Performing Index Operations Online.

ALLOW_ROW_LOCKS = { ON | OFF }

Specifies whether row locks are allowed. The default is ON.

ON: Row locks are allowed when accessing the index. The Database Engine determines when row locks are used.
OFF: Row locks are not used.

ALLOW_PAGE_LOCKS = { ON | OFF }

Specifies whether page locks are allowed. The default is ON.

ON: Page locks are allowed when accessing the index. The Database Engine determines when page locks are used.
OFF: Page locks are not used.

MAXDOP = max_degree_of_parallelism

Overrides the max degree of parallelism configuration option for the duration of the index operation. Use MAXDOP to limit the number of processors used in a parallel plan execution. The maximum is 64 processors.
max_degree_of_parallelism can be:

1: Suppresses parallel plan generation.
>1: Restricts the maximum number of processors used in a parallel index operation to the specified number or fewer based on the current system workload.
0 (default): Uses the actual number of processors or fewer based on the current system workload.

For more information, see Configuring Parallel Index Operations.

Note
Parallel index operations are available only in SQL Server Enterprise, Developer, and Evaluation editions.

DATA_COMPRESSION

Specifies the data compression option for the specified index, partition number, or range of partitions. The options are as follows:

NONE: Index or specified partitions are not compressed.
ROW: Index or specified partitions are compressed by using row compression.
PAGE: Index or specified partitions are compressed by using page compression.

For more information about compression, see Creating Compressed Tables and Indexes.

ON PARTITIONS ( { <partition_number_expression> | <range> } [ ,...n ] )

Specifies the partitions to which the DATA_COMPRESSION setting applies. If the index is not partitioned, the ON PARTITIONS argument will generate an error. If the ON PARTITIONS clause is not provided, the DATA_COMPRESSION option applies to all partitions of a partitioned index.
<partition_number_expression> can be specified in the following ways:

Provide the number for a partition, for example: ON PARTITIONS (2).
Provide the partition numbers for several individual partitions separated by commas, for example: ON PARTITIONS (1, 5).
Provide both ranges and individual partitions, for example: ON PARTITIONS (2, 4, 6 TO 8).

<range> can be specified as partition numbers separated by the word TO, for example: ON PARTITIONS (6 TO 8).
To set different types of data compression for different partitions, specify the DATA_COMPRESSION option more than once, for example:

Copy

REBUILD WITH 
(
DATA_COMPRESSION = NONE ON PARTITIONS (1), 
DATA_COMPRESSION = ROW ON PARTITIONS (2, 4, 6 TO 8), 
DATA_COMPRESSION = PAGE ON PARTITIONS (3, 5)
)

Remarks

The CREATE INDEX statement is optimized like any other query. To save on I/O operations, the query processor may choose to scan another index instead of performing a table scan. The sort operation may be eliminated in some situations. On multiprocessor computers that are running SQL Server 2005 Enterprise Edition or SQL Server 2008, CREATE INDEX can use more processors to perform the scan and sort operations associated with creating the index, in the same way as other queries do. For more information, see Configuring Parallel Index Operations.
The create index operation can be minimally logged if the database recovery model is set to either bulk-logged or simple. For more information, see Choosing a Recovery Model for Index Operations.
Indexes can be created on a temporary table. When the table is dropped or the session ends, the indexes are dropped.
Indexes support extended properties. For more information, see Using Extended Properties on Database Objects.

Clustered Indexes

Creating a clustered index on a table (heap) or dropping and re-creating an existing clustered index requires additional workspace to be available in the database to accommodate data sorting and a temporary copy of the original table or existing clustered index data. For more information, see Determining Index Disk Space Requirements. For more information about clustered indexes, see Creating Clustered Indexes.

Unique Indexes

When a unique index exists, the Database Engine checks for duplicate values each time data is added by a insert operations. Insert operations that would generate duplicate key values are rolled back, and the Database Engine displays an error message. This is true even if the insert operation changes many rows but causes only one duplicate. If an attempt is made to enter data for which there is a unique index and the IGNORE_DUP_KEY clause is set to ON, only the rows violating the UNIQUE index fail. For more information about unique indexes, see Creating Unique Indexes.

Partitioned Indexes

Partitioned indexes are created and maintained in a similar manner to partitioned tables, but like ordinary indexes, they are handled as separate database objects. You can have a partitioned index on a table that is not partitioned, and you can have a nonpartitioned index on a table that is partitioned.
If you are creating an index on a partitioned table, and do not specify a filegroup on which to place the index, the index is partitioned in the same manner as the underlying table. This is because indexes, by default, are placed on the same filegroups as their underlying tables, and for a partitioned table in the same partition scheme that uses the same partitioning columns.
When partitioning a non-unique, clustered index, the Database Engine by default adds any partitioning columns to the list of clustered index keys, if not already specified.
Indexed views can be created on partitioned tables in the same manner as indexes on tables. For more information about partitioned indexes, see Partitioned Tables and Indexes.

Indexed Views

Creating a unique clustered index on a view improves query performance because the view is stored in the database in the same way a table with a clustered index is stored. The query optimizer may use indexed views to speed up the query execution. The view does not have to be referenced in the query for the optimizer to consider that view for a substitution.
The following steps are required to create an indexed view are critical to the successful implementation of the view:

Verify the SET options are correct for all existing tables that will be referenced in the view.
Verify the SET options for the session are set correctly before creating any new tables and the view.
Verify the view definition is deterministic.
Create the view by using the WITH SCHEMABINDING option.
Create the unique clustered index on the view.

Required SET Options for Indexed Views

Evaluating the same expression can produce different results in the Database Engine if different SET options are active when the query is executed. For example, after the SET option CONCAT_NULL_YIELDS_NULL is set to ON, the expression 'abc' + NULL returns the value NULL. However, after CONCAT_NULL_YIEDS_NULL is set to OFF, the same expression produces 'abc'.
To make sure that the views can be maintained correctly and return consistent results, indexed views require fixed values for several SET options. The SET options in the following table must be set to the values shown in the RequiredValue column whenever the following conditions occur:

The indexed view is created.
There is any insert, update, or delete operation performed on any table that participates in the indexed view. This includes operations such as bulk copy, replication, and distributed queries.

The indexed view is used by the query optimizer to produce the query plan.

SET options	Required value	Default server value	Default OLE DB and ODBC value	Default DB-Library value
ANSI_NULLS	ON	ON	ON	OFF
ANSI_PADDING	ON	ON	ON	OFF
ANSI_WARNINGS*	ON	ON	ON	OFF
ARITHABORT	ON	ON	OFF	OFF
CONCAT_NULL_YIELDS_NULL	ON	ON	ON	OFF
NUMERIC_ROUNDABORT	OFF	OFF	OFF	OFF
QUOTED_IDENTIFIER	ON	ON	ON	OFF

*Setting ANSI_WARNINGS to ON implicitly sets ARITHABORT to ON when the database compatibility level is set to 90 or higher. If the database compatibility level is set to 80 or earlier, the ARITHABORT option must explicitly be set to ON.

If you are using an OLE DB or ODBC server connection, the only value that must be modified is the ARITHABORT setting. All DB-Library values must be set correctly either at the server level by using sp_configure or from the application by using the SET command. For more information about SET options, see Using Options in SQL Server.

Important
We strongly recommend that the ARITHABORT user option be set server-wide to ON as soon as the first indexed view or index on a computed column is created in any database on the server.

Deterministic Functions

The definition of an indexed view must be deterministic. A view is deterministic if all expressions in the select list, as well as the WHERE and GROUP BY clauses, are deterministic. Deterministic expressions always return the same result any time they are evaluated with a specific set of input values. Only deterministic functions can participate in deterministic expressions. For example, the DATEADD function is deterministic because it always returns the same result for any given set of argument values for its three parameters. GETDATE is not deterministic because it is always invoked with the same argument, but the value it returns changes each time it is executed. For more information, see Deterministic and Nondeterministic Functions.
Even if an expression is deterministic, if it contains float expressions, the exact result may depend on the processor architecture or version of microcode. To ensure data integrity, such expressions can participate only as non-key columns of indexed views. Deterministic expressions that do not contain float expressions are called precise. Only precise deterministic expressions can participate in key columns and in WHERE or GROUP BY clauses of indexed views.
Use the IsDeterministic property of the COLUMNPROPERTY function to determine whether a view column is deterministic. Use the IsPrecise property of the COLUMNPROPERTY function to determine if a deterministic column in a view with schema binding is precise. COLUMNPROPERTY returns 1 if TRUE, 0 if FALSE, and NULL for input that is not valid. This means the column is not deterministic or not precise.

Additional Requirements

In addition to the SET options and deterministic function requirements, the following requirements must be met:

The user that executes CREATE INDEX must be the owner of the view.
If the view definition contains a GROUP BY clause, the key of the unique clustered index can reference only the columns specified in the GROUP BY clause.
Base tables must have the correct SET options set at the time the table is created or it cannot be referenced by the view with schema binding.
Tables must be referenced by two-part names, schema.tablename, in the view definition.
User-defined functions must be created by using the WITH SCHEMABINDING option.
User-defined functions must be referenced by two-part names, schema.function.
The view must be created by using the WITH SCHEMABINDING option.
The view must reference only base tables in the same database, not other views.

The view definition must not contain the following:

COUNT(*)	ROWSET function
Derived table	self-join
DISTINCT	STDEV, VARIANCE, AVG
float *, text, ntext, or image columns	Subquery
full-text predicates (CONTAIN, FREETEXT)	SUM on nullable expression
CLR user-defined aggregate function	TOP
MIN, MAX	UNION

*The indexed view can contain float columns; however, such columns cannot be included in the clustered index key.

If GROUP BY is present, the VIEW definition must contain COUNT_BIG(*) and must not contain HAVING. These GROUP BY restrictions are applicable only to the indexed view definition. A query can use an indexed view in its execution plan even if it does not satisfy these GROUP BY restrictions.
Indexed views can be created on a partitioned table, and can themselves be partitioned. For more information about partitioning, see the previous section "Partitioned Indexes".
To prevent the Database Engine from using indexed views, include the OPTION (EXPAND VIEWS) hint on the query. Also, if any of the listed options are incorrectly set, this will prevent the optimizer from using the indexes on the views. For more information about the OPTION (EXPAND VIEWS) hint, see SELECT (Transact-SQL).
The compatibility level of the database cannot be less than 80. A database containing an indexed view cannot be changed to a compatibility level lower than 80.

Filtered Indexes

A filtered index is an optimized nonclustered index, suited for queries that select a small percentage of rows from a table. It uses a filter predicate to index a portion of the data in the table. A well-designed filtered index can improve query performance, reduce storage costs, and reduce maintenance costs.

Required SET Options for Filtered Indexes

The SET options in the Required Value column are required whenever any of the following conditions occur:

Create a filtered index.
INSERT, UPDATE, DELETE, or MERGE operation modifies the data in a filtered index.

The query optimizer uses the filtered index in the query execution plan.

SET options	Required value
ANSI_NULLS	ON
ANSI_PADDING	ON
ANSI_WARNINGS*	ON
ARITHABORT	ON
CONCAT_NULL_YIELDS_NULL	ON
NUMERIC_ROUNDABORT	OFF
QUOTED_IDENTIFIER	ON

If the SET options are incorrect, the following conditions can occur:

The filtered index is not created.
The Database Engine generates an error and rolls back INSERT, UPDATE, DELETE, or MERGE statements that change data in the index.
Query optimizer does not consider the index in the execution plan for any Transact-SQL statements.

For more information about Filtered Indexes, see Filtered Index Design Guidelines.

Spatial Indexes

For information about spatial indexes, see CREATE SPATIAL INDEX (Transact-SQL) and Working with Spatial Indexes (Database Engine).

XML Indexes

For information about XML indexes see, CREATE XML INDEX (Transact-SQL) and Indexes on XML Data Type Columns.

Index Key Size

The maximum size for an index key is 900 bytes. Indexes on varchar columns that exceed 900 bytes can be created if the existing data in the columns do not exceed 900 bytes at the time the index is created; however, subsequent insert or update actions on the columns that cause the total size to be greater than 900 bytes will fail. For more information, see Maximum Size of Index Keys. The index key of a clustered index cannot contain varchar columns that have existing data in the ROW_OVERFLOW_DATA allocation unit. If a clustered index is created on a varchar column and the existing data is in the IN_ROW_DATA allocation unit, subsequent insert or update actions on the column that would push the data off-row will fail. For more information about allocation units, see Table and Index Organization.
Nonclustered indexes can include non-key columns in the leaf level of the index. These columns are not considered by the Database Engine when calculating the index key size . For more information, see Index with Included Columns.

Computed Columns

Indexes can be created on computed columns. In addition, computed columns can have the property PERSISTED. This means that the Database Engine stores the computed values in the table, and updates them when any other columns on which the computed column depends are updated. The Database Engine uses these persisted values when it creates an index on the column, and when the index is referenced in a query.
To index a computed column, the computed column must deterministic and precise. However, using the PERSISTED property expands the type of indexable computed columns to include:

Computed columns based on Transact-SQL and CLR functions and CLR user-defined type methods that are marked deterministic by the user.
Computed columns based on expressions that are deterministic as defined by the Database Engine but imprecise.

Persisted computed columns require the following SET options to be set as shown in the previous section "Required SET Options for Indexed Views".
The UNIQUE or PRIMARY KEY constraint can contain a computed column as long as it satisfies all conditions for indexing. Specifically, the computed column must be deterministic and precise or deterministic and persisted. For more information about determinism, see Deterministic and Nondeterministic Functions.
Computed columns derived from image, ntext, text, varchar(max), nvarchar(max), varbinary(max), and xml data types can be indexed either as a key or included non-key column as long as the computed column data type is allowable as an index key column or non-key column. For example, you cannot create a primary XML index on a computed xml column. If the index key size exceeds 900 bytes, a warning message is displayed.
Creating an index on a computed column may cause the failure of an insert or update operation that previously worked. Such a failure may take place when the computed column results in arithmetic error. For example, in the following table, although computed column c results in an arithmetic error, the INSERT statement works.

Copy

CREATE TABLE t1 (a int, b int, c AS a/b);
INSERT INTO t1 VALUES (1, 0);

If, instead, after creating the table, you create an index on computed column c, the same INSERT statement will now fail.

Copy

CREATE TABLE t1 (a int, b int, c AS a/b);
CREATE UNIQUE CLUSTERED INDEX Idx1 ON t1(c);
INSERT INTO t1 VALUES (1, 0);

For more information, see Creating Indexes on Computed Columns.

Included Columns in Indexes

Non-key columns, called included columns, can be added to the leaf level of a nonclustered index to improve query performance by covering the query. That is, all columns referenced in the query are included in the index as either key or non-key columns. This allows the query optimizer to locate all the required information from an index scan; the table or clustered index data is not accessed. For more information, see Index with Included Columns.

Specifying Index Options

SQL Server 2005 introduced new index options and also modifies the way in which options are specified. In backward compatible syntax, WITH option_name is equivalent to WITH ( <option_name> = ON ). When you set index options, the following rules apply:

New index options can only be specified by using WITH (option_name= ON | OFF).
Options cannot be specified by using both the backward compatible and new syntax in the same statement. For example, specifying WITH (DROP_EXISTING, ONLINE = ON) causes the statement to fail.
When you create an XML index, the options must be specified by using WITH (option_name= ON | OFF).

DROP_EXISTING Clause

You can use the DROP_EXISTING clause to rebuild the index, add or drop columns, modify options, modify column sort order, or change the partition scheme or filegroup.
If the index enforces a PRIMARY KEY or UNIQUE constraint and the index definition is not altered in any way, the index is dropped and re-created preserving the existing constraint. However, if the index definition is altered the statement fails. To change the definition of a PRIMARY KEY or UNIQUE constraint, drop the constraint and add a constraint with the new definition.
DROP_EXISTING enhances performance when you re-create a clustered index, with either the same or different set of keys, on a table that also has nonclustered indexes. DROP_EXISTING replaces the execution of a DROP INDEX statement on the old clustered index followed by the execution of a CREATE INDEX statement for the new clustered index. The nonclustered indexes are rebuilt once, and then only if the index definition has changed. The DROP_EXISTING clause does not rebuild the nonclustered indexes when the index definition has the same index name, key and partition columns, uniqueness attribute, and sort order as the original index.
Whether the nonclustered indexes are rebuilt or not, they always remain in their original filegroups or partition schemes and use the original partition functions. If a clustered index is rebuilt to a different filegroup or partition scheme, the nonclustered indexes are not moved to coincide with the new location of the clustered index. Therefore, even the nonclustered indexes previously aligned with the clustered index, they may no longer be aligned with it. For more information about partitioned index alignment, see Special Guidelines for Partitioned Indexes.
The DROP_EXISTING clause will not sort the data again if the same index key columns are used in the same order and with the same ascending or descending order, unless the index statement specifies a nonclustered index and the ONLINE option is set to OFF. If the clustered index is disabled, the CREATE INDEX WITH DROP_EXISTING operation must be performed with ONLINE set to OFF. If a nonclustered index is disabled and is not associated with a disabled clustered index, the CREATE INDEX WITH DROP_EXISTING operation can be performed with ONLINE set to OFF or ON.
When indexes with 128 extents or more are dropped or rebuilt, the Database Engine defers the actual page deallocations, and their associated locks, until after the transaction commits. For more information, see Dropping and Rebuilding Large Objects.

ONLINE Option

The following guidelines apply for performing index operations online:

The underlying table cannot be altered, truncated, or dropped while an online index operation is in process.
Additional temporary disk space is required during the index operation. For more information, see Determining Index Disk Space Requirements.
Online operations can be performed on partitioned indexes and indexes that contain persisted computed columns, or included columns.

For more information, see Performing Index Operations Online.

Row and Page Locks Options

When ALLOW_ROW_LOCKS = ON and ALLOW_PAGE_LOCK = ON, row-, page-, and table-level locks are allowed when accessing the index. The Database Engine chooses the appropriate lock and can escalate the lock from a row or page lock to a table lock. For more information, see Lock Escalation (Database Engine).
When ALLOW_ROW_LOCKS = OFF and ALLOW_PAGE_LOCK = OFF, only a table-level lock is allowed when accessing the index.
For more information about configuring the locking granularity for an index, see Customizing Locking for an Index.

Viewing Index Information

To return information about indexes, you can use catalog views, system functions, and system stored procedures. For more information, see Viewing Index Information.

Data Compression

Data compression is described in the topic Creating Compressed Tables and Indexes. The following are key points to consider:

Compression can allow more rows to be stored on a page, but does not change the maximum row size.
Non-leaf pages of an index are not page compressed but can be row compressed.
Each nonclustered index has an individual compression setting, and does not inherit the compression setting of the underlying table.
When a clustered index is created on a heap, the clustered index inherits the compression state of the heap unless an alternative compression state is specified.

The following restrictions apply to partitioned indexes:

You cannot change the compression setting of a single partition if the table has nonaligned indexes.
The ALTER INDEX <index> ... REBUILD PARTITION ... syntax rebuilds the specified partition of the index.
The ALTER INDEX <index> ... REBUILD WITH ... syntax rebuilds all partitions of the index.

To evaluate how changing the compression state will affect a table, an index, or a partition, use the sp_estimate_data_compression_savings stored procedure.

Permissions

Requires ALTER permission on the table or view. User must be a member of the sysadmin fixed server role or the db_ddladmin and db_owner fixed database roles.

Examples

A. Creating a simple nonclustered index

The following example creates a nonclustered index on the BusinessEntityID column of the Purchasing.ProductVendor table.

Copy

USE AdventureWorks2008R2;
GO
IF EXISTS (SELECT name FROM sys.indexes
            WHERE name = N'IX_ProductVendor_VendorID')
    DROP INDEX IX_ProductVendor_VendorID ON Purchasing.ProductVendor;
GO
CREATE INDEX IX_ProductVendor_VendorID 
    ON Purchasing.ProductVendor (BusinessEntityID); 
GO

B. Creating a simple nonclustered composite index

The following example creates a nonclustered composite index on the SalesQuota and SalesYTD columns of the Sales.SalesPerson table.

Copy

USE AdventureWorks2008R2
GO
IF EXISTS (SELECT name FROM sys.indexes
            WHERE name = N'IX_SalesPerson_SalesQuota_SalesYTD')
    DROP INDEX IX_SalesPerson_SalesQuota_SalesYTD ON Sales.SalesPerson ;
GO
CREATE NONCLUSTERED INDEX IX_SalesPerson_SalesQuota_SalesYTD
    ON Sales.SalesPerson (SalesQuota, SalesYTD);
GO

C. Creating a unique nonclustered index

The following example creates a unique nonclustered index on the Name column of the Production.UnitMeasure table. The index will enforce uniqueness on the data inserted into the Name column.

Copy

USE AdventureWorks2008R2;
GO
IF EXISTS (SELECT name from sys.indexes
             WHERE name = N'AK_UnitMeasure_Name')
    DROP INDEX AK_UnitMeasure_Name ON Production.UnitMeasure;
GO
CREATE UNIQUE INDEX AK_UnitMeasure_Name 
    ON Production.UnitMeasure(Name);
GO

The following query tests the uniqueness constraint by attempting to insert a row with the same value as that in an existing row.

Copy

--Verify the existing value.
SELECT Name FROM Production.UnitMeasure WHERE Name = N'Ounces';
GO
INSERT INTO Production.UnitMeasure (UnitMeasureCode, Name, ModifiedDate)
    VALUES ('OC', 'Ounces', GetDate());

The resulting error message is:
Server: Msg 2601, Level 14, State 1, Line 1
Cannot insert duplicate key row in object 'UnitMeasure' with unique index 'AK_UnitMeasure_Name'. The statement has been terminated.

D. Using the IGNORE_DUP_KEY option

The following example demonstrates the effect of the IGNORE_DUP_KEY option by inserting multiple rows into a temporary table first with the option set to ON and again with the option set to OFF. A single row is inserted into the #Test table that will intentionally cause a duplicate value when the second multiple-row INSERT statement is executed. A count of rows in the table returns the number of rows inserted.

Copy

USE AdventureWorks2008R2;
GO
CREATE TABLE #Test (C1 nvarchar(10), C2 nvarchar(50), C3 datetime);
GO
CREATE UNIQUE INDEX AK_Index ON #Test (C2)
    WITH (IGNORE_DUP_KEY = ON);
GO
INSERT INTO #Test VALUES (N'OC', N'Ounces', GETDATE());
INSERT INTO #Test SELECT * FROM Production.UnitMeasure;
GO
SELECT COUNT(*)AS [Number of rows] FROM #Test;
GO
DROP TABLE #Test;
GO

Here are the results of the second INSERT statement.
Server: Msg 3604, Level 16, State 1, Line 5 Duplicate key was ignored.

Number of rows
--------------
38
Notice that the rows inserted from the Production.UnitMeasure table that did not violate the uniqueness constraint were successfully inserted. A warning was issued and the duplicate row ignored, but the entire transaction was not rolled back.
The same statements are executed again, but with IGNORE_DUP_KEY set to OFF.

Copy

USE AdventureWorks2008R2;
GO
CREATE TABLE #Test (C1 nvarchar(10), C2 nvarchar(50), C3 datetime);
GO
CREATE UNIQUE INDEX AK_Index ON #Test (C2)
    WITH (IGNORE_DUP_KEY = OFF);
GO
INSERT INTO #Test VALUES (N'OC', N'Ounces', GETDATE());
INSERT INTO #Test SELECT * FROM Production.UnitMeasure;
GO
SELECT COUNT(*)AS [Number of rows] FROM #Test;
GO
DROP TABLE #Test;
GO

Here are the results of the second INSERT statement.
Server: Msg 2601, Level 14, State 1, Line 5
Cannot insert duplicate key row in object '#Test' with unique index
'AK_Index'. The statement has been terminated.

Number of rows
--------------
1
Notice that none of the rows from the Production.UnitMeasure table were inserted into the table even though only one row in the table violated the UNIQUE index constraint.

E. Using DROP_EXISTING to drop and re-create an index

The following example drops and re-creates an existing index on the ProductID column of the Production.WorkOrder table by using the DROP_EXISTING option. The options FILLFACTOR and PAD_INDEX are also set.

Copy

USE AdventureWorks2008R2;
GO
CREATE NONCLUSTERED INDEX IX_WorkOrder_ProductID
    ON Production.WorkOrder(ProductID)
    WITH (FILLFACTOR = 80,
        PAD_INDEX = ON,
        DROP_EXISTING = ON);
GO

F. Creating an index on a view

The following example creates a view and an index on that view. Two queries are included that use the indexed view.

Copy

USE AdventureWorks2008R2;
GO
--Set the options to support indexed views.
SET NUMERIC_ROUNDABORT OFF;
SET ANSI_PADDING, ANSI_WARNINGS, CONCAT_NULL_YIELDS_NULL, ARITHABORT,
    QUOTED_IDENTIFIER, ANSI_NULLS ON;
GO
--Create view with schemabinding.
IF OBJECT_ID ('Sales.vOrders', 'view') IS NOT NULL
DROP VIEW Sales.vOrders ;
GO
CREATE VIEW Sales.vOrders
WITH SCHEMABINDING
AS
    SELECT SUM(UnitPrice*OrderQty*(1.00-UnitPriceDiscount)) AS Revenue,
        OrderDate, ProductID, COUNT_BIG(*) AS COUNT
    FROM Sales.SalesOrderDetail AS od, Sales.SalesOrderHeader AS o
    WHERE od.SalesOrderID = o.SalesOrderID
    GROUP BY OrderDate, ProductID;
GO
--Create an index on the view.
CREATE UNIQUE CLUSTERED INDEX IDX_V1 
    ON Sales.vOrders (OrderDate, ProductID);
GO
--This query can use the indexed view even though the view is 
--not specified in the FROM clause.
SELECT SUM(UnitPrice*OrderQty*(1.00-UnitPriceDiscount)) AS Rev, 
    OrderDate, ProductID
FROM Sales.SalesOrderDetail AS od
    JOIN Sales.SalesOrderHeader AS o ON od.SalesOrderID=o.SalesOrderID
        AND ProductID BETWEEN 700 and 800
        AND OrderDate >= CONVERT(datetime,'05/01/2002',101)
GROUP BY OrderDate, ProductID
ORDER BY Rev DESC;
GO
--This query can use the above indexed view.
SELECT  OrderDate, SUM(UnitPrice*OrderQty*(1.00-UnitPriceDiscount)) AS Rev
FROM Sales.SalesOrderDetail AS od
    JOIN Sales.SalesOrderHeader AS o ON od.SalesOrderID=o.SalesOrderID
        AND DATEPART(mm,OrderDate)= 3
        AND DATEPART(yy,OrderDate) = 2002
GROUP BY OrderDate
ORDER BY OrderDate ASC;
GO

G. Creating an index with included (non-key) columns

The following example creates a nonclustered index with one key column (PostalCode) and four non-key columns (AddressLine1, AddressLine2, City, StateProvinceID). A query that is covered by the index follows. To display the index that is selected by the query optimizer, on the Query menu in SQL Server Management Studio, select Display Actual Execution Plan before executing the query.

Copy

USE AdventureWorks2008R2;
GO
IF EXISTS (SELECT name FROM sys.indexes
            WHERE name = N'IX_Address_PostalCode')
    DROP INDEX IX_Address_PostalCode ON Person.Address;
GO
CREATE NONCLUSTERED INDEX IX_Address_PostalCode
    ON Person.Address (PostalCode)
    INCLUDE (AddressLine1, AddressLine2, City, StateProvinceID);
GO
SELECT AddressLine1, AddressLine2, City, StateProvinceID, PostalCode
FROM Person.Address
WHERE PostalCode BETWEEN N'98000' and N'99999';
GO

H. Creating a partitioned index

The following example creates a nonclustered partitioned index on TransactionsPS1, an existing partition scheme. This example assumes the partitioned index sample has been installed.

Copy

USE AdventureWorks2008R2;
GO
IF EXISTS (SELECT name FROM sys.indexes
    WHERE name = N'IX_TransactionHistory_ReferenceOrderID'
    AND object_id = OBJECT_ID(N'Production.TransactionHistory'))
DROP INDEX IX_TransactionHistory_ReferenceOrderID
    ON Production.TransactionHistory;
GO
CREATE NONCLUSTERED INDEX IX_TransactionHistory_ReferenceOrderID
    ON Production.TransactionHistory (ReferenceOrderID)
    ON TransactionsPS1 (TransactionDate);
GO

I. Creating a filtered index

The following example creates a filtered index on the Production.BillOfMaterials table. The filter predicate can include columns that are not key columns in the filtered index. The predicate in this example selects only the rows where EndDate is non-NULL.

Copy

USE AdventureWorks2008R2;
GO
IF EXISTS (SELECT name FROM sys.indexes
    WHERE name = N'FIBillOfMaterialsWithEndDate' 
    AND object_id = OBJECT_ID(N'Production.BillOfMaterials'))
DROP INDEX FIBillOfMaterialsWithEndDate
    ON Production.BillOfMaterials;
GO
CREATE NONCLUSTERED INDEX "FIBillOfMaterialsWithEndDate"
    ON Production.BillOfMaterials (ComponentID, StartDate)
    WHERE EndDate IS NOT NULL;
GO

J. Creating a compressed index

The following example creates an index on a nonpartitioned table by using row compression.

Copy

CREATE NONCLUSTERED INDEX IX_INDEX_1 
    ON T1 (C2)
WITH ( DATA_COMPRESSION = ROW ) ; 
GO

The following example creates an index on a partitioned table by using row compression on all partitions of the index.

Copy

CREATE CLUSTERED INDEX IX_PartTab2Col1
ON PartitionTable1 (Col1)
WITH ( DATA_COMPRESSION = ROW ) ;
GO

The following example creates an index on a partitioned table by using page compression on partition 1 of the index and row compression on partitions 2 through 4 of the index.

Copy

CREATE CLUSTERED INDEX IX_PartTab2Col1
ON PartitionTable1 (Col1)
WITH (DATA_COMPRESSION = PAGE ON PARTITIONS(1),
    DATA_COMPRESSION = ROW ON PARTITIONS (2 TO 4 ) ) ;
GO

Pages

Technalogy In formation

what is sql transaction....?

SQL-Transaction Statements

Transaction Overview

Transaction Isolation

SQL-Schema Statements in Transactions

COMMIT Statement

ROLLBACK Statement

SqlTransaction Class

.NET Framework

.NET Framework Client Profile

SQL triggers

Database trigger

Contents

[edit] The need and the usage

[edit] Triggers in Oracle

[edit] Schema-level triggers

[edit] Mutating tables

[edit] Triggers in Microsoft SQL Server

[edit] Triggers in PostgreSQL

[edit] Triggers in Firebird

[edit] Triggers in MySQL

[edit] Triggers in IBM DB2 LUW

[edit] Triggers in SQLite

[edit] Triggers in XML databases

An Introduction to Triggers -- Part I

When to Use Triggers

A Real-World Example

Part II

An Introduction to Triggers -- Part II

UPDATE Trigger

DELETE Trigger

INSTEAD OF Triggers

Need More Information on Views?

Introduction to SQL Triggers

Advantages of using SQL trigger

Disadvantages of using SQL trigger

SQL Server 2005 - Defining Indexes

SQL Server 2005 - Defining Indexes

To create an index

To create a unique index

To create a clustered index

To create full-text indexes

To change index properties

To rename an index

To delete an index

To specify a fill factor for an index

To create an XML index

To delete XML Indexes

CREATE INDEX (Transact-SQL)

Clustered Indexes

Unique Indexes

Partitioned Indexes

Indexed Views

Required SET Options for Indexed Views

Deterministic Functions

Additional Requirements

Filtered Indexes

Required SET Options for Filtered Indexes

Spatial Indexes

XML Indexes

Index Key Size

Computed Columns

Included Columns in Indexes

Specifying Index Options

DROP_EXISTING Clause

ONLINE Option

Row and Page Locks Options

Viewing Index Information

Data Compression

A. Creating a simple nonclustered index

B. Creating a simple nonclustered composite index

C. Creating a unique nonclustered index

D. Using the IGNORE_DUP_KEY option

E. Using DROP_EXISTING to drop and re-create an index

F. Creating an index on a view

G. Creating an index with included (non-key) columns

H. Creating a partitioned index

I. Creating a filtered index