14 December 2017
14 December 2017

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.

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.

Share this post.

Share on FacebookShare on Google+Share on LinkedInTweet about this on Twitter

You may also like

  • Article

    SQL Prompt Code Analysis: Table does not have clustered index (BP021)

    If SQL Prompt alerts you to a table without a clustered index, investigate the reason for its absence carefully. It is rare indeed to find a table where data retrieval is faster without one.

  • 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 NULL values, and ensuring the new column is well-documented.

  • Article

    The '= NULL' Mistake and other SQL NULL Heresies

    The SQL Prompt Best Practice rule checks whether a comparison or expression includes a NULL literal ('NULL'), which in SQL Server, rather than result in an error, will simply always produce a NULL result. Phil Factor explains how to avoid this, and other SQL NULL-related calamities.

  • Article

    Why your Development team needs SQL Prompt

    While everyone knows SQL Prompt for its code completion and IntelliSense features, a lot of its extra value comes from features that allow the development team to standardize coding practices and drive up code quality.

  • University

    Take the SQL Prompt course

    This Redgate University course takes you from installation all the way up to getting the most out of the advanced operations in SQL Prompt. As well as autocompleting your code, you’ll learn how SQL Prompt can help you with code formatting, object renaming, code analysis, and other useful tip and tricks.

  • Forums

    SQL Prompt Forum

    Write, format, and refactor SQL effortlessly