Select * from a table with one billion rows failing

Question

Select * from a table with one billion rows failing

Viewing 9 posts - 16 through 23 (of 23 total)

You must be logged in to reply to this topic. Login to reply

Leo.Miller SSChampion Points: 13051 More actions · Answer 1

Lowell (11/4/2010)
maybe he was doing a select * from BillionRowTable so he could see the column names, and the error threw him off.
He may not be familiar with sp_help [TableName] yet.

Or select top 0 * from TableName.

Or why not just use the Object Explorer?

Hay RPSql you need to reply to some of these posts so we know what you wanted. If you aren't careful the forum may decide you are a Trol and have you blocked.

Cheers

Leo

Leo
Nothing in life is ever so complicated that with a little work it can't be made more complicated.

GSquared SSC Guru Points: 260824 More actions · Answer 2

Leo.Miller (11/4/2010)
Lowell (11/4/2010)
maybe he was doing a select * from BillionRowTable so he could see the column names, and the error threw him off.
He may not be familiar with sp_help [TableName] yet.
Or select top 0 * from TableName.
Or why not just use the Object Explorer?
Hay RPSql you need to reply to some of these posts so we know what you wanted. If you aren't careful the forum may decide you are a Trol and have you blocked.
Cheers
Leo

select *

from sys.columns

where object_id = object_id(N'MyBillionRowTable','U');

Though I have to admit, I usually use a "select top 1000 * from table" for that kind of thing, so I can glance at the data and make sure it's what the column name leads me to expect. Found that the "FaxNumber" column was being used for a marketing code in one table that way.

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

Sean Lange SSC Guru Points: 286590 More actions · Answer 3

And that is why I wrote a simple gui for looking at the top 20 rows of a table. Has few other odds and ends too. I can post it if anybody wants to check it out.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

Jeff Moden SSC Guru Points: 1004324 More actions · Answer 4

RPSql (11/3/2010)
Select * from a table with one billion rows failing with following error (SQL 2005 SP3 HF11)
An error occurred while executing batch. Error message is: Exception of type 'System.OutOfMemoryException' was thrown.

Dude! You've had a lot of people try to help you. A little feedback on your part would be a big help! 😉

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Carlton Leach SSCarpal Tunnel Points: 4202 More actions · Answer 5

But his SSC.com rating is higher than yours Mr Miller, I was trying to have a little faith 😉

Carlton Leach SSCarpal Tunnel Points: 4202 More actions · Answer 6

And hay come on: at least he didn't use right click > open table 😎

Leo.Miller SSChampion Points: 13051 More actions · Answer 7

Carlton Leach (11/5/2010)
But his SSC.com rating is higher than yours Mr Miller, I was trying to have a little faith 😉

What's with the Mr Miller? Makes me feel like a school kid again.

Actually I've been working with SQL since the 6.5 days back in about 1998, just as SQL 7 was being introduced. Unfortunately I only relatively recently decided it was a good idea to get involved in the SSC community beyond the occasional search for an answer. I also don't spend a lot of time on the forum or doing those thing that will improve my rating.

Tough luck on me, I'll live with the low rating. I do after all have a real job I need to do and boss to keep happy so I can pay the bills.

Cheers

Leo

Leo
Nothing in life is ever so complicated that with a little work it can't be made more complicated.

Carlton Leach SSCarpal Tunnel Points: 4202 More actions · Answer 8

Leo.Miller (11/7/2010)
Carlton Leach (11/5/2010)
But his SSC.com rating is higher than yours Mr Miller, I was trying to have a little faith 😉
What's with the Mr Miller? Makes me feel like a school kid again.
Actually I've been working with SQL since the 6.5 days back in about 1998, just as SQL 7 was being introduced. Unfortunately I only relatively recently decided it was a good idea to get involved in the SSC community beyond the occasional search for an answer. I also don't spend a lot of time on the forum or doing those thing that will improve my rating.
Tough luck on me, I'll live with the low rating. I do after all have a real job I need to do and boss to keep happy so I can pay the bills.
Cheers
Leo

TBH, I take SSC ratings about as seriously as MS certs 😀

6.5? You poor scarred soul, I hear the dentist is far more pleasant

Leo.Miller SSChampion Points: 13051 More actions · Answer 9

Carlton Leach (11/7/2010)
TBH, I take SSC ratings about as seriously as MS certs 😀
6.5? You poor scarred soul, I hear the dentist is far more pleasant

A man after my won heart!

Leo
Nothing in life is ever so complicated that with a little work it can't be made more complicated.