Follow sqlserver-dba.com

Subscribe to RSS feed  Follow @jackvamvas - Twitter

*Use the Comments section for questions

SQLServer-DBA.com Links

Dba_db2_button

dba-ninja.com

SQL Server DBA Jobs

DRY or WET – SQL Antipattern #005

30 January,2016 by Tom Collins

I was discussing with some software developers the  DRY software design principle. It is an acronym for "Don’t repeat  yourself".  For some developers , it’ s considered one of the basic principles of software programming.  But like many principles, DRY can be misunderstood and applied in a way leading to confusion.

DRY or WET may be an  SQL Antipattern . Read more on how to spot a SQL Antipattern

The DRY principle is stated as “Every piece of knowledge must have a single, unambiguous, authoritative representation within a system.”The principle was formulated by Andy Hunt and Dave Thomas in The Pragmatic Programmer,

To assist me in  understanding  DRY  principle I’ve broken down the definition into two key components

1) piece of knowledge

2) single representation

Most examples of abusing the DRY principle stem from an over emphasis of one of these components.  Don’t confuse  literal knowledge with meaning.  Understanding the context of the literal value makes a difference in how it is used. The word “cup” may mean different things in different parts of the code.   Simply creating a variable and associating this value can easily lead to confusion  within understanding the code.Maintaining the code, troubleshooting or bug fixing may become a complicated process, due to misunderstanding the usage of values

The DRY principle violation is known a WET   “write everything twice” . Comparing  WET  and  DRY  practises leads to a useful perspective on  best practises.

  1. Code reuse – How can a script be written to avoid rewriting the same code for the same purpose repeatedly? Avoid copy and paste of code. There are a number of methods within SQL Server to assist
  2. Views can offer a way of representing common joins. Read more on Precalculated SQL Views data
  3. Functions are a method to encapsulate certain code, which can be referenced in the main code body. Applying functions need to be carefully considered as they can lead to sub optimal performance - Functions and WHERE clause and suboptimal query plans
  4. Automated testing versus manual testing. Although not a coding practise, thinking about testing through the DRY lens can lead to a more efficient practise. Why test something manually , when it is possible to automate manual repeatable tasks?
  5. Database design and schemas. How relational is your database design ? At what point does the design become overly relational and some normalisation needs to be introduced? Analyse the database design to see if it will lead to normalisation

 

Read more on SQL Antipatterns

Using NULL as an ordinary value in sql code - SQL Antipattern # 001

The practise of Hard Coding – SQL Antipattern #002

SQL Silver Bullets and Easy Fixes - SQL Antipattern #003

Blind Faith versus Test Oriented programming – SQL Antipattern #004

 

 


Author: Tom Collins (http://www.sqlserver-dba.com)


Share:

Verify your Comment

Previewing your Comment

This is only a preview. Your comment has not yet been posted.

Working...
Your comment could not be posted. Error type:
Your comment has been posted. Post another comment

The letters and numbers you entered did not match the image. Please try again.

As a final step before posting your comment, enter the letters and numbers you see in the image below. This prevents automated programs from posting comments.

Having trouble reading this image? View an alternate.

Working...

Post a comment on DRY or WET – SQL Antipattern #005


sqlserver-dba.com | SQL Server Performance Tuning | SQL Server DBA:Everything | FAQ | Contact|Copyright & Disclaimer