SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


The ARRAY In SQL Server 2000


The ARRAY In SQL Server 2000

Author
Message
dmbaker
dmbaker
Right there with Babe
Right there with Babe (715 reputation)Right there with Babe (715 reputation)Right there with Babe (715 reputation)Right there with Babe (715 reputation)Right there with Babe (715 reputation)Right there with Babe (715 reputation)Right there with Babe (715 reputation)Right there with Babe (715 reputation)

Group: General Forum Members
Points: 715 Visits: 3519
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
Eric Wilson
SSC-Enthusiastic
SSC-Enthusiastic (151 reputation)SSC-Enthusiastic (151 reputation)SSC-Enthusiastic (151 reputation)SSC-Enthusiastic (151 reputation)SSC-Enthusiastic (151 reputation)SSC-Enthusiastic (151 reputation)SSC-Enthusiastic (151 reputation)SSC-Enthusiastic (151 reputation)

Group: General Forum Members
Points: 151 Visits: 44
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
Ed Sanford
Grasshopper
Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)

Group: General Forum Members
Points: 13 Visits: 16
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
Ed Sanford
Grasshopper
Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)

Group: General Forum Members
Points: 13 Visits: 16
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
Eric Wilson
SSC-Enthusiastic
SSC-Enthusiastic (151 reputation)SSC-Enthusiastic (151 reputation)SSC-Enthusiastic (151 reputation)SSC-Enthusiastic (151 reputation)SSC-Enthusiastic (151 reputation)SSC-Enthusiastic (151 reputation)SSC-Enthusiastic (151 reputation)SSC-Enthusiastic (151 reputation)

Group: General Forum Members
Points: 151 Visits: 44
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
Jeff Moden
SSC Guru
SSC Guru (84K reputation)SSC Guru (84K reputation)SSC Guru (84K reputation)SSC Guru (84K reputation)SSC Guru (84K reputation)SSC Guru (84K reputation)SSC Guru (84K reputation)SSC Guru (84K reputation)

Group: General Forum Members
Points: 84949 Visits: 41071
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.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
scott.sisson
scott.sisson
Forum Newbie
Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)

Group: General Forum Members
Points: 3 Visits: 16
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]%.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search