The ARRAY In SQL Server 2000

  • dmbaker

    SSCertifiable

    Points: 5044

    Eric Wilson (2/14/2008)


    Regarding the overall article's topic:

    The only possible reason to want an ARRAY type is because of assumptions of how the data will be *physically* organized. But we should have simply TABLES (including in-memory tables, table variables, etc.) and have some control over physical-level implementation concerns like whether it's -- under the hood -- sorted in a particular way. Those physical details do not impact the logical ways we interact with the table--which is in fact one of the points!

    I think you're missing the point. An array is a programming construct, not a relational one. I think it's pretty safe to say that all modern programming languages support arrays, it's not unreasonable to desire that T-SQL be brought into the 20th (sorry, I mean the 21st) century and offer support for this fundamental programming construct.

    Of course, it could be argued that we do have it now, with CLR integration in SQL Server (and also ability to pass relational structures between client and server, a.k.a. the DataSet). But that's a whole other can of worms.

  • dmbaker

    SSCertifiable

    Points: 5044

    Eric Wilson (2/14/2008)


    Regarding the overall article's topic:

    The only possible reason to want an ARRAY type is because of assumptions of how the data will be *physically* organized. But we should have simply TABLES (including in-memory tables, table variables, etc.) and have some control over physical-level implementation concerns like whether it's -- under the hood -- sorted in a particular way. Those physical details do not impact the logical ways we interact with the table--which is in fact one of the points!

    I think you're missing the point. An array is a programming construct, not a relational one. I think it's pretty safe to say that all modern programming languages support arrays, it's not unreasonable to desire that T-SQL be brought into the 20th (sorry, I mean the 21st) century and offer support for this fundamental programming construct.

    Of course, it could be argued that we do have it now, with CLR integration in SQL Server (and also ability to pass relational structures between client and server, a.k.a. the DataSet). But that's a whole other can of worms.

  • dmbaker

    SSCertifiable

    Points: 5044

    Eric Wilson (2/14/2008)


    Regarding the overall article's topic:

    The only possible reason to want an ARRAY type is because of assumptions of how the data will be *physically* organized. But we should have simply TABLES (including in-memory tables, table variables, etc.) and have some control over physical-level implementation concerns like whether it's -- under the hood -- sorted in a particular way. Those physical details do not impact the logical ways we interact with the table--which is in fact one of the points!

    I think you're missing the point. An array is a programming construct, not a relational one. I think it's pretty safe to say that all modern programming languages support arrays, it's not unreasonable to desire that T-SQL be brought into the 20th (sorry, I mean the 21st) century and offer support for this fundamental programming construct.

    Of course, it could be argued that we do have it now, with CLR integration in SQL Server (and also ability to pass relational structures between client and server, a.k.a. the DataSet). But that's a whole other can of worms.

  • dmbaker

    SSCertifiable

    Points: 5044

    Wow, sorry for the multiple posts, not sure how that happened.

  • Eric Wilson

    SSChasing Mays

    Points: 611

    dmbaker (2/18/2008)


    Eric Wilson (2/14/2008)


    Regarding the overall article's topic:

    The only possible reason to want an ARRAY type is because of assumptions of how the data will be *physically* organized. But we should have simply TABLES (including in-memory tables, table variables, etc.) and have some control over physical-level implementation concerns like whether it's -- under the hood -- sorted in a particular way. Those physical details do not impact the logical ways we interact with the table--which is in fact one of the points!

    I think you're missing the point. An array is a programming construct, not a relational one. I think it's pretty safe to say that all modern programming languages support arrays, it's not unreasonable to desire that T-SQL be brought into the 20th (sorry, I mean the 21st) century and offer support for this fundamental programming construct.

    Of course, it could be argued that we do have it now, with CLR integration in SQL Server (and also ability to pass relational structures between client and server, a.k.a. the DataSet). But that's a whole other can of worms.

    With due respect, I'm afraid you missed my point. One (major) goal of the Relational Model was to be as uncluttered as possible. To that end, relations (a.k.a. tables) provide all the necessary complexity we need. Arrays, or any other data structure, only add complexity without adding additional power! This leads to arbitrariness of solutions and additional confusion between the logical and physical layers. No good can come of it.

    Most programming languages do not have this "minimalist" goal. And a lot of the complexity I've seen over the years can be attributed to this: different programmers use arbitrarily different solutions because--well--they can. But it doesn't mean that's a good thing. More "options" or complexity does not mean "better."

    Including things into an otherwise cleanly constructed system, just because it's common elsewhere is, frankly, a really dumb reason to do it. Show me the extra power (semantically) that Arrays give me over tables, and I'll be sold. But I'm certain no one can because tables are more powerful (if implemented fully in the system--which admittedly is not the case in most systems today).

    Before asking for new features in an RDBMS, people really owe it to themselves to understand the goals of such a system in the first place. Asking for Arrays undeniably indicates ignorance of the relational model.

  • dmbaker

    SSCertifiable

    Points: 5044

    Eric Wilson (2/18/2008)


    With due respect, I'm afraid you missed my point. One (major) goal of the Relational Model was to be as uncluttered as possible. To that end, relations (a.k.a. tables) provide all the necessary complexity we need. Arrays, or any other data structure, only add complexity without adding additional power! This leads to arbitrariness of solutions and additional confusion between the logical and physical layers. No good can come of it.

    I'm not arguing that relations are more or less powerful than arrays, in fact I fully agree that arrays are a poor substitute for relations -- that's a no-brainer. I wasn't suggesting that they *are* a substitute for relations. I was suggesting that the array is an incredibly useful feature in modern programming languages that is wholly missing from T-SQL, and one that, as T-SQL *is now implemented*, would be quite useful to solve a number of problems that we've had to hack around for many years.

    As a programmer, I most certainly *do not* use a different solution simply "because I can". I look for what I believe to be the best solution to a given problem, and preferably look for a solution that someone else has already come up with. More "options" does not necessarily mean "more complexity", but more options just might give me the *option* to deliver a better solution. And delivering the "best" solution is my job (whatever the definition of "best" may be for the task at hand).

    Anyway, I really don't disagree with you. I think you nailed it when you said tables "...are more powerful (if fully implemented...)". If we were able to toss tables around in TSQL just like any other datatype, that would certainly go a long way.

  • Eric Wilson

    SSChasing Mays

    Points: 611

    dmbaker (2/18/2008)


    Eric Wilson (2/18/2008)


    With due respect, I'm afraid you missed my point. One (major) goal of the Relational Model was to be as uncluttered as possible. To that end, relations (a.k.a. tables) provide all the necessary complexity we need. Arrays, or any other data structure, only add complexity without adding additional power! This leads to arbitrariness of solutions and additional confusion between the logical and physical layers. No good can come of it.

    { snip }

    Anyway, I really don't disagree with you. I think you nailed it when you said tables "...are more powerful (if fully implemented...)". If we were able to toss tables around in TSQL just like any other datatype, that would certainly go a long way.

    Yes, seems we agree after all. My point was, as long as we (the DB developer community) are asking vendors for features, let's all please ask for more fully implemented table support that is rich and robust! That would give us far more power than Arrays and has the additional bonus of being completely consistent with the existing model of what a relational DB is to be.

  • Ed Sanford

    SSC Journeyman

    Points: 83

    FYI ... I tried creating a function to return a pre-structured table from xml text, and found that sql 2005 would not allow it to function, complaining that only extended stored procs could be called from within a function.

    Even though sp_xml_preparedocument is listed as an extended stored proc, it apparently is seen as not extended; so, the prepare and remove calls have do be done outside of the function call.

  • Ed Sanford

    SSC Journeyman

    Points: 83

    Just wanted to add that my main purpose for wanting to use this Array logic is to be able to pass selection lists to a query.

    Although my current method (when using ColdFusion anyway) is to pass list parameters ( they get turned in to long lists of bind parameters); but, that doesn't work well if I want to have the query in a stored procedure, or function call; and, besides, it would be nice to be able to logically pass a large list as one parameter, not a 100 or 1000 of them.

    Passing the list data as XML seems logical; although, it would be nice if there was a less bloated format to use (CSV would be nice).

    I suppose I could keep the path simple like root/v

  • Eric Wilson

    SSChasing Mays

    Points: 611

    Ed Sanford (2/18/2008)


    Just wanted to add that my main purpose for wanting to use this Array logic is to be able to pass selection lists to a query.

    ...

    I agree with the intent (and do the very same thing myself). But wouldn't you agree that it would be simpler AND more powerful to have more fully-implemented "table" support?

    We should be able to have table variables (client and server), specify table values (whole tables) as literals, do equality comparisons, etc. Just like A REAL TYPE.

    Then we could just pass a table as an argument.

    Since the industry gave us XML instead (sigh!), I use that as a work-around too.

    As to the "textual encoding" of it, I agree it should be somewhat terse. But that is not to be confused with the "value" of the parameter. For example, both of these "encodings" could be used to specify the same "value":

    <searchtypes><type name="green"></type><type name="blue"></type><type name="purple"></type></searchtypes>

    Or...

    searchtypes ( string name ) {

    green;

    blue;

    purple;

    }

    These are both the same "value" semantically. I prefer the second encoding and it's too bad we got XML instead.

  • Jeff Moden

    SSC Guru

    Points: 996622

    It seems to me that the real big difference between a mere table and an array, are the array indicies themselves... You know, the stuff where you can say IF A(2,3) = X THEN... where A(2,3) would be the row two, column three of an array called "A". Would that be true in most of your eyes?

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".
    "If "pre-optimization" is the root of all evil, then what does the resulting no optimization lead to?"

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • scott.sisson

    Grasshopper

    Points: 13

    I found this article very timely for me. I have a SQL function that does basic Proper casing of names and some matching criteria for the hundreds (eventually thousands I am guessing) of exceptions. In processing 18,197 rows with 6 calls to the function per select statement to change potential names the CURSOR ran 2x as fast as the "ARRAY". After reading the article, I am quite confused by this. Below is the code with the cursor

    select statement

    select name1,

    dbname.dbo.proper(name1)

    ... to name6

    ... set up and simple exlcusion stuff

    DECLARE properex CURSOR FAST_FORWARD FOR

    SELECT Oldval, Cvtval FROM Proper_exceptions

    where loc = 'M' order by sort

    OPEN properex

    FETCH NEXT FROM properex

    INTO @old, @new

    -- Check @@FETCH_STATUS to see if there are any more rows to fetch.

    WHILE @@FETCH_STATUS = 0

    BEGIN

    select @outputstring = replace(@outputstring,@old,@new)

    -- This is executed as long as the previous fetch succeeds.

    FETCH NEXT FROM properex

    INTO @old, @new

    END

    CLOSE properex

    DEALLOCATE properex

    -- the table variable

    ... same set up and simple exclusion stuff

    DECLARE @tbl TABLE(

    RowID INT IDENTITY(1, 1),

    ov VARCHAR(100),

    nv VARCHAR(50))

    /*Local variables */

    DECLARE @old VARCHAR(100),

    @new VARCHAR(50),

    @ctr int, /*create local @@fetch_status*/

    @cRow int /*row pointer (index)*/

    /* create array simulator */

    INSERT @tbl (ov,nv)

    SELECT Oldval, Cvtval FROM Common.dbo.Proper_exceptions

    where loc = 'M'

    order by sort

    set @ctr = @@ROWCOUNT

    set @cRow = 1

    WHILE @cRow <= @ctr

    BEGIN

    select @old = ov, @new = nv from @tbl

    where RowId = @cRow

    select @outputstring = replace (@outputstring,@old,@new)

    set @cRow = @cRow + 1

    END

    -- the current size of the table is only 310 rows

    after post comment - I should have read all the posts before adding mine. Sounds like cursors are more efficient in this case. I would appreciate other suggestions to my issue at hand. In try to proper case words - I already have a basic upper case first letter lower case the rest. I need an efficient way to loop through all my other criteria. For example for the name JOHN SMITH IV, CEO, CFO after the first pass would look like John Smith Iv Ceo, Cfo. I have a table with 'M' atching values of

    _ = blanks

    part of list of 300 match replacements

    _ceo_ = _CEO_

    _cfo_ = _CFO_

    _Iv_ = _IV_

    After passing thru all the replacements the final word is

    John Smith IV CEO, CFO

    Is there an easier way to process all of the potential replacements. Using the like statement I have filtered out all potential replacement words that do not have a letter match for the given word. For example the like clause for the example shown is %[johnsmitvcef]%.

Viewing 12 posts - 46 through 57 (of 57 total)

You must be logged in to reply to this topic. Login to reply