-----------------------------------------------------------------------------------
-- SQL Server 2008 Enhancements
-----------------------------------------------------------------------------------
/*
Syntax Enhancements
Intialize variables inline as part of variable declaration statement
Instead of declare and set statements separately
we can combine both into one statement in declare.
*/
use tempdb
go
if OBJECT_ID('dbo.portposition','U') is not null
drop table dbo.portposition;
go
create table dbo.portposition
(ason date,snam varchar(7),cusip varchar(13),marketvalue numeric (10,2)
primary key (ason,snam,cusip)
)
insert into dbo.portposition (ason,snam,cusip,marketvalue)
values ('01/31/2009','ax0001','acvbfghi',100.25),
('02/28/2009','ax0001','acvbfghi',200.50),
('03/31/2009','ax0001','acvbfghi',300.75),
('01/31/2009','ay0001','acvbfghi',100.25),
('02/28/2009','ay0001','acvbfghi',200.25),
('01/31/2009','az0001','acvbfghi',100.25);
declare @ason datetime ='12/31/2008' ,@snam varchar(7)='AA1YZY'
select top 100 * from port_position
where ason = @ason and SNAM = @snam
------------------------------------------------------------------------
/*
Compound Operators:
Compound assignment operators are same as
in C++ and C#; they execute some operation
and set an original value to the result of the operation.
They help in writing cleaner and abbreviate code.
(It also works in the SET clause of an UPDATE statement).
Supported compound operators in SQL Server 2008.
This is applicables to columns ,variables whereever assignment is normally used
+= Add and assign
-= Subtract and assign
*= Multiply and assign
/= Divide and assign
%= Modulo and assign
&= Bitwise AND and assign
^= Bitwise XOR and assign
= Bitwise OR and assign
*/
declare @quantity numeric(10,4) = 10.2584,
@cost numeric(10,2) = 200.80
declare @marketvalue numeric(10,2) = @quantity * @cost,
@total@marketvalue numeric(10,2) = 100
set @total@marketvalue += @marketvalue
select @quantity as quantity,
@cost as cost,
@marketvalue as marketvalue,
@total@marketvalue as total@marketvalue
go
declare @quantity numeric(10,4) = 10.2584,
@cost numeric(10,2) = 200.80
declare @marketvalue numeric(10,2) = @quantity * @cost,
@total@marketvalue numeric(10,2) = 100
set @total@marketvalue *= @marketvalue
select @quantity as quantity,
@cost as cost,
@marketvalue as marketvalue,
@total@marketvalue as total@marketvalue
go
declare @quantity numeric(10,4) = 10.2584,
@cost numeric(10,2) = 200.80
declare @marketvalue numeric(10,2) = @quantity * @cost,
@total@marketvalue numeric(10,2) = 100
set @total@marketvalue =+ @marketvalue
select @quantity as quantity,
@cost as cost,
@marketvalue as marketvalue,
@total@marketvalue as total@marketvalue
go
------------------------------------------------------------------------
/*
Table value constructor through VALUES clause
Single VALUES clause can be used to construct a set of rows.
This allows multiple rows to be inserted based on single
*/
use tempdb
go
if OBJECT_ID('dbo.country','U') is not null
drop table dbo.country;
go
create table dbo.country
( countryid int identity(1,1) primary key, country varchar(80) not null);
insert into dbo.country(country)
values
( 'India'),
('USA'),
('Russia'),
('France');
select * from dbo.country;
insert into dbo.country(country)
values
( 'Newzeland'),
( 'Switzerland'),
( 'Mozambique'),
( 'Ethiopia'),
(null);
select * from dbo.country;
/*
INSERT statement runs in atomic transaction , failing insert on one row.
entire insert fails
*/
/*
VALUES can also be used for derived tables, CTES.
This will avoid using unions .
This can be used as any table expression in joins where, group by etc
*/
-- VALUES clause as a derived table
select * from
(VALUES
( 'India'),
('USA'),
('Russia'),
('France')
) AS countrytable(countrycolumn);
------------------------------------------------------------------------
/*
-- New DATE and TIME Data types
DATE - 3 Bytes - 01-01-0001 to 12/31/2009 - accuracy of 1 day
TIME - 3 to 5 Bytes - 100 nanoseconds accuracy
DATETIME2 6 to 8 Bytes - 01-01-0001 to 12/31/2009 - 100 nanoseconds accuracy
DATETIMEOFFSET 8 to 10 Bytes - 01-01-0001 to 12/31/2009 - 100 nanoseconds accuracy
DATE -- Only Stores Date portion -- 'YYYY-MM-DD'
TIME -- Only Stores Time Portion -- 'hh:mm:ss.nnnnnnn'
DATETIME2 -- Combination of DATE and TIME types
-- 'YYYY-MM-DD hh:mm:ss.nnnnnnn'
DATETIMEOFFSET -- DATETIME2 with timezone offset
-- 'YYYY-MM-DD hh:mm:ss.nnnnnnn +05.30'
-- TIME,DATETIME2,DATETIMEOFFSET can be specified with precision of fractional seconds
*/
declare @dt date = '2010-01-01' ,
@ti time = '22:45:16.1234567',
@dt2 datetime2 = '2010-01-01 22:45:16.1234567',
@dtoffset datetimeoffset = '2010-01-01 22:45:16.1234567 +02:00';
select @dt as dateval,@ti as timeval,@dt2 as datetime2val,
@dtoffset as datetimeoffsetval
declare @dt4 datetime2 = '2010-01-01 22:45:16.1234567'
declare @dt01 date,@tm time
set @dt01 = @dt4 -- truncates time portion
set @tm = @dt4 -- display time portion
select @dt4,@dt01,@tm
declare @tm1 time(7) = '23:44:25.1235567', @tm3 time(3),@tm4 time(4),@tm5 time(5)
select @tm3 = @tm1, @tm5= @tm1, @tm4 = @tm1
select @tm1,@tm3,@tm4,@tm5
------------------------------------------------------------------------
/*
New Datetime functions
SYSDATETIME() - Returns datatype datetime2
SYSUTCDATETIME() - Returns datatype datetime2 with UTC
SYSDATETIMEOFFSET() - Returns datatype DATETIMEOFFSET with systemtimezone
*/
select
GETDATE() as currentdatetimegetdate,
SYSDATETIME() as currentdatetime,
SYSUTCDATETIME() as currentdatetimeUTC,
SYSDATETIMEOFFSET() as currentdatetimetimezone
select
CAST(SYSDATETIME() as date) as currentdate,
CAST(SYSDATETIME() as time) as currenttime
-- using CAST(SYSDATETIMEcolumn as date) , optimizer uses the index
-- DATEADD, DATEDIFF,DATENAME,DATEPART add support to micro and nano seconds
------------------------------------------------------------------------
/*
Table Types and Table Valued Paramaeters
Table Types -- can save table definition in the database
and can be used to define table variables
Table Valued Paramaeters -- Enable to pass parameter of table type
to SP and functions
*/
use tempdb
go
--drop type dbo.pptype
CREATE TYPE dbo.pptype as table
( ason date,snam varchar(7),cusip varchar(13),marketvalue numeric(18,2))
go
declare @tblpp as dbo.pptype
insert into @tblpp(ason,snam,cusip,marketvalue)
values ('01/01/2009','AXYZ2','74834L100',123.45)
select * from @tblpp
select * from sys.table_types -- to get all table type metadata
go
create procedure dbo.usp_list_port_position
(@tblportposition as dbo.pptype READONLY )
as
set nocount on
select * from @tblportposition;
return
go
declare @tblpp as dbo.pptype
insert into @tblpp(ason,snam,cusip,marketvalue)
values ('01/01/2009','AXYZ2','74834L100',123.45)
exec dbo.usp_list_port_position @tblpp -- passing table as parameter
go
-- When passing from .NET pass the data type as SqlDbType.Structured
------------------------------------------------------------------------
/*
MERGE Statement
This is a single statement which combines INSERT,UPDATE and DELETE
acton as a single opertion based on condtional logic
More efficient than individual statements applied separately
Target Table: specified in MERGE INTO clause
Source Table: specified in USING clause each case
WHEN MATCHED THEN
WHEN NOT MATCHED BY TARGET THEN
WHEN NOT MATCHED BY SOURCE THEN
Atleast one case need to be specified
Supports OUTPUT clause
*/
use tempdb
go
if OBJECT_ID('dbo.portposition','U') is not null
drop table dbo.portposition;
go
create table dbo.portposition
(
ason date,snam varchar(7),cusip varchar(13),marketvalue numeric (10,2)
primary key (ason,snam,cusip) )
insert into dbo.portposition (ason,snam,cusip,marketvalue)
values ('01/31/2009','ax0001','acvbfghi',100.25),
('02/28/2009','ax0001','acvbfghi',200.50),
('03/31/2009','ax0001','acvbfghi',300.75),
('01/31/2009','ay0001','acvbfghi',100.25),
('02/28/2009','ay0001','acvbfghi',200.25),
('01/31/2009','az0001','acvbfghi',100.25);
if OBJECT_ID('dbo.temp_portposition','U') is not null
drop table dbo.temp_portposition;
go
create table dbo.temp_portposition
( ason date,snam varchar(7),cusip varchar(13),marketvalue numeric (10,2)
primary key (ason,snam,cusip) )
insert into dbo.temp_portposition (ason,snam,cusip,marketvalue)
values ('01/31/2009','ax0001','acvbfghi',1000.25),
('02/28/2009','ax0001','acvbfghi',2000.50),
('03/31/2009','ax0001','acvbfghi',3000.75),
('03/31/2009','ay0001','acvbfghi',4000.25);
MERGE INTO dbo.portposition as TGT -- Target
USING dbo.temp_portposition as SRC -- Source
ON TGT.ason = SRC.ason
AND TGT.snam = SRC.snam
AND TGT.cusip = SRC.cusip
WHEN MATCHED THEN
UPDATE SET
TGT.marketvalue = SRC.marketvalue
WHEN NOT MATCHED THEN -- present in source not in target
INSERT (ason,snam,cusip,marketvalue)
VALUES (src.ason,src.snam,src.cusip,src.marketvalue)
WHEN NOT MATCHED BY SOURCE THEN -- present in target not in source
DELETE ;
SELECT * FROM dbo.portposition;
------------------------------------------------------------------------
-- GROUPING SETS
--select * from port_position
select snam,YEAR(ason)asonyear,month(ason) asonmonth ,SUM(marketvalue)as marketvalue from port_position
where Ason is not null and SNAM > ''
group by
GROUPING SETS (
(SNAM,YEAR(ason),month(ason)),
(SNAM,YEAR(ason)),
(SNAM)
);
select snam,YEAR(ason)asonyear,month(ason) asonmonth ,SUM(marketvalue)as marketvalue from port_position
where Ason is not null and SNAM > ''
group by GROUPING SETS
(
(SNAM,YEAR(ason)),
(SNAM,month(ason))
);
-- ROLL UP AND CUBE are enahanced
-- ROLL UP equivalent to
-- GROUPING SETS ({SNAM,YEAR,MONTH},{SNAM,YEAR},{SNAM},{} )
select snam,YEAR(ason)asonyear,month(ason) asonmonth ,SUM(marketvalue)as marketvalue from port_position
where Ason is not null and SNAM > ''
group by ROLLUP( SNAM,YEAR(ason),month(ason) );
------------------------------------------------------------------------
-- SPARSE Columns
/*
Sparse columns are optimized for the storage of NULLs.
When considerable portion of rows ( > 70 %) for columns contains NULLs ,
it is better to define columns as SPARSE.
Sparse columns do not consume when NULL is stored , but storage of non NULL values
become more expensive.
*/
/*
Single XML column can be defined as a column set to hold
all sparse columns
This is useful when there are large number of sparse columns
in a table and operating individually will be come difficult
When XML column set is used, select * and select individual columns
returns different results
select * results XML columns
select individual columns returns columns in relational way
*/
/*
-- Filtered Indexes and Statisitcs
Non clustered indexes on predicates (where clause) stores
rows honuoring filter condition in B-Tree
Also manual statistics can be created on filter conditions
Optimizer evalutes appropriate indexes and statistics during query execution
Well desigend filter indexes improves query performance
and are smaller than usual indexes .
Index/Statisitc maintenance like reindexing,defragmnetation,
update statistics, storage are reduced using filtered indexes
*/
use tempdb
go
if OBJECT_ID('dbo.portposition','U') is not null
drop table dbo.portposition;
go
create table dbo.portposition
( ason date,snam varchar(7),cusip varchar(13),marketvalue numeric (10,2),
accruedincome numeric(10,2) null primary key (ason,snam,cusip)
)
insert into dbo.portposition (ason,snam,cusip,marketvalue,accruedincome)
values ('01/31/2009','ax0001','acvbfghi',100.25,null),
('02/28/2009','ax0001','acvbfghi',200.50,28),
('03/31/2009','ax0001','acvbfghi',300.75,null),
('01/31/2009','ay0001','acvbfghi',100.25,null),
('02/28/2009','ay0001','acvbfghi',200.25,null),
('01/31/2009','az0001','acvbfghi',100.25,null);
go
create nonclustered index idx_portposition_accruedincome
on dbo.portposition(accruedincome)
where accruedincome is not null ;
create nonclustered index idx_portposition_ason
on dbo.portposition(ason)
where ason >= '02/01/2009' ;
create nonclustered index idx_portposition_marketvalue
on dbo.portposition(marketvalue)
where marketvalue >= 200;
select * from dbo.portposition where accruedincome is not null;
select * from dbo.portposition where ason between '02/01/2009' and '03/31/2009'
select * from dbo.portposition where marketvalue between 200 and 400;
------------------------------------------------------------------------
/*
Object dependency is improved and provides accurate information
sys.sql_expression_dependencies -- object dependencies by name
sys.dm_sql_referenced_entities -- DMF provides all entities that the
input entity references ( entity depending on other entities)
this replaces older sys.sql_dependencies
sys.dm_sql_referencing_entities -- DMF provides all entities that refrence
the input entity ( all entities depending on the input entity)
*/
select
OBJECT_SCHEMA_NAME(referencing_id) as srcschema,
OBJECT_NAME(referencing_id) as srcname,
referencing_minor_id as src_minor_id ,
referenced_schema_name as tgtschema,
referenced_entity_name as tgtname,
referenced_minor_id as tgtminorid
from sys.sql_expression_dependencies
order by srcschema,srcname
-- entity depending on other entities
-- procedure dbo.getOrgUnit depends on
select
referenced_schema_name as objschema,
referenced_entity_name as objname,
referenced_minor_name as minorname,
referenced_class_desc as class
from sys.dm_sql_referenced_entities ('dbo.getOrgUnit','OBJECT')
order by objschema,objname
select
referenced_schema_name as objschema,
referenced_entity_name as objname,
referenced_minor_name as minorname,
referenced_class_desc as class
from sys.dm_sql_referenced_entities (null,'OBJECT')
where referenced_minor_name = 'orgunitid'
order by objschema,objname
-- all entities depending on the give entitty
-- entities depending on the table dbo.account
select
referencing_schema_name as objschema,
referencing_entity_name as objname,
referencing_class_desc as class
from sys.dm_sql_referencing_entities ('dbo.account','OBJECT')
order by objschema,objname
-------------------------------------------------------------------
--Convert function enhanced for converting hex values to binary
-- These styles convert a hex string literal to a binary value that contains the same digits
-- when presented in hex form and vice versa.
-- New Style Number is added
-- Style 1 -- with 0x
-- Style 2 -- without 0x
SELECT
CONVERT(VARCHAR(12) , 0x49747A696B , 1) AS [Bin to Char 1],
CONVERT(VARBINARY(5), '0x49747A696B', 1) AS [Char to Bin 1],
CONVERT(VARCHAR(12) , 0x49747A696B , 2) AS [Bin to Char 2],
CONVERT(VARBINARY(5), '49747A696B' , 2) AS [Char to Bin 2];
------------------------------------------------------------------------
Hierarchy
------------------------------------------------------------------------
USE tempdb;
IF OBJECT_ID('dbo.Orgunit', 'U') IS NOT NULL DROP TABLE dbo.Orgunit;
CREATE TABLE dbo.Orgunit
(
OrgunitID INT NOT NULL,
hid HIERARCHYID NOT NULL,
lvl AS hid.GetLevel() PERSISTED, -- Computed column and persisted for performance
OrgUnitName VARCHAR(100) NOT NULL,
OrgTypeID char(2),
CONSTRAINT PK_Orgunit PRIMARY KEY NONCLUSTERED(OrgunitID)
);
/*
1. GetLevel method of the hid column to define the persisted computed column level.
2. This method returns the level of the current node in the hierarchy.
3. The HIERARCHYID value: A child’s sort value is guaranteed to be higher than the parent’s sort value.
( topological sorting; a node’s sort value is guaranteed to be higher
than all of its ancestors)
*/
CREATE CLUSTERED INDEX idx_depth_first ON dbo.Orgunit(hid);
-- Above index can use to fnd all descendents of a node
CREATE INDEX idx_breadth_first ON dbo.Orgunit(lvl, hid);
-- Above Index on lvl and hide use to find all nodes from the same level
-- and direct subordinates of a node
--drop index orgunit.idx_breadth_first
--drop index orgunit.idx_depth_first
/*
Inserting New Nodes
1. To insert a node into the hierarchy, a new HIERARCHYID
value to be created that represents the correct position in the hierarchy.
2. HIERARCHYID::GetRoot() method to produce the value for the root node.
3. GetDescendant method to produce a value below a given parent.
4. GetDescendant method accepts two optional HIERARCHYID input
values representing the two nodes between which
new node can be positioned .
which adds a new node to the hierarchy:
*/
IF OBJECT_ID('dbo.usp_AddOrgUnit', 'P') IS NOT NULL DROP PROC dbo.usp_AddOrgUnit;
GO
CREATE PROC dbo.usp_AddOrgUnit
@OrgunitID AS INT,
@ParentOrgunitID AS INT = NULL,
@OrgUnitName AS VARCHAR(100),
@OrgTypeID AS char(2)
as
set nocount on
DECLARE
@hid AS HIERARCHYID,
@Parent_hid AS HIERARCHYID,
@last_child_hid AS HIERARCHYID;
IF @ParentOrgunitID IS NULL
SET @hid = HIERARCHYID::GetRoot();
ELSE
BEGIN
-- Find Hierarchy ID of ParentOrguniID
SET @Parent_hid = (SELECT hid FROM dbo.Orgunit WHERE OrgunitID = @ParentOrgunitID);
-- There may several children for the ParentOrgunitID
-- Find the maximum child hierearchyid
SET @last_child_hid =
(SELECT MAX(hid) FROM dbo.Orgunit
WHERE hid.GetAncestor(1) = @Parent_hid);
-- Create a new hierarchyID in between Parent hierchyid and max child hierchyid
SET @hid = @Parent_hid.GetDescendant(@last_child_hid, NULL);
END
INSERT INTO dbo.Orgunit(OrgunitID, hid, OrgUnitName, OrgTypeID)
VALUES(@OrgunitID, @hid, @OrgUnitName, @OrgTypeID);
GO
EXEC dbo.usp_AddOrgUnit @OrgunitID = 0, @ParentOrgunitID = NULL, @OrgUnitName = 'Harrow Partners Ltd' , @OrgTypeID = 'CO';
EXEC dbo.usp_AddOrgUnit @OrgunitID = 1, @ParentOrgunitID = 0, @OrgUnitName = 'HP Division Business Unit' , @OrgTypeID = 'DI';
EXEC dbo.usp_AddOrgUnit @OrgunitID = 2, @ParentOrgunitID = 1, @OrgUnitName = 'HP BD' , @OrgTypeID = 'BD';
EXEC dbo.usp_AddOrgUnit @OrgunitID = 3, @ParentOrgunitID = 2, @OrgUnitName = 'BD ADV Firm1' , @OrgTypeID = 'AF';
EXEC dbo.usp_AddOrgUnit @OrgunitID = 4, @ParentOrgunitID = 0, @OrgUnitName = 'HP RIA Firm1' , @OrgTypeID = 'RI';
EXEC dbo.usp_AddOrgUnit @OrgunitID = 5, @ParentOrgunitID = 3, @OrgUnitName = 'HP Region 1' , @OrgTypeID = 'RG';
EXEC dbo.usp_AddOrgUnit @OrgunitID = 6, @ParentOrgunitID = 1, @OrgUnitName = 'HP Branch 1' , @OrgTypeID = 'BH';
EXEC dbo.usp_AddOrgUnit @OrgunitID = 8, @ParentOrgunitID = 6, @OrgUnitName = 'New Orgunit' , @OrgTypeID = 'CO';
EXEC dbo.usp_AddOrgUnit @OrgunitID = 9, @ParentOrgunitID = 1, @OrgUnitName = 'New Orgunit' , @OrgTypeID = 'CO';
/*
Querying the Hierarchy
hid value represents binary,ToString method on hid provides logical string representation of the value, which shows the path with a slash sign used as a separator between the levels.
*/
SELECT C.OrgunitID, C.OrgUnitName,P.OrgunitID ParentOrgunitID,c.lvl as hierarchylevel,C.hid.ToString() AS path
FROM dbo.Orgunit AS P
JOIN dbo.Orgunit AS C
ON C.hid.GetAncestor(1) = P.hid
order by c.lvl,c.hid
SELECT C.OrgunitID, C.OrgUnitName,P.OrgunitID ParentOrgunitID,c.lvl as hierarchylevel,C.hid.ToString() AS path
FROM dbo.Orgunit AS P
JOIN dbo.Orgunit AS C
ON C.hid.GetAncestor(1) = P.hid
order by c.hid
/*
Graphical depiction of the hierarchy— sort the rows by hid,
and produce indentation based on the lvl column as follows:
*/
SELECT orgunitid,
REPLICATE(' ', lvl) + OrgUnitName ,
hid.ToString() AS path
FROM dbo.Orgunit
ORDER BY hid;
/*
To get all child orgunits of an orgunit (subtree), use a method called IsDescendantOf
This method accepts a node’s HIERARCHYID value as input
and returns 1-- true or 0 -- false if queried node is a descendant of the input node.
The following query returns all child orgunits—direct and indirect—of orgunit 1:
*/
SELECT C.OrgunitID, C.OrgUnitName, C.lvl
FROM dbo.Orgunit AS P
JOIN dbo.Orgunit AS C
ON P.OrgunitID = 1
AND C.hid.IsDescendantOf(P.hid) = 1;-- 1 - true , 0 - false
/*
IsDescendantOf method can also be used to return all parent orgunits of a given orgunit.
The following query returns all parent orgunits of orgunit 3:
*/
SELECT P.OrgunitID, P.OrgUnitName, P.lvl
FROM dbo.Orgunit AS P
JOIN dbo.Orgunit AS C
ON C.OrgunitID = 3
AND C.hid.IsDescendantOf(P.hid) = 1
/*
To get a whole level of child orgunits of a certain orgunit,
use the GetAncestor method. This method accepts a number (call it n) as input
and returns the HIERARCHYID value of the ancestor of the queried node, n levels above.
The following query returns direct child orgunits(1 level below) of orgunit 1,2,3:
*/
-- Level 1 below OrgunitID = 1
SELECT C.OrgunitID, C.OrgUnitName,P.OrgunitID as ParentOrgunitID
FROM dbo.Orgunit AS P
JOIN dbo.Orgunit AS C
ON P.OrgunitID = 1
AND C.hid.GetAncestor(1) = P.hid
order by c.hid
-- Level 1 below OrgunitID = 0
SELECT C.OrgunitID, C.OrgUnitName
FROM dbo.Orgunit AS P
JOIN dbo.Orgunit AS C
ON P.OrgunitID = 0
AND C.hid.GetAncestor(1) = P.hid;
-- Level 2 below OrgunitID = 0
SELECT C.OrgunitID, C.OrgUnitName
FROM dbo.Orgunit AS P
JOIN dbo.Orgunit AS C
ON P.OrgunitID = 0
AND C.hid.GetAncestor(2) = P.hid;
-- Level 3 below OrgunitID = 0
SELECT C.OrgunitID, C.OrgUnitName
FROM dbo.Orgunit AS P
JOIN dbo.Orgunit AS C
ON P.OrgunitID = 0
AND C.hid.GetAncestor(3) = P.hid;
-- HIERARCHYID::Parse Method -- converts string to hierarchyid
-- GetReparentedValue -- Reparent Nodes (old root,new root )
-- Current Path -- /1/1/2/3/2/, old root -- /1/1/, new root -- /2/1/4/
-- New Path /2/1/4/2/3/2/
