August 4, 2015 at 12:55 pm
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.
August 4, 2015 at 1:17 pm
It's possible that you were connected to a different database or server.
August 4, 2015 at 1:24 pm
There are only 2 SQLSERVERS that I can connect to and I verified the instance I was connecting.
August 4, 2015 at 1:32 pm
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.
August 4, 2015 at 1:41 pm
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>
August 4, 2015 at 2:21 pm
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.
August 5, 2015 at 4:00 am
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
August 7, 2015 at 2:12 pm
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.
August 9, 2015 at 5:46 am
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
August 9, 2015 at 6:54 am
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
😎
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply