SQL Server execution plan with convert implicit 2

env: Windows Server 2016
        SQL Server 2016 SP2

這次一樣遇到隱含轉換的問題，與上次比較不同是:
上次是variable table宣告的欄位資料型態造成，這次就是實體Table造成。

這次遇到的問題除了隱含轉換，還有CPU使用的問題。
這次最大的表有近20億筆。在同一台server跨DB join 4張表。

1.調整前執行
訊息:
Type conversion in expression ([XXXX].[dbo].[XXXX].[XXXX]=CONVERT(nvarchar(512),[XXXX].[dbo].[XXXX].[XXXX],0)) may affect "SeekPlan" in query plan choice, Type conversion in expression ([XXXX].[dbo].[XXXX].[XXXX]=CONVERT(nvarchar(512),[XXXX].[dbo].[XXXX].[XXXX],0)) may affect "SeekPlan" in query plan choice

雖然查詢的語法有使用MAXDOP=4，但是可以發現過程中也耗損不少CPU


















2.調整表的欄位資料型態，將VARCHAR改為NVARCHAR
command:
ALTER TABLE dbo.TESTTB01 ALTER COLUMN Token NVARCHAR(512) NULL
GO
ALTER TABLE dbo.TESTTB02 ALTER COLUMN Token NVARCHAR(512) NULL
GO


3.再次執行語法
我們只執行select部分，將insert into語句移除。

我們可以發現調整後CPU不再需要大量去做型別轉換。只是有個疑問，執行時間稍微拉長?
但是使用者還可以接受


















4.從以下官網得知資料型態優先順序
Data type precedence (Transact-SQL)

NVARCHAR優先順序高於VARCHAR，所以VARCHAR會轉會為NVARCHAR。
另外個人覺得還可以優化的部分是nvarchar(512)，裡面放的資料不到nvarchar(100)。

SQL Server uses the following precedence order for data types:

1. user-defined data types (highest)
2. sql_variant
3. xml
4. datetimeoffset
5. datetime2
6. datetime
7. smalldatetime
8. date
9. time
10. float
11. real
12. decimal
13. money
14. smallmoney
15. bigint
16. int
17. smallint
18. tinyint
19. bit
20. ntext
21. text
22. image
23. timestamp
24. uniqueidentifier
25. nvarchar (including nvarchar(max) )
26. nchar
27. varchar (including varchar(max) )
28. char
29. varbinary (including varbinary(max) )
30. binary (lowest)

SQL Server execution plan with convert implicit

env: Windows Server 2016

         SQL Server 2016 SP2

最近幫忙看報表語法，發現執行計劃常出現以下警告訊息:
Type conversion in expression ([i].[Item]=CONVERT_IMPLICIT(nvarchar(64),[XXXXX].[XXXXX],0)) may affect "SeekPlan" in query plan choice, Type conversion in expression ([i].[Item]=CONVERT_IMPLICIT(nvarchar(32),[XXXXX].[XXXXX],0)) may affect "SeekPlan" in query plan choice

造成這個原因是等號兩邊資料型態不一致的關係。

比對語法使用的實體TABLE欄位資料型態都是一致的，

以這次的例子發生的原因是宣告的variable table造成:
DECLARE @TESTTB02 TABLE(Item NVARCHAR(64));
       
DECLARE @TESTTB02 TABLE(Item NVARCHAR(32));

解決方式是將NVARCHAR改為VARCHAR，隱含轉換的警告就消失了。
DECLARE @TESTTB02 TABLE(Item VARCHAR(64));
       
DECLARE @TESTTB02 TABLE(Item VARCHAR(32));

SQL Server clone database

env:Windows Server 2016
       SQL Server 2016 SP2
       SQL Server 2016 SP1

以前複製資料庫有2種方式，一個用snapshot，一個使用備份檔。
SQL Server 2012提供一個複製資料庫的指令:dbcc clonedatabase。
這個指令陸續都有增加新的參數，可以參考此連結:DBCC CLONEDATABASE (Transact-SQL)

我們使用SQL 2016來測試幾個功能。
複製的DB約有200GB

1.進行複製資料庫，不帶參數
command:
DBCC CLONEDATABASE (TESTDB01_20190119, TESTDB01_20190119_Clone);    
GO

複製資料庫的過程十分快速，而且複製出來的DB空間很小

複製的資料庫預設是"read-only"
可以利用以下指令變更為"read-write"
ALTER DATABASE UgsDataWarehouse_20190119_Clone SET READ_WRITE









2.可以透過以下指令檢查此資料庫是否為被複製出來的。
command:
USE [master]
GO
SELECT DATABASEPROPERTYEX('TESTDB01_20190119_Clone', 'IsClone')
GO

3.參數"VERIFY_CLONEDB"是SQL Server 2016 SP2的新功能
command:
DBCC CLONEDATABASE (TESTDB01_20190119, TESTDB01_20190119_Clone_1) WITH VERIFY_CLONEDB;    
GO

假如在SQL Server 2016 SP1執行會出現以下錯誤:









4.嘗試在SQL Server 2016 SP2使用參數"VERIFY_CLONEDB"
command:
DBCC CLONEDATABASE (TBTEST03, TBTEST03_CLONE) WITH VERIFY_CLONEDB;    
GO

errorlog內容:
DBCC CHECKDB (TBTEST03_CLONE) WITH no_infomsgs executed by sa found 0 errors and repaired 0 errors. Elapsed time: 0 hours 0 minutes 0 seconds.  Internal database snapshot has split point LSN = 00000022:00000262:0001 and first LSN = 00000022:00000260:0001


5.檢查複製的資料庫在過程中是否有被verify
command:
USE [master]
GO
SELECT DATABASEPROPERTYEX('TBTEST03_CLONE', 'IsVerifiedClone')
GO

6.在複製指令中加入參數"VERIFY_CLONEDB"與"BACKUP_CLONEDB"
"BACKUP_CLONEDB"這個參數會在複製資料庫後進行備份，這個功能是在SQL Server 2016 SP2開始
command:
DBCC CLONEDATABASE (TBTEST03, TBTEST03_CLONE_1) WITH VERIFY_CLONEDB, BACKUP_CLONEDB;    
GO

errorlog內容，時間由近到遠:

CHECKDB for database 'TBTEST03_CLONE_1' finished without errors on 2019-01-19 09:40:35.853 (local time). This is an informational message only; no user action is required.

Database backed up. Database: TBTEST03_CLONE_1, creation date(time): 2019/01/19(09:40:34), pages dumped: 354, first LSN: 34:576:37, last LSN: 34:595:1, number of dump devices: 1, device information: (FILE=1, TYPE=DISK: {'E:\DBBackup\TBTEST03_CLONE_1_1176299034.bak'}). This is an informational message only. No user action is required.

CHECKDB for database 'TBTEST03_CLONE_1' finished without errors on 2019-01-19 09:40:35.853 (local time). This is an informational message only; no user action is required.

CHECKDB for database 'TBTEST03_CLONE_1' finished without errors on 2019-01-19 09:40:35.853 (local time). This is an informational message only; no user action is required.

DBCC CHECKDB (TBTEST03_CLONE_1) WITH no_infomsgs executed by sa found 0 errors and repaired 0 errors. Elapsed time: 0 hours 0 minutes 0 seconds.  Internal database snapshot has split point LSN = 00000022:00000233:0001 and first LSN = 00000022:00000231:0001.

DBCC CHECKDB (TBTEST03_CLONE_1) WITH no_infomsgs executed by sa found 0 errors and repaired 0 errors. Elapsed time: 0 hours 0 minutes 0 seconds.  Internal database snapshot has split point LSN = 00000022:00000233:0001 and first LSN = 00000022:00000231:0001.

Database 'TBTEST03_CLONE_1' is a cloned database. This database should be used for diagnostic purposes only and is not supported for use in a production environment.


結論:
這個功能方便使用，但是官方也提供以下警語。
這個是使用資料庫內部的快照功能來進行複製所以還是會產生LOCK。若要在生產環境使用，還是請再三評估。

DBCC CLONEDATABASE uses an internal database snapshot of the source database for the transactional consistency that is needed to perform the copy. Using this snapshot prevents blocking and concurrency problems when these commands are executed. If a snapshot can't be created, DBCC CLONEDATABASE will fail.

Database level locks are held during following steps of the copy process:

Validate the source database

Get S lock for the source database

Create snapshot of the source database

Create a clone database (an empty database inherited from the model database)

Get X lock for the clone database

Copy the metadata to the clone database

Release all DB locks

As soon as the command has finished running, the internal snapshot is dropped. TRUSTWORTHY and DB_CHAINING options are turned off on a cloned database.

SQL Server how to combine column data to string

env:Windows Server 2016
       SQL Server 2016 SP2

之前遇到一個需求，把欄位的資料合併為一個字串，每個資料需要用逗號分隔。
這次我們直接組出語法

1.利用FOR XML語法來組出結合字串的語法
command:
SELECT
(SELECT CAST([Name] AS NVARCHAR) + ',' FROM [dbo].[TETTB01] for xml path(''))
FROM [dbo].[TETTB01]

2.字串已經組合出來並且是以逗號做分隔，但是字串結尾也是逗號，這是接下來要處理的部分。移除字串結尾的逗號。
command:
SELECT 
LEFT((SELECT CAST([Name] AS NVARCHAR) + ',' FROM [dbo].[TETTB01] for xml path(''))
,LEN((SELECT CAST([Name] AS NVARCHAR) + ',' FROM [dbo].[TETTB01] for xml path('')))-1)
FROM [dbo].[TETTB01]

SQL Server find filename from directory column

env:Windows Server 2016
       SQL Server 2016 SP2

column裡面儲存的資料是檔案名稱與路徑，要怎麼順利把檔案名稱取出來?

1.建立存放測試資料的表
command:
CREATE TABLE #DirTree
(
subdirectory NVARCHAR(200),
depth INT,
isfile BIT
)

2.利用xp_dirtree放入資料到測試表
command:
INSERT INTO #DirTree (subdirectory, depth, isfile)
EXECUTE master.sys.xp_dirtree ' F:\DBBackup\dbserver03\Fullbackup\',1,1

3.再利用測試表的資料做出含有路徑檔名的完整測試資料
command:
SELECT 'F:\DBBackup\dbserver03\Fullbackup\' + subdirectory AS FullDirectory
INTO #DirTreeFull
FROM #DirTree

4.最後利用REVERSE與CHARINDEX這個function將檔名分離到一個獨立欄位
command:
SELECT *,
REVERSE(left(REVERSE(FullDirectory), CHARINDEX('\', REVERSE(FullDirectory)) - 1)) AS [FileName]
FROM #DirTreeFull

5.下面語法分離路徑與檔案
command:
SELECT *,
REVERSE(SUBSTRING(REVERSE(FullDirectory), CHARINDEX('\', REVERSE(FullDirectory)) + 1, LEN(FullDirectory))) AS [DirectoryName],
REVERSE(left(REVERSE(FullDirectory), CHARINDEX('\', REVERSE(FullDirectory)) - 1)) AS [FileName]
FROM #DirTreeFull