0

What's the best way to find out the number of rows in a view in SQL Server?

I have seen this answer for tables but I'd like to get the size for view.

Dale K
  • 25,246
  • 15
  • 42
  • 71
nilsinelabore
  • 4,143
  • 17
  • 65
  • 122
  • 5
    A view doesn't actually **contain** any rows (unlike a table) - it's just a stored SQL statement that gets run when you select from a view. Therefore, you cannot really determine the "number of rows in a view" - there are no rows in a view.... (unless it's an indexed view - but those are pretty rare) – marc_s Mar 29 '21 at 05:05
  • 4
    If you just want the number of rows, you can do a SELECT COUNT(*) FROM [myView]. That will return the current number of rows that the view returns. The link that you put was a query to find other physical properties of tables, like pages, etc. As @marc_s pointed out, unless a view is indexed, it doesn't have the same sorts of properties as a physical table (that is storing data). As I say, though, you can still get a count of rows – Craig Mar 29 '21 at 05:07
  • 2
    FYI SSMS is purely a client interface... its not the database engine. You only tag SSMS when you are asking a question about a specific part of the SSMS UI. Its not generally for query related questions. – Dale K Mar 29 '21 at 05:09
  • @marc_s, @Craig @Dale Wow thanks for all your replies. So the reason why I'm asking is because I was trying to open a view from an Azure SQL database into Power Bi which stopped responding so I guessed it might be that the number of rows is too large and I'd like to verify it. I tried something like `SELECT COUNT(*) FROM [myView]` and it took around 40 min to run the code so I'm wondering if there's a better way to do this. – nilsinelabore Mar 29 '21 at 05:18
  • 1
    You would have to examine the execution plan, it the data being returned is unexepctedly large it might be the result of a bad join producing a catesian product, the plan will show where. If you wanted to materialize the data you can do `select * into #table from` – Stu Mar 29 '21 at 08:14
  • 1
    If SELECT COUNT(*) is taking 40 minutes, then you should look at the performance of the view. View the execution plan to identify where you might be having an issue. – Sean Pearce Mar 29 '21 at 09:40
  • Hi @Stu thanks for the suggestion. I tried `select * into #table from My_View` and it took more than 30min. I'm wondering if I'm doing it correctly? – nilsinelabore Mar 29 '21 at 22:39
  • 1
    @nilsinelabore the suggestion was just to give you phyisical data in order to analyze, if the base query is slow - it's slow and that's impossible to diagonse without a whole lot more info. – Stu Mar 29 '21 at 23:01
  • @Stu Thanks I'm kinda new to SQL really appreciate your clarification. – nilsinelabore Mar 29 '21 at 23:17
  • 1
    If you want help to improve or understand issues with your query, I would suggest you ask a new question specifically for that and share the actual execution plan using [PasteThePlan](https://www.brentozar.com/pastetheplan) – Stu Mar 29 '21 at 23:35

0 Answers0