Ten Worst Things to do In a Database – Day 4

New Show (and new show format!)
SelecTViews – JQuery, Couchbase, Watson and Windows Phone Developer Tools…Also, the IMnsHO for Databases, SSWUG newsletter and events update and much more in our new show format!
[Watch the Show] (Let us know what you think of the new format…)

Ten Worst Things to do In a Database – Day 4
The list of bad things to do is complete. In fact, the list grew so big, I had to break it out into three surveys.

• TOP (10) Bad Development Things to Do In a Database
• TOP (10) Bad Production Things to Do In a Database
• TOP (10) Bad Warehouse/SharePoint Things to Do

The staff at SSWUG is actually putting together a survey you will be able to access without having to add Friends on Facebook or anything else. It will be available this afternoon. Sam will probably send out a Tweet when the survey is posted.

Voting begins today. You may choose any three out of the list…but only three.

You can vote by going to the SSWUG survey when it posts today (watch tweets from @sswugorg or #SSWUG), or you may send your top three picks to me in an Email at btaylor@sswug.org. I am including the list of Bad Database Things to Do in this editorial should you choose to vote by Email.

We’ll have the results posted in the Tuesday editorial next week.

Here is the big list of Bad Development Things to Do in a Database…

1. Not Follow DB Design Methodology
   Ex. Logical -> Functional -> Conceptual (Normalized) Designs in that order followed by Data Analysis and Business Requirements
2. Use your business objects and Client Code to define the schema of your base data
   • Save JSON objects as binary data in a table where that data could be mined
   • Store data in XML data type because it is jagged or you don’t want to take the time to understand it
3. De-normalize for ease of consumption by business layer
   Ex. Phone, Cell, Fax, Help Desk all in separate phone columns instead of separate records in a phone table
4. Embed non-structured data with structured data in the same table and/or database
   ex. BLOB (Binary Large Object) data included in same table as normalized data
5. Tables with Relationships where a row may be related to many different tables based on the value of a different column. Ex. Table1 relates to Table2 or Table3, both using Table1.Column1 value based on the value of Table1.Column2.
6. Using wrong size data types ex. Always use BIGINT when smaller type will do or Use of GUID when INT may be better
7. Over-Normalization for theoretical purposes only
8. Not Enforce Foreign Key Relationships
9. Not Document a Database Schema
   • Tables
   • Columns
   • Relationships
   • Not Keep Table/View Schema Current
   • Include place holder columns for future data not yet populated
   • documentation for foreign language
10. Not have and implement a data retention policy
11. Compensate for a bad Schema Design
    • Use Indexed Views to compensate for a bad schema
    • Use Triggers to compensate
    • Use Only Surrogate Keys When Real World unique keys are available
    • Use Replication when tables are separated into different databases inappropriately
    • Use User Defined Functions to compensate for bad schema
    • Create Many Covering Indexes on a Single Table
12. Write stored procedures in a database that only accesses data in another database
13. Data Corruption
    1. Retain deprecated tables or columns after all accessing code is gone
    2. No Check Constraints
    3. Inappropriate Use of Nulls
14. Not create and maintain indexes for Primary Keys, Foreign Keys or based on Data access patterns
15. Not create and maintain appropriate statistics
16. Create index to fix query rather than tuning the query
17. Not use Appropriate Clustered and Non-Clustered indexes
18. Have global indexes on Partitioned Tables
19. Not Optimize Queries
    • (oracle) not Wrapping the package specs
    • (oracle) not using Bind Variables
    • Not reviewing query plans
20. Embedded SQL in Application Without Parameterization (Possible SQL Injection Target)
21. Not using Packages (Oracle) or Procedures (Other Supporting DBs)
22. Inappropriate security – Over Simplify Application User Authentication
    • Use SA or Administrative Account
    • Make All Logins Database Owner, or Administrator role
23. Inappropriate security – Not Utilize Schema’s
    ex. Simply use DBO schema instead of separating roles
24. Inappropriate security – Not Separating Data Access
    • Not Associate Authentication and User (if shared authentication) with appropriate roles and schema’s
    • Not Restrict Columns for User
    • Not Restrict Rows for User
25. Not Test Database Code (Triggers, Functions, and Stored Procedures)

So, there’s your list of 25 bad practices to choose from. Be sure to send in a vote with the Three you hate the most!

Thank you to all participants sending in great ideas for things we really should avoid or handle with care. Everything has been consolidated to try and keep the list as short as possible…but nothing was lost or forgotten.

As always, feel free to contact me through facebook, twitter or btaylor@sswug.org. You can become a friend of SSWUG at the SSWUG Facebook Wall as well. Lots of great interaction happening there. [Check It Out]

Cheers,

Ben

$$SWYNK$$

Featured Article(s)
What Is Going On? (Part 1 of 2)
Have you ever wondered what is happening on your server? Did the backup run? Did it fail? Why is Outlook installed on my SQL Server? I don’t like MAPI. Get it off my SQL Server! SMTP who? Can I use SMTP to send my mail? Let’s use this new feature called Database Mail. But wait, what is this profile? I did not get my email. How can I tell if my email has been sent? When and where should I use email? Come learn about Database Mail.

Featured White Paper(s)
Upgrading to Microsoft SQL Server 2008 R2 from Microsoft SQL Server 2008, SQL Server 2005, and SQL Server 2000
More than ever, organizations rely on data storage and analysis for business operations. Companies need the ability to deploy… (read more)

Featured Script
Capture Replication Statistics in AdminDB
This procedure can be used in a job to regularly capture Replication Counters (Statistics) for historical review. This can be… (read more)