UNPIVOT: Normalizing data on the fly

weblogs.sqlteam.com — Everyone seems to want to "pivot" or "cross tab" data, but knowing how to do the opposite is equally important. In fact, I would argue that the skill of "unpivoting" data is more useful and more important and more relevant to a SQL programmer, since pivoting results in denormalized data, while unpivoting can transform non-normalized data into a normalized result set. read more...

add a comment | category: Database | Views: 1

Some SELECTs will never return 0 results -- regardless of the criteria

weblogs.sqlteam.com — In SQL, the general rule of thumb is that the number of rows returned from a SELECT will be zero if your criteria did not match any data. However, there is an important exception to this rule; it does not apply when asking for aggregate calculations such as SUM(), MIN() or MAX(), without any grouping. This is rather interesting and important to know and look out for, as it can cause some confusion and recently some of my ASP.NET code failed due to this. Let's take a look. read more...

add a comment | category: Database | Views: 1

Avoid Mixing INNER and OUTER Joins in SQL

weblogs.sqlteam.com — I had previously written about the danger of Criteria on Outer Joins, but recently another situation popped up that occasionally causes issues with OUTER JOINS that I thought I might address. The issue is when you have multiple tables joined in a single SELECT, and you mix OUTER and INNER JOINS together. The end result doesn't always seem to "work", and it can be tricky to understand exactly why and how to fix it without incurring additional unintended side effects. read more...

add a comment | category: Database | Views: 1

Conditional WHERE Clauses: Avoiding ORs and CASE Expressions

weblogs.sqlteam.com — Some tips regarding optimizing your SELECT statements when using conditions in your WHERE clause based on optional parameter values. read more...

add a comment | category: Database | Views: 1

Don't forget about composite primary keys -- many programmers do!

weblogs.sqlteam.com — When you define more than one column as your primary key on a table, it is called a composite primary key. And many experienced and otherwise talented database programmers have never used them and may not even be aware of them! Yet, composite primary keys are very important when designing a good, solid data model with integrity. read more...

add a comment | category: Database | Views: 0

But *WHY* Must That Column Be Contained in an Aggregate Function or th

weblogs.sqlteam.com — Column 'xyz' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause. Arggh!! There it is, yet again .. that annoying error message. Why is SQL so picky about this? What's the deal!? read more...

1 comment | category: Database | Views: 1

Data Types -- The Easiest Part of Database Design

weblogs.sqlteam.com — I see it time and time again in forums -- "dates" that don't sort properly, "numbers" that don't sort or sum() correctly, made up "boolean" conventions that are difficult to work with, and so on ... Of course, without a schema or DDL to see, it sometimes takes many posts going back and forth until we finally realize: "wait ... you aren't using a datetime data type to store these dates???" read more...

add a comment | category: Database | Views: 0

What I learned about SQL from using Access

weblogs.sqlteam.com — Microsoft Access / JET SQL doesn't support cursors; the only way to process rows one by one is using VBA and opening a Recordset. This means that you're stuck using set-based "queries". So ... is this a good thing or a bad thing? A very, very good thing -- in fact, it's what makes Access a great tool for learning SQL! read more...

add a comment | category: Database | Views: 0