March 4, 2020 at 6:15 pm
I have a table with some allowed duplicates in the most important index. But not all types of data allow duplicates. Specifically:
- a column with one or two letters
- a column with a number
When the letter column is '1e', the number column must be Null.
When the letter column has 's' as the second letter, duplicate numbers are allowed, and the number is required.
When the letter column does not have 's' as the second letter, duplicate numbers are not allowed, and the number is required.
Example data:
1e – Null
1e – Null
1e – Null
G – 1
G – 2
G – 3
Gs – 4
Gs – 4
Gs – 4
are all okay. But
G – 1
G – 1
would not be okay.
This was originally an Access database, and there I fixed the requirement for a 'partly unique' index by adding another column, which was 0 for all items requiring a unique number, and a generated, unique number for all items that allowed duplicate values in the first two columns. The combination of those three columns allowed me to create a unique index on the table, which enforced the 'partially unique' requirement for these two key fields.
Now that I have it in SQL Server, I think I can dispense with that extra field, and use a filtered view to enforce uniqueness only on the subset of records meeting the criteria specified here. (If I'm wrong, and there is a better way to do that, I'd like to hear about that as well.)
But my primary question here is about the view. Given that the only real purpose of the view is to enforce this uniqueness, should the view include ONLY the columns that I want to enforce this restriction on, or should I make the view broader, across the entire table, under the theory that it might be useful if someone is searching only for records that match this restricted criteria (which is most often the case – the 1e and ?s records are referenced much less frequently).
I don't want to create a needlessly large materialized view, but if such a view is likely to be used, and would speed things up, I would rather have it. Disk space is not an issue.
March 4, 2020 at 7:36 pm
Whether the number must be NULL or NOT NULL can be done with a CHECK constraint:
CONSTRAINT data__CK_1
CHECK(CASE WHEN letter_column = '1e' THEN CASE WHEN number_column IS NULL THEN 1
ELSE 0 END ELSE CASE WHEN number_column IS NULL THEN 0 ELSE 1 END END = 1)
Personally I'd just use an extra column and a standard index to enforce the uniqueness constraints, since you aren't worried about space anyway.
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
March 4, 2020 at 7:54 pm
Yes, I can use the check constraint for the field. But I need the additional constraint of uniqueness for specific types of data, while not for others. I don't think I can use a check constraint for that, can I?
March 4, 2020 at 8:31 pm
No. CHECK does not go across rows. You will need an index or some other multi-row method.
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
March 4, 2020 at 8:47 pm
We can't tell if you should add more columns to that view - we know nothing about your system.
And I am not sure that an indexed view will actually work in this case, since there are restrictions of what you can use an indexed view.
Overall, filtered indexes are better choices for enforcing uniqueness, but again, I don't think this will fly here, since only quite simple conditions are permitted for a filtered index. You would need substring or LIKE here, and none of them are permitted, as far as I know. If this second letter had been a column instead, it would have been a different thing.
And as Scott points: for the NULL requirement, use a CHECK constraint.
[font="Times New Roman"]Erland Sommarskog, SQL Server MVP, www.sommarskog.se[/font]
March 4, 2020 at 10:03 pm
The requirements are to look up and filter by conditions in the data, almost always at least partly by the letter. I would not use this filtered view directly, I would aim my Select at the underlying table. But I wonder if the query engine would use such a pre-filtered and materialized view, if the conditions on the view matched some of the condition in the Where clause, or if it would always go to the underlying table, and so having extra columns in this view would simply waste space.
I've read the restrictions on these views, and what I need fits in the guidelines. There is no self-reference or join, or any of the other forbidden items. It does have a LIKE phrase in the Where clause: 'EvidenceLetter LIKE N'_', but I see no mention of that being forbidden, and the view works. Creating a unique clustered index on the view worked as well. Found a few duplicates in the process that had somehow slid by my application code, which is one of the reasons I'm interested in this.
I'm now just curious if including the entire table in the view can be of any use with regards to performance, or if I'm just wasting space. The fact that I have plenty still does not incline me to waste it pointlessly.
Is there a way to determine if the data in this view is ever used, or whether the engine always goes back to the base table?
March 4, 2020 at 10:11 pm
What edition of SQL Server do you have? The optimizer will only consider indexed views in Enterprise Edition. The only way an indexed view can be used in Standard Edition is if you query the view directly and specify the NOEXPAND hint.
[font="Times New Roman"]Erland Sommarskog, SQL Server MVP, www.sommarskog.se[/font]
March 4, 2020 at 10:30 pm
I'm still using 2008 R2 standard edition. But I'm looking at upgrading to 2014. Nothing newer will run on the OS this server has, and I don't know if I can upgrade the OS.
The machine is over ten years old. Still running well, but I'm getting a little nervous, and am looking at some preparations for if the server seriously packs it in.
March 4, 2020 at 10:32 pm
But I think you've answered my question. If the indexed view will only be considered in Enterprise, then the extra columns are pointless, and my indexed view would be better off with only the columns participating in the anti-duplicate restriction.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply