New THROW statement in SQL Server 2012 (vs RAISERROR)

The new THROW keyword introduced in SQL server 2012 is an improvement over the existing RAISERROR() statement.
Both RAISERROR & THROW can be used in T-SQL code/script to raise and throw error within a TRY-CATCH block.
With RAISERROR developers had to use different ERROR_XXX() system functions to get the error details to pass through the RAISERROR() statement, like:
- ERROR_NUMBER()
- ERROR_MESSAGE()
- ERROR_SEVERITY()
- ERROR_STATE()

let’s see an example:

-- Using RAISERROR()
DECLARE
@ERR_MSG AS NVARCHAR(4000)
,@ERR_SEV AS SMALLINT
,@ERR_STA AS SMALLINT
BEGIN TRY
SELECT 1/0 as DivideByZero
END TRY
BEGIN CATCH
SELECT @ERR_MSG = ERROR_MESSAGE(),
@ERR_SEV =ERROR_SEVERITY(),
@ERR_STA = ERROR_STATE()
SET @ERR_MSG= 'Error occurred while retrieving the data from database: ' + @ERR_MSG
RAISERROR (@ERR_MSG, @ERR_SEV, @ERR_STA) WITH NOWAIT
END CATCH
GO

Output:
(0 row(s) affected)
Msg 50000, Level 16, State 1, Line 15
Error occurred while retrieving the data from database: Divide by zero error encountered.

The RAISERROR() can take first argument as message_id also instead of the message. But if you want to pass the message_id then it has to be in sys.messages

With THROW the benefit is: it is not mandatory to pass any parameter to raise an exception. Just using the THROW; statement will get the error details and raise it, as shown below:

-- Using THROW - 1
BEGIN TRY
SELECT 1/0 as DivideByZero
END TRY
BEGIN CATCH
THROW;
END CATCH
GO

Output:
(0 row(s) affected)
Msg 8134, Level 16, State 1, Line 2
Divide by zero error encountered.

As you see in the Output above, the error message thrown is the default one. But you can also add your customized message, we will see below.
IMP NOTE: Default THROW statement will show the exact line where the exception was occurred, here the line number is 2. But RAISERROR() will show the line number where the RAISERROR statement was executed i.e. Line 15 and not the actual exception.
Also passing the message_id won’t require it to be stored in sys.messages, let’s check this:

-- Using THROW - 2
DECLARE
@ERR_MSG AS NVARCHAR(4000)
,@ERR_STA AS SMALLINT
BEGIN TRY
SELECT 1/0 as DivideByZero
END TRY
BEGIN CATCH
SELECT @ERR_MSG = ERROR_MESSAGE(),
@ERR_STA = ERROR_STATE()
SET @ERR_MSG= 'Error occurred while retrieving the data from database: ' + @ERR_MSG;
THROW 50001, @ERR_MSG, @ERR_STA;
END CATCH
GO

Output:
(0 row(s) affected)
Msg 50001, Level 16, State 1, Line 14
Error occurred while retrieving the data from database: Divide by zero error encountered.
But if you parameterize the THROW statement as above it will not show the actual position of exception occurrence, and the behavior will be same as RAISERROR(). As with RAISERROR() you've to provide mandatory params, so there is no way to get the actual position of Line where the error occurred.

Update View Definition After Underlying Table Changed

One of the tasks that a SQL Server DBA/developer mostly miss out is to update the definition of all the dependent views after an underlying table changed its schema (add/drop/modify a column). Unlike the recompilation of stored procedures, SQL Server will NOT automatically refresh the definition of views in case of table schema change, you must do it manually by running the sp_refreshview system stored procedure.
But why it's easily missed out by so many DBA? Because 1) no error will be raised when you alter the table (except you have SCHEMABINDING view); 2) if the SQL developer follows a good coding practice - avoid using "SELECT * FROM...", when writing any SQL objects, including views, then even the view definition is outdated, mostly the view performs exactly the same as before (I just can say mostly, because SQL Server keeps changed its internal mechanism, and there are too much variations of your coding in the real world).
Below is an example shows why you need to run sp_refreshview explicitly:
-- Create sample table for SQL view object
Create Table ViewTable (
id int identity(1,1),
viewname sysname,
description nvarchar(max),
active bit
)
go
-- Insert sample data into SQL table
insert into ViewTable select 'SQLView1','Sample SQL View',1
insert into ViewTable select 'SQLView2','Example T-SQL View',1
go
-- Create Transact-SQL View in SQL Server
Create View ViewTableList
as
select * from ViewTable
go

SELECT * FROM ViewTable -- read data from database table
SELECT * FROM ViewTableList -- read data from SQL view object

Alter Table ViewTable Add CreateDate datetime
go
SELECT * FROM ViewTable -- Select data directly from database table
SELECT * FROM ViewTableList -- Select data from non-updated view

Execute sp_refreshview 'ViewTableList' -- refresh view definition in SQL Server

SELECT * FROM ViewTable -- Select data directly from database table
SELECT * FROM ViewTableList -- Select data from updated view

Anyway, do it manually can prevent any uncertainties. Keep this step as one of your DBA practices.

SQL Express does NOT have Agent service

Even there's an "SQL Server Agent (SQLEXPRESS)" service inside the "SQL Server Services" list of Sql Server Configuration Manager, you can never be started it! All EXPRESS editions do NOT have Agent service. Refers to the "Features Supported by the Editions of SQL Server 2012".
http://msdn.microsoft.com/en-us/library/cc645993.aspx#Mgmt_tools

SQL 2012 TRY_CONVERT

There're new built-in functions in SQL2012 which can be used to trying convert/cast/parse a value into specified type, and if the conversion can't be done, it won't raise any error but return a NULL value. I found it very handy for parsing XML into SQL data. The example below demonstrate how to use TRY_CONVERT.

SELECT TRY_CONVERT(date, dbo.ufnBlankToNull(T.c.value('./@tradedate', 'varchar(50)')), 120),
TRY_CONVERT(float, dbo.ufnBlankToNull(T.c.value('./@PX_OPEN', 'varchar(50)')))
FROM @x.nodes('/PriceHistory/instrument') AS I(c)
CROSS APPLY I.c.nodes('./trade') AS T(c)

As the TRY_CONVERT function will convert blank string to 0 for numeric type and '1900-01-01' for date type, which isn't what I want. So I create a custom scalar function as below to convert blank string to NULL before input the value into TRY_CONVERT.
CREATE FUNCTION ufnBlankToNull
(
    @s nvarchar(max)
)
RETURNS nvarchar(max)
AS
BEGIN
    RETURN LTRIM(RTRIM(NULLIF(@s, '')))
END

Get user options of current session

This command gets the user options, including isolation level, language, dateformat, etc., for the current session:
DBCC USEROPTIONS WITH NO_INFOMSGS

Checking the conflict row in Transactional Replication

Data conflict of a single row among the publication database and subscription database will lead to the whole transactional replication being suspended. The replication monitor sometimes only return the xact_seqno (transaction sequence number) and the command_id of the conflicted data row. Below stored procedure helps you to check which row is in conflict:

USE msdb
GO
CREATE PROCEDURE viewReplicationErrorCommand
@transaction_sequence_number nchar(22),
@command_id int
AS

CREATE TABLE #tmpReplErrCmd (
xact_seqno varbinary(16) NULL,
originator_srvname sysname NULL,
originator_db sysname NULL,
article_id int NULL,
[type] int NULL,
partial_command bit NULL,
hashkey int NULL,
originator_publication_id int NULL,
originator_db_version int NULL,
originator_lsn varbinary(16) NULL,
command nvarchar(1024) NULL,
command_id int NULL
)

INSERT #tmpReplErrCmd EXEC distribution.dbo.sp_browsereplcmds
@xact_seqno_start = @transaction_sequence_number,
@xact_seqno_end = @transaction_sequence_number

SELECT * FROM #tmpReplErrCmd WHERE command_id = @command_id

DROP TABLE #tmpReplErrCmd

GO

How to check your tables or indexes are in buffer cache

In order to reduce disk I/O and improve performance, add more RAM to your SQL Server machine is the most intuitive idea. However, how can you know your SQL queries get benefit from more RAM? Below SQL query helps you to check the content of the SQL Server buffer cache, it shows you the top 50 objects (tables and indexes) there, by the number of data pages, of the current database.

;WITH memusage_CTE AS (SELECT bd.database_id, bd.file_id, bd.page_id, bd.page_type
, COALESCE(p1.object_id, p2.object_id) AS object_id
, COALESCE(p1.index_id, p2.index_id) AS index_id
, bd.row_count, bd.free_space_in_bytes, CONVERT(TINYINT,bd.is_modified) AS 'DirtyPage'
FROM sys.dm_os_buffer_descriptors AS bd
JOIN sys.allocation_units AS au
ON au.allocation_unit_id = bd.allocation_unit_id
OUTER APPLY (
SELECT TOP(1) p.object_id, p.index_id
FROM sys.partitions AS p
WHERE p.hobt_id = au.container_id AND au.type IN (1, 3)
) AS p1
OUTER APPLY (
SELECT TOP(1) p.object_id, p.index_id
FROM sys.partitions AS p
WHERE p.partition_id = au.container_id AND au.type = 2
) AS p2
WHERE bd.database_id = DB_ID() AND
bd.page_type IN ('DATA_PAGE', 'INDEX_PAGE','TEXT_MIX_PAGE') )
SELECT TOP 50 DB_NAME(database_id) AS 'Database',OBJECT_NAME(object_id,database_id) AS 'Table Name', index_id,COUNT(*) AS 'Pages in Cache', SUM(dirtyPage) AS 'Dirty Pages'
FROM memusage_CTE
GROUP BY database_id, object_id, index_id
ORDER BY COUNT(*) DESC