Never Prefix Your Stored Procedures with sp_

One more from the archive that has aged well — the advice holds in every version of SQL Server since. It started during a code review, when I noticed a brand-new stored procedure named sp_GetSomething. Here's why that leading sp_ is a habit worth breaking.

Why sp_ is special

In SQL Server, sp_ isn't just a convention — it's effectively reserved. System stored procedures live in the master database and use the sp_ prefix, and SQL Server gives any name starting with sp_ special treatment. When you call one, it resolves the name in a fixed order:

• the procedure in the master database, first;
• then the procedure based on any qualifier you provided (database or owner);
• then the one owned by dbo, if no owner was specified.

Microsoft's own documentation isn't subtle about this — it says it's "strongly recommended" that you not create procedures with the sp_ prefix (SQL Server Books Online). Here's the shape of the mistake, and the fix:

-- Don't: the sp_ prefix is reserved for system procedures
CREATE PROCEDURE sp_GetCustomer @CustomerId INT
AS BEGIN
    SELECT * FROM Customer WHERE CustomerId = @CustomerId;
END

-- Do: a clear name, optionally prefixed by functional group
CREATE PROCEDURE CUST_GetCustomerDetails @CustomerId INT
AS BEGIN
    SELECT * FROM Customer WHERE CustomerId = @CustomerId;
END

What actually goes wrong

• A small performance hit. Every call pays for that master-first lookup before SQL Server ever gets to your procedure.
• The nasty one — a silent collision. If a system procedure in master shares your procedure's name, yours will never be executed — the system one wins, even if you qualify the call with your database name. Name something generic like sp_BackupData and you may be running Microsoft's code without realizing it.
• A future time bomb. Even if there's no collision today, a future SQL Server release could ship a new system procedure with your generic sp_ name — quietly turning problem #2 into a bug that appears after an upgrade.

What to do instead

Give your procedures meaningful names. If you like a prefix, prefix by the functional group the procedure belongs to — CUST_GetCustomerDetails, ORD_CreateOrder — not sp_. It reads better, groups naturally, and sidesteps every problem above.

Small rule, real consequences. It costs nothing to follow and can save a genuinely baffling debugging session.