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 12345»»»

We'll go no more a' tibbling Expand / Collapse
Author
Message
Posted Saturday, January 23, 2010 12:23 PM


Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Today @ 3:30 AM
Points: 587, Visits: 2,533
Comments posted to this topic are about the item We'll go no more a' tibbling


Best wishes,

Phil Factor
Simple Talk
Post #852584
Posted Sunday, January 24, 2010 11:15 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, April 10, 2012 9:33 PM
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.
Post #852836
Posted Monday, January 25, 2010 1:17 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Friday, February 25, 2011 3:18 AM
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.
Post #852871
Posted Monday, January 25, 2010 1:26 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, October 5, 2012 1:41 AM
Points: 155, Visits: 146
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.
Post #852872
Posted Monday, January 25, 2010 1:32 AM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Today @ 1:37 AM
Points: 554, Visits: 1,179
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 :-(
Post #852873
Posted Monday, January 25, 2010 1:46 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, October 5, 2012 1:41 AM
Points: 155, Visits: 146
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...
Post #852878
Posted Monday, January 25, 2010 2:08 AM


Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Today @ 3:30 AM
Points: 587, Visits: 2,533

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
Post #852879
Posted Monday, January 25, 2010 2:48 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Monday, December 9, 2013 3:40 AM
Points: 58, 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

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"? )
Post #852886
Posted Monday, January 25, 2010 3:30 AM


SSC-Dedicated

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

Group: General Forum Members
Last Login: Today @ 2:25 PM
Points: 39,910, Visits: 36,250
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 2008, MVP
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

Post #852902
Posted Monday, January 25, 2010 3:34 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, July 13, 2011 4:11 AM
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
Post #852904
« Prev Topic | Next Topic »

Add to briefcase 12345»»»

Permissions Expand / Collapse