Contents

1 Row Constructors
2 MERGE
3 Table Value Parameters
4 Grouping Set
5 Force Seek
6 OPTIMIZE FOR Query Hint Option Include UNKNOWN Variable Value
7 INSERT EXECUTE Statements Can Execute Against Linked Servers
8 CONVERT Function
9 Lock Escalation Option
10 New Data Type ? 讌蟯
11 Compound Assignment Operators
12 Hierarchyid Data Type
13 spatial data types
14 谿瑚襭


..覦 蟲谿..
襦 .. <;;

1 Row Constructors #

覯 Row襯 ロ . 讌 襯 覲企 危願 觜襯企.
use tempdb
go

--drop table source_table
create table source_table (
	id varchar(10)
,	pw varchar(10)
);

--drop table target_table
create table target_table (
	id varchar(10)
,	pw varchar(10)
);

--drop table audit
create table audit(
	action varchar(50)
,	insert_id varchar(10)
,	insert_pw varchar(10)
,	delete_id varchar(10)
,	delete_pw varchar(10)
);

insert source_table values 
	('dwa', '1234')
,	('yasi', '4567')
,	('lk', 'lk123'); --new syntax, T-SQL Row Constructors
go

/*谿瑚襦 企郁 螳ロ.
select *
from (
	values
		('dwa', '1234')
	,	('yasi', '4567')
	,	('lk', 'lk123')
) t(id, passwd)
*/

2 MERGE #

'朱 螳煙螻, 朱 曙'朱 襦讌 蟲覓語企. 讀, 'IF Found THEN UPDATE ELSE INSERT' 企. Row Constructors 襷 企襦 蟆.
select * from target_table;
/*
id         pw
---------- ----------
dwa        1234
yasi       4567
lk         lk123
*/

-- source 一危郁 螻, target 一危郁 .
insert audit(action, insert_id, insert_pw, delete_id, delete_pw)
select action, insert_id, insert_pw, delete_id, delete_pw
from (
	merge --top (100) percent 
	target_table a using source_table b
	on a.id = b.id 
	when matched then 
		update set 
			a.id = b.id
		,	a.pw = b.pw
	when not matched by target then
		insert values (id, pw)
	when not matched by source then
		delete
	output $action, inserted.*, deleted.*
) t (action, insert_id, insert_pw, delete_id, delete_pw);

select * from audit;
/*
action                                             insert_id  insert_pw  delete_id  delete_pw
-------------------------------------------------- ---------- ---------- ---------- ----------
INSERT                                             dwa        1234       NULL       NULL
INSERT                                             yasi       4567       NULL       NULL
INSERT                                             lk         lk123      NULL       NULL
*/

襷れ讌 row襯 曙 覲伎.
insert source_table values ('dwa2', '1234');
insert target_table values ('dwa3', '1234');
/*
select * from source_table;
select * from target_table;

id         pw
---------- ----------
dwa        1234
yasi       4567
lk         lk123
dwa2       1234		<--- not matched

id         pw
---------- ----------
dwa        1234
yasi       4567
lk         lk123
dwa3       1234		<--- not matched
*/

not matched 襦磯れ 豌襴襯 企慨.
insert audit(action, insert_id, insert_pw, delete_id, delete_pw)
select action, insert_id, insert_pw, delete_id, delete_pw
from (
	merge --top (100) percent 
	target_table a using source_table b
	on a.id = b.id 
	when matched then 
		update set 
			a.id = b.id
		,	a.pw = b.pw
	when not matched by target then
		insert values (id, pw)
	when not matched by source then
		delete
	output $action, inserted.*, deleted.*
) t (action, insert_id, insert_pw, delete_id, delete_pw);

select * from audit;
/*
action                                             insert_id  insert_pw  delete_id  delete_pw
-------------------------------------------------- ---------- ---------- ---------- ----------
INSERT                                             dwa        1234       NULL       NULL
INSERT                                             yasi       4567       NULL       NULL
INSERT                                             lk         lk123      NULL       NULL
INSERT                                             dwa2       1234       NULL       NULL
UPDATE                                             dwa        1234       dwa        1234
UPDATE                                             yasi       4567       yasi       4567
UPDATE                                             lk         lk123      lk         lk123
DELETE                                             NULL       NULL       dwa3       1234
*/
	
select * from source_table;
select * from target_table;
/*
id         pw
---------- ----------
dwa        1234
yasi       4567
lk         lk123
dwa2       1234

id         pw
---------- ----------
dwa        1234
yasi       4567
lk         lk123
dwa2       1234	

dwa2 traget_table 朱襦 insert, dwa3 source_table not matched企襦 target_table delete
*/

れ螻 螳 'AND'襦 譟郁唄 豢螳 .
insert audit(action, insert_id, insert_pw, delete_id, delete_pw)
select action, insert_id, insert_pw, delete_id, delete_pw
from (
	merge --top (100) percent 
	target_table a using source_table b
	on a.id = b.id 
	when matched and a.id like 'dw%' then --譟郁唄豢螳  蟆
		update set 
			a.id = b.id
		,	a.pw = b.pw
	when not matched by target then
		insert values (id, pw)
	when not matched by source then
		delete
	output $action, inserted.*, deleted.*
) t (action, insert_id, insert_pw, delete_id, delete_pw);

/* 谿瑚..
insert a(id, pw)
select id, pw
from (
	delete from target_table
	output deleted.*
) t
go

-- 蟆 譟壱
create proc usp_teset
as
delete from a
output deleted.*
go
*/


谿瑚襭

3 Table Value Parameters #

朱 ろ碁ゼ 蟆曙 襷れ. 伎螻 螳.
use tempdb
go

--drop table source_table
create table source_table (
	id varchar(10)
,	pw varchar(10)
);

--drop table target_table
create table target_table (
	id varchar(10)
,	pw varchar(10)
);

insert source_table values 
	('dwa', '1234')
,	('yasi', '4567')
,	('lk', 'lk123') --new syntax, T-SQL Row Constructors
go

襷り, 襦 Row Set 襷り覲襦 覦 . 襯 れ 襷り覲襦 '1,2,3,4' 螳 蟲覿襯 螳讌 覓語伎 燕 企 襦 襷れ朱 SQL Server 2008 覦 覃 .
create type id as table --new syntax, Table Value Type
(
	id varchar(10) null
);
go

create proc table_value_param_test
	@table id readonly
as
begin
	declare @i int = 10; --new syntax
	select * from source_table 
	where id in (select id from @table)
end
go

declare @param as id;
insert @param(id) values('dwa'), ('yasi');
exec table_value_param_test @param;
/*
id         pw
---------- ----------
dwa        1234
yasi       4567
*/


4 Grouping Set #

ROLLUP螻 CUBE襯 伎 貎朱Μ れ螻 螳 蟆郁骸襯 訖碁. 2005 覯 危 企.
--Grouping Set
use tempdb
go

--drop table grp_set_test
create table grp_set_test(
	group1 int
,	group2 char(1)
,	val int
);

insert grp_set_test values 
	(1, 'a', 10)
,	(1, 'a', 20)
,	(1, 'a', 30)
,	(1, 'b', 60)
,	(2, 'a', 70)
,	(2, 'a', 80)
,	(3, 'c', 70)
,	(3, 'c', 90);
go

select
	group1
,	group2 
,	SUM(val) as total
from grp_set_test
group by 
	group1
,	group2 with rollup;
/*
group1      group2 total
----------- ------ -----------
1           a      60
1           b      60
1           NULL   120
2           a      150
2           NULL   150
3           c      160
3           NULL   160
NULL        NULL   430
*/

select
	group1
,	group2 
,	SUM(val) as total
from grp_set_test
group by 
	group1
,	group2 with cube;
/*
group1      group2 total
----------- ------ -----------
1           a      60
2           a      150
NULL        a      210
1           b      60
NULL        b      60
3           c      160
NULL        c      160
NULL        NULL   430
1           NULL   120
2           NULL   150
3           NULL   160
*/

2008覯 れ螻 螳 . 貎朱Μ 螳 蟆郁骸襯 訖襴磯.
--sql server 2008 new syntax
select
	case when grouping(group1) = 1 then 'total' else convert(varchar(5), group1) end group1
,	case when grouping(group2) = 1 then 'total' else group2 end group2 
,	grouping_id(group1, group2) grouping_ids --new syntax
,	SUM(val) as total
from grp_set_test
group by 
	rollup(group1, group2); --new syntax
/*
group1 group2 grouping_ids total
------ ------ ------------ -----------
1      a      0            60
1      b      0            60
1      total  1            120
2      a      0            150
2      total  1            150
3      c      0            160
3      total  1            160
total  total  3            430
*/

select
	case when grouping(group1) = 1 then 'total' else convert(varchar(5), group1) end group1
,	case when grouping(group2) = 1 then 'total' else group2 end group2 
,	grouping_id(group1, group2) grouping_ids --new syntax
,	SUM(val) as total
from grp_set_test
group by 
	grouping sets --new syntax
	(
		(group1, group2)
	,	(group1)
	,	()
	);
/*
group1 group2 grouping_ids total
------ ------ ------------ -----------
1      a      0            60
1      b      0            60
1      total  1            120
2      a      0            150
2      total  1            150
3      c      0            160
3      total  1            160
total  total  3            430
*/

蟆郁骸 螻 豐螻襯 蟇壱 覲伎.
--螻 蟇
select
	case when grouping(group1) = 1 then 'total' else convert(varchar(5), group1) end group1
,	case when grouping(group2) = 1 then 'total' else group2 end group2 
,	grouping_id(group1, group2) grouping_ids --new syntax
,	SUM(val) as total
from grp_set_test
group by 
	grouping sets
	(
		(group1, group2)
--	,	(group1)
	,	()
	);
/*
group1 group2 grouping_ids total
------ ------ ------------ -----------
1      a      0            60
1      b      0            60
2      a      0            150
3      c      0            160
total  total  3            430
*/

--豐螻 蟇
select
	case when grouping(group1) = 1 then 'total' else convert(varchar(5), group1) end group1
,	case when grouping(group2) = 1 then 'total' else group2 end group2 
,	grouping_id(group1, group2) grouping_ids --new syntax
,	SUM(val) as total
from grp_set_test
group by 
	grouping sets
	(
		(group1, group2)
	,	(group1)
--	,	()
	);
/*
group1 group2 grouping_ids total
------ ------ ------------ -----------
1      a      0            60
1      b      0            60
1      total  1            120
2      a      0            150
2      total  1            150
3      c      0            160
3      total  1            160
*/
蟆郁骸 願 譬 譟.

谿瑚:
SELECT customer, year, SUM(sales)
FROM T
GROUP BY GROUPING SETS ((customer), (year))

--is the same as
SELECT customer, NULL as year, SUM(sales)
FROM T 
GROUP BY customer
UNION ALL
SELECT NULL as customer, year, SUM(sales)
FROM T 
GROUP BY year

5 Force Seek #

Force Seek 螳襦 碁煙るゼ 郁鍵(?) 貎朱Μ 碁. 襯 覲企 所 . ろ碁ゼ 譴觜襯 .
use tempdb
go

set nocount on 
set statistics io off

--drop table force_seek_table
create table force_seek_table
(
	col1 int identity not null,
	col2 tinyint not null
);
go

begin tran;
declare @i int;
set @i = 1;
while (@i <= 1000000)
begin
	if (@i <=100000)
		insert into force_seek_table (col2) values (10);
	if (@i > 100000 and @i <= 1000000)
		insert into force_seek_table (col2) values (90);
	set @i = @i + 1;
end
commit;
go

create nonclustered index force_seek_table on force_seek_table (col2);
go

れ 貎朱Μ襯 ろ 覲伎.
select * from force_seek_table
where col2 = 10;
Upload new Attachment "force_seek01.jpg" on the "UploadFile"

SQL Server 牛磯伎 れ . Force Seek 碁ゼ 譯朱 企至 蟾? 轟壱 Index Seek .

select * from force_seek_table with (forceseek)
where col2 = 10;
Upload new Attachment "force_seek02.jpg" on the "UploadFile"

6 OPTIMIZE FOR Query Hint Option Include UNKNOWN Variable Value #

OPTIMIZE FOR 貎朱Μ瑚 蟆朱. (2005 覯 危) OPTIMIZE FOR 襦貉覲襯 牛磯伎螳 襦貉覲 覿れ煙朱 豕 貎朱Μ螻 語一 覈詩 蟆曙 牛磯伎蟆 糾覲企ゼ 伎 襦 螳 螻牛蠍 . OPTIMIZE FOR 覈 螳 貎朱Μ 豕 襷 螻 ろ 讌 . (襦貉覲 覓語 Magic Density襯 谿瑚.)
USE AdventureWorks;
GO

EXEC sp_helpindex 'Person.Address';
/*
rowguid
AddressLine1, AddressLine2, City, StateProvinceID, PostalCode
StateProvinceID
AddressID
*/

create index nix_postal_code
on Person.Address(PostalCode);
go

DECLARE @city_name nvarchar(30);
DECLARE @postal_code nvarchar(15);
SET @postal_code = 86171;

--sql1
SELECT * FROM Person.Address
WHERE City = @city_name AND PostalCode = @postal_code
OPTION ( OPTIMIZE FOR (@city_name = 'Seattle', @postal_code UNKNOWN) );
optimize_for01.jpg

--sql2
SELECT * FROM Person.Address
WHERE City = @city_name AND PostalCode = @postal_code
OPTION ( OPTIMIZE FOR (@city_name = 'Seattle', @postal_code = 98028) );
optimize_for02.jpg

7 INSERT EXECUTE Statements Can Execute Against Linked Servers #

2005覯 loopback 襷 覯 伎 INSERT... EXECUTE 讌 讌 . 讌襷 2008覯 讌. 襯 谿瑚.
--sql server 2005
create database work;
go

exec sp_addlinkedserver 
			@Server='LoopBack'
		,	@SrvProduct='SQLNCLI'
		, 	@Provider=''
		, 	@DataSrc='localhost'
		, 	@Catalog='work';

exec sp_addlinkedsrvlogin 'LoopBack', 'False', 'Sa', 'userid', 'pw';
go

EXEC sp_serveroption 'LoopBack', 'rpc', 'true'
GO

EXEC sp_serveroption 'LoopBack', 'rpc out', 'true'
GO

use work
go
--drop table test
create table test(id int);
insert test values(1);

insert test(id) exec LoopBack.work.dbo.usp_test;
/*
覃讌3910, 譴16, 2, 譴1
るジ語碁貉ろ碁ゼ譴.

Transaction context in use by another session.  
*/

--sql server 2008
create database work;
go

exec sp_addlinkedserver 
			@Server='LoopBack'
		,	@SrvProduct=''
		, 	@Provider='SQLNCLI10'
		, 	@DataSrc='localhost\sql2008_ctp6_feb'
		, 	@Catalog='work';

exec sp_addlinkedsrvlogin 'LoopBack', 'False', 'Sa', 'userid', 'pw';
go

EXEC sp_serveroption 'LoopBack', 'rpc', 'true'
GO

EXEC sp_serveroption 'LoopBack', 'rpc out', 'true'
GO

use work
go
--drop table test
create table test(id int);
insert test values(1);
go

create proc usp_test
as
set xact_abort on
select 1
go

insert test(id) exec LoopBack.work.dbo.usp_test;

8 CONVERT Function #

declare @chr varchar(4) = 'yasi';
declare @bin varbinary(4) = convert(varbinary(20), @chr);
select
	@bin bin
,	CONVERT(varchar(4), @bin, 0) style0
,	CONVERT(varchar(4), @bin, 1) style1
,	CONVERT(varchar(4), @bin, 2) sytel2
/*
bin        style0 style1 sytel2
---------- ------ ------ ------
0x79617369 yasi   0x79   7961

谿瑚: sys.fn_varbintohexstr
*/

9 Lock Escalation Option #

2008覯 れ錆伎 覈朱 譟一 蟲覓語 蟆朱. 讌 ろ 企慨.
--ろ碁一危一
-- SET ( LOCK_ESCALATION = { AUTO | TABLE | DISABLE } )
set nocount on 
set statistics io off

--drop table lock_test
create table lock_test
(
	id int primary key
,	name varchar(20)
);
go

begin tran
declare @i int = 1;
while(@i <= 10000)
begin
	insert lock_test 
	values(@i, left(convert(varchar(50), newid()), 20));
	set @i = @i + 1;
end
commit;
go

--auto
alter table lock_test 
set(lock_escalation = auto);
go

語1 れ ろ.
--session1
begin tran
	update lock_test
	set name = 'update'
	where id between 1 and 6000;

語2 曙 覯 危エ覲伎.
--session2
exec sp_lock
Upload new Attachment "lock01.jpg" on the "UploadFile"

key曙 蟆 . 語1 れ れ ろ rollback , れ 7000螳 row襯 覯 一危 貅覲伎. 企 讌 伎 れ錆伎 蟇碁Μ蟆 蠍 伎.
--session1
rollback

--session1
begin tran
	update lock_test
	set name = 'update'
	where id between 1 and 7000;


語2 誤企慨.
--session2
exec sp_lock
Upload new Attachment "lock02.jpg" on the "UploadFile"

れ 語1 rollback 企 れ れ 覦蠖覲伎. 蠏碁Μ螻 れ 碁 覦れ.
--session1
rollback

--table
alter table lock_test 
set(lock_escalation = table);
go

--session1
begin tran
	update lock_test
	set name = 'update'
	where id between 1 and 6219;

語2 誤企慨.
--session2
exec sp_lock
Upload new Attachment "lock03.jpg" on the "UploadFile"

れ 語1 rollback 襷 Row 曙 蟇語企慨.
--session1
rollback

--session1
begin tran
	update lock_test
	set name = 'update'
	where id between 1 and 6220;

exec sp_lock
Upload new Attachment "lock04.jpg" on the "UploadFile"

伎 れ錆伎 覦讌 襦 企 れ 覦蠖覲伎.
--session1
rollback

--disable
alter table lock_test 
set(lock_escalation = disable);
go

--session1
begin tran
	update lock_test
	set name = 'update'
	where id between 1 and 6220;

-- れ錆伎 殊企讌 .
Upload new Attachment "lock05.jpg" on the "UploadFile"

10 New Data Type ? 讌蟯 #

DECLARE @D0 datetime2(0) = '2008-05-10 06:59:11.1234567';
DECLARE @D1 datetime2(1) = '2008-05-10 06:59:11.1234567';
DECLARE @D2 datetime2(2) = '2008-05-10 06:59:11.1234567';
DECLARE @D3 datetime2(3) = '2008-05-10 06:59:11.1234567';
DECLARE @D4 datetime2(4) = '2008-05-10 06:59:11.1234567';
DECLARE @D5 datetime2(5) = '2008-05-10 06:59:11.1234567';
DECLARE @D6 datetime2(6) = '2008-05-10 06:59:11.1234567';
DECLARE @D7 datetime2(7) = '2008-05-10 06:59:11.1234567';
PRINT @D0;
PRINT @D1;
PRINT @D2;
PRINT @D3;
PRINT @D4;
PRINT @D5;
PRINT @D6;
PRINT @D7;

/*
2008-05-10 06:59:11
2008-05-10 06:59:11.1
2008-05-10 06:59:11.12
2008-05-10 06:59:11.123
2008-05-10 06:59:11.1235
2008-05-10 06:59:11.12346
2008-05-10 06:59:11.123457
2008-05-10 06:59:11.1234567
*/

DECLARE @T0 time(0) = '16:59:11.1234567';
DECLARE @T1 time(1) = '16:59:11.1234567';
DECLARE @T2 time(2) = '16:59:11.1234567';
DECLARE @T3 time(3) = '16:59:11.1234567';
DECLARE @T4 time(4) = '16:59:11.1234567';
DECLARE @T5 time(5) = '16:59:11.1234567';
DECLARE @T6 time(6) = '16:59:11.1234567';
DECLARE @T7 time(7) = '16:59:11.1234567';
PRINT @T0;  
PRINT @T1;  
PRINT @T2;  
PRINT @T3;  
PRINT @T4;  
PRINT @T5;  
PRINT @T6;  
PRINT @T7;
/*
16:59:11
16:59:11.1
16:59:11.12
16:59:11.123
16:59:11.1235
16:59:11.12346
16:59:11.123457
16:59:11.1234567
*/

DECLARE @D DATETIMEOFFSET = '2008-05-10 06:59:11.1234567';
DECLARE @D0 DATETIMEOFFSET(0) = '2008-05-10 06:59:11.1234567';
DECLARE @D1 DATETIMEOFFSET(1) = '2008-05-10 06:59:11.1234567';
DECLARE @D2 DATETIMEOFFSET(2) = '2008-05-10 06:59:11.1234567';
DECLARE @D3 DATETIMEOFFSET(3) = '2008-05-10 06:59:11.1234567';
DECLARE @D4 DATETIMEOFFSET(4) = '2008-05-10 06:59:11.1234567';
DECLARE @D5 DATETIMEOFFSET(5) = '2008-05-10 06:59:11.1234567';
DECLARE @D6 DATETIMEOFFSET(6) = '2008-05-10 06:59:11.1234567';
DECLARE @D7 DATETIMEOFFSET(7) = '2008-05-10 06:59:11.1234567';
PRINT @D;
PRINT @D0;
PRINT @D1;
PRINT @D2;
PRINT @D3;
PRINT @D4;
PRINT @D5;
PRINT @D6;
PRINT @D7;
/*
2008-05-10 06:59:11.1234567 +00:00
2008-05-10 06:59:11 +00:00
2008-05-10 06:59:11.1 +00:00
2008-05-10 06:59:11.12 +00:00
2008-05-10 06:59:11.123 +00:00
2008-05-10 06:59:11.1235 +00:00
2008-05-10 06:59:11.12346 +00:00
2008-05-10 06:59:11.123457 +00:00
2008-05-10 06:59:11.1234567 +00:00
*/

11 Compound Assignment Operators #

Compound Assignment OperatorDescription
+= declare @i int = 10
set @i += 3
print @i --13
-= declare @i int = 10
set @i -= 3
print @i --7
*= declare @i int = 10
set @i *= 3
print @i --30
/= declare @i int = 10
set @i /= 3
print @i --3
%= declare @i int = 10
set @i %= 3
print @i --1
&= declare @i int = 10
set @i &= 3
print @i --2
|= declare @i int = 10
set @i |= 3
print @i --11
^= declare @i int = 10
set @i ^= 3
print @i --9

12 Hierarchyid Data Type #

Hierarchyid れ螻 螳 轟螻 伎 螳讌.

  • 螻豸旧 Data type
    • 譟一蟲譟

    • 襦 ろ 讌
    • 語 伎 覿襯
    • 伎 螳 襷 蠏碁


USE AdventureWorks
GO

--企
CREATE TABLE HumanResources.EmployeeOrg
(
   OrgNode hierarchyid PRIMARY KEY CLUSTERED, --蠍一朱!
   OrgLevel AS OrgNode.GetLevel(), --蠍一朱!
   EmployeeID int UNIQUE NOT NULL,
   EmpName varchar(20) NOT NULL,
   Title varchar(20) NULL
) ;
GO

--碁煙れ(願鍵覦碁煙れ)
CREATE UNIQUE INDEX EmployeeOrgNc1 
ON HumanResources.EmployeeOrg(OrgLevel, OrgNode) ;
GO

--襭語曙
INSERT HumanResources.EmployeeOrg (OrgNode, EmployeeID, EmpName, Title)
VALUES (hierarchyid::GetRoot(), 6, 'David', 'Marketing Manager') ;
GO

--蟆1

SELECT * FROM HumanResources.EmployeeOrg
Upload new Attachment "hierarchyid01.jpg" on the "UploadFile"

--蟆2
SELECT 
	OrgNode.ToString() AS Text_OrgNode
,	OrgNode
,	OrgLevel
,	EmployeeID
,	EmpName
,	Title 
FROM HumanResources.EmployeeOrg
Upload new Attachment "hierarchyid02.jpg" on the "UploadFile"

覿讌 曙(GetDescendant()覃)
Upload new Attachment "hierarchyid03.jpg" on the "UploadFile"

DECLARE @Manager hierarchyid 
SELECT @Manager = hierarchyid::GetRoot()
FROM HumanResources.EmployeeOrg ;

INSERT HumanResources.EmployeeOrg (OrgNode, EmployeeID, EmpName, Title)
VALUES
(@Manager.GetDescendant(NULL, NULL), 46, 'Sariya', 'Marketing Specialist') ; 

--蟆
SELECT 
	OrgNode.ToString() AS Text_OrgNode
,	OrgNode
,	OrgLevel
,	EmployeeID
,	EmpName
,	Title 
FROM HumanResources.EmployeeOrg
Upload new Attachment "hierarchyid04.jpg" on the "UploadFile"

--蠏谿朱襦燕曙
CREATE PROC AddEmp(@mgrid int, @empid int, @e_name varchar(20), @title varchar(20)) 
AS 
BEGIN
   DECLARE @mOrgNode hierarchyid, @lc hierarchyid
   SELECT @mOrgNode = OrgNode 
   FROM HumanResources.EmployeeOrg 
   WHERE EmployeeID = @mgrid
   SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
   BEGIN TRANSACTION
      SELECT @lc = max(OrgNode) 
      FROM HumanResources.EmployeeOrg 
      WHERE OrgNode.GetAncestor(1) =@mOrgNode ;

      INSERT HumanResources.EmployeeOrg (OrgNode, EmployeeID, EmpName, Title)
      VALUES(@mOrgNode.GetDescendant(@lc, NULL), @empid, @e_name, @title)
   COMMIT
END ;
GO

EXEC AddEmp 6, 271, 'John', 'Marketing Specialist' ;
EXEC AddEmp 6, 119, 'Jill', 'Marketing Specialist' ;
EXEC AddEmp 46, 269, 'Wanida', 'Marketing Assistant' ;
EXEC AddEmp 271, 272, 'Mary', 'Marketing Assistant' ;
EXEC AddEmp 6, 120, 'yasi', 'Marketing Specialist' ;

--蟆
SELECT 
	OrgNode.ToString() AS Text_OrgNode
,	OrgNode
,	OrgLevel
,	EmployeeID
,	EmpName
,	Title 
FROM HumanResources.EmployeeOrg
Upload new Attachment "hierarchyid05.jpg" on the "UploadFile"

SELECT 
	OrgNode.ToString() AS Text_OrgNode
,	hierarchyid::GetRoot() AS Text_Root
,	OrgNode.GetAncestor(1) AS Text_Ancestor
,	(SELECT EmpName FROM HumanResources.EmployeeOrg B
	WHERE B.OrgNode = A.OrgNode.GetDescendant(0x78, NULL)) 
FROM HumanResources.EmployeeOrg A
WHERE EmpName = 'David'
Upload new Attachment "hierarchyid06.jpg" on the "UploadFile"

13 spatial data types #

14 谿瑚襭 #