Unique Index

Question

Post reply

Unique Index

sw_engr

SSC Enthusiast

Points: 154
More actions
March 31, 2015 at 3:23 am

#306179

I am trying to create an unique index on the following table that stores relationship information between two persons with constraints where [end_date] IS NULL OR [end_date] >= GETDATE()
CREATE TABLE [dbo].[person_relationship](
[person_relationship_id] [bigint] IDENTITY(1,1) NOT NULL,
[person_id_1] [bigint] NOT NULL,
[person_id_2] [bigint] NOT NULL,
[person_relationship_type_id] [bigint] NOT NULL,
[start_date] [datetime] NOT NULL,
[end_date] [datetime] NULL,
CONSTRAINT [PK_person_relationship] PRIMARY KEY CLUSTERED
(
[person_relationship_id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
ALTER TABLE [dbo].[person_relationship] WITH CHECK ADD CONSTRAINT [FK_person_relationship_1_person] FOREIGN KEY([person_id_1])
REFERENCES [dbo].[person] ([person_id])
GO
ALTER TABLE [dbo].[person_relationship] CHECK CONSTRAINT [FK_person_relationship_1_person]
GO
ALTER TABLE [dbo].[person_relationship] WITH CHECK ADD CONSTRAINT [FK_person_relationship_2_person] FOREIGN KEY([person_id_2])
REFERENCES [dbo].[person] ([person_id])
GO
ALTER TABLE [dbo].[person_relationship] CHECK CONSTRAINT [FK_person_relationship_2_person]
GO
The following is the unique index I am attempting to create but SQL is not having it.
CREATE UNIQUE INDEX [IX_person_1_person_2_relationship_type]
ON [dbo].[person_relationship]([person_id_1], [person_id_2], [person_relationship_type_id])
WHERE ([end_date] IS NULL) OR ([end_date] >= GETDATE())
The issues are "OR" and "GETDATE()". I found out that using date functions result in error but not sure why "OR" is causing grief.
Any advise/suggestions will be deeply appreciated.

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

dbafromthecold@gmail.com SSChampion Points: 10160 More actions · Answer 1

I don't think OR is supported in filtered indexes WHERE clause. Have a look at Aaron Bertrand's answer here:-

http://stackoverflow.com/questions/20912892/sql-server-2012-filtered-index-is-the-where-clause-too-complex

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 2

DBA From The Cold (3/31/2015)
I don't think OR is supported in filtered indexes WHERE clause.

That's my recollection as well. Filtered indexes are nice in places, but they're still very limited.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

MadAdmin SSChampion Points: 11261 More actions · Answer 3

When using Filtered indexes, just be careful of Procs where Quoted_Identifier is OFF for the update inserts.

You can find this by selecting * from sql_modules where quoted_identifier is off, to see which procs are impacted, and then join those to syscomments to find the procs which use the table.

Catch-all queries done right [/url]
Gail Shaw's Performance Blog[/url]

sw_engr SSC Enthusiast Points: 154 More actions · Answer 4

Thanks for your replies.

Are there any workarounds?

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 5

If you can figure out a computed column that will always be unique, using your requirements and probably the primary key, you can put a unique index on that

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

0x00F123 SSC Journeyman Points: 82 More actions · Answer 6

0x00F123

SSC Journeyman

Points: 82

March 31, 2015 at 9:28 am

#1787770

or - not allowed

GETDATE() - not deterministic

Dwain Camps SSC Guru Points: 86908 More actions · Answer 7

I see you have start and end dates on your relationships.

If you add a new row for an existing relationship, what are the rules regarding the dates?

How is the current relationship modeled (i.e, what is the end date)?

My mantra: No loops! No CURSORs! No RBAR! Hoo-uh![/I]
My thought question: Have you ever been told that your query runs too fast?

My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.

Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St

joshdbguy SSCertifiable Points: 7707 More actions · Answer 8

MadAdmin (3/31/2015)
When using Filtered indexes, just be careful of Procs where Quoted_Identifier is OFF for the update inserts.
You can find this by selecting * from sql_modules where quoted_identifier is off, to see which procs are impacted, and then join those to syscomments to find the procs which use the table.

Also double check Triggers as well. As it's unlikely, but possible Quoted_Identifier is set to off. It's also in sql_modules. Just yesterday, I had set quoted_identifiers to On for around 40 SP's due to a new filtered index. Luckily, it didn't cause other conflicts by changing the status to On.