Database Reference
In-Depth Information
Listing 9-9. Views and Joins: Adding foreign key constraint
alter table dbo.Orders
with check
add constraint FK_Orders_Clients
foreign key(ClientId)
references dbo.Clients(ClientId)
A trusted foreign key constraint would guarantee that every order has a corresponding client row. As a result, SQL
Server can eliminate the join from the plan, as shown in Listing 9-10 and in Figure 9-4 .
Listing 9-10. Views and Joins: Selecting columns from the Orders table using vOrders view
select OrderId, OrderNumber, Amount
from dbo.vOrders
where OrderId = @OrderId
Figure 9-4. Execution plan with inner join when foreign key constraint is present
Unfortunately, there is no guarantee that SQL Server will eliminate all unnecessary joins, especially in very
complex cases with many tables involved. Moreover, SQL Server does not eliminate joins if the foreign key constraints
include more than one column.
Now let's review an example where a system collects location information for devices that belong to multiple
companies. The code that creates the tables is shown in Listing 9-11.
Listing 9-11. Join elimination and multi-column foreign key constraints: Table creation
create table dbo.Devices
(
CompanyId int not null,
DeviceId int not null,
DeviceName nvarchar(64) not null,
);
create unique clustered index IDX_Devices_CompanyId_DeviceId
on dbo.Devices(CompanyId, DeviceId);
create table dbo.Positions
(
CompanyId int not null,
OnTime datetime2(0) not null,
RecId bigint not null,
DeviceId int not null,
Latitude decimal(9,6) not null,
Longitute decimal(9,6) not null,
constraint FK_Positions_Devices
foreign key(CompanyId, DeviceId)
references dbo.Devices(CompanyId, DeviceId)
);
 
Search WWH ::




Custom Search