MS SQL Server - A Plethora Of Limitations...

Why is it, Microsoft manage to write operating systems and office
applications with every bell and whistle facility known to man. Yet,
even after years and years of development they still cannot produce a
decent version of MS SQL Server, one without a plethora of flaws and
limitations? Personally I'd sack the MS SQL Server Chief Architect,
start addressing some fundementals and do what MS do best - copy the
best functionality of their competitors.

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 :

1. Can't use domains in Multi-statement table-valued function table
definition

2. No concept of table.column.datatype MYTABLE.MYCOLUMN%TYPE for
function / proc parameters, variables. Schema evolution will require
changes to dep functions / procs.

3. Cannot call non extended user defined procedure from within stored
proc - prevents code re-use

4. No create or replace for functions / procs. Changes to procs
require either a change from create to alter, or drop then create

5. Functions must return result on final statement - requires
excessive use of local variables. Why!!!???

6. No user specifiable date format routines - must use convert with
limited options

7. No treewalk mechanism, not easy to traverse hierarchical structure

8. Calling procedures with out parameters require user to also
specifiy which are out params

9. Stored Proc recursion limitations to only 32 nested levels, rather
than monitoring by stack size.

10. No on delete set null referential integrity, this is fundemental
in relational databases, has to be done by trigger. Yuk.

11. No partitioning on db tables

12. Cannot raiseerror in stored functions. No way to inform user of
incorrect parameters etc.

13. No facility for sub - procs / funcs - for localised code
normalisation.

14. No function overloading

15. No concept of grouping similar procs / functions into a collective
package - can mean 1000s of procs rather than a handful of packages.

16. Non standard func / proc calling - proc non parenthesised but,
function defined and called with parenthesis

17. Limited column name length in table variables / Multi-statement
table-valued function table definitions

18. No intersect, minus operators to complement union and union all

19. No exception handling - need I say more about this fundemental
requirement?

20. No handling for select statements using proc parameters passed in
as null e.g.
create procedure sp_test.......
......

select @MyID = ID
from mytable
where parent_id = @parent_id.

If @parent_id is null, select will not return anything even if values
for null parent_id exist.

21 No boolean type for use in stored procs / funcs. SQL standards do
allow booleans (null, FALSE, TRUE)

22 No repeat until - (post predicate loops), while loops can perform
this but one extra check often has to be performed

23 No equivalent to cursor or table %ROWTYPE, when fetching from a
many column cursor, all bind variables have to be declared
individually

24. Declarative Delete cascade in SQL Server is at best rubbish - e.g.
No table can appear more than once in the list of all cascading
referential actions that result from the DELETE or UPDATE. Almost all
other RDBMS I've tried seems to manage it.

Create Table ForKey
(
MyID int not null identity primary key,
MyText varchar(30)
)
go

Create Table Refs
(
SomeID int not null primary key,
RefID1 int not null foreign key references ForKey(MyID) on delete
cascade,
RefID2 int not null foreign key references ForKey(MyID) on delete
cascade
)

Server: Msg 1785, Level 16, State 1, Line 2
Introducing FOREIGN KEY constraint 'FK__Refs__RefID2__1BFD2C07' on
table 'Refs' may cause cycles or multiple
cascade paths. Specify ON DELETE NO ACTION or ON UPDATE NO ACTION, or
modify other FOREIGN KEY constraints.
Server: Msg 1750, Level 16, State 1, Line 2
Could not create constraint. See previous errors.

25. Domains cannot be altered without dropping all dependencies
(stored procs / funcs) even if type isn't changed but say scale and
precision- this is ridiculous. What is the point of domains?

26. No "for loop", while loops can perform this, but resultant code is
not as clear or tidy

27. Can't use CURDATE() inside stored procs / funcs. How crap is
that?

Don't try and make excuses for MS as I have hundreds, anyone else have
any?

Jeager