Situation #1

Use a Derived Table (Or a Common Table Expression [CTE] in SqlServer 2005) and lose the variables:

SELECT
  t1.col1, t1.col2, dt.col1, dt.col2 
FROM
  Customer t1
CROSS JOIN 
 (
    SELECT TOP 1 col1, col2
    FROM GeneralInfo t2
 \) dt
WHERE t1.status > 0

Situation #2

Discussion is incomplete without mentioning effect of concurrency & transaction isolation. Yes, splitting into smaller queries may reduce lock hold time and reduce contention, but what does that gain if the end result is *different* than 1 large statement, because other processes changed data between the steps of a broken out process ?

Pretty good article, with some nice tips.  Regarding PW's note, it's a valid option, but no real performance increase over using variables.

Situation 3 looks dicey, though.  I understand what you're trying to do, and if your "Where" clause is expensive it could perform better.  What you really need to do in this case is populate a seed table with the PK values, then use this to move rows around.  It will perform better than moving the whole row one extra time, especially if it's a big row.

Situation 4 is nice...I've been harping on the uselessness of fast forward cursors for a "while" (oops....  I usually use a different form, though; The performance is significantly better.

----------------------------------------------------------------------------------

declare @name sysname

While 1 = 1

 begin

   select top 1 @Name = name
 from master..sysdatabases
 where Name > isnull(@Name, '')
 order by Name

 if @@RowCount = 0 Break

 select @Name Name

 end

Good solution as well. And achieve the same point.

Second comment. I am writing this article mainly for the application developers and/or report writers. Most of them have no idea what mean effect of concurrency & transaction isolation. But they need a general guide line to make better performance.

Very nice decision. But in my case this is just a quick example. 

This article I placed as a general guideline for the developers because I am tired with obvious performance glitches. Probably some parts can be done better. But my main point was to show that small thoughts can make a difference.

You can rename indexes and other objects:

use pubs

if object_ID('test_rename_HIS') is not null drop table test_rename_HIS
if object_ID('test_rename_ARC') is not null drop table test_rename_ARC

create table test_rename_HIS
  (ID int Not Null constraint PK_HIS Primary Key,
   Name varchar(255))

go

create index I_HIS on test_rename_HIS (Name)

go

exec sp_rename
 @objname =  'test_rename_HIS' ,
    @newname =  'test_rename_ARC'  ,
    @objtype =  'Object'

go

exec sp_rename
 @objname =  'PK_HIS' ,
    @newname =  'PK_ARC'  ,
    @objtype =  'Object'

go

exec sp_rename
 @objname =  'test_rename_ARC.I_HIS' ,
    @newname =  'I_ARC'  ,
    @objtype =  'Index'

go

DBCC FREEPROCCACHE

Yes, I can do it. Rename indexes, tables, databases, and so on.

But the main point of the article is to show that a simple change may have a big effect. And another point, that developer who is writing an archiving process have no permissions to rename objects.  Plus this is only a small part of the process. Process consists with tonns of pages of code and this is the only one small part of the logic.

I do understand your point. But as an example I get criteria (not the best one) just to illustrate the point.

If you look my first performance article http://www.sqlservercentral.com/columnists/lPeysakhovich/overviewofperformance.asp)

you will see that I am using the idea you suggesting to populate a seed table with the PK values