February 18, 2008 at 7:07 am
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.
February 18, 2008 at 7:09 am
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.
February 18, 2008 at 7:09 am
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.
February 18, 2008 at 7:11 am
Wow, sorry for the multiple posts, not sure how that happened.
February 18, 2008 at 10:10 am
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.
February 18, 2008 at 11:02 am
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.
February 18, 2008 at 11:54 am
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.
February 18, 2008 at 2:04 pm
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.
February 18, 2008 at 2:19 pm
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
February 18, 2008 at 5:53 pm
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.
February 18, 2008 at 6:03 pm
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
Change is inevitable... Change for the better is not.
February 19, 2008 at 11:14 am
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 56 (of 56 total)
You must be logged in to reply to this topic. Login to reply