February 13, 2013 at 8:28 am
Andy Hyslop (2/11/2013)[/b
...
Why should User Defined Functions not be prefixed UDF or udf, views not Prefixed with VW or vw and Stored Procedures not USP or usp
...
That is an odd question to show up on a SQL test. There are no technical reasons (that I know of) why you can't do that, or why the application would work better.
I have seen many applications that used UDF, VW, and USP, but I have never seen any actual application problems caused by this.
Of course there are many good reasons not to use them that others have pointed out, but in the end they amount to style preference, standards, ease of coding, ease of understanding, etc.
February 13, 2013 at 8:39 am
Grant Fritchey (2/13/2013)
Ever is probably an over-reaction, but in general these types of naming conventions just don't make sense. How is tblInvoices easier to understand than Invoices? How is it more clear? Further, when you get hit by a bus, what does it communicate to the next person who takes over your job. I just do not see it.I'll give you an exception I have used and that's for lookup data. You know, the little name/value pair tables that are just there to enforce select lists? On those I've used c_TableName. The 'c_' stood for Code tables. It served two purposes. It made them all group together within SSMS, and it differentiated these tables from real system tables.
However, now, I wouldn't even do that. I'd put them into a separate schema but with standard names. It would serve the same purpose but improve the overall readability. Instead of c_ which means nothing unless I tell you what it means, I could use Code.TableName or Lookup.TableName which makes things more clear.
Naming standards should improve clarity and communication. Specialized abbreviations just don't add to the clarity. In fact, they tend to reduce it. But, this is one of those things that comes down to preference, not a technical reason. So if it works for you, go for it. But it's not a standard I'd support given the choice.
On the other hand, I don't necessarily want all of those lookup tables to group together. I'd rather have them group together with the tables they support. Although I hate the idea of using Hungarian notation to identify the object type, I'm all for relational prefixes that identify the main process they're associated with. For example, if I'm dealing with loans, I'll have a table call "Loan". I'll have a lookup table for loan type and rather than calling it c_LoanType or putting it in a separate schema, which will appear quite the physical distance from the Loan table in the object explorer in the size databases I work with, I'll call the table "LoanType" so it's very close or maybe even adjacent to the "Loan" table in the object explorer. I also have an audit table for the loan table. Rather than grouping all the audit tables together by using the "Audit*" naming convention, I call the table "LoanAudit" so that you can easily tell there's an audit table for the "Loan" table just by having the same leading prefix as the main table name.
Shifting gears, there are two schools of thought. One is to prefix object names using Hungarian notation and, trying desperately to not come across like Joe Celko, I just see no merit in that especially since things can change. The other school of thought is to group names by the functionality they have according to functionally related areas (Loan, LoanType, LoanAudit, Customer, CustomerType, CustomerAudit, for example) and THAT type of prefix makes a lot of sense.
There's also more than one type of database which adds to the "It Depends" notion of prefixing. I can easily see how object name prefixes, such as "Dim" or "Fact", would be extremely helpful in a data warehouse. The problem there is that some people overdo it IMHO. For example, it's not likely that I'd name a general purpose Calendar table "Dim_Calendar". Maybe "Util_Calendar" but not "Dim_Calendar". I still lean away from using prefixes of such a nature though because they would physically separate (for example) related tables such as "Fact_Loan" from "Dim_LoanType". I'll probably have every DW expert in the world yell at me because it violates status quo naming conventions but if forced to identify a table as either a dimension table or a fact table, I'd rather use suffixes so that all the (for example) Loan related tables are physically grouped together in object explorer no matter if they're Dim or Fact or something else. The exception to that rule would be general purpose utility tables that would get the "Util" prefix or live in the "Util" schema for obvious separation.
With the possible exception of Hungarian notation (again, just my opinion but should never be used), it truly "Depends". That, not withstanding, the key for everyone, whether it's good or bad in their opinion, is to follow the "standard" used in whatever shop you're working in. Doesn't matter whether you're an FTE or a consultant, follow what is currently in place for a given database because consistency if very important is naming conventions.
Again, just expressing an opinion here.
--Jeff Moden
Change is inevitable... Change for the better is not.
February 13, 2013 at 8:44 am
Michael Valentine Jones (2/13/2013)
Andy Hyslop (2/11/2013)[/b
...
Why should User Defined Functions not be prefixed UDF or udf, views not Prefixed with VW or vw and Stored Procedures not USP or usp
...
That is an odd question to show up on a SQL test. There are no technical reasons (that I know of) why you can't do that, or why the application would work better.
I have seen many applications that used UDF, VW, and USP, but I have never seen any actual application problems caused by this.
Of course there are many good reasons not to use them that others have pointed out, but in the end they amount to style preference, standards, ease of coding, ease of understanding, etc.
I completely agree, it was unusual and that's why it threw me....totally!!! :-S
I really couldn't think of a valid reason and my answer reflected as such..
Although I am seeing some valid reasons why not to and am enjoying the discussion I just cant see any real impact that necessitates the term "must not be used"...:crazy:
==========================================================================================================================
A computer lets you make more mistakes faster than any invention in human history - with the possible exceptions of handguns and tequila. Mitch Ratcliffe
February 13, 2013 at 8:49 am
I'll probably have every DW expert in the world yell at me because it violates status quo naming
Consider this a "Yelling" Jeff 😉
That, not withstanding, the key for everyone, whether it's good or bad in their opinion, is to follow the "standard" used in whatever shop you're working in
That was actually my answer, almost word for word
Andy
==========================================================================================================================
A computer lets you make more mistakes faster than any invention in human history - with the possible exceptions of handguns and tequila. Mitch Ratcliffe
February 13, 2013 at 9:38 am
Jeff Moden (2/13/2013)
On the other hand, I don't necessarily want all of those lookup tables to group together. I'd rather have them group together with the tables they support. Although I hate the idea of using Hungarian notation to identify the object type, I'm all for relational prefixes that identify the main process they're associated with. For example, if I'm dealing with loans, I'll have a table call "Loan". I'll have a lookup table for loan type and rather than calling it c_LoanType or putting it in a separate schema, which will appear quite the physical distance from the Loan table in the object explorer in the size databases I work with, I'll call the table "LoanType" so it's very close or maybe even adjacent to the "Loan" table in the object explorer. I also have an audit table for the loan table. Rather than grouping all the audit tables together by using the "Audit*" naming convention, I call the table "LoanAudit" so that you can easily tell there's an audit table for the "Loan" table just by having the same leading prefix as the main table name.Shifting gears, there are two schools of thought. One is to prefix object names using Hungarian notation and, trying desperately to not come across like Joe Celko, I just see no merit in that especially since things can change. The other school of thought is to group names by the functionality they have according to functionally related areas (Loan, LoanType, LoanAudit, Customer, CustomerType, CustomerAudit, for example) and THAT type of prefix makes a lot of sense.
There's also more than one type of database which adds to the "It Depends" notion of prefixing. I can easily see how object name prefixes, such as "Dim" or "Fact", would be extremely helpful in a data warehouse. The problem there is that some people overdo it IMHO. For example, it's not likely that I'd name a general purpose Calendar table "Dim_Calendar". Maybe "Util_Calendar" but not "Dim_Calendar". I still lean away from using prefixes of such a nature though because they would physically separate (for example) related tables such as "Fact_Loan" from "Dim_LoanType". I'll probably have every DW expert in the world yell at me because it violates status quo naming conventions but if forced to identify a table as either a dimension table or a fact table, I'd rather use suffixes so that all the (for example) Loan related tables are physically grouped together in object explorer no matter if they're Dim or Fact or something else. The exception to that rule would be general purpose utility tables that would get the "Util" prefix or live in the "Util" schema for obvious separation.
With the possible exception of Hungarian notation (again, just my opinion but should never be used), it truly "Depends". That, not withstanding, the key for everyone, whether it's good or bad in their opinion, is to follow the "standard" used in whatever shop you're working in. Doesn't matter whether you're an FTE or a consultant, follow what is currently in place for a given database because consistency if very important is naming conventions.
Again, just expressing an opinion here.
I hope no one is shocked when I basically agree with you. I like the concept of grouping through words, but clear language, not embedded codes.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
February 13, 2013 at 10:10 am
Given that the q said "why should they not be prefixed", I think it's OK. It shouldn't be done because it does slow down name lookup for those objects, which of course must be done every time a query plan is built.
As others have noted, I dislike adding "_vw" to views for the same reason: at some future point a view might become a table, and a table might become a view.
As to "usp": it is illogical. The "sp_" in system procs stands for "special". So for a proc named "usp", why is this user proc "special" from other user procs??
Now, sometimes you need to create your owned stored procs with "sp_" to make them "special" in the same way that sys procs are "special", but that's a different subject :-).
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
February 13, 2013 at 1:30 pm
As to "usp": it is illogical. The "sp_" in system procs stands for "special". So for a proc named "usp", why is this user proc "special" from other user procs??
Now, sometimes you need to create your owned stored procs with "sp_" to make them "special" in the same way that sys procs are "special", but that's a different subject
I was always under the impression that usp stood for user stored procedure. I would never recommend using sp for a user stored procedure to avoid any confusion the the stored procs that are inherent to the master database.
My stored procs have a number of possible prefixes, but I think the comment about suffixes is also valid.
I would still have concerns to have no naming convention. I've seen too many disorganized systems that are difficult to figure out. I try not to be wedded to outdated ways, but on the other hand I'm not sure some just need to learn to take more care and think of their successors.
February 13, 2013 at 1:48 pm
Grant Fritchey (2/13/2013)
Ever is probably an over-reaction, but in general these types of naming conventions just don't make sense. How is tblInvoices easier to understand than Invoices? How is it more clear? Further, when you get hit by a bus, what does it communicate to the next person who takes over your job. I just do not see it.I'll give you an exception I have used and that's for lookup data. You know, the little name/value pair tables that are just there to enforce select lists? On those I've used c_TableName. The 'c_' stood for Code tables. It served two purposes. It made them all group together within SSMS, and it differentiated these tables from real system tables.
However, now, I wouldn't even do that. I'd put them into a separate schema but with standard names. It would serve the same purpose but improve the overall readability. Instead of c_ which means nothing unless I tell you what it means, I could use Code.TableName or Lookup.TableName which makes things more clear.
Naming standards should improve clarity and communication. Specialized abbreviations just don't add to the clarity. In fact, they tend to reduce it. But, this is one of those things that comes down to preference, not a technical reason. So if it works for you, go for it. But it's not a standard I'd support given the choice.
I wouldn't use a separate schema, first of all because of the security considerations: you'll break the ownership chain, thus forcing a lot more security checks on objects.
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
February 13, 2013 at 1:50 pm
RonKyle (2/13/2013)
Also, it's extremely inconsistent unless you also name all your tables tblXXX (and DO NOT, EVER, do that, ).
Why would you never do this? I've seen dimension tables prefixed Dim and fact tables prefixed Fact. I use dtbl and ftbl. With my tables, I have tbl, tlkp, trel, xtbl and stbl. I find this very useful when going back to the design long after it's created to understand what I've done.
As for the stored procedures, I'll use add_, tfm_, sel_ and others. Again, this helps me remember it's purpose when going back to it later.
I don't have many views, but it helps to have at least vw_ if only to distinguish it from tables.
I've been doing this a long time and seen the benefits. Without some real factual reason, I don't think it's a good idea to tell someone to never ever use the system.
So what happens when an sel_ proc gets changed to also do adds? What do you name a proc originally does multiple functions? Let's admit it: the object name will never be changed later, no matter how its functionality changes.
You can, and should!, easily document that info lots of other ways that don't interfere with the use and management of the underlying SQL objects.
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
February 13, 2013 at 1:55 pm
Just imagine if SQL itself tried to use all these prefixes? That would be a royal pain!
In general they use the prefix to logically group things, such as sys.dm_db_*, sys.dm_os_*, etc..
Something like sys.vw_dm_db_1 and sys.tbl_dm_db_2 would be a nightmare to try to correlate.
Or sp_sel_configure and sp_set_configure vs just sp_configure.
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
February 13, 2013 at 1:56 pm
ScottPletcher (2/13/2013)
Grant Fritchey (2/13/2013)
Ever is probably an over-reaction, but in general these types of naming conventions just don't make sense. How is tblInvoices easier to understand than Invoices? How is it more clear? Further, when you get hit by a bus, what does it communicate to the next person who takes over your job. I just do not see it.I'll give you an exception I have used and that's for lookup data. You know, the little name/value pair tables that are just there to enforce select lists? On those I've used c_TableName. The 'c_' stood for Code tables. It served two purposes. It made them all group together within SSMS, and it differentiated these tables from real system tables.
However, now, I wouldn't even do that. I'd put them into a separate schema but with standard names. It would serve the same purpose but improve the overall readability. Instead of c_ which means nothing unless I tell you what it means, I could use Code.TableName or Lookup.TableName which makes things more clear.
Naming standards should improve clarity and communication. Specialized abbreviations just don't add to the clarity. In fact, they tend to reduce it. But, this is one of those things that comes down to preference, not a technical reason. So if it works for you, go for it. But it's not a standard I'd support given the choice.
I wouldn't use a separate schema, first of all because of the security considerations: you'll break the ownership chain, thus forcing a lot more security checks on objects.
Don't you need security considerations to well have security? Wouldn't breaking the ownship chain foster security since permissions would have to be explicitly granted.
February 13, 2013 at 9:10 pm
Grant Fritchey (2/13/2013)
I hope no one is shocked when I basically agree with you. I like the concept of grouping through words, but clear language, not embedded codes.
I'll strongly second that. Sorry I wrote so much under your post. Most of it wasn't as a result of your post. I just wanted to give folks a stong hint as to how I've made my life a whole lot easier.
--Jeff Moden
Change is inevitable... Change for the better is not.
February 13, 2013 at 9:19 pm
ScottPletcher (2/13/2013)
I wouldn't use a separate schema, first of all because of the security considerations: you'll break the ownership chain, thus forcing a lot more security checks on objects.
I believe that's one of the main purposes of schemas. Don't get me wrong... I'm a frequent flyer of the "dbo only" club but, I have to ask, what's wrong with breaking the ownership chain when that's normally why someone would make separate schemas?
--Jeff Moden
Change is inevitable... Change for the better is not.
February 13, 2013 at 10:16 pm
As a former member of the dbo only club who initially used schemas for organizational purposes there are two lessons learned for me.
There may be performance implications (probably negligible) because of the security checks.
Ownership chains need to be addressed. I didn't previously know what they were. My development may be marginally and some what inadvertently more secure. There also is a bit more development overhead configuring security because of the ownership chain issues and a bit more complexity...
February 14, 2013 at 8:56 am
Jeff Moden (2/13/2013)
ScottPletcher (2/13/2013)
I wouldn't use a separate schema, first of all because of the security considerations: you'll break the ownership chain, thus forcing a lot more security checks on objects.I believe that's one of the main purposes of schemas. Don't get me wrong... I'm a frequent flyer of the "dbo only" club but, I have to ask, what's wrong with breaking the ownership chain when that's normally why someone would make separate schemas?
He clearly stated his reason for putting lookup tables into a separate was not for security reasons, but to separate the table names from the main list; yes, that's a bizarre reason for a separate schema, but stated clearly as such nonetheless.
I don't see the need to provide extra, separate security for code lookup tables only. Since when you GRANT SELECT on the main table, you have to remember to also GRANT SELECT on, say, the state code lookup table ... that doesn't seem worthwhile to me as a use for schemas.
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
Viewing 15 posts - 16 through 29 (of 29 total)
You must be logged in to reply to this topic. Login to reply