update with variable and stop at some predefined target

When using update with variables, it seem the variable is always evaluated first before any other filed.
Also it will not respect the condition using variable with the where clause. Put another way, no, you ca not use variable to dynamically control the data being updated. One workaround is to set condition inside set section.
For example. Try this:


USE [Test]
GO

/****** Object:  Table [dbo].[UpdateTest]    Script Date: 8/22/2012 10:02:10 AM ******/
if (exists (select 1 from sys.Tables where name='UpdateTest'))
    DROP TABLE [dbo].[UpdateTest]
GO

/****** Object:  Table [dbo].[UpdateTest]    Script Date: 8/22/2012 10:02:10 AM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

SET ANSI_PADDING ON
GO

CREATE TABLE [dbo].[UpdateTest](
    [id] [int] IDENTITY(1,1) NOT NULL,
    [count] [int] NOT NULL,
    [value] [int] NOT NULL,
 CONSTRAINT [PK_UpdateTest] PRIMARY KEY CLUSTERED
(
    [id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

SET ANSI_PADDING OFF
GO


SET IDENTITY_INSERT [dbo].[UpdateTest] ON

GO
INSERT [dbo].[UpdateTest] ([id], [count], [value]) VALUES (1, 5, 0)
GO
INSERT [dbo].[UpdateTest] ([id], [count], [value]) VALUES (2, 12, 0)
GO
INSERT [dbo].[UpdateTest] ([id], [count], [value]) VALUES (3, 20, 0)
GO
INSERT [dbo].[UpdateTest] ([id], [count], [value]) VALUES (4, 1, 0)
GO
INSERT [dbo].[UpdateTest] ([id], [count], [value]) VALUES (5, 5, 0)
GO
INSERT [dbo].[UpdateTest] ([id], [count], [value]) VALUES (6, 98, 0)
GO
INSERT [dbo].[UpdateTest] ([id], [count], [value]) VALUES (7, 98, 0)
GO
SET IDENTITY_INSERT [dbo].[UpdateTest] OFF
GO


declare @sum int, @TagetHitTimes int
set @sum=0
set @TagetHitTimes=0

--update [UpdateTest] set val1=0

--select * from [UpdateTest]

update [UpdateTest]
set  
    -- variable seems to be computed first
    -- only add sum when target is not achived
    -- stop adding when target is achived
    @sum=case when @sum>24 then @sum else @sum+[count] end
    -- used to track the first time target is achived
    ,@TagetHitTimes=@TagetHitTimes+(case when @sum>24 then 1 else 0 end)
    ,[value]=case when  @TagetHitTimes<=1 then [value]+@sum else 0 end

select * from [UpdateTest]

temp table vs table varialbe

(http://databases.aspfaq.com/database/should-i-use-a-temp-table-or-a-table-variable.html)
table varialbe:

• has some limitation compared to temp table
• has less recompilation
• not good choice for large data (row number is always 1 and no stats is available)
• can be used with DMV tables
• etc

Missing Index From SQL Server MDV

(From
http://blogs.msdn.com/b/bartd/archive/2007/07/19/are-you-using-sql-s-missing-index-dmvs.aspx
)



SELECT

    migs.avg_total_user_cost * (migs.avg_user_impact / 100.0) * (migs.user_seeks + migs.user_scans) AS improvement_measure

    ,db_name(mid.database_id) as DatabaseName

    ,object_name( mid.[object_id], mid.database_id) as TableName

    ,'CREATE INDEX [missing_index_' + CONVERT (varchar, mig.index_group_handle) + '_' + CONVERT (varchar, mid.index_handle)

    + '_' + LEFT (PARSENAME(mid.statement, 1), 32) + ']'

    + ' ON ' + mid.statement

    + ' (' + ISNULL (mid.equality_columns,'')

    + CASE WHEN mid.equality_columns IS NOT NULL AND mid.inequality_columns IS NOT NULL THEN ',' ELSE '' END

    + ISNULL (mid.inequality_columns, '')

    + ')'

    + ISNULL (' INCLUDE (' + mid.included_columns + ')', '') AS create_index_statement

    ,migs.*

    , mid.[object_id]

    FROM    sys.dm_db_missing_index_groups mig

            INNER JOIN sys.dm_db_missing_index_group_stats migs ON migs.group_handle = mig.index_group_handle

            INNER JOIN sys.dm_db_missing_index_details mid ON mig.index_handle = mid.index_handle

    WHERE    migs.avg_total_user_cost * (migs.avg_user_impact / 100.0) * (migs.user_seeks + migs.user_scans) > 10

    ORDER BY migs.avg_total_user_cost * migs.avg_user_impact * (migs.user_seeks + migs.user_scans) DESC