Conflicting results in SQLSERVER management studio: select * and Rt click table to edit rows

Question

Conflicting results in SQLSERVER management studio: select * and Rt click table to edit rows

chandrika5

SSCrazy

Points: 2734
More actions
August 4, 2015 at 12:55 pm

#302976

This is SQLSERVER 2008 with the management studio. I have a simple table with 250 rows. This table is updated via a web app.
On SQL SERVER management studio:
1.simple query : select * from table1 OR select count(*) from table1 gives 11 rows!
I know the above is incorrect. Before going in full panic mode:
2.Right click the table and select Edit top 200 rows
After 200 rows are returned
Open the SQL pane and remove "TOP (200)" from the query
Run the SQL
I get all the 250 rows!.
My simple query still gives 11 rows.
After reconnecting to the DB, restarting the management studio and several iteration of #2, I get 250 rows from my simple query.
How should I explain this to the Infrastructure group ? What could be the problem.

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

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

Luis Cazares SSC Guru Points: 183706 More actions · Answer 1

It's possible that you were connected to a different database or server.

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

chandrika5 SSCrazy Points: 2734 More actions · Answer 2

There are only 2 SQLSERVERS that I can connect to and I verified the instance I was connecting.

Luis Cazares SSC Guru Points: 183706 More actions · Answer 3

Was it the same on the Object Explorer and the Query Editor?

It's not possible to randomly return less rows using "select * from table" unless there's something else going on.

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

Eirikur Eiriksson SSC Guru Points: 182915 More actions · Answer 4

chandrika5 (8/4/2015)
This is SQLSERVER 2008 with the management studio. I have a simple table with 250 rows. This table is updated via a web app.
On SQL SERVER management studio:
1.simple query : select * from table1 OR select count(*) from table1 gives 11 rows!
I know the above is incorrect. Before going in full panic mode:
2.Right click the table and select Edit top 200 rows
After 200 rows are returned
Open the SQL pane and remove "TOP (200)" from the query
Run the SQL
I get all the 250 rows!.
My simple query still gives 11 rows.
After reconnecting to the DB, restarting the management studio and several iteration of #2, I get 250 rows from my simple query.
How should I explain this to the Infrastructure group ? What could be the problem.

Quick guess, is there any paging of results on the web server? Does set rowcount XX ring a bell? Once the rowcount is set it lives with the session or until unset.

😎

<grin>fun joke to welcome DBA beginners</grin>

chandrika5 SSCrazy Points: 2734 More actions · Answer 5

No rowcount!.

I wrote the web app. It is straight select with some wherepart.

Is there some SQL server level caching or similar that is not visible to developers ?

I will watch if I can replicate it.

Grant Fritchey SSC Guru Points: 398909 More actions · Answer 6

I'd guess it's something on the code side of things. How it's connecting or how the call is being made. There's nothing on the SQL Server side that would limit the results like that. Maybe they're calling a view or a function instead of directly to the table? Maybe you have different schema's and they're calling a different one?

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

chandrika5 SSCrazy Points: 2734 More actions · Answer 7

Just replicated it!.

I am using SQLSERVER Management Studio for both the queries.

1. select * from dbo.mytable

VS

Expand the Instance --> Databases --> tables --> Right click the table and select "Edit top 200 rows".

In the result pane --right click and select SQL, remove TOP(200) from the sql statement and execute.

I have no idea how to explain this to the Infrastructure group. There is no dedicated DBA per say. They are all networking people.

Grant Fritchey SSC Guru Points: 398909 More actions · Answer 8

I can't replicate it here. Check your SET ROWCOUNT in the properties. Maybe that's set to 11.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

Eirikur Eiriksson SSC Guru Points: 182915 More actions · Answer 9

chandrika5 (8/7/2015)
Just replicated it!.
I am using SQLSERVER Management Studio for both the queries.
1. select * from dbo.mytable
VS
Expand the Instance --> Databases --> tables --> Right click the table and select "Edit top 200 rows".
In the result pane --right click and select SQL, remove TOP(200) from the sql statement and execute.
I have no idea how to explain this to the Infrastructure group. There is no dedicated DBA per say. They are all networking people.

Can you post the actual execution plan for the query? A hint towards rowcount limit would be that StatementEstRows, EstimateRows and ActualRows would all be showing the same number, in this case 11. Also try running the select again with this on top:

SET ROWCOUNT 0;

GO

😎