Having a switch to disallow duplicate rows. Would it be a useful feature?

Question

Having a switch to disallow duplicate rows. Would it be a useful feature?

sqlvogel

SSCrazy Eights

Points: 9526
More actions
December 6, 2009 at 12:04 pm

#96776

I think Codd may have been the first to suggest that SQL ought to have a software setting that allowed the developer to turn off duplicate rows in tables and queries.
As far as I know, no DBMS has actually done it. Although the intention is good I've always been sceptical as to whether it would really be feasible within the SQL language syntax. It seems like we'd have to sacrifice a lot of backwards compatibility. Does anyone else think it would be a desirable feature to have?

Viewing 5 posts - 1 through 4 (of 4 total)

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

RBarryYoung SSC Guru Points: 143329 More actions · Answer 1

What would that do the DISTINCT, UNIQUE, and Primary Keys don't already do?

[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung [font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]

Jeff Moden SSC Guru Points: 1004363 More actions · Answer 2

David Portas (12/6/2009)
I think Codd may have been the first to suggest that SQL ought to have a software setting that allowed the developer to turn off duplicate rows in tables and queries.
As far as I know, no DBMS has actually done it. Although the intention is good I've always been sceptical as to whether it would really be feasible within the SQL language syntax. It seems like we'd have to sacrifice a lot of backwards compatibility. Does anyone else think it would be a desirable feature to have?

I agree with Barry... PK's and Unique "keys" are there for that purpose.... Codd or not.

--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)

sqlvogel SSCrazy Eights Points: 9526 More actions · Answer 3

RBarryYoung (12/6/2009)
What would that do the DISTINCT, UNIQUE, and Primary Keys don't already do?

It would enable more advanced optimisation of queries and data access. SQL database engines are much less effective than they could be because of the need to allow for the possibility of duplicates, even when duplicate rows aren't actually used. For example, potential query rewrites can be used in a set-based database (ie: duplicates turned OFF) that would not be permissible in a multi-set database (duplicates allowed).

Longer term it could also ease the transition to new data access languages in the future.

RBarryYoung SSC Guru Points: 143329 More actions · Answer 4

David Portas (12/7/2009)
RBarryYoung (12/6/2009)
What would that do the DISTINCT, UNIQUE, and Primary Keys don't already do?
It would enable more advanced optimisation of queries and data access. SQL database engines are much less effective than they could be because of the need to allow for the possibility of duplicates, even when duplicate rows aren't actually used. For example, potential query rewrites can be used in a set-based database (ie: duplicates turned OFF) that would not be permissible in a multi-set database (duplicates allowed).
Longer term it could also ease the transition to new data access languages in the future.

Sorry, David, AFAIK, none of this is true. SQL Server CAN enforce no duplicates if you tell it to and it DOES take advantage of that in its optimizations, and it IS more highly optimized than any "purely relational" DBMS that I know of. There's a reason that the Top Ten spots in the TPC OLTP benchmarks have been almost exclusively SQL databases for the last 20 years, and it's not because other types of databases can be better optimized.

[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung [font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]