How to write query generate unique Id for concate both columns search parts and company id ?

ahmed salah 3,216 Reputation points
2021-02-22T16:47:51.637+00:00

I work on sql server 2012 . I face issue I can't generate unique Id from merge or concave both columns

Search Parts and Company ID and generated Id must be on column Generated ID

to be easier and fast on search

so Please How to generate unique Id from both column Search Parts and Company ID
and if both column repeated value both get same Id
so please How to do that

AS Example

SearchParts CompanyId GeneratedId
A5ghf7598f7GGHYUTYA 3456 901
when concate both columns Search Parts and Company Id generate Id
and take same Id in case of repeated
but main idea generate number unique for both column search parts and company id

every search parts text and CompanyId must be unique and take unique Id on generated Id

create table #partswithcompany
 (
 SearchParts  nvarchar(200),
 CompanyId  int,
 GeneratedId INT
 )
 insert into #partswithcompany (SearchParts,CompanyId,GeneratedId)
 values
 ('A5ghf7598fdmlcpghjk',1234,NULL),
 ('AKLJGSA7598fdmlcpghjk',5870,NULL),
 ('A5ghfJKKJGHHGghjk',9081818,NULL),
 ('KHJLFFS8fdmlcpghjk',123345,NULL),
 ('A5ghf7598f7GGHYUTYA',3456,NULL),
 ('A5ghfJKKJGHHGghjk',9081818,NULL),
  ('A5ghf7598f7GGHYUTYA',3456,NULL),
  ('A5ghf7598f7GGHYUTYA',3456,NULL)

Expected Result

    SearchParts CompanyId   GeneratedId
    A5ghf7598fdmlcpghjk 1234    5
    AKLJGSA7598fdmlcpghjk   5870    9
    A5ghfJKKJGHHGghjk   9081818 8
    KHJLFFS8fdmlcpghjk  123345  6
    A5ghf7598f7GGHYUTYA 3456    7
    A5ghfJKKJGHHGghjk   9081818 8
    A5ghf7598f7GGHYUTYA 3456    7
    A5ghf7598f7GGHYUTYA 3456    7
SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
13,117 questions
Transact-SQL
Transact-SQL
A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
4,583 questions
0 comments No comments
{count} votes

Accepted answer
  1. MelissaMa-MSFT 24,176 Reputation points
    2021-02-23T03:19:51.16+00:00

    Hi @ahmed salah

    Based on Viorel-1's answer, you could create a trigger or procedure to update the GeneratedId if newly created data is inserted tomorrow.

    Please refer below example and check whether it is helpful:

    --insert some new data  
      insert into #partswithcompany (SearchParts,CompanyId,GeneratedId)  
      values  
      ('A5ghf7598fdmlcpghjk',1234,NULL),  
      ('AKLJGSA75fukfgjpghjk',5870,NULL),  
      ('A5ghfJKKJdfhgetrhsGghjk',818,NULL)  
      
    --update GeneratedId with same search parts and company id repeated  
    update a  
    set a.GeneratedId=b.GeneratedId  
    from #partswithcompany A   
    INNER JOIN #partswithcompany B ON A.SearchParts=B.SearchParts AND A.CompanyId = B.CompanyId  
    WHERE a.GeneratedId IS NULL and b.GeneratedId is not null  
      
    --update GeneratedId with different search parts and company id   
    ; with M as  
     (  
         select a.*, dense_rank() over (order by a.SearchParts, a.CompanyId) as n  
         from #partswithcompany A   
        INNER JOIN #partswithcompany B ON A.SearchParts=B.SearchParts AND A.CompanyId = B.CompanyId  
    	WHERE a.GeneratedId IS NULL and b.GeneratedId is  null  
     )  
      update M  
     set GeneratedId = (select max(GeneratedId)+n  from #partswithcompany where GeneratedId is not null)  
    
    select * from #partswithcompany  
    

    Output:

    SearchParts	CompanyId	GeneratedId  
    A5ghf7598fdmlcpghjk	1234	2  
    AKLJGSA7598fdmlcpghjk	5870	4  
    A5ghfJKKJGHHGghjk	9081818	3  
    KHJLFFS8fdmlcpghjk	123345	5  
    A5ghf7598f7GGHYUTYA	3456	1  
    A5ghfJKKJGHHGghjk	9081818	3  
    A5ghf7598f7GGHYUTYA	3456	1  
    A5ghf7598f7GGHYUTYA	3456	1  
    A5ghf7598fdmlcpghjk	1234	2  
    AKLJGSA75fukfgjpghjk	5870	7  
    A5ghfJKKJdfhgetrhsGghjk	818	6  
    

    Best regards
    Melissa


    If the answer is helpful, please click "Accept Answer" and upvote it.
    Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

    0 comments No comments

4 additional answers

Sort by: Most helpful
  1. Viorel 114K Reputation points
    2021-02-22T22:54:05.677+00:00

    If you need this as presumptive method of increasing the performance of queries drastically (using a single index on GeneratedId), then maybe you can also find why the normal queries and indices (on SearchParts and CompanyId) do not give good results.

    To make an non-unique integer from two columns and to implement a hash index you can also consider CHECKSUM or BINARY_CHECKSUM (https://learn.microsoft.com/en-us/sql/t-sql/functions/checksum-transact-sql):

    drop table if exists #partswithcompany  
      
    create table #partswithcompany  
    (  
        SearchParts nvarchar(200),  
        CompanyId  int,  
        GeneratedId as checksum(SearchParts, CompanyId)  
    )  
      
    -- TODO: create indices  
      
    insert into #partswithcompany (SearchParts, CompanyId) values  
    ('A5ghf7598fdmlcpghjk',1234),  
    ('AKLJGSA7598fdmlcpghjk',5870),  
    ('A5ghfJKKJGHHGghjk',9081818),  
    ('KHJLFFS8fdmlcpghjk',123345),  
    ('A5ghf7598f7GGHYUTYA',3456),  
    ('A5ghfJKKJGHHGghjk',9081818),  
    ('A5ghf7598f7GGHYUTYA',3456),  
    ('A5ghf7598f7GGHYUTYA',3456)  
      
    select * from #partswithcompany  
      
    -- sample search by Parts and Company  
      
    declare @parts as nvarchar(200) = 'A5ghf7598f7GGHYUTYA'  
    declare @company as int = 3456  
      
    declare @generated_id int = checksum(@parts, @company)  
      
    select *  
    from #partswithcompany  
    where GeneratedId = @generated_id  
    and SearchParts = @parts  
    and CompanyId = @company  
    
    
    
    
      
    
    1 person found this answer helpful.

  2. Viorel 114K Reputation points
    2021-02-22T17:02:03.107+00:00

    See if the next statement satisfies:

    ;
    with N as
    (
        select *, dense_rank() over (order by SearchParts, CompanyId) as n
        from #partswithcompany
    )
    update N
    set GeneratedId = n
    

    If you need just a query, then use and adjust the select part of above statement.


  3. Tom Phillips 17,716 Reputation points
    2021-02-22T21:57:23.863+00:00

    The simplest thing is to just use an IDENTITY field.

    create table #partswithcompany
      (
      SearchParts  nvarchar(200),
      CompanyId  int,
      GeneratedId INT IDENTITY(1,1)
      )
      insert into #partswithcompany (SearchParts,CompanyId)
      values
      ('A5ghf7598fdmlcpghjk',1234),
      ('AKLJGSA7598fdmlcpghjk',5870),
      ('A5ghfJKKJGHHGghjk',9081818),
      ('KHJLFFS8fdmlcpghjk',123345),
      ('A5ghf7598f7GGHYUTYA',3456),
      ('A5ghfJKKJGHHGghjk',9081818),
       ('A5ghf7598f7GGHYUTYA',3456),
       ('A5ghf7598f7GGHYUTYA',3456)
    
    SELECT *
    FROM #partswithcompany
    

  4. Erland Sommarskog 104.2K Reputation points MVP
    2021-02-22T22:32:49.207+00:00

    suppose today i put on database
    A5ghf7598f7GGHYUTYA 3456
    tomorrow
    A5ghf7598f7GGHYUTYA 3456
    then both must get same id
    so How to do that

    Viorel's solution is good for first round.

    When you get new data, you first lookup if you have the existing pairs of SearchParts and CompanyID to get the ID. For entirely new rows, you use Viorel's query, adding the current max Id.

    0 comments No comments