CTE (SQL SERVER)

Specifies a temporary named result set, known as a common table expression (CTE). This is derived from a simple query and defined within the execution scope of a single SELECT, INSERT, UPDATE, or DELETE statement. 
This clause can also be used in a CREATE VIEW statement as part of its defining SELECT statement.
A common table expression can include references to itself. This is referred to as a recursive common table expression.





Examples:

1. Creating a simple common table expression

-- Define the CTE expression name and column list.

WITH Person_CTE (PersonID, PersonName, PersonSalary)

AS
-- Define the CTE query.
(
Select 
 PersonID, PersonName, PersonSalary
FROM 
 Person
)

-- Define the outer query referencing the CTE name.

SELECT

 * 
FROM 
 Person_CTE
ORDER BY 
 PersonID;

2. Using a recursive common table expression to display multiple levels of recursion

WITH Emp(superiorid, id, username) AS

(
SELECT superiorid, id, username
FROM User 
WHERE superiorid IS NULL

UNION ALL

SELECT e.superiorid, e.id, e.username
FROM User AS e
INNER JOIN IS_User AS e1
ON e.superiorid = e1.id 
)

SELECT superiorid, id , username

FROM Emp
ORDER BY superiorid;

Transaction SQL Server

BEGIN TRANSACTION

BEGIN TRANSACTION represents a point at which the data referenced by a connection is logically and physically consistent. If errors are encountered, all data modifications made after the BEGIN TRANSACTION can be rolled back to return the data to this known state of consistency. Each transaction lasts until either it completes without errors and COMMIT TRANSACTION is issued to make the modifications a permanent part of the database, or errors are encountered and all modifications are erased with a ROLLBACK TRANSACTION statement.

ROLLBACK TRANSACTION

Rolls back an explicit or implicit transaction to the beginning of the transaction, or to a savepoint inside the transaction. You can use ROLLBACK TRANSACTION to erase all data modifications made from the start of the transaction or to a savepoint. It also frees resources held by the transaction.

COMMIT TRANSACTION 

Marks the end of a successful implicit or explicit transaction. If @@TRANCOUNT is 1, COMMIT TRANSACTION makes all data modifications performed since the start of the transaction a permanent part of the database, frees the resources held by the transaction, and decrements @@TRANCOUNT to 0. If @@TRANCOUNT is greater than 1, COMMIT TRANSACTION decrements @@TRANCOUNT only by 1 and the transaction stays active. 

Sample:

begin transaction

update table1
set field1='a'

if @@ERROR <> 0
begin
 rollback transaction
 RAISERROR ('Error table1',16,1)
 return -1
end


update table2
set field3='a'

if @@ERROR <> 0
begin
rollback transaction
RAISERROR ('Error table2',16,1)
return -1
end

commit transaction

sysobjects table SQL Server

sysobjects table SQL Server

Contains one row for each database object (constraint, trigger , stored procedure, and so on).

Important coumns:

name	sysname	Object name.
Id	int	Object identification number.
uid	smallint	User ID of owner object.
type	char(2)	Object type: C = CHECK constraint D = Default or DEFAULT constraint F = FOREIGN KEY constraint FN = Scalar function IF = Inline table-function K = PRIMARY KEY or UNIQUE constraint L = Log P = Stored procedure R = Rule RF = Replication filter stored procedure S = System table TF = Table function TR = Trigger U = User table V = View X = Extended stored procedure

Example:

select name,id,uid, type from sysobjects where name ='trg_asset_after_update'

name                             id                  uid type
trg_asset_after_update 1719155553 1    TR

FOR XML clause (SQL Server)

You can retrieve results of a SQL query as XML by specifying the FOR XML clause in the query.

create table customer

(
       id                  int,
       name         nvarchar(max)
)
go
create table [order]
(
       id                  int,
       Date         date,
       amount       int,
       customer_id  int
)
set nocount on
go

insert into 
       customer (id, name)
values
       (1,'Customer 1')
insert into 
       customer (id, name)
values
       (2,'Customer 2')
insert into 
        [order] (id, Date, amount, customer_id)
values
       (1,GETDATE(),100, 1)
insert into 
       [order] (id, Date, amount, customer_id)
values
       (2,GETDATE(),100, 1)
insert into 
       [order] (id, Date, amount, customer_id)
values
       (3,GETDATE(),200, 1)
insert into 
       [order] (id, Date, amount, customer_id)
values
       (4,GETDATE(),300, 2)

--query returns results as a rowset

select

       customer.id,
       customer.name,
       ord.id order_id,
       ord.Date,
       ord.amount     
from 
       customer 
       join [order] ord on customer.id = ord.customer_id 

Result:

id            name                  order_id  Date                    amount

1             Customer 1        1               2013-03-04         100
1             Customer 1        2               2013-03-04         100
1             Customer 1        3               2013-03-04         200
2             Customer 2        4               2013-03-04         300
                                                   


--query returns results as XML 

AUTO mode returns query results as nested XML elements

select

       customer.id,
       customer.name,
       ord.id order_id,
       ord.Date,
       ord.amount     
from 
       customer 
       join [order] ord on customer.id = ord.customer_id 
FOR XML auto

Result:

<customer id="1" name="Customer 1">

  <ord order_id="1" Date="2013-03-04" amount="100" />
  <ord order_id="2" Date="2013-03-04" amount="100" />
  <ord order_id="3" Date="2013-03-04" amount="200" />
</customer>
<customer id="2" name="Customer 2">
  <ord order_id="4" Date="2013-03-04" amount="300" />
</customer>

 

RAW mode transforms each row in the query result set into an XML element that has the generic identifier , or the optionally provided element name.

select

       customer.id,
       customer.name,
       ord.id order_id,
       ord.Date,
       ord.amount     
from 
       customer 
       join [order] ord on customer.id = ord.customer_id 
FOR XML raw

Result:

<row id="1" name="Customer 1" order_id="1" Date="2013-03-04" amount="100" />

<row id="1" name="Customer 1" order_id="2" Date="2013-03-04" amount="100" />
<row id="1" name="Customer 1" order_id="3" Date="2013-03-04" amount="200" />
<row id="2" name="Customer 2" order_id="4" Date="2013-03-04" amount="300" />

PATH mode provides a simpler way to mix elements and attributes.

select

       customer.id,
       customer.name,
       ord.id order_id,
       ord.Date,
       ord.amount     
from 
       customer 
       join [order] ord on customer.id = ord.customer_id 
FOR XML path

 Result:

<row>
  <id>1</id>
  <name>Customer 1</name>
  <order_id>1</order_id>
  <Date>2013-03-04</Date>
  <amount>100</amount>
</row>
<row>
  <id>1</id>
  <name>Customer 1</name>
  <order_id>2</order_id>
  <Date>2013-03-04</Date>
  <amount>100</amount>
</row>
<row>
  <id>1</id>
  <name>Customer 1</name>
  <order_id>3</order_id>
  <Date>2013-03-04</Date>
  <amount>200</amount>
</row>
<row>
  <id>2</id>
  <name>Customer 2</name>
  <order_id>4</order_id>
  <Date>2013-03-04</Date>
  <amount>300</amount>
</row>

drop table [order]

drop table customer

 

Dynamic PIVOT and cross-tab query (SQL Server)

SQL Server > Operators > PIVOT > Dynamic PIVOT and cross-tab query



Sometimes we don't know values in FOR clause for pivot.
In this example we will build dynamic columns with values and use dynamic query to build pivot and cross-tab query.

Example:

create table pivot_data

(
       id           int,
       Date   date,
       amount int
)
set nocount on
go

insert into 
       pivot_data (id, Date, amount)
values
       (1,GETDATE(),100)
insert into 
       pivot_data (id, Date, amount)
values
       (1,dateadd(day,1, GETDATE()),200)
insert into 
       pivot_data (id, Date, amount)
values
       (2,dateadd(month,1, GETDATE()),300)
insert into 
       pivot_data (id, Date, amount)
values
       (2,dateadd(month,2, GETDATE()),400)     

select * from pivot_data

Result:

id            Date                    amount

1             2013-03-04         100
1             2013-03-05         200
2             2013-04-04         300
2             2013-05-04         400

DECLARE

    @cols AS NVARCHAR(MAX),

    @query  AS NVARCHAR(MAX);
SET @cols = STUFF((SELECT  ',' + QUOTENAME(c.Date )
            FROM pivot_data c
            order by Date
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)')
        ,1,1,'')
print @cols

set @query = 'SELECT id, ' + @cols + ' from

            (
                select 
                    id
                    , date
                    , amount
                from 
                    pivot_data
           ) x
            pivot
            (
                sum(amount)
                for  date in (' + @cols + ')
            ) p '

execute(@query)

Result:

id 2013-03-04    2013-03-05        2013-04-04        2013-05-04

1 100                 200                     NULL                 NULL
2 NULL              NULL                 300                     400

drop table pivot_data