Prevent sp_ Procedures with SQLCop

Question

Post reply

Prevent sp_ Procedures with SQLCop

Steve Jones - SSC Editor

SSC Guru

Points: 736212
More actions
January 14, 2015 at 9:42 pm

#74387

Comments posted to this topic are about the item Prevent sp_ Procedures with SQLCop

Viewing 15 posts - 1 through 15 (of 27 total)

You must be logged in to reply to this topic. Login to reply

Vaidy Mohan SSC Enthusiast Points: 183 More actions · Answer 1

Is there a compelling reason as to why we should not be writing procedures with "sp_" prefix?

Ness SSCarpal Tunnel Points: 4282 More actions · Answer 2

Is there a reason why you wouldn't just use native policy management in SQL to achieve the same?

SQL DBA
Every day is a school day, and don't trust anyone who tells you any different.
http://sqlblogness.blogspot.co.uk

Vaidy Mohan SSC Enthusiast Points: 183 More actions · Answer 3

Intention behind my question was to learn why it's a bad idea. SQL Server is not my expertise yet. I am an amateur SQL developer. I have begun learning things about SQL.

I used to write SQL procedures with sp_ prefix. Your post caught my attention, for obvious reasons.

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 4

Vaidy Mohan (1/15/2015)
Is there a compelling reason as to why we should not be writing procedures with "sp_" prefix?

"sp_" is the prefix used for system stored procedures, so there are two reasons why it's a bad idea:

(1) SQL Server always looks first in the master database when it is asked to execute a "sp_" stored procedure. When it doesn't find it there, it looks in the current or specified database. You therefore waste time looking in the master database. The performance impact may be noticeable if the stored procedure is frequently executed.

(2) There's a chance that a future version (or even service pack) of SQL Server will use for a new system stored procedure the very same name you chose. If that happens, your code will probably break.

John

Vaidy Mohan SSC Enthusiast Points: 183 More actions · Answer 5

Thanks for the insights, John. I do realize now why that's a bad idea.

SQLvis SSC Eights! Points: 851 More actions · Answer 6

Is there a list of standard object nomenclature for indexes, procedures, functions, and views (etc) that most teams should follow?

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 7

SQLvis (1/15/2015)
Is there a list of standard object nomenclature for indexes, procedures, functions, and views (etc) that most teams should follow?

Not really. It's more important to have a convention or standard than what that convention actually is. Decide on something that works for you and your colleagues, and stick to it.

John

Steve Jones - SSC Editor SSC Guru Points: 736212 More actions · Answer 8

Ness (1/15/2015)
Is there a reason why you wouldn't just use native policy management in SQL to achieve the same?

PBM works great in production servers, but it takes some setup and often wouldn't be enabled in development environments. This is a developer catch for standards, not necessarily a prevention mechanism.

You could also use this in a similar way to enforce a specific naming pattern for specific databases.

Steve Jones - SSC Editor SSC Guru Points: 736212 More actions · Answer 9

John Mitchell-245523 (1/15/2015)
SQLvis (1/15/2015)
Is there a list of standard object nomenclature for indexes, procedures, functions, and views (etc) that most teams should follow?
Not really. It's more important to have a convention or standard than what that convention actually is. Decide on something that works for you and your colleagues, and stick to it.
John

Completely agree with John. There isn't really a standard globally, and I wouldn't want one now. Best to decide how you might do this in your own team.

RonKyle SSC-Dedicated Points: 31556 More actions · Answer 10

As a minimum, usp_ for user stored procedure would be a good start. I'm not advocating that as the ideal solution. Only that it makes a contrast with sp_.

subsonic-abercrombie Mr or Mrs. 500 Points: 520 More actions · Answer 11

Why not just use a server trigger?

I've done so successfully with great success. Even have it email the whole team the name of the offending developer so we can give him/her the stink eye.:-D

Steve Jones - SSC Editor SSC Guru Points: 736212 More actions · Answer 12

If you start to extend this to lots of potential items, then a trigger grows to slow things, or cause issues. A trigger might work in production, but potentially if there were applications that required this, maybe third parties, then this doesn't work.

Jeff Moden SSC Guru Points: 1004340 More actions · Answer 13

John Mitchell-245523 (1/15/2015)
SQLvis (1/15/2015)
Is there a list of standard object nomenclature for indexes, procedures, functions, and views (etc) that most teams should follow?
Not really. It's more important to have a convention or standard than what that convention actually is. Decide on something that works for you and your colleagues, and stick to it.
John

I have to disagree with that a bit. There have been times where we've changed views to tables and tables to views. If the Hungarian Naming Convention (or other similar convention) is used, then in order to prevent across the board changes to code, you either need to make a synonym (ugh!) for it or change the code or you can end up with a table like vw_tablename or a view that looks like tbl_viewname.

The same goes for column names. I've seen a number of databases that use the old Hungarian Notation Style of prefixing variable names and column names with the abbreviation for the base datatype. It's a real fun trip through the code if even one well used column has its datatype changed.

Of course, the "sp_" naming convention absolutely must be avoided for user stored procedures for reasons previously stated whether someone makes it a standard or not.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

RonKyle SSC-Dedicated Points: 31556 More actions · Answer 14

It's a real fun trip through the code if even one well used column has its datatype changed.

Just because something is difficult on change doesn't mean it's a bad idea. It seems to me useful to have for example intThirdParty and strThirdParty. One is the integer key and the other the string name. A data type change for either one is extremely unlikely. In real life the most likely change is among integers, but I have gone to using int for tinyint, smallint, int. I'd probably use it for bigint, too, but I haven't needed that yet. I've worked with databases without this with generally minimal confusion, and I can see both sides, but it seems odd that so many dismiss it out of hand.