Everyone.
Something I have been trying lately is the reduction of Existence lookups on some of our larger tables in the domain and having the work done with a check constraint. Here is a simple example with simple users table.
CREATE SCHEMA Org
CREATE TABLE Org.Users
(
UserID INT IDENTITY(1,1) PRIMARY KEY NONCLUSTERED NOT NULL,
SourceSystemId INT NOT NULL,
SourceUserId VARCHAR(50) NOT NULL,
OrganizationIDINT NOT NULL,
LastName VARCHAR(75) NOT NULL,
FirstName VARCHAR(50) NOT NULL,
EmailAddress VARCHAR(100) NULL,
PhoneNumber VARCHAR(14) NULL,
AddressID INT
)