Answer by Grant Fritchey ·
Answer by Matt Whitfield ·
Answer by Cyborg ·
CREATE TABLE dbo.MyTestTable( message_id int NOT NULL, language_id smallint NOT NULL, severity tinyint NULL, is_event_logged bit NOT NULL, text nvarchar(2048) NOT NULL, CONSTRAINT PK_MyTestTable PRIMARY KEY CLUSTERED ( message_id ASC,language_id ASC ) ) ON [PRIMARY] GO INSERT INTO MyTestTable SELECT * FROM Sys.messages DECLARE @NoRcrds INT SET @NoRcrds = NULL DECLARE @NoRcrds INT SET @NoRcrds = NULL SELECT message_id, language_id, severity, is_event_logged, text FROM ( SELECT ROW_NUMBER() OVER(ORDER BY Message_ID) ID, message_id, language_id, severity, is_event_logged, text FROM MyTestTable ) T WHERE ID
Addressing to second part of your question. Its all depends on whether your column on SELECT Query is indexed or not. Because while running Top operator SQL Server performs Sorting, Which definitely is an overhead if the columns on the select list is not a part of index key.
Answer by Christophe Vanderhaeghen ·To wrap it up a bit: - Using Row_number works a bit better then TOP with small amounts of data (< 1000), but it's not exact science. - Using TOP is most of the time the best way to go. Most certainly if you have/expect 1000+ records to fetch. - Use TOP together with the ORDER BY clause (else the rows would be chosen arbitrary, which is pointless) - Ad hoc querying to an OLTP system absolutely leads to trouble - If ad hoc querying is needed, keep the following in mind: 1. you better use stored procedures or at least parameterized queries 2. Instead, set up a reporting system where you can generate a more BI friendly schema, add in aggregates, make the database read only, so locking isn't an issue... etc 3. In SQL server 2008, consider looking at the Resource governor for (partly) solving ad hoc queries - Profiling or looking at the query optimizer can also help - Indexing can boost performance (if correct indexing is used of course) Thanks a bunch for those tips all of you. Best regards, Christophe
Answer by malpashaa ·1- Try this: DECLARE @var AS INT; SET @var = 200; SELECT TOP(ISNULL(@var, 2147483647)) * FROM dbo.MyTable ORDER BY some_columns And do not forget to specify ORDER BY columns, or else the rows would be chosen arbitrary. 2- In my opinion you should not think in optimization until you need to, and there is no silver bullet regarding whether TOP is good or bad.