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

Taking a look at CROSS APPLY (SQL Server 2005)

weblogs.sqlteam.com — I think the easiest way to think of CROSS APPLY is that it is like doing a CROSS JOIN with a correlated sub-query instead of a derived table. Let's take a look .... read more...

add a comment | category: Database | Views: 1

Creating CSV strings in SQL: Should Concatenation and Formatting Be Do

weblogs.sqlteam.com — A question I see very often in the SQLTeam forums is how to return data in a somewhat summarized form by concatenating multiple values into a single CSV columns. This can be done fairly easily in T-SQL, but as the formatting and concatenation requirements becomes more elaborate, be sure to ask yourself: Am I forcing presentation code into the database layer? read more...

add a comment | category: Database | Views: 0

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