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

  • 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
  • I think the best way to handle this would be DENSE_RANK().  Something along the lines of:

     SELECT
    [SearchParts]
    , [CompanyId]
    , DENSE_RANK() OVER (ORDER BY searchparts, companyID) AS [GeneratedID]
    FROM [#partswithcompany];
  • ahmed_elbarbary.2010 wrote:

    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

    This won't make your search faster.  You've been told how to make the search faster both on these forums and over on SQLTeam.  The way to do this for data like what you've presented is to create a composite, 2 column index containing both the SearchParts and CompanyId columns and in that order.

    If that didn't make things more or less "instant" even on a 40 million row table, then something else is wrong and tricks like what you're trying to do either won't help at all or are the really long way to go about something with an unnecessary increase in data and unnecessary complexity when it comes time to maintain the table.

    So... to ask the questions...

    1. Are you actually using such short values for the SearchParts column?  If so, then WHY are you using an NVARCHAR(200) column???
    2. What else does the table contain?  I recommend that you post the CREATE TABLE with all the columns, all the constraints, and all of the indexes.
    3. As previously recommended, I also urge you to attach an "Actual Exection Plan" that can actually be loaded into SSMS so we can help you figure out the slowness of your query.  This will also tell us things like how many rows will be returned and a bunch of other stuff.
    4. Speaking of queries, you need to post the actual query you're using and not the mockup you've posted.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".
    "Dear Lord... I'm a DBA so please give me patience because, if you give me strength, I'm going to need bail money too!"

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

Viewing 3 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply