1

During an interview, an interviewer was asking this question and I didn't answer well. The question is: if we have a table which has millions of records. Each record has first name, last name and telephone number. On the web page, we have three textboxes: FirstName, LastName and Telephone and a Search button. If users click on the search button, it will retrieve data from the database and display 20 records on each page. What's the good practice of retrieving the data? Should we get all matched data and put them in a buffer? or we get the first 20 records only? If users click on "Go to next page", then we retrieve the next 20 records from the database. How should we right a stored procedure for this process?

user3139633
  • 13
  • 3
  • As always it depends. One way is to write stored procedure that use [OFFSET FETCH](https://technet.microsoft.com/en-us/library/gg699618%28v=sql.110%29.aspx) for pagination – Lukasz Szozda Oct 30 '15 at 18:12
  • 1
    That is a pretty ambiguous question to say the least which is likely to be filled with opinion. It would however force the interviewer to demonstrate some knowledge of how this stuff works. I suspect they were looking for pagination from the sql server side. That means you pass a page number and row count to your search procedure and it returns only those rows required for that page of data. Even with that there are a number of ways to write it. – Sean Lange Oct 30 '15 at 18:12
  • Intrviewer was excpecting the answer with row_number window function before sql server 2012 and built in offset fetch from 2012... – Giorgi Nakeuri Oct 30 '15 at 18:14
  • @GiorgiNakeuri Are you interviewer? – Lukasz Szozda Oct 30 '15 at 18:14
  • @lad2025, I am team lead and yes, I interview sometimes. My opinion is the same as Sean's. – Giorgi Nakeuri Oct 30 '15 at 18:19
  • Not exactly a duplicate, but the more specific question has been asked and answered here - http://stackoverflow.com/a/19609938/99691 – gordy Oct 30 '15 at 18:22
  • if we use offset fetch, it means whenever we go to a new page, we access the database and retrieve the data again? Is it a good idea to access database so frequently? – user3139633 Oct 30 '15 at 19:36
  • @user3139633 it's certainly better to use offset/fetch to ask the database for one page of results at a time vs. downloading all of the results - there could be millions of results and the user will never look at them all, it would take a long time to fetch them all and it would be expensive. – gordy Oct 31 '15 at 02:20

0 Answers0