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 Monday, January 25, 2010 3:49 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
Gail

I agree with you wholeheartedly. The only concession I would make would be UPPER CASE for table names - but that is more a matter of style and preference rather than vital for understanding the context or helping with coding.
Post #852907
Posted Monday, January 25, 2010 4:28 AM
SSC-Enthusiastic

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

Group: General Forum Members
Last Login: Monday, August 18, 2014 4:04 AM
Points: 176, Visits: 567
in databases where i have to make a front-end vb.net application, i find it easier to tibble the column names. but maybe this was because i was under the dreaded visual studio.net 2002-03. i seem to remember having to correctly designate the vb type for the type of sql data it was supposed to retrieve.

i am not a religious fanatic for one type of language or programming style, i often cull the best of whatever there is; or for my multi-role case, whatever keeps me from tripping over every 2-3 years or so.
Post #852917
Posted Monday, January 25, 2010 4:33 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Sunday, September 8, 2013 5:39 PM
Points: 263, Visits: 862
I would rather a table named tblCustomer than one named A72GZF (or something equally nonsensical). Quite the opposite, I tend to be somewhat verbose when naming objects. Sure, there's a little more typing but think of all the saved trips to the data dictionary to find out exactly what table A72GZF is for. Having said that, I'm not fond of column names prefixed with the datatype. That's just lazy.


James Stover, McDBA
Post #852921
Posted Monday, January 25, 2010 4:41 AM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Wednesday, December 17, 2014 4:07 AM
Points: 365, Visits: 966
Ok there's no way I am prefixing database names with "db", no way I'd prefix column names. But views, functions and procs yes. Tables, well I don't think there's a real need... but if the project requires it, then hey ho. The biggest issues arrise where one or more standards are used within the same database, which does look crud.

GilaMonster (1/25/2010)All I find that prefixing stored procedures does is make it harder to locate them and navigate to them in Object Explorer


If they've all been prefixed with the same "tibble" then the order would be the same with as without. It's only if mixed tibbles are being used where you get issues.

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....


Now that's a clever trick I hadn't though about... but then the dev's would now also need to know which are views with triggers on.... Hmm not something easily seen at a glance of a name.

Personally I do like to know the difference between a view and a table. If it has a table has to be split and a view placed above then the app code should be changed unless the app code uses stored procedures. I don't like app's doing table level stuff... but unfortunatley where I work, this is common place.

At the end of the day each to their own, as long as they follow the design laid out when working in someone else's sand pit.


Post #852924
Posted Monday, January 25, 2010 4:44 AM
SSC-Enthusiastic

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

Group: General Forum Members
Last Login: Monday, August 18, 2014 4:04 AM
Points: 176, Visits: 567
the content inside a can, cannot know the content outside the world.

intEmployeeID vs. EmployeeID, nvarcharCustomerCode vs. CustomerCode ?

mine is based upon a variety of experience, but i do not expect all experiences to be the same, in which case, passing judgment at once is cause for comment.
Post #852925
Posted Monday, January 25, 2010 5:14 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
James Stover (1/25/2010)
I would rather a table named tblCustomer than one named A72GZF (or something equally nonsensical).


So you've worked with Financial systems with 'Dynamic Table Creation' as well? Didn't anyone tell these developers about EAV? But then I've also seen the other extreme with almost one table of 'thing'.


Bill
Post #852930
Posted Monday, January 25, 2010 6: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: Wednesday, November 19, 2014 7:42 AM
Points: 999, Visits: 884
dhamilton-905368 (1/25/2010)


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....


I was always under the impression that this is BAD practice for user written stored procedures, as SQL automatically assumes any proc name starting with "sp_" is a system proc and searches the master database for it first, only going to the user database if it can't find it. This can lead to a loss of performance, or even to the wrong proc being run if your name happens to match an existing system proc.

I've got to be honest, a part of me agrees with Phil here, and I find my teeth on edge when I open a new database at a client's site and see every table prefixed with "tbl" or "tbl_" (or in really bad cases a mixture of both). Over the years as a DBA whenever I've designed a database I've always tried to give tables meaningful names, but assumed that a fellow DBA could tell it was a table without me having to "tibble" it.
And when I find all the column names "tibbled" as well, then it makes me want to flail about with a blunt instrument in a way that would have my neighbours on the evening news saying things like "He seemed such a nice man, always kept himself to himself..." and "He was always so polite and quiet..."




Post #852941
Posted Monday, January 25, 2010 6:06 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Friday, December 12, 2014 10:30 AM
Points: 2,391, Visits: 578
I have to admit that I've been guilty of the occasional "tibble" in my time, but I've usually managed to avoid prefixing tables or databases. Of course, you should always avoid using "sp_" as a prefix for stored procedures.

By far the worst example of tibbling I've seen is a database developed by a contractor. I think he designed it by reading an "MS Access 1.0 for Dummies" book. Every table is named "TBL_CMPYNM_SOMETHING", every view is named "VW_CMPYNM_SOMETHING", and every stored procedure is named "sp_cmpynm_something" (where "CMPYNM" is the company name with the vowels removed).

Most tables have a primary key called "ID", although in some cases it's "UniqueID", some have a "UniqueID" and a separate primary key, and some have no primary key at all.

He had some concept of data normalization, but no idea when to stop - hey, you never know when they'll change the number of days in a week, right??!

Of course, once we paid him for the initial work, he ran off in the direction of the bank, laughing maniacally, leaving me to maintain this monstrosity!



Post #852946
Posted Monday, January 25, 2010 6:13 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Yesterday @ 1:14 PM
Points: 40,615, Visits: 37,081
Fozzie (1/25/2010)
GilaMonster (1/25/2010)All I find that prefixing stored procedures does is make it harder to locate them and navigate to them in Object Explorer


If they've all been prefixed with the same "tibble" then the order would be the same with as without. It's only if mixed tibbles are being used where you get issues.


Not quite the same. I'm dealing with a client's db where every proc is prefixed usp_ (there are several hundred). If I use the Object Explorer ability to type the start of the name to navigate to it, that's minimum 9 characters that I have to type to even begin to navigate (dbo.usp_<next letter>). If I type too slowly, then object explorer forgets the characters that went before and starts searching again from the next character.

It's definitely slightly harder, when scanning down hundreds of stored proc names to ignore the meaningless prefix and look for the useful part of the name

Now that's a clever trick I hadn't though about... but then the dev's would now also need to know which are views with triggers on.... Hmm not something easily seen at a glance of a name.


Why? The whole reason for doing that is so that you can change a table design and no stored proc or app code has to change. A select on the view returns exactly what a select of the table used to, and the instead of triggers on the view ensure that inserts, updates and deletes behave exactly as they used to.

New code then targets the tables, not the view. Consider the view as a compatibility view (like sysobjects)

If it has a table has to be split and a view placed above then the app code should be changed unless the app code uses stored procedures.


In that case, you probably wouldn't bother with the view. Change the table design and fix all the code that breaks because of that.



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 #852951
Posted Monday, January 25, 2010 6:31 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: 2 days ago @ 8:22 AM
Points: 14,205, Visits: 28,534
I think a lot of this, for me anyway, is just habit. Is there a good reason any more to put idx or ix at the beginning of index names? Nope. But I keep doing it because... I'm creating an index and as I type it, I put ix at the beginning.

One are where I think it's possibly usefule, but not carved in stone, is as a means to differentiate betwee views that are meant to be known as views and views that are meant to be known as tables. That's the one place I can think of that this makes a substantial difference. Other than that, naming a proc sprGetMyData or GetMyData... neither one makes a difference in terms of actual programming or performance. It just becomes a matter of convenience to break the update, spu, from the reads, spr, from the deletes, spd, from the inserts, spi or spc, but it really doesn't matter a whit.


----------------------------------------------------
"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 Query Performance Tuning
and
SQL Server Execution Plans

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

Add to briefcase ««12345»»»

Permissions Expand / Collapse