From Oracle Expert to SQL Server Newbie - First Impressions

Oops, I've done it again - I left you readers hanging when I published the first part of two on generating code for your lookup/reference/code tables.  I promise to get back to it.

But in the meantime:
I have a new job.  Yeah, it was pretty sudden - finished my old job at the end of the year, and jumped directly into the new one at the beginning of the new year.  And the biggest shock is that this old Oracle DBA and Database Architect is going to be designing a database to be implemented in Microsoft SQL Server.  No, I didn't lie about my credentials, but I've worked with this boss before, and he said, "Oh, you'll do fine.  You're a smart guy.  And SQL is SQL, right? RDBMS is RDBMS, right?"

So, here I am, the newbie, posing as an expert.  I've been learning all I can about SQL Server, and trying things out, watching tutorials (Microsoft has some really good video lessons, designed especially for the Oracle DBA transitioning.)  Here are some first impressions:

• SQL is SQL.  Yes, there are differences, but the basics of the SELECT, INSERT, UPDATE, DELETE, and most of the DDL commands too, are pretty close.
• T-SQL is NOT PL/SQL.  Think of Transact-SQL as more like a SQL*Plus script with some flow control statements like IF/ELSE.  Some of it is nice, like no difference between a substitution variable and a language variable.  I think (not sure) that a T-SQL variable does act as a bind variable where PL/SQL variables would, but you can do things that PL/SQL would have to put inside an EXECUTE IMMEDIATE.  Cool.
• I miss PL/SQL packages.
• All triggers are statement level triggers.  This means that you write triggers very differently. Hard to get used to.  That doesn't mean you can't do row-level stuff - instead of getting a pseudo-record with the contents of the pre-DML row (the :OLD record) and one with the contents of the post-DML row (the :NEW record) you get pseudo-tables containing the old and new rows.  I don't know for sure, but I think this means that you don't deal with mutating table problems.
• The real kicker is that most triggers are AFTER statement triggers. If you want to do BEFORE stuff, you write an INSTEAD OF trigger.  In Oracle, INSTEAD OF is just for views.  Just like Oracle, the INSTEAD OF trigger needs to do the DML on the underlying table(s).
• There are two SQL Developer-like GUI tools - the SQL Server Management Studio, and SQL Server Data Tools for Visual Studio.  These are nice, but I wish they had a good SQL formatting tool.
• SQL Developer CAN talk to a SQL Server database, which is nice. But it doesn't really understand T-SQL, so it can have problems with it.  I did use its formatting tool on some SELECTs, however, and it worked pretty well.  Also, if you export a table as INSERTs - it writes them as Oracle INSERTs - doesn't work for dates because SQL Server doesn't have TO_DATE.
• Oracle has nothing like the SQL Server Integration Server - this is nice but TOTALLY new to me.
• SQL Server Reporting Server and its report building tool are MUCH better than SQL Reports from Oracle.  But then, I have always hated Oracle's reporting tool.