I'm currently having to get 'into' SQL server at work. I come from an Oracle background, but hey, how different can they be, right? Well... you'd be surprised.
One of the nice things about Oracle is PL/SQL. In my last job I wrote a lot of PL/SQL... it's easy, quick to do, well documented, and allows good software engineering. SQL Server uses something called 't-SQL' and compared to PL/SQL, it's appalling. The best way of demonstrating this is with an example. In stored procedures I can do this in PL/SQL:
errorCode OUT NUMBER,
errorText OUT VARCHAR2,
someItemID IN ITEM.ITEMID%TYPE,
someNumber IN ITEM.ITEMNUMBER%TYPE,
somethingElse IN OUT NUMBER,
result OUT RESULTTABLE.RESULT%TYPE) IS
This can be part of a package defined as two files - a spec and a body. All good software engineering.
With MS SQL Server I simply can't so this. As far as I know, there are no packages with specs and bodies, for one thing. There are weird, implicit returns for selects, variables defined within the procedure, and other strange and wacky stuff that really works against every good software engineering principle. Also, a roughly equivalent header might look like this:
CREATE PROCEDURE [dbo].[MyProcedure]
@errorCode INT OUT,
@errorText VARCHAR(1024) OUT,
@somethingElse INT OUT,
@result INT OUT AS
The biggest problem is that you can't get the type from the columns of a table, so if the table changed you have to change all of your code. This is insane! Large applications may have thousands of stored procedures, all of which would have to be changed. The lack of packages (although as I say, I might be wrong there) mean that you can't logically group procedures either, and you lose some context that you get in Oracle just from the package name.
As I mentioned, the way stored procedures seem to work in SQL Server is by implicit returns. For example, say we have this line:
declare @intReturnCode int
We can then add a 'return @intReturnCode' later and return a value not even defined in the header. Worse, you can do something like this:
set @someValue = (select fieldValue from someTable where name = 'this')
So looking at the parameters you'd have no idea that you were going to get 'someValue' back, or what type it is. Madness, absolute madness.
I must admit I was wary of SQL Server, but the more I look at it the more I'm amazed that anyone actually uses it. I mean, they only added exception handling in 2005! It's like some sort of toy database, but with a fairly hefty price tag.
Anyway, feel free to correct me. In the meantime I will continue to think poorly of it..