January 25, 2010 at 5:14 am
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
January 25, 2010 at 6:01 am
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..."
January 25, 2010 at 6:06 am
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!
January 25, 2010 at 6:13 am
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, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
January 25, 2010 at 6:31 am
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
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
January 25, 2010 at 6:42 am
OK, but even if one accepts that constraints or index need some sort of indication of their type from their name, why not just put, for example, CustomerUniqueIndex or Customer_Unique_Index rather than ixuCustomer (or whatever the correct tibble is) After all, how many times do you ever have to type the names of your constraints or indexes in code? Grant is right; it is just habit, we've drifted into this tibbling thing.
Best wishes,
Phil Factor
January 25, 2010 at 7:02 am
Fozzie (1/25/2010)
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.
Agree 100%
Although I'm currently working on a database where all (yes, all!) the sps are prefixed with sp_
Moral dilemma - whilst I can't bring myself to follow this naming convention, it's a very low-use, low transaction db so do I shut my eyes and name them sp_ to keep things consistent, or do the right thing and name them differently? Other apps use the sps so I can't rename those, however I could easily name mine correctly. Or incorrectly, depending on your perspective :unsure:
January 25, 2010 at 7:02 am
edit
January 25, 2010 at 7:14 am
I disagree on the use of Hungarian Notation as well. It is way too handy in debugging applications, reporting, etc to not use it just because some people go too far and use it on columns.
January 25, 2010 at 7:26 am
While this doesn't invalidate any points made about archaic prefixing practices in people use in DBs, maybe you would be interested in this tidbit on Hungarian Notation...
http://www.joelonsoftware.com/articles/Wrong.html
If you skip down to "Iām Hungary" it talks about a great confusion with the concept.
January 25, 2010 at 7:29 am
Phil Factor (1/25/2010)
OK, but even if one accepts that constraints or index need some sort of indication of their type from their name, why not just put, for example, CustomerUniqueIndex or Customer_Unique_Index rather than ixuCustomer (or whatever the correct tibble is) After all, how many times do you ever have to type the names of your constraints or indexes in code? Grant is right; it is just habit, we've drifted into this tibbling thing.
I think you're really on to something with this. I can see a few places for exceptions, but by & large, I think this is actually a great idea for changing some of our habits, making our database structures more clear and eliminating things like tblTableName, which has ALWAYS made me crazy.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
January 25, 2010 at 7:39 am
January 25, 2010 at 7:45 am
GilaMonster (1/25/2010)
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.
I meant in terms of say that happening 10 times in a large database. If a new dev comes along he can see views, he can see tables. His task is to do an import and an export. He's not been told that some views have nested triggers on, some don't. He's an new dev, maybe a years experience, been dropped in the deep end, so automatically thinks tables. Does an import and export from the table. He finds no failures, no issues, no worries.
The table has to be split again, it's grown rapidly out of expectations. HorseyBank Mortgage Company bought out HouseAFax Mortgage Company as well as SandAnders Mortgage Company... and their all now using the same app and database.
We could change our first view.. but that doesn't help the import and export process that has been written, so we'll put in another view / trigger.
We now have ClientTransactions, ClientTransactionsA, ClientTransactionsB, ClientTransactionsA1, ClientTransactionsA2, ClientTransactionsB1, ClientTransactionsB2....
So now from the app, we hit a view, that hits a view, that hits a table... the import hits a view and table.
Ok this is an extreme case that probably wouldn't happen, but with smoke and mirrors things can get complicated in the wrong environments.
I still think it's a clever way of doing things. Could be used as either a temporary fix or permanent in the right situations. If the app is no longer being developed, or not being released, prefec. If there are releases going on, then we can notify the app team of the measures taken, and they can deal with it in the next release.
Not quite the same. I'm dealing with a client's db where every proc is prefixed usp_ (there are several hundred).
Ah yes can see your point, too many characters to play with. Two characters one can cope with, three isn't too bad, but four plus is plain tedious.
Daft (1/25/2010)
Although I'm currently working on a database where all (yes, all!) the sps are prefixed with sp_
Anyone who names a proc "sp_" should be shot... I know I said about playing fairly in others sandpits, but doesn't mean that you can't throw a paddy while you're in there š
January 25, 2010 at 7:47 am
I'm a little late, but I'll add my $0.02 in there.
For the most part I don't think tables, stored procedures, and certainly not columns, need a prefix. The code itself lets you know what you're dealing with based on structure. As Gail mentioned, you can just tell.
The idea with the prefixes is to convey information to the coder. If you don't realize that a FROM clause requires tables, or a USE clause requires a database, that's another issue. You want there to be value from the notation. I can see where having the datatype for columns conveys that, but I don't think we have type mismatches enough to worry about this
However views have been a place where I have typically prefixed so that I'm aware that I am potentially accessing multiple tables underneath. That often means I might want to look here if I cam having performance issues.
Functions are a place where I think I might consider this. Now that we have scalar and table functions, and it might be worth letting a coder know that a particular type of function is being used in code. I haven't done enough work to know if there is value here, but it is something to consider.
I do like a prefix or postfix in indexes for one reason. I want to easily see if an index is a CI or NCI. It's quicker than going through properties.
January 25, 2010 at 7:50 am
The voice of sweet reason Steve!
Bill
Viewing 15 posts - 16 through 30 (of 77 total)
You must be logged in to reply to this topic. Login to reply