November 4, 2015 at 11:03 am
I answered 1,2, & 3 as I knew 1 and 2 would give syntax errors.
But then I was also thinking all of the above as you should also avoid not including a schema name!
November 4, 2015 at 11:09 am
tom.w.brannon (11/4/2015)
tom.w.brannon (11/4/2015)
I answered All of the Above because I understood it to be about practices, not syntax. I agree with others that bad syntax will be avoided because it won't work. However, while the other two answers are syntactically correct but both should be avoided according to https://www.mssqltips.com/sqlservertutorial/169/naming-conventions-for-sql-server-stored-procedures/How can it be 'all of the above'? There's nothing wrong with option 4, particularly as the questioner says 'Note Myproc is a placeholder and could be any meaningful name'.
I was citing a source that recommended using a prefix on the stored procedures so the stored procedure should not just be an unqualified word/phrase. I found the wording of the question confusing, made my choice and got the result. The school solution and discussion contribute to my education.
Some of us prefer to avoid prefixes for most objects. Procedures should just be descriptive on the action they perform. Adding a prefix only slows down coding.
November 4, 2015 at 12:14 pm
Luis Cazares (11/4/2015)
Some of us prefer to avoid prefixes for most objects. Procedures should just be descriptive on the action they perform. Adding a prefix only slows down coding.
When creating an object without specifying the prefix, SQL Server will have to take extra steps to look up the default schema for your userID. That's just a minor performance overhead but it can be avoided.
When accessing an object without specifying the prefix, SQL Server will first have to take those same extra steps (same overhead, but accessing objects occurs more often than creating them so it can become more of an issue). It will then check if the object exists in that schema. If it doesn't, it will then check if the object exists in dbo. All that together adds up to a significant performance overhead.
Worse than this: any query (and that includes queries in the context of a stored procedure!!) will have its execution plan marked as unsafe for reuse. You may end up getting a copy of each execution plan for each individual user. That is because for a different user, the same object name might resolve to a different object (based on default schema).
Probably even more important, SQL Server will allow objects of the same name to exist in different schemas. This can result in weird and hard to explain "bugs" if someone creates a new object in schema X when an object with the same name already exists in schema dbo - for some users, the same stored procedure can suddenly produce incorrect results because a query now resolves to the new object in schema X.
Trading four keystrokes for a combination of various performance hits, extra pressure on the procedure cache, extra compilations, and a chance of incorrect results does not sound like a good trade to me.
November 4, 2015 at 12:14 pm
The fact that most people got it wrong speaks for itself.
November 4, 2015 at 12:18 pm
Hugo Kornelis (11/4/2015)
Luis Cazares (11/4/2015)
Some of us prefer to avoid prefixes for most objects. Procedures should just be descriptive on the action they perform. Adding a prefix only slows down coding.When creating an object without specifying the prefix, SQL Server will have to take extra steps to look up the default schema for your userID. That's just a minor performance overhead but it can be avoided.
When accessing an object without specifying the prefix, SQL Server will first have to take those same extra steps (same overhead, but accessing objects occurs more often than creating them so it can become more of an issue). It will then check if the object exists in that schema. If it doesn't, it will then check if the object exists in dbo. All that together adds up to a significant performance overhead.
Worse than this: any query (and that includes queries in the context of a stored procedure!!) will have its execution plan marked as unsafe for reuse. You may end up getting a copy of each execution plan for each individual user. That is because for a different user, the same object name might resolve to a different object (based on default schema).
Probably even more important, SQL Server will allow objects of the same name to exist in different schemas. This can result in weird and hard to explain "bugs" if someone creates a new object in schema X when an object with the same name already exists in schema dbo - for some users, the same stored procedure can suddenly produce incorrect results because a query now resolves to the new object in schema X.
Trading four keystrokes for a combination of various performance hits, extra pressure on the procedure cache, extra compilations, and a chance of incorrect results does not sound like a good trade to me.
Hugo, I don't think Luis was talking about not naming the schema. He was talking about a prefix like "sp_" or "usp" or any of the other rather silly prefixes, not the schema.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
November 4, 2015 at 12:22 pm
Sean Lange (11/4/2015)
Hugo, I don't think Luis was talking about not naming the schema. He was talking about a prefix like "sp_" or "usp" or any of the other rather silly prefixes, not the schema.
Ahh. In that case I fully agree. I think that adding a prefix like "sp" or "usp" to stored procedures is about as useful as writing "door" and "window" on all doors and windows as a reminder of what they are - I know that it's a stored procedure, because it appears in the stored procedures node of object explorer, or because I see the name after the EXEC keyword, so I do not need the additional reminder.
November 4, 2015 at 12:23 pm
Lambasted again for a poorly worded question, and yes I have an apostrophe in my name but is that any reason to pillion someone more than if they have an e o, or a in their name, apparently it is and sad to see in such an intelligent community.
Some at least saw the point of the question as being “you should avoid the use of sp_ as a prefix for stored procedures”: The question was prompted by yet again seeing a high street software product which ships with a database in which the developers, have almost exclusively, used sp_ as a prefix for stored procedures! The support staff reaction when I pointed it out? “We have not had any problems so far”. It made me wonder why their server spec was so high though.
I accept that if I had changed the options available e.g. using brackets around names, then the name was viable, but where does that end, how many option is it feasible to provide? And besides which of these names are desirable?
Many thanks for the few supportive comments received, I can only hope a few have learned not to use sp_ as a prefix, but I doubt anyone really has and those that know not to do so will continue to use their own convention.
...
November 4, 2015 at 12:24 pm
tom.w.brannon (11/4/2015)
I answered All of the Above because I understood it to be about practices, not syntax. I agree with others that bad syntax will be avoided because it won't work. However, while the other two answers are syntactically correct but both should be avoided according to https://www.mssqltips.com/sqlservertutorial/169/naming-conventions-for-sql-server-stored-procedures/
I know that naming conventions are highly subject to opinion but I disagree with almost every point in that article. I truly dislike prefixes on objects, they are pointless if the names make sense. I also really don't like things like uspInsertPerson. What this does is totally screws up the list of procedures. When you look at the list in SSMS all your insert procedures will be together. This is a nightmare on a system with a lot of objects. I would prefer to group objects together with a little different twist. I would prefer a name more like Person_Insert for a procedure that inserts into the Person table. I know this it totally off topic for this QOTD but I felt I needed to comment. 😀
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
November 4, 2015 at 12:40 pm
sipas (11/4/2015)
And this question comes from someone with an apostrophe in their name - surely a shameful practice that needs to be eradicated in the interest of making everyone's lives a little bit simpler. But don't tell De'Anthony Thomas I said that.
HUH??? Your comment here makes no sense to me. Are you somehow saying that because this person has an apostrophe in their name that their name is somehow shameful and they should change it??? Why should they have to change their name because your software can't handle it? I just don't understand this statement at all.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
November 4, 2015 at 12:44 pm
g.britton (11/4/2015)
Iulian -207023 (11/4/2015)
g.britton (11/4/2015)
Carlo Romagnano (11/4/2015)
If I want to be pedant, the right answer is "All of the above".Because of lacking of batch separator "GO".
If you run the script no procedure is created.
+1. Also because we should really always specify the schema.
well ... why ? 🙂
I mean why should always specify the schema ?
To be sure the proc is going where you want it. You may have a default schema (not dbo) for your user id in the target database but you want it in dbo. Or you may want it in some other schema. Explicit is better than implicit.
Thanks Gerald ,
This makes me think about having a schema for the application's procedures, I mean a schema only for application's/module functionality (procs + functions).
And of course reffering to a specific proc by schema.proc_name.
How do you see this aproach ? ( others might have pros and cons so please feel free to share )
Iulian
November 4, 2015 at 1:35 pm
Sean Lange (11/4/2015)
sipas (11/4/2015)
And this question comes from someone with an apostrophe in their name - surely a shameful practice that needs to be eradicated in the interest of making everyone's lives a little bit simpler. But don't tell De'Anthony Thomas I said that.HUH??? Your comment here makes no sense to me. Are you somehow saying that because this person has an apostrophe in their name that their name is somehow shameful and they should change it??? Why should they have to change their name because your software can't handle it? I just don't understand this statement at all.
Thank you Sean, Have had to tackle this issue so many times it is tiring.
...
November 4, 2015 at 3:09 pm
This question tested more of my English understanding rather than the actual problem. Question should have been expanded. A quick read by anybody would consider syntax errors also.
November 5, 2015 at 2:56 am
happygeek (11/4/2015)
Sean Lange (11/4/2015)
sipas (11/4/2015)
And this question comes from someone with an apostrophe in their name - surely a shameful practice that needs to be eradicated in the interest of making everyone's lives a little bit simpler. But don't tell De'Anthony Thomas I said that.HUH??? Your comment here makes no sense to me. Are you somehow saying that because this person has an apostrophe in their name that their name is somehow shameful and they should change it??? Why should they have to change their name because your software can't handle it? I just don't understand this statement at all.
Thank you Sean, Have had to tackle this issue so many times it is tiring.
Apologies for any upset caused - it was just a pathetic attempt at humour - I won't do it again.
November 5, 2015 at 9:27 am
sipas (11/5/2015)
happygeek (11/4/2015)
Sean Lange (11/4/2015)
sipas (11/4/2015)
And this question comes from someone with an apostrophe in their name - surely a shameful practice that needs to be eradicated in the interest of making everyone's lives a little bit simpler. But don't tell De'Anthony Thomas I said that.HUH??? Your comment here makes no sense to me. Are you somehow saying that because this person has an apostrophe in their name that their name is somehow shameful and they should change it??? Why should they have to change their name because your software can't handle it? I just don't understand this statement at all.
Thank you Sean, Have had to tackle this issue so many times it is tiring.
Apologies for any upset caused - it was just a pathetic attempt at humour - I won't do it again.
Stupid internet and not being able to hear people's inflection!!! No problem here at all. I always appreciate good humor I just wasn't sure if you were trying to funny or not. 😀
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
November 6, 2015 at 7:17 am
To my opinion, the correct answer is 1,2,3.
The questing was "should you avoid" and the answer is: "Yes, you should". For different reasons, but avoid.
Viewing 15 posts - 46 through 60 (of 74 total)
You must be logged in to reply to this topic. Login to reply