SQL Server - how to create index in table?

This creates an index with the name "subscription_ID" for the table subscription. The combination of taxNumber and newspaperName will be unique, can not occur double

Go through this link:

http://216.71.124.162/cursus/dbdesign/english/imple040.php3

Hi,

Creating index is very simple its just a matter of one small query but to decide the colunm names on which you want to create index is the critical job. You need to find out the colunm names where users executes the query frequently like "whete col=blah" and on that colunm you should create index to improve the performance.

There are two types of indexes: Clustered and non-clustered. A clustered index is a special type of index that reorders the way records in the table are physically stored. Therefore table can have only one clustered index. The leaf nodes of a clustered index contain the data pages.

A nonclustered index is a special type of index in which the logical order of the index does not match the physical stored order of the rows on disk. The leaf node of a nonclustered index does not consist of the data pages. Instead, the leaf nodes contain index rows

You can create either of one based on your requirements to improve the performace.

After finalizing all your requirement useeither of below query to create index on colunms that you have finelized.

CREATE INDEX INDEX_NAME OF TABLENAME (COLUNMS);

refer http://msdn.microsoft.com/en-us/library/ms190197.aspx and http://msdn.microsoft.com/en-us/library/aa258260(SQL.80).aspx  for creating clustered, non-cluseter indexes.

Regards,

Megha

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

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 ,…)

<comparison> ::=
        column_name <comparison_op> constant

<comparison_op> ::=
    { IS | IS NOT | = | <> | != | > | >= | !> | < | <= | !< }

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


Backward Compatible Relational Index
Important   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 
}

http://msdn.microsoft.com/en-us/library/ms188783.aspx

CREATE INDEX "INDEX_NAME" ON "TABLE_NAME" (COLUMN_NAME)

Let's assume that we have the following table,

TABLE Customer
(First_Name char(50),
Last_Name char(50),
Address char(50),
City char(50),
Country char(25),
Birth_Date date)

and we want to create an index on the column Last_Name, we would type in,

CREATE INDEX IDX_CUSTOMER_LAST_NAME
on CUSTOMER (Last_Name)

If we want to create an index on both City and Country, we would type in,

CREATE INDEX IDX_CUSTOMER_LOCATION
on CUSTOMER (City, Country)

There is no strict rule on how to name an index. The generally accepted method is to place a prefix, such as "IDX_", before an index name to avoid confusion with other database objects. It is also a good idea to provide information on which table and column(s) the index is used on.

Please note that the exact syntax for CREATE INDEX may be different for different databases. You should consult with your database reference manual for the precise syntax.

http://publib.boulder.ibm.com/infocenter/iseries/v5r3/topic/db2/rbafzmst02.htm#ToC_825

http://publib.boulder.ibm.com/infocenter/iseries/v5r3/topic/db2/rbafzmstxcindx.htm#skipsyn-268>>-CREATE--+----------------------------+--INDEX--index-name---->
           +-UNIQUE--+----------------+-+
           |         '-WHERE NOT NULL-' |
           '-ENCODED VECTOR-------------'
 
                      .-,---------------------.
                      V              .-ASC--. |
>--ON--table-name--(----column-name--+------+-+--)-------------->
                                     '-DESC-'
 
>--+-------------------------------------+---------------------->
   |                .-DISTINCT-.         |
   '-WITH--integer--+----------+--VALUES-'
 
>--+-----------------+-----------------------------------------><
   +-NOT PARTITIONED-+
   '-PARTITIONED-----'
 

CREATE INDEX Syntax

CREATE [UNIQUE|FULLTEXT|SPATIAL] INDEX index_name
    [index_type]
    ON tbl_name (index_col_name,...)
    [index_type]

index_col_name:
    col_name [(length)] [ASC | DESC]

index_type:
    USING {BTREE | HASH | RTREE}