|
|
You're in the
sql server programming
group:Creating Clustred/Index Keys
sql server programming:
I have a small question. :) I just wanted to know what is the best method out of these two methods when creating indexes on a very heavy transactional database 1. Create a wider CLUSTRED INDEX , for an example, in the Customers table, Put the CLUSTRED KEY AS CustomerID,PostralCode, CompanyCode or 2.Create the CLUSRED INDEX on CustomerID and create indexes for dah all other columns. ? which one is the best ?? thanks for your advice and time -Aruna
Hi Uri,
Thanks for your reply.. do you think it's better to create one non-clustred (CustomerID,CountryCode,PostralCode) togather or shoud i create a non-clustred key for each column? thanks again, -Aruna [quoted text, click to view] "Uri Dimant" wrote:
> Hi > I have always ( yep it depends:-)))) tried to keep a clustred index as small > as possible > > Good start > > http://www.sql-server-performance.com/clustered_indexes.asp > http://www.sql-server-performance.com/nonclustered_indexes.asp > > > > "Sniper" <kakopappa@hotmail.com> wrote in message > news:A68BC694-8B80-4D26-8593-11BDCA7F4550@microsoft.com... > > Hi guys > > > > I have a small question. :) > > > > I just wanted to know what is the best method out of these two methods > > when > > creating indexes on a very heavy transactional database > > > > 1. Create a wider CLUSTRED INDEX , for an example, > > in the Customers table, Put the CLUSTRED KEY AS > > CustomerID,PostralCode, CompanyCode > > > > or > > 2.Create the CLUSRED INDEX on CustomerID and create indexes for dah all > > other columns. > > > > ? which one is the best ?? > > > > > > thanks for your advice and time > > > > -Aruna > > > > > >
Hi Tibor ,
Thanks for your reply. I just wanted to know what what are the difference performance implications between creating a single non-clsutred index for each colum say like you create one key for PostralCode , one key for Country Code... and multipal colums non-clsutred indexes. like you include all the keys in a one none-clsured index thanks -Aruna [quoted text, click to view] "Tibor Karaszi" wrote:
> You create indexes to assist your queries. without knowing that information, we really can't help > you. If you typically, for instance, search on Companycode, then you should have an index on that > column (or at least that column as the first column in an index). Start with your queries. > > -- > Tibor Karaszi, SQL Server MVP > http://www.karaszi.com/sqlserver/default.asp > http://www.solidqualitylearning.com/ > Blog: http://solidqualitylearning.com/blogs/tibor/ > > > "Sniper" <kakopappa@hotmail.com> wrote in message > news:A68BC694-8B80-4D26-8593-11BDCA7F4550@microsoft.com... > > Hi guys > > > > I have a small question. :) > > > > I just wanted to know what is the best method out of these two methods when > > creating indexes on a very heavy transactional database > > > > 1. Create a wider CLUSTRED INDEX , for an example, > > in the Customers table, Put the CLUSTRED KEY AS > > CustomerID,PostralCode, CompanyCode > > > > or > > 2.Create the CLUSRED INDEX on CustomerID and create indexes for dah all > > other columns. > > > > ? which one is the best ?? > > > > > > thanks for your advice and time > > > > -Aruna > > > > >
You can use covering index if you are using more than one columns in
your query , Create the column with the highest cardinality is the first column. Column combination for index depends on your query. Please read the article suggested by Uri. Regards Amish
Hi
I have always ( yep it depends:-)))) tried to keep a clustred index as small as possible Good start http://www.sql-server-performance.com/clustered_indexes.asp http://www.sql-server-performance.com/nonclustered_indexes.asp [quoted text, click to view] "Sniper" <kakopappa@hotmail.com> wrote in message news:A68BC694-8B80-4D26-8593-11BDCA7F4550@microsoft.com... > Hi guys > > I have a small question. :) > > I just wanted to know what is the best method out of these two methods > when > creating indexes on a very heavy transactional database > > 1. Create a wider CLUSTRED INDEX , for an example, > in the Customers table, Put the CLUSTRED KEY AS > CustomerID,PostralCode, CompanyCode > > or > 2.Create the CLUSRED INDEX on CustomerID and create indexes for dah all > other columns. > > ? which one is the best ?? > > > thanks for your advice and time > > -Aruna > >
The two options you listed below are not functionally equivalent. If you
create a compund index (clustered or non-clustered) and the user queries on a column other than the leading column (in this case PostalCode) then a non-indexed scan will probably result. However, if PostalCode is indexed seperately, then that index can be used. When designing indexes, always have a specific goal in mind. For example, improving queries against a specific column or a primary key constraint. Clustered Index Design Guidelines http://msdn2.microsoft.com/en-us/library/ms190639.aspx Planning and Creating Indexes http://www.microsoft.com/technet/prodtechnol/sql/70/books/c0618260.mspx [quoted text, click to view] "Sniper" <kakopappa@hotmail.com> wrote in message news:0689C8D3-570B-4CD1-8782-CEC3970E5C82@microsoft.com... > Hi Uri, > > Thanks for your reply.. > > do you think it's better to create one non-clustred > (CustomerID,CountryCode,PostralCode) togather or shoud i create a > non-clustred key for each column? > > thanks again, > -Aruna > > > "Uri Dimant" wrote: > >> Hi >> I have always ( yep it depends:-)))) tried to keep a clustred index as >> small >> as possible >> >> Good start >> >> http://www.sql-server-performance.com/clustered_indexes.asp >> http://www.sql-server-performance.com/nonclustered_indexes.asp >> >> >> >> "Sniper" <kakopappa@hotmail.com> wrote in message >> news:A68BC694-8B80-4D26-8593-11BDCA7F4550@microsoft.com... >> > Hi guys >> > >> > I have a small question. :) >> > >> > I just wanted to know what is the best method out of these two methods >> > when >> > creating indexes on a very heavy transactional database >> > >> > 1. Create a wider CLUSTRED INDEX , for an example, >> > in the Customers table, Put the CLUSTRED KEY AS >> > CustomerID,PostralCode, CompanyCode >> > >> > or >> > 2.Create the CLUSRED INDEX on CustomerID and create indexes for dah all >> > other columns. >> > >> > ? which one is the best ?? >> > >> > >> > thanks for your advice and time >> > >> > -Aruna >> > >> > >> >> >>
Clustered indexes are more benefitial for single lookups, ordered results,
and group by queries. However, it requires more I/O than non-clustered indexes when inserting and updating, especially on large tables. I tend to avoid the use of clustered indexes in a transactional (OLTP) database and use them mostly in data warehouse or reporting databases. [quoted text, click to view] "Sniper" <kakopappa@hotmail.com> wrote in message news:A68BC694-8B80-4D26-8593-11BDCA7F4550@microsoft.com... > Hi guys > > I have a small question. :) > > I just wanted to know what is the best method out of these two methods > when > creating indexes on a very heavy transactional database > > 1. Create a wider CLUSTRED INDEX , for an example, > in the Customers table, Put the CLUSTRED KEY AS > CustomerID,PostralCode, CompanyCode > > or > 2.Create the CLUSRED INDEX on CustomerID and create indexes for dah all > other columns. > > ? which one is the best ?? > > > thanks for your advice and time > > -Aruna > >
You create indexes to assist your queries. without knowing that information, we really can't help
you. If you typically, for instance, search on Companycode, then you should have an index on that column (or at least that column as the first column in an index). Start with your queries. -- Tibor Karaszi, SQL Server MVP http://www.karaszi.com/sqlserver/default.asp http://www.solidqualitylearning.com/ Blog: http://solidqualitylearning.com/blogs/tibor/ [quoted text, click to view] "Sniper" <kakopappa@hotmail.com> wrote in message
news:A68BC694-8B80-4D26-8593-11BDCA7F4550@microsoft.com... > Hi guys > > I have a small question. :) > > I just wanted to know what is the best method out of these two methods when > creating indexes on a very heavy transactional database > > 1. Create a wider CLUSTRED INDEX , for an example, > in the Customers table, Put the CLUSTRED KEY AS > CustomerID,PostralCode, CompanyCode > > or > 2.Create the CLUSRED INDEX on CustomerID and create indexes for dah all > other columns. > > ? which one is the best ?? > > > thanks for your advice and time > > -Aruna > >
Hi
Again it depends on many things like what you are going to put in WHERE condition, only you do know your table's structure Also keep in mind that SQL Server keeps statistics on left (first) column in the composite index It does not matter that the optimizer will not be able using indexes ,just try to keep the first colum more selective and think what is the best column to choose as a first one [quoted text, click to view] "Sniper" <kakopappa@hotmail.com> wrote in message news:0689C8D3-570B-4CD1-8782-CEC3970E5C82@microsoft.com... > Hi Uri, > > Thanks for your reply.. > > do you think it's better to create one non-clustred > (CustomerID,CountryCode,PostralCode) togather or shoud i create a > non-clustred key for each column? > > thanks again, > -Aruna > > > "Uri Dimant" wrote: > >> Hi >> I have always ( yep it depends:-)))) tried to keep a clustred index as >> small >> as possible >> >> Good start >> >> http://www.sql-server-performance.com/clustered_indexes.asp >> http://www.sql-server-performance.com/nonclustered_indexes.asp >> >> >> >> "Sniper" <kakopappa@hotmail.com> wrote in message >> news:A68BC694-8B80-4D26-8593-11BDCA7F4550@microsoft.com... >> > Hi guys >> > >> > I have a small question. :) >> > >> > I just wanted to know what is the best method out of these two methods >> > when >> > creating indexes on a very heavy transactional database >> > >> > 1. Create a wider CLUSTRED INDEX , for an example, >> > in the Customers table, Put the CLUSTRED KEY AS >> > CustomerID,PostralCode, CompanyCode >> > >> > or >> > 2.Create the CLUSRED INDEX on CustomerID and create indexes for dah all >> > other columns. >> > >> > ? which one is the best ?? >> > >> > >> > thanks for your advice and time >> > >> > -Aruna >> > >> > >> >> >>
hi all
thank you for the information .. just one more thing extra .. +-------+--------+--------- COL1 COL2 COL3 +-------+--------+--------- TC AAA ITEM1 TC AAA ITEM2 TC AAB ITEM3 in this is Table if i have created a clustred key on COL1, COL2 to match with this query SELECT COL1, COL2, COL3 FROM Table WHERE COL1 = 'TC' AND COL2 = 'AAB' AND COL3 = 'ITEM3' should I create a non-clustred index on colum COL3 ? or should I create a non-clustred key for COL1, COL2, COL3 ?? thanks in advice again -Aruna [quoted text, click to view] "Tibor Karaszi" wrote:
> You create indexes to assist your queries. without knowing that information, we really can't help > you. If you typically, for instance, search on Companycode, then you should have an index on that > column (or at least that column as the first column in an index). Start with your queries. > > -- > Tibor Karaszi, SQL Server MVP > http://www.karaszi.com/sqlserver/default.asp > http://www.solidqualitylearning.com/ > Blog: http://solidqualitylearning.com/blogs/tibor/ > > > "Sniper" <kakopappa@hotmail.com> wrote in message > news:A68BC694-8B80-4D26-8593-11BDCA7F4550@microsoft.com... > > Hi guys > > > > I have a small question. :) > > > > I just wanted to know what is the best method out of these two methods when > > creating indexes on a very heavy transactional database > > > > 1. Create a wider CLUSTRED INDEX , for an example, > > in the Customers table, Put the CLUSTRED KEY AS > > CustomerID,PostralCode, CompanyCode > > > > or > > 2.Create the CLUSRED INDEX on CustomerID and create indexes for dah all > > other columns. > > > > ? which one is the best ?? > > > > > > thanks for your advice and time > > > > -Aruna > > > > >
Try to visualize the indexes. As soon as you get int the habit of doing that, most of these
questions will be simple to answer. In your example, you have a cl ix on col1, col2. Cl means that the leaf pages is the data. The query does ANDed equal comparison on col1, col2 and col3. It also returns only these three columns. Since you are using AND, SQL Server can use the index on (col1, col2) to find the rows that satisfies that condition. For each row found (in the leaf level of the cl is, which also is the data pages), SQL server will look at each row to see if it also satisfied the condition on the col3 column. So, the gain in adding the col3 column is really the difference in how many rows does the query return if you only have the condition on col1 and col2 vs. if you have the condition on col1, col2 and col3. The number of rows is the difference. But since we count pages, you need to consider how mane rows you fit on each page. After calculating this (formulas is in Books Online), you can take number of rows per page divided by the difference of rows returned if you have condition on two or all three columns. As you can see, it is possible to calculate these things, but it is easier to just test the different strategies and check the difference (looking at the execution plan, number of I/O etc). You can be even fancier, by creating anon-clustered index on (col1, col2, col3). Now that index would cover the query. So SQL Server navigate the index and when in the leaf level, no need to jump down to a data page. How much gain compared to a clustered index (as per above)? It depends on the row size in the non-clustered index (col1, col2, col3) vs. the row size in a data page. You can calculate for these two alternatives number of pages that fit a row (in the nc index, vs. a data page) and then calculate the difference. -- Tibor Karaszi, SQL Server MVP http://www.karaszi.com/sqlserver/default.asp http://www.solidqualitylearning.com/ Blog: http://solidqualitylearning.com/blogs/tibor/ [quoted text, click to view] "Sniper" <kakopappa@hotmail.com> wrote in message
news:A92A20B1-FAFC-460B-8890-943CBFD963D0@microsoft.com... > hi all > > thank you for the information .. just one more thing extra .. > > > +-------+--------+--------- > COL1 COL2 COL3 > +-------+--------+--------- > TC AAA ITEM1 > TC AAA ITEM2 > TC AAB ITEM3 > > in this is Table if i have created a clustred key on COL1, COL2 > > to match with this query > > SELECT COL1, COL2, COL3 FROM Table > WHERE COL1 = 'TC' AND COL2 = 'AAB' AND COL3 = 'ITEM3' > > should I create a non-clustred index on colum COL3 ? > > or should I create a non-clustred key for COL1, COL2, COL3 ?? > > > thanks in advice again > -Aruna > > > > > > > "Tibor Karaszi" wrote: > >> You create indexes to assist your queries. without knowing that information, we really can't help >> you. If you typically, for instance, search on Companycode, then you should have an index on that >> column (or at least that column as the first column in an index). Start with your queries. >> >> -- >> Tibor Karaszi, SQL Server MVP >> http://www.karaszi.com/sqlserver/default.asp >> http://www.solidqualitylearning.com/ >> Blog: http://solidqualitylearning.com/blogs/tibor/ >> >> >> "Sniper" <kakopappa@hotmail.com> wrote in message >> news:A68BC694-8B80-4D26-8593-11BDCA7F4550@microsoft.com... >> > Hi guys >> > >> > I have a small question. :) >> > >> > I just wanted to know what is the best method out of these two methods when >> > creating indexes on a very heavy transactional database >> > >> > 1. Create a wider CLUSTRED INDEX , for an example, >> > in the Customers table, Put the CLUSTRED KEY AS >> > CustomerID,PostralCode, CompanyCode >> > >> > or >> > 2.Create the CLUSRED INDEX on CustomerID and create indexes for dah all >> > other columns. >> > >> > ? which one is the best ?? >> > >> > >> > thanks for your advice and time >> > >> > -Aruna >> > >> > >> >>
Don't see what you're looking for? Try a search.
|
January 2003
March 2003
April 2003
May 2003
June 2003
July 2003
August 2003
September 2003
October 2003
November 2003
December 2003
January 2004
February 2004
March 2004
April 2004
May 2004
June 2004
July 2004
August 2004
September 2004
October 2004
November 2004
December 2004
January 2005
February 2005
March 2005
April 2005
May 2005
June 2005
July 2005
August 2005
September 2005
October 2005
November 2005
December 2005
January 2006
February 2006
March 2006
April 2006
May 2006
June 2006
July 2006
August 2006
September 2006
October 2006
November 2006
December 2006
January 2007
February 2007
March 2007
April 2007
May 2007
June 2007
July 2007
August 2007
September 2007
October 2007
November 2007
April 2008
August 2008
| home · blog · groups | Questions? Comments? Contact the dn |