How to retain and increase the value of variable in SQL server 2008?

You can use a recursive CTE and row_number something like this.

with C1 as
(
  select T1.id,
         T1.x,
         T1.y,
         row_number() over (partition by T1.x order by T1.id) as rn
  from table1 as T1
), C2 as
(
  select C1.id,
         C1.x,
         C1.y,
         C1.rn,
         1 as z
  from C1
  where C1.rn = 1
  union all
  select C1.id,
         C1.x,
         C1.y,
         C1.rn,
         C2.z + case when C1.y <> C2.y then 1 else 0 end
  from C1 
    inner join C2
      on C1.x = C2.x and
         C1.rn = C2.rn + 1
)
select x, y, z
from C2
order by id
option (maxrecursion 0);

Note that using a CTE as the source for a recursive query can have some issues with performance. If that s the case I would recommend you to put the result of the CTE C1 in a temporary table with a primary key on (x, rn).

SQL Fiddle

You can't quite do this with row_number() over (partition by ... order by ...) and a CTE query doesn't seem to work either. I like to avoid using cursors whenever possible, but a cursor may be the very thing for your situation:

declare @tbl table (id int, x char(1), y int, z int null)

declare
  @id int, @x char(1), @y int,
  @x2 char(1) = null, @y2 int = null, @z int

insert into @tbl (id, x, y) values
(1, 'a', 0), (2, 'a', 0), (3, 'a', 0),
(4, 'a', 1),
(5, 'a', 0), (6, 'a', 0),
(7, 'a', 2),
(8, 'a', 3),
(9, 'b', 0),
(10, 'b', 1),
(11, 'b', 0), (12, 'b', 0),
(13, 'b', 2),
(14, 'b', 3),
(15, 'b', 0), (16, 'b', 0)

declare cr cursor for
  select id, x, y
  from   @tbl
  order by  id

open cr
fetch next from cr into @id, @x, @y

while @@fetch_status = 0
begin
  set @z =
    case when @x2 is null or @x <> @x2
      then 1
      else
        case when @y = @y2
          then @z
          else @z + 1
        end
    end

  update @tbl
  set    z = @z
  where  id = @id

  set @x2 = @x
  set @y2 = @y

  fetch next from cr into @id, @x, @y
end

close cr
deallocate cr

select x, y, z
from   @tbl
order by id

Thought I'd throw my solution into the mix:

declare @temp table ([row] int identity,[x] char,[y] int)
insert into @temp([x],[y]) values ('a',0)
insert into @temp([x],[y]) values ('a',0)
insert into @temp([x],[y]) values ('a',0)
insert into @temp([x],[y]) values ('a',1)
insert into @temp([x],[y]) values ('a',0)
insert into @temp([x],[y]) values ('a',0)
insert into @temp([x],[y]) values ('a',2)
insert into @temp([x],[y]) values ('a',3)
insert into @temp([x],[y]) values ('b',0)
insert into @temp([x],[y]) values ('b',1)
insert into @temp([x],[y]) values ('b',0)
insert into @temp([x],[y]) values ('b',0)
insert into @temp([x],[y]) values ('b',2)
insert into @temp([x],[y]) values ('b',3)
insert into @temp([x],[y]) values ('b',0)
insert into @temp([x],[y]) values ('b',0)


declare @temp1 table
(
 [row] int
,[x] char
,[y] int
,[change] int
)
insert into @temp1
(
 [row]
,[x]
,[y]
,[change]
)
select 
 [t1].[row]
,[t1].[x]
,[t1].[y] 
,iif([t1].[x] = [t2].[x] and [t1].[y] <> [t2].[y],1,0) [change]
from @temp [t1]
left join @temp [t2] on [t2].[row] = [t1].[row] - 1

select * from @temp1

select 
 [row]
,[x]
,[y]
,(select sum([change]) from @temp1 [t2] where [t2].[row] <= [t1].[row] and [t2].[x] = [t1].[x]) + 1 [z]
from @temp1 [t1]