THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - The SQL Server blog spot on the web Sign in | |
in Search

Peter DeBetta's SQL Programming Blog

Peter DeBetta's blog about programming in SQL Server using technologies such as T-SQL, .NET, CLR, C#, VB, Visual Studio, and SQL Server Management Studio.

Really, what are your SQL Server "Worst Practices"

Richard Hundhausen already posted about this topic on his blog Tales from the Doghouse, but I thought it be wise to post as well, if only to get more feedback.

Rich and I will be presenting at SQL Connections about SQL Server "worst practices", while of course, highlighting "preferred practices".

So, as Rich asked, "what are some of your 'worst practices' that you'd like to share?"

Published Tuesday, August 01, 2006 1:44 PM by Peter W. DeBetta

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS

Comments

 

k_nitin_r said:

I don't know about you, but the first time I had to demo a point-in-time recovery, I forgot to take a Transaction Log backup. It's good to know that it wasn't a live system so nothing important was lost.

Next time, I'll make sure I have a procedure manual and checklist prepared before any kind of DBA activity, especially when performing a point-in-time recovery.
August 3, 2006 3:17 AM
 

Michael Pfälzer said:

Not clearly defined who is responsible for the database administration.
To many admins on the box (BUILTIN\Administrators).

Example: In the past a coworker (not a DBA) changes settings of transaction logs on
the production server and caused a major outage.

Because the DBA in charge are on vacation (;-)
August 4, 2006 1:16 AM
 

The Kid said:

Everyone who accesses the Database uses the SA account without a password!
August 7, 2006 8:14 AM
 

Rudyx said:

#1 --> Not having a DBA on staff.
August 7, 2006 1:47 PM
 

Chris Compton said:

Hello from Raleigh,

Wow, the day I decide to look you up, there's a post that I can comment on :-)

The worst practice I've encountered in my current job is using stored procedures to build the html to present to the user.  Note that the app uses ASP to get it out of the db and display it... oh, and did I mention that the only thing it builds is shoving the <tr> and <td> tags around the data and the REST of the html is built in ASP?
Oh yeah, and as the html could be very large (but never is >2000) he writes it to a CLOB in another table and THEN it goes back to ASP.

In case you're talking more admin type stuff:
the worst I've seen is linking every SQL Server to every other SQL Server as a linked server, and giving everyone the sa password so they could do 'whatever'.  The pw wasn't blank, but it was only 5 characters and wasn't changed in the 18 months I was there (even though two people [at least] were fired and I do mean the "walked to the door" kind of fired).

Hope you and yours are doing well  :-)
August 8, 2006 11:15 AM
 

Stuart said:

This is one of my favorites:

Boss: "We need to Restore last night's Backup of the Quarterly Reports Database. One of the Developers hosed it up this morning."

DBA: "No problem, boss. I'll just get it from last night's tape!"

[ Whistling. Then panic. Anger. Denial. Acceptance ]

DBA: "I don't understand it but I can't get anything off that tape. Or any others in the last month either."

Boss: "You tested it before, right?"

[ Crickets chirping ]

I LOVE that bit!
August 8, 2006 4:03 PM
 

k_nitin_r said:

Oops.... I just discovered typos in my last post and so if the moderator could remove this line and my post from about a minute ago, it would be appreciated.

Just today, someone from the Network computing team changed the hostname on my SQL Server and I spent a while trying to figure out why the clients couldn't connect.

I spent about half an hour, looking up the settings in Configuration Manager and even did a restart (much like we do with Windows) but it still didn't work. I then decided to check the hostname after staring at various elements of the login dialog box when I finally got to the root of the problem.

It's more of a bad practice on part of the network computing guy who came by at night and switched names without documentation or notification of the change.
August 8, 2006 11:55 PM
 

Peter W. DeBetta said:

I removed the entire previous comment with the typo since you had a correct version in the comment that immediately followed.

--Peter
August 9, 2006 8:37 AM
 

RickHeiges said:

Worst Practice:  Taking a physical disk (or array) and carving logical disks out of it and placing data AND logs on the "different" logical drives.  Why bother?  I ihave seen this more times than I want to admit.
August 10, 2006 5:39 AM
 

Martin Mickleburgh said:

The last place I worked, about a year before I arrived they had decided it was time to drop the quill pens and velum and invest in a database.  They hired a guy to do it.  To save time, he took every file in their stone-age file based system and converted into a table, directly, no re-design, nothing.....I didn't know whether to laugh or cry when I realised what I had to support.
August 21, 2006 1:40 AM
 

Tomas said:

The worst DB practice is table column naming with local (non-english) characters with spaces. I was repairing this due last four weeks...
August 21, 2006 3:10 AM
 

Dave said:

I am in the process of migrating databases from 2000 to 2005 and so far have identified the following examples of worst practices. Most happened because of no dedicated DBA.

Listed in no particular order.

1. No use of stored procedures.
2. Using "id" or other reserved keywords as column names.
3. Using generic xxx_id cloumn names as the primary key, just because every single programming book does it, instead of using using a natural key or compound key.
4. Duplicate data because of no primary key or use of generic id key.
5. Using spaces \, *, ? or any punctuation other then . or _.
6. No use of FK. or Lookup tables for dropdown menus
7. Using id keys with default identity seeding vs user created function that wold have somehting meaningful like year-object_type-unique_id
8. Using the database upsize wizard to move Access databases into SQL Server and not changing the default columns of text for memo and nvarchar for everything else.
9. Creating databases from Excel spreadsheet by making every sheet a table, ignoring all the duplicate data in each table. Also known as not normalizing.
10. Adding default values to tables as constraints
11. Allowing bit columns to be NULL when there is no need. Sometimes there is a need like surveys or when adding it as a column to an existing table other times it is simple ignorance.
12. No use of indexes.
13. Calling user created stored procedures sp_
14. Not creating roles for consolidation and administration of permissions.
15. Allowing passwords to be blank
16. Breaking the row size limit not because of need but because you didn't know any better.
17. Using text datatypes instead of correctly sized varchars
18. No DBA
19. Test/ Development and production environments are in the same database instance. So no separation of development from production.
20. Not refactoring user requested changes, and hoping things do not break.
21. Not having a change control process see item #20.

August 21, 2006 3:09 PM
 

Dave said:

Additions to worst practices:

22. Not having troubleshooting login's for the application that uses the database. This makes it hard to duplicate problems.
23. Not knowing which group of people use which database. You should know who is going to scream at you if things go bad.
24. No risk management plan. This can include tested disaster recovery plans, colocation, and balancing the risk of a machine going down with the applications that run on them. For example you should not have all your revenue making applications sitting on the same machine, even if it is clustered because the loss in revenue to the company if they go down is too great. Also known as not keeping all you eggs in one basket.
25. Not auditing sensitive in-house data that is not covered by regulations.
August 21, 2006 3:20 PM
 

Dis4ea said:

As explained on the original article.

- Guideline for a data and log file of this DB: everything MUST reside on the D drive
- Why use exotic datatypes when you can place everything in CHAR fields?
eg. Use CHAR(105) when 99% of the data is less than 50 characters, CHAR(10) for a 10 digit number
- If you can use MONTH(FieldName) = 9 don't bother rewriting this as a BETWEEN that uses an index
- Selecting fields is a real pain, use SELECT * instead
- Prefix all LIKE queries with %

I'm suprised they came to me with performance problems :-)

August 22, 2006 11:24 PM
 

Peter W. DeBetta said:

Hmmm, I beg to differ on #3...

-Using generic xxx_id cloumn names as the primary key, just because every single programming book does it, instead of using using a natural key or compound key.

I am in the arbitrary key camp and in general avoid natural keys. The key field for a table is for finding the specific row and shouldn't be a characteristic of the data itself. Natural keys are prone to problems (potential duplicates, security issues, etc.) that an arbitrary keys don't have. Keep in mind that there are exceptions to this rule, but in general, I use arbitrary keys.
August 23, 2006 8:58 AM
 

Kevin3NF said:

Upgrading an Access application's tables to SQL Server, forgetting to move the indexes, and then never creating them on the SQL side.

Solution: Pay me to fix your performance issues on a database I have to charge you to learn about. :)
October 5, 2006 7:09 AM

Leave a Comment

(required) 
(required) 
Submit

About Peter W. DeBetta

Peter DeBetta works for Microsoft as an Application Platform Technology Strategist, specializing in strategies, design, implementation, and deployment of Microsoft SQL Server and .NET solutions. Peter writes courseware, articles, and books – most recently the title Introducing SQL Server 2008 from Microsoft Press. Peter speaks at conferences around the world, including TechEd, SQL PASS Community Summit, DevTeach, SQL Connections, DevWeek, and VSLive!

When Peter isn’t working, you can find him singing and playing guitar (click here to hear an original song by Peter), taking pictures, or simply enjoying life with his wife, son, and daughter.
Powered by Community Server (Commercial Edition), by Telligent Systems
  Privacy Statement