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 «««1213141516

Just For Fun: An Impossible Delete Expand / Collapse
Author
Message
Posted Monday, November 2, 2009 6:42 PM


SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: General Forum Members
Last Login: Wednesday, October 15, 2014 9:14 AM
Points: 9,294, Visits: 9,483
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!


-- RBarryYoung, (302)375-0451 blog: MovingSQL.com, Twitter: @RBarryYoung
Proactive Performance Solutions, Inc.
"Performance is our middle name."
Post #812690
Posted Monday, November 2, 2009 6:45 PM


SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: General Forum Members
Last Login: Wednesday, October 15, 2014 9:14 AM
Points: 9,294, Visits: 9,483
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



-- RBarryYoung, (302)375-0451 blog: MovingSQL.com, Twitter: @RBarryYoung
Proactive Performance Solutions, Inc.
"Performance is our middle name."
Post #812691
Posted Monday, November 2, 2009 6:46 PM


SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: General Forum Members
Last Login: Wednesday, October 15, 2014 9:14 AM
Points: 9,294, Visits: 9,483
BlackHawk-17 (10/30/2009)
What a great thought experiment!

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.


-- RBarryYoung, (302)375-0451 blog: MovingSQL.com, Twitter: @RBarryYoung
Proactive Performance Solutions, Inc.
"Performance is our middle name."
Post #812692
Posted Monday, November 2, 2009 6:52 PM


SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: General Forum Members
Last Login: Wednesday, October 15, 2014 9:14 AM
Points: 9,294, Visits: 9,483
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.


-- RBarryYoung, (302)375-0451 blog: MovingSQL.com, Twitter: @RBarryYoung
Proactive Performance Solutions, Inc.
"Performance is our middle name."
Post #812693
Posted Tuesday, November 3, 2009 9:52 PM


SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: General Forum Members
Last Login: Wednesday, October 15, 2014 9:14 AM
Points: 9,294, Visits: 9,483
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.


-- RBarryYoung, (302)375-0451 blog: MovingSQL.com, Twitter: @RBarryYoung
Proactive Performance Solutions, Inc.
"Performance is our middle name."
Post #813407
Posted Tuesday, November 3, 2009 9:54 PM


SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: General Forum Members
Last Login: Wednesday, October 15, 2014 9:14 AM
Points: 9,294, Visits: 9,483
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.


-- RBarryYoung, (302)375-0451 blog: MovingSQL.com, Twitter: @RBarryYoung
Proactive Performance Solutions, Inc.
"Performance is our middle name."
Post #813408
Posted Thursday, November 12, 2009 1:37 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: 2 days ago @ 11:12 AM
Points: 7,791, Visits: 9,545
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
Post #818102
« Prev Topic | Next Topic »

Add to briefcase «««1213141516

Permissions Expand / Collapse