October 24, 2008 at 4:11 am
ooh! very good one!
October 24, 2008 at 4:30 am
Why:
From the POV of a developer who has a significant background in databases, for one. And for two, a consultant who finds himself working on many different databases of all sizes at different times and frequencies.
What:
Naming conventions! I don't even care (too much) which conventions are used, but the consistency is a must. I need to be able to go into a database that maybe I haven't seen for over year, that has maybe a hundred or two tables and 500 or more sprocs and be able to find what I'm looking for quickly.
October 24, 2008 at 4:31 am
Malcolm Leach (10/24/2008)
The only absolute should be that there are no absolutes.... 😉There should only be guidelines, validation, discussion and feedback.
I think you beat me to it there - I was going to say that for me, backups are never an absolute; they are one of the most variable things there is. Every situation has a different budget, existing systems and procedures, different priorities (some data simply is not worth backing up), different growth expectations ...
October 24, 2008 at 4:39 am
Backups - definitely
Primary indexes - ideally
Client apps that require sysadmin access (there are still many out there and it is just so lazy). I hate them and in an ideal world would banish every single one of them - defintiely
Consistant naming strategies for database objects - ideally
But as noted by another post above: absolutes are themselves a trap, as a DBA you're in a partnership with the business and it is the nature of partnerships that you've got to compromise.
October 24, 2008 at 4:53 am
I do not really have any absolutions. I judge from situation to situation. But there are a few things I try to reach... Never ever use cursors, because if you do it right there is always a better way. Do not allow null values, if you need null you have a design flaw, however, not allowing null in some cases would bring in a way to high complexity in several situations.
One more important thing, always debate the old grumpy one's that think they know best but haven't read the latest articles and who has not tested another approach then the one's they always use.
October 24, 2008 at 5:26 am
I'm a stickler for naming conventions on database objects. I am on a crusade to remove the information technology world of acronyms, and meaningless abbreviations - especially in the names of database objects.
Spell it out man!
Like you spell DBA, or SQL 😉
October 24, 2008 at 5:53 am
Absolutely always always always start ad hoc table updates with BEGIN TRAN
October 24, 2008 at 6:06 am
Backups, absolutely.
And, this one does make people crazy, but I've been insisting on it for over a decade now... a tested script to deploy changes to production. No winging it, no developing against production. You put me in charge of protecting the corporate data and I'm going to do it gol-darnit.
Over the years experience, and blood, has worn away insistence on primary keys, clustered indexes,naming conventions... but I'm not giving up backups or a scripted production rollout!
----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software
October 24, 2008 at 6:11 am
"I'm a stickler for naming conventions on database objects. I am on a crusade to remove the information technology world of acronyms, and meaningless abbreviations - especially in the names of database objects.
Spell it out man!
Like you spell DBA, or SQL 😉"
word....
A clear concise naming convention can make things so much easier when several people are involved in the evolution of a system.
October 24, 2008 at 6:20 am
I've always worked in small shops, but I always insist on separate dev and production systems. Since I and my co-workers have fill all the roles from design to production support I've never had the ability to get QA systems.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
October 24, 2008 at 6:21 am
I am also a big fan of naming conventions. Unfortunately, my group currently has none. 🙁
A personal pet peeve is adding "sp" to the beginning of a stored procedure name (or even worse "sp_" - I hate typing underscores!) Imagine my surprise when I recently found some new stored procedures that ENDED with "sp". What's the point of that?
October 24, 2008 at 6:25 am
'sp_' is a 'no no' as the request will go through the master db first looking for the proc.
If you have a proc named 'sp_x' in your master db and one in your app database with the same name;
the app one will never get executed.
bad!bad!bad!
Hiding under a desk from SSIS Implemenation Work :crazy:
October 24, 2008 at 6:31 am
Backups are key, but just before the backups its the integrity check (DBCC CHECKDB). I have spent way too many hours trying to fix corrupt databases for clients that either did not understand the importance of the integrity check, or didn't know about it at all.
An important lesson I have learned is allocation and consistency errors are nearly always hardware beginning to malfunction. Every instance of corruption I have dealt with the source of the problem was bad hardware, and the integrity check was the canary in the mine.
October 24, 2008 at 6:37 am
I think my only 100% absolute is no sp_ in front of stored procedure names. As I think Grant said above, the sweat, blood and tears have worn away all my other resistances. Sure I want backups, and will have them, but there are tens of reasons for why a backup is done a certain way (full weekly + differential daily + transaction logs are not always feasible).
Problem I have where I am now, is that a consultant (or a misguided employee none wants to name) that was here before me insisted on every stored procedure beginning with sp_!!!!!! Slowly changing them over though.
October 24, 2008 at 6:41 am
i have a few absolutes... especially as i am not invovled in the development phase I inherit whatever is done well... or not so well.
#1: Test to the max scale you expect +1. Far to often i am told 'it is just a small database' or 'it barely gets used. One great experience with this was a database that stored the results of image quality tests for a check capture system. There was a definite bottleneck at the database and the developer said 'no way. Queries are ALL completing in 250ms or less.' I had no choice but to agree with her and then add an index to get the queries to complete in 20ms or less. She had no concept of what the 230ms savings meant until i physically layed the math in front of her. For a dozen items... no big deal... for 100,000 items per day... that really adds up. My favorite response afterwards was 'I don't know how to do SQL i just made it work'. Never just make it work... You may feel it will never get used... but everyone uses things in ways they were never intended. Screwdriver anyone?
#2: document. Make a high level table layout document. Don't tell me the tables are to dynamic and that you can't document that. You know what your app is capable of and your naming conventions... give me something. keywordtablexxx, configurationtablexxx. Anything. And don't tell me that the training department will do the documentation. They don't know what your intentions were.
Viewing 15 posts - 16 through 30 (of 92 total)
You must be logged in to reply to this topic. Login to reply