2 cluster or not 2 cluster, that is the quetion 1

[Rousseau10]

OK so I import 2 tables, 1 with 500,000 rows and one with 300,000 rows using dts and creating the tables for them in sql server.

Now I need to query these 2 tables and join them in the query and use a where clause to filter records I want.

But first I think I should makes some indexes which is where I need help.

I need to query the enrollee_number field which is the common link between the 2 tables. I need to search though on the group field located in table1.

I read for equality searches it is best to use a non-clustered index so I made this index on the group field since I will search knowing the exact string and nott using a range.

I will then since both tables are linked by the enrollee_number feild make these filed the one and only clustered index in their respective tables. I read clustered is good for sortes searching whatever that means. I am knew to creating indexs and tuning.

Does this seem like the best plan to fit my query or cover it as they sometimes say?

could someone please tell me the difference in a key index and just an index?


One more question...does sql use my indexes are they just hint and sql ultimately will use the best execution plan it thinks is best?????????


TANK U !!!!!!!!!!!!

 

[mkal]

Some basics: Clustered indexes physically sort the data on the data pages in ascending/descending order (asending is the default). You can have only one clustered index per table and they are usually good for finding a range of information i.e. dates or lastnames. After creating a clustered index you can then created any number of non-clustred indexes (up to 1048) but keep them to a minimum - usually 3-4 should suffice, non-clustred indexes are not stored in the data pages but in there own datapage and are good for searching for a single/few value(s). Then there are unique indexes, these are good for Key values that can be used to uniquely defined each row in the table, normally this is use for the Primary Key (PK) but can be used on other columns as well where uniqueness needs to be inforced i.e. Social Security Numbers. It's a good idea that every table should have a PK.

There is also compound indexes which are comprised of more than one column. The general rule of thumb here is to make sure the most unique values come first in the indexes definition and then the most unique after that and so on, again keep the number to a minimum. So if you were seaching for city and state names a lot creating a non-clustered index comprised of city & state would increase the performance of your query.

By default when you create a primary key you get a unique clustered index, but this isn't always the most desired situation. So spend sometime thinking about how you plan to retrieve the information before defining your indexes.

Hope this helps

 

[Rousseau10]

thanks so MUUC for taking the tinme, that really helped!!!!!!!!!!!! I reread this several times to let it sink in!

I am being trained by experts to be an experts trainer Newbee - Adam