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

Question

Post reply

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

ahmed_elbarbary.2010

Hall of Fame

Points: 3118

February 22, 2021 at 4:51 pm

#3848592

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

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

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

Mr. Brian Gale SSC-Insane Points: 24689 More actions · Answer 1

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];

The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.

Jeff Moden SSC Guru Points: 1003863 More actions · Answer 2

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...

Are you actually using such short values for the SearchParts column? If so, then WHY are you using an NVARCHAR(200) column???
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.
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.
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.

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