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

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

Comments inline.
[color=blue]
> 1. Can't use domains in Multi-statement table-valued function table
> definition[/color]

User-defined datatypes? That may be addressed in Yukon which has UDTs. (I
know, "jam tomorrow...")
[color=blue]
> 2. No concept of table.column.datatype MYTABLE.MYCOLUMN%TYPE for
> function / proc parameters, variables. Schema evolution will require
> changes to dep functions / procs.[/color]

True. Although the changes addressed by Oracle's column and row type
functions are surely a fairly small subset of the code maintenance required
when the schema changes.
[color=blue]
> 3. Cannot call non extended user defined procedure from within stored
> proc - prevents code re-use[/color]

You *can* do that. You can't call SPs from within a UDF and there are good
reasons why not (determinism).
[color=blue]
> 4. No create or replace for functions / procs. Changes to procs
> require either a change from create to alter, or drop then create[/color]

That would indeed be a nice feature to have.
[color=blue]
> 5. Functions must return result on final statement - requires
> excessive use of local variables. Why!!!???[/color]

Not sure what this means.
[color=blue]
> 6. No user specifiable date format routines - must use convert with
> limited options[/color]

You can code your own but converting date strings is really an ETL
function - use DTS or some other tool.
[color=blue]
> 7. No treewalk mechanism, not easy to traverse hierarchical structure[/color]

That's not quite correct. There isn't a non-iterative way to expand an
*adjacency list* hierarchy. Other hierarchy models make this easy to do.
CTEs in Yukon will address the limitation relevant to adjacency hierarchies.
[color=blue]
> 8. Calling procedures with out parameters require user to also
> specifiy which are out params[/color]

Why is that a problem?
[color=blue]
> 10. No on delete set null referential integrity, this is fundemental
> in relational databases, has to be done by trigger. Yuk.[/color]

I agree that's inconvenient.
[color=blue]
> 11. No partitioning on db tables[/color]

Partitioned views are the SQLServer alternative.
[color=blue]
> 12. Cannot raiseerror in stored functions. No way to inform user of
> incorrect parameters etc.[/color]

True, that's a little annoying.
[color=blue]
> 9. Stored Proc recursion limitations to only 32 nested levels, rather
> than monitoring by stack size.
>
> 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[/color]
[color=blue]
> 26. No "for loop", while loops can perform this, but resultant code is
> not as clear or tidy[/color]

Transact SQL isn't intended to be a fully featured programming language.
..Net is Microsoft's development platform. Keep basic data manipulation in
the database, more complex logic in the middle tier.
[color=blue]
> 17. Limited column name length in table variables / Multi-statement
> table-valued function table definitions[/color]

Do you know of a database or language that allows identifiers of *un*limited
length? The maximum length is 128 characters. Surely enough for most people!
[color=blue]
> 18. No intersect, minus operators to complement union and union all[/color]

True. Although this is trivial to code yourself.
[color=blue]
> 19. No exception handling - need I say more about this fundemental
> requirement?[/color]

Error handling is improved in Yukon but yes it's a pain.
[color=blue]
> 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.[/color]

That's just ANSI Standard handling of NULLs. Use IS NULL.
[color=blue]
> 21 No boolean type for use in stored procs / funcs. SQL standards do
> allow booleans (null, FALSE, TRUE)[/color]

No, but you can return "T" or "F" or something else instead. Why is that a
limitation? It's still just a single scalar value.
[color=blue]
> 22 No repeat until - (post predicate loops), while loops can perform
> this but one extra check often has to be performed[/color]

Yes.
[color=blue]
> 23 No equivalent to cursor or table %ROWTYPE, when fetching from a
> many column cursor, all bind variables have to be declared
> individually[/color]

How often do you use cursors? I don't think I've ever written one except for
some administrative processes.
[color=blue]
> 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[/color]

This is a significant limitation, yes.
[color=blue]
> 25. ... What is the point of domains?[/color]

You said it. Personally, I never use user-defined datatypes.
[color=blue]
> 27. Can't use CURDATE() inside stored procs / funcs. How crap is
> that?[/color]

You can use CURRENT_TIMESTAMP, GETDATE() in procs but not in functions. See
my answer to 3.
[color=blue]
> Don't try and make excuses for MS as I have hundreds, anyone else have
> any?[/color]

Lots. But if it bothers you why not use another product?

--
David Portas
SQL Server MVP
--

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

Comments inline.
[color=blue]
> 1. Can't use domains in Multi-statement table-valued function table
> definition[/color]

User-defined datatypes? That may be addressed in Yukon which has UDTs. (I
know, "jam tomorrow...")
[color=blue]
> 2. No concept of table.column.datatype MYTABLE.MYCOLUMN%TYPE for
> function / proc parameters, variables. Schema evolution will require
> changes to dep functions / procs.[/color]

True. Although the changes addressed by Oracle's column and row type
functions are surely a fairly small subset of the code maintenance required
when the schema changes.
[color=blue]
> 3. Cannot call non extended user defined procedure from within stored
> proc - prevents code re-use[/color]

You *can* do that. You can't call SPs from within a UDF and there are good
reasons why not (determinism).
[color=blue]
> 4. No create or replace for functions / procs. Changes to procs
> require either a change from create to alter, or drop then create[/color]

That would indeed be a nice feature to have.
[color=blue]
> 5. Functions must return result on final statement - requires
> excessive use of local variables. Why!!!???[/color]

Not sure what this means.
[color=blue]
> 6. No user specifiable date format routines - must use convert with
> limited options[/color]

You can code your own but converting date strings is really an ETL
function - use DTS or some other tool.
[color=blue]
> 7. No treewalk mechanism, not easy to traverse hierarchical structure[/color]

That's not quite correct. There isn't a non-iterative way to expand an
*adjacency list* hierarchy. Other hierarchy models make this easy to do.
CTEs in Yukon will address the limitation relevant to adjacency hierarchies.
[color=blue]
> 8. Calling procedures with out parameters require user to also
> specifiy which are out params[/color]

Why is that a problem?
[color=blue]
> 10. No on delete set null referential integrity, this is fundemental
> in relational databases, has to be done by trigger. Yuk.[/color]

I agree that's inconvenient.
[color=blue]
> 11. No partitioning on db tables[/color]

Partitioned views are the SQLServer alternative.
[color=blue]
> 12. Cannot raiseerror in stored functions. No way to inform user of
> incorrect parameters etc.[/color]

True, that's a little annoying.
[color=blue]
> 9. Stored Proc recursion limitations to only 32 nested levels, rather
> than monitoring by stack size.
>
> 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[/color]
[color=blue]
> 26. No "for loop", while loops can perform this, but resultant code is
> not as clear or tidy[/color]

Transact SQL isn't intended to be a fully featured programming language.
..Net is Microsoft's development platform. Keep basic data manipulation in
the database, more complex logic in the middle tier.
[color=blue]
> 17. Limited column name length in table variables / Multi-statement
> table-valued function table definitions[/color]

Do you know of a database or language that allows identifiers of *un*limited
length? The maximum length is 128 characters. Surely enough for most people!
[color=blue]
> 18. No intersect, minus operators to complement union and union all[/color]

True. Although this is trivial to code yourself.
[color=blue]
> 19. No exception handling - need I say more about this fundemental
> requirement?[/color]

Error handling is improved in Yukon but yes it's a pain.
[color=blue]
> 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.[/color]

That's just ANSI Standard handling of NULLs. Use IS NULL.
[color=blue]
> 21 No boolean type for use in stored procs / funcs. SQL standards do
> allow booleans (null, FALSE, TRUE)[/color]

No, but you can return "T" or "F" or something else instead. Why is that a
limitation? It's still just a single scalar value.
[color=blue]
> 22 No repeat until - (post predicate loops), while loops can perform
> this but one extra check often has to be performed[/color]

Yes.
[color=blue]
> 23 No equivalent to cursor or table %ROWTYPE, when fetching from a
> many column cursor, all bind variables have to be declared
> individually[/color]

How often do you use cursors? I don't think I've ever written one except for
some administrative processes.
[color=blue]
> 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[/color]

This is a significant limitation, yes.
[color=blue]
> 25. ... What is the point of domains?[/color]

You said it. Personally, I never use user-defined datatypes.
[color=blue]
> 27. Can't use CURDATE() inside stored procs / funcs. How crap is
> that?[/color]

You can use CURRENT_TIMESTAMP, GETDATE() in procs but not in functions. See
my answer to 3.
[color=blue]
> Don't try and make excuses for MS as I have hundreds, anyone else have
> any?[/color]

Lots. But if it bothers you why not use another product?

--
David Portas
SQL Server MVP
--

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

Jeager (Jeager@nowhere.com) writes:[color=blue]
> 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 :[/color]

Some of the issues you raise are certainly weaknesses in SQL Server of
some significance. Some of them are addressed in the next version of
SQL Server, but not all.

However, other issues you raise, do in no way warrant your yelling
and shouting. Maybe you have a favourite DBMS which supports all this,
but I doubt. And that DBMS is such case likely to be without features
that SQL Server has.
[color=blue]
> 1. Can't use domains in Multi-statement table-valued function table
> definition[/color]

You can't use domains anywhere, because SQL Server does not have the
concept. But if you mean user-defined datatypes, this is somewhat stupid
limitation.
[color=blue]
> 2. No concept of table.column.datatype MYTABLE.MYCOLUMN%TYPE for
> function / proc parameters, variables. Schema evolution will require
> changes to dep functions / procs.[/color]

Would be nice, yes, but a serious restriction? No.
[color=blue]
> 3. Cannot call non extended user defined procedure from within stored
> proc - prevents code re-use[/color]

Wrong. But you are probably thinking of calling an SP from a function.
It appears that you have not understood the concept of a function. When
MS introduced functions, they took a very conservative approach and
decided that a function may not change the state of the database. Of
course, they could have been more lax, but that would also have reduced
where you can use a function. Say that you have:

SELECT ...
FROM tbl
JOIN some_fuction() ON ..

Now, if some_function changes data in tbl, you could different results
depending in which order the optimizer accesses the table. Do you think
that would be a good thing?
[color=blue]
> 4. No create or replace for functions / procs. Changes to procs
> require either a change from create to alter, or drop then create[/color]

No big deal. See http://www.abaris.se/abaperls which takes care of this
problem entirely. (And which also address the first issue on the list.)
[color=blue]
> 5. Functions must return result on final statement - requires
> excessive use of local variables. Why!!!???[/color]

Come on, you call this a serious issue.
[color=blue]
> 6. No user specifiable date format routines - must use convert with
> limited options[/color]

Then again, it's better to return as date to the client. The server
cannot now which the preferences of the end user are.
[color=blue]
> 8. Calling procedures with out parameters require user to also
> specifiy which are out params[/color]

Funky. Part of the Sybase legacy.
[color=blue]
> 9. Stored Proc recursion limitations to only 32 nested levels, rather
> than monitoring by stack size.[/color]

Probably a good thing. The only times when I have dug this deep was
then I shouldn't have.
[color=blue]
> 10. No on delete set null referential integrity, this is fundemental
> in relational databases, has to be done by trigger. Yuk.[/color]

Fundamental?
[color=blue]
> 11. No partitioning on db tables[/color]

You can use partitioned views. Partition tables are in Yukon.
[color=blue]
> 12. Cannot raiseerror in stored functions. No way to inform user of
> incorrect parameters etc.[/color]

Given that SQL Server does not handle errors raised in functions well
at all, this is probably a good thing. :-)
[color=blue]
> 14. No function overloading[/color]

And you are calling that a serious restriction? Well, in Yukon you will
be able to program in .Net languages, so there you be able to overload
to your hears content.
[color=blue]
> 15. No concept of grouping similar procs / functions into a collective
> package - can mean 1000s of procs rather than a handful of packages.[/color]

Again, that is nothing that warrants the tone of your post.
[color=blue]
> 16. Non standard func / proc calling - proc non parenthesised but,
> function defined and called with parenthesis[/color]

Irritating, but it's not the end of the world.
[color=blue]
> 17. Limited column name length in table variables / Multi-statement
> table-valued function table definitions[/color]

Isn't 128 chars enough for you?
[color=blue]
> 18. No intersect, minus operators to complement union and union all[/color]

Yes, this is missing, and no signs of being added either.
[color=blue]
> 19. No exception handling - need I say more about this fundemental
> requirement?[/color]

Error handling in SQL2000 is really poor. Yukon changes the scene
radically.
[color=blue]
> 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.[/color]

Wrong. If you want this behaviour, you can use SET ANSI_NULLS OFF. But
I definitely to not encourage it.
[color=blue]
> 21 No boolean type for use in stored procs / funcs. SQL standards do
> allow booleans (null, FALSE, TRUE)[/color]

There is bit. Works for me.
[color=blue]
> 22 No repeat until - (post predicate loops), while loops can perform
> this but one extra check often has to be performed[/color]

If you want a language with good control structure, try a traditional
one. You will even be able to use them in SQL Server in Yukon.
[color=blue]
> 23 No equivalent to cursor or table %ROWTYPE, when fetching from a
> many column cursor, all bind variables have to be declared
> individually[/color]

Big deal. Cursors are best avoided anyway.
[color=blue]
> 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?[/color]

To alter them each and every day? I seriously doubt. Actually, this
is the sort of feature I don't know if I want to see. You have this
user-defined data type (still no domains in SQL Server), which is
decimal(8,2), and which is used in 15 big tables. Now you decide
to change the type decimal(14,2). Since you now make the type bigger,
SQL Server has to rebuild all tables. In one single transaction.
[color=blue]
> 27. Can't use CURDATE() inside stored procs / funcs. How crap is
> that?[/color]

Probably you mean getdate(), which you can use in stored procedures.
You cannot use it functions, because when a function is called repeatedly
in a query, the result should be the save for any given input parameter
at time during the query.


--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

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

Well guy I am a french SQL expert and recently became a MVP...

I now pretty well ISO standard SQL Language.

The state of art in the normative process is actually SQL:1992.
SQL:1999 wich has got relational object feature is not yet a ISO standard...

Let me say that, SQL Server is the RDBMS closest to the standard !

Do you know RDBMS wich allows database object names to be long to 128 chars ?
Do you know RDBMS wich use INFORMATION_SCHEMA views to meta data the data base ?
Do you know RDBMS wich use COALESCE and NULLIF function ?
Do you know RDBMS wich have the two structured CASE ?
Do you know RDBMS wich allow you to subqueries in the FROM clause of a SELECT ?
....

The thing you want will be a mix of Java, C, Excel, Ada but not a RDBMS !

Jeager a écrit:[color=blue]
> 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[/color]

DOMAINs can be simulated by a combination of rules and UserType.
[color=blue]
>
> 2. No concept of table.column.datatype MYTABLE.MYCOLUMN%TYPE for
> function / proc parameters, variables. Schema evolution will require
> changes to dep functions / procs.[/color]

Not OK for that ! We are not in a Ada ou C++ dev !
[color=blue]
>
> 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[/color]

Don't you use a tool ? Don't you use the script auto create of Enterprise Manager to get the SQL equivalent code ???
[color=blue]
>
> 5. Functions must return result on final statement - requires
> excessive use of local variables. Why!!!???[/color]

This does not appear to be so stupid ![color=blue]
>
> 6. No user specifiable date format routines - must use convert with
> limited options[/color]

Wrong... use SET DATEFORMAT.
[color=blue]
>
> 7. No treewalk mechanism, not easy to traverse hierarchical structure[/color]

Don't need. Every recursive process can be write without recursive code by use of a "pile" structure.
For instance, trees in SQL can be modelate by intervals.
Read me for that : http://sqlpro.developpez.com/Tree/SQL_tree.html
[color=blue]
>
> 8. Calling procedures with out parameters require user to also
> specifiy which are out params[/color]

Is it really a problem ?[color=blue]
>
> 9. Stored Proc recursion limitations to only 32 nested levels, rather
> than monitoring by stack size.[/color]

Recursion processes in RDBMS should never exists. See answer 7.
[color=blue]
>
> 10. No on delete set null referential integrity, this is fundemental
> in relational databases, has to be done by trigger. Yuk.[/color]

This is really a point !
[color=blue]
>
> 11. No partitioning on db table
>[/color]
Manually !
[color=blue]
> 12. Cannot raiseerror in stored functions. No way to inform user of
> incorrect parameters etc.[/color]

Yes this is the second point, the error manager of SQL Server must be improve ![color=blue]
>
> 13. No facility for sub - procs / funcs - for localised code
> normalisation.
>
> 14. No function overloading[/color]

SQL is not an OO language and must not be one ! The future of the standard does not goes in this way ![color=blue]
>
> 15. No concept of grouping similar procs / functions into a collective
> package - can mean 1000s of procs rather than a handful of packages.
>[/color]
Yes, but with a use of a 128 char name, you can group by prefix ! This is not the cas in Oracle wich cannot accept more
than 28 chars... And the real interest of package for this "poor" naming object database !
[color=blue]
> 16. Non standard func / proc calling - proc non parenthesised but,
> function defined and called with parenthesis[/color]

The weight of the past... Procs was developped before the standard came, functions, after the standard and close to the
standar ...[color=blue]
>
> 17. Limited column name length in table variables / Multi-statement
> table-valued function table definitions[/color]

128 characters is not enough for you ? This is the SQL:1992 standard !
[color=blue]
>
> 18. No intersect, minus operators to complement union and union all[/color]

Wich can be represented by two equivalents... But I agree, I would prefer they exists ![color=blue]
>
> 19. No exception handling - need I say more about this fundemental
> requirement?[/color]

"déjà dit"[color=blue]
>
> 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.[/color]

depend on the parameters fixed for normative NULL treatment
[color=blue]
>
> 21 No boolean type for use in stored procs / funcs. SQL standards do
> allow booleans (null, FALSE, TRUE)[/color]

Wrong, the type is BIT, wich allow 1, 0, NULL. SQL does not have any standard about boolean. This is planed in SQL:199
wich is not yet a ISO standard !
[color=blue]
>
> 22 No repeat until - (post predicate loops), while loops can perform
> this but one extra check often has to be performed[/color]

not really a problem WHILE is a general loop that can replace all.[color=blue]
>
> 23 No equivalent to cursor or table %ROWTYPE, when fetching from a
> many column cursor, all bind variables have to be declared
> individually[/color]

A lack, I agree
[color=blue]
>
> 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.[/color]

The use of CASCADED delete or update is always a performance problem. This limitation has a good intention... The use of
a trigger is better ![color=blue]
>
> 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[/color]

Redondant with 22[color=blue]
>
> 27. Can't use CURDATE() inside stored procs / funcs. How crap is
> that?
>[/color]
In Stored Proc Yes, In Function not. Because of DETERMINISTIC limitation.
You can use a view instaed :

CREATE VIEW GIVE_ME_CURRENT_DATETIME
AS
SELECT CURRENT_TIMESTAMP AS DT

CREATE FUNCTION dbo.FN_GETDATETIME
RETURNS DATETIME
BEGIN
RETURN (SELECT DT FROM GIVE_ME_CURRENT_DATETIME)
END

By the way, CURRENT_TIMESTAMP is the SQL:1992 standard of datetime function !
[color=blue]
>
> Don't try and make excuses for MS as I have hundreds, anyone else have
> any?[/color]

For me the most important lack is the deferability of constraint...
wich is a SQL:1992 standard

One important point, wich is not a SQL standard, but planned for SQL:1999 is that SQL Server does not allow BEFORE trigger.

And some point I will be enjoy to have is :
ROW VALUE CONSTRUCTOR
MATCH predicate
and some other standard stuff like EXTRACT, OVERLAPS and INTERVAL data type (time process)
[color=blue]
>
> Jeager[/color]

--
Frédéric BROUARD, MVP SQL Server. Expert SQL / spécialiste Delphi, web
Livre SQL - col. Référence : http://sqlpro.developpez.com/bookSQL.html
Le site du SQL, pour débutants et pros : http://sqlpro.developpez.com
************************ www.datasapiens.com *************************

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

Fred BROUARD - SQLpro (brouardf@club-internet.fr) writes:[color=blue]
> Let me say that, SQL Server is the RDBMS closest to the standard ![/color]

Hey, would you expect anyone who thinks that NULL should be equal to
NULL would care about standards?

(Sorry, could not resist.)


--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

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

Fred BROUARD - SQLpro wrote:[color=blue]
>
> Well guy I am a french SQL expert and recently became a MVP...
>
> I now pretty well ISO standard SQL Language.
>
> The state of art in the normative process is actually SQL:1992.
> SQL:1999 wich has got relational object feature is not yet a ISO
> standard...
> Let me say that, SQL Server is the RDBMS closest to the standard ![/color]
I like that French humour of yours :-)
SQL:1999 is outdated itself, btw.

Cheers
Serge
--
Serge Rielau
DB2 SQL Compiler Development
IBM Toronto Lab

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

"Fred BROUARD - SQLpro" <brouardf@club-internet.fr> wrote in message
news:40c8d44e$0$308$7a628cd7@news.club-internet.fr...[color=blue]
>
> Well guy I am a french SQL expert and recently became a MVP...
>
> I now pretty well ISO standard SQL Language.
>
> The state of art in the normative process is actually SQL:1992.
> SQL:1999 wich has got relational object feature is not yet a ISO standard...
>
> Let me say that, SQL Server is the RDBMS closest to the standard !
>
> Do you know RDBMS wich allows database object names to be long to 128 chars ?
> Do you know RDBMS wich use INFORMATION_SCHEMA views to meta data the data base ?
> Do you know RDBMS wich use COALESCE and NULLIF function ?
> Do you know RDBMS wich have the two structured CASE ?
> Do you know RDBMS wich allow you to subqueries in the FROM clause of a SELECT ?
> ...
>[/color]


Adaptive Server Anywhere from Sybase's iAnywhere group has all those features except INFORMATION_SCHEMA. I believe
that's planned for their next release - v10.0.

--
Paul Horan
Sr. Architect
VCI Springfield, Mass
www.vcisolutions.com

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

Erland Sommarskog wrote:
[color=blue]
> Fred BROUARD - SQLpro (brouardf@club-internet.fr) writes:
>[color=green]
>>Let me say that, SQL Server is the RDBMS closest to the standard ![/color]
>
>
> Hey, would you expect anyone who thinks that NULL should be equal to
> NULL would care about standards?
>
> (Sorry, could not resist.)
>[/color]

I think that NULL is used for three separate concepts, unknown,
absent and not applicable (would turn up in queries, due to outer
joins). Absent should be equal to absent, unknown should *not*
be equal to unknown, and I'm not really sure about not applicable.
To do all that, though, one kind of Null isn't enough. I blame
SQL for this, however, not MS SQL.

Bill

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

Jeager wrote:
[color=blue]
> 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.
>[/color]
[color=blue]
> 2. No concept of table.column.datatype MYTABLE.MYCOLUMN%TYPE for
> function / proc parameters, variables. Schema evolution will require
> changes to dep functions / procs.
>[/color]
Do you mean that you can't use variables for database objects (this
really bugs me)? If not, I have no idea what you're saying here.
[color=blue]
> 21 No boolean type for use in stored procs / funcs. SQL standards do
> allow booleans (null, FALSE, TRUE)
>[/color]
What about boolean types for use within queries? Personally, I'm sick
of writing "Case When Field1 = Field2 Then 1 Else 0 End As Equals". It
should just be "(Field1 = Field2) As Equals".

Bill

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

Just to set the record straight, since we're listing...:-P
[color=blue]
> Let me say that, SQL Server is the RDBMS closest to the standard !
>
> Do you know RDBMS wich allows database object names to be long to 128 chars ?[/color]
Yes, DB2/UDB does as well as SQLServer. Oracle needs to catch up
here.[color=blue]
> Do you know RDBMS wich use INFORMATION_SCHEMA views to meta data the data base ?[/color]
Yes, Oracle has more data dictionary views than you can shake a stick
at. There's a data dictionary view to find anything you can possibly
imagine, including access to memory structures. SQLServer and DB2/UDB
are both woefully lacking in this area.[color=blue]
> Do you know RDBMS wich use COALESCE and NULLIF function ?[/color]
All three.[color=blue]
> Do you know RDBMS wich have the two structured CASE ?[/color]
All three.[color=blue]
> Do you know RDBMS wich allow you to subqueries in the FROM clause of a SELECT ?[/color]
All three.[color=blue]
> ...[/color]

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

On Tue, 15 Jun 2004 01:55:32 GMT, William Cleveland wrote:
[color=blue]
> What about boolean types for use within queries? Personally, I'm sick
> of writing "Case When Field1 = Field2 Then 1 Else 0 End As Equals". It
> should just be "(Field1 = Field2) As Equals".[/color]

Use a UDF. Shouldn't add to overhead, adds clarity.

AreEqual(Field1,Field2) As Equals

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

Ross Presser (rpresser@imtek.com) writes:[color=blue]
> Use a UDF. Shouldn't add to overhead, adds clarity.
>
> AreEqual(Field1,Field2) As Equals[/color]

Would have to write one for each datatype. And, as always with scalar
UDFs, the performance cost may not be the worth the gain in clarity.



--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

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

>> I think that NULL is used for three separate concepts, unknown, absent
and not applicable <<

Why stop at just three? There are several other meanings assigned to NULLs
in SQL including indeterminate value, unsupplied value, non-existent value,
empty set...sadly, as you mentioned, using standard 3VL for all such missing
information handling is an obvious SQL kludge.

--
Anith