An even more efficient solution might be to replace the c.u.r.s.o.r. *cough* with a set based solution. But we would need much more detailed information to help any further.

duplicate post.

original post including a number of replies.

@bhargava

Please don't cross post. Instead of open yet another thread with the very same question it would have been better to provide the...

Use ISNULL() to assign a non-NULL value?? :ermm:

Are you aware that we're talking about a table with more than a billion rows and such an index would only be required...

vinod.saraswat (7/4/2011)

---

Hi All,

Datatype for both the cols are int and allow null as it is a source for the ETL developement. UNQKEY will remail unique i.e. not null once the...

Please provide table def and sample data in a ready to use format as described in the first link in my signature.

Also, please include your expected result based on your...

It's not the ORDER BY by itself causing the long duration.

It's the combination of TOP x ORDER BY.

In order to find the top 10 rows all rows need to...

What have you tried so far and where did you get stuck?

Your request that we should write the stored procedure for you is quite demanding.... We're here to assist you,...

Just a warning: Depending on how the proc is written you may end up with heavy locking, blocking or even deadlocking if running the code in parallel. This may cause...

Syed Jahanzaib Bin hassan (7/1/2011)

---

Dont use Clustered index column in the NonClustered index this is overhead and leaf level of this nonclustered index will be same as your index when...

I second opc.three.

In the scenario you describe I might use a "master proc" with the general structure as shown.

Depending on the logic inside each IF block I'd either have...

30k rows are not that much by just looking at the number itself. It depends on how frequent the table is queried and how many indexes are in place. If...

Assuming you're looking for the ISO week number the DATEFIRST approach won't help you at all. The ISO week number depends on the weekday of January 1st.

Since you're using SS2K5...

The classic answer: "It depends" 🙂

INDEX_2 might be chosen by a query since it's more narrow than the original table. But since there are only two more columns (8 byte...

You could change the query to to

AND psf.assetRegion_ID >= @assetregion_id_min

AND psf.assetRegion_ID <= @assetregion_id_max

set @assetregion_id_min to 1, 2 or @assetRegion_ID and

@assetregion_id_max to 2147483647 (max value for an INT data type)...