-2

I created a view whose definition is SELECT [Some address related columns] FROM TableName. The base table has several million rows. (SQL 2012)

I want to make this view available temporarily to a development team that is working on improving functionality in a connecting system and I don't have time to be the middle man retrieving data for them while they're doing that. Hence, a view for them to test their (hopefully) improved SQL statements.

I want this view however, to only ever return a maximum of 1000 rows that meets the WHERE criteria the connected user constructs. For the life of me I can't find some guidance on how to do this.

Monica
  • 55
  • 1
  • 11
  • Have you tried using `SELECT TOP 1000 ...`? – PinnyM Apr 30 '15 at 04:48
  • provide your trial code! – Vikrant Apr 30 '15 at 04:50
  • 1
    (sigh) [***Have you tried anything?***](http://whathaveyoutried.com) Also, I can see you're using SQL Server... you *should* tag your question appropriately with `sql-server`... the `sql` tag is for general questions about the SQL *language* (and there are more implementations than SQL Server out there) – Barranka Apr 30 '15 at 05:00
  • Certainly no need for ugly sarcasm @Barranka, thank you. And since this wasn't a server administration question, I chose not to use the sql-server tag which I thought was more for adminstration related inquiries. I'm sorry you're personally offended. Yes, of course I've "tried anything". The view statement is as simplistic as I've described, there is no need to spell out every single column. SELECT blah FROM TableName. When I add TOP 1000 to that, it only allows the TOP 1000 rows of the underlying table to be queried. The entire table needs to be queried but never return more than 1000 rows. – Monica Apr 30 '15 at 06:54

2 Answers2

1

I believe you are looking for the expression "TOP" https://msdn.microsoft.com/en-us/library/ms189463.aspx

In management studio you can right click the table and select the first option, this will generate the code for you. It should look something like this:

SELECT TOP 1000 column1, column2, column3 FROM mytable
AnswerIsNot42
  • 25
  • 1
  • 7
1

You should use TOP function to retrieve specific number of rows from table.

      Select TOP 100 column1, column2 from sample_table;

You can also use 'ORDER BY' in you query to specify which rows (last or first) you need to retrieve.

    Select TOP 100 column1, column2 from sample_table order by column1 DESC.
Rahul Parit
  • 321
  • 3
  • 11
  • Thank you for your more adult response @Rahul. Using TOP was the first solution I turned to, but in testing I found that if the view definition contained SELECT TOP 1000.... and then I queried the view with SELECT * FROM myView WHERE State = NY, the query would only really look in the first 1000 rows of the underlying table. The objective is to allow the entire table to be queried, but to guarantee that any result set is capped at 1000 rows returned. – Monica Apr 30 '15 at 07:05