Friday, 4 December 2009

SQL: Select your table rows gently...

How to get all rows in a table?

Simple answer:



SELECT * FROM TABLE


BUT, a better practise is to

1. Do a count first if you suspect it is a large table
2. Use (nolock) in order not to lock the table during the query execution

Syntax examples for MS SQL Server:



--Count rows without locking table
SELECT COUNT(*) FROM TABLE(NOLOCK)

--Get all rows without locking table
SELECT * FROM TABLE(NOLOCK)

--Get the 1000 last entries based on primary key (assuming it is column #1)
SELECT TOP 1000 * FROM TABLE(NOLOCK) ORDER BY 1 DESC

No comments: