Best Book/Site for improving MS SQL Skills?

July 12, 2005 1:58 PM

I currently have a very low level of SQL knowledge (I can construct simple queries for multiple tables, but I start to fall apart on the difference between inner and outer joins). I need to learn to create multiple join SQL queries and Dynamic SQL/Stored Procedures on MS SQL Server 2000 in a short period of time. I'd also like to learn to use MS Query Analyzer more efficiently. Is there one book or online resource that is best for this? Additionally, are there alternatives to using MS Query Analyzer, or is that really *the* tool?

(I'd like to transfer the knowledge over to using MySQL/PHP eventually, but right now I'm being paid to use Microsoft's spew)

posted by de void to Computers & Internet (12 answers total)

A sidenote, but what you write for MS SQL will likely be different than what you write for MySQL. In this case, Microsoft spew is many, many times better than MySQL in terms of a real database. Postgres is somewhat comparable,a nd if you ever use PHP with a database I'd seriously look into that.

For your question, I learned by reading a couple online sites and looking at examples. Playing around with the Northwind database will help you more than reading a book will.
posted by devilsbrigade at 2:17 PM on July 12, 2005

i used o'reilly's transact sql programming book. it was my bible for a while.
I find to grasp this kind of thing (joins), you just gotta do them a bunch.
If you've got MSSQL installed (ie you've paid for a license), you may as well use ms Query Analyzer. If you wish to save licensing costs, i use a little tool called winsql. its much cheaper. besides cost i don't see what alternatives you would want.
posted by alkupe at 2:18 PM on July 12, 2005

I've not specifically used the SQL tutorial but I've found the stuff at W3Schools helpful in the past. Also play around in Access - build your query with the GUI and then look at the SQL it generates - it won't be transferable to other databases but it should give you an idea of how it constructs joins.

And as for outer joins, I really wouldn't worry about getting them perfect. I've been working with databases for several years and still can never remember which side the damn thing goes on. It's the principle you need to master - ie you want everything from one side regardless of if there is a link or not on the other - in a customer / sales example, you would see all customers whether they have sales or not, unlike an inner join which would only show you customers who have records in the sales table.
posted by jontyjago at 2:46 PM on July 12, 2005

W3Schools is a good reference site, with the ability to try SQL statements to see what results they return.

Unfortunately, I can't help with the Query Analyzer as I've never used it (and don't really even know what it is).

(on preview, damn! jontyjago beat me to it :)
posted by geeky at 2:50 PM on July 12, 2005

I found the best way to learn MS SQL2000 queries, etc was to pickup one of the certification prep books for the MCDBA certification. They're a pretty good overview with decent detail on all things SQL'y.
posted by blue_beetle at 2:58 PM on July 12, 2005

The Wrox book is a lot less intimidating than the T-SQL reference for the mid-level user. Only a single author, so the learning is consistent, and he's a good teacher.

The Guru's Guide will give you a few extra tricks that Wrox doesn't.

Query Analyzer is where it's at. Learn it, use it, make it your friend.
posted by matildaben at 2:59 PM on July 12, 2005

Doing will teach you as much as reading, so do both. I never got the hang of joins (I still suspect there's still a whole arcane level of joining I don't even know about yet) until I screwed up a lot of them.

I flip back and forth between Query Analyzer for some things when I want to save my queries on the fly, and the SQL pane in Enterprise Manager, which I find comfy to work in (even though I don't ever bother with the Grid or Diagram panes), particularly when I don't quite know what I'm looking for yet.
posted by Lyn Never at 3:27 PM on July 12, 2005

Thankfully, the upcoming MSSQL 2005 (aka "Yukon") joins the Enterprise Manager and Query Analyzer together. One of the biggest obstacles I faced early on was trying to figure out what tool was good for specific SQL functions.

Anyway, as a lot of people have already mentioned, use Query Analyzer. It will force you to learn the actual code (and it's far more powerful in certain areas than Enterprise Manager). One tool I didn't see mentioned is SQL Profiler. Just leave it running in the background to see how your queries are performing. I went a few years developing with MSSQL before utilizing this application but having since started, I can't believe I ever got by without it.
posted by purephase at 3:56 PM on July 12, 2005

Playing around with the Northwind database will help you more than reading a book will.

No. I started off with Database Design for Mere Mortal and SQL for Mere Mortals and those helped me a lot more than that awful database. MySQL and MS SQL are different beasts, but you can learn in either. I started out in MySQL/ PHP and still get teased mercilessly for my sloppy joins (using clauses in the WHERE statement). When I wanted to learn to use Query Analyzer (which is a nice tool), I downloaded the Access version of Lahman's baseball database and imported it into SQL Server. Because it's pretty well normalized, I had plenty to do, writing stored procedures for stats like OBP and SLG.
posted by yerfatma at 4:50 PM on July 12, 2005

From a general RDBMS development standpoint, please allow me to offer a little bit of advice:

Before you implement any stored proc or query, say to yourself "there is probably a more simple way to get the data I need", and find it.

The biggest problem people seem to have (myself included) is jumping through lots of hoops to get the 'right data'. Usually it's not as hard or complex as most developers make it.
posted by icey at 1:30 AM on July 13, 2005

And when you're done with the basics and want to take your SQL to the next level, get hold of SQL for smarties.
posted by Arthur Dent at 7:27 AM on July 13, 2005

One of the best descriptions of INNER and OUTER joins I ever read was in Robert Vieira's Professional SQL Server 2000 Programming, specifically Chapter 5.

A couple of highlights:

1) INNER Joins are exclusive by nature
2) OUTER Joins are inclusive by nature
3) With OUTER Joins, the LEFT and the RIGHT keywords are critical to getting the information you want

SELECT (select list)
FROM (the table you want to be the "LEFT" table)
(LEFT|RIGHT) [OUTER] JOIN (table you want to be the "RIGHT" table)
ON (join condition)

A LEFT OUTER JOIN includes all the information from the table on the left, and a RIGHT OUTER JOIN includes all information from the table on the right.
posted by smcniven at 9:22 AM on July 13, 2005