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.
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.The isnull function is something that could be used here. good point. Nevertheless I think that I should really think it over, looking at the remarks on the Order by clause.Specifically on the point of top @var being bad for performance - that will depend on how the query optimizer chooses to optimise the query. Normally, based on a number of expected rows, a different join operator would be chosen to operate a join - or the query optimiser may decide that a seek is more efficient for a small number of rows whereas a scan might be more effective for a large number of rows. This type of difference in decision by the query optimiser can yield performance variability - and that is where the forum posts you mention come from. Try looking at the actual plan for this simple query: DECLARE @var [int] = 100 SET @var = 10000 SELECT TOP (@var) * FROM [sys].[all_columns] In that, you will see that the estimated number of rows is different from the actual number of rows. That difference can be critical when trying to diagnose query optimiser choices (seeing as the estimated number of rows is what the plan is based on).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, ChristopheWow, execelent summary. I'd make one minor change. The next to the last point says "Profiling or looking at the query optimizer can also help, but this is more for specific queries and not ad hoc" is not accurate. It is for ad hoc as well. Other than that, you've nailed it all.
Write an Answer
No one has followed this question yet.