Is a clustered index created on primary key on default as soon as we create a table? can we create a clustered index on some other attribute except this primary key ? if yes, what happens in that case does the default ordering of table on primary key gets destroyed and gets physically ordered on tha

in SQL server, a primary key is not created unless explicitly specified. Same goes for a clustered index. A clustered index doesn't have to be a primary key and vice versa. There is no default ordering of a table. In fact data in a table is by definition not ordered. Regardless of any primary keys or indexes. Database engine will try to keep similar values of a clustered index next to each other, but there is no guarantee as to what order they will be retrieved in. if you want to change the columns in the clustered index you would first have to drop it. This will put the table back to a heap state. you can then create a new clustered index which will re-organize data into a different B-tree.

With SQL Server, by default, if you do nothing else, when you declare a primary key, that is automatically built as a clustered index. But it does not have to be. You can declare it to be nonclustered. Then you can add a clustered index to some other column(s) on the table. One point worth noting, since the clustered index acts as the definition of table storage, each row must be uniquely identified. If the keys you've defined for your clustered index are not unique, then SQL Server will add an additional value, called a uniquifier, to make sure that each row can be uniquely identified. When this clustered index gets created, it does reorder the data in the order of the key values that define the index. In general, this should be considered a good thing. This is because the clustered index, which holds the data, usually should be placed on the most common access path to the data. Usually that access path is the primary key, but it doesn't have to be.

By default SQL Server creates a Clustered Index (CI) on a Primary key. But yes, you can also create a PK with a Non-Clustered Index (NCI) by specifying it, but this index will be a non-covering index. And thus you can create  a CI on non-Key column. The physical ordering of the Table/Index is with Clustered Index key only, not on the PK, but that's also not guaranteed. Check my blog posts on:  - http://sqlwithmanoj.com/2011/03/02/clustered-vs-nonclustered-indexes-and-data-sorting/ - http://sqlwithmanoj.com/2013/06/02/clustered-index-do-not-guarantee-physically-ordering-or-sorting-of-rows/ - http://sqlwithmanoj.com/2013/06/10/clustered-index-will-not-always-guarantee-sorted-rows/ ~http://SQLwithManoj.com