MS SQL Server - a plethora of limitations...

Collapse
This topic is closed.
X
X
 
  • Time
  • Show
Clear All
new posts
  • Jeager

    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.da tatype MYTABLE.MYCOLUM N%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__RefI D2__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
  • David Portas

    #2
    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.da tatype MYTABLE.MYCOLUM N%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_TIMESTA MP, 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
    --


    Comment

    • David Portas

      #3
      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.da tatype MYTABLE.MYCOLUM N%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_TIMESTA MP, 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
      --


      Comment

      • Erland Sommarskog

        #4
        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.da tatype MYTABLE.MYCOLUM N%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@sommarsk og.se

        Books Online for SQL Server SP3 at
        Get the flexibility you need to use integrated solutions, apps, and innovations in technology with your data, wherever it lives—in the cloud, on-premises, or at the edge.

        Comment

        • Fred BROUARD - SQLpro

          #5
          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_SCH EMA 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.da tatype MYTABLE.MYCOLUM N%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__RefI D2__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_TIMESTA MP AS DT

          CREATE FUNCTION dbo.FN_GETDATET IME
          RETURNS DATETIME
          BEGIN
          RETURN (SELECT DT FROM GIVE_ME_CURRENT _DATETIME)
          END

          By the way, CURRENT_TIMESTA MP 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 *************** **********

          Comment

          • Erland Sommarskog

            #6
            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@sommarsk og.se

            Books Online for SQL Server SP3 at
            Get the flexibility you need to use integrated solutions, apps, and innovations in technology with your data, wherever it lives—in the cloud, on-premises, or at the edge.

            Comment

            • Serge Rielau

              #7
              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

              Comment

              • -P-

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

                "Fred BROUARD - SQLpro" <brouardf@clu b-internet.fr> wrote in message
                news:40c8d44e$0 $308$7a628cd7@n ews.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_SCH EMA 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_SCH EMA. I believe
                that's planned for their next release - v10.0.

                --
                Paul Horan
                Sr. Architect
                VCI Springfield, Mass




                Comment

                • William Cleveland

                  #9
                  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

                  Comment

                  • William Cleveland

                    #10
                    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.da tatype MYTABLE.MYCOLUM N%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

                    Comment

                    • Dave Sisk

                      #11
                      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_SCH EMA 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]

                      Comment

                      • Ross Presser

                        #12
                        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

                        Comment

                        • Erland Sommarskog

                          #13
                          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@sommarsk og.se

                          Books Online for SQL Server SP3 at
                          Get the flexibility you need to use integrated solutions, apps, and innovations in technology with your data, wherever it lives—in the cloud, on-premises, or at the edge.

                          Comment

                          • Anith Sen

                            #14
                            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


                            Comment

                            Working...