Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


We'll go no more a' tibbling


We'll go no more a' tibbling

Author
Message
Phil Factor
Phil Factor
Right there with Babe
Right there with Babe (741 reputation)Right there with Babe (741 reputation)Right there with Babe (741 reputation)Right there with Babe (741 reputation)Right there with Babe (741 reputation)Right there with Babe (741 reputation)Right there with Babe (741 reputation)Right there with Babe (741 reputation)

Group: General Forum Members
Points: 741 Visits: 2937
Comments posted to this topic are about the item We'll go no more a' tibbling


Best wishes,

Phil Factor
Simple Talk
RobNicholson
RobNicholson
SSC-Enthusiastic
SSC-Enthusiastic (115 reputation)SSC-Enthusiastic (115 reputation)SSC-Enthusiastic (115 reputation)SSC-Enthusiastic (115 reputation)SSC-Enthusiastic (115 reputation)SSC-Enthusiastic (115 reputation)SSC-Enthusiastic (115 reputation)SSC-Enthusiastic (115 reputation)

Group: General Forum Members
Points: 115 Visits: 93
I don't mind tibbling. I've worked with and without it. It makes it easy to classify objects especially in large queries, Example: is that object the whole table or a filtered result set (view)? Object names (tables, SP, views etc) are OK but column names is going a bit far, as mentioned in article these sometimes grow or change.
lokkers
lokkers
SSC Rookie
SSC Rookie (34 reputation)SSC Rookie (34 reputation)SSC Rookie (34 reputation)SSC Rookie (34 reputation)SSC Rookie (34 reputation)SSC Rookie (34 reputation)SSC Rookie (34 reputation)SSC Rookie (34 reputation)

Group: General Forum Members
Points: 34 Visits: 82
I don't mind prefixes when it gives an indication of the objects purpose. For example, a [d] prefix for data tables, [ref] for reference data. Procedures like cussp_GetCustomerById, or addsp_GetAddressForCustomerId tell me immediately that they are customer and address procedures - and with the prefix they are logically grouped together in SSMS. What I hate is opening a database and finding all procedures prefixed [sp]! There is little point in that I think.
dhamilton-905368
dhamilton-905368
SSC-Enthusiastic
SSC-Enthusiastic (155 reputation)SSC-Enthusiastic (155 reputation)SSC-Enthusiastic (155 reputation)SSC-Enthusiastic (155 reputation)SSC-Enthusiastic (155 reputation)SSC-Enthusiastic (155 reputation)SSC-Enthusiastic (155 reputation)SSC-Enthusiastic (155 reputation)

Group: General Forum Members
Points: 155 Visits: 148
Phil,

I frequently hang on your every word. This is a place where I disagree. I am a dreaded developer, but I am also a part-time SQL DBA and have been for many years. I've NEVER done a serious conversion from MS Access to SQL (as your Editorial suggests), however, I have always been, and remain somewhat of an adherent to hungarian notation...

Microsoft has backed off of it, and for good reason, in some areas, but I don't totally agree with their retreat. Hungarian notation had it's place, and whatever the replacement might be, it will have its place. In general, I don't religiously use Hungarian notation for variable names OR [especially] column names (when you see it in column names, that is, to me a clear sign of a database ported from MS Access to MS SQL!).

I do believe that most DATABASES (something most people don't do!) should be prefixed with a "db", most tables should be prefixed with a "tbl" and most stored procedures (which I won't personally do db CRUD without) should be prefixed with "sp". Sticking to this standard makes things so much easier for full time developers like myself to try to track down issues, especially in XML generated by .NET and in VB projects written before .NET existed. (You don't have to search for every "Vendor" occurrence, but only the "tblVendor" occurrences!).

I would NEVER prefix column names however, but I've certainly "lived" in environments where that was common -- due to MS Access roots....

On top of that, on a typical day, I work on 3-6 different clients' systems. One of my better clients had someone that hadn't heard of any of these things, or at least filtered them through too many people before implementing them... Instead I have poorly named [hard to find] databases with similarly named [hard to find] tables. Additionally, to follow the lead of Microsoft, _I guess_, they prefixed ALL of their stored procedures (well over 500) with sp_... We all know what that means....

Anyway, that is my 42 cents. Sorry so long, but I do have some strong opinions here. I predominantly develop in C#, and I really don't use Hungarian notation in most of that code (unless I really think it will help document that code for the next guy.
P Jones
P Jones
Say Hey Kid
Say Hey Kid (682 reputation)Say Hey Kid (682 reputation)Say Hey Kid (682 reputation)Say Hey Kid (682 reputation)Say Hey Kid (682 reputation)Say Hey Kid (682 reputation)Say Hey Kid (682 reputation)Say Hey Kid (682 reputation)

Group: General Forum Members
Points: 682 Visits: 1505
I only really use tibbling on views and stored procs names and have moved right away from it on tables. My application development work is totally through stored procs and the .Net data access layer now so I never refer to the tables directly in program code but to datatable objects within the dataset which are built from often complex select statements referring to multiple tables. In T-SQL short aliases are often used for table names anyway, as column references need quantifying with the table name.

I use various prefixes on stored procs to denote their type eg dap_ for data access layer CRUD) procedures, lp_ for lookup tables procedures, rp_ for procedures used solely for reporting and up_ (user defined proc) for the leftovers.

I really hate tibbling on column names. One database we manage, written by a long departed contractor, has total tibbling and is highly normalised but has no database stored relationships, so that you have to remember that intTitleFK_ID relates to key field PKintTitleID in TblTitles just to get Mr, Mrs or Miss - the name difference in the columns means nothing will link automatically and various int columns have been changed to varchar over the years but not been re-named :-(
dhamilton-905368
dhamilton-905368
SSC-Enthusiastic
SSC-Enthusiastic (155 reputation)SSC-Enthusiastic (155 reputation)SSC-Enthusiastic (155 reputation)SSC-Enthusiastic (155 reputation)SSC-Enthusiastic (155 reputation)SSC-Enthusiastic (155 reputation)SSC-Enthusiastic (155 reputation)SSC-Enthusiastic (155 reputation)

Group: General Forum Members
Points: 155 Visits: 148
I couldn't agree more with P Jones' comments -- I realize I didn't really address the "tibbling" question well, but rather went at it from a developer's point of view.

I like "tibbling" (a new term to me, actually) with regards to databases, tables, views and stored procs, but NEVER in terms of columns. I think P[aul?] said it well in those terms, with regards to us .NET developers, however, he missed a couple of points with regards to someone like me...

I generally have to develop the _best_ code I can without the benefit of a DBA -- I am the DBA. So I run query plans (when something is slow or _potentially_ slow), and work from those. I DO NOT let .NET dictate those sorts of things. I'm too familiar/close to the DB (since I do it all) to trust a "generator" to do it all for me...
Phil Factor
Phil Factor
Right there with Babe
Right there with Babe (741 reputation)Right there with Babe (741 reputation)Right there with Babe (741 reputation)Right there with Babe (741 reputation)Right there with Babe (741 reputation)Right there with Babe (741 reputation)Right there with Babe (741 reputation)Right there with Babe (741 reputation)

Group: General Forum Members
Points: 741 Visits: 2937

I frequently hang on your every word. This is a place where I disagree.

Please feel free to disagree. Tibbling has somehow become a practice in data naming with SQL Server without much discussion, and it is nice to have that discussion with all views represented. Don't mind me. I'm just putting forward the case for the prosecution. It suddenly occurred to me after writing the editorial that the entire process could be automated. One button for 'Tibble my database', another for 'Untibble my database'. Wake up, 3rd party Tool Providers!




Best wishes,

Phil Factor
Simple Talk
daft
daft
Valued Member
Valued Member (60 reputation)Valued Member (60 reputation)Valued Member (60 reputation)Valued Member (60 reputation)Valued Member (60 reputation)Valued Member (60 reputation)Valued Member (60 reputation)Valued Member (60 reputation)

Group: General Forum Members
Points: 60 Visits: 153
dhamilton-905368 (1/25/2010)
...most stored procedures (which I won't personally do db CRUD without) should be prefixed with "sp".

...Additionally, to follow the lead of Microsoft, _I guess_, they prefixed ALL of their stored procedures (well over 500) with sp_... We all know what that means....


Glad you cleared that up :-D

I tend to prefix all mine with "usp" just to differentiate... six and two threes really but I just like to stay away from "sp".

Agree with everything else you said though :-)

I'm from a very similar background and still tend to use tbl prefixes. One project I'm currently working on is a legacy database - all fields are prefixed with str, lng (spot the access conversion!), etc... actually helps to some extent when writing ORM classes in .Net, but looks kinda hideous and, as previously mentioned, these types can change... so I'll stick to tibbling for table names, stored procs and udf's, but strongly avoid it for field names ("fiddling"? :-))
GilaMonster
GilaMonster
SSC-Forever
SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)

Group: General Forum Members
Points: 47212 Visits: 44368
dhamilton-905368 (1/25/2010)
I do believe that most DATABASES (something most people don't do!) should be prefixed with a "db"

Why?

most tables should be prefixed with a "tbl"

Why?

and most stored procedures (which I won't personally do db CRUD without) should be prefixed with "sp".

Why?

If there's a piece of SQL script that reads

USE Travel
EXEC dbo.ScheduleTravel

is there any doubt whatsoever that Travel is a database and ScheduleTravel is a stored procedure?
Can you USE a view or EXEC a database?

All I find that prefixing stored procedures does is make it harder to locate them and navigate to them in Object Explorer

Tables and views are worse. Let's say I have a large table that's been called tblClientTransactions, then, due to volume or new development it becomes necessary to split the table into 2. The standard trick would be to create a view with instead of triggers with the name of the table to present an unchanged interface to any queries that need to see the table as it was (especially if the queries are embedded in the client app). Now I have a view named tblClientTransactions. Hmmmm....

The only thing that I use prefixes on are indexes, primary keys and unique constraints. Why? Well, two reasons. They never appear in code (or at least shouldn't) and so the more complex name is not a burden anywhere. So I can see, when looking at a query's execution plan whether the index used is just an index or if it's one of the constraints as the only thing that the exec plan tells me is whether or not the index is clustered


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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass


bill.sugden
bill.sugden
SSC-Enthusiastic
SSC-Enthusiastic (113 reputation)SSC-Enthusiastic (113 reputation)SSC-Enthusiastic (113 reputation)SSC-Enthusiastic (113 reputation)SSC-Enthusiastic (113 reputation)SSC-Enthusiastic (113 reputation)SSC-Enthusiastic (113 reputation)SSC-Enthusiastic (113 reputation)

Group: General Forum Members
Points: 113 Visits: 144
Hi all.

I'm one of those developers that write front ends to databases, and usually end up doing the database design as well.

I used to code in VB and used Hungarian for that exclusively. BUT with my database design hat on I NEVER used hungarian for column names or anything like that.
The only prefix I allowed myself was for logical grouping of stored procs (a la customer, address examples above) and why would I need to include 'sp', it should be obvious that I'm calling a stored proc from the program context. You do separate your data access layer don't you?

I started back in the day of btreive databases with maximum 8 character column names - yuck. The freedom from this restriction with first ACCESS and then SQLServer was wonderful. I tend to name my columns for what they logically contain. A bit column may be something like 'isAccountHolder' in a customer table, a money column might be 'maxCredit' in the customer account table.

Now we can start the argument about camel case etc. but it suits me as I now code mostly in C# and this makes for easier matching to objects - but heck you can use ToUpper() in code if you want to.

Bill
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search