Monday, February 08, 2010

T-SQL to learn

xml data type,
xml variable.nodes
xml element.value
select from xmlv.nodes

set showplan_xml on

for xml path(), type
severity level
alter table switch
create index where
outer apply
permission set on assembly

with xmltable
generate xml
openxml
raiseerror with no wait
select fast 50
dm_db_missing_index_details
msdb.sysmail_faileditems

Passing parameter to sql script via isql

An example:
ECHO OFF
isql /S dbserver /U user /P password /d db /o output.txt /s , /w8000 /Q "EXIT(exec sp1 '%1','%2')"

Tuesday, February 02, 2010

T-SQL Group Sets

This is generic way to provide union of the aggregates based on the columns specified in each set in the Grouping set. you can write corresponding statements to replace the specific CUBE and ROLLUP funtion.

You can use Group Sets to understand how CUBE and ROLLUP work.

CUBE Equivalent:

SELECT c1, c2,avg(c3)
from tbl
Group BY
CUBE (c1, c2)

Equals to:
SELECT c1, c2, avg(c3)
from tbl
GROUPING SETS
(
(c1, c2),
(c1),
(c2) ,
()
)

ROLLUP Equivalent:
SELECT c1, c2, avg(c3)
from tbl
Group BY
ROLLUP (c1, c2)

Equals to:
SELECT c1,c2, avg(c3)
from tbl
Group BY
Grouping Sets
(
(C1, C2),
(c1),
()
)