|
|
|
Forum Newbie
Group: General Forum Members
Last Login: Thursday, November 19, 2009 5:53 AM
Points: 5,
Visits: 22
|
|
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
|
|
|
|
|
Forum Newbie
Group: General Forum Members
Last Login: Monday, November 23, 2009 6:00 AM
Points: 2,
Visits: 12
|
|
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
|
|
|
|
|
Mr or Mrs. 500
Group: General Forum Members
Last Login: Monday, March 08, 2010 11:52 AM
Points: 576,
Visits: 90
|
|
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
|
|
|
|
|
Forum Newbie
Group: General Forum Members
Last Login: Thursday, June 05, 2008 11:14 AM
Points: 4,
Visits: 13
|
|
| 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...
|
|
|
|
|
SSC-Enthusiastic
Group: General Forum Members
Last Login: Thursday, September 17, 2009 11:20 AM
Points: 137,
Visits: 40
|
|
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.
|
|
|
|
|
SSC-Addicted
Group: General Forum Members
Last Login: Yesterday @ 7:11 AM
Points: 445,
Visits: 1,779
|
|
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.
|
|
|
|
|
SSC-Addicted
Group: General Forum Members
Last Login: Yesterday @ 7:11 AM
Points: 445,
Visits: 1,779
|
|
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.
|
|
|
|
|
SSC-Addicted
Group: General Forum Members
Last Login: Yesterday @ 7:11 AM
Points: 445,
Visits: 1,779
|
|
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.
|
|
|
|
|
SSC-Addicted
Group: General Forum Members
Last Login: Yesterday @ 7:11 AM
Points: 445,
Visits: 1,779
|
|
Wow, sorry for the multiple posts, not sure how that happened.
|
|
|
|
|
SSC-Enthusiastic
Group: General Forum Members
Last Login: Thursday, September 17, 2009 11:20 AM
Points: 137,
Visits: 40
|
|
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.
|
|
|
|
