Solved

SQL SERver

Posted on 2012-04-11
4
306 Views
Last Modified: 2012-04-11
Hi Experts,

I have an already existing table in SQL server 2005.

Table name: T_AutoPolicyTransaction

ColumnNames:

[TransactionID]
[PolicyNumber] - varchar(20), null
[PolicyInputXml]
[PolicyOutputXml]
[BatchRateStatus]
[TransactionDate]
[PolicyHeaderID] - int, not null

So far, I don't have any index or primary key in this table.

To increase the performance, I need to add index to

[PolicyNumber] and [PolicyHeaderID] columns.

In both columns, I can store duplicate values.

So, I am planning to write the below query to create the index,


CREATE INDEX PolicyNumberIndex
ON T_AutoPolicyTransaction (PolicyNumber)

CREATE INDEX PolicyHeaderIDIndex
ON T_AutoPolicyTransaction (PolicyHeaderID)

Is it a good way to create index or is there any best option?

Please help.
0
Comment
Question by:RameshLathu
  • 3
4 Comments
 

Author Comment

by:RameshLathu
ID: 37833308
Hi experts,

Sorry, my question is wrong, I want to create index on PolicyNumber and PolicyHeadID (Combine two columns and create one index). Please suggest
0
 

Author Comment

by:RameshLathu
ID: 37833338
I saying something like,

CREATE INDEX PolicyNumberandHeaderIndex
ON T_AutoPolicyTransaction (PolicyNumber, PolicyHeaderID).

Is this correct?
0
 
LVL 12

Accepted Solution

by:
Harish Varghese earned 500 total points
ID: 37834210
Hello Ramesh,

There are a number of points to consider while creating an index:

1. Determine the columns - You should choose indexes based on your need, i.e. what column(s) are searched in your frequently used queries.

2. Order of columns - Now that you have identified that you need to have a composite index on these two columns (PolicyNumber, PolicyHeaderID), you need to determine the order of the columns in the index - (PolicyNumber, PolicyHeaderID) Vs. (PolicyHeaderID, PolicyNumber). If your queries will search for PolicyNumber alone in some cases and PolicyNumber and PolicyHeader together in other cases, then (PolicyNumber, PolicyHeaderID) order should be ok. If your queries are going to search on these two columns together always, then you should use the column with larger number of varying data (more distinct values) as the first column.

3. Clustered Vs. Non-Clustered - If there are frequent writes / bulk inserts into your table, then Clustered index is not advisable. Google on "Clustered vs non clustered indexes" or read http://itknowledgeexchange.techtarget.com/sql-server/back-to-basics-clustered-vs-nonclustered-indexes-whats-the-difference/ to understand about indexes. If you are not sure, go with non-clustered. (If you dont mention explicitly in your CREATE INDEX statement, the index will be non-clustered by default).

4. Add a primary key to your table. Is the "TransactionID" column unique? In that case you can create primary key on that column. If it is not unique, atleast create an Identity column and use as Primary Key.

5. If you are creating non-clustered index, include your primary key in the index. You can also add other most frequently used (in SELECT or in WHERE clause) columns in INCLUDE list.
Eg. If TransactionId is your primary key, then:
    CREATE INDEX PolicyNumberandHeaderIndex
    ON T_AutoPolicyTransaction (PolicyNumber, PolicyHeaderID)
    INCLUDE (TransactionID)

If you use another column, say PrimaryKeyColumn as Primary Key, then:
   CREATE INDEX PolicyNumberandHeaderIndex
   ON T_AutoPolicyTransaction (PolicyNumber, PolicyHeaderID)
   INCLUDE (PrimaryKeyColumn)

If you choose not to have any primary key, then:
   CREATE INDEX PolicyNumberandHeaderIndex
   ON T_AutoPolicyTransaction (PolicyNumber, PolicyHeaderID)

If you decide to use clustered index, then:
   CREATE CLUSTERED INDEX PolicyNumberandHeaderIndex
   ON T_AutoPolicyTransaction (PolicyNumber, PolicyHeaderID)
(No INCLUDE statements with clustered index)

These are few basic points to consider while creating index. There are many more things to consider if you want to tune it perfect. Google and read more about indexes.

Thanks,
Harish
0
 

Author Closing Comment

by:RameshLathu
ID: 37835894
Thank you.....Very helpful.

I created the index, but it is giving me File group full error.

I opend a separate question for that. Please check my question (SQL Server - text Vs nvarchar). and suggest me.

Thanks
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Viewers will learn how the fundamental information of how to create a table.

911 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

23 Experts available now in Live!

Get 1:1 Help Now