June 28, 2017 at 4:08 am
Morning All,
I have a need to demonstrate the difference between execution plans when a constraint is trusted and untrusted.
I've set up the example but now that I have made the foreign key trusted (using with check check constraint) and observe a better execution plan - I can't now make the constraint untrusted. Issuing with NOCHECK doesn't seem to alter the meta data in sys.foreign_keys -- its always is_not_trusted =0.
Is there a way to make this untrusted properly?
Cheers
Alex
June 28, 2017 at 4:19 am
Try dropping the constraint and recreating it WITH NOCHECK.
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
June 28, 2017 at 4:23 am
Issuing a command of NOCHECK does change the is_not_trusted value for me, however, you could also make double sure my inserting an untrusted record:USE DevTestDB;
GO
--Create Primary Table
CREATE TABLE PrimaryTable
(PrimaryID int
CONSTRAINT PK_PrimaryID PRIMARY KEY CLUSTERED,
String varchar(20));
GO
--Create ForeignTable
CREATE TABLE ForeignTable
(ForeignID int
CONSTRAINT PK_ForeignID PRIMARY KEY CLUSTERED,
PrimaryID int,
AnotherString varchar(20));
--Add Foreign key Constraint WITH CHECK
ALTER TABLE ForeignTable WITH CHECK ADD CONSTRAINT FK_PrimaryID FOREIGN KEY (PrimaryID)
REFERENCES PrimaryTable(PrimaryID);
GO
--Create some Primary records
INSERT INTO PrimaryTable
VALUES
(1,'sdkjfhkf#'),
(2,'asdihfgasdklf');
GO
--Create some Foreign records, with valid keys
INSERT INTO ForeignTable
VALUES
(1,1,'fgaskdjf'),
(2,1,'dsjahgdslfhs'),
(3,2,'as,dbaskdh');
GO
--Check is_not_trusted value
SELECT name, is_not_trusted
FROM sys.foreign_keys
WHERE name = 'FK_PrimaryID';
GO
--Set the Foreignkey to NOCHECK
ALTER TABLE ForeignTable NOCHECK CONSTRAINT FK_PrimaryID;
GO
--Check is_not_trusted value again (it should be 1 now)
SELECT name, is_not_trusted
FROM sys.foreign_keys
WHERE name = 'FK_PrimaryID';
GO
--Just in case, insert some bad data
INSERT INTO ForeignTable
VALUES
(4,3,'sdkagsdkjf');
GO
--Check is_not_trusted value again. It will definitely be 1 now.
SELECT name, is_not_trusted
FROM sys.foreign_keys
WHERE name = 'FK_PrimaryID';
GO
--Clean up
DROP TABLE ForeignTable;
DROP TABLE PrimaryTable;
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
June 28, 2017 at 4:26 am
Thanks Gail. Worked perfectly after doing that.
Owe you beer as that was really annoying me. I even tried pressing F5 harder in case that helped.
June 28, 2017 at 4:41 am
Thom A - Wednesday, June 28, 2017 4:23 AMIssuing a command of NOCHECK does change the is_not_trusted value for me, however, you could also make double sure my inserting an untrusted record:
Interesting Thom.
I've tried it on a few tables in test now and none respond to reverting back to NOCHECK after doing WITH CHECK.
Are you using SQL Server 2016?
June 28, 2017 at 4:59 am
alex.sqldba - Wednesday, June 28, 2017 4:41 AMThom A - Wednesday, June 28, 2017 4:23 AMIssuing a command of NOCHECK does change the is_not_trusted value for me, however, you could also make double sure my inserting an untrusted record:Interesting Thom.
I've tried it on a few tables in test now and none respond to reverting back to NOCHECK after doing WITH CHECK.
Are you using SQL Server 2016?
Tested initially on SQL 2012, but then again on 2016 dev. Same result. After changing the to NOCHECK, the is_not_trusted value changed to 1. Even works on SQL 2017 CTP 2.1 on Linux. 😉
Not sure why different results.
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply