See Re: Record ID generator question Message #611104 for how to get the next value for the second part of the key. To combine two parts into one key you can use something like

SELECT CAST( 35256 AS binary(2)) + CAST( 2225666 AS binary(4))

>Would somebody share a SQL Server stored procedure that creates compound primary keys? I know how to do it in VFP but not T-SQL.
>
>The first part of the key is a site number packed in two bytes and is kept in a one record table. The second part is a running number for each table, kept in a keys table, and packed into four bytes.
>
>TIA,
>
>Alex