Tag: T-SQL Tuesday

Basic Inner Joins – Basic Skill #4
This post is part of a series based on my presentation The Top Ten Skills You Need for SQL Server. This post is part of Skill #4 – Joins.

T-SQL Tuesday #23

This post is also serving as my T-SQL Tuesday post for the month. This month the party is brought to you by Stuart Ainsworth. If you want to know more about what T-SQL Tuesday is about, read Stuart’s post as well as Adam Machanic’s original post and then join in next month.

An inner join is essentially an intersection of two sets. If you go back to grade school, and think about sets, you can have two items like this:

If you look at these two sets, you see that there are various elements in each set. If we were to show the intersection, it would be this set: (B). This is shown below (excuse my horrible artwork):

In SQL, we deal with tables, but we can model this as follows:
```
CREATE TABLE SET1
( mychar varchar(1) ) GO CREATE TABLE SET2
( mychar varchar(1) ) GO INSERT SET1 SELECT 'A' INSERT SET1 SELECT 'B' GO INSERT SET2 VALUES ('B'), ('C'), ('D') GO SELECT a.mychar
  , b.mychar
 FROM SET1 a
   INNER JOIN set2 b
     ON a.mychar = b.mychar
```
```
mychar mychar
------ ------
B      B
```
The results are the matching values in each table. In this case each table is a single column, modeling the images above where there is a single letter in each item of the set. The matching columns are the join columns, and in database work, these would be the data items that we are storing in both tables.

However to expand this, in a database table, we usually have multiple items, so each letter could be a series of data elements, or could have a series of other fields attached to it. Suppose I change these “sets” a little:
```
DROP TABLE set1
DROP TABLE dbo.SET2
go CREATE TABLE SET1
( mychar varchar(1) , Customer VARCHAR(50) , ActiveDate datetime ) GO CREATE TABLE SET2
( mychar varchar(1) , Customer VARCHAR(50) , ActiveDate datetime ) GO INSERT SET1 SELECT 'A', 'Bob', '1/1/2011' INSERT SET1 SELECT 'B', 'Bill', '2/1/2011' GO INSERT SET2 VALUES ('B', 'Steve', '1/2/2011'), ('C', 'Andy', '3/1/2011'), ('D', 'Brian', '3/3/2011') GO SELECT a.mychar
  , b.mychar
 FROM SET1 a
   INNER JOIN set2 b
     ON a.mychar = b.mychar
```
I still only have one matching row, but there are other data points. If I alter my diagram, they look like this:

The matching rows, in this case the rows with a “B” in them, have different data, which seems to be an issue. However suppose set 1 was a list of customers along with their first order date and set 2 was a list of salespeople and the dates they started. Then the join might be on sales, with the letter (A, B, C, D) representing the order.

That’s the basic of a join. It gets complicated as you look to join three, four, or more tables, but this is the basic idea of an inner join in SQL.
October 4, 2011
Data Presentation – T-SQL Tuesday #22

It’s T-SQL Tuesday again, with our host this month being Robert Pearl, of Pearl Knowledge Solutions. He chose the topic of Data Presentation for September.

If you haven’t participated in a T-SQL Tuesday, it’s easy. Read the rules in Robert’s post and read the basic idea in Adam Machanic’s original invittation. Then write a blog post with the logo to the right in it and link it back to Robert’s post.

That’s it.

Formatting Data

One of the things that DBAs and data professionals should learn is that formatting typically doesn’t belong in the database. The client, front end, or the tools used to extract the data should handle formatting, presenting the data as the client wants to see it.

When you store numbers, you don’t want to store things like currency symbols. So in SQL Server I should see this:

and not this:

The former gives more flexibility, and the latter limits what you display (and requires character storage).

The same thing goes for other data, for example, phone numbers. We don’t want to store our phone numbers like this:

If someone wants to see just the number, or they add in another format of number (say European), then you have inconsistencies. Also, it becomes harder to separate out issues. When you do separate out the number from the area code, how do you handle things if you have this:

That’s hard to handle. If all the phone numbers were numbers, it’s easier to handle. This allows one easy CASE statement based on length.

Or better yet, use better design and let the front end handle data presentation.

Doing It Right

I guess I haven’t talked data presentation so much as how not to handle data presentation. I know that SQL Server can do things like ordering, formatting, combining or splitting strings, or more, but ultimately I think that’s not the right way to handle things.

I’ve always viewed the database as the single bottleneck. It’s incredibly hard, and expensive, to scale a database server, while it’s easier to scale web servers, app servers, and client tools, and much less expensive. It’s even easier to scale developers and have them write more code to handle presentation on the front end instead of using SQL Server to do the work. I’d try to always push any presentation work to the client instead of the database server, just because of the workload and bottleneck on the server.

It might seem like more work up front, and it will be, but it will be infinitely better than trying to re-write code or upgrade your database server later when the load becomes larger.

September 13, 2011
T-SQL Tuesday #19 – Disaster Recovery
This month Allen Kinsel hosts T-SQL Tuesday #19 with a disaster recovery theme. This is the blog party idea started by Adam Machanic where everyone that participates writes on a single theme.

If you want to host, contact Adam. If you want to participate, read Allen’s post and write your own post and link it in.

What’s a Disaster

It’s interesting that this is the theme since I already had two posts on DR scheduled this week and am working on modifying my “Preparation for Disaster” presentation for SQL in the City.

It’s hurricane season for Allen on the Gulf, and while hurricanes give you advance notice when they are coming, you have to be prepared. I’ve lived on the East coast, and we always were worried about getting hit in Virginia Beach. We never did get hit, but that didn’t stop us from being prepared and sometimes even initiating some of our disaster protocols.

As a side note, we had an office in High Point, NC, and it actually got hit by a hurricane, or the remnants of one and lost power. I was in a downtown hotel at the time and woke up without power.

The thing is large disasters are rare. You have very little chance of your data center getting hit by a hurricane, a tsunami as Japan recently had, a tornado such as the Northeast have experienced this year, They happen regularly, but the chance of them hitting your data center is low. The chance of you “losing” a data center is low. It just doesn’t happen that often, and the complete loss of a data center might mean you have bigger things to worry about.

However there are plenty of disasters that you are likely to experience. It’s possible that you could have a fire in your data center that affects the database server. Not likely, but it could happen. It’s more likely that you could have one of these:
- an operator spill coffee on your server
- someone trips over a power cord or fiber cable and brings down your system
- a memory chip go bad and get in memory corruption.
- it’s possible power goes out for some reason, even with a UPS.
- It’s highly likely that a drive will fail that contains data you need.
These are the types of disasters that you really have to prepare for, since they are possible, and even likely.

What’s more likely are the “Whoops” disasters as I like to call them. It’s very likely that someone will import the wrong file, update all prices instead of one, or even more likely, a developer or DBA will run the wrong code on the wrong server. The most likely disaster is probably this one:

If the DBA hits “Execute” here, it’s a disaster. Not a big one, but if this is a critical table in a critical system, you might have an even bigger reaction from manangement than if a hurricane hit.

You have to prepare for disasters, but don’t get caught up in worrying about the data center being destroyed. Those disasters are rare. Most of your preparation, your practice, your checks, have to be focused on the more likely disasters, which are often smaller in scale and focused on your database.
June 14, 2011

T-SQL Tuesday #18 – My CTE

It’s time for T-SQL Tuesday again, and it’s number 18. Hard to believe it’s been a year and a half since Adam Machanic (blog | @AdamMachanic) thought of the idea. I’ve participated in most and it’s something I look forward to each month. This month Bob Pusateri hosts the party with the theme of CTEs. No, it’s not thermal unit of expansion, and I hated chemistry.

My CTE

When CTEs were introduced, I thought they were a great idea. They made it much easier to write complicated queries that might need derived tables. In the past, writing something like this was hard to read.

SELECT p.Class, p.Color, p.DaysToManufacture, p.ListPrice
 FROM Production.Product p
  INNER JOIN ( SELECT ph.ProductID, ph.StandardCost, pri.Quantity
                 FROM Production.ProductCostHistory ph
                   INNER JOIN Production.ProductInventory pri
                     ON ph.ProductID = pri.ProductID
                WHERE StandardCost > 10
             ) b
    ON p.ProductID = b.ProductID
  INNER JOIN Production.ProductInventory pi ON p.ProductID = pi.ProductID
  WHERE p.Color IS NULL AND p.DiscontinuedDate IS NULL

A CTE can make this much easier to keep track of, especially in places where you don’t want to create a view instead.

WITH ProductCTE
AS ( SELECT ph.ProductID, ph.StandardCost, pri.Quantity
                 FROM Production.ProductCostHistory ph
                   INNER JOIN Production.ProductInventory pri
                     ON ph.ProductID = pri.ProductID
                WHERE StandardCost > 10
) SELECT p.Class, p.Color, p.DaysToManufacture, p.ListPrice
 FROM Production.Product p
  INNER JOIN ProductCTE b
    ON p.ProductID = b.ProductID
  INNER JOIN Production.ProductInventory pi ON p.ProductID = pi.ProductID
  WHERE p.Color IS NULL AND p.DiscontinuedDate IS NULL

I know this isn’t a great example, but by moving subqueries to a CTE structure, the end query is easier to debug and read.

Top X of a Group

Suppose you have a small result set of something like this.

CREATE TABLE Books
( BookID INT IDENTITY(1,1) , BookName VARCHAR(200) , Genre VARCHAR(50) , reads INT ) go INSERT Books SELECT 'Old Man''s War', 'Sci-Fi', 200
INSERT Books SELECT 'Ender''s Game', 'Sci-Fi', 345
INSERT Books SELECT 'Red Thunder', 'Sci-Fi', 143
INSERT Books SELECT 'Quarter Share', 'Sci-Fi', 25
INSERT books SELECT 'The Enemy', 'Thriller', 67
INSERT books SELECT 'The Hunt for Red October', 'Thriller', 678
INSERT books SELECT 'Bad Luck and Trouble', 'Thriller', 545
INSERT books SELECT 'Game of Lions', 'History', 644
INSERT books SELECT 'The Rise of Theodore Roosevelt ', 'History', 67
INSERT books SELECT 'An American Life: The Autobiography', 'History', 267

Suppose I wanted to top two books from each genre, ranked by reads. A TOP 2 won’t work because that doesn’t allow you to specify groups. However using ROW_NUMBER and an OVER clause in a CTE, this becomes an easy query.

WITH BookRanks AS ( SELECT b.BookID
      , b.BookName
      , b.Genre
      , b.reads
      , ROW_NUMBER() OVER (PARTITION BY b.genre ORDER BY reads DESC) AS Counter FROM Books b
 ) SELECT bookID
      , Genre
      , reads
      , bookname
  from BookRanks
   WHERE counter <= 2

That gives me an easy to read result set:

bookID Genre reads bookname

——- ——– —– ———————————————————-

8 History 644 Game of Lions

10 History 267 An American Life: The Autobiography

2 Sci-Fi 345 Ender’s Game

1 Sci-Fi 200 Old Man’s War

6 Thriller 678 The Hunt for Red October

7 Thriller 545 Bad Luck and Trouble

Older T-SQL Tuesday Topics

Just a quick list of the past topics and the roundups.

May 10, 2011

Tag: T-SQL Tuesday

Basic Inner Joins – Basic Skill #4

Data Presentation – T-SQL Tuesday #22

Formatting Data

Doing It Right

T-SQL Tuesday #19 – Disaster Recovery

What’s a Disaster

T-SQL Tuesday #18 – My CTE

My CTE

Top X of a Group

Older T-SQL Tuesday Topics