SQL Prompt是一款實(shí)用的SQL語法提示工具。SQL Prompt根據(jù)數(shù)據(jù)庫的對(duì)象名稱、語法和代碼片段自動(dòng)進(jìn)行檢索，為用戶提供合適的代碼選擇。自動(dòng)腳本設(shè)置使代碼簡(jiǎn)單易讀--當(dāng)開發(fā)者不大熟悉腳本時(shí)尤其有用。SQL Prompt安裝即可使用，能大幅提高編碼效率。此外，用戶還可根據(jù)需要進(jìn)行自定義，使之以預(yù)想的方式工作。

點(diǎn)擊下載SQL Prompt正式版

標(biāo)量UDF作為全局?jǐn)?shù)據(jù)庫常量的錯(cuò)誤使用是一個(gè)主要的性能問題，每當(dāng)SQL Prompt在任何生產(chǎn)代碼中發(fā)現(xiàn)此錯(cuò)誤時(shí)，都應(yīng)進(jìn)行調(diào)查。除非您需要在計(jì)算列或約束中使用這些全局常量，否則通常將值存儲(chǔ)在內(nèi)聯(lián)表值函數(shù)中或使用視圖會(huì)更安全，更方便。

開發(fā)人員傾向于期望能夠在數(shù)據(jù)庫中設(shè)置全局值，以提供諸如Pi的值之類的常量，或諸如稅率，語言，文件URN或URL之類的變量。用戶定義的標(biāo)量函數(shù)返回單個(gè)值，因此似乎提供了理想的方法。這對(duì)于不經(jīng)常執(zhí)行的功能以及處理相對(duì)較小的數(shù)據(jù)集是很好的，但是在其他情況下，它可能會(huì)導(dǎo)致嚴(yán)重的查詢性能問題。出現(xiàn)問題是因?yàn)镾QL Server不相信未經(jīng)模式驗(yàn)證的標(biāo)量函數(shù)是精確的和確定性的，因此在執(zhí)行它們時(shí)會(huì)選擇最安全（盡管最慢）的選項(xiàng)。

調(diào)用具有BEGIN…END塊的任何SQL Server函數(shù)都會(huì)產(chǎn)生開銷，因?yàn)槌窃试SSQL Server通過使用架構(gòu)綁定創(chuàng)建該函數(shù)來驗(yàn)證其輸出，否則它將在過濾數(shù)據(jù)之前重新為每一行執(zhí)行該函數(shù)。 ，即使您很明顯每次都會(huì)返回相同的值。這是一個(gè)比較隱蔽的問題，盡管擴(kuò)展事件會(huì)話將揭示實(shí)際發(fā)生的情況，但是它并沒有真正顯示其在執(zhí)行計(jì)劃中的全部意義。

簡(jiǎn)而言之，請(qǐng)勿在JOIN條件，WHERE搜索條件或SELECT列表中使用標(biāo)量用戶定義函數(shù)（UDF），除非該函數(shù)是架構(gòu)綁定的。SQL Prompt實(shí)現(xiàn)了靜態(tài)代碼分析規(guī)則PE017，該規(guī)則專門用于幫助您檢測(cè)和糾正此問題。除非您對(duì)模式綁定及其對(duì)數(shù)據(jù)庫更改的后果有信心，否則最好使用將值傳輸?shù)阶兞浚蛘呤褂弥T如視圖或內(nèi)聯(lián)表值函數(shù)之類的模塊。

解決問題

如果SQL Prompt檢測(cè)到您的代碼出現(xiàn)PE017，該怎么辦？

通過添加架構(gòu)綁定以確保其經(jīng)過系統(tǒng)驗(yàn)證，可以正確使用標(biāo)量函數(shù)。清單1創(chuàng)建了相同的簡(jiǎn)單Wordcount函數(shù)的兩個(gè)版本，首先不帶模式綁定，然后帶模式綁定，這兩個(gè)版本都簡(jiǎn)單地返回一個(gè)常量。在每一種情況下，我們檢查IsDeterministic，IsPrecise以及IsSystemVerified屬性值對(duì)每個(gè)對(duì)象。

最后，它創(chuàng)建了第三個(gè)版本，該版本僅返回其參數(shù)值，僅用于檢查這是否是SQL Server驗(yàn)證過程中的一個(gè)因素。

IF Object_Id('dbo.Wordcount') IS NOT NULL DROP FUNCTION dbo.Wordcount
GO
 
CREATE FUNCTION dbo.Wordcount()
/**
Summary: >
A simple scalar multi-statement function
without schemabinding that returns a constant
Author: PhilFactor
Date: 01/02/2018
Returns: >
  the integer value 5
**/
RETURNS INT
AS
  BEGIN
    RETURN 5
  END
GO
 
/* we now test it to see whether SQL Server trusts it */
SELECT ObjectPropertyEx(
         Object_Id('dbo.Wordcount'), N'IsDeterministic') AS deterministic;
 
--Is a precise number returned? Whenever floating point operations are used in resolving 
--expressions, the results are not precise, by the very nature of the way that the datatype is stored. 
SELECT ObjectPropertyEx(Object_Id('dbo.Wordcount'), N'IsPrecise') AS precise;
 
--Can SQL Server verify that the function is precise and deterministic?
SELECT ObjectPropertyEx(
         Object_Id('dbo.Wordcount'), N'IsSystemVerified') AS verified;
GO
 
IF Object_Id('dbo.WordcountSchemaBound') IS NOT NULL DROP FUNCTION dbo.WordcountSchemaBound
GO
 
CREATE FUNCTION dbo.WordcountSchemaBound()
/**
Summary: >
A second version of a simple scalar multi-statement function
with schemabinding that returns a constant
Author: PhilFactor
Date: 01/02/2018
Returns: >
  the integer value 5
**/
RETURNS INT
WITH SCHEMABINDING
AS
  BEGIN
    RETURN 5
  END
GO
 
/* Repeat tests to see if SQL Server trusts dbo.WordcountSchemaBound*/
SELECT ObjectPropertyEx(
         Object_Id('dbo.WordcountSchemaBound'), N'IsDeterministic') AS deterministic;
 
SELECT ObjectPropertyEx(
         Object_Id('dbo.WordcountSchemaBound'), N'IsPrecise') AS precise;
 
SELECT ObjectPropertyEx(
         Object_Id('dbo.WordcountSchemaBound'), N'IsSystemVerified') AS verified;
GO
 
IF Object_Id('dbo.Wordcounter') IS NOT NULL DROP FUNCTION dbo.Wordcounter
GO
 
CREATE FUNCTION dbo.Wordcounter
  /**
Summary: >
A third version of a simple scalar multi-statement function
without schemabinding that merely returns its parameter
To test whether the absense of any parameter is a determining
Factor -- whether adding a parameter here gets round the problem
Author: PhilFactor
Returns: >
  the integer value passed to it
**/
  (@howMany INT)
RETURNS INT
AS
  BEGIN
    RETURN @howMany
  END
GO
 
/* Repeat tests to see if SQL Server trusts dbo.Wordcounter*/
SELECT ObjectPropertyEx(
         Object_Id('dbo.Wordcounter'), N'IsDeterministic') AS deterministic;
 
SELECT ObjectPropertyEx(Object_Id('dbo.Wordcounter'), N'IsPrecise') AS precise;
 
SELECT ObjectPropertyEx(
         Object_Id('dbo.Wordcounter'), N'IsSystemVerified') AS verified;
GO

如果運(yùn)行清單1，您將看到該函數(shù)的第二個(gè)版本W(wǎng)ordCountSchemaBound，對(duì)這三個(gè)屬性返回true。稍后我們將看到這對(duì)調(diào)用這些函數(shù)的所有查詢的性能有多大影響。

盡管模式綁定具有許多優(yōu)點(diǎn)，但是在這種情況下，這意味著您將被明確禁止將常量視為變量，這并不是一件壞事。如果您更改了“常量”函數(shù)（即您已在表中的約束或計(jì)算列中使用的函數(shù)），它將被證明很復(fù)雜。另外，如果嘗試在數(shù)據(jù)庫工作時(shí)更改常量，則使用正在執(zhí)行的功能的計(jì)劃將在功能上放置模式穩(wěn)定性鎖，這將防止您更改常量的值，因?yàn)樗鼈冃枰哪Ｊ健ｆi。

標(biāo)量UDF的替代品

清單2展示了標(biāo)量UDF的幾種替代選擇，它們可以在不需要或不想對(duì)其進(jìn)行模式綁定的情況下保存數(shù)據(jù)庫范圍的值。首先是視圖，然后是表值函數(shù)。

IF Object_Id('dbo.WordCountView') IS NOT NULL DROP VIEW dbo.WordCountView
GO
CREATE VIEW dbo.WordCountView
AS
/**
Summary: >
A very simple view that returns a single row with one column
Author: PhilFactor
Date: 01/02/2018
Returns: >
  a single row with a column called 'wordcount'
**/
SELECT 5 AS wordcount
GO
IF Object_Id('dbo.WordCountTVF') IS NOT NULL DROP FUNCTION dbo.WordCountTVF
GO
CREATE FUNCTION dbo.WordCountTVF()
/**
Summary: >
A table valued function that returns a single 
row with a column called 'wordcount' 
Author: PhilFactor
Date: 01/02/2018
Returns: >
  a single row with a column called 'wordcount'
**/
RETURNS TABLE
AS
RETURN
  (SELECT 5 AS wordcount)
GO

視圖定義中引用的對(duì)象不能以使視圖定義非法或強(qiáng)制SQL Server在視圖上重新創(chuàng)建索引的方式進(jìn)行更改。

盡管有額外的CHECK約束保護(hù)，但我沒有使用表來保存常量的方法。表格的設(shè)計(jì)并非一成不變！作為破壞者，我會(huì)告訴您它們的表現(xiàn)和視圖一樣好。

性能測(cè)試

排列所有候選解決方案后，讓我們看看它們的性能如何。我將測(cè)試每個(gè)選項(xiàng)能多快找出英語中常用的五個(gè)字母的單詞。這些測(cè)試要求我們創(chuàng)建一個(gè)簡(jiǎn)單Commonwords表，其中包含所有常見單詞的單列（主鍵）。要填充它，您需要下載commonwords 文件，然后運(yùn)行清單3，并使用正確的文件路徑。

DECLARE @AllCommonWords XML =
          (SELECT * FROM OpenRowset(BULK 'C:\MyPath\commonwords.XML',
                           SINGLE_BLOB) AS x );
 
IF Object_Id('commonwords', 'U') IS NOT NULL DROP TABLE commonwords;
 
CREATE TABLE commonwords (word VARCHAR(40) NOT NULL PRIMARY KEY);
 
INSERT INTO commonwords(word)
  SELECT word = word.value('@el', 'varchar(40)')
    FROM @AllCommonWords.nodes('/commonwords/row') AS CommonWords(word);

對(duì)于計(jì)時(shí)，我將使用我的文章如何使用SQL Prompt片段記錄T-SQL執(zhí)行時(shí)間中描述的簡(jiǎn)單測(cè)試工具。

-- create a temporary table variable to hold timings 
DECLARE @log TABLE
  (
  TheOrder INT IDENTITY(1, 1),
  WhatHappened VARCHAR(200),
  WhenItDid DATETIME2 DEFAULT GetDate()
  )
 
----start of timing
INSERT INTO @log(WhatHappened) SELECT 'Starting the test run'--place at the start
 
--first we see how fast it is with a literal number, as a benchmark
 SELECT Count(*) FROM commonWords WHERE Len(word)=5
INSERT INTO @log(WhatHappened) SELECT 'simple Query with literal number'--place at the start
 
--then see how long it takes if you transfer the 'constant' to a local variable
 DECLARE @NumberOfLetters INT=dbo.wordcount()
 SELECT Count(*) FROM commonWords WHERE Len(word)=@NumberOfLetters
INSERT INTO @log(WhatHappened) SELECT 'Same query but with value transferred from UDF to variable '
 
--Now we see how long with the scalar UDF that just passes the value back
 SELECT Count(*) FROM commonWords WHERE Len(word)=dbo.wordcounter(5)
iNSERT INTO @log(WhatHappened) SELECT 'Same but using a scalar function with parameter  '
 
-- And now with the use of a scalar UDF function as a global constant
 SELECT Count(*) FROM commonWords WHERE Len(word)=dbo.wordcount()
iNSERT INTO @log(WhatHappened) SELECT 'Same with a ''constant'' UD scalar function '
 
-- And now with the use of a schema-bound scalar UDF as a global constant
 SELECT Count(*) FROM commonWords WHERE Len(word)=dbo.wordcountSchemaBound()
iNSERT INTO @log(WhatHappened) SELECT 'Same with schema-bound ''constant'' UD scalar function'
--We'll now use a view to do the same thing
 SELECT Count(*) FROM commonWords
 INNER JOIN  dbo.WordCountView
 ON Len(word)=wordcount
iNSERT INTO @log(WhatHappened) SELECT 'Using a view containing a constant with inner join '
 
--We'll now use a view with a cross join to do the same thing
 SELECT Count(*) FROM commonWords
 CROSS JOIN  dbo.WordCountView
 where Len(word)=wordcount
iNSERT INTO @log(WhatHappened) SELECT 'Using a ''constant'' view with a cross join '
 
--and now with an inline table-valued function. Some functions are OK!
 SELECT Count(*) FROM commonWords
 INNER JOIN  dbo.WordCountTVF()
 ON Len(word)=wordcount
iNSERT INTO @log(WhatHappened) SELECT 'Using an inline TVF to provide a constant'
 
--we see if a different syntax makes a difference
 SELECT Count(*) FROM commonWords
 cross JOIN  dbo.WordCountTVF()
 WHERE Len(word)=wordcount
iNSERT INTO @log(WhatHappened) SELECT 'Using an inline TVF and cross join to provide a constant'
SELECT ending.whathappened AS test, DateDiff(ms, starting.whenItDid,ending.WhenItDid) [Time in ms] FROM @log starting
INNER JOIN @log ending ON ending.theorder=starting.TheOrder+1
--list out all the timings
GO
/* this is the end of the test section */

運(yùn)行此命令時(shí)，我們驗(yàn)證查詢中使用常量的所有形式均產(chǎn)生相同的結(jié)果。時(shí)代非常清楚地表明問題是什么，問題的嚴(yán)重程度

IF Object_Id('dbo.Wordcount') IS NOT NULL
   DROP function dbo.Wordcount
GO
IF Object_Id('dbo.WordcountSchemaBound') IS NOT NULL
   DROP function dbo.WordcountSchemaBound
GO
IF Object_Id('dbo.Wordcounter') IS NOT NULL
   DROP function dbo.Wordcounter
GO
IF Object_Id('dbo.WordCountView') IS NOT NULL
   DROP view dbo.WordCountView
GO
IF Object_Id('dbo.WordCountTVF') IS NOT NULL
   DROP function dbo.WordCountTVF
GO

推薦建議

如果您使用未經(jīng)驗(yàn)證的標(biāo)量函數(shù)，那么查詢將非常緩慢，因?yàn)闊o論是否具有參數(shù)，您都將在每一行上執(zhí)行該查詢。

如果您面對(duì)大量使用標(biāo)量UDF作為全局常量的繼承代碼，則可以使用架構(gòu)綁定重做它們。但是，如果這些是全局變量，并且在實(shí)時(shí)系統(tǒng)中很少更改，那么我不會(huì)想到這種選擇，因?yàn)槿绻慌R時(shí)更改在約束或計(jì)算列中使用它的每個(gè)表，就無法更改模式綁定函數(shù)，要?jiǎng)h除它們，請(qǐng)更改函數(shù)，然后替換約束和計(jì)算列。

視圖或TVF的用途更加廣泛，所以我更傾向于使用它們來保存“全局”值。如果更改了這些內(nèi)容，則會(huì)記錄該更改，因?yàn)樗鼈冃枰狣DL更改。唯一的問題是在約束或計(jì)算列中只能使用標(biāo)量函數(shù)。如果使用表，那很好，但是請(qǐng)記住，更改常量不是DDL更改，因此您必須設(shè)置訪問權(quán)限以拒絕任何人更改權(quán)限，例如稅率！

試用下載>>>

SQL Prompt 使用教程>>>