Large Records -- No Successfull Result

Question

Post reply

Large Records -- No Successfull Result

Minnu

SSCertifiable

Points: 6451
More actions
June 24, 2013 at 8:01 am

#395022

Hi Team,
I've a simple table USG_Data with below columns, but having 2.5 lac records.
id, Name, Address, City, Phone.
when i select count(*) from USG_Data, gettting result with count of records, but
when i select * from USG_Data am not gettting the complete result set, after displaying some records sql server displaying that execuing query....
Want to know the reason please.

Viewing 9 posts - 1 through 8 (of 8 total)

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

Matthew Darwin SSCertifiable Points: 5513 More actions · Answer 1

If I've read that right, it sounds like the row count settings in your SSMS.

Go to "Tools" -> "Options"

In the new window, expand "Query Options" then "SQL Server" then "General" and check what is populated in the "SET ROWCOUNT" field. If it's a number other than 0, then that will be your issue.

Follow me on twitter @EvoDBACheck out my blog Natural Selection DBA[/url]

Minnu SSCertifiable Points: 6451 More actions · Answer 2

Hi,

Below are the settings in specified path....

Set Rowcount = 0

Set Textsize = 2147483647

execution time-out=0

Batch seperator=GO

Jeff Moden SSC Guru Points: 1004370 More actions · Answer 3

Is anyone else using the table? It could be blocking caused by an update or an oddball SELECT.

--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)

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 4

Post the ddl (CREATE TABLE ...) including any indexes.

Do you get the full set of rows if you restrict to one column in the SELECT? Does NOLOCK make any difference?

SELECT ID FROM USG_Data NOLOCK

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

Minnu SSCertifiable Points: 6451 More actions · Answer 5

Hi,

there are no locks on table, selecting data by specefying NOLOCK only, still gettting the same problem.

Please suggest...!

J Livingston SQL SSC Guru Points: 51550 More actions · Answer 6

if you run

select *

INT0 #tmp

from USG_Data

how many records are inserted?

________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 7

Minnu (6/25/2013)
Hi,
there are no locks on table, selecting data by specefying NOLOCK only, still gettting the same problem.
Please suggest...!

Please answer!

ChrisM@Work (6/25/2013)
Post the ddl (CREATE TABLE ...) including any indexes.
Do you get the full set of rows if you restrict to one column in the SELECT?