Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase «««23456

The ARRAY In SQL Server 2000 Expand / Collapse
Author
Message
Posted Monday, February 18, 2008 11:02 AM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Wednesday, October 22, 2014 12:44 PM
Points: 519, Visits: 2,835
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.



Post #457002
Posted Monday, February 18, 2008 11:54 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, March 14, 2014 10:29 AM
Points: 137, 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.



Post #457025
Posted Monday, February 18, 2008 2:04 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, June 3, 2010 3:17 PM
Points: 5, 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.
Post #457072
Posted Monday, February 18, 2008 2:19 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, June 3, 2010 3:17 PM
Points: 5, 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
Post #457076
Posted Monday, February 18, 2008 5:53 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, March 14, 2014 10:29 AM
Points: 137, 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.



Post #457139
Posted Monday, February 18, 2008 6:03 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 9:37 AM
Points: 35,371, Visits: 31,914
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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #457142
Posted Tuesday, February 19, 2008 11:14 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, July 25, 2014 2:14 PM
Points: 1, 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]%.
Post #457542
« Prev Topic | Next Topic »

Add to briefcase «««23456

Permissions Expand / Collapse