9

I'm adding the following cross-reference table to my SQL Server-hosted DB:

company_id bigint not null (FK)
org_path nvarchar (2048) not null

The company_id field refers to the id field in another table (in which it's the primary key).

Given that there can also be multiple records with the same company_id, any primary key would have to use both fields. However, I'm unable to create a key using both fields because org_path is too long for SQL Server.

As for org_path, this is the only table in which it exists. There's every likelihood that queries to this table will be asking for either all entries or all org_path entries by company_id. Or to put it another way, it looks doubtful that this table will ever be queried by org_path. Furthermore, it's unlikely that org_path will be updated, and more likely inserted and - probably rarely - deleted.

I expect that the total number of rows will be in the low thousands.

Also, the reason it's nvarchar (2048) is because the value has to mimic that in a third-party DB. A typical example will be something like

\Translation Providers\[customer name]\[order name]\

and can contain diacritics.

So my question is this: would it be more efficient to add an auto-increment id field and use that in conjunction with company_id as the primary key, or would it add unnecessary overhead - and does the fact that company_id is the primary key in another table have any effect here?

7

For a non-unique clustered index on comany_id alone, SQL Server will automatically add a 4 byte integer uniqueifier to all duplicate (i.e. second and subsequent for a key value) clustered index keys to make it unique. This is not exposed to the user though.

The advantage of adding your own unique identifier as a secondary key column is that you can then still seek by company_id but also seek to individual rows more efficiently (using company_id, identitycol rather than company_id with a residual predicate on org_path). The clustered index would then be unique on company_id, identitycol, so no hidden uniqueifiers would be added.

Also, if you end up with duplicates for (company_id,org_path), having the explicit identity column (a sort of "exposed uniqueifier") will make it easier to target just one of them for delete or update.

12

One thing to consider is that a Primary Key and a Clustered Index are not the same thing. A Primary Key is a constraint and deals with the rules by which the data lives (i.e. data integrity); it has nothing to do with efficiency / performance. A Primary Key requires that the key column(s) be unique (in combination) and NOT NULL (individually). A PK is enforced via a Unique Index, though it can be either Clustered or Non-Clustered.

A Clustered Index is a means of physically (i.e. on disk) ordering the data in the table and deals with performance; it has nothing to do with data integrity. A Clustered Index can require that the key column(s) be unique (in combination), but it doesn't need to. However, since the Clustered Index is the physical order of the data, it needs to uniquely identify each row no matter what. So if you don't set it to require uniqueness, it will create its own uniqueness via a hidden 4-byte "uniquifier" column. That column is always there in non-Unique Clustered Indexes, but it doesn't take up any space when the key fields are unique (in combination). To see first hand how this "uniquifier" column works (both in the Clustered Index and the effect on Non-Clustered Indexes), please check out this test script I posted on PasteBin: T-SQL script to test Uniquifier size.

Hence, the main question of:

would it be more efficient to add an auto-increment id field and use that in conjunction with company_id as the primary key, or would it add unnecessary overhead

is conflating those two concepts, so they need to be addressed separately, though there is definitely some overlap.

Should an IDENTITY column be added or would it be unnecessary overhead?

If you add an INT IDENTITY column and use it to create a PK, assuming it would be a Clustered PK, that adds 4 bytes to every row. This column is visible and usable in queries. It could be added to other tables as a Foreign Key, though in this particular case that won't happen.

If you don't add the INT IDENTITY column, then you can't create a PK on this table. However, you can still create a Clustered Index on the table as long as you don't use the UNIQUE option. In this case, SQL Server will add a hidden column called "uniquifier" which behaves as described above. Because the column is hidden, it cannot be used in queries or as a reference for Foreign Keys.

So as far as efficiency goes, these options are roughly the same. Yes, there will be slightly less space taken up by having the non-Unique Clustered Index due to some rows (ones with the initial unique key values) taking up 0 bytes while all rows in the IDENTITY / PK will take the 4 bytes. But there won't be enough of the 0 byte rows (especially with the small amount of rows expected) to ever notice a difference, let alone out-weigh the convenience of being able to use the ID column in queries.

INT IDENTITY Column or Hash of org_path Persisted Computed Column?

Given that you won't be looking up rows based on org_path values, then it doesn't make sense to add the overhead of the Persisted Computed Column plus needing to compute that hash in queries in order to match against the Computed Column (this was my original suggestion, available in the revision history here, which was based on the initial wording / details of the Question). In this particular case, the INT IDENTITY "ID" Column is probably best.

Key Column Order

Given that the ID Column will rarely, if ever, be used in queries, and given that the two main use-cases are to get either "all rows" or "all rows for a given company_id", I would create the PK on company_id, id. And because this means that rows are not inserted sequentially, I would specify a FILLFACTOR of 90. You will also need to make sure to do regular index maintenance to reduce the fragmentation.

Second Question

does the fact that company_id is the primary key in another table have any effect here

No.

Trigger

Since org_path values within a company_id are unique, you should still create a Trigger on INSERT, UPDATE to enforce this. In the Trigger, do an IF EXISTS with a query that probably does a COUNT(*) and GROUP BY company_id, org_path. If anything is found, issue a ROLLBACK to cancel the DML operation and then a RAISERROR saying that there are duplicates.

Collation

In my initial answer (based on original wording / sparse details of the question, and available in the revision history here), I had suggested possibly using a binary (i.e. _BIN2) Collation. Now that we have insight into what exactly org_path is, I would not recommend using a binary Collation. Since there will be diacritical marks, you do want to make use of linguistic equivalences.

0

Why do you need a PK?

Why not just go with company_id as a non-clustered index?

You said most searched are on all entries or by company_id
Rarely update
Rarely delete
org_path, this is the only table in which it exists

The answer from Martin Smith may get you what you need
I am not familiar with automatically add a 4 byte integer uniqueifier
Maybe I am missing something but if you don't have other columns indexed then I see no purpose to it in this use case

If you are concerned about DRI the tables should use the Company table as the FK for company_id

  • Hey. Regarding "Why not just go with company_id as a non-clustered index?": because that would have 2 down-sides: 1) it would be 1 more thing taking up space whereas a Clustered Index is the table so no additional item, and 2) it would still require an RID lookup to get the NVARCHAR field, unless that were an INCLUDE column, but that's even worse as it's merely duplicating the table. True, the PK isn't necessary; the important part is the Clustered Index. But once you have the IDENTITY, might as well go with PK. And please see the new link in my answer for a walk-through on Uniquifier 😃 – Solomon Rutzky Jun 2 '16 at 16:12
  • @srutzky But it avoids a 4 byte integer uniqueifier so I see that as a wash – paparazzo Jun 2 '16 at 16:25
  • With less than 10k rows, it won't matter; you probably need to be in the millions of rows before you notice the effect of just 4 bytes. So for the "get all rows" query there isn't really any difference in any of these options. But for the "get for company_id = @param" query, having the data physically ordered by company_id will help, especially when it doesn't need to do an RID lookup for every row. – Solomon Rutzky Jun 2 '16 at 16:55
  • @srutzky Wash is a wash - 10K or 1G. It is just something for the OP to consider. – paparazzo Jun 2 '16 at 17:01

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.