Tag Archives: T-SQL

透過 T-SQL Script 建立 Table 定義

Sorry,忘了來源出處,但為了讓朋友 Copy,所以貼在此處…

CREATE proc [ETL].[spCreateTable] @table_name SYSNAME,@On nvarchar(max)=",@SQLScript nvarchar(max) output
as
DECLARE
      @object_name SYSNAME
    , @object_id INT

SELECT
      @object_name = ‘[‘ + s.name + ‘].[‘ + o.name + ‘]’
    , @object_id = o.[object_id]
FROM CPADW.sys.objects o WITH (NOWAIT)
JOIN CPADW.sys.schemas s WITH (NOWAIT) ON o.[schema_id] = s.[schema_id]
WHERE s.name + ‘.’ + o.name = @table_name
    AND o.[type] = ‘U’
    AND o.is_ms_shipped = 0

DECLARE @SQL NVARCHAR(MAX) = “

;WITH index_column AS
(
    SELECT
          ic.[object_id]
        , ic.index_id
        , ic.is_descending_key
        , ic.is_included_column
        , c.name
    FROM CPADW.sys.index_columns ic WITH (NOWAIT)
    JOIN CPADW.sys.columns c WITH (NOWAIT) ON ic.[object_id] = c.[object_id] AND ic.column_id = c.column_id
    WHERE ic.[object_id] = @object_id
),
fk_columns AS
(
     SELECT
          k.constraint_object_id
        , cname = c.name
        , rcname = rc.name
    FROM CPADW.sys.foreign_key_columns k WITH (NOWAIT)
    JOIN CPADW.sys.columns rc WITH (NOWAIT) ON rc.[object_id] = k.referenced_object_id AND rc.column_id = k.referenced_column_id
    JOIN CPADW.sys.columns c WITH (NOWAIT) ON c.[object_id] = k.parent_object_id AND c.column_id = k.parent_column_id
    WHERE k.parent_object_id = @object_id
)
SELECT @SQL = ‘CREATE TABLE ‘ + @object_name + CHAR(13) + ‘(‘ + CHAR(13) + STUFF((
    SELECT CHAR(9) + ‘, [‘ + c.name + ‘] ‘ +
        CASE WHEN c.is_computed = 1
            THEN ‘AS ‘ + cc.[definition]
            ELSE UPPER(tp.name) +
                CASE WHEN tp.name IN (‘varchar’, ‘char’, ‘varbinary’, ‘binary’, ‘text’)
                       THEN ‘(‘ + CASE WHEN c.max_length = -1 THEN ‘MAX’ ELSE CAST(c.max_length AS VARCHAR(5)) END + ‘)’
                     WHEN tp.name IN (‘nvarchar’, ‘nchar’, ‘ntext’)
                       THEN ‘(‘ + CASE WHEN c.max_length = -1 THEN ‘MAX’ ELSE CAST(c.max_length / 2 AS VARCHAR(5)) END + ‘)’
                     WHEN tp.name IN (‘datetime2’, ‘time2’, ‘datetimeoffset’)
                       THEN ‘(‘ + CAST(c.scale AS VARCHAR(5)) + ‘)’
                     WHEN tp.name = ‘decimal’
                       THEN ‘(‘ + CAST(c.[precision] AS VARCHAR(5)) + ‘,’ + CAST(c.scale AS VARCHAR(5)) + ‘)’
                    ELSE "
                END +
                CASE WHEN c.collation_name IS NOT NULL THEN ‘ COLLATE ‘ + c.collation_name ELSE " END +
                CASE WHEN c.is_nullable = 1 THEN ‘ NULL’ ELSE ‘ NOT NULL’ END +
                CASE WHEN dc.[definition] IS NOT NULL THEN ‘ DEFAULT’ + dc.[definition] ELSE " END +
                CASE WHEN ic.is_identity = 1 THEN ‘ IDENTITY(‘ + CAST(ISNULL(ic.seed_value, ‘0’) AS CHAR(1)) + ‘,’ + CAST(ISNULL(ic.increment_value, ‘1’) AS CHAR(1)) + ‘)’ ELSE " END
        END + CHAR(13)
    FROM CPADW.sys.columns c WITH (NOWAIT)
    JOIN CPADW.sys.types tp WITH (NOWAIT) ON c.user_type_id = tp.user_type_id
    LEFT JOIN CPADW.sys.computed_columns cc WITH (NOWAIT) ON c.[object_id] = cc.[object_id] AND c.column_id = cc.column_id
    LEFT JOIN CPADW.sys.default_constraints dc WITH (NOWAIT) ON c.default_object_id != 0 AND c.[object_id] = dc.parent_object_id AND c.column_id = dc.parent_column_id
    LEFT JOIN CPADW.sys.identity_columns ic WITH (NOWAIT) ON c.is_identity = 1 AND c.[object_id] = ic.[object_id] AND c.column_id = ic.column_id
    WHERE c.[object_id] = @object_id
    ORDER BY c.column_id
    FOR XML PATH(“), TYPE).value(‘.’, ‘NVARCHAR(MAX)’), 1, 2, CHAR(9) + ‘ ‘)
    + ISNULL((SELECT CHAR(9) + ‘, CONSTRAINT [‘ + k.name + ‘] PRIMARY KEY (‘ +
                    (SELECT STUFF((
                         SELECT ‘, [‘ + c.name + ‘] ‘ + CASE WHEN ic.is_descending_key = 1 THEN ‘DESC’ ELSE ‘ASC’ END
                         FROM CPADW.sys.index_columns ic WITH (NOWAIT)
                         JOIN CPADW.sys.columns c WITH (NOWAIT) ON c.[object_id] = ic.[object_id] AND c.column_id = ic.column_id
                         WHERE ic.is_included_column = 0
                             AND ic.[object_id] = k.parent_object_id
                             AND ic.index_id = k.unique_index_id    
                         FOR XML PATH(N"), TYPE).value(‘.’, ‘NVARCHAR(MAX)’), 1, 2, “))
            + ‘)’ + CHAR(13)
            FROM CPADW.sys.key_constraints k WITH (NOWAIT)
            WHERE k.parent_object_id = @object_id
                AND k.[type] = ‘PK’), “) + ‘) ‘ + @On + CHAR(13)
    + ISNULL((SELECT (
        SELECT CHAR(13) +
             ‘ALTER TABLE ‘ + @object_name + ‘ WITH’
            + CASE WHEN fk.is_not_trusted = 1
                THEN ‘ NOCHECK’
                ELSE ‘ CHECK’
              END +
              ‘ ADD CONSTRAINT [‘ + fk.name  + ‘] FOREIGN KEY(‘
              + STUFF((
                SELECT ‘, [‘ + k.cname + ‘]’
                FROM fk_columns k
                WHERE k.constraint_object_id = fk.[object_id]
                FOR XML PATH(“), TYPE).value(‘.’, ‘NVARCHAR(MAX)’), 1, 2, “)
               + ‘)’ +
              ‘ REFERENCES [‘ + SCHEMA_NAME(ro.[schema_id]) + ‘].[‘ + ro.name + ‘] (‘
              + STUFF((
                SELECT ‘, [‘ + k.rcname + ‘]’
                FROM fk_columns k
                WHERE k.constraint_object_id = fk.[object_id]
                FOR XML PATH(“), TYPE).value(‘.’, ‘NVARCHAR(MAX)’), 1, 2, “)
               + ‘)’
            + CASE
                WHEN fk.delete_referential_action = 1 THEN ‘ ON DELETE CASCADE’
                WHEN fk.delete_referential_action = 2 THEN ‘ ON DELETE SET NULL’
                WHEN fk.delete_referential_action = 3 THEN ‘ ON DELETE SET DEFAULT’
                ELSE "
              END
            + CASE
                WHEN fk.update_referential_action = 1 THEN ‘ ON UPDATE CASCADE’
                WHEN fk.update_referential_action = 2 THEN ‘ ON UPDATE SET NULL’
                WHEN fk.update_referential_action = 3 THEN ‘ ON UPDATE SET DEFAULT’ 
                ELSE "
              END
            + CHAR(13) + ‘ALTER TABLE ‘ + @object_name + ‘ CHECK CONSTRAINT [‘ + fk.name  + ‘]’ + CHAR(13)
        FROM CPADW.sys.foreign_keys fk WITH (NOWAIT)
        JOIN CPADW.sys.objects ro WITH (NOWAIT) ON ro.[object_id] = fk.referenced_object_id
        WHERE fk.parent_object_id = @object_id
        FOR XML PATH(N"), TYPE).value(‘.’, ‘NVARCHAR(MAX)’)), “)
    + ISNULL(((SELECT
         CHAR(13) + ‘CREATE’ + CASE WHEN i.is_unique = 1 THEN ‘ UNIQUE’ ELSE " END
                + ‘ NONCLUSTERED INDEX [‘ + i.name + ‘] ON ‘ + @object_name + ‘ (‘ +
                STUFF((
                SELECT ‘, [‘ + c.name + ‘]’ + CASE WHEN c.is_descending_key = 1 THEN ‘ DESC’ ELSE ‘ ASC’ END
                FROM index_column c
                WHERE c.is_included_column = 0
                    AND c.index_id = i.index_id
                FOR XML PATH(“), TYPE).value(‘.’, ‘NVARCHAR(MAX)’), 1, 2, “) + ‘)’ 
                + ISNULL(CHAR(13) + ‘INCLUDE (‘ +
                    STUFF((
                    SELECT ‘, [‘ + c.name + ‘]’
                    FROM index_column c
                    WHERE c.is_included_column = 1
                        AND c.index_id = i.index_id
                    FOR XML PATH(“), TYPE).value(‘.’, ‘NVARCHAR(MAX)’), 1, 2, “) + ‘)’, “)  + CHAR(13)
        FROM CPADW.sys.indexes i WITH (NOWAIT)
        WHERE i.[object_id] = @object_id
            AND i.is_primary_key = 0
            AND i.[type] = 2
        FOR XML PATH(“), TYPE).value(‘.’, ‘NVARCHAR(MAX)’)
    ), “)
set @SQLScript=@SQL

廣告

建立用來移轉帳號的 T-SQL 語法

SELECT    CONCAT(‘CREATE LOGIN [‘, name, ‘] WITH PASSWORD=’, CONVERT(varchar(256),password_hash,1), ‘ HASHED,SID=’,CONVERT(varchar(85),sid,1),’;’)
FROM    sys.sql_logins
WHERE    name IN (‘SQL 自家帳號 1′,’SQL 自家帳號 2’);

執行計畫快取的正確與否

回一位朋友的問題。

參照此篇: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 的好處 🙂