Custom PrimaryKey Based on IdentityValue

 
CREATE TABLE [dbo].[PrimaryKeyTest](
 [ID] [int] IDENTITY (1,1),
 [PKV] [nchar](50) NOT NULL
) ON [PRIMARY]

GO


DECLARE @CustomPKV NVARCHAR(50)

SELECT @CustomPKV = (
CASE WHEN (ISNULL(MAX(Id), -1) = -1) THEN 1
 WHEN MAX(Id) >= 1 THEN MAX(Id) + 1
END 
)
FROM [dbo].[PrimaryKeyTest]

PRINT @CustomPKV

INSERT INTO [dbo].[PrimaryKeyTest] (PKV) VALUES(@CustomPKV)


SELECT ID, PKV FROM [dbo].[PrimaryKeyTest]


--To CHAR DATA TYPE 'R' +  CAST(
--(CASE WHEN (ISNULL(MAX(Id), -1) = -1) THEN 1
-- WHEN MAX(Id) >= 1 THEN MAX(Id) + 1
--END)  AS VARCHAR)