November 13, 2011 at 1:55 pm
Is there any documented Naming Conventions for Tables, Views and Functions?
We know that Stored Procedures names are prefixed with sp_, but is there a prefix convention such as tbl_ for Table names, vew_ for View names and udf_ for Function names, etc.
November 13, 2011 at 3:17 pm
Graham-1036457 (11/13/2011)
We know that Stored Procedures names are prefixed with sp_
sp_ means system procedure and a procedure with that naming has special resolution rules intended for system objects. It is not a prefix that should be used for user procedures as it can have some interesting implications.
Try this (in a test DB)
CREATE PROCEDURE sp_help (@Name varchar(10))
AS
PRINT 'Help me ' + @Name
GO
EXEC sp_help 'John'
GO
Oops...
Personally I loath any form of prefixing. It's a waste (I know it's a procedure if it appears in the form EXEC ObjectName). It make it hard to refactor tables and put views in their place (leaves me with views starting with tbl) and it has little to no gain. Hungarian notation is supposed to be about what a variable is for, not what type is is
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
November 13, 2011 at 4:17 pm
Hi Gail,
Thank you for your esponse. I guess the answer is No, there is no Naming Convention, other than the prefix of sp_ for Stored Procs which I mentioned.
What I was looking for is a Prefix to distinguish Tables from Views. They get lumped into one bucket when using a .NET front end with Table Adapters and I was looking for a convention that may distinguish between them.
I guess I need to use my own.
Regards, Graham.
November 13, 2011 at 4:57 pm
Graham-1036457 (11/13/2011)
Hi Gail,Thank you for your esponse. I guess the answer is No, there is no Naming Convention, other than the prefix of sp_ for Stored Procs which I mentioned.
No, the convention you mentioned does not exist. The sp prefix indicates system stored procedures (stored procedures which are an essential component of the RDBMS software, eg of SQLServer, and should never be used for stored procedures designed as part of a database supported by a RDBMS. I think you must have badly misunderstood Gail's reply.
There are in fact many naming conventions, but none of them are supported by a large enough number of practitioners to matter. Most of them are dangerous misguided nonsense (like the idea of distinguishing views from base tables by a naming convention, as Gail pointed out) but some are harmless and some (generally the simple ones that suggest for example that you should not call the table that provides the annual salary of employees the AccountsRecievable table instead of something like the Salary table) are just common sense.
What I was looking for is a Prefix to distinguish Tables from Views. They get lumped into one bucket when using a .NET front end with Table Adapters and I was looking for a convention that may distinguish between them.
Generally making that distinction in names is a bad idea. Any time you decide to partition a table so that it becomes a view you have the problem that everything that refers to it has to change - big and nasty maintenance problem unless you are 100% certain that you have detected every business rule so that no further schema normalisation can possibly be needed ever in the future. Of course if you only expect your application to live for half a year, you can inflict such database naming conventions on it - they wcan do no more than short term harm; but most people try to design for a useful product life, generally more than six months.
Tom
November 13, 2011 at 4:59 pm
Graham-1036457 (11/13/2011)
I guess the answer is No, there is no Naming Convention, other than the prefix of sp_ for Stored Procs which I mentioned.
As I said, sp_ is not a prefix for stored procedures. It's a prefix for system procedures. It should not be used for user procedures. It can have interesting side effects.
What I was looking for is a Prefix to distinguish Tables from Views. They get lumped into one bucket when using a .NET front end with Table Adapters and I was looking for a convention that may distinguish between them.
I guess I need to use my own.
That's something I always argue against. There are cases (and they're not that uncommon) where it may be necessary to refactor a table into multiple tables and place a view (usually with INSTEAD OF triggers) in its place so as not to break front end apps. If you have some naming convention to distinguish views from tables and you need to do something like that, you have two unpleasant options:
- Change all front end apps to keep the naming convention correct (which is what you're trying to avoid by putting a view down in place of the table)
- Create a view that has a tbl prefix. (yuck)
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
November 13, 2011 at 5:10 pm
I agree with Gail. I really hate the vw_ or tbl_ or variations of such prefixes. I have inherited databases like that and the re-factoring problem is coming into play.
I don't need a prefix to tell me if it is a table or a view. The app should not care what the prefix is and frankly it (and devs) don't really need that prefix either. It seems the only purpose it serves is to tell people that are coding that the data is coming from a table or a view etc etc etc. That is not a benefit imho.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
November 13, 2011 at 5:40 pm
Thank you Gail, Jason and Tom.
I certainly have missunderstoog Gail's advice. I thought sp_ was to prefix all Stored Procs.
I'll fix that straight away and consider how to rethink my distinction concerns.
I'll get there I hope. 🙂
Regards, Graham.
November 14, 2011 at 7:53 am
SQLRNNR (11/13/2011)
I agree with Gail. I really hate the vw_ or tbl_ or variations of such prefixes. I have inherited databases like that and the re-factoring problem is coming into play.I don't need a prefix to tell me if it is a table or a view. The app should not care what the prefix is and frankly it (and devs) don't really need that prefix either. It seems the only purpose it serves is to tell people that are coding that the data is coming from a table or a view etc etc etc. That is not a benefit imho.
+1000 on not using crazy prefixes. That has to be one of my biggest pet peeves.
This leads itself to using prefixes on all your column names too. Column name prefixes are simply horrid. How to handle things like foreign keys? Do they transform from cust_CustNumber to order_CustNumber? ACK!!!!!!! Does it matter in the name which table it comes from? That can be handled by using the table alias instead of some arbitrary prefix.
_______________________________________________________________
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 15, 2011 at 2:07 pm
Just to add another 2 cents. Objects with prefixes to explain what they are also don't sort in a friendly manner so as to be able to find them. In large systems there can be hundreds of tables and views. If all tables start with tbl_ then they sort together. I would rather look at sorted tables names starting at 'O' to find OrderHeader and OrderDetail rather than scan the whole list to find what I'm looking for.
Just makes it easier to eyeball.
Todd Fifield
November 15, 2011 at 3:30 pm
tfifield (11/15/2011)
Just to add another 2 cents. Objects with prefixes to explain what they are also don't sort in a friendly manner so as to be able to find them. In large systems there can be hundreds of tables and views. If all tables start with tbl_ then they sort together. I would rather look at sorted tables names starting at 'O' to find OrderHeader and OrderDetail rather than scan the whole list to find what I'm looking for.Just makes it easier to eyeball.
Todd Fifield
If they really ALL start with tbl it's not a problem. It's just visual noise that most folk, including myself, find annoying. 😛
November 15, 2011 at 5:46 pm
Ninja's_RGR'us (11/15/2011)
tfifield (11/15/2011)
Just to add another 2 cents. Objects with prefixes to explain what they are also don't sort in a friendly manner so as to be able to find them. In large systems there can be hundreds of tables and views. If all tables start with tbl_ then they sort together. I would rather look at sorted tables names starting at 'O' to find OrderHeader and OrderDetail rather than scan the whole list to find what I'm looking for.Just makes it easier to eyeball.
Todd Fifield
If they really ALL start with tbl it's not a problem. It's just visual noise that most folk, including myself, find annoying. 😛
Remi,
My eyes aren't what they used to be:crying:. The visual noise really gets to me
November 15, 2011 at 6:14 pm
tfifield (11/15/2011)
Ninja's_RGR'us (11/15/2011)
tfifield (11/15/2011)
Just to add another 2 cents. Objects with prefixes to explain what they are also don't sort in a friendly manner so as to be able to find them. In large systems there can be hundreds of tables and views. If all tables start with tbl_ then they sort together. I would rather look at sorted tables names starting at 'O' to find OrderHeader and OrderDetail rather than scan the whole list to find what I'm looking for.Just makes it easier to eyeball.
Todd Fifield
If they really ALL start with tbl it's not a problem. It's just visual noise that most folk, including myself, find annoying. 😛
Remi,
My eyes aren't what they used to be:crying:. The visual noise really gets to me
I hate it too. Laser operation?
November 15, 2011 at 6:46 pm
No laser. Stronger reading glasses:pinch:
November 16, 2011 at 4:39 am
tfifield (11/15/2011)
No laser. Stronger reading glasses:pinch:
Was just a suggestion. Worked for me, 12 years ago and I'm still fine!
November 16, 2011 at 5:23 am
Graham-1036457 (11/13/2011)
Is there any documented Naming Conventions for Tables, Views and Functions?We know that Stored Procedures names are prefixed with sp_, but is there a prefix convention such as tbl_ for Table names, vew_ for View names and udf_ for Function names, etc.
As the other said, I do not like prefixes as well. In my oppinion they do not add value. When using an object in a DB, you are supposed know what it is so does the RDBMS.
There are lots of naming conventions out if you do a web search on it. Anyone can create his own.
If you are interested in a lenghty treatment you could try Joe Celkos book on this topic "SQL Programming Style" what I consider worth a read.
What I like is the idea of a global data dictionary. That means if your column is named 'organisation_name', so does the variable in the front end and your buttons and field names deviate from it. E. g. 'organisation_name_search_button' , 'organisation_name_textbox', etc.
brgds
Philipp Post
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply