Is there efficient SQL to query a portion of a large table

Question

The typical way of selecting data is:

select * from my_table

But what if the table contains 10 million records and you only want records 300,010 to 300,020

Is there a way to create a SQL statement on Microsoft SQL that only gets 10 records at once?

Eg

select * from my_table from records 300,010 to 300,020

This would be way more efficient than retrieving 10 million records across the network, storing them in the IIS server and then counting to the records you want.

Answer 1

SELECT * FROM my_table is just the tip of the iceberg. Assuming you're talking a table with an identity field for the primary key, you can just say:

SELECT * FROM my_table WHERE ID >= 300010 AND ID <= 300020

You should also know that selecting * is considered poor practice in many circles. They want you specify the exact column list.

Answer 2

Absolutely. On MySQL and PostgreSQL (the two databases I've used), the syntax would be

SELECT [columns] FROM table LIMIT 10 OFFSET 300010;

On MS SQL, it's something like SELECT TOP 10 ...; I don't know the syntax for offsetting the record list.

Note that you never want to use SELECT *; it's a maintenance nightmare if anything ever changes. This query, though, is going to be incredibly slow since your database will have to scan through and throw away the first 300,010 records to get to the 10 you want. It'll also be unpredictable, since you haven't told the database which order you want the records in.

This is the core of SQL: tell it which 10 records you want, identified by a key in a specific range, and the database will do its best to grab and return those records with minimal work. Look up any tutorial on SQL for more information on how it works.

Answer 3

Try looking at info about pagination. Here's a short summary of it for SQL Server: http://www.singingeels.com/Articles/Pagination_In_SQL_Server_2005.aspx.

Answer 4

Use TOP to select only a limited amont of rows like:

SELECT TOP 10 * FROM my_table WHERE ID >= 300010

Add an ORDER BY if you want the results in a particular order.

To be efficient there has to be an index on the ID column.

Answer 5

When working with large tables, it is often a good idea to make use of Partitioning techniques available in SQL Server.

The rules of your partitition function typically dictate that only a range of data can reside within a given partition. You could split your partitions by date range or ID for example.

In order to select from a particular partition you would use a query similar to the following.

SELECT <Column Name1>…/* 
FROM <Table Name> 
WHERE $PARTITION.<Partition Function Name>(<Column Name>) = <Partition Number>

Take a look at the following white paper for more detailed infromation on partitioning in SQL Server 2005.

http://msdn.microsoft.com/en-us/library/ms345146.aspx

I hope this helps however please feel free to pose further questions.

Cheers, John