搜尋本站文章

2018-07-14

[SQL Server] CREATE OR ALTER - New enhancements in SQL Server 2016 SP1



SQL Server 2016 (13.x) SP1 與 Azure SQL Database 開始支援 CREATE OR ALTER

  • 僅需 1 句 T-SQL,即可自動判斷是該 CREATE 或 ALTER 此 Stored Procedure。
  • 無需額外撰寫判斷式去檢查是否存在於 sys.objects 中。


CREATE OR ALTER 支援以下物件:

  • STORED PROCEDURES
  • FUNCTIONS
  • TRIGGERS
  • VIEWS



不受限於 COMPATIBILITY_LEVEL。




CREATE OR ALTER - New enhancements in SQL Server 2016 SP1




01. 僅需 1 句 T-SQL,即可自動判斷是該 CREATE 或 ALTER 此 Stored Procedure。


  • CREATE OR ALTER PROC
  • 無需額外撰寫判斷式去檢查是否存在於 sys.objects 中。
  • SQL Server 2016 (13.x) SP1 支援。


-- figure 01_CREATE OR ALTER PROC






02. 精簡的判斷式,但仍需額外再寫 CREATE PROC。


  • DROP PROCEDURE IF EXISTS
  • 無需增加判斷式去檢查是否存在於 sys.objects 中。
  • SQL Server 2016 (13.x) 支援。


-- figure 02_DROP PROCEDURE IF EXISTS





03. 額外撰寫判斷式去檢查是否存在於 sys.objects 中。SQL Server 2016 之前的語法。

-- figure 03_EXISTS_sys_objects



04. 綜合來看:


  • SQL Server 2016 (13.x) SP1 支援的 CREATE OR ALTER ,最為簡潔。


-- figure 04_Compare_All






CREATE OR ALTER 支援的物件


CREATE OR ALTER 支援以下物件:

  • STORED PROCEDURES
  • FUNCTIONS
  • TRIGGERS
  • VIEWS


01. CREATE OR ALTER PROC

-- figure 11_CREATE OR ALTER PROC




02. CREATE OR ALTER FUNCTION

-- figure 12_CREATE OR ALTER FUNCTION




03. CREATE OR ALTER VIEW


-- figure 13_CREATE OR ALTER VIEW




04. CREATE OR ALTER TRIGGER


-- figure 14_CREATE OR ALTER TRIGGER






Sample Code


20180714_CREATE OR ALTER
https://drive.google.com/drive/folders/0B9PQZW3M2F40OTk3YjI2NTEtNjUxZS00MjNjLWFkYWItMzg4ZDhhMGIwMTZl?usp=sharing





Reference

CREATE OR ALTER – another great language enhancement in SQL Server 2016 SP1
https://blogs.msdn.microsoft.com/sqlserverstorageengine/2016/11/17/create-or-alter-another-great-language-enhancement-in-sql-server-2016-sp1/

Update introduces CREATE OR ALTER Transact-SQL statement in SQL Server 2016
https://support.microsoft.com/en-us/help/3190548/update-introduces-create-or-alter-transact-sql-statement-in-sql-server

2018-07-12

[SQL Server] 1,700 bytes and 32 columns for nonclustered index


SQL Server 2016 (13.x) 與 Azure SQL Database 增強 Nonclustered Index(非叢集索引):

  • 資料長度延展為 1,700 Bytes,由 32 個 Column 組成。


先前版本是:

  • 資料長度為 900 Bytes,由 16 個 Column 組成。


Clustered Index(叢集索引) 仍是 900 Bytes。

若有需要超過,請改用 Included Columns (內含資料行的索引)



[SQL Server] 1,700 bytes and 32 columns for nonclustered index


01. 建立資料表: NonCL_Size


  • NCL VARCHAR(2048)


並建立 NonClustered Index

-- figure 11_Create NonClustered Index_Maximum_1700



Warning! The maximum key length for a nonclustered index is 1700 bytes. 
The index 'ix_NCL' has maximum length of 2048 bytes. 

For some combination of large values, the insert/update operation will fail.


02. 輸入資料:


  • 第 1 筆,資料長度是 1700 bytes,沒有超過 Nonclustered Indexes 上限。
  • 第 2 筆,資料長度是 1701 bytes,超過 Nonclustered Indexes 上限,導致新增失敗。


-- figure 12_Operation failed_over_1700



Msg 1946, Level 16, State 3, Line 32
Operation failed. 

The index entry of length 1701 bytes for the index 'ix_NCL' exceeds the maximum length of 1700 bytes for nonclustered indexes.


03. 查詢資料表,確認僅 第 1 筆 輸入成功。


-- figure 13_Only 1 row




04.  建立資料表: CL_Size


  • CL VARCHAR(1024)


建立 Clustered Index

-- figure 21_Create Clustered Index_Maximum_900



Warning! The maximum key length for a clustered index is 900 bytes. 
The index 'ix_CL' has maximum length of 1024 bytes. 

For some combination of large values, the insert/update operation will fail.


05. 輸入資料:


第 1 筆,資料長度是 900 bytes,沒有超過 Clustered Indexes 上限。
第 2 筆,資料長度是 901 bytes,超過 Clustered Indexes 上限,導致新增失敗。


-- figure 22_Operation failed_over_900



Msg 1946, Level 16, State 3, Line 72
Operation failed. 

The index entry of length 901 bytes for the index 'ix_CL' exceeds the maximum length of 900 bytes for clustered indexes.


06. 查詢資料表,確認僅 第 1 筆 輸入成功。

-- figure 23_Only 1 row






SQL Server 2016 與 Azure SQL Database 增強 nonclustered index(非叢集索引),將資料長度延展為 1,700 Bytes,先前版本是 900 Bytes。

Clustered Index(叢集索引)為 900 個位元組數。 Nonclustered Index(非叢集索引) 為 1,700 Bytes。

您可以使用大小上限增加超過限制的可變長度資料行,來定義索引鍵。
不過,這些資料行中的資料大小總和不得超過限制。

在非叢集索引中,您可以包含額外的非索引鍵資料行,這些資料行不會計入索引鍵的大小限制。
非索引鍵資料行可能有助於提升某些查詢的執行效能。




Sample Code


20180712_1700byte_32column_Nonclustered
https://drive.google.com/drive/folders/0B9PQZW3M2F40OTk3YjI2NTEtNjUxZS00MjNjLWFkYWItMzg4ZDhhMGIwMTZl?usp=sharing





Reference

Maximum Capacity Specifications for SQL Server
https://docs.microsoft.com/en-us/sql/sql-server/maximum-capacity-specifications-for-sql-server?view=sql-server-2017

Increased nonclustered index key size with SQL Server 2016
https://blogs.msdn.microsoft.com/sqlserverstorageengine/2016/04/26/increased-nonclustered-index-key-size-with-sql-server-2016/

Create Indexes with Included Columns
https://docs.microsoft.com/en-us/sql/relational-databases/indexes/create-indexes-with-included-columns?view=sql-server-2017