UNPIVOT: Normalizing data on the fly(weblogs.sqlteam.com)

submitted by jimrice14(880) 4 years ago

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: 4

tags: Database another

Simple DataViewReader class that implements IDataReader for a DataView(weblogs.sqlteam.com)

submitted by jimrice14(880) 4 years, 2 months ago

I previously wrote that the DataTableReader class is really handy, but unfortunately there is no DataViewReader class. So, I wrote one. read more...

add a comment |category: ADO.NET |Views: 10

tags: DataViewReader ADO.NET Database another

Some SELECTs will never return 0 results -- regardless of the criteria(weblogs.sqlteam.com)

submitted by jimrice14(880) 4 years, 6 months ago

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

tags: Database another

Taking a look at CROSS APPLY (SQL Server 2005)(weblogs.sqlteam.com)

submitted by jimrice14(880) 4 years, 7 months ago

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: 32

tags: Database another

Avoid Mixing INNER and OUTER Joins in SQL(weblogs.sqlteam.com)

submitted by jimrice14(880) 4 years, 7 months ago

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: 22

tags: Database another

Creating CSV strings in SQL: Should Concatenation and Formatting Be Do (weblogs.sqlteam.com)

submitted by jimrice14(880) 4 years, 7 months ago

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

tags: Database another

Conditional WHERE Clauses: Avoiding ORs and CASE Expressions(weblogs.sqlteam.com)

submitted by jimrice14(880) 4 years, 8 months ago

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: 76

tags: SQL Database another

Working with Date-Only and Time-Only data in SQL Server(weblogs.sqlteam.com)

submitted by jimrice14(880) 4 years, 8 months ago

It's all about using the right data types. Sure, there's no "date" or "time" only data type available, but that's no excuse to return VARCHAR strings formatted as dates or times instead of the correct data type: datetime. read more...

1 comment |category: Database |Views: 5

tags: Time Date SQL another

Don't forget about composite primary keys -- many programmers do!(weblogs.sqlteam.com)

submitted by jimrice14(880) 4 years, 9 months ago

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: 23

tags: DB CompositeKeys DataModel another

The problem isn't the poor database model; It's that external code is (weblogs.sqlteam.com)

submitted by jimrice14(880) 4 years, 9 months ago

Dealing with poorly designed databases is a simple and common fact of life for programmers. It happens, sometimes due to lack of experience or education, or sometimes because business requirements were never analyzed properly or they changed. It's hard to avoid poor database designs, but it takes only a simple concept to make fixing those designs much easier. read more...

1 comment |category: Database |Views: 1

tags: DB Database another

But *WHY* Must That Column Be Contained in an Aggregate Function or th(weblogs.sqlteam.com)

submitted by jimrice14(880) 4 years, 10 months ago

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: 6

tags: GroupBy SQL another

The "Nested WHERE-IN" Anti-Pattern(weblogs.sqlteam.com)

submitted by jimrice14(880) 4 years, 10 months ago

There's a fascinating technique that I see many beginners use when writing SQL statements, and I call it the "Nested WHERE-IN" anti-pattern. It is, unfortunately, a common SQL technique used to avoid JOINS at all costs. read more...

add a comment |category: Database |Views: 4

tags: AntiPatterns SQL another

Data Types -- The Easiest Part of Database Design(weblogs.sqlteam.com)

submitted by jimrice14(880) 4 years, 10 months ago

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: 1

tags: another

Passing Arrays or Tables as Parameters to a Stored Procedure(weblogs.sqlteam.com)

submitted by jimrice14(880) 4 years, 11 months ago

SQL is a set-based language, and often we wish to pass sets of data as a parameter to a stored procedure. For example, you might wish to pass a set of Customers to an invoice generating stored procedure, or a set of employees which for you’d like to calculate a particular bonus. There doesn't seem to be an easy way to accomplish this in T-SQL, but here's a simple technique that you might want to look at. read more...

add a comment |category: Database |Views: 11

tags: another

What I learned about SQL from using Access(weblogs.sqlteam.com)

submitted by jimrice14(880) 4 years, 11 months ago

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

tags: another

Data Modeling: Don't Let Output Dictate your Design(weblogs.sqlteam.com)

submitted by jimrice14(880) 4 years, 11 months ago

I often talk about "database layer" versus "presentation" layer, but even the within just the database layer it is important to understand that how the data is physically stored does not always have to correlate with how the database returns results. There is still room for your SQL statements to use Views, Stored Procedures and User-Defined Functions to occasionally transform your well designed, clean normalized data into something that is easier for reporting tools to consume -- especially if those tools are used by clients who don't have strong skills. read more...

add a comment |category: Database |Views: 0

tags: another