Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««123»»

Naming Convention for UDF's, Views and SP's Expand / Collapse
Author
Message
Posted Tuesday, February 12, 2013 3:43 AM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Today @ 10:09 AM
Points: 15,661, Visits: 28,050
Chrissy321 (2/11/2013)
I have never been a tbl-er but I have been a usp-er.

Lately I put everything in a schema. Is this a reasonable statement as far as a best practice? Always put all objects in a schema besides dbo.

Educational comments most welcome....


It depends.

If I have discrete sets of functionality, then I break things up into schemas. But it needs to be substantial. I've never created a schema for 2 or 3 tables alone. If there really isn't any way to differentiate the parts of an application or if the application is small, then I just leave it in dbo. Since schema ownership can make things easier to manage, I find it helpful for big databases.


----------------------------------------------------
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood..." Theodore Roosevelt
The Scary DBA
Author of: SQL Server 2012 Query Performance Tuning
SQL Server 2008 Query Performance Tuning Distilled
and
SQL Server Execution Plans

Product Evangelist for Red Gate Software
Post #1418848
Posted Tuesday, February 12, 2013 4:02 AM


Say Hey Kid

Say Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey Kid

Group: General Forum Members
Last Login: Friday, August 22, 2014 9:02 AM
Points: 689, Visits: 2,779
Thanks all for the replies - most helpful

I have never prefixed tables with tbl but I have prefixed usp's and views

It just seemed like an odd question the way it was phrased, I think the word never prefix was a little strong and that threw me a little.

From what I can gather from the conversation, yes it makes sense not to do so (and I won't from now on) and there are good reasons not to however as I said never was a little strong, is there a huge impact (for example to coin Jeffs phrase writing RBAR when set based will do the job better) then no?


==========================================================================================================================
A computer lets you make more mistakes faster than any invention in human history - with the possible exceptions of handguns and tequila. Mitch Ratcliffe
Post #1418858
Posted Wednesday, February 13, 2013 7:01 AM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Yesterday @ 2:43 PM
Points: 814, Visits: 2,014
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.



Post #1419491
Posted Wednesday, February 13, 2013 7:18 AM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Today @ 10:09 AM
Points: 15,661, Visits: 28,050
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.


----------------------------------------------------
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood..." Theodore Roosevelt
The Scary DBA
Author of: SQL Server 2012 Query Performance Tuning
SQL Server 2008 Query Performance Tuning Distilled
and
SQL Server Execution Plans

Product Evangelist for Red Gate Software
Post #1419501
Posted Wednesday, February 13, 2013 7:49 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 3:16 PM
Points: 13,301, Visits: 12,159
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.


I know what you mean about wanting to know the purpose. This is actually quite useful. I prefer to use a suffix for this type of thing instead of a prefix. This serves two main advantages. First it will keep like items grouped in SSMS. And secondly I find it makes it far easier to code with. Say you have a Product and you need the basic CRUD sprocs. With your naming convention you would have add_Product, sel_Product, del_Product, upd_Product. Those are certainly clear what they do but they are hard to code with and in SSMS you will have stuff scattered all over. If instead you moved it to the end like this. Product_add, Product_sel, Product_del, Product_upd. Now when you are coding you think I need the Product sproc, intellisense will now show you only those that start with Product. For me I also think first of the object I am working with and then what I need to do to it. Just my 2¢.


_______________________________________________________________

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 Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1419529
Posted Wednesday, February 13, 2013 8:28 AM
Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Today @ 1:10 PM
Points: 3,135, Visits: 11,480
[b]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.


Post #1419562
Posted Wednesday, February 13, 2013 8:39 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 4:03 PM
Points: 36,983, Visits: 31,508
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
"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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1419576
Posted Wednesday, February 13, 2013 8:44 AM


Say Hey Kid

Say Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey Kid

Group: General Forum Members
Last Login: Friday, August 22, 2014 9:02 AM
Points: 689, Visits: 2,779
Michael Valentine Jones (2/13/2013)
[b]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"...


==========================================================================================================================
A computer lets you make more mistakes faster than any invention in human history - with the possible exceptions of handguns and tequila. Mitch Ratcliffe
Post #1419581
Posted Wednesday, February 13, 2013 8:49 AM


Say Hey Kid

Say Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey Kid

Group: General Forum Members
Last Login: Friday, August 22, 2014 9:02 AM
Points: 689, Visits: 2,779
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
Post #1419586
Posted Wednesday, February 13, 2013 9:38 AM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Today @ 10:09 AM
Points: 15,661, Visits: 28,050
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
The Scary DBA
Author of: SQL Server 2012 Query Performance Tuning
SQL Server 2008 Query Performance Tuning Distilled
and
SQL Server Execution Plans

Product Evangelist for Red Gate Software
Post #1419621
« Prev Topic | Next Topic »

Add to briefcase ««123»»

Permissions Expand / Collapse