This method is to get scope identity from a table where both columns are auto increment

Create Table Testing (  
   id int identity,
   somedate datetime default getdate()
)
insert into Testing  
output inserted.*
default values

or

INSERT INTO Testing  SELECT NULL
select max(id) from Testing

Dynamic PIVOT in SQL Server 2005

The PIVOT operator available in SQL Server 2005 is used to generate the Cross-tab results
Consider this example

select * from
(
select Year(OrderDate) as pivot_col,e.lastname, o.OrderDate FROM northwind..Employees as e
INNER JOIN northwind..Orders as o ON (e.EmployeeID=o.EmployeeID)
) as t
pivot
(
Count(OrderDate) for pivot_col in ([1996],[1997])
) as p

which shows total orders of each employees for years 1996 and 1997

What if we want to have this for all the years available in the table
You need to use dynamic sql


--This procedure is used to generate Dynamic Pivot results
The approach is very similar to my Dynamic Crosstab with multiple PIVOT Columns blog post
which can be used in SQL Server 2000
create procedure dynamic_pivot
(
@select varchar(2000),
@PivotCol varchar(100),
@Summaries varchar(100)
) as

declare @pivot varchar(max), @sql varchar(max)
select @select =replace(@select,'select ','select '+@PivotCol+' as pivot_col,')


create table #pivot_columns (pivot_column varchar(100))

Select @sql='select distinct pivot_col from ('+@select+') as t'

insert into #pivot_columns
exec(@sql)

select @pivot=coalesce(@pivot+',','')+'['+pivot_column+']'from #pivot_columns

select @sql=
'
select * from
(
'+@select+'
) as t
pivot
(
'+@Summaries+' for pivot_col in ('+@pivot+')
) as p
'
exec(@sql)


Purpose : Find total sales made by each employee for each year(from Employees and Orders table from Northwind databases)
Usage :

EXEC dynamic_pivot
'SELECT e.lastname, o.OrderDate FROM northwind..Employees as e
INNER JOIN northwind..Orders as o ON (e.EmployeeID=o.EmployeeID) ',
'Year(OrderDate)',
'Count(OrderDate)'

Purpose : Find total sales made by each company for each product(from products, order details and suppliers table from Northwind database)
Usage :

EXEC dynamic_pivot
'SELECT s.companyname,coalesce(od.unitprice*od.quantity ,0) as total_cost FROM northwind..products as p
inner join northwind..[order details] as od on p.productid=od.productid
inner join northwind..suppliers as s on p.supplierid=s.supplierid',
'productname',
'sum(total_cost)'

This functionis used for finding a date for a particular year,month and n(th) week day

CREATE FUNCTION [dbo].[findDate]
(
@month SMALLINT, -- Month of Date
@year INT, -- Year of Date
@day SMALLINT, -- Day of Date
@weekday SMALLINT -- (n)th Week of Date
)
RETURNS DATETIME
AS
BEGIN

/*
@day variable can be :
1: Sunday
2: Monday
3: Tuesday
4: Wednesday
5: Thursday
6: Friday
7: Saturday
*/

DECLARE @start_date DATETIME, @end_date DATETIME, @retrunDate DATETIME, @total_days TINYINT,@counter TINYINT

SET @start_date = CAST(CAST(@year AS VARCHAR(4)) + '-' + CAST(@month AS VARCHAR(4)) + '-01' As SmallDateTime)

SET @total_days = DAY(DATEADD(d, -DAY(DATEADD(m,1,CAST(CAST(YEAR(@start_date) AS VARCHAR(4)) + '-'
+ (CAST(MONTH(@start_date)AS VARCHAR(6)) + '-01') AS SMALLDATETIME))),DATEADD(m,1,CAST(CAST(YEAR(@start_date) AS VARCHAR(4)) + '-'
+ cast(MONTH(@start_date) AS VARCHAR(5)) + '-01' AS SMALLDATETIME))))

SET @end_date = CAST(CAST(@year AS VARCHAR(4)) + '-' + CAST(@month AS VARCHAR(4)) + '-'+ CAST(@total_days AS VARCHAR(4)) As SmallDateTime)

SET @counter =1

WHILE @start_date <= @end_date
BEGIN

IF(DATEPART(dw , @start_date )= @day) AND @counter <= @weekday
BEGIN
--SELECT CAST(@start_date as VARCHAR(50)),DATEPART(dw , @start_date )

SET @retrunDate = @start_date
SET @counter = ISNULL(@counter,0)+1
END
-- SELECT @counter, @retDate
SET @start_date = DATEADD(DAY,1,@start_date)

END

--SELECT @retrunDate

RETURN @retrunDate

END

This is used to find all dates of a year mentioned along with (n)th week day

DECLARE @month int
DECLARE @year INT
DECLARE @weekday int
DECLARE @whichday int
DECLARE @dt datetime

SET @year=2012
SET @month=1
SET @weekday=5
SET @whichday=5


WHILE @month <= 12
BEGIN

SET @dt=CAST(@year AS CHAR(4))+'-'+CAST(@month AS CHAR(2))+'-01'

IF(MONTH(DATEADD(DAY,@weekday-DATEPART(DW,@dt),@dt))=@month)
BEGIN
IF(MONTH(DATEADD(WEEK,@whichday-1,DATEADD(DAY,@weekday-DATEPART(DW,@dt),@dt)))=@month)
SELECT DATEADD(WEEK,@whichday-1,DATEADD(DAY,@weekday-DATEPART(DW,@dt),@dt)),1
ELSE
SELECT DATEADD(WEEK,@whichday-2,DATEADD(DAY,@weekday-DATEPART(DW,@dt),@dt)),2
END
ELSE
BEGIN
IF(MONTH(DATEADD(WEEK,@whichday,DATEADD(DAY,@weekday-DATEPART(DW,@dt),@dt)))=@month)
SELECT DATEADD(WEEK,@whichday,DATEADD(DAY,@weekday-DATEPART(DW,@dt),@dt)),3
ELSE
SELECT DATEADD(WEEK,@whichday-1,DATEADD(DAY,@weekday-DATEPART(DW,@dt),@dt)),4
END

SET @month = @month + 1
END

Calculate no days in a particular month

DECLARE @today_date DATETIME
SET @today_date = '2009/02/01'

SELECT DAY(DATEADD(d, -DAY(DATEADD(m,1,CAST(CAST(YEAR(@today_date) AS VARCHAR(4)) + '-'
+ (CAST(MONTH(@today_date)AS VARCHAR(6)) + '-01') AS SMALLDATETIME))),DATEADD(m,1,CAST(CAST(YEAR(@today_date) AS VARCHAR(4)) + '-'
+ cast(MONTH(@today_date) AS VARCHAR(5)) + '-01' AS SMALLDATETIME))))

Use of CTE along with PIVOTING

;with cte
as
(
select cb.name as Branch, cee.entity_fk as entity, cee.id, cee.emp_code, cee.first_name +' '+ cee.last_name as Employee,
es.salary_component_fk as component, es.amount
from employee cee
inner join entity ee on ee.id = cee.entity_fk
inner join branch cb on cb.id = ee.branch_fk
inner join salary es on es.employee_fk= cee.id
where --cee.entity_fk = 3 and
cee.emp_code is not null
and cee.dol is null
and cee.active = 1
and es.active = 1
and es.amount > 0
)
select Branch,emp_code, Employee, Isnull([1],0) as [Basic],Isnull([2],0) as HRA,Isnull([3],0 ) as [TPL All],Isnull([4],0) as [SPL All],
(Isnull([1],0) + Isnull([2],0) + Isnull([3],0) + Isnull([4],0)) as Total

from (select Branch, entity, id, emp_code,Employee, component, amount from cte) AS vs
PIVOT (sum(amount) FOR component in([1], [2], [3], [4])) AS p

order by Branch, entity, CAST(SUBSTRING(emp_code,5,len(emp_code)) AS INT)

This is the way to insert OPENXML data into table without using view

INSERT into TableName
SELECT *
FROM OPENXML(@hdoc, @nodeName)
WITH
(
id INT '@id',
component_fk int '@component_fk',
amount float '@amount',
active bit '@active'
)

XML inSQL Query

We can use New way of selecting Nodes from XMl by using following:
Declare @xmlData xml
SET @xmlData='';
Declare @temp as table (id int, Name varchar(50))
Insert into @temp
SELECT
T.item.value('@id', 'int') as [id],
T.item.value('@first_name', 'varchar(50)') as [Name]
FROM @xmlData.nodes('/employee/user') AS T(item)
where T.item.value('@id', 'int')=57
So there is no need of using OPENXML() method.

OpenXML and xml Nodes

DECLARE @xmlData XMLIF (@xmldata.exist('//sd/member')=1)BEGIN
DECLARE @hdoc int Declare @nodeName as varchar(100)
EXEC sp_xml_preparedocument @hdoc OUTPUT, @xmldata Set @nodeName = '//sd/member'-- Declare a temp table and insert from open xml having auto increment ID column-- This ID column is used to avoid cursor--INSERT into @tempSponsorNew SELECT * FROM OPENXML( @hdoc, @nodeName, 8 ) WITH ( id INT '@id' ,[sp_fk] INT '@sponsor_fk' ,[name] varchar(100) '@name')
EXEC sp_xml_removedocument @hDoc
DECLARE @sponserCounter INT
DECLARE @totalRowSponser INT -- avoid using cursorSET @sponserCounter=0 SELECT @totalRowSponser=count(1) FROM @tempSponsorNew While(@sponserCounter < @totalRowSponser) BEGIN SET @sponserCounter=@sponserCounter + 1 -- Do what ever you want to do with this temp table dataselect *FROM @tempSponsorNew ts WHERE ts.id < 0 and ts.active=1 and ts.[primary_id]=@sponserCounter END
END

rebuild all index of all database

DECLARE @Database VARCHAR(255) DECLARE @Table VARCHAR(255) DECLARE @cmd NVARCHAR(500) DECLARE @fillfactor INTSET @fillfactor = 90DECLARE DatabaseCursor CURSOR FOR SELECT name FROM master.dbo.sysdatabases WHERE name NOT IN ('master','model','msdb','tempdb','distrbution') ORDER BY 1 OPEN DatabaseCursor FETCH NEXT FROM DatabaseCursor INTO @Database WHILE @@FETCH_STATUS = 0 BEGIN SET @cmd = 'DECLARE TableCursor CURSOR FOR SELECT table_catalog + ''.'' + table_schema + ''.'' + table_name as tableName FROM ' + @Database + '.INFORMATION_SCHEMA.TABLES WHERE table_type = ''BASE TABLE''' -- create table cursor EXEC (@cmd) OPEN TableCursor FETCH NEXT FROM TableCursor INTO @Table WHILE @@FETCH_STATUS = 0 BEGIN -- SQL 2000 command --DBCC DBREINDEX(@Table,' ',@fillfactor) -- SQL 2005 command SET @cmd = 'ALTER INDEX ALL ON ' + @Table + ' REBUILD WITH (FILLFACTOR = ' + CONVERT(VARCHAR(3),@fillfactor) + ')' EXEC (@cmd) FETCH NEXT FROM TableCursor INTO @Table END CLOSE TableCursor DEALLOCATE TableCursor FETCH NEXT FROM DatabaseCursor INTO @Database END CLOSE DatabaseCursor DEALLOCATE DatabaseCursor