Product articles SQL Prompt SQL Code Analysis
Finding code smells using SQL Prompt:…

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.

Guest post

This is a guest post from Phil Factor. Phil Factor (real name withheld to protect the guilty), aka Database Mole, has 30 years of experience with database-intensive applications.

Despite having once been shouted at by a furious Bill Gates at an exhibition in the early 1980s, he has remained resolutely anonymous throughout his career.

He is a regular contributor to Simple Talk and SQLServerCentral.

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:

  1. First, it searches the sys schema of the current database
  2. 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
  3. 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.

Listing 1

If executed, this will give the following results:

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

SQL Prompt

Write, format, and refactor SQL effortlessly

Find out more