DBA’s sometimes have to deal with funny requests like don’t create that foreign key because developers will handle that in the code… \ud83d\ude42<\/p>\n
By definition, Foreign key constraints will enforce the existence of a value in a table prior to be inserted into another table which references it. <\/p>\n
That can be done programmatically in TSQL or any other language, but it is important to understand that for the SQL engine, knowing that information in the form of a foreign key constraint can help it to take decisions beforehand and avoid some extra work.<\/p>\n
But, let’s stop the talking and go to the point.<\/p>\n
\r\nUSE AdventureWorks2014\r\nGO\r\n\r\nSELECT * INTO Person.Address_noFK FROM Person.Address\r\nGO\r\n<\/pre>\nI’ve just created a copy of [Person].[Address] with no Foreign Keys as opposed to the original which contains one, [StateProvinceID] which references the table [Person].[StateProvince]<\/p>\n
If we run a simple query against both, the original and the one without Foreign Key, we can see how they behave completely different.<\/p>\n
\r\nSELECT * \r\nFROM Person.Address\r\nWHERE StateProvinceID IN (SELECT [StateProvinceID] FROM [Person].[StateProvince])\r\nGO\r\n\r\nSELECT * \r\nFROM Person.Address_noFK\r\nWHERE StateProvinceID IN (SELECT [StateProvinceID] FROM [Person].[StateProvince])\r\nGO \r\n<\/pre>\n
![\"01_Query_Plan\"](\"https:\/\/sqldoubleg.live-website.com\/wp-content\/uploads\/2016\/06\/01_Query_Plan.png\")
<\/a><\/p>\n