This is the 25th day of my participation in the August Challenge

First, user-defined stored procedure names should never start with sp_ or xp_.

Stored procedures with sp_, xp_ prefixes

Stored procedures are compiled at creation time so that they do not need to be recompiled for each subsequent execution, which improves execution efficiency. And by encapsulating complex logical operations as stored procedures, logic processing can be simplified and easily executed with other SQL transactions.

In SQL Server, stored procedures starting with SP_ and Xp_ are commonly provided by default.

  • Sp_ : Sp_ indicates the system stored procedure. Used for SQL Server Settings, access to information, related management operations. For example, sp_help retrieves information about the specified object.

  • Xp_ : indicates Extended Stored Procedures starting with xp_. Used to provide operating system-level functionality, write server-level programs or logic processing. You can also call it a stored procedure API. This feature will be removed in the latest version and is not recommended. New and more powerful technologies such as CLR Integration or distributed queries can be used instead to implement the required functionality.

CLR Stored Procedures is one of the CLR integration technologies.

SQL Server stored procedure naming conventions

SQL Server uses object names and schema names to find specific objects to use. Such as a table, stored procedure, function, view, and so on.

So it’s a good idea to come up with a standard naming convention for each object.

Don’t usesp_The prefix

The “sp_” prefix should not be used as a naming convention. This is the standard naming convention used in master databases. Used to serve system stored Procedures.

If you do not specify the database where the object resides, SQL Server will first search the Master database to see if the object exists, and then search the user database. Using the sp_ prefix causes additional time overhead and execution efficiency issues.

Normalized prefix

In addition to sp_, you can use the following:

  • usp_
  • sp
  • usp
  • proc_

You can use either, but the ones above help distinguish objects and make them easier to manage.

Examples of this might be:

  • usp_InsertPerson
  • spInsertPerson
  • uspInsertPerson
  • proc_InsertPerson

Name the operation type and object name

You can use Insert, Delete, Update, Select, Get, Validate, and other related operations.

Next, specify the object name, which can be a real object, such as a table; Or a simple and meaningful processing name.

Such as:

  • Usp_InsertPerson – Inserts a new person record
  • Usp_GetAccountBalance – Gets the account balance
  • Usp_GetOrderHistory – Returns the order list

It is recommended to follow the verb-noun convention.

Architecture name (Schema)

An architecture is a collection of objects, similar to a container. This logical grouping helps distinguish objects.

Some examples of architectures:

  • HR.usp_GetPerson
  • HR.usp_InsertPerson
  • UTIL.usp_Get
  • UTIL.usp_GetLastBackupDate

Use the CREATE SCHEMA command to CREATE a new SCHEMA.

As follows, create a new schema named “HR” and authorize this schema to “DBO”.

CREATE SCHEMA [HumanResources] AUTHORIZATION [dbo];
Copy the code

combination

Thus, a complete naming convention consists of the following parts:

  • Schema
  • Prefix
  • Action
  • Object

Naming conventions for other objects

Do not use descriptive prefixes such as’ TBL ‘,’sp_’,’xp_’,’dt_’. They have special meaning and are first looked up in the Master database.

The TBL prefix was first used as the table represented in the import of SQL Server from Access, often called “tibbling”

The table name should not be the same as one of its column names.

-- Find tables with the same table name and column name
SELECT Thetable.Name FROM sys.columns cols
INNER JOIN sys.tables Thetable 
  ON Thetable.object_id = cols.object_id
  WHERE cols.NAME=Thetable.name
Copy the code

Alternatively, find redundant tables that contain table names in their column names:

-- Find the table whose column name contains the table name
SELECT TheTable.name AS TableName, TheColumn.name AS ColumnName
  FROM sys.tables AS TheTable
    INNER JOIN sys.columns AS TheColumn
      ON TheColumn.object_id = TheTable.object_id
  WHERE TheColumn.name LIKE The '%' + TheTable.name + The '%';
Copy the code

Keep table names short because they are often required in index names, constraint names, trigger names, and so on. To avoid redundancy, table names should also not contain collective names, such as schema names.

-- Find tables, views, and other objects with the same table name and schema name
SELECT name FROM sys.objects WHERE Object_Schema_Name(object_id) = name;
Copy the code

The main reference article has been found, not listed for the time being.