Tag Archives: T-SQL

執行計畫快取的正確與否

回一位朋友的問題。

參照此篇:http://blogs.msdn.com/b/sqlprogrammability/archive/2008/11/26/optimize-for-unknown-a-little-known-sql-server-2008-feature.aspx,簡單測試如下:

use northwind
go
create table t(c1 int identity primary key,c2 nvarchar(10) default(N’Hello’),c3 uniqueidentifier default(newid()),c4 datetime2(7) default(sysdatetime()))
go
insert t(c2) values(‘Hello’)
go
insert t(c2) select c2 from t
go 20
insert t(c2) values(‘Hi’)
go
create index idx on t(c2)
go
create proc sp @c2 nvarchar(10)
as
    select * from t where c2=@c2
go

–以下是搭配執行後執行計畫的驗證

exec sp ‘Hi’
exec sp ‘Hello’  –依執行計畫快取執行

image

 

–依資料分佈平均值建立執行計畫

alter proc sp @c2 nvarchar(10)
as
    declare @var nvarchar(10)
    set @var=@c2
    select * from t where c2=@var
go
exec sp ‘Hi’
exec sp ‘Hello’

image

 

–依參數建議值建立執行計畫

alter proc sp @c2 nvarchar(10)
as
    select * from t where c2=@c2
    option(optimize for(@c2=’Hello’))
go
exec sp ‘Hi’
exec sp ‘Hello’

image

 

–SQL Server 2008 後依資料分佈建立執行計畫,與上述使用變數中介的結果相同

alter proc sp @c2 nvarchar(10)
as
    select * from t where c2=@c2
    option(optimize for(@c2 unknown))
go
exec sp ‘Hi’
exec sp ‘Hello’

image

SQL Server 2016 Audit 搭配 Temporal 資料,傳回使用者查詢時所看到的資料

Terry 提了一個有趣的需求,回傳使用者查詢時所看到的資料

USE [master]
GO
CREATE SERVER AUDIT [Audit-20150917-085416]
TO FILE
(    FILEPATH = N’C:\temp’
    ,MAXSIZE = 0 MB
    ,MAX_ROLLOVER_FILES = 2147483647
    ,RESERVE_DISK_SPACE = OFF
)
WITH
(    QUEUE_DELAY = 1000
    ,ON_FAILURE = CONTINUE
)
GO
alter server audit [Audit-20150917-085416] with(state=on)
go

create database dbTemporal
–非 CDC 從 ldf 透過 log reader 讀交易紀錄放到 system table
alter database dbTemporal set recovery simple
go
use dbTemporal
go

–自行指定記錄時間歷程的對應資料表
create table tbOrg
(
    PK int IDENTITY NOT NULL PRIMARY KEY,
    c1 nvarchar(500),
    StartTime datetime2 GENERATED ALWAYS AS ROW START HIDDEN NOT NULL, –Hidden 關鍵字會隱藏這個欄位
    DueTime datetime2 GENERATED ALWAYS AS ROW END HIDDEN NOT NULL, 
    PERIOD FOR SYSTEM_TIME (StartTime,DueTime) 
)
go

CREATE TABLE tbOrgHistory(
    PK int NOT NULL,
    c1 nvarchar(500) NULL,
    StartTime datetime2(7) NOT NULL,
    DueTime datetime2(7) NOT NULL
)
go

ALTER TABLE tbOrg SET (SYSTEM_VERSIONING = ON
(HISTORY_TABLE =  dbo.tbOrgHistory)
)
go

CREATE DATABASE AUDIT SPECIFICATION [DatabaseAuditSpecification-20150917-085751]
FOR SERVER AUDIT [Audit-20150917-085416]
ADD(SELECT,INSERT,UPDATE,DELETE,EXECUTE ON database::dbTemporal BY public)
WITH (STATE = ON)
GO

–某個時間點查詢語法看到的資料
create proc spGetSnapshot @sql nvarchar(max),@time datetime2(7)
as
    set @sql += ‘ for system_time as of “‘ + convert(varchar(100),dateadd(hour,-8,@time)) + “" –轉成 UTC 時間
    exec(@sql)
go

———————————————————————————–
–Audit 搭配 Temporal 檢視回傳的資料
insert tbOrg(c1) values(‘a’)
waitfor delay ’00:00:01′
declare @t datetime2(7)=sysdatetime()
select * from tbOrg –模擬使用者查詢
waitfor delay ’00:00:01′
update tbOrg set c1=c1 + ‘1’
insert tbOrg(c1) values(‘d2’)
declare @sql nvarchar(max)

–Audit 也是用 UTC 時間
SELECT top(1) @sql=statement
FROM sys.fn_get_audit_file (‘C:\temp\Audit*.sqlaudit’,default,default)
where event_time=dateadd(hour,-8,@t) and object_name=’tbOrg’;
select @sql
exec spGetSnapshot @sql,@t

ALTER TABLE tbOrg SET (SYSTEM_VERSIONING = OFF)
drop table tbOrg
drop table tbOrgHistory

ALTER DATABASE AUDIT SPECIFICATION [DatabaseAuditSpecification-20150917-085751]
WITH (STATE = OFF)
GO
DROP DATABASE AUDIT SPECIFICATION [DatabaseAuditSpecification-20150917-085751]
GO

透過 XQuery 進行 base64 編碼

參考以下網址:http://blog.falafel.com/t-sql-easy-base64-encoding-and-decoding/

declare @source varbinary(max), @encoded varchar(max), @decoded varbinary(max)
set @source = convert(varbinary(max), ‘Hello Base64′)
set @encoded = cast(” as xml).value(‘xs:base64Binary(sql:variable("@source"))’, ‘varchar(max)’)
set @decoded = cast(” as xml).value(‘xs:base64Binary(sql:variable("@encoded"))’, ‘varbinary(max)’)

select
convert(varchar(max), @source) as source_varchar,
@source as source_binary,
@encoded as encoded,
@decoded as decoded_binary,
convert(varchar(max), @decoded) as decoded_varchar

看來若要寫自訂函數,encode 可以將 binary 轉成 varchar,decode 將 varchar 轉成 binary

create function dbo.fnBase64Encode(@Binary varbinary(max))
returns varchar(max)
as
begin
    declare @encoded varchar(max)
    set @encoded=cast(" as xml).value(‘xs:base64Binary(sql:variable("@binary"))’, ‘varchar(max)’)
    return @encoded
end
go

create function dbo.fnBase64Decode(@Varchar varchar(max))
returns varbinary(max)
as
begin
    declare @decoded varbinary(max)
    set @decoded=cast(" as xml).value(‘xs:base64Binary(sql:variable("@varchar"))’, ‘varbinary(max)’)
    return @decoded
end
go

 

–驗證

declare @hash binary(20)=HASHBYTES(‘sha1′,’Password’)
select @hash ‘雜湊編碼後的二進位資料’,
datalength(dbo.fnBase64Encode(@hash)) ‘Base64 編碼後的長度’
select dbo.fnBase64Encode(@hash) ‘Base64 編碼後內容’,
dbo.fnBase64Decode(dbo.fnBase64Encode(@hash)) ‘Base64 解碼後原始資料’

結果如下:

image

加任意 bytes 數量的亂數值

use tempdb
go
CREATE VIEW rndView
AS
SELECT RAND() rndResult
GO

create function RandBytes
(
    @ByteLength int
)
returns varbinary(max)
as
begin
    declare @ret varbinary(max)=0x,@i int=0,@rndValue tinyint
    while @i<@ByteLength
    begin
        select @rndValue = convert(tinyint,rndResult*256)
        from rndView
        set @ret+=convert(binary(1),@rndValue)
        set @i+=1
    end
    return @ret
end
GO

create table #t(c1 nvarchar(10))
insert #t values(N’中文’),(N’測試一下’)

select dbo.RandBytes(10)+convert(varbinary(max),c1),
substring(dbo.RandBytes(10)+convert(varbinary(max),c1),11,datalength(convert(varbinary(max),c1))),
convert(nvarchar(max),substring(dbo.RandBytes(10)+convert(varbinary(max),c1),11,datalength(convert(varbinary(max),c1))))
from #t

drop table #t

嘗試模擬 cume_dist 和 percent_rank 的定義

with v(Part,ID) as (select * from (values(1,null),(1,0),(1,1),(1,1),(1,3),
(2,1),(2,1),(2,3),(2,4)) t(Part,ID)),
maxRank(Part,ID,RankNO,IDCount) as (select Part,ID,RANK() OVER
(PARTITION BY Part ORDER BY ID )-1 AS [RankNo],
case when lag(id,1) over(order by Part,ID)=id then 0 else count(*) –CUME_DIST 有計算 null,所以這要 count(*) 否則是 count(id)
over(partition by Part,ID) end IDCount  from v)
SELECT Part,ID,
IDCount,
–‘累計到當下的筆數除以總紀錄數,但相同大小的紀錄,其筆數相同’,
sum(IDCount) over (PARTITION BY Part order by id rows between unbounded preceding and current row) as Cumulate,
convert(float,sum(IDCount) over (PARTITION BY Part order by id rows between unbounded preceding and current row))/count(*) over(partition by part) myCumeDist,
CUME_DIST () OVER (PARTITION BY Part ORDER BY ID) AS CumeDist,RankNo,

Max(RankNo) OVER (Partition By Part) [Max],
Convert(Float,RankNo)/Max(RankNo) OVER (Partition By Part) myPercentRank,
PERCENT_RANK() OVER (PARTITION BY Part ORDER BY ID ) AS PctRank
FROM maxRank

為已授權的資料庫內物件建立T-SQL語法

use northwind
declare @sp sysname
set @sp=’orders’
  declare @permissions nvarchar(max) = “

  select @permissions += dp.state_desc + N’ ‘ + dp.permission_name + N’ ON ‘ +
       quotename(schema_name(o.schema_id)) + N’.’ + quotename(o.name) + N’ TO ‘ +
       quotename(u.name) + N’; ‘ + char(13)
    from sys.database_permissions as dp
    join sys.database_principals as u
      on u.principal_id = dp.grantee_principal_id
    join sys.objects as o
      on o.object_id = dp.major_id
  where dp.class = 1 /* object */
     and dp.minor_id = 0 and o.object_id=object_id(@sp);

  select @permissions

透過一句 SQL 建立時間資料表

–取自 Microsoft SQL Server 2012 Analysis Services: The BISM Tabular Model    P. 294

DECLARE @StartYear AS INT = 2000 ;
DECLARE @EndYear AS INT = 2006 ;

WITH Years
    AS ( SELECT YYYY = @StartYear
    UNION ALL
    SELECT YYYY + 1
    FROM Years
    WHERE YYYY < @EndYear
    ),
Months
    AS ( SELECT MM = 1
    UNION ALL
    SELECT MM + 1
    FROM Months
    WHERE MM < 12
    ),
Days
    AS ( SELECT DD = 1
    UNION ALL
    SELECT DD + 1
    FROM Days
    WHERE DD < 31
    ),
DatesRaw
    AS ( SELECT YYYY = YYYY,
    MM = MM,
    DD = DD,
    ID_Date = YYYY * 10000 + MM * 100 + DD,
    Date = CASE WHEN ISDATE(YYYY * 10000 + MM * 100 + DD) = 1
    THEN CAST(CAST(YYYY * 10000 + MM * 100
    + DD AS VARCHAR) AS DATE)
    ELSE NULL
    END
    FROM Years
    CROSS JOIN Months
    CROSS JOIN Days
    WHERE ISDATE(YYYY * 10000 + MM * 100 + DD) = 1
    )
SELECT d.ID_Date,
    d.Date,
    [Year] = YEAR(d.Date),
    MonthNumber = MONTH(d.Date),
    [Month] = DATENAME(MONTH, d.Date),
    DayOfMonth = DAY(d.Date),
    DayOfWeekNumber = DATEPART(dw, d.Date),
    [DayOfWeek] = DATENAME(dw, d.Date),
    WorkingDay = CAST(CASE DATEPART(dw, d.Date)
    WHEN 1 THEN 0 — Sunday
    WHEN 7 THEN 0 — Saturday
    ELSE 1 — Might lookup for a holidays table here
    END AS BIT)
FROM DatesRaw d
ORDER BY d.Date

T-SQL 查詢注意事項

小妙針對 SQL 2008 R2 以前,依工作經驗,寫了篇不錯的 Blog:T-SQL效能

因為她工作環境尚未用 SQL Server 2012,所以未提及 Windowing 的好處 🙂

同一連接(Session)建的暫存物件都在 tempdb,故無法用 schema

Ellie 提了一個有趣的問題:請問Temporary table是不是沒有schema的概念?

在不同schema下,無法create 同一名稱的temp table

Use TempDB

GO

Create schema schema_aa

GO

Create schema schema_bb

GO

create table schema_aa.#TT (C1 int)

GO

create table schema_bb.#TT (C1 int)

GO

–Msg 2714, Level 16, State 6, Line 2

–There is already an object named ‘#TT’ in the database..

 

然後,四節名稱我亂打,居然也都能select出結果 …

四節名稱隨便打,卻不影響SQL解析temporary物件名稱 => 請問這是什麼原理呢?

image

 

我的想法是:同一連接(Session)建的暫存物件都在 tempdb,故無法用 schema

因為同一連接可以切換不同 Context DB,而 Schema 分別隸屬個別 DB,但暫存物件都實際建在 TempDB,則 TempDB 很難維護分屬其他 DB 和 TempDB 自己的 Schema。稍微添加一點 Ellie 的範例進一步來檢視:

Use TempDB
GO
 
Create schema schema_aa
GO
Create schema schema_bb
GO

–非暫存物件,在 tempdb 也可以用 schema
create table schema_aa.TT (C1 int)
GO
create table schema_aa.#TT (C1 int)
GO
create table schema_aa.##TT (C1 int)
go

create table schema_bb.#TT (C1 int)
GO
–Msg 2714, Level 16, State 6, Line 2
–There is already an object named ‘#TT’ in the database..
 
 
select schema_name(schema_id),* from sys.tables
go
create proc schema_aa.sp
as
print ‘schema_aa.sp’
go

–暫存 sp 的狀況與暫存 table 相同
create proc schema_aa.#sp
as
print ‘schema_aa.#sp’
go

select schema_name(schema_id),* from sys.procedures

use northwind
go

create schema s
go

create table s.#t(c1 int)
create table s.##t(c1 int)
go

–因為沒有 schema,且也是建在 tempdb,所以會跟先前建立的 sp 相衝
–訊息 2714,層級 16,狀態 3,程序 #sp,行 1
–資料庫中已經有一個名為 ‘#sp’ 的物件。
create proc s.#sp
as
print ‘s.#sp’
go

select schema_name(schema_id),* from sys.tables
select schema_name(schema_id),* from tempdb.sys.tables

 

 

Ellie:

所以暫存物件也就沒有所謂的四節名稱了嗎?

 

我:

照你的 Sample 看起來是 temp 物件的名稱解析不管物件名稱之前的前三節。

從另一個面向來想,Session 內的任和暫存物件都在 tempdb,那何必在乎後三節。而第一節的 Instance 名稱,我尚想不到如何在遠端 Instance,透過相同的 Session 針對 Linked Server 建立 temp 物件,然後再以四節名稱使用它。

用 UNION 取代 OR

巧玫 mail 了一個有趣的連結

http://sqlserverplanet.com/optimization/using-union-instead-of-or

/*

總紀錄 704 筆

SQL Server 剖析與編譯時間:
CPU 時間 = 15 ms,經過時間 = 18 ms。

(704 個資料列受到影響)
資料表 ‘SalesOrderDetail’。掃描計數 2,邏輯讀取 2179,實體讀取 0,讀取前讀取 0,LOB 邏輯讀取 0,LOB 實體讀取 0,LOB 讀取前讀取 0。

SQL Server 執行次數:
,CPU 時間 = 0 ms,經過時間 = 201 ms。

*/
SELECT *
FROM sales.SalesOrderDetail
WHERE ProductID = 750
UNION
SELECT *
FROM sales.SalesOrderDetail
WHERE ProductID = 953

/*
SQL Server 剖析與編譯時間:
CPU 時間 = 0 ms,經過時間 = 0 ms。

(704 個資料列受到影響)
資料表 ‘SalesOrderDetail’。掃描計數 1,邏輯讀取 1246,實體讀取 0,讀取前讀取 0,LOB 邏輯讀取 0,LOB 實體讀取 0,LOB 讀取前讀取 0。

SQL Server 執行次數:
,CPU 時間 = 47 ms,經過時間 = 234 ms。
*/
SELECT *
FROM sales.SalesOrderDetail
WHERE ProductID = 750 OR ProductID = 953

image

 

因為 OR 造成 SQL Server 判讀符合的紀錄較多,認定需要 Scan table,但 Union 是個別判讀獨自兩句,各自認定符合的紀錄少,因此 Seek 比較好

透過 set statistics io on 與 set statistics time on 與兩句話加在一起比執行計畫:

總 IO 數是 union 較多,所以就執行計畫的比較,SQL Server 認定 OR 比較好。但因為 OR 的 Filter 較耗 CPU,所以真實的總時間上 Union 較好。且因為 Union 個別執行較省 CPU,所以多人同時存取時,也比較好。