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

點擊下載SQL Prompt正式版

SQL提示實現(xiàn)了靜態(tài)代碼分析規(guī)則PE001，該規(guī)則將在開發(fā)和測試工作期間自動檢查代碼，以查找是否存在通過EXECUTE命令調(diào)用存儲過程的情況，而無需指定架構(gòu)。

即使您不必限定存儲過程的名稱，也就是該過程位于默認模式中時，如果指定該模式，性能也會稍好一些，這會使代碼對其他人更易懂，更一致，而且更容易重構(gòu)。

任何基于模式的數(shù)據(jù)庫對象的全名最多包含四個標識符：服務(wù)器名稱，數(shù)據(jù)庫名稱，模式名稱和對象名稱。僅在調(diào)用遠程存儲過程時，才需要由所有四個標識符組成的完全限定名稱。如果要在另一個數(shù)據(jù)庫中調(diào)用過程，則顯然需要名稱中的數(shù)據(jù)庫標識符。在數(shù)據(jù)庫內(nèi)，只要過程位于相同的架構(gòu)中，則只需要對象名稱本身即可。通過指定架構(gòu)，數(shù)據(jù)庫引擎需要更少的搜索來識別它。甚至系統(tǒng)存儲過程也應(yīng)使用“ sys”架構(gòu)名稱進行限定。同樣在創(chuàng)建存儲過程時，始終指定父架構(gòu)是一個好習慣。

數(shù)據(jù)庫對象名稱在服務(wù)器中不是唯一的，而在架構(gòu)中不是唯一的，因此我們需要在適當?shù)臅r候添加限定符，例如服務(wù)器名稱，數(shù)據(jù)庫名稱或架構(gòu)名稱，以確保我們可以標識希望執(zhí)行的過程，毫不含糊。這樣，我們可以避免某些錯誤，最大程度地減少引擎用于搜索過程的時間，并幫助確保對過程的緩存查詢計劃進行重用。

不符合模式的程序存在問題

一個nonschema限定過程名稱將是無論是名稱的一部分，沒有資格，或看起來像一個兩部分名稱，數(shù)據(jù)庫和對象，與他們之間的雙點在架構(gòu)名稱丟失。在這種情況下，數(shù)據(jù)庫引擎必須按以下順序在多個位置搜索以找到所需的過程：

1. 首先，它搜索當前數(shù)據(jù)庫的sys模式
2. 接下來，引擎將搜索用戶的默認模式，除非該過程嵌套在另一個過程中，在這種情況下，它將搜索包含外部過程的模式
3. 最后，它在當前數(shù)據(jù)庫中搜索dbo模式

不符合模式要求的過程會引入意想不到的歧義，這可能會導致難以解決的錯誤。過程調(diào)用的嵌套越多，出錯的機會就越大。這種歧義也可以防止緩存的查詢計劃被重用。

最好的方法是限定過程名稱，并確保（如果名稱在數(shù)據(jù)庫中重復(fù)）該過程是有意進行的。

模式認證和系統(tǒng)存儲過程

可以從任何數(shù)據(jù)庫中執(zhí)行系統(tǒng)存儲過程，Microsoft建議您使用sys模式對它們進行限定，但是如果不這樣做，則不會破壞任何內(nèi)容。

系統(tǒng)存儲過程的名稱始終以代表特殊的字符sp_開頭，這些過程存儲在Resource數(shù)據(jù)庫中。它們將出現(xiàn)在該SQL Server實例中所有用戶定義的數(shù)據(jù)庫的sys模式中。如果您在數(shù)據(jù)庫中創(chuàng)建與系統(tǒng)存儲過程同名的過程，則無論您如何使用模式名稱對其進行限定，都將永遠不會執(zhí)行該過程。數(shù)據(jù)庫引擎始終總是首先在sys模式中搜索。

同一數(shù)據(jù)庫中的過程名稱重復(fù)

如果具有相同名稱的過程位于單獨的模式中，則它們可以完美地共存于同一數(shù)據(jù)庫中，這樣做是有正當理由的。例如，您可能希望不同類別的用戶通過同一過程調(diào)用執(zhí)行不同的代碼。在這種情況下，每組用戶將需要具有不同的默認架構(gòu)，該架構(gòu)存儲了他們的過程版本。用戶將僅對自己的架構(gòu)具有EXECUTE權(quán)限，并使用所有權(quán)鏈接來訪問所需的數(shù)據(jù)。然后，用戶將需要指定不帶模式的過程，以允許SQL Server從用戶的默認模式中選擇存儲過程。

如果您有重復(fù)的程序名稱，然后將其變成連架構(gòu)限定任何過程調(diào)用這更重要的不能有歧義！如果沒有，它可能會引入幾乎無法檢測到的錯誤，這些錯誤是在調(diào)用錯誤的同義過程時引起的。

SQL Server如何處理非架構(gòu)限定的過程調(diào)用

我們可以通過創(chuàng)建多個具有相同名稱但在不同模式中的存儲過程來演示如何搜索存儲過程。清單1模擬了數(shù)據(jù)庫中具有三個不同用戶的情況：我自己擁有DBO之類的神力，另外兩個用戶名為The_First_User和The_Second_User。我們?yōu)槊總€用戶分配不同的默認架構(gòu)，并為他們提供測試程序的不同版本。然后，當它們進行不符合模式資格的過程調(diào)用時，我們將看到它們執(zhí)行哪個版本的過程，以及刪除它們時會發(fā)生什么。

/*
  This script is a series batches that illustrate how the Database engine works out what you want when you don't specify the schema of a stored procedure. 
  The behavior can be quite valuable if you want the same code to be handled differently according to the default schema of the user.
  Even if you don't have duplicate procedure names, this code shows that the default schema is searched before the DBO schema. 
  */
  SET NOCOUNT ON
  IF Object_Id('dbo.Test_Procedure') IS NOT NULL DROP PROCEDURE dbo.Test_Procedure;
  GO
  -- firstly we create a procedure in the DBO schema 
  CREATE PROCEDURE dbo.Test_Procedure
    @param1 VARCHAR(100) = 'Hello %s', @param2 VARCHAR(100) OUTPUT
  AS
  SELECT @param2 =
    CURRENT_USER + ' is in the ' + Schema_Name() + ' schema, while '
    + Replace(Coalesce(@param1, ''), '%s', 'World');
  RETURN 0;
  GO
  --now we create a procedure with the same name in the MySchema schema
  IF Object_Id('MySchema.Test_Procedure') IS NOT NULL DROP PROCEDURE MySchema.Test_Procedure;
  GO
  --just in case it has been left over from last time
  IF EXISTS
    (SELECT schemas.name FROM sys.schemas WHERE schemas.name LIKE 'MySchema')
    DROP SCHEMA MySchema;
  GO
  --we first create the schema
  CREATE SCHEMA MySchema;
  GO
  --now we create the stored procedure in this schema with a different output just so we know which is executed
  CREATE PROCEDURE MySchema.Test_Procedure
    @param1 VARCHAR(100) = 'Hello %s', @param2 VARCHAR(100) OUTPUT
  AS --make the wording different so we can prove which version of the procedure was executed
  SELECT @param2 =
    CURRENT_USER + ' is in the ' + Schema_Name() + ' schema, while '
    + Replace(Coalesce(@param1, ''), '%s', 'Planet');
  RETURN 0;
  GO
  --now we create a procedure with the same name in the MyOtherSchema schema
  IF Object_Id('MyOtherSchema.Test_Procedure') IS NOT NULL
    DROP PROCEDURE MyOtherSchema.Test_Procedure;
  GO
  IF EXISTS
    (SELECT schemas.name FROM sys.schemas WHERE schemas.name LIKE 'MyOtherSchema')
    DROP SCHEMA MyOtherSchema;
  GO
  CREATE SCHEMA MyOtherSchema;
  GO
  ---and in this schema we place a third schema
  CREATE PROCEDURE MyOtherSchema.Test_Procedure
    @param1 VARCHAR(100) = 'Hello %s', @param2 VARCHAR(100) OUTPUT
  AS --make the wording different again, so we can prove which version of the procedure was executed
  SELECT @param2 =
    CURRENT_USER + ' is in the ' + Schema_Name() + ' schema, while '
    + Replace(Coalesce(@param1, ''), '%s', 'Back yard');
  RETURN 0;
  GO
  --Now we create a couple of test users whose default schemas are those we've just created
  IF Database_Principal_Id('The_First_User') IS NOT NULL DROP USER The_First_User;
  IF Database_Principal_Id('The_Second_User') IS NOT NULL DROP USER The_Second_User;
  IF Database_Principal_Id('OurPhonyUsers') IS NOT NULL DROP ROLE OurPhonyUsers;
  GO
  -- before creating the users we create a role that we can assign to them so that 
  --they can do stuff
  CREATE ROLE OurPhonyUsers;
  GRANT EXECUTE ON SCHEMA::MySchema TO OurPhonyUsers;
  GRANT EXECUTE ON SCHEMA::MyOtherSchema TO OurPhonyUsers;
  GRANT EXECUTE ON SCHEMA::dbo TO OurPhonyUsers; --comment this out to see what happens!
  GO
  --Now we create the users and attach them to the role we created
  CREATE USER The_First_User WITHOUT LOGIN WITH DEFAULT_SCHEMA = MySchema;
  EXEC sys.sp_addrolemember N'OurPhonyUsers', N'The_First_User';
  EXECUTE AS USER = 'The_First_User';
  GO
  DECLARE @MyMessage VARCHAR(100);
  --execute the stored procedure without any qualifier as The_First_User
  EXECUTE Test_Procedure @param1 = 'firstly calling ''hi'' to the %s',
    @param2 = @MyMessage OUTPUT;
  SELECT @MyMessage;
  REVERT; --to being myself
  GO
  CREATE USER The_Second_User WITHOUT LOGIN
  WITH DEFAULT_SCHEMA = MyOtherSchema;
  EXEC sys.sp_addrolemember N'OurPhonyUsers', N'The_Second_User';
  EXECUTE AS USER = 'The_Second_User';
  DECLARE @MyMessage VARCHAR(100);
  --execute the stored procedure without any qualifier as The_Second_User
  EXECUTE Test_Procedure @param1 = 'Secondly welcoming the %s',
    @param2 = @MyMessage OUTPUT;
  SELECT @MyMessage;
  REVERT; --to being myself
  GO
  DECLARE @MyMessage VARCHAR(100);
  --execute the stored procedure without any qualifier as dbo
  EXECUTE Test_Procedure @param1 = 'thirdly acknowledging the %s',
    @param2 = @MyMessage OUTPUT;
  SELECT @MyMessage;
  SELECT 'now deleting the MyOtherSchema.Test_Procedure'
  --now we drop the procedure with the same name in the MyOtherSchema schema
  IF Object_Id('MyOtherSchema.Test_Procedure') IS NOT NULL
    DROP PROCEDURE MyOtherSchema.Test_Procedure;
  GO
  EXECUTE AS USER = 'The_Second_User';
  DECLARE @MyMessage VARCHAR(100);
  --execute the stored procedure without any qualifier as The_Second_User
  EXECUTE Test_Procedure @param1 = 'fourthly saying ''greetings'' to the %s',
    @param2 = @MyMessage OUTPUT;
  SELECT @MyMessage;
  --execute the stored procedure with a qualifier as The_Second_User
  EXECUTE MySchema.Test_Procedure @param1 = 'finally saying hello to the %s',
    @param2 = @MyMessage OUTPUT;
  SELECT @MyMessage;
  REVERT; --to being myself
  GO
  ---and clean up
  DROP PROCEDURE MySchema.Test_Procedure;
  DROP PROCEDURE dbo.Test_Procedure;
  DROP USER The_First_User;
  DROP USER The_Second_User;
  DROP ROLE OurPhonyUsers;
  DROP SCHEMA MyOtherSchema;
  DROP SCHEMA MySchema;

如果執(zhí)行，將產(chǎn)生以下結(jié)果：
----------------------------------------------------------------------------------------
  The_First_User is in the MySchema schema, while firstly calling 'hi' to the Planet
  ----------------------------------------------------------------------------------------
  The_Second_User is in the MyOtherSchema schema, while Secondly welcoming the Back yard
  -----------------------------------------------------------------------------------------
  dbo is in the dbo schema, while thirdly acknowledging the World
  -----------------------------------------------------------------------------------------
  now deleting the MyOtherSchema.Test_Procedure
  -----------------------------------------------------------------------------------------
  The_Second_User is in the MyOtherSchema schema, while fourthly saying 'greetings' to the World
  -----------------------------------------------------------------------------------------
  The_Second_User is in the MyOtherSchema schema, while finally saying hello to the Planet

你怎么看的

• 當?shù)谝粋€用戶執(zhí)行存儲過程，Test_Procedure，它執(zhí)行的版本MYSCHEMA，因為這是它的默認模式，和數(shù)據(jù)庫引擎看了之后有旁邊的檢查SYS模式。
• 同樣的事情發(fā)生在第二個用戶，誰被分配到不同版本的Test_Procedure，我們投入的模式MyOtherSchema。
• 我們以用戶身份還原為我，并且因為我的默認架構(gòu)是dbo，所以我執(zhí)行了該過程的第一個版本。
• 接下來，我們放棄了MySchema.Test_Procedure，下一次The_Second_User執(zhí)行Test_Procedure沒有模式修飾，這是一個在DBO已執(zhí)行。發(fā)生這種情況是因為，如果數(shù)據(jù)庫引擎無法在用戶的默認架構(gòu)中找到該過程，它將在dbo中查找該過程。
• 最后，The_Second_User在MyOtherSchema中，并執(zhí)行模式限定的過程，并執(zhí)行正確的版本（MySchema.Test_Procedure）。

試用下載>>>

SQL Prompt 使用教程>>>