Simple SQL: Optional Parameters, Static Statements

Dynamic SQL, as wonderful a thing as it is, is horrible for security and maintenance. Your best bet is to avoid it. People often whine about making parameters optional. If you set the parameters that you are not using to null, this is a simple thing to do:

declare
@state char(02),
@zip char(05)

set @state = 'CA'
set @zip = null

select
Address
from
CustomerAddress
where
State = coalesce(@state, State)
and Zip = coalesce(@zip, Zip)

Simple and elegant.

Tags: sql

EC2-SQL: Competition in the Cloud

Not to be outdone by Oracle, Microsoft SQL Server will be available on Amazon's EC2 come late autumn. Read about it here... And you may want to read up on Microsoft's Windows Cloud operating system for developers at Slashdot. Is Microsoft adapting? Come on, now... It happens... Sometimes... Seldom.

And so goes another step for cloud computing...

Tags: amazon, mssql, MicroSoft

Sulphur Clouds! Oracle on Amazon's EC2

Oracle puts its 11g database in Amazon's cloud. With a title like that, what can I add? This is a well written article about Oracle on EC2. They are also supporting backups being shipped to Amazon. Anything else and I will be reiterating the article.

Tags: amazon, oracle

Oklahoma Finds Next Level of Stupidity

Wow. That is all that I have to say after reading this article:

Oklahoma Leaks Tens of Thousands of Social Security Numbers, Other Sensitive Data.

Technically, State information is public... But this goes beyond overshare. You just know that the application account has escalated privileges... I have to go back to my opening statement; wow. What a kick in the head...

Tags: security, sql injection

Bad Design: A Reminder to Document

Brian Walker writes about SQL Server database design disasters: What not to do. Unfortunately, these design failings are all too common. We all take different things from articles; my tangent is about a lack of documentation.

As a nerd, I would much rather do the fun part--creating, troubleshooting or anything else that requires thought. As a consultant, time and time again I have seen poor designs that are compounded by a lack of documentation. Not determining standards and and conventions, you facilitate others mutating a design with their own habits. Even if you have a poor system, the maintenance of it can be helped by documentation. Poor and consistent is a far cry better than poor and inconsistent. That makes it generally referred to as 'awful.'

It really does not take much time to document your approach. Simple documentation to outline the what and the why--even just a paragraph or two--might save countless hours down the line. Bad design is often perpetuated by lack of formalizing the business process. The least that a good database designer (or architect, if you prefer (I do--who doesn't want to be an architect?)) can do is document their approach; a naming convention document and a data dictionary. It is also important to do this before, during and after the design/implementation. Saying you will do it later typically means a rushed deadline is justification for not documenting (yeah, I have used that excuse too). Perhaps writing it out will key you into a couple weaknesses of your design... And wouldn't that make that horrible documentation process worthwhile?

Tags: design, documentation

Random: Data Presentation of Discretionary Spending

The New York Times has an interesting article about discretionary incoming spending, broken into a few categories. Numbers are always interesting, but what really got me was the presentation of the data--very cool way of showing the world.

More on Naming Standards

Ronald Bradford has a very nice article about naming conventions--and not just because we generally line up on our opinions.


Tags: design,
naming convention

Singular Table Name

Almost as bad as prefixing tables with a table-indicator is using plural names. Even Oracle tools default to this... It is just wrong.

A relation constitutes a header and a body. The header (or relational schema) is a set of attribute (column) and domain (value) pairs. The body is the set of tuples (rows). A tuple is a valued occurrence of the header.

Consequently, if you make your table names plural, make your column names plural for consistency.

Less mathematical justifications for using singular tables names include:

Consistency - Because sometimes you need 'es' to pluralize a word.
Consistency - Shouldn't 'ProductOrders' be 'ProductsOrders?'

Just like your mother taught you, just because everyone else is doing it, doesn't make it right.


Tags: design,
naming convention

Newish DBA Comic Strip, World Rejoices

For a few weeks I have been following The Adventures of Ace, DBA. There have been a few funnies. And today's is rather good (the previous Error Code strip was good too). It is a new database-centric stick figure comic strip. There is no more to read here; go, be amused.

SQL Server 2008 Released

SQL Server 2008 is now available. Read about what's new and the return of the debugger.

Tags: mssql