SQL Clone
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
bill.sugden
bill.sugden
SSC-Enthusiastic
SSC-Enthusiastic (145 reputation)SSC-Enthusiastic (145 reputation)SSC-Enthusiastic (145 reputation)SSC-Enthusiastic (145 reputation)SSC-Enthusiastic (145 reputation)SSC-Enthusiastic (145 reputation)SSC-Enthusiastic (145 reputation)SSC-Enthusiastic (145 reputation)

Group: General Forum Members
Points: 145 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.
Open Minded
Open Minded
SSC-Addicted
SSC-Addicted (494 reputation)SSC-Addicted (494 reputation)SSC-Addicted (494 reputation)SSC-Addicted (494 reputation)SSC-Addicted (494 reputation)SSC-Addicted (494 reputation)SSC-Addicted (494 reputation)SSC-Addicted (494 reputation)

Group: General Forum Members
Points: 494 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.
James Stover
James Stover
Right there with Babe
Right there with Babe (745 reputation)Right there with Babe (745 reputation)Right there with Babe (745 reputation)Right there with Babe (745 reputation)Right there with Babe (745 reputation)Right there with Babe (745 reputation)Right there with Babe (745 reputation)Right there with Babe (745 reputation)

Group: General Forum Members
Points: 745 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

Fozzie
Fozzie
Right there with Babe
Right there with Babe (725 reputation)Right there with Babe (725 reputation)Right there with Babe (725 reputation)Right there with Babe (725 reputation)Right there with Babe (725 reputation)Right there with Babe (725 reputation)Right there with Babe (725 reputation)Right there with Babe (725 reputation)

Group: General Forum Members
Points: 725 Visits: 1172
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.
Open Minded
Open Minded
SSC-Addicted
SSC-Addicted (494 reputation)SSC-Addicted (494 reputation)SSC-Addicted (494 reputation)SSC-Addicted (494 reputation)SSC-Addicted (494 reputation)SSC-Addicted (494 reputation)SSC-Addicted (494 reputation)SSC-Addicted (494 reputation)

Group: General Forum Members
Points: 494 Visits: 567
the content inside a can, cannot know the content outside the world. :-D

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.
bill.sugden
bill.sugden
SSC-Enthusiastic
SSC-Enthusiastic (145 reputation)SSC-Enthusiastic (145 reputation)SSC-Enthusiastic (145 reputation)SSC-Enthusiastic (145 reputation)SSC-Enthusiastic (145 reputation)SSC-Enthusiastic (145 reputation)SSC-Enthusiastic (145 reputation)SSC-Enthusiastic (145 reputation)

Group: General Forum Members
Points: 145 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
Andeavour
Andeavour
Ten Centuries
Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)

Group: General Forum Members
Points: 1312 Visits: 932
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..."



richardd
richardd
Hall of Fame
Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)

Group: General Forum Members
Points: 3242 Visits: 648
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!



GilaMonster
GilaMonster
SSC Guru
SSC Guru (227K reputation)SSC Guru (227K reputation)SSC Guru (227K reputation)SSC Guru (227K reputation)SSC Guru (227K reputation)SSC Guru (227K reputation)SSC Guru (227K reputation)SSC Guru (227K reputation)

Group: General Forum Members
Points: 227233 Visits: 46335
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>Wink. 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, 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


Grant Fritchey
Grant Fritchey
SSC Guru
SSC Guru (99K reputation)SSC Guru (99K reputation)SSC Guru (99K reputation)SSC Guru (99K reputation)SSC Guru (99K reputation)SSC Guru (99K reputation)SSC Guru (99K reputation)SSC Guru (99K reputation)

Group: General Forum Members
Points: 99207 Visits: 33014
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
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