November 2, 2009 at 6:42 pm
sjimmo (10/30/2009)
Brings back many fond memories, or nightmares back then. Excellent article, which has the opportunity to not only show alternatives to how we do things now, but also provides opportunities to expand our knowledgebase with alternative examples. Never know when one may run into some of these opportunities.
Thanks, sjimmo!
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
November 2, 2009 at 6:45 pm
Tom Garth (10/30/2009)
Thanks for the thought provoking article R, and 5 stars for a good read!I've used the Temp In-Place method in the past. Sometimes to fix my own mistakes. I never knew it had a name.
An aside: Assuming tinyint for the age would be incorrect, since the primary solution in the article drove the column negative.
Tinyint cannot go negative, so it gets to use positive integers up to 255. (try the following code to demonstrate):
declare @t tinyint;
set @t = 127
select @t = @t + 2
select @t
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
November 2, 2009 at 6:46 pm
BlackHawk-17 (10/30/2009)
What a great thought experiment! :smooooth:It was fun to watch the problem solving unfold and the answer develop.
These are the articles that keep me coming back.
Thanks a ton for sharing;
Greg
Thanks Greg, that's great to hear. 🙂
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
November 2, 2009 at 6:52 pm
Wanderlei Santos (10/30/2009)
I know this is an old article, and maybe someone already offered this solution, but to be honest the comment list is just too long for me to read it all...So I used your approach, but instead of the sex column, I used the age, which I assumed was an integer. ...
Oh, I definitely considered using the Age column, and it sure would have been easier if it was indeed an INTEGER. The problem is though, that we cannot assume that it is an INTEGER, since a TINYINT is more than sufficient. Since a TINYINT saves three bytes per row in the table over an INTEGER, there is every possibility that it is not in fact a full INTEGER.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
November 3, 2009 at 9:52 pm
JLSSCH (11/2/2009)
Thanks for your comments. The following modified solution works when you use the row_number() function to number the rows in an output query.
ROW_NUMBER() makes it easy. However, let's review the restrictions:
To this the OP adds the following restrictions:
No intermediate tables, and
No additional identity column.
And implicit to this, of course, is that it is for SQL2000.
The ROW_NUMBER() function is SQL Server 2005.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
November 3, 2009 at 9:54 pm
JLSSCH (11/2/2009)
One final update to my solution. Using CTE's, the output query is much easier to understand.
CTE's also are not avaialble in SQL Server 2000.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
November 12, 2009 at 1:37 pm
Nice article. I liked very much seeing the "temp-in-place" technique applied, I guess it's very rare these days.
But I didn't recognise the description of the early 70s. I started in computing somewhere between 5 and 10 years ealier than you, and although I did work briefly on (sometimes "in" rather than "on" - walking around inside them was not uncommon) machines with very small stores (one using mercury delay lines for main storage, another using magnetostrictors) , they were already very obsolescent and long before 1970 this sort of thing was thoroughly obsolete. Typical "small" machines of the early 60s like the Elliott 803 (1959) and the IBM 1620 (also 1959) and the PDP-1 (1960?) had minimum shipable configurations with thousands of words/digits/whatever (they didn't use "bytes") of memory - you couldn't get one http://www.sqlservercentral.com/Forums/Skins/Classic/Images/RichTextBoxTable/tbl_bottomleft.gifwith less than 160kbits, 60kbits(?? maybe 72kbits, can't remember), and 72kbits respectively. And of course the at the opposite extreme were were things like the IBM 709, with 1152kbits memory, as early as 1958, the Ferranti Atlas I with 5Mbits of "One level storage" (Virtual Memory) 1962 and Ferranti Titan with 6Mbits of core store in 1964. I can't remember even seeing a machine with as little main memory as you mention after March 1969 (except in museums).
As for kilobyte discs, I was using 7Mbyte removable discs in 1967 (7Mbytes capacity, not kilobytes). These were the cheap option - the expensive option was 30 MBytes on a single exchangeable disc pack (the 30MByte version - actually 29MByte - was introduced by IBM early in 1965). Even some "drums" (in quotes because they were mostly single platter discs with a head per track, instead of the cylindrical drums with recording surface parallel to the axis of rotation of the 1950s) were by then up to 1 Mbyte. I really don't remember discs measured in kilobytes.
So it would be nice to know what machine models you were using - there seems to be a whole thread in the development of modern computers that I somehow managed to miss, and I'd like to read up on it.
Tom
Viewing 7 posts - 151 through 156 (of 156 total)
You must be logged in to reply to this topic. Login to reply