Re: MS SQL Server - a plethora of limitations...

Jeager_at_jeager.com
Date: 06/10/04 

Date: Thu, 10 Jun 2004 17:13:08 +0100

>>> Here's a few reasons why I feel MS SQL Server is still a load of
> crap, and will never be many developers RDBMS of choice <<
>
> Most of your objections are that MS-SQL is not Oracle. Well, Oracle
> sucks rocks -- those lying idiots cannot even get the basic data types
> correct -- and fails to follow standards even worse that SQL Server.
All of the major RDBMSs have proprietry extensions. Oracle 8i wasn't
great, but both Oracle 9i and 10g are documented to be more compliant than
MS S S.

>>> 1. Can't use domains in Multi-statement table-valued function table
> definition <<
>
> There is no such thing in Standard SQL. However, the lack of a CREATE
> DOMAIN statement is a problem.
Table valued functions and domains are in SQL/SPM, so should be valid
according to the standards.

> 2. No concept of table.column.datatype MYTABLE.MYCOLUMN%TYPE for
> function / proc parameters, variables. Schema evolution will require
> changes to dep functions / procs. <<
>
> There is no such syntax or concept in Standard SQL.
Perhaps not, but there should be. It means dependencies on column types
(procs / funcs params and vars etc.) need not be changed when DDL on
columns occurs. It is common sense, it means schema evolution is not
bogged down with checking 1000s of lines of server SPM code.

>>> 5. Functions must return result on final statement - requires
> excessive use of local variables. <<
>
> Wrong; look at the RETURN statement.
Wrong, try and write a stored function that returns a value from within a
condition after say it has found a terminating value, that has code after
it.

>>> 7. No treewalk mechanism, not easy to traverse hierarchical structure
> <<
>
> You mean like the non-relatiional, PROPRIETARY, Hidden cursor syntax
> that Oracle has? Here is the link on Amazon.com for my new book on
> "Trees & Hierarchies in SQL"
>
> http://www.amazon.com/exec/obidos/tg/detail/-/1558609202/qid=1080772873/
> sr=1-1/ref=sr_1_1/102-7683601-6345721?v=glance&s=books#product-details
>
> Only poor programmers violate the relation model to do hierarchies.

Absolute rubbish. Hierarchies are perfectly valid in relational models,
they are often just parent child relationships. e.g. How would you model
the ancestory of your family in a relational DB?

I'll give you another simple example :
CREATE TABLE Personnel
(emp CHAR(10) NOT NULL PRIMARY KEY,
boss CHAR(10) DEFAULT NULL REFERENCES Personnel(emp),
salary DECIMAL(6,2) NOT NULL DEFAULT 100.00)

SQL standards do define and allow recursive querying (exactly what a
treewalk is), a hierarchy would be returned by a recursive SQL select.

>>> 8. Calling procedures with OUT parameters require user to also
> specify which are OUT params <<
>
> Wow! Just like the SQL/PSM Standards, with a minor synrax change!
So MS SQL Server can break the rules but others can't?

>>> 9. Stored Proc recursion limitations to only 32 nested levels, rather
> than monitoring by stack size. <<
>
> So? Did you know that 98% of recursive procedures go five or fewer
> levels deep according to Burrough research with Algol 60? This is SQL
> and not LISP.
So what about the 2% where more levels are required? Having a static
recursive limit, regardless of the data being popped onto the stack, is
just a hack by MS because they couldn't be bothered to do it properly.

>>> 11. No partitioning on db tables <<
>
> Who cares? SQL Server is not for VLDB. That ought to be done for me by
> the engine; I don't want to have to do myself, like Oracle. I'd rather
> be in DB2 or Teradata when this is a serious issue. They do it better
> and without an extra DBA like Oracle.
Partitioning shouldn't be done automatically by the DB, as there can be
many decisions involved : You may want to partition across raid, disks,
computers, computer clusters; you may want partitions of different sizes,
you may have to weigh up issues relating to performance issues caused by
partitioning incorrectly, and costs of partitioning in both hardware and
maintenance. I believe Yukon will add partitioning, but will only make
partition recommendations to DBA much akin to the way Oracle does.

>>> 12. Cannot raiserror in stored functions. No way to inform user of
> incorrect parameters etc. <<
>
> Unh? RAISERROR is part of T-SQL. You have to do some work with it but
> it is a subset of the SQL/PSM specs.
Have you tried writing a stored function and calling RAISERROR? Try it,
you can't.

>>> 13. No facility for sub - procs / funcs - for localised code
> normalisation. <<
>
> T-SQL was not meant to be an application language. That stuf is a bitch
> to do in a simple one-pass compiler.
Poor excuse, sub procs have nothing to do with application writing, it is
about code normalisation and good programming practises.

>>> 16. Non standard func / proc calling - proc non parenthesised but,
> function defined and called with parenthesis <<
>
> Put in the parens -- this is arguing about local syntatic sugar. Even
> as rigid as I am, I understanding "portable" versus "standard"
My argument is that the syntax is not consistent.

>>> 17. Limited column name length in table variables / Multi-statement
> table-valued function table definitions <<
>
> They are up to standards. Why would anyone use anything near the
> Standard limits? Such code would be a nightmare to maintain and
> probably in violation of ISO-11179 conventions.
Why should standard tables column lengths differ from those allowed for
table variables / Multi-statement table-valued function table definitions?
A table is a table whether it is in physically stored in memory or
whatever.

>>> 21. No boolean type for use in stored procs / funcs. SQL standards
> do allow booleans (null, FALSE, TRUE) <<
>
> Not in SQL-92 and they make no sense in SQL-99 and SQL-2003; hopefully,
> they will be deprecated shortly, as was the BIT datatype. This screws
> up the whole 3VL system.
Boolean has been in the SQL and SQL/SPM standards for a while. Booleans do
make sense, in fact the SQL and SQL/SPM standards documents even include
examples of stored procs using locally declared booleans for process
control, and the SQL standards have a large section on booleans.

>>> 22 No repeat until - (post predicate loops), while loops can perform
> this but one extra check often has to be performed <<
>
> Big deal. First of all, good SQL programmers do not use loops. And
> again, remember that T-SQL is not an application development language.
Part of the SQL/PSM standards you keep harping on about. See "Iterated SQL
statements". Your "Good SQL programmers do not use loops" - sounds like
your opinion and far from being a fact.

>>> 23 No equivalent to cursor or table %ROWTYPE, when fetching from a
> many column cursor, all bind variables have to be declared individually
> <<
>
> Gee, they followed standards again! The bastards!
%ROWTYPE is so sensible it beggars belief why it isn't in the standards.
If I have a cursor with 60 columns I have to define 60 bind variables to
fetch into, rather than a single rowtype bind variable. Evidentally
standards don't always follow sensibilities.

>>> 26. No "for loop", while loops can perform this, but resultant code
> is not as clear or tidy <<
>
> Again, T-SQL is not an application development language. ASnd what kind
> of SQL programmer used -ugh!- loops? The FOR in Standard SQL is not a
> counting loop anyway.
I was talking about cursor for loops - which are in standards.

>>> 27. Can't use CURDATE() inside stored procs / funcs. <<
I meant getdate() - (I got mixed up with MySQL.) Obviously relating to
determinism.

> In spite of being more of an SQL fanatic than most people, I do give
> them credit for getting closer and closer to SQL-92 specs and much
> better than Oracle.
Having worked on many RDBMS platforms (DB2, MS SQL Server, Interbase,
MySQL, Oracle); I would be informed enough to say that MS SQL Server would
never be considered better than Oracle in any shape or form other than
cost.

Jeager