SQL Server also pre-processes all sorts of things.
For example:
WHERE A <= 10
AND >= 10
That can safely be replaced by:
WHERE A = 10
But they are very cautious about rewriting your code. Very easy to break it.
But they really have to. While humans can write lousy code, code generators are
spectacular at doing so. Here’s an example:
WHERE
b.entry >= ISNULL(NULL,'1/1/1900')
AND b.entry < DATEADD(DAY,1,ISNULL(NULL,'1/1/3000')
)
AND --Filter on Facility
(
(NULL IS NOT NULL AND pv.FacilityID IN (NULL)) OR
(NULL IS NULL)
)
AND --Filter on Company
(
(NULL IS NOT NULL AND pv.CompanyID IN (NULL)) OR
(NULL IS NULL)
)
AND --Filter on Financial Class
(
(NULL IS NOT NULL AND pv.FinancialClassMID IN (NULL)) OR
(NULL IS NULL)
)
Etc. etc. etc. (Not to mention their Y3K problem)
Regards,
Greg
Dr Greg Low
1300SQLSQL (1300 775 775) office | +61 419201410 mobile│ +61 3 8676 4913 fax
SQL Down Under | Web: www.sqldownunder.com<http://www.sqldownunder.com/>
From: [email protected] [mailto:[email protected]] On
Behalf Of Greg Low (??????)
Sent: Tuesday, 9 February 2016 4:12 PM
To: ozDotNet <[email protected]>
Subject: RE: SQL foreign key question
Yes, if it just points to itself, it already knows that it satisfies it. It is
respecting the constraint. There is nothing to check.
I see dumb stuff like this all the time.
Was at a site recently where they had triple-created all the FK constraints. No
big surprise that it doesn’t actually check the value 3 times (to be sure, to
be sure, to be sure), regardless of what it shows in the plan.
It’s also one of the reasons why I always name constraints. Then you can’t
accidentally multiple-create them. (Reason #3723924 for not using visual table
designers).
Regards,
Greg
Dr Greg Low
1300SQLSQL (1300 775 775) office | +61 419201410 mobile│ +61 3 8676 4913 fax
SQL Down Under | Web: www.sqldownunder.com<http://www.sqldownunder.com/>
From: [email protected]<mailto:[email protected]>
[mailto:[email protected]] On Behalf Of David Burstin
Sent: Tuesday, 9 February 2016 4:06 PM
To: ozDotNet <[email protected]<mailto:[email protected]>>
Subject: Re: SQL foreign key question
On 9 February 2016 at 16:02, Greg Low (罗格雷格博士)
<[email protected]<mailto:[email protected]>> wrote:
You often see tables referring to themselves (due to some sort of hierarchical
data) but having a column refer to itself seems at best pointless, at worst
silly.
For me the bigger question is how this actually works. Does the sql engine just
decide to ignore the constraint because it is so obviously dumb? If so, am I
the only one bothered by that? I would have actually preferred if no records
could be added to the table, because at least then I know that the engine is
respecting my constraints.
And reason #3723923 why I won’t ever use such a visual table designer.
Regards,
Greg
Dr Greg Low
1300SQLSQL (1300 775 775) office | +61 419201410<tel:%2B61%20419201410> mobile│
+61 3 8676 4913<tel:%2B61%203%208676%204913> fax
SQL Down Under | Web: www.sqldownunder.com<http://www.sqldownunder.com/>
From: [email protected]<mailto:[email protected]>
[mailto:[email protected]<mailto:[email protected]>] On
Behalf Of Tony McGee
Sent: Tuesday, 9 February 2016 3:59 PM
To: ozDotNet <[email protected]<mailto:[email protected]>>
Subject: Re: SQL foreign key question
This can sometimes happen to a primary key if you use the visual query designer
to create a FK relationship and don't change the defaults before clicking OK.
It looks like it's ignored in the insert query execution plan.
On 9 Feb 2016 2:32 pm, "David Burstin"
<[email protected]<mailto:[email protected]>> wrote:
I came across this (snipped to protect the innocent):
CREATE TABLE [dbo].[V2_BREC_NMIStatusHistory] (
[NMIStatusHistoryId] INT IDENTITY (1, 1) NOT FOR REPLICATION NOT
NULL,
<snip/>
CONSTRAINT [PK_V2_BREC_NMIStatusHistory] PRIMARY KEY CLUSTERED
([NMIStatusHistoryId] ASC),
CONSTRAINT [FK_V2_BREC_NMIStatusHistory_V2_BREC_NMIStatusHistory] FOREIGN
KEY ([NMIStatusHistoryId]) REFERENCES [dbo].[V2_BREC_NMIStatusHistory]
([NMIStatusHistoryId])
);
Notice that the primary key identity field has a foreign key constraint on
itself. How does this work?
I would have thought that any attempt to add a record would check the table for
the existence of the new key, and as it obviously wouldn’t exist yet, that
would break the foreign key constraint resulting in the record not being
written. But, the table has plenty of data.
Anyone have any ideas how this actually works, or does it just do nothing?
