With SQL Server 2008, many options may affect the performance of individual SQL statements or the overall performance of a SQL Server instance. There are instance-wide configuration options (set using the
sp_configure system stored procedure or with the server properties dialog within SSMS), database-level options (set with
ALTER DATABASE), database compatibility levels (using the
sp_dbcmptlevel system stored procedure), batch-level options (using the
SET command for things such as
SET ANSI_NULLS), and statement-level options (such as table hints, query hints, and join hints). Always remember that a hint overrides a
SET option, a
SET option overrides a database option, and a database option overrides an instance-wide option. ...