At work our database server is humming along. It runs a little under 50% on the CPU, the RAM is a bit high, and the IO is very responsive. But, it could be better. About a quarter of the load on that server comes from one application. It’s a reporting solution built to handle end…
Category: Microsoft SQL
T-SQL Challenge: Find Days of the Week
My personal goal is to post an article every Monday, Wednesday and Friday. What I’d like to do is create stubs in my content database with each of those days already set aside. That way, I can look at the next stub available and simply start writing. The challenge for today is to create a…
Incomplete Tables
One of the things I’ve noticed while performance tuning is many of the tables in my current environment are incomplete. They all have columns defined, and they all have data, but they’re still missing something. Many are missing a primary key. You don’t always have to have a primary key, but when you’re joining against…
SQL 201 – Combining Query Results
Overview There comes a point in your SQL career where you will need to combine the results of several queries into one data set, and present it in one view. When you get to this point, you’ll need to be familiar with the UNION command. With Microsoft SQL 2005, there are two new functions, similar…
SQL 101 – JOIN
Overview I want to cover a fundamental topic for SQL programming. This is the topic you should start studying right after you have a basic understanding of what a relational database, what tables are, and that tables can be related to each other. If you’re a fan of Duct tape, then Joins will become the…
SQL 201 – Sub Queries
Sub queries or nested queries are complete queries wrapped inside of another complete query. Some times this technique is used as a replacement for a JOIN statement. Some times it’s the only way to define exactly what you you want to get from a database. The important thing to learn about sub queries is they…
SQL 101 – GROUP BY
Consider the following table, orders. buyer productName purchaseDate qtyPurchased pricePaid ————- ————— ————— ———– ——— Shannon Lowder pencil 1/1/2000 1 .25 Shannon Lowder paper 1/1/2000 2 1.00 Shannon Lowder Mountain Dew 1/1/2000 1 1.25 Shannon Lowder pencil 1/5/2000 1 …
SQL 101 – Summarizing Data
This is the last of the lessons on functions for the 100 level. I hope you’ve enjoyed them so far, but it’s time to wrap these up! When beginning to learn SQL, it won’t be long until you have to answer questions that require you to summarize the data. It’s one of the primary reasons…
SQL 101 – System Functions
There are many functions built in to SQL 2000. I’m not going to cover them all, since many of them you won’t have a need for until you really dig in deep with SQL programming. But there are seven you should really be familiar with. CASE You have no idea how much you’ll use the…
SQL 101 – Date Manipulation Functions
Welcome back! This post is a followup to the post about string functions. The difference is this time, we’re discussing functions that manipulate dates. GETDATE The most often used function I can think of, with the possible exception of COUNT(*) is GETDATE(). When called this gives you the current datestamp from the server. This gives…