A question about unique keys

Question

Post reply

A question about unique keys

eichnerm

SSC Enthusiast

Points: 184
More actions
December 15, 2022 at 9:17 pm

#4126233

I've been away from SQL Server for a number of years. Years ago what I am asking could not be done. I assume that it still can't be done, but rather than make an assumption I thought that I should ask.
Below is a completely fictitious example, but it illustrates my question.
Supposing that I have a table with only two fields: Username and User Status. Is there a way using only database features as opposed to adding business logic to my stored procedures or code that calls my stored procedures, that I could ensure that given the following entries:
Username='Superman' User Status = 'D'
Username='Superman' User Status = 'D'
Username='Superman' User Status = 'D'
Username='Superman' User Status = 'D'
That I could add another entry with the Username = 'Superman' and ensure that I only have one record with a User Status = 'I' or 'A'. In other words duplicate Usernames with a UserStatus of 'D' are okay, but only one Username of 'Superman' can have a UserStatus of 'A' or 'I'.
I need to be able to do know that if I issue a select command where Username='Superman' and UserStatus <> 'D' that I will only return one record. A record with a status of 'A' or 'I'.
I am just wondering if in the years since I've been away that there hasn't been an advancement that would allow me to accomplish what I want by just using some sort of new database feature as opposed to adding business logic to my stored procedures or the code that calls my stored procedures.
If there is such a way, can someone please point how how to do this or a link to an article that explains how to accomplish my goal.
Thanks
December 16, 2022 at 5:25 am

This was removed by the editor as SPAM
December 16, 2022 at 7:10 pm

This was removed by the editor as SPAM

Viewing 11 posts - 1 through 10 (of 10 total)

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

frederico_fonseca SSCoach Points: 16154 More actions · Answer 1

you likely can create a unique filtered index to achieve that - with a "where <> 'D'"

create unique nonclustered index xxx on table yyy (username) where <> 'D'

Ken McKelvey SSCoach Points: 18842 More actions · Answer 2

https://learn.microsoft.com/en-us/sql/relational-databases/tables/temporal-tables?view=sql-server-ver16

eichnerm SSC Enthusiast Points: 184 More actions · Answer 3

Thanks to both of you for getting back to me.

I am a little confused about the unique filtered index. I was trying to read up on them, but every article that I read talked about performance. Nothing else.

Are you saying that if I create a unique filtered index that in the scenario below if someone tries to add in a second Username='Superman' User Status = 'A' that the insert will fail? If so, great. That's exactly what I am looking for.

Are there any downsides? Will performance when selecting the 'A' records be slower? The goal is that the 'A' records are the only ones that I am interested in. Those marked 'D' are there for historical purposes and will only be used in queries that use the primary key (which I didn't show), not the composite key of Username and UserStatus.

Username='Superman' User Status = 'A'

Username='Superman' User Status = 'D'

Thanks so much.

Jeff Moden SSC Guru Points: 1004312 More actions · Answer 4

Manish_Bose wrote:

How to convert long data to wide data in SQL without using hard coding?
Regards

Like I suggested on the other thread, Manish, open a separate thread for your own question.

[EDIT] Totally missed the fact that "Manish" had embedded spam in his signature. Removed that.

Thanks for the heads up, Frederico.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Grant Fritchey SSC Guru Points: 398881 More actions · Answer 5

Yeah, it should work as described.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

eichnerm SSC Enthusiast Points: 184 More actions · Answer 6

Thank you. I ran a test. It's amazing. It simplifies what I was planning to do.

I am wondering if there are any negatives beyond the negative of hardcoding a value in the index for the where clause (where <> 'D').

Thanks

Grant Fritchey SSC Guru Points: 398881 More actions · Answer 7

Yeah, hard coding isn't perfection, but, that's the situation. Work within it.

The negative is, that index probably won't be used for a lot of SELECT queries since it doesn't eliminate that many rows (I assume, maybe I'm wrong). So, while it's helping you in terms of ensuring the unique values of all none D shaped columns, you may be paying overhead for storage and maintenance of the index with no additional benefit. However, it's getting the job done that you need it to do, so, that may be all the benefit you need.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

eichnerm SSC Enthusiast Points: 184 More actions · Answer 8

Thank you for your advice. For my needs this appears to be a great solution.