September 26, 2008 at 11:47 am
J (9/25/2008)
About using singular names for tables:Good idea.
That name is then suitable for constructs such as
ForEach Officer ...
Looks pretty sorry when it is
ForEach Officers.
In that one case, yes, the singular name looks like it makes more sense. But I don't find:
select Name
from Officer -- Is there only one officer?
to make sense. Update, Insert, Delete and Select look more natural to me when the table name is plural, and I use those a LOT more often that I'd ever use a ForEach contstruct.
That's just an opinion, but it does feel more natural to me that way.
I use singular column names, because each column should contain 1 and only 1 value for each row. Again, totally arbitrary, but it just feels right to me that way. Plural for tables, to indicate the multiplicity of rows, singular for columns to indicate the normalization of the table.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
September 26, 2008 at 7:03 pm
The basic table naming convention I try to follow is... what does one row contain? Does it contain a Customer or Customers (answer should be obvious, there)? Same for User, Officer, etc, etc. I also generally try to stay away from "User" because it's a reserver word.
For lookup, definition, or reference tables (whatever you prefer to call them), I follow the ol' "parent table" naming convention. For example... If I have a table called "Customer" and I want to include a custom "type" (ie, business, residential, internal, etc) column in the Customer table (would normally be a CustomerTypeID column), the name of the table to find the ID/Name cross reference would simply be CustomerType and it's primary key column would, in fact, be CustomerTypeID.
For disassociated (ie. General purpose) lookup tables, the name would obviously be a table name with no embedded parent. Further, in order to differentiate between internal "ID's" and "Code's", such as the 2 letter abbreviation for a State or Province, I may have a table called "State" which may or may not have StateID, StateCode, and LongName columns.
Ok, duck... here come the natural key zealots... 😛
--Jeff Moden
September 29, 2008 at 4:18 am
Hi all,
Jane, thanks for posting your article, one things I did notice was;
"Stored procedures should be named by function and the object they work on. Standard prefix like ‘p’, ‘usp’ or ‘sp_’ (contra-indicated by Microsoft for performance reasons) are not recommended"
I understand about 'sp_' as this indicates a system stored procedure to SQL, however does the 'usp' also indicate this?
Thanks,
Jackal
September 29, 2008 at 7:01 am
michael vessey (9/24/2008)
Agree Completely..not just tables and views though - I've inherited a Database written by a .Net developer and all my columns have prefixes and suffixes
for example
Mytable_Mycolumns_tinyint
Particularly odd given that standard procedure for .NET is to do away with Hungarian anyway.
And have I missed something, but do you mean you're getting things like Customer_Postcode_Char as a column in the customer table?
September 29, 2008 at 7:03 am
About naming parameters:
I have been using @pi_ prefix for "parameter, integer" and @ls_ prefix for "local variable, string".
I am one who uses very long stored proedures to say, process incoming Electronic Data Interchange incoming customer purchase orders with multiple validaton steps, etc. And I am not talking about a few hundred lines either. TGhink about an order of magnitude higher. All set-based but (quick, hide the children) procedural code.
Never swa the need to distihguish between input and output parameters, in fact, I have yet to use an output parameter. I welcome any feedback as how an output parameter could be useful in a practical applicaiton.
One thing I will do is return a result code to identify processing errors, and for this I use a local variable instead of an OUTPUT parameter.:
[font="Courier New"]DECLARE @li_RetCode int
SET @li_RetCode = -1
IF @@ERROR <> 0 SET @li_RetCode = -2 ELSE SET @l;i_RetCode = 0
IF @li_RetCode <> 0 RETURN @li_RetCode
IF @@ERROR <> 0 SET @li_RetCode = -2 [/font]
OR
[font="Courier New"]
IF @@ERROR <> 0 SET @li_RetCode = -2 ELSE SET @li_RetCode = 0
IF @li_RetCode = 0 BEGIN
.
. IF @@ERROR <> 0 SET @li_RetCode = -2
END[/font]
OR
DECLARE @lb_InTransaction bit
SET @lb_InTransaction = 0
IF @@ERROR <> 0 SET @li_RetCode = -2 ELSE SET @li_RetCode = 0
IF @li_RetCode <> 0 GOTO RATS
BEGIN TRAN
SET @lb_InTransaction = 0
IF @@ERROR <> 0 SET @li_RetCode = -2
IF @li_RetCode <> 0 GOTO RATS
IF @@ERROR <> 0 SET @li_RetCode = -2
IF @li_RetCode <> 0 GOTO RATS
COMMIT TRAN
RETURN @li_RetCode
RATS:
IF @lb_InTransaction = 1 ROLLBACK TRAN
RETURN @li_RetCode
Any constructive comment on the above is welcome, especially if someone can point out why this is a bad idea.
September 29, 2008 at 7:59 am
I follow the ParentChild table naming convention, but use plural names. Ends up kind of awkward sometimes, like "CustomersTypesCategories", but makes it very easy to find things in the Management Studio object window, and very easy to tell what a table is, at a glance. Keeps all related tables together in sequence.
I use an underscore for many-to-many joins. "Customers_Orders" (yes, I had a database with multiple customers per order). Always named those based on the most common left-to-right in From clauses. In 2005, I like to include a synonym for the inverse name: "Orders_Customers". That way, you don't have to remember which way it goes, just use what makes sense.
On the subject of output parameters, I've found them very useful a number of times. I tend to rip large procs into a series of smaller procs that are called by a master proc. Makes documentation and maintenance easier. Passing data back and forth by using input and output parameters is quite efficient in this case. If I have multiple rows to pass back and forth, I'll have the master proc build a temp table, and the sub procs insert into it. If it's not multi-row, I usually use params.
On using a local variable to return error values, that works. I haven't had much use for it, since I tend these days to use Try...Catch, and have a proc specifically for handling those.
On naming procs, I like to use the main table they affect/select and a very brief action description in the name. The place I currently work prefixes proc names with "ins_", "del_", "sel_", "upd_", but I can't recommend that, since it makes things harder to find in the object explorer. It's fine for single-table CRUD, but it's a mess for anything more complex.
If I'm working on procs that affect the Customers table, I want them all listed under things like "CustomersCreate", "CustomersList", "CustomersDel" (based on table "Customers"). That way, if I need to refactor the table at all, I don't have to look all over the place to find them, they're all listed one after the other.
Of course, that breaks down a bit in any database that's at all complex, since procs for the Orders table will often have to pull data from the Customers table (joins, Where In, etc.). Still have to select from sys.sql_modules where definition like '%customers%', to make sure I get everything. But most of the code will be in one place most of the time. Makes it easier for me.
Oh, and on Jeff's thing of naming the table's ID column, on parent tables, I use "CustomerID", "OrderID", etc. On child tables, I don't use the parent table name in the ID column. So the ID for "CustomersTypes" would be "TypeID". Means I have to use a few more two-part names, but it's slightly less typing overall in the long run. (This from a guy who creates tables with names like "CustomersTypesCategories". Less typing! Ha!)
Other people think different ways, and use different standards. None of them are "absolutely right", it's very subjective.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
September 29, 2008 at 8:13 am
Try...Catch instead of @li_RetCode for error handling...
I forgot this is part of the SS2K5 forum. Mentioning the new way of doing things is a valid point as I thought I was in an SS2K forum.
When I get to SS2K5, I will use the new constructs, but I am not about to go back through all my stored procedures to retrofit the new construct.
September 29, 2008 at 2:18 pm
Jackal (9/29/2008)
Hi all,Jane, thanks for posting your article, one things I did notice was;
"Stored procedures should be named by function and the object they work on. Standard prefix like ‘p’, ‘usp’ or ‘sp_’ (contra-indicated by Microsoft for performance reasons) are not recommended"
I understand about 'sp_' as this indicates a system stored procedure to SQL, however does the 'usp' also indicate this?
Thanks,
Jackal
No... 'usp' does not indicate a system stored procedure... it just indicates bad form because it uses Hungarian notation. 😛
--Jeff Moden
September 30, 2008 at 7:33 am
I wish I could have scrapped Hungarian notation on the database I've been working on for the past couple of years. Almost every table has a tbl... prefix, which adds nothing and has led to there being several views with tbl... prefixes as requirements evolved. 🙁
My recommendations are along the lines of Jane's document. Avoid underscores. Use Pascal case. Prefix things so they group nicely independent of what they are (e.g.procedures ClientInsert & ClientUpdate rather than the other way, tables like IssueStatus, IssueText, IssueAssigneeList, etc.).
Oh, for the luxury of redesigning a system from scratch... 🙂
Derek
September 30, 2008 at 6:19 pm
The naming convention I prefer is as follows.
Table Names :
Use the plural of the entity.
Customers
Parent Child tables :
Use the plural of the last entity - lose plural on parent entity
CustomerOrders
CustomerOrderItems
Stored Procedures :
Prefix with "p_"
First word in name is action being performed
Singular Entity (even for searches that return multiple rows).
By clause used for Criteria
p_GetCustomerOrderByCustomerName
p_CreateCustomerOrder
Columns :
No Prefix.
Singular
Surname
ID's all use singular entity name
CustomerID
Parameters :
No prefix
Singular
@Surname nvarchar(60)
Local Variables :
Prefix with v_
Singular
declare @v_MyError int
-- John Oliver
Sometimes banging your head against a wall is the only solution.
October 1, 2008 at 9:30 am
Naming stored procedures:
[font="Courier New"]
stp_ prefix, arbitary, to avoid using "sp".
the name of the application module which invokes the stored proc.
what it does
example:
stp_EDI_Import_Customer_Order.
[/font]
This way, the stored procs invoked by the same module are listed one next to the other and I have to do less searching to find a particular stored proc., instead of sometimes having to go back to the application code and find out from there the name of the stored proc invoked.
Or, if a stored proc is part of a chain to maintain inventory then I would use something like
stp_INV_Sales_Remove_Stock,
stp_INV_Receiving_Add_Stock, etc.
October 1, 2008 at 9:53 am
I guess it's best practise then to always define a case for objects to be in, i.e always lower case. As execution plans for procedures are case sensitive.
This way I can just tell the developer to run stored procedure 'dbo.selectthisandthat' and not have to worry about them calling 'dbo.SelectThisAndThat' and getting potential cache misses.
Jackal
October 1, 2008 at 8:57 pm
Jackal (10/1/2008)
As execution plans for procedures are case sensitive.
Please show me a Microsoft document or viable test code that proves that. 🙂
--Jeff Moden
October 2, 2008 at 1:50 am
Hi Jeff,
The following article contains a sample test.
http://qa.sqlservercentral.com/articles/Performance+Tuning+and+Scaling/hitsandmisses/1776/
When I replicated the test (in SS2K), it did appear to be as the article listed. However, re-doing the test this morning (in SS2K5) does not provide the results as the article defines. I guess it would be best for Mike to comment on this, as it is his article.
Jackal
October 2, 2008 at 6:23 am
Thanks, Jackal. I sure appreciate it. I'll take a look.
--Jeff Moden
Viewing 15 posts - 16 through 30 (of 58 total)
You must be logged in to reply to this topic. Login to reply