Report Writing
Reporting Services, Crystal Reports, Access and other report-writing tools. Also, this covers T-SQL techniques that come in especially handy in report-writing.
A question I see very often in the SQLTeam forums is how to return data in a summarized form by concatenating a list of values into a single CSV column. 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...
Another common area of difficulty or confusion for SQL beginners is how to efficiently retrieve data just for a single month. What is the best way to declare parameters that will be used to indicate which month you are looking for, and how can we efficiently and easily make use of those parameters to get back the data we need? read more...
So, I hear you're a "set-based SQL master" ! As Yoda once said, you've "unlearned what you have learned". You've trained yourself to attack your database code not from a procedural, step-by-step angle, but rather from the set-based "do it all at once" approach. However, don't completely forget the most important skill that you learned in the procedural world!
read more...
As many of you know, I strongly recommend that you avoid using RIGHT OUTER JOINS, since they make your SQL code less readable and are easily rewritten as LEFT OUTER JOINs. In addition, I have yet to find a situation where a FULL OUTER JOIN makes sense or is necessary -- I have found that in just about every case other techniques work better.
read more...
A non-technical guide to writing reports, with a focus on managing requirements, results, and expectations.
read more...
Sometimes, when writing SELECTs, we come across situations in which we we need to write a join containing a condition of some sort. You might think to do this with either a CASE expression or with some OR boolean logic in your join expression. There's a much better way to approach the problem.
read more...
I've been playing around with a handy tool for creating Word and Excel files called OfficeWriter that's pretty impressive.
read more...
As promised in my last post, here is some performance testing to help you determine the performance benefits (if any) of performing your crosstabs at the presentation or code layer, as opposed to forcing SQL Server to do this.
read more...