..覦 蟲谿..
襦 .. ＜;;
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
SQL Server 牛磯伎 れ . Force Seek 碁ゼ 譯朱 企至 蟾? 轟壱 Index Seek .
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) );
--sql2
SELECT * FROM Person.Address
WHERE City = @city_name AND PostalCode = @postal_code
OPTION ( OPTIMIZE FOR (@city_name = 'Seattle', @postal_code = 98028) );
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;
key曙 蟆 . 語1 れ れ ろ rollback , れ 7000螳 row襯 覯 一危 貅覲伎. 企 讌 伎 れ錆伎 蟇碁Μ蟆 蠍 伎.
--session1
rollback
--session1
begin tran
update lock_test
set name = 'update'
where id between 1 and 7000;
れ 語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;
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"
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"
![[http]](/moniwiki/imgs/http.png)
Dynamic SQL Merge(http://www.sqlservercentral.com/articles/T-SQL/66066/)
