Finding code smells using SQL Prompt: procedures that lack schema-qualification (PE001)
SQL Prompt will warn you if it spots use of EXECUTE, without specifying the schema in which the stored procedure resides, because it can cause slower execution times, or even result in running of the wrong procedure.
SQL Prompt implements a static code analysis rule, PE001, which will check code automatically, during development and testing work, for occurrences of a stored procedure being called, via the EXECUTE command, without the schema being specified.
Even where you don’t have to qualify the name of a stored procedure, that is whenever the procedure is in your default schema, performance is slightly better if you specify the schema, and it makes the code more intelligible to others, more consistent, and easier to refactor.
The complete name of any schema-based database object consists of up to four identifiers: the server name, database name, schema name, and object name. Only if you are calling a remote stored procedure would you need a fully qualified name consisting of all four identifiers. If you are calling a procedure in another database, you obviously need its database identifier in the name. Within a database, you only need the object name itself so long as the procedure is in the same schema. By specifying the schema, the database engine needs less searching to identify it. Even system stored procedures should be qualified with the ‘sys’ schema name. When creating a stored procedure as well, it is a good habit to always specify the parent schema.
Database object names aren’t unique within a server, only within a schema, therefore we need to add qualifiers when appropriate, such as the server name, database name, or schema name, to ensure that we can identify the procedure we wish to execute, unambiguously. By doing so, we avoid certain bugs, minimize the time the engine spends searching for the procedure, and help ensure that cached query plans for the procedures get reused.
Problems with nonschema-qualified procedures
A nonschema-qualified procedure name will be either a one-part name with no qualifier, or what looks like a two-part name, database and object, with a double-dot between them where the schema name is missing. In such cases, the database engine must search in several places to find the required procedure, in the following order:
- First, it searches the sys schema of the current database
- Next, the engine searches the users’ default schema, unless the procedure is nested within another one, in which case it will search the schema that contains the outer procedure
- Finally, it searches the dbo schema in the current database
Conversely, if we schema-qualify a procedure, simply by prefixing its name with the name of its schema, then the database engine does not have to search several schemas to locate the object, just the one specified.
Nonschema-qualified procedures introduce unexpected ambiguity, which can cause a bug that is hard to resolve. The more nesting of procedure calls takes place, the more chance of error. This ambiguity can also prevent cached query plans being reused.
The best approach is to qualify procedure names and to make sure that, if names are duplicated within a database, it is done intentionally.
Schema qualification and system stored procedures
System stored procedures can be executed from any database, and Microsoft recommends that you qualify them with the sys schema, though nothing will break if you don’t.
Names of system stored procedures always begin with the characters, sp_, standing for ‘special’ and these procedures are stored in the Resource database. They will appear in the sys schema of all user-defined databases in that instance of SQL Server. If you create a procedure in your database with the same name as a system stored procedure, your procedure will never execute no matter how you qualify it with a schema name. The database engine always searches in the sys schema first.
Duplicate procedure names in the same database
Procedures with the same name can coexist in the same database perfectly happily, if they are in separate schemas, and there are legitimate reasons for doing this. For example, you might want different categories of users to execute different code with the same procedure call. In this case, each group of users would need to have a different default schema, which stored their version of the procedure. Users would have EXECUTE permissions only on their own schema, and use ownership-chaining to access the data they need. Users would then need to specify the procedure without the schema to allow SQL Server to choose the stored procedure from the default schema of the user.
If you have duplicate procedure names, then it becomes even more important to schema-qualify any procedure calls that mustn’t be ambiguous! If not, it can introduce almost undetectable bugs that are caused when the wrong synonymous procedure gets called.
How SQL Server deals with procedure calls that aren’t schema-qualified
We can demonstrate how stored procedures are searched by creating several with the same name but in different schemas. Listing 1 simulates having three different users in the database: myself with god-like powers as DBO, and two other users called The_First_User and The_Second_User. We assign each user to different default schemas and give them a different version of our test procedure. We then see which version of the procedures they execute when they make procedure calls that aren’t schema-qualified, and what happens when we delete one.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 |
/* 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; |
Listing 1
If executed, this will give the following results:
|
1 2 3 4 5 6 7 8 9 10 11 12 |
---------------------------------------------------------------------------------------- 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 |
What do you make of this?
- When the first user executes the stored procedure, Test_Procedure, it executes the version in MySchema because that is its default schema, and the database engine looked there next after the checking the sys schema.
- The same happened to the second user, who was assigned to a different version of Test_Procedure that we put in the schema MyOtherSchema.
- We reverted to me as user, and because my default schema is dbo, I executed the first version of the procedure.
- Next, we dropped the MySchema.Test_Procedure and the next time The_Second_User executed Test_Procedure without schema qualification, it was the one in dbo that was executed. That happened because if the database engine can’t find the procedure in the user’s default schema it looks in dbo for it.
- Finally, The_Second_User is in MyOtherSchema, and executes the schema-qualified procedure and the correct version (MySchema.Test_Procedure) is executed.
Conclusions
It is a very good idea to get into the habit of qualifying the names of procedures with their schema. It is not only makes your code more resilient and maintainable, but as Microsoft introduces new features that use schemas, such as auditing mechanisms, you code contains no ambiguities that could cause problems.
Tools in this post
You may also like
-
Article
Controlling how SQL Prompt Formats your Code: The Knobs and Dials
Phil Factor explores and discusses the current state of the art in SQL Formatting, as done automatically by SQL Prompt.
-
Article
Customizing the SQL Prompt built-in snippets: a better ALTER TABLE ADD (ata) snippet
Phil Factor improves SQL Prompt's built-in ALTER TABLE ADD (ata) snippet so that it enforces certain coding standards, such as specifying whether the column accepts
NULLvalues, and ensuring the new column is well-documented. -
Article
SQL Prompt code analysis: avoiding the old-style TOP clause (ST006)
If you want to use TOP with an expression or subquery, or in INSERT, UPDATE, MERGE, and DELETE statements, then use of brackets is required, so it's a good habit to adopt everywhere.
-
Live training session
What’s new in SQL Prompt
As a Redgate customer, you are invited to attend this exclusive training session to discover SQL History and how you and your team can accelerate your coding and standardize development. In this 30-minute training session, Grant Fritchey, Redgate Advocate, will demonstrate how SQL History works, provide an overview of SQL Prompt and allow time for Q&A
-
