CODEWITHSUNDEEP
sql-server
Rajesh Kumar
Rajesh Kumar

Reputation: 1290

Do we have any limit for maximum number of rows returned by SELECT query

I am using a SELECT query on a SQL Server table with 33,000 rows, but it is returning only 3500 rows.

Do we have any limit for maximum number of rows returned by SELECT query in SQL Server? Please let me know how to change it if yes.

Thanks in advance

Upvotes: 1

Views: 8842

Answers (3)

Alex Martinez
Alex Martinez

Reputation: 21

Select * from youtable must show you all the 33,000 rows, If you really have that amount of rows. If I write select top 1000 * from yourtable, then I will see only 1,000 thousand. I work with tables with 950,000 rows, so you have to be sure that you really have the amount you said. Try again with select * from yourtable.

Upvotes: 0

Ajay2707
Ajay2707

Reputation: 5808

By Default Sql-server return every thing in your table like select * from yourtable , if it has 4 rows or 40000 rows, it return everything.

For this Sqlserver have where clause which used to filter the data. 3500 rows from your 33000 rows , it is not done automatically, either you search on wrong database or you are given a filter condition.

Now if you want to get only limited entry to return, the first thing comes in my mind is TOP intvalue clause which return the rows which you define in intvalue.

- Top
- Where
- Distinct

And others as @Joel suggested.

Upvotes: 1

Joel Coehoorn
Joel Coehoorn

Reputation: 416039

The default in Sql Server is to return everything the query asked for. You can override the default via SET ROWCOUNT, but I'll be very surprised if that's what's happening here. Instead, check the sql command for any of the following that might be limiting the results:

  • TOP
  • OFFSET/FETCH
  • JOIN
  • WHERE
  • APPLY
  • GROUP BY
  • DISTINCT
  • HAVING (Implies GROUP BY, but could restrict records where the groups do not)

Upvotes: 5

Related Questions