sql indexing, optimization

When considering optimization, indexing is one of the options. The two types of indexing.
1. clustering index and 2. non- clustering index.

Clustering index is referred to like book shelf where the files in the shelf are refered to as pages, records in the files are referred to as the rows in the table, the shelf drawers on its own by alphabetical order is referred to as intermediate level.

Non clustered index is referred to as the index at the back of the book, where in a book when looked for a particular topic you can see references of certain pages based on that topic, in similar way non clustered index references the pages of similar key.

The next step will be to how effectivily shall we use the indexing. The main two functions of a indexing is to provide uniqueness and to return results much faster.

The next step is choosing between cluster and non-cluster index. We can have only one cluster index per table, where we can have 249 non - cluster index on a table. So in most cases we have the primary key set as the cluster index and work on the selectivity to determine the need of the number of non-cluster indexes.

Now how to determine and set up indexes.

For example if you are looking for a word "customer" in a book then a index will help you to find all the pages where the customer is available.

For example if you are looking to search all the words in a book then it is better to read the ent book which is table scan in our sql query, so in this case a index will be of no beneficial only burden.

There are a few ways to determine the need for a index, sql profiler is one of our best tools to start with, to check where there is more time spent.

Then based on the time, when the tables are identified this query can be used to determine the selectivity ratio.

selectivity

select count(distinct salesordernoitem) as '# unique',
count(*) as '# rows',
str(count(distinct salesordernoitem) / cast (count(*)as real),4,2) as
'selectivity' from transactionpricing

if the results are as below

#unique #rows selectivity
75000 75000 1.00

in this case an index on the sales order number in the transaction pricing table will be appropriate.