ThatJeffSmith

Teaching Toad or Teaching SQL?

So for the past two days I’ve been taking a group of about 15 people through the paces of Toad and working with SQL Server. These folks are coming from an all Oracle environment to a completely SQL Server one.

Tangent: Yes, they are actually migrating an entire application over. They are in year 3 of this process, and I’m sure the money they are saving on infrastructure and licensing costs more than makes up for the migration cost. Yes, that is sarcasm.

Anyways, about 3 of the people in the class had actually written SQL statements before, and the rest were just used to being able to do whatever Oracle Discoverer had allowed. They were not happy with it, hence their process to evaluate reporting and querying tools, and their decision to start with Toad for Data Analysts.

I knew I was in for a good time when I saw that their experience level with SQL was so low. The prepared materials for the training I had been set to deliver was totally inadequate for what they needed. Trying to teach someone how to use a Query Builder to write a Group By clause and use Aggregate functions who does not understand Set Theory is like trying to teach someone how to drive a stick shift that’s never been in a car before.

To make my challenge even more fun, I personally am not used to teaching the basics of SQL and set theory. I’m used to teaching someone how to use a specific set of tools to get the most out of SQL. Oh, and just another wrinkle, my 10 years of experience with RDBMS is heavy on Oracle and light on SQL Server. I was however extremely confident that I could have a big impact on these people’s professional lives.

Although they had paid for a course on Toad, I wanted to teach them Toad and some SQL basics as well. I’m not sure how successful I was, but I do think the folks came out of the class with a better appreciation of databases in general.

I also left the class with a few new bits of SQL know-how. I’ve always found this to be true, when teaching a subject, you are always bound to learn at least as much as your students will.

Oracle vs SQL Server ‘Quirks’

  • SYSDATE in Oracle is GETDATE in SQL Server. To reference in a query however you need to 1 – use proper function () syntax in your select and 2 – forget all about the need for a dummy DUAL
    table, so no FROM is necessary!
  • To concatenate in SQL, use + versus || in Oracle (thanks to @OracleNerd for reminding me of the ANSI SQL concat() function that works in both platforms
  • Schemas are nice for organizing objects in SQL Server databases, but they make querying a pain. If my object name isn’t overloaded for a database, why are you making me qualify my objects with the schema name? And to make matters more confusing, if you leave your objects in the default system schema ‘DBO’, then you don’t need to qualify them. A schema is a schema, right?
  • The SELECT * argument is a hard one to win when you’re outnumbered, whether it’s SQL Server or Oracle. Especially when your data model is in -1 Normal Form with lookup fields added to all child tables to make querying ‘easier’ for the end users.
  • Re-writing your Oracle Outer Join (+) syntax queries to SQL Server ANSI ones is a real pain in the butt. Thankfully Toad for Oracle has a feature that does this for you auto-magically!
  • I want to give a special shout-out to the Montgomery Regional Airport for having free WiFi. It’s made this early morning much more enjoyable and productive.