question

The first result from a Bing.com search sums things up quite nicely. Go check out [ http://mssqlcity.com/FAQ/General/clustered_vs_nonclustered_indexes.htm][1] Your clustering key should, wherever possible, be an ever incrementing key (frequently but not always this will be your primary key). Non-clustered indexes should be added to provide performance improvements for query support. [1]: http://mssqlcity.com/FAQ/General/clustered_vs_nonclustered_indexes.htm

I often use a paper Phone Book as an example. The phone book is mostly sorted by lastname, Andersson comes before Axelsson etc. Firstname is also a key, if two persons have the same lastname, they sort on firstname. Adam Smith comes before Axel Smith. The actual data is sorted by (lastname,firstname). In SQL Server, a tables datapages are sorted by the clustered index. (Lastname,firstname) is the clustered index of a phonebook. After the actual data in a phonebook, you often have a streetname-registry pointing out the names of the people living on the street. That's a non-clustered index. In SQL Server, instead of being stored along with the datapages, the non-clustered index is stored in its own datapages, with pointers to the clustered index of the datapages where the actual data is stored. When choosing a candidate column (or columns) for a clustered index, there are a few things to consider. One is fragmentation. To avoid fragmentation, you can use an identity-column as clustered index. But on the other hand, you really have no use of an identity-column - it's redundant data stored in the table and it won't help you search. I usually look at how much the data will change when choosing between an identity-column and a natural column as primary key (and clustered index). If I take a Country-table for example, it's pretty static. The UN might add or remove a country once a year or so. In that case, I'd choose the two-digit ISO-name for the table (UK for United Kingdom, SE for Sweden). That way, when I add a City-table to my database, and want to search for all cities in Sweden, I don't need to use a join to find "Sweden" as a name. Instead I can just query the City-table WHERE CountryID='SE'. If I've chosen (CountryID, CityName) as primary key (and clustered index) for the City-table, the search will be really fast - just a clustered index-seek, instead of a key lookup. On the other hand again, the clustered index is stored along with the non-clustered index, so you'll get bigger non-clustered indexes if you use a wide clustered index. I could go on and on about clustered versus non-clustered indexes, natural versus surrogate keys etc. It's a big topic... The headline of you rquestion is "Whats difference between clustered index and nonclustered". I think I've answered that with the phone-book example. But in your question-text you ask "..tell me what i should set in clustered index and what i should set in nonclustered index with simple and small example please". And the answer to that is, like so often, "it depends". It depends on so many things that it's not really possible to give a general advice. There's also many "religious" thoughts on this topic. Some people say "Never use natural keys" and some people say "Never use surrogate keys". I try to consider how static the content of a table is, if the database is to be online and in heavy use 24/7 (and therefore not very suitable for running index maintenance jobs often), if there's a good natural key in the data and how much fragmentation that key would cause.

Clustered index is created on Primary Key. Since a table can have a single primary key, a clustered index can be present only once for a table. Where as there can be mutiple Non-Clustered indexes for a table.

** http://www.sqlservercentral.com/articles/Bookmark+Lookup/95193/**