SQL IDENTITY_INSERT

SQL IDENTITY_INSERT is allow values to be inserted into the identity column of a table.
Identity column creates a numeric sequence value for you automatic.

It only allow one table in a session can have the IDENTITY_INSERT property set to ON.
If you need to change the table, you need to set the existing table IDENTITY_INSERT back to OFF.

SET IDENTITY_INSERT TableName OFF

If the value inserted is larger than the current identity value for the table, SQL Server automatically uses the new inserted value as the current identity value.

SET IDENTITY_INSERT TableName ON

http://sqltutorials.blogspot.co.nz/2008/04/sql-identityinsert.html

Pivot table definition, explaination, How to do a dynamic columned pivot table?

Pivot table does two things:
1. Aggregate on the row fields' values
2. Subcategorizing on the (pivot) column values

Three key elements:
1. row field
2. column field
3. value field

The reason the Pivot table got its name is because you take some or all the values from the column field and rotate those values to become actually columns in the pivot table.

--How to do a dynamic columned pivot table:
--The following query will run on the AdventureWorks DB of the sql 2008R2 sample Database.

DECLARE @cols nvarchar(4000);

WITH CustomerIDList
AS
(
SELECT DISTINCT TOP 100
CustomerID
FROM
Sales.SalesOrderHeader
ORDER BY
CustomerID
)

SELECT @cols = STUFF(
(SELECT
'],[' + CAST(CustomerID AS varchar)
FROM
CustomerIDList
ORDER BY
CustomerID
FOR XML PATH('')), 1, 2, '') + ']'

Print(@cols)


DECLARE @query NVARCHAR(4000)
SET @query = N'SELECT OrderYear, '+
@cols +'
FROM
(SELECT CustomerID
, DATEPART(yyyy, OrderDate) as OrderYear
, TotalDue
FROM
Sales.SalesOrderHeader
) AS orgn
PIVOT
(
MAX([TotalDue])
FOR CustomerID IN
( '+
@cols +' )
) AS pvt
'

EXECUTE(@query)

Sql tracing profiler event explained

RPC: Completed
The RPC: Completed event fires after a stored procedure is executed as a remote procedure call. It includes useful information about the execution of the stored procedure, including the CPU time used to execute the stored procedure, the total length of time the stored procedure ran, logical reads and writes that occurred during its execution, along with the name of the stored procedure itself.

SP: StmtCompleted
Stored procedures are made up of one or more statements. In SQL Server 2005, each statement within a stored procedure is traced. The SP: StmtCompleted event indicates when a statement within a stored procedure has ended. The StmtCompleted event’s data columns provide lots of useful information about the statement, including the actual code in the statement, the duration the statement ran, the amount of CPU time used, the number of logical reads and writes, the number of rows returned by the statement, among others.

SQL: BatchStarting
The SQL: BatchStarting event is fired whenever a new batch begins. Once a batch begins, then one or more individual Transact-SQL statements occur. The SQL: BatchStarting event is a good event to easily see where a batch begins, but other than this, it is not particularly useful.

SQL: BatchCompleted
The SQL: BatchCompleted event occurs when a batch completes. This means that one or more Transact-SQL statements have completed for the batch. The SQL: BatchCompleted event is more useful than the SQL: BatchStarting event because it includes useful information like the duration of the entire batch, the logical number of reads and writes caused by all the statements inside the batch, the total number of rows returned by the batch, and other useful information.

Deadlock Graph
Of seven events I have listed above, the only event you must have is the Deadlock Graph event. It captures, in both XML format and graphically, a drawing that shows you exactly the cause of the deadlock. We will examine how to interpret this drawing later in this article.

Lock:Deadlock
This event is fired whenever a deadlock occurs, and because of this, it is also fired every time the Deadlock Graph event is fired, producing redundant data. I have included it here because it makes it a little easier to see what is happening, but if you like, you can drop this event from your trace.

Lock:Deadlock Chain
This event is fired once for every process involved in a deadlock. In most cases, a deadlock only affects two processes at a time, and because of this, you will see this event fired twice just before the Deadlock Graph and the Lock:Deadlock events fire. In rare cases, more than two processes are involved in a deadlock, and if this is the case, an event will be fired for every process involved in the deadlock.

temp table, table variable, CTE (Common Table Expression)

Rule of thumb: If logic is simple CTE, otherwise Table Variable

CTE (Common Table Expression):
* May have performance issues
* Like a sub-query
* Save you some type and make the field type consistent with the original table
* Saved in memory

Table Variable:
* Performance better
* Saved in database

Temp Table:
* Do you best to not use it

http://databases.aspfaq.com/database/should-i-use-a-temp-table-or-a-table-variable.html

Temp Table

CREATE TABLE #MyTable
(
Id int,
Name varchar(32)
)

DROP TABLE #MyTable

alternative syntax
select * into #MyTable from contacts
drop table #MyTable
-----------------------

Table Variable

DECLARE @MyTable TABLE
(
Id int,
Name varchar(32)
)

----------------------

CTE (Common Table Expression)
WITH MyTable (Id, Name)
AS
(
SELECT Id, Name
FROM User
)