Prevent sp_ Procedures with SQLCop

  • Steve Jones - SSC Editor

    SSC Guru

    Points: 716562

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

  • Vaidy Mohan

    SSC Enthusiast

    Points: 183

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

  • Ness

    SSCarpal Tunnel

    Points: 4282

    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

    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: 148431

    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

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

  • SQLvis

    Right there with Babe

    Points: 740

    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: 148431

    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: 716562

    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: 716562

    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: 31462

    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

    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: 716562

    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: 995116

    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.
    "If you think its expensive to hire a professional to do the job, wait until you hire an amateur."--Red Adair
    "Change is inevitable... change for the better is not."
    When you put the right degree of spin on it, the number 3|8 is also a glyph that describes the nature of a DBAs job. 😉

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

  • RonKyle

    SSC-Dedicated

    Points: 31462

    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.

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

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