Every version of SQL Server comes with a lot of optimizations to the database engine, and these changes require changes to the T-SQL syntax. New statements are introduced or modified and existing ones are dropped. Often table and query hints are optimized and modified in a similar fashion. These table and query hints are often used (in many cases, indiscriminately) to “improve the performance” of Microsoft SQL Server.

Let me start outright by saying that till date, I have never encountered the need to use the hint that we are about to discuss today.

Table v/s Query hints

Before going any further, I thought it best to revisit the simple method of identifying which is a table hint and which one is a query hint. For example, take the query below:

--Query for demonstration purposes only!
USE AdventureWorks2012
GO
SELECT *
FROM HumanResources.Employee WITH (NOLOCK) -- WITH (NOLOCK) is the table hint
WHERE OrganizationLevel = 3
OPTION (MAXDOP 1)                          -- OPTION (MAXDOP 1) is the query hint

The WITH (NOLOCK) hint is applied to the table/CTE/sub-query, i.e. a data source. Hence the name – table hint.

The OPTION (MAXDOP 1) is applied to the entire query, and hence the name – query hint.

[Edit]: You may also find the following post from Pinal Dave, a.k.a. “SQLAuthority” (B|T), useful in understanding Query Hints: http://blog.sqlauthority.com/2012/05/09/sql-server-quiz-and-video-introduction-to-basics-of-a-query-hint/

FASTFIRSTROW

According to SQL Server Books On Line, the FASTFIRSTROW is a table hint that helps the developer control the behaviour of the optimizer such that the optimizer,

• Optimizes for overall minimum cost (minimum processing time/resources, etc), OR
  
• Optimizes such that the time taken for the first row to be returned is minimized

The FASTFIRSTROW tells the optimizer that it is important to have the first row returned as fast as possible, allowing the application to return results to the users faster. You can see the difference by comparing the actual execution plans produced by the queries below:

--Query for demonstration purposes only!
USE AdventureWorks2008
GO
--Without the table hint
SELECT *
FROM HumanResources.Employee 
GO
--With the table hint
SELECT *
FROM HumanResources.Employee WITH (FASTFIRSTROW)
GO

OPTION (FAST n)

Running the above query in SQL Server 2012 results in the following error:

Msg 321, Level 15, State 1, Line 3
“FASTFIRSTROW” is not a recognized table hints option. If it is intended as a parameter to a table-valued function or to the CHANGETABLE function, ensure that your database compatibility mode is set to 90.

The message says it all – FASTFIRSTROW is no longer available, and if you would like to use it, the database compatibility level needs to be set to 90 (i.e.  SQL Server 2005).

The replacement available is not a table hint, but a new query hint – FAST number_rows. This query hint specifies that the query is optimized for fast retrieval of the first number_rows. This is a nonnegative integer. After the first number_rows are returned, the query continues execution and produces its full result set. SQL Server 2012 has given more flexibility to the developer/administrator because the table hint only optimized for the first row, whereas the query hint allows you to specify a custom number of first rows to optimize for.

As a replacement of FASTFIRSTROW, you can use OPTION (FAST 1).

--Query for demonstration purposes only!
USE AdventureWorks2012
GO
--Without the table hint
SELECT *
FROM HumanResources.Employee 
GO
--With the table hint
SELECT *
FROM HumanResources.Employee
OPTION (FAST 1);
GO

References:

• Table hints supported by SQL Server 2008 R2: http://msdn.microsoft.com/en-us/library/ms187373(v=sql.105).aspx
  
• Query Hints: http://msdn.microsoft.com/en-us/library/ms181714.aspx
  
• As I was learning about this table hint, I came across the following post, which I thought might make interesting reading: http://sqlblog.com/blogs/kalen_delaney/archive/2008/12/02/using-a-nonclustered-index-to-avoid-a-sort.aspx


• http://blog.sqlauthority.com/2012/05/09/sql-server-quiz-and-video-introduction-to-basics-of-a-query-hint/

Until we meet next time,

Be courteous. Drive responsibly.

Every version of SQL Server comes with a lot of optimizations to the database engine, and these changes require changes to the T-SQL syntax. New statements are introduced or modified and existing ones are dropped. Often table and query hints are optimized and modified in a similar fashion. These table and query hints are often used (in many cases, indiscriminately) to “improve the performance” of Microsoft SQL Server.

Let me start outright by saying that till date, I have never encountered the need to use the hint that we are about to discuss today.

Table v/s Query hints

Before going any further, I thought it best to revisit the simple method of identifying which is a table hint and which one is a query hint. For example, take the query below:

~~~–Query for demonstration purposes only!
USE AdventureWorks2012
GO
SELECT *
FROM HumanResources.Employee WITH (NOLOCK) — WITH (NOLOCK) is the table hint
WHERE OrganizationLevel = 3
OPTION (MAXDOP 1) — OPTION (MAXDOP 1) is the query hint~~~

The WITH (NOLOCK) hint is applied to the table/CTE/sub-query, i.e. a data source. Hence the name – table hint.

The OPTION (MAXDOP 1) is applied to the entire query, and hence the name – query hint.

[Edit]: You may also find the following post from Pinal Dave, a.k.a. “SQLAuthority” (B|T), useful in understanding Query Hints: http://blog.sqlauthority.com/2012/05/09/sql-server-quiz-and-video-introduction-to-basics-of-a-query-hint/

FASTFIRSTROW

According to SQL Server Books On Line, the FASTFIRSTROW is a table hint that helps the developer control the behaviour of the optimizer such that the optimizer,

• Optimizes for overall minimum cost (minimum processing time/resources, etc), OR
  
• Optimizes such that the time taken for the first row to be returned is minimized

The FASTFIRSTROW tells the optimizer that it is important to have the first row returned as fast as possible, allowing the application to return results to the users faster. You can see the difference by comparing the actual execution plans produced by the queries below:

~~~–Query for demonstration purposes only!
USE AdventureWorks2008
GO
–Without the table hint
SELECT *
FROM HumanResources.Employee
GO
–With the table hint
SELECT *
FROM HumanResources.Employee WITH (FASTFIRSTROW)
GO~~~

OPTION (FAST n)

Running the above query in SQL Server 2012 results in the following error:

Msg 321, Level 15, State 1, Line 3
“FASTFIRSTROW” is not a recognized table hints option. If it is intended as a parameter to a table-valued function or to the CHANGETABLE function, ensure that your database compatibility mode is set to 90.

The message says it all – FASTFIRSTROW is no longer available, and if you would like to use it, the database compatibility level needs to be set to 90 (i.e.  SQL Server 2005).

The replacement available is not a table hint, but a new query hint – FAST number_rows. This query hint specifies that the query is optimized for fast retrieval of the first number_rows. This is a nonnegative integer. After the first number_rows are returned, the query continues execution and produces its full result set. SQL Server 2012 has given more flexibility to the developer/administrator because the table hint only optimized for the first row, whereas the query hint allows you to specify a custom number of first rows to optimize for.

As a replacement of FASTFIRSTROW, you can use OPTION (FAST 1).

~~~–Query for demonstration purposes only!
USE AdventureWorks2012
GO
–Without the table hint
SELECT *
FROM HumanResources.Employee
GO
–With the table hint
SELECT *
FROM HumanResources.Employee
OPTION (FAST 1);
GO~~~

References:

• Table hints supported by SQL Server 2008 R2: http://msdn.microsoft.com/en-us/library/ms187373(v=sql.105).aspx
  
• Query Hints: http://msdn.microsoft.com/en-us/library/ms181714.aspx
  
• As I was learning about this table hint, I came across the following post, which I thought might make interesting reading: http://sqlblog.com/blogs/kalen_delaney/archive/2008/12/02/using-a-nonclustered-index-to-avoid-a-sort.aspx


• http://blog.sqlauthority.com/2012/05/09/sql-server-quiz-and-video-introduction-to-basics-of-a-query-hint/

Until we meet next time,

Be courteous. Drive responsibly.

If you have been into database administration & development for a while now, what is the first thing that comes to your mind when someone asks to modify database options like setting the database read-only, or taking it offline? Answer: The sp_dboption system stored procedure.

The typical way to modify these database options would be:

--"Before" state
SELECT 'Before',is_read_only,state,state_desc,* FROM sys.databases db WHERE db.name = 'TestDB'
GO
--Set the database to READ-ONLY mode
sp_dboption 'TestDB','read only','TRUE'
GO
--"After" state
SELECT 'Post Read Only',is_read_only,state,state_desc,* FROM sys.databases db WHERE db.name = 'TestDB'
GO
--Set the database OFFLINE
sp_dboption 'TestDB','offline','TRUE'
GO
--"After" state
SELECT 'All done',is_read_only,state,state_desc,* FROM sys.databases db WHERE db.name = 'TestDB'
GO

SQL Server 2012 – Replacement – ALTER DATABASE…SET

However, starting SQL Server 2012, attempting to change the database options this way would produce the following results.

Msg 2812, Level 16, State 62, Line 1

Could not find stored procedure ‘sp_dboption’.

The system could not find the system stored procedure! That’s simply because it has been deprecated! The replacement is the ALTER DATABASE statement, and the existing sp_dboption calls need to be modified as demonstrated in the script below:

--"Before" state
SELECT 'Before',is_read_only,state,state_desc,* FROM sys.databases db WHERE db.name = 'TestDB'
GO
--Set the database to READ-ONLY mode
ALTER DATABASE TestDB SET READ_ONLY
GO
--"After" state
SELECT 'Post Read Only',is_read_only,state,state_desc,* FROM sys.databases db WHERE db.name = 'TestDB'
GO
--Set the database OFFLINE
ALTER DATABASE TestDB SET OFFLINE
GO
--"After" state
SELECT 'All done',is_read_only,state,state_desc,* FROM sys.databases db WHERE db.name = 'TestDB'
GO

The sp_dboption system stored procedure was marked as “discontinued” ever since SQL Server 2005. Yet, I have seen  a lot of production code this year itself that uses the sp_dboption statement. All of this code will break unless it is replaced with ALTER DATABASE…SET statement.

References:

• sp_dboption – http://msdn.microsoft.com/en-us/library/ms187310(v=sql.90).aspx
• ALTER DATABASE statement – http://msdn.microsoft.com/en-us/library/bb522682.aspx

Until we meet next time,

Be courteous. Drive responsibly.

If you have been into database administration & development for a while now, what is the first thing that comes to your mind when someone asks to modify database options like setting the database read-only, or taking it offline? Answer: The sp_dboption system stored procedure.

The typical way to modify these database options would be:

~~~–"Before" state
SELECT ‘Before’,is_read_only,state,state_desc,* FROM sys.databases db WHERE db.name = ‘TestDB’
GO
–Set the database to READ-ONLY mode
sp_dboption ‘TestDB’,’read only’,’TRUE’
GO
–"After" state
SELECT ‘Post Read Only’,is_read_only,state,state_desc,* FROM sys.databases db WHERE db.name = ‘TestDB’
GO
–Set the database OFFLINE
sp_dboption ‘TestDB’,’offline’,’TRUE’
GO
–"After" state
SELECT ‘All done’,is_read_only,state,state_desc,* FROM sys.databases db WHERE db.name = ‘TestDB’
GO~~~

SQL Server 2012 – Replacement – ALTER DATABASE…SET

However, starting SQL Server 2012, attempting to change the database options this way would produce the following results.

Msg 2812, Level 16, State 62, Line 1

Could not find stored procedure ‘sp_dboption’.

The system could not find the system stored procedure! That’s simply because it has been deprecated! The replacement is the ALTER DATABASE statement, and the existing sp_dboption calls need to be modified as demonstrated in the script below:

~~~–"Before" state
SELECT ‘Before’,is_read_only,state,state_desc,* FROM sys.databases db WHERE db.name = ‘TestDB’
GO
–Set the database to READ-ONLY mode
ALTER DATABASE TestDB SET READ_ONLY
GO
–"After" state
SELECT ‘Post Read Only’,is_read_only,state,state_desc,* FROM sys.databases db WHERE db.name = ‘TestDB’
GO
–Set the database OFFLINE
ALTER DATABASE TestDB SET OFFLINE
GO
–"After" state
SELECT ‘All done’,is_read_only,state,state_desc,* FROM sys.databases db WHERE db.name = ‘TestDB’
GO~~~

The sp_dboption system stored procedure was marked as “discontinued” ever since SQL Server 2005. Yet, I have seen  a lot of production code this year itself that uses the sp_dboption statement. All of this code will break unless it is replaced with ALTER DATABASE…SET statement.

References:

• sp_dboption – http://msdn.microsoft.com/en-us/library/ms187310(v=sql.90).aspx
• ALTER DATABASE statement – http://msdn.microsoft.com/en-us/library/bb522682.aspx

Until we meet next time,

Be courteous. Drive responsibly.

There would hardly be a database developer who hasn’t written triggers in their career. It is fairly common knowledge that you can have more than one trigger of the same type, i.e. INSERT/UPDATE/DELETE on the same table. Therefore, the following is perfectly valid:

~~~USE tempdb
GO
CREATE TABLE dbo.AppendTriggerTest (RowId INT IDENTITY(1,1),
RowValue VARCHAR(50)
)
GO

CREATE TRIGGER dbo.MainTrigger
ON dbo.AppendTriggerTest
FOR INSERT
AS
BEGIN
PRINT ‘<<<< INFO >>>> Main Trigger fired…’
END
GO

CREATE TRIGGER dbo.AppendTrigger
ON dbo.AppendTriggerTest
FOR INSERT
AS
BEGIN
PRINT ‘<<<< INFO >>>> Append Trigger fired…’
END
GO~~~

However, this was not the case in the days of SQL Server 6.5. In order to have a more than one trigger of the same type on the same table, the WITH APPEND clause needs to be used, which is effectively interpreted as AFTER. In the days of SQL Server 6.5, the trigger “AppendTrigger” will need to be defined as:

~~~CREATE TRIGGER dbo.AppendTrigger
ON dbo.AppendTriggerTest
FOR INSERT
WITH APPEND –NOTICE THE WITH APPEND CLAUSE HERE,
–DEPRECATED FROM SQL 2012 ONWARDS
AS
BEGIN
PRINT ‘<<<< INFO >>>> Append Trigger fired…’
END
GO~~~

Starting SQL Server 2012, the compatibility levels lower than 90 (i.e. SQL Server 2000 and below) are no longer supported. In accordance to this, the WITH APPEND clause has also been deprecated.

I assume that you are not using this in your production code, if you are, please upgrade! There is no workaround/alternate to this.

Reference:

• SQL 2000 Books-On-Line page for CREATE TRIGGER: http://msdn.microsoft.com/en-us/library/aa258254(v=sql.80).aspx

Until we meet next time,

Be courteous. Drive responsibly.