Tag: T-SQL

Counting the Existing, and Missing, Rows
I saw this as a problem recently from someone and thought it would make a good post. As I was building a quick solution, someone else posted theirs, but I decided to save mine as a blog post. It was a good, quick, T-SQL exercise for me to work on.

I decided to take the Christmas season and use that as my example. My wife and I buy presents for the family and we try to understand what we’ve bought each year to balance out our efforts for each kid.

My setup:
```
CREATE TABLE People
    (
      id INT
    , firstname VARCHAR(20)
    );

CREATE TABLE presents
    (
      id INT
    , present VARCHAR(20)
    , value NUMERIC(6, 2)
    );


INSERT INTO People
    VALUES  ( 1, 'Kyle' ),
            ( 2, 'Delaney' ),
            ( 3, 'Kendall' ),
            ( 4, 'Tia' ),
            ( 5, 'Steve' )
INSERT INTO presents
    VALUES  ( 1, 'Book', 10 ),
            ( 1, 'Fire', 157 ),
            ( 3, 'Book', 8 ),
            ( 3, 'tablet', 162 ),
            ( 3, 'hat', 12 ),
            ( 4, 'bracelet', 80 )
```
I’ve modeled this with two tables: one holding people and one with the presents. I need to join them together and see what I’ve bought.
```
SELECT p.firstname
      , presentcount = COUNT(ps.present)
      , value = ISNULL(SUM( ps.value), 0)
    FROM people p
        INNER JOIN presents ps
        ON p.id = ps.id
    GROUP BY p.firstname
```
That gives me a count of gifts and money spent

The problem is that it doesn’t let me know what people I haven’t bought for. For that I need to change to an outer join, in this case I’ll choose a left outer join since the people table is the one I need all rows from:
```
SELECT p.firstname
      , presentcount = COUNT(ps.present)
      , value = ISNULL(SUM( ps.value), 0)
    FROM people p
        LEFT OUTER JOIN presents ps
        ON p.id = ps.id
    GROUP BY p.firstname
```
Now I can see that Kendall and Steve haven’t received any presents yet. More work to be done:

Outer joins are a quick way to find issues, but be sure you understand how they work. In this simple case, it’s an easy change.

Now this looks like software I might actually use. Perhaps this would make a good project for me?
January 20, 2014
Checking Up on Developers
This editorial was originally published on May 8, 2009. It is being re-run as Steve is on vacation.

The other day I was reading Brad McGeHee’s post on duplicate indexes, and it struck a chord with me. That same issue is one I’ve seen many times with in house applications. Developers don’t understand indexing, they create one clustered index (CI) as the PK, and then they create the same index as a nonclustered (NCI) one. Either they don’t realize that the PK is an index, or they are ignorant of the differences between a CI and an NCI.

What’s worse is that I see it in third party applications as well. If you’re selling a product, I think you ought to know how to tune it for a database. If you don’t, I’d like to see some recourse for clients. Maybe some common settlement in a lawsuit. A few of those and I bet you’d have more DBAs hired by software firms.

Or a little more training for developers.

For this Friday’s poll, I wanted to pick on developers a little. I like developers, I appreciate the work they do creating some great applications I use, but I also think they make some silly database mistakes. So this week:

What are common mistakes developers make in SQL Server?

Indexing is one area, but what other things do you see a lot from developers? I’m thinking a good list of mistakes here might be a nice checklist to go over with developers as they complete an application. It could be in code reviews, QA, pre-purchase requirements, RFPs, any number of places.

I’ll add one more indexing issue that I’ve seen often in third party applications. Too often I’ve seen developers create a separate index on every single field in a table. For all tables! I realize that indexes improve query performance, but it does create an issue with inserts/updates, and space. Space isn’t an issue as much as it was in the past, but as data sizes get larger, I would not be surprised to see this becoming a problem in some applications. More indexes also impact backup time and size.

Give us a response this Friday of the common mistakes that you see developers making, and that you wish they’d learn to avoid.

Steve Jones

The Voice of the DBA Podcasts

The podcast feeds are available atsqlservercentral.mevio.com. Comments are definitely appreciated and wanted, and you can get feeds from there.

You can also follow Steve Jones on Twitter:

Overall RSS Feed: or now on iTunes!
- Windows Media Podcast – 32.4MB WMV
- iPod Video Podcast – 23.2MB MP4
- MP3 Audio Podcast – 4.7MB
Today’s podcast features music by Everyday Jones. No relation, but I stumbled on to them and really like the music. Support this great duo at www.everydayjones.com.
November 26, 2013

Core T-SQL

Often I find myself wondering about the minimum bar we expect people to clear to be competent in some field. Almost all fields are evolving these days as technology and new ideas are put into practice in medicine, art, construction, law, etc. It seems as though a Renaissance is taking place with the speed and variety at which new information is spreading, usually due to advances in technology.

In our field, working with SQL Server, we certainly see new ideas and enhancements taking place all the time. However there are also a number of core skills that evolve, but at a slower pace. For example, T-SQL grows with each new version of SQL Server, but the core language remains, and many people continue to use the knowledge they have had for years when writing code.

This week I’m curious if we can debate about, and compile, a list of core skills with T-SQL that we think someone ought to understand to be considered competent as a database developer. I’m asking:

What core skills should someone have with T-SQL?

I’ll start the list, but feel free to add to it or give me your thoughts. I think someone ought to be able to understand these items and write code to solve problems that involve:

finding duplicate rows (grouping, joins)
returning aggregates of single or multiple columns (MAX, MIN, SUM, COUNT)
return aggregates in groups, or islands. (grouping and aggregates, windowing)
join multiple tables together on matching, multiple columns (joins)
find data in one table that doesn’t have matches in another (outer joins)
filtering data (WHERE)
subqueries and complex CTE joins of data
create row numbers and join back to a table without numbers (APPLY)
pivoting data from rows to columns

That’s a starting list from a number of questions I’ve seen, but feel free to add your own skills you think people need.

Steve Jones

Video and Audio versions

Today’s podcast features music by Everyday Jones. No relation, but I stumbled on to them and really like the music. Support this great duo at www.everydayjones.com.

Follow Steve Jones on Twitter to find links and database related items and announcements.

Windows Media Video ( 20.3MB)

MP4 iPod Video ( 23.4MB)

MP3 Audio ( 4.8MB)

Feeds are available at iTunes and Mevio

To submit an article, rant or editorial,
log in to the Contribution Center

November 22, 2013

Test Coverage

I’ve never had to work full time in a QA group, but I have had to perform software testing of an application and it wasn’t fun. Even as I worked through the various features, looking for edge cases, common mistakes, etc., I realized that running tests was something that really required more discipline and process than I was giving it at the time. Too often I ‘d realize that my regressions weren’t 100% duplicates of previous executions because I’d allowed too much “human effort” into my process.

These days most testing of software is automated. I know QA groups still exist, and they need to handle some of the manual checks that are very hard to automate. However more and more testing is being pushed back onto developers to handle, with frameworks like NUnit and JUnit. In the database world, we haven’t done a great job of including testing into the code we write, but there’s a great testing framework we can use.

TSQLT is a framework written by Sebastian Meine and Dennis Lloyd and it’s free. It’s been developed to help you write tests that can exercise your T-SQL code to determine if it’s doing what you expect. I’ve used it a little, and while I see the potential, I also realize that this will take some practice to learn how to more easily write tests that can cover the various potential places where bugs can be introduced.

However, if you use version control, and you should, then you can easily spread the load of writing tests to all of your developers. Since the tests are stored procedures, they can be included in your VCS project and shared by all your developers. With a little effort from each member of your team, you might be surprised at the code coverage you can achieve with this framework.

Testing is important, as we see over and over again as software is released and doesn’t work as expected. I think much of that is our fault, as software developers, for not improving our testing skills and discipline.

Steve Jones

If you want more automated ways to easily share tests between developers and execute them, take a look at SQL Source Control and SQL Test from Red Gate Software.

Video and Audio versions

Today’s podcast features music by Everyday Jones. No relation, but I stumbled on to them and really like the music. Support this great duo at www.everydayjones.com.

Follow Steve Jones on Twitter to find links and database related items and announcements.

Windows Media Video ( 20.9MB)

MP4 iPod Video ( 24.8MB)

MP3 Audio ( 4.9MB)

Feeds are available at iTunes and Mevio

To submit an article, rant or editorial,
log in to the Contribution Center

November 12, 2013