How To Create a Custom Alphanumeric Primary key in Sql Server 2005

i write this alghorithm a and it's correct: Tables were :

create table PrimKeyTest (primarykeycolumn varchar(8), nextcolumn int)   
GO   
insert into PrimKeyTest values ('P09-0001', 1)   
GO   

and My function is :

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
CREATE function [dbo].[GetSpecialPrimaryKey](@yearvalue int)   
returns nvarchar(8)   
as   

begin   

    declare @maxkey varchar(4)   
    declare @maxLength int, @maxkeylength int   

    set @maxLength = 4   

select @maxkey = ISNULL(cast(max(cast(substring(primaryKeycolumn, 5, 4) as integer)+1) as varchar),'1')   
 from PrimKeyTest   
        where substring(primaryKeycolumn, 2, 2) = substring(convert(varchar, @yearvalue), 3, 2)   

    set @maxkeylength = len(@maxkey)   

    while @maxkeylength < @maxLength   
    begin   
        set @maxkey = '0' + @maxkey   
        set @ma开发者_开发知识库xkeylength = len(@maxkey)   
    end   

    return 'P' + substring(convert(varchar, @yearvalue), 3, 2) + '-' + @maxkey   

end  

Now when i delete last row of this table ,new last record give correct number eg. P09-0001 P09-0002 P09-0003 P09-0004 P09-0005 but when i delete 2nd row of this table order of primary column has incorrect eg. P09-0001 P09-0003 P09-0004 P09-0005 can you help me? i want this: P09-0001 P09-0002 P09-0003 P09-0004

This is actually not a great approach to dealing with primary keys. Doing this means "realigning" all of your pkeys whenever one is deleted (except when it's the last.) Doing this could be a complex, costly and error-prone process. For example, you have a pkey in this table which will probably be referenced via a foreign key from other tables. If you change the value of the pkey in the first table then you also have to change it in all the other tables that reference it. This means dropping any constraints for the duration of the change etc.

It looks like you're trying to create an identifier that will most likely be presented to the end user. You can go ahead and use your function to do that, BUT do not make it a primary key. Use an auto-incrementing column as the primary key and the 'P09-N' value as a separate field. Then, if you want to modify the values you can do so without affecting the rest of your table design.

Now to update the identifier values for the table whenever one is deleted you'll probably need to use a cursor in a stored procedure. Here's a good overview on cursors. You could also use CTEs (Common Table Expressions) to do the updating.

Here is a cursor example where Col1 is your pkey and Col2 is the identifier you want to change:

begin tran -- it's important to wrap this in a transaction!

declare @counter int
set @counter = 1
declare @val varchar(50)

DECLARE crs CURSOR 
FOR SELECT Col1 FROM TblTest ORDER By Col1

OPEN crs 
FETCH NEXT FROM crs INTO @val
WHILE @@FETCH_STATUS = 0
BEGIN

    UPDATE TblTest
    SET Col2 = 'P09-' + cast(@counter as varchar(50))
    WHERE Col1 = @val

    SET @counter = @counter + 1

    FETCH NEXT FROM crs INTO @val
END
CLOSE crs 
DEALLOCATE crs 

commit tran

I didn't do the leading zero logic but you can Google for that pretty easily.