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

點擊下載SQL Prompt正式版

標量UDF作為全局數據庫常量的錯誤使用是一個主要的性能問題，每當SQL Prompt在任何生產代碼中發現此錯誤時，都應進行調查。除非您需要在計算列或約束中使用這些全局常量，否則通常將值存儲在內聯表值函數中或使用視圖會更安全，更方便。

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

調用具有BEGIN…END塊的任何SQL Server函數都會產生開銷，因為除非允許SQL Server通過使用架構綁定創建該函數來驗證其輸出，否則它將在過濾數據之前重新為每一行執行該函數。 ，即使您很明顯每次都會返回相同的值。這是一個比較隱蔽的問題，盡管擴展事件會話將揭示實際發生的情況，但是它并沒有真正顯示其在執行計劃中的全部意義。

簡而言之，請勿在JOIN條件，WHERE搜索條件或SELECT列表中使用標量用戶定義函數（UDF），除非該函數是架構綁定的。SQL Prompt實現了靜態代碼分析規則PE017，該規則專門用于幫助您檢測和糾正此問題。除非您對模式綁定及其對數據庫更改的后果有信心，否則最好使用將值傳輸到變量，或者使用諸如視圖或內聯表值函數之類的模塊。

解決問題

如果SQL Prompt檢測到您的代碼出現PE017，該怎么辦？

通過添加架構綁定以確保其經過系統驗證，可以正確使用標量函數。清單1創建了相同的簡單Wordcount函數的兩個版本，首先不帶模式綁定，然后帶模式綁定，這兩個版本都簡單地返回一個常量。在每一種情況下，我們檢查IsDeterministic，IsPrecise以及IsSystemVerified屬性值對每個對象。

最后，它創建了第三個版本，該版本僅返回其參數值，僅用于檢查這是否是SQL Server驗證過程中的一個因素。

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

如果運行清單1，您將看到該函數的第二個版本WordCountSchemaBound，對這三個屬性返回true。稍后我們將看到這對調用這些函數的所有查詢的性能有多大影響。

盡管模式綁定具有許多優點，但是在這種情況下，這意味著您將被明確禁止將常量視為變量，這并不是一件壞事。如果您更改了“常量”函數（即您已在表中的約束或計算列中使用的函數），它將被證明很復雜。另外，如果嘗試在數據庫工作時更改常量，則使用正在執行的功能的計劃將在功能上放置模式穩定性鎖，這將防止您更改常量的值，因為它們需要更改模式。鎖。

標量UDF的替代品

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

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

視圖定義中引用的對象不能以使視圖定義非法或強制SQL Server在視圖上重新創建索引的方式進行更改。

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

性能測試

排列所有候選解決方案后，讓我們看看它們的性能如何。我將測試每個選項能多快找出英語中常用的五個字母的單詞。這些測試要求我們創建一個簡單Commonwords表，其中包含所有常見單詞的單列（主鍵）。要填充它，您需要下載commonwords 文件，然后運行清單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);

對于計時，我將使用我的文章如何使用SQL Prompt片段記錄T-SQL執行時間中描述的簡單測試工具。

-- 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 */

運行此命令時，我們驗證查詢中使用常量的所有形式均產生相同的結果。時代非常清楚地表明問題是什么，問題的嚴重程度

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

推薦建議

如果您使用未經驗證的標量函數，那么查詢將非常緩慢，因為無論是否具有參數，您都將在每一行上執行該查詢。

如果您面對大量使用標量UDF作為全局常量的繼承代碼，則可以使用架構綁定重做它們。但是，如果這些是全局變量，并且在實時系統中很少更改，那么我不會想到這種選擇，因為如果不臨時更改在約束或計算列中使用它的每個表，就無法更改模式綁定函數，要刪除它們，請更改函數，然后替換約束和計算列。

視圖或TVF的用途更加廣泛，所以我更傾向于使用它們來保存“全局”值。如果更改了這些內容，則會記錄該更改，因為它們需要DDL更改。唯一的問題是在約束或計算列中只能使用標量函數。如果使用表，那很好，但是請記住，更改常量不是DDL更改，因此您必須設置訪問權限以拒絕任何人更改權限，例如稅率！

試用下載>>>

SQL Prompt 使用教程>>>