24 bit integer

I was recently in a situation where every byte was important, but I needed a lookup table that had more than 65,536 unique values (16-bit). Rather than use a 32-bit integer, I could save a byte per record by using a 24-bit integer, which doesn't exist, so I used a BINARY(3) instead. Since you can't do an IDENTITY on a binary, I made my own.

In this example, it's a lookup table where there's a case-sensitive string value, and an Idx column that's used by multiple other tables with multiple columns, to look up string values.

Probably not too useful in most scenarios, but it'll save me quite a big of storage, so it made sense in my project.

create type dbo.LookupValueType as table (
    LookupValue varchar(300) collate Latin1_General_CS_AS

create procedure dbo.SaveLookups (
    @LookupValues dbo.LookupValueType readonly
    set nocount on;

    begin transaction;
    begin try
        -- Filter to be only new distinct items
        declare @ToInsert table (idx int not null identity, val varchar(400) collate Latin1_General_CS_AS);
        insert @ToInsert
        select distinct LookupValue from @LookupValues where LookupValue not in (select LookupValue from dbo.Lookups);

        declare @NumRows int;
        select @NumRows = count(1) from @ToInsert;

        -- Create a numbers table from 1 to max(binary(3)), including only values that
        -- are not already in the table, and only creating as many as are needed
        declare @Numbers table (idx binary(3) not null primary key);
            q1 as (select 1 val union all select 1 union all select 1 union all select 1)
            ,q2 as (select 1 val from q1 a, q1 b, q1 c, q1 d)
            ,q3 as (select 1 val from q2 a, q2 b, q2 c)
            ,tally as (select row_number() over (order by val) [rownum] from q3)
        insert @Numbers
        select top (@NumRows) rownum from tally
        where rownum not in (select Idx from dbo.Lookups)

        -- Join the new values against the numbers table to insert the new values
        ;with indexed as (
            select n.idx, row_number() over (order by n.idx) [rownum]
            from @Numbers n
        insert dbo.Lookups (Idx, LookupValue)
        select indexed.idx, toi.val
        from @ToInsert toi
        join indexed on toi.idx = indexed.rownum;

        select LookupValue, Idx
        from dbo.Lookups;

        commit transaction;
    end try
    begin catch
        if @@trancount > 0
            rollback transaction;
        declare @errorMessage nvarchar(4000) = error_message();
        declare @errorSeverity int = error_severity();
        declare @errorState int = error_state();
        declare @procName nvarchar(4000) = object_name(@@procid);

        raiserror(@errorMessage, @errorSeverity, @errorState);
    end catch;