SET DEADLOCK_PRIORITY

SET DEADLOCK_PRIORITY - is exactly what it sounds like: Specifies the relative importance that the current session continue processing if it is deadlocked with another session.

SET DEADLOCK_PRIORITY { LOW | NORMAL | HIGH | <numeric-priority> | @deadlock_var | @deadlock_intvar }

<numeric-priority> ::= { -10 | -9 | -8 | … | 0 | … | 8 | 9 | 10 }
LOW maps to -5, NORMAL to 0, and HIGH to 5.

http://technet.microsoft.com/en-us/library/ms186736.aspx

Force statistics update - WITH FULLSCAN

SQL Server automatically update statistics according to few rules. 

In order to force statistics update (even when SQL Server rules are not met) - use WITH FULLSCAN.

UPDATE STATISTICS TABLENAME WITH FULLSCAN

Note: sp_autostats doesn't force the statistics update.

Random ordering of query

SELECT .... order by NEWID()

Costs are estimated, lines thickness is actual

This one is important and confusing:

costs are estimated, lines thickness is actual!

Create filtered statistics

Create Statistics <StatisticsName> 

On <TableName>(<columns list>) 

Where <condition>

Example:

CREATE STATISTICS ContactPromotion1

    ON Person.Person (BusinessEntityID, LastName, EmailPromotion)

WHERE EmailPromotion = 2

http://msdn.microsoft.com/en-us/library/ms188038.aspx

Permissions list of the connected user

SELECT * FROM fn_my_permissions (NULL, 'DATABASE');

There are no primary or candidate keys in the referenced table that match the referencing column list in the foreign key

Error message:

There are no primary or candidate keys in the referenced table that match the referencing column list in the foreign key

Explanation:

The foreign key must reference:

• Primary key, 

or:

• a field with a unique constraint.

Solution:

If you can edit the PK - do it. If not - alter/create a fit unique constraint.