SO, Sql Server - rextester

Language: Layout:

create table #myTable (
    [Name] varchar(20)
    , [Number] int
)

insert into #myTable
SELECT 'John' AS [Name], 1 AS [Number] UNION ALL
SELECT 'John' AS [Name], 3 AS [Number] UNION ALL
SELECT 'John' AS [Name], NULL AS [Number] UNION ALL
SELECT 'John' AS [Name], NULL AS [Number] UNION ALL
SELECT 'John' AS [Name], NULL AS [Number] UNION ALL
SELECT 'John' AS [Name], 7 AS [Number] UNION ALL
SELECT 'Mike' AS [Name], 1 AS [Number] UNION ALL
SELECT 'Mike' AS [Name], 2 AS [Number] UNION ALL
SELECT 'Mike' AS [Name], NULL AS [Number] UNION ALL
SELECT 'Mike' AS [Name], 4 AS [Number] UNION ALL
SELECT 'Mike' AS [Name], NULL AS [Number] UNION ALL
SELECT 'Mike' AS [Name], 6 AS [Number] UNION ALL
SELECT 'John' AS [Name], NULL AS [Number] UNION ALL
SELECT 'Mike' AS [Name], 9 AS [Number] UNION ALL
SELECT 'Mike' AS [Name], 14 AS [Number] UNION ALL
SELECT 'Mike' AS [Name], 15 AS [Number] UNION ALL
SELECT 'Jimm' AS [Name], 1 AS [Number] UNION ALL
SELECT 'Jimm' AS [Name], 4 AS [Number] UNION ALL
SELECT 'Jimm' AS [Name], 5 AS [Number] UNION ALL
SELECT 'Jimm' AS [Name], 10 AS [Number] UNION ALL
SELECT 'Jimm' AS [Name], 15 AS [Number] UNION ALL
SELECT 'Jimm' AS [Name], NULL AS [Number] UNION ALL
SELECT 'Jimm' AS [Name], NULL AS [Number] UNION ALL
SELECT 'Jimm' AS [Name], NULL AS [Number] UNION ALL
SELECT 'Jimm' AS [Name], NULL AS [Number] UNION ALL
SELECT 'Jimm' AS [Name], NULL AS [Number] UNION ALL
SELECT 'Jimm' AS [Name], NULL AS [Number] UNION ALL
SELECT 'Jimm' AS [Name], NULL AS [Number] UNION ALL
SELECT 'Jimm' AS [Name], NULL AS [Number] UNION ALL
SELECT 'Jimm' AS [Name], NULL AS [Number] UNION ALL
SELECT 'Jimm' AS [Name], NULL AS [Number] UNION ALL
SELECT 'Jimm' AS [Name], NULL AS [Number] UNION ALL
SELECT 'Jimm' AS [Name], NULL AS [Number] UNION ALL
SELECT 'Jimm' AS [Name], NULL AS [Number]

;with gaps_between_numbers as (
    select
        t.Name, cnt = t.nextNum - t.Number - 1, dr = dense_rank() over (partition by t.Name order by t.Number)
        , rn = row_number() over (partition by t.Name order by t.Number)
    from (
        select 
            Name, Number, nextNum = isnull(lead(Number) over (partition by Name order by number), Number + 1)
        from 
            #myTable
        where
            Number is not null
    ) t
    join master.dbo.spt_values v on t.nextNum - t.Number - 1 > v.number
    where
        t.nextNum - t.Number > 1
        and v.type = 'P'
)
, ordering_nulls as (
    select
        t.Name, dr = isnull(q.dr, 2147483647)
    from (
        select
            Name, rn = row_number() over (partition by Name order by (select 1))
        from
            #myTable
        where 
            Number is null
    ) t
    left join gaps_between_numbers q on t.Name = q.Name and t.rn = q.rn
)
, ordering_not_null_numbers as (
    select
        Name, Number, rn = dense_rank() over (partition by Name order by gr)
    from (
        select
            Name, Number, gr = sum(lg) over (partition by Name order by Number)
        from (
            select
                Name, Number, lg = iif(Number - lag(Number) over (partition by Name order by Number) = 1, 0, 1)
            from
                #myTable
            where
                Number is not null
        ) t
    ) t
)

select
    Name, Number
    , Desidered_col = row_number() over (partition by Name order by rn, isnull(Number, 2147483647))
from (
    select * from ordering_not_null_numbers
    union all
    select Name, null, dr from ordering_nulls

) t

View schema
Execution time: 0,03 sec, rows selected: 34, rows affected: 34, absolute service time: 0,19 sec