Jeff Smith Blog

More on Runs and Streaks in SQL

Wed May 30, 2007 by Jeff Smith in t-sql, techniques, efficiency

That's right boys and girls, it's what you've been waiting for all weekend: Another edition of the mailbag! Damian writes: Hi I have a tricky SQL question that I have been trawling the net and workmates to find an answer. Read more →

Real Programmers don't need to write test applications!

Wed May 23, 2007 by Jeff Smith in miscellaneous, techniques, humor

You are a very important, talented, enterprise-level programmer! You write and maintain millions of lines of code, compiling your applications takes several hours, and your databases contain hundreds of tables with millions of rows. Read more →

SQL Challenge Response: Finding Consecutive Available Blocks in a Schedule

Tue May 15, 2007 by Jeff Smith in t-sql, techniques, efficiency, links, datetime-data

(Note: Updated 5/15/2007 @ 12:25 PM EST to show another possible solution with different results.) Here's my response to the SQL Challenge given here. The challenge involves having a schedule table with days and times, and displaying all available consecutive free time slots to schedule an event. Read more →

Thinking Set-Based …. or not?

Mon Apr 30, 2007 by Jeff Smith in t-sql, techniques, report-writing, joins-relations

Thinking "Set-Based" 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. Read more →

Better Alternatives to a FULL OUTER JOIN

Thu Apr 19, 2007 by Jeff Smith in t-sql, techniques, efficiency, report-writing, joins-relations, group-by

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. Read more →

How to format a Date or DateTime in SQL Server

Fri Apr 13, 2007 by Jeff Smith in t-sql, techniques, efficiency, database-design, datetime-data

Question: How do you create a DateTime in a specific format in SQL Server? Answer: You don't. You can't. The only way to translate a DateTime into a specific format is to convert it to a VARCHAR or other "string" data type. Read more →

How to be an Effective Report Writer

Fri Apr 13, 2007 by Jeff Smith in techniques, report-writing

Learn the Database Schema Before you can write any reports off of any system, you must be familiar with the schema you are working with. Print out the data dictionary, schema diagrams, documentation -- anything you can find that will help you work with the database. Read more →

Dear DBA ….

Wed Mar 28, 2007 by Jeff Smith in techniques, efficiency, humor, database-design

Dear DBA – Thanks so much for helping us developers out with the latest changes you've made to the stored procedures in our system. While it may have been nice if we got together first to discuss these changes, I do appreciate that you worked very hard to make things much easier for us. Read more →

Sometimes the problem isn't the code. It's the specs.

Wed Mar 14, 2007 by Jeff Smith in t-sql, techniques, group-by

I thought I'd take a few minutes to discuss a general situation we see quite often in the programming world, using a simple T-SQL example. Let's say we have created a stored procedure that returns Customers from a database which allows you to optionally specify a particular Region to filter by. Read more →

Alias Naming Conventions

Mon Jul 31, 2006 by Jeff Smith in miscellaneous, techniques

Let's go to the mailbag! In response to this post, Chris writes: Jeff, I just got done reading your VB posts. I love those sarcastic posts you do. Read more →

Always Use Parameters. Even if you don't use Stored Procedures.

Fri Jul 21, 2006 by Jeff Smith in t-sql, net-c-vb, techniques

(3/25/2007 update: Fixed the incorrect parameter name the last two examples) Parameters without Stored Procedures? Let's assume that for some reason you are not using Stored Procedures. While I can respect your choice in that regard, that doesn't mean that you cannot still use parameters when constructing your SQL statements at the client. Read more →

How to JOIN Multiple Transactional Tables in SQL

Mon Jun 19, 2006 by Jeff Smith in t-sql, techniques, joins-relations, group-by

A common difficulty beginning SQL programmers encounter is joining two or more transactional tables all in one SELECT statement. Missing data, duplicates, time-out errors, and other unexpected results often arise from trying to directly write JOINS between two transaction tables. Read more →

Vulnerable to SQL Injection?

Fri Apr 21, 2006 by Jeff Smith in t-sql, techniques, security

One of the things that troubles me most about SQL Injection is that is seems it is still very misunderstood. Is the following psuedo-code vulnerable to SQL Injection? String SearchTerm = {some user input here, unvalidated and unscrubbed -- uh oh ! Read more →

Another SQL Suduko Solver

Thu Apr 6, 2006 by Jeff Smith in t-sql, techniques

There's been quite a few posts out there with SQL implementations of Suduko puzzle solvers: This one is a T-SQL solver, but it really doesn't do any set-based operations and doesn't really make use of SQL that much. Read more →

Programming is a funny thing ….

Thu Mar 30, 2006 by Jeff Smith in miscellaneous, techniques

In most industries, beginners work on simple, short projects, using only the most basic concepts that they have learned. The experts in those industries, meanwhile, work on things that are very large, complicated, difficult to manage, and require lots of time, energy and resources. Read more →

Some Simple SQL Rules to Live By

Tue Mar 14, 2006 by Jeff Smith in t-sql, techniques, efficiency, sql-server-2005, security

Most of these are really basic. Some are my take on established ideas and standards, and may be controversial. Agree or disagree? All feedback is welcome! (well ... mostly the "I agree, you're a genius" feedback is welcome . Read more →

Data belongs in your tables – not in your code

Fri Feb 10, 2006 by Jeff Smith in t-sql, techniques, efficiency, database-design

Here’s my advice of the day: Try your best to store all of the data relating to your database in the tables. Sounds pretty obvious, I know, but you might be surprised just how much data in your application is not in your tables, where it belongs, but rather nestled away in your code. Read more →