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

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

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

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

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

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

不符合模式的程序存在問(wèn)題

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

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

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

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

模式認(rèn)證和系統(tǒng)存儲(chǔ)過(guò)程

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

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

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

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

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

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

我們可以通過(guò)創(chuàng)建多個(gè)具有相同名稱但在不同模式中的存儲(chǔ)過(guò)程來(lái)演示如何搜索存儲(chǔ)過(guò)程。清單1模擬了數(shù)據(jù)庫(kù)中具有三個(gè)不同用戶的情況：我自己擁有DBO之類的神力，另外兩個(gè)用戶名為T(mén)he_First_User和The_Second_User。我們?yōu)槊總€(gè)用戶分配不同的默認(rèn)架構(gòu)，并為他們提供測(cè)試程序的不同版本。然后，當(dāng)它們進(jìn)行不符合模式資格的過(guò)程調(diào)用時(shí)，我們將看到它們執(zhí)行哪個(gè)版本的過(guò)程，以及刪除它們時(shí)會(huì)發(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

你怎么看的

• 當(dāng)?shù)谝粋€(gè)用戶執(zhí)行存儲(chǔ)過(guò)程，Test_Procedure，它執(zhí)行的版本MYSCHEMA，因?yàn)檫@是它的默認(rèn)模式，和數(shù)據(jù)庫(kù)引擎看了之后有旁邊的檢查SYS模式。
• 同樣的事情發(fā)生在第二個(gè)用戶，誰(shuí)被分配到不同版本的Test_Procedure，我們投入的模式MyOtherSchema。
• 我們以用戶身份還原為我，并且因?yàn)槲业哪J(rèn)架構(gòu)是dbo，所以我執(zhí)行了該過(guò)程的第一個(gè)版本。
• 接下來(lái)，我們放棄了MySchema.Test_Procedure，下一次The_Second_User執(zhí)行Test_Procedure沒(méi)有模式修飾，這是一個(gè)在DBO已執(zhí)行。發(fā)生這種情況是因?yàn)椋绻麛?shù)據(jù)庫(kù)引擎無(wú)法在用戶的默認(rèn)架構(gòu)中找到該過(guò)程，它將在dbo中查找該過(guò)程。
• 最后，The_Second_User在MyOtherSchema中，并執(zhí)行模式限定的過(guò)程，并執(zhí)行正確的版本（MySchema.Test_Procedure）。

試用下載>>>

SQL Prompt 使用教程>>>