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

Import data from SQL Server to Excel using VBA

Excel is a great tools for reporting, many users prefer data can be provided in Excel format, so that they can further manipulate it. Here is an simple example demonstrating how to import the result set of a SQL Server stored procedure into Excel using VBA code.

Sub DataExtract()
    ' Create a connection object.
    Dim conn As ADODB.Connection
    Set conn = New ADODB.Connection

    ' Provide the connection string.
    Dim strConn As String

    ' Use the SQL Server OLE DB Provider.
    strConn = "PROVIDER=SQLOLEDB;"
    ' Connect to the database.
    strConn = strConn & "DATA SOURCE=YourSqlServer;INITIAL CATALOG=YourDB;"

    ' Open the connection.
    conn.Open strConn, "YourSqlAccount", "YourSqlPassword"

    ' Create a recordset object.
    Dim rs As ADODB.Recordset
    Set rs = New ADODB.Recordset

    With rs
        ' Assign the Connection object.
        .ActiveConnection = conn
        ' Extract the required records by a select statement or execute a stored proc.
        .Open "EXEC YourStoredProc;"
        ' Print field headers on 1st row
        For i = 1 To .Fields.Count
            Cells(1, i).Value = .Fields(i - 1).Name
        Next i
        ' Copy the records into cell A2 on Sheet1.
        Worksheets("Sheet1").Range("A2").CopyFromRecordset rs
        ' Tidy up
        .Close
    End With

    conn.Close
    Set rs = Nothing
    Set conn = Nothing
End Sub

T-SQL to Check Running Sessions and Statements

Below SQL script can be used to query the current running sessions and their executing statements. It also shows whether it's a parallel execution (multi-thread), which login account, whether it's being blocked and its blocker, what resource it's waiting for, the CPU and I/O usage, etc.

SELECT CASE WHEN (SELECT COUNT(*) FROM sysprocesses WHERE spid = r.spid) > 1 THEN 'Multithread' ELSE '' END AS Multithread, LEFT(t.[text], 255) AS [text255], SUBSTRING( t.[text], (r.stmt_start / 2) + 1, (( CASE r.stmt_end WHEN -1 THEN DATALENGTH(t.[text]) ELSE r.stmt_end END - r.stmt_start) / 2) + 1) AS stmt, DB_NAME(t.dbid) AS ObjectDB, OBJECT_NAME(t.objectid, t.dbid) AS Object, r.spid, r.ecid, r.blocked, r.waittime, r.lastwaittype, r.waitresource, DB_NAME(r.dbid) AS connectDB, r.cpu, r.physical_io, r.memusage, r.login_time, r.last_batch, r.open_tran, r.status, r.hostname, r.program_name, r.loginame, r.cmd, r.net_library, r.login_time, r.stmt_start, r.stmt_end FROM sys.sysprocesses AS r CROSS APPLY sys.dm_exec_sql_text(r.[sql_handle]) AS t WHERE r.status IN ('runnable', 'suspended', 'running', 'rollback', 'pending', 'spinloop') ORDER BY spid, ecid

Below screen illustrates the query result (split to two rows)"