none
Varchar Auto Number Primary Key RRS feed

  • Question

  • hi,

    i want a auto primary key in my table look like this

    AAA

    AAB

    AAC

    AAD

    ...

    AAZ

    ABA

    ...

    how i do this

    thanks

    Mohsin


    Saturday, August 10, 2013 10:37 PM

Answers

  •   /****************************************************************************************************************
      By the nature, it is certainly not a good idea to use a string column for an automatic changing primary key since this automatic unique
      string has to be coming from an automatic changing number or some procedural computation. If we are not using a CLR self-created function
      then the option to me it has to be derived from an Identity number. My solution is therefore based on this concept.
      ***********************************************************************************************************************/
      --1. Take the question as an example, if we want to create a 3 character automatic increasing ID like, AAA, AAB,...ZZZ etc.
      --the idea is to have an identity column in the table first. (I stick the solution with pre-SQL server 2012 so I am not using
      --the new sequence data here.
    
      --Create A table with an Identity column with no primary key defined.
    	If Object_ID('dbo.MyTable ', 'U') Is Not Null
    	Begin
    		Drop Table dbo.MyTable ;
    	End
    
      Create Table MyTable (
    						  ID Int identity(0,1) Not Null Check(ID < Power(26,3)) --(since the 3 character ID maximum number is 17576)
    						  , MyCol varchar(5) Null
    						);
    
    	--Add an persisted computed column based on the idenity id column to meet the automatic character ID purpose
      Alter Table [dbo].[MyTable] Add Character_ID as
      Char(65 + (([ID] % Power(26,3)) / Power(26,2)))   --the first character
      + 
      Char(65 + (([ID] % Power(26,2)) / Power(26,1)))   --the secone character
      + 
      Char(65 + (([ID] % Power(26,1)) / Power(26,0)))  --the third Character
      Persisted not null;
    
      --Create add a primary key constraint on the table based on the persisted computed column.
      ALTER TABLE dbo.MyTable ADD CONSTRAINT PK_MyTable PRIMARY KEY CLUSTERED (Character_ID);
    
      --2. Test the table above.
      Truncate table MyTable;
      Insert into Mytable
      SELECT TOP(Power(26,3))  '----'
      FROM	master.sys.all_columns A
    			CROSS JOIN 
    		master.sys.all_columns B;
      Select Character_ID, MyCol, ID
      From	MyTable
      /****************************************************************************
      Sample data:
    	ID	MyCol	Character_ID
    	0	----	AAA
    	1	----	AAB
    	2	----	AAC
    	3	----	AAD
    	4	----	AAE
      ****************************************************************************/
    
      --3. A bigger picture, since the character ID may not only 3 characters, we indeed can parameterize this as below to cater for any character
      --size of the character ID column (certain needs to be within the biggest of INTEGER value 2,147,483,647)
      Declare @NumberofCharacters tinyint;
      Declare @SQL nvarchar(Max);
      Set @NumberofCharacters = 4; --change the number to the max number of characters in your string id
    
      If Object_ID('dbo.MyTable ', 'U') Is Not Null
    	Begin
    		Drop Table dbo.MyTable ;
    	End
    
    
      Set @SQL = N'Create Table MyTable 
    						(
        						  ID Int identity(0,1) Not Null Check(ID < Power(26,' + Cast(@NumberofCharacters as nvarchar(2)) + '))
    							, MyCol varchar(5) Null
    						);'
      Exec(@SQL);
    
      Set @SQL = ''
      While @NumberofCharacters > 0
    		Begin
    			Set @SQL = @SQL +  '+ Char(65 + (([ID] % Power(26,' + Cast(@NumberofCharacters as nvarchar(2)) + ')) / Power(26,' + Cast(@NumberofCharacters - 1 as nvarchar(2)) + '))) '
    			Set @NumberofCharacters = @NumberofCharacters - 1
    		End
       Set @SQL = 'Alter Table [dbo].[MyTable] Add Character_ID as ' + Stuff(@SQL,1,1,'') + 'Persisted not null;'
    
       Exec(@SQL);
       
       ALTER TABLE dbo.MyTable ADD CONSTRAINT PK_MyTable PRIMARY KEY CLUSTERED (Character_ID);


    View Steven Wang's profile on LinkedIn | Blog: MSBICOE.com | MCSE: Data Platform | MCITP: BI, SQL Developer & DBA

    Hate to mislead others, if I'm wrong slap me. Thanks!

    • Marked as answer by Mohsin Akhlaq Thursday, November 14, 2013 5:59 PM
    Sunday, August 11, 2013 7:39 AM
  • Not a good idea.

    You can generate that ALPHA sequence dynamically any time using ROW_NUMBER().


    Kalman Toth Database & OLAP Architect sqlusa.com
    New Book / Kindle: Pass SQL Exam 70-461 & Job Interview: Programming SQL Server 2012


    Saturday, August 10, 2013 11:35 PM
    Moderator
  • I agree with Kalman: Not a good idea.

    However, if you are using SQL Server 2012, you might be able to use a sequence object and translate a sequence of numbers into a sequence of alphabetic strings.  To me it looks like the key idea is translating the number series from base 10 (decimal) to base 26 (alphabetic).  Here is an example; it is a bit clunky, but ... :

    --drop sequence dbo.test_Seq;
    --create sequence dbo.test_Seq start with 123456789
    declare @test table(seqValue int);
    insert into @test
    select 
      next value for dbo.test_Seq
    from master.dbo.spt_Values with(nolock)
    where name is null
      and number <= 3;
    select
      seqValue,
      substring(base36Digit, 1 + a1, 1)
        + substring(base36Digit, 1 + a2, 1)
        + substring(base36Digit, 1 + a3, 1)
        + substring(base36Digit, 1 + a4, 1)
        + substring(base36Digit, 1 + a5, 1)
        + substring(base36Digit, 1 + a6, 1)
        + substring(base36Digit, 1 + a7, 1)
      as charSeq
    from @test
    cross apply
    ( select
        'abcdefghijklmnopqrstuvwxyz',
        seqValue/308915776,
        (seqValue % 308915776)/11881376,
        (seqValue % 11881376)/456976,
        (seqValue % 456976)/17576,
        (seqValue % 17576)/676,
        (seqValue % 676)/26,
        seqValue % 26
    ) ua1(base36Digit, a1, a2, a3, a4, a5, a6, a7);
    /* ----------  Sample Output:  --------
    seqValue    charSeq
    ----------- -------
    123456821   akkeelh
    123456822   akkeeli
    123456823   akkeelj
    123456824   akkeelk
    ------------------------------------ */


    Saturday, August 10, 2013 11:50 PM
    Moderator
  • Hello Mohsin,

    I agree with Steven, it's an easy to convert a numeric id to the required char "id" with some simple math operations:

    CREATE TABLE dbo.test
        (ID smallint IDENTITY(0, 1)
        ,MyVALUE VARCHAR(100));
    GO
    
    ALTER TABLE dbo.test Add CharPK as
         CHAR(65 + (ID / 676) % 26) +
         CHAR(65 + (ID / 26) % 26) +
         CHAR(65 + (ID) % 26)
      PERSISTED NOT NULL;
    GO
    
    -- INSERT test data
    SET NOCOUNT ON;
    DECLARE @loop smallint;
    SET @loop = 1;
    
    WHILE @loop < 1000
    BEGIN
        INSERT INTO dbo.test (MyVALUE) 
        VALUES ('Datarow ' + CONVERT(varchar(10), @loop));
        SET @loop = @loop + 1;
    END
    GO
    
    SELECT *
    FROM dbo.test

    Result:


    Olaf Helper

    [ Blog] [ Xing] [ MVP]

    • Marked as answer by Mohsin Akhlaq Thursday, November 14, 2013 5:59 PM
    Sunday, August 11, 2013 3:32 PM

All replies

  • Not a good idea.

    You can generate that ALPHA sequence dynamically any time using ROW_NUMBER().


    Kalman Toth Database & OLAP Architect sqlusa.com
    New Book / Kindle: Pass SQL Exam 70-461 & Job Interview: Programming SQL Server 2012


    Saturday, August 10, 2013 11:35 PM
    Moderator
  • Are gaps acceptable as rows are inserted?  Are you expecting gaps after rows are deleted?  What about values greater than ZZZ?


    Dan Guzman, SQL Server MVP, http://www.dbdelta.com

    Saturday, August 10, 2013 11:48 PM
  • I agree with Kalman: Not a good idea.

    However, if you are using SQL Server 2012, you might be able to use a sequence object and translate a sequence of numbers into a sequence of alphabetic strings.  To me it looks like the key idea is translating the number series from base 10 (decimal) to base 26 (alphabetic).  Here is an example; it is a bit clunky, but ... :

    --drop sequence dbo.test_Seq;
    --create sequence dbo.test_Seq start with 123456789
    declare @test table(seqValue int);
    insert into @test
    select 
      next value for dbo.test_Seq
    from master.dbo.spt_Values with(nolock)
    where name is null
      and number <= 3;
    select
      seqValue,
      substring(base36Digit, 1 + a1, 1)
        + substring(base36Digit, 1 + a2, 1)
        + substring(base36Digit, 1 + a3, 1)
        + substring(base36Digit, 1 + a4, 1)
        + substring(base36Digit, 1 + a5, 1)
        + substring(base36Digit, 1 + a6, 1)
        + substring(base36Digit, 1 + a7, 1)
      as charSeq
    from @test
    cross apply
    ( select
        'abcdefghijklmnopqrstuvwxyz',
        seqValue/308915776,
        (seqValue % 308915776)/11881376,
        (seqValue % 11881376)/456976,
        (seqValue % 456976)/17576,
        (seqValue % 17576)/676,
        (seqValue % 676)/26,
        seqValue % 26
    ) ua1(base36Digit, a1, a2, a3, a4, a5, a6, a7);
    /* ----------  Sample Output:  --------
    seqValue    charSeq
    ----------- -------
    123456821   akkeelh
    123456822   akkeeli
    123456823   akkeelj
    123456824   akkeelk
    ------------------------------------ */


    Saturday, August 10, 2013 11:50 PM
    Moderator
  •   /****************************************************************************************************************
      By the nature, it is certainly not a good idea to use a string column for an automatic changing primary key since this automatic unique
      string has to be coming from an automatic changing number or some procedural computation. If we are not using a CLR self-created function
      then the option to me it has to be derived from an Identity number. My solution is therefore based on this concept.
      ***********************************************************************************************************************/
      --1. Take the question as an example, if we want to create a 3 character automatic increasing ID like, AAA, AAB,...ZZZ etc.
      --the idea is to have an identity column in the table first. (I stick the solution with pre-SQL server 2012 so I am not using
      --the new sequence data here.
    
      --Create A table with an Identity column with no primary key defined.
    	If Object_ID('dbo.MyTable ', 'U') Is Not Null
    	Begin
    		Drop Table dbo.MyTable ;
    	End
    
      Create Table MyTable (
    						  ID Int identity(0,1) Not Null Check(ID < Power(26,3)) --(since the 3 character ID maximum number is 17576)
    						  , MyCol varchar(5) Null
    						);
    
    	--Add an persisted computed column based on the idenity id column to meet the automatic character ID purpose
      Alter Table [dbo].[MyTable] Add Character_ID as
      Char(65 + (([ID] % Power(26,3)) / Power(26,2)))   --the first character
      + 
      Char(65 + (([ID] % Power(26,2)) / Power(26,1)))   --the secone character
      + 
      Char(65 + (([ID] % Power(26,1)) / Power(26,0)))  --the third Character
      Persisted not null;
    
      --Create add a primary key constraint on the table based on the persisted computed column.
      ALTER TABLE dbo.MyTable ADD CONSTRAINT PK_MyTable PRIMARY KEY CLUSTERED (Character_ID);
    
      --2. Test the table above.
      Truncate table MyTable;
      Insert into Mytable
      SELECT TOP(Power(26,3))  '----'
      FROM	master.sys.all_columns A
    			CROSS JOIN 
    		master.sys.all_columns B;
      Select Character_ID, MyCol, ID
      From	MyTable
      /****************************************************************************
      Sample data:
    	ID	MyCol	Character_ID
    	0	----	AAA
    	1	----	AAB
    	2	----	AAC
    	3	----	AAD
    	4	----	AAE
      ****************************************************************************/
    
      --3. A bigger picture, since the character ID may not only 3 characters, we indeed can parameterize this as below to cater for any character
      --size of the character ID column (certain needs to be within the biggest of INTEGER value 2,147,483,647)
      Declare @NumberofCharacters tinyint;
      Declare @SQL nvarchar(Max);
      Set @NumberofCharacters = 4; --change the number to the max number of characters in your string id
    
      If Object_ID('dbo.MyTable ', 'U') Is Not Null
    	Begin
    		Drop Table dbo.MyTable ;
    	End
    
    
      Set @SQL = N'Create Table MyTable 
    						(
        						  ID Int identity(0,1) Not Null Check(ID < Power(26,' + Cast(@NumberofCharacters as nvarchar(2)) + '))
    							, MyCol varchar(5) Null
    						);'
      Exec(@SQL);
    
      Set @SQL = ''
      While @NumberofCharacters > 0
    		Begin
    			Set @SQL = @SQL +  '+ Char(65 + (([ID] % Power(26,' + Cast(@NumberofCharacters as nvarchar(2)) + ')) / Power(26,' + Cast(@NumberofCharacters - 1 as nvarchar(2)) + '))) '
    			Set @NumberofCharacters = @NumberofCharacters - 1
    		End
       Set @SQL = 'Alter Table [dbo].[MyTable] Add Character_ID as ' + Stuff(@SQL,1,1,'') + 'Persisted not null;'
    
       Exec(@SQL);
       
       ALTER TABLE dbo.MyTable ADD CONSTRAINT PK_MyTable PRIMARY KEY CLUSTERED (Character_ID);


    View Steven Wang's profile on LinkedIn | Blog: MSBICOE.com | MCSE: Data Platform | MCITP: BI, SQL Developer & DBA

    Hate to mislead others, if I'm wrong slap me. Thanks!

    • Marked as answer by Mohsin Akhlaq Thursday, November 14, 2013 5:59 PM
    Sunday, August 11, 2013 7:39 AM
  • Hello Mohsin,

    I agree with Steven, it's an easy to convert a numeric id to the required char "id" with some simple math operations:

    CREATE TABLE dbo.test
        (ID smallint IDENTITY(0, 1)
        ,MyVALUE VARCHAR(100));
    GO
    
    ALTER TABLE dbo.test Add CharPK as
         CHAR(65 + (ID / 676) % 26) +
         CHAR(65 + (ID / 26) % 26) +
         CHAR(65 + (ID) % 26)
      PERSISTED NOT NULL;
    GO
    
    -- INSERT test data
    SET NOCOUNT ON;
    DECLARE @loop smallint;
    SET @loop = 1;
    
    WHILE @loop < 1000
    BEGIN
        INSERT INTO dbo.test (MyVALUE) 
        VALUES ('Datarow ' + CONVERT(varchar(10), @loop));
        SET @loop = @loop + 1;
    END
    GO
    
    SELECT *
    FROM dbo.test

    Result:


    Olaf Helper

    [ Blog] [ Xing] [ MVP]

    • Marked as answer by Mohsin Akhlaq Thursday, November 14, 2013 5:59 PM
    Sunday, August 11, 2013 3:32 PM
  • Not a good idea for several reasons. We have the ANSI/ISO Standard CREATE SEQUENCE construct now, so yu can make the SQL engine do the work for you. What you do need is

    foo_id CHAR(3) NOT NULL CHECK (foo_id LIKE '[A-Z][A-Z][A-Z]')  PRIMARY KEY

    In a good schema, we discover keys more often than generate them. Have you ever invented an EAN, VIN, UPC or other industry standard code? Of course not! So, the better question is why do you need this? Ther are only (26^3= 17576 trigrams. That is a small table! You could generate them all at once and use them as needed. 

    INSERT INTO Foobars (foo_id, foo_seq)
    SELECT A1.letter + A2.letter + A3.letter,
           ROW_NUMBER () 
           OVER (ORDER BY 1.letter, A2.letter, A3.letter)
      FROM Alphabet;



    --CELKO-- Books in Celko Series for Morgan-Kaufmann Publishing: Analytics and OLAP in SQL / Data and Databases: Concepts in Practice Data / Measurements and Standards in SQL SQL for Smarties / SQL Programming Style / SQL Puzzles and Answers / Thinking in Sets / Trees and Hierarchies in SQL

    Sunday, August 11, 2013 11:06 PM