Information générale
Forum:
Microsoft SQL Server
>I am testing various scenarios of making SQL Select last 100 (top 100 order desc) records executing faster. The current PK constraint (on ORDER_NO) is non-clustered. I change it (in SSMS) from non-clustered to clustered. It took a little time (I understand that all non-clustered indexes have to be rebuilt). Now SQL Select works much faster and execution plan shows that the PK clustered index is used about 99%.
>All seems to be good.
>
>Two question:
>1. Why by default when I was creating the PK (a while ago) SQL Server created non-clustered?
Each index non-clustered uses the clustered index as an index lookup to find the row fields,
whether as a cluster index using a multi-field key,
all clustered index fields are included in each element non-clustered index.
This can be inefficient, then SQL Server doesn't do a choice for you.
>2. What are the downsides of making this PK clustered? It is an order number assigned and is never changed.
If the primary key is an integer, there are no downsides..
The real point is difficult to understand how SQL Server locks rows during operations on the data,
an operation closely linked to the indices of table.
>
>TIA
Précédent
Suivant
Répondre
Voir le fil de ce thread
Voir le fil de ce thread à partir de ce message seulement
Voir tous les messages de ce thread
Voir tous les messages de ce thread à partir de ce message seulement