SQL Prompt教程:缺少架构限定(PE001)的过程

翻译|使用教程|编辑:杨鹏连|2021-03-31 10:40:15.493|阅读 52 次

概述:如果SQL Prompt发现使用EXECUTE,则会警告您,而无需指定存储过程所在的架构,因为它可能导致执行时间变慢,甚至导致运行错误的过程。

# 31款JAVA开发必备控件和工具 # 开发软/控件产品年终优惠

相关链接:

SQL Prompt是一款实用的SQL语法提示工具。SQL Prompt根据数据库的对象名称、语法和代码片段自动进行检索,为用户提供合适的代码选择。自动脚本设置使代码简单易读--当开发者不大熟悉脚本时尤其有用。SQL Prompt安装即可使用,能大幅提高编码效率。此外,用户还可根据需要进行自定义,使之以预想的方式工作。

点击下载SQL Prompt正式版

SQL提示实现了静态代码分析规则PE001,该规则将在开发和测试工作期间自动检查代码,以查找是否存在通过EXECUTE命令调用存储过程的情况,而无需指定架构。


即使您不必限定存储过程的名称,也就是该过程位于默认模式中时,如果指定该模式,性能也会稍好一些,这会使代码对其他人更易懂,更一致,而且更容易重构。

任何基于模式的数据库对象的全名最多包含四个标识符:服务器名称,数据库名称,模式名称和对象名称。仅在调用远程存储过程时,才需要由所有四个标识符组成的完全限定名称。如果要在另一个数据库中调用过程,则显然需要名称中的数据库标识符。在数据库内,只要过程位于相同的架构中,则只需要对象名称本身即可。通过指定架构,数据库引擎需要更少的搜索来识别它。甚至系统存储过程也应使用“ sys”架构名称进行限定。同样在创建存储过程时,始终指定父架构是一个好习惯。

数据库对象名称在服务器中不是唯一的,而在架构中不是唯一的,因此我们需要在适当的时候添加限定符,例如服务器名称,数据库名称或架构名称,以确保我们可以标识希望执行的过程,毫不含糊。这样,我们可以避免某些错误,最大程度地减少引擎用于搜索过程的时间,并帮助确保对过程的缓存查询计划进行重用。

不符合模式的程序存在问题

一个nonschema限定过程名称将是无论是名称的一部分,没有资格,或看起来像一个两部分名称,数据库和对象,与他们之间的双点在架构名称丢失。在这种情况下,数据库引擎必须按以下顺序在多个位置搜索以找到所需的过程:

  1. 首先,它搜索当前数据库的sys模式
  2. 接下来,引擎将搜索用户的默认模式,除非该过程嵌套在另一个过程中,在这种情况下,它将搜索包含外部过程的模式
  3. 最后,它在当前数据库中搜索dbo模式
相反,如果我们通过对过程的名称加上其模式名称的前缀来对过程进行模式限定,则数据库引擎不必搜索多个模式来定位该对象,只需搜索指定的对象即可。

不符合模式要求的过程会引入意想不到的歧义,这可能会导致难以解决的错误。过程调用的嵌套越多,出错的机会就越大。这种歧义也可以防止缓存的查询计划被重用。

最好的方法是限定过程名称,并确保(如果名称在数据库中重复)该过程是有意进行的。

模式认证和系统存储过程

可以从任何数据库中执行系统存储过程,Microsoft建议您使用sys模式对它们进行限定,但是如果不这样做,则不会破坏任何内容。

系统存储过程的名称始终以代表特殊的字符sp_开头,这些过程存储在Resource数据库中。它们将出现在该SQL Server实例中所有用户定义的数据库的sys模式中。如果您在数据库中创建与系统存储过程同名的过程,则无论您如何使用模式名称对其进行限定,都将永远不会执行该过程。数据库引擎始终总是首先在sys模式中搜索。

同一数据库中的过程名称重复

如果具有相同名称的过程位于单独的模式中,则它们可以完美地共存于同一数据库中,这样做是有正当理由的。例如,您可能希望不同类别的用户通过同一过程调用执行不同的代码。在这种情况下,每组用户将需要具有不同的默认架构,该架构存储了他们的过程版本。用户将仅对自己的架构具有EXECUTE权限,并使用所有权链接来访问所需的数据。然后,用户将需要指定不带模式的过程,以允许SQL Server从用户的默认模式中选择存储过程。

如果您有重复的程序名称,然后将其变成连架构限定任何过程调用这更重要的不能有歧义!如果没有,它可能会引入几乎无法检测到的错误,这些错误是在调用错误的同义过程时引起的。

SQL Server如何处理非架构限定的过程调用

我们可以通过创建多个具有相同名称但在不同模式中的存储过程来演示如何搜索存储过程。清单1模拟了数据库中具有三个不同用户的情况:我自己拥有DBO之类的神力,另外两个用户名为The_First_User和The_Second_User。我们为每个用户分配不同的默认架构,并为他们提供测试程序的不同版本。然后,当它们进行不符合模式资格的过程调用时,我们将看到它们执行哪个版本的过程,以及删除它们时会发生什么。

/*
  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;
清单1

如果执行,将产生以下结果:
----------------------------------------------------------------------------------------
  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

你怎么看的

  • 当第一个用户执行存储过程,Test_Procedure,它执行的版本MYSCHEMA,因为这是它的默认模式,和数据库引擎看了之后有旁边的检查SYS模式。
  • 同样的事情发生在第二个用户,谁被分配到不同版本的Test_Procedure,我们投入的模式MyOtherSchema。
  • 我们以用户身份还原为我,并且因为我的默认架构是dbo,所以我执行了该过程的第一个版本。
  • 接下来,我们放弃了MySchema.Test_Procedure,下一次The_Second_User执行Test_Procedure没有模式修饰,这是一个在DBO已执行。发生这种情况是因为,如果数据库引擎无法在用户的默认架构中找到该过程,它将在dbo中查找该过程。
  • 最后,The_Second_User在MyOtherSchema中,并执行模式限定的过程,并执行正确的版本(MySchema.Test_Procedure)。
结论
养成用程序模式限定过程名称的习惯是一个很好的主意。这不仅使您的代码更具弹性和可维护性,而且随着Microsoft引入使用架构的新功能(例如审核机制),您的代码中不会包含可能引起问题的歧义。

试用下载>>>

SQL Prompt 使用教程>>>


想要购买SQL Prompt正版授权,或了解更多产品信息请点击【咨询在线客服】

标签:

本站文章除注明转载外,均为本站原创或翻译。欢迎任何形式的转载,但请务必注明出处、不得修改原文相关链接,如果存在内容上的异议请邮件反馈至hey@evget.com

文章转载自:SQL Prompt

为你推荐

  • 推荐视频
  • 推荐活动
  • 推荐产品
  • 推荐文章
  • 慧都慧问
在线咨询
联系我们
TOP
在线客服系统
live chat