StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
text
Body
copied!<p>What you are describing sounds kind of like an inheritance structure. I have created an example dataset as far as I understand it. Can you verify this is your intent?</p> <p>If it is, then this should work fine, and I do not see why you are not setting the CategoryType.Id as the primary key? If it is not your PK, nor being referenced as a FK elsewhere, then I dont see a point to it. I personally dont think you gain enough in bandwidth savings, and should probably just request the data by CategoryId and Name. In fact, no PK is often how inheritance structures are represented ( <a href="https://stackoverflow.com/questions/3579079/how-can-you-represent-inheritance-in-a-database">How can you represent inheritance in a database?</a> ).</p> <p>If you must keep it the way that you have it set up, I personally suggest setting the Id as the PK, and just setting up a unique constraint on CategoryId/Name. </p> <p>That is just my two cents, though.</p> <pre><code>Category ---- Id|Name 1 |Food 2 |Drink CategoryType ---- Id|CategoryId|Name 1 |2 |Water 2 |2 |Orange Juice </code></pre> <p>UPDATED ANSWER (to directly address performance concerns)</p> <p>First, I would suggest not entirely worrying about it too much if it is not a problem. That is a common problem many of us make, overcomplicating something that does not need it. That falls under the <a href="http://en.wikipedia.org/wiki/KISS_principle" rel="nofollow noreferrer">KISS principle</a> in my book</p> <p>However, if you are deadset on trying to figure this out ahead of time the way you explained, then here are my additional thoughts:</p> <ul> <li>Create the PK as Id, however make it NONCLUSTERED</li> <li>Create the Clustered Index on CategoryId and consider using an <a href="http://msdn.microsoft.com/en-us/library/ms190806.aspx" rel="nofollow noreferrer">INCLUDE keyword</a> on the above nonclustered index.</li> <li>Only do the above if you are more often going to be using the CategoryId for querying, than the CategoryType.Id</li> <li>A consideration when creating your keys though (even taken from the <code>INCLUDE</code> article)</li> </ul> <blockquote> <pre><code>Index maintenance may increase the time that it takes to perform modifications , inserts, updates, or deletes, to the underlying table or indexed view. </code></pre> </blockquote> <p>Ultimately, I think what you are doing will be fine, however a PK does not have to be clustered, so I would definitely move the PK to the Id field. It is your choice if you want to make the cluster on CategoryId or CategoryId/Name, or if you want to try using the INCLUDE as I suggested. This really will depend on how the tables are being used, so comparing execution plans might help here.</p> <p>Hopefully this helps :)</p>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

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