Distinct issues
in [Programming SQL]
|
Prev: need help with query
Next: Sum and aggregations
From: Tony Rogerson on 21 Apr 2010 00:32 > and not a human being. This was the safest way to do things when > people used to use IDENTITY as a key instead of the actual key for > tables in the early days. No - that's not realistic and you don't see that type of thing too often on real systems. If there is a natural key then there will also be RI on it either a unique index or unique constraint. The majority of folk correctly use the IDENTITY as a surrogate key generator - that's the reality of the situation on the user base of this product - I should know, I'm a product expert of over 15 years of SQL Server with 24 in the industry itself. Perhaps they read too much Darwen - because SQL is so bad at implementing relational theory that there is a corner of academia that actually recommend DISTINCT because tables must have unique rows otherwise its not a table at all. By the way, I realise you are blue in the face trying to convince people that the IDENTITY property is some sort of physical row locator - you can keep saying it until you are ultra violet in the face but it makes it no truer 10 years ago when you first started incorrectly saying it than it is now. Boy oh boy - don't you wish you did some research first before opening your mouth? --ROGGIE-- "--CELKO--" <jcelko212(a)earthlink.net> wrote in message news:833bcfa1-6096-4fcd-8040-ca727dd4dbed(a)z11g2000yqz.googlegroups.com... >>> It appears that the original programmer just decided to put DISTINCT on >>> everything. << > > That is often a sign that the original programmer was a code generator > and not a human being. This was the safest way to do things when > people used to use IDENTITY as a key instead of the actual key for > tables in the early days. > > You are probably screwed. Each query will have to be examined by hand > for keys in the results. > > But quick trick is to look for a SELECT DISTINCT with a GROUP BY that > has the same columns so you can drop the DISTINCT. Generated code was > that awful.
From: --CELKO-- on 21 Apr 2010 14:35 Instead of personal insults and unsupported claims, can you offer an explanation of why code would have so many SELECT DISTINCT at all levels of nesting? Even better, do you know if there is a tool or algorithm that will find if a query returns duplicate rows or not for the general case? I think the algorithm should exist, but it will be buried in an optimizer if anyone implemented it. But the tool will depend on the schema having properly declared uniqueness constraints. Without them the only safe assumption is that a column will have duplicate values.
From: tshad on 21 Apr 2010 14:34 "--CELKO--" <jcelko212(a)earthlink.net> wrote in message news:833bcfa1-6096-4fcd-8040-ca727dd4dbed(a)z11g2000yqz.googlegroups.com... >>> It appears that the original programmer just decided to put DISTINCT on >>> everything. << > > That is often a sign that the original programmer was a code generator > and not a human being. This was the safest way to do things when > people used to use IDENTITY as a key instead of the actual key for > tables in the early days. > That may be the case. > You are probably screwed. Each query will have to be examined by hand > for keys in the results. > > But quick trick is to look for a SELECT DISTINCT with a GROUP BY that > has the same columns so you can drop the DISTINCT. Generated code was > that awful. Good idea. That would work for query that had the GROUP BY and will have to struggle through the other queries. Thanks, Tom
From: Tony Rogerson on 22 Apr 2010 03:12
> Instead of personal insults and unsupported claims, There were no personal insults in my response to your drivel over IDENTITY. I'm fed up with you posting incorrect information around the IDENTITY property so on every occurrence you do expect me to respond and correct and highlight the fact you never listen to other product experts on what is a MICROSOFT SQL SERVER forum. You seem to be of the opinion that you somehow know more about the implementation of the IDENTITY property that other product experts on this forum like myself, Kalen and other SQL MVP's. Anyway, here was my response to your post for completeness.... No - that's not realistic and you don't see that type of thing too often on real systems. If there is a natural key then there will also be RI on it either a unique index or unique constraint. The majority of folk correctly use the IDENTITY as a surrogate key generator - that's the reality of the situation on the user base of this product - I should know, I'm a product expert of over 15 years of SQL Server with 24 in the industry itself. Perhaps they read too much Darwen - because SQL is so bad at implementing relational theory that there is a corner of academia that actually recommend DISTINCT because tables must have unique rows otherwise its not a table at all. By the way, I realise you are blue in the face trying to convince people that the IDENTITY property is some sort of physical row locator - you can keep saying it until you are ultra violet in the face but it makes it no truer 10 years ago when you first started incorrectly saying it than it is now. Boy oh boy - don't you wish you did some research first before opening your mouth? --ROGGIE-- |