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 Friday, February 15, 2008 3:51 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, January 22, 2013 7:30 AM
Points: 38, Visits: 39
Very nice article

Now I know how to solve my problem : "SELECT * FROM table WHERE column IN @var". I should have test a lot of thing without finding the solution :D

thanks for the idea
Post #456228
Posted Friday, February 15, 2008 4:28 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, April 28, 2014 9:09 AM
Points: 2, Visits: 35
The example coding in the SQL Cursor SP is incorrect as it displays the last record twice. This is really bad programming in that the fetch status should be checked immediately after it is retrieved in order to exit the loop:

declare @OK bit

-- Get first row
open mycursor /*get row values*/
set @OK = 1;

while @OK = 1
BEGIN
/*get row values*/
FETCH MyCursor
INTO @CompName, @ContName

if @@fetch_status <> 0
set @OK=0
else
begin
/*perform operations with single row*/
PRINT 'T_SQL cursor row | ' + @ContName + ' | ' + @CompName
end
END
Post #456240
Posted Friday, February 15, 2008 12:18 PM


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: Tuesday, June 21, 2011 10:03 AM
Points: 577, Visits: 102
This article is over 2 1/2 years old, and it generated some controversy the first time around. So why is it a "featured article" in today's newsletter?

Steve, do you guys need some writers?


Signature is NULL
Post #456470
Posted Friday, February 15, 2008 12:28 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, April 10, 2012 10:48 AM
Points: 4, Visits: 14
I tried the same comparisons with a real-world example, a data table with over 200,000 rows. In the first iteration the cursor method took 15 seconds, while I killed the 'array' method after 15 minutes after only a small fraction of the rows had been processed. The next time around I added a PRIMARY KEY constraint to the identity field and this did the trick. The time for the array method dropped to 8 seconds. Kind of a key omission, I'd say...
Post #456478
Posted Friday, February 15, 2008 12:56 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
By the way, regarding an earlier comment that you cannot Index table variables...

1) True, technically, and another example of the failure to separate logical and physical issues. (Tables should behave as Tables, regardless...)

2) False, in practice. You can't "create index" on one. But you *can* create additional UNIQUE constraints on the table-variable at declaration time. This is implemented as an index, and queries (in my tests) will actually use them just like on a real table.

And any UNIQUE constraint that *also* includes the PK, is guaranteed to be unique. Just put the "thing you want to index" first in the list, so it's more selective. (The extra PK part ends up being wasteful, but oh well.)

I was able to drop a significantly complex scenario from about 10-seconds to about 3-seconds by placing two additional UQ constraints on the table-variable.



Post #456491
Posted Monday, February 18, 2008 7:07 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: Today @ 6:46 AM
Points: 519, Visits: 2,795
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.



Post #456893
Posted Monday, February 18, 2008 7:09 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: Today @ 6:46 AM
Points: 519, Visits: 2,795
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.



Post #456895
Posted Monday, February 18, 2008 7:09 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: Today @ 6:46 AM
Points: 519, Visits: 2,795
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.



Post #456896
Posted Monday, February 18, 2008 7:11 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: Today @ 6:46 AM
Points: 519, Visits: 2,795
Wow, sorry for the multiple posts, not sure how that happened.


Post #456897
Posted Monday, February 18, 2008 10:10 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/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.



Post #456985
« Prev Topic | Next Topic »

Add to briefcase «««23456»»

Permissions Expand / Collapse