SQL Server - how to create index in table? - Asked By Prem Anandh on 10-Oct-08 07:38 AM

how to create index in table?

Index for table: - Binny ch replied to Prem Anandh on 10-Oct-08 07:42 AM

 An index can cover one or more columns. The general syntax for creating an index is:

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.

create index - alice johnson replied to Prem Anandh on 10-Oct-08 07:46 AM

CREATE UNIQUE INDEX subscription_ID ON subscription ( taxNumber, newspaperName )

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

create index - Shobha Kumawat replied to Prem Anandh on 10-Oct-08 07:46 AM

CREATE  INDEX <index_name> ON <table_name> (indexing_param1, indexing_param2, indexing_param3....)
GO
index - Amrita Gaur replied to Prem Anandh on 10-Oct-08 07:51 AM
CREATE [ UNIQUE ] [ CLUSTERED | NONCLUSTERED ] INDEX index_name
    ON <object> ( column [ ASC | DESC ] [ ,...n ] )
    [ INCLUDE ( column_name [ ,...n ] ) ]
    [ WITH ( <relational_index_option> [ ,...n ] ) ]
    [ ON { partition_scheme_name ( column_name )
         | filegroup_name
         | default
         }
    ]
[ ; ]
reply - Perry replied to Prem Anandh on 10-Oct-08 07:58 AM

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

re - Web Star replied to Prem Anandh on 10-Oct-08 08:02 AM

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 in table - C_A P replied to Prem Anandh on 10-Oct-08 11:44 AM
it is often desirable to create indexes on tables. An index can cover one or more columns. The general syntax for creating an index is:

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.

sql create index sintex - C_A P replied to Prem Anandh on 10-Oct-08 11:45 AM

SQL syntax

Create Index
The options ASC (ascending, from low to high) and DESC (descending, from high to low) indicate the sorting of the index. Most of the time you can leave this option out.

Example

CREATE UNIQUE INDEX subscription_ID ON subscription ( taxNumber, newspaperName )

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.

another syntax for index in sql - C_A P replied to Prem Anandh on 10-Oct-08 11:46 AM

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#synscrtidx
Read syntax diagramhttp://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-----'

syntax for create index - C_A P replied to Prem Anandh on 10-Oct-08 11:48 AM

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}