Databases
StackOverflow2013
Postsdbo
POComposite clustered PK behavior vs Nonclustered PK + nonunique clustered index
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POComposite clustered PK behavior vs Nonclustered PK + nonunique clustered index
    primarykey
    data
    text
    <p>I have a table with a few columns, the 2 important ones are appid and fileid. Together, they make up a PK for the table. A typical use case for the table will be how many files contain appid x, or which appid is the most popular. Those queries will be run very often on only subsets of files as well, instead of all files. Neither column is unique individually.</p> <p>Based on that, I feel like the best choice for a clustered index would be AppId. However, since setting both columns as a PK will make an extra nonclustered index, and appids lack of uniqueness (there will be lots of repeats) means it will need a uniquifier column behind the scenes anyway, would it make more sense to just say the PK is clustered and not specify another clustered index? Assuming I specified AppId first in the PK, would it treat diagnosticfileid like a uniquifier behind the scenes and give me the optimal performance that way?</p> <p>EDIT: An important thing i forgot to originally mention is that APPId's won't be steadily increasing or anything, so there will be insertions to the middle of the table. I was thinking I could prevent some problems with this by using a fillfactor, but the table will get pretty big, so I dont know how much that will help. </p> <p>Also, it is going to be inserted into pretty often, but never large chunks at a time. Probably something like a few thousand rows an hour. There isn't really any value that will reliably increase and be a good choice for a clustered index in that respect, but I wasn't sure how big of a deal that is. I could add an id just to have a good value to cluster around, but I feel like that'd slow down selects a lot.</p>
    singulars
    1. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. PTQuestion
    1. USbdwain
    1. USbdwain
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    2. PO
      singulars
      1. PTAnswer
    1. VO
      singulars
      1. POComposite clustered PK behavior vs Nonclustered PK + nonunique clustered index
      1. This table or related slice is empty.
      1. VTUpMod
    2. VO
      singulars
      1. POComposite clustered PK behavior vs Nonclustered PK + nonunique clustered index
      1. This table or related slice is empty.
      1. VTUpMod
    3. VO
      singulars
      1. POComposite clustered PK behavior vs Nonclustered PK + nonunique clustered index
      1. This table or related slice is empty.
      1. VTModeratorReview
    1. COhow is the "most popular `appId`" defined? is it the one with most files?
      singulars
      1. POComposite clustered PK behavior vs Nonclustered PK + nonunique clustered index
      1. USQuassnoi
    2. COyes that's how.
      singulars
      1. POComposite clustered PK behavior vs Nonclustered PK + nonunique clustered index
      1. USbdwain
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload