Generate a number for a combination of columns in SQL Server

Question

I have a requirement to generate an ID field for a combination of a few fields in SQL Server. Let me give you an example.

I have a table like below

Brand	Owner	Source
Pip	People	Online
whip	People	Online
Pip	People	Offline
zip	Demons	Online
Rip	Zombies	Online
Dip	Ghosts	Online

I need to derive BrandID for the distinct BrandName and Owner Combination (irrespective of their source)

I have written SQL like the following which got me the desired output.

Select
  DENSE_RANK() OVER (ORDER BY Brand,Owner) AS BrandID,
  Brand AS BrandName,
  Owner AS BrandOwner,
  Source
From derivdTable

generate the output as

BrandID	BrandName	Owner	Source
1	Dip	Ghosts	Online
2	Pip	People	Online
2	Pip	Poeple	Offline
3	Rip	Zombies	Online
4	whip	people	Online
5	zip	Demons	Online

However, after a few days if I get another entry into my "dervdTable" like

Brand	Owner	Source
Pip	People	Online
whip	People	Online
Pip	People	Offline
zip	Demons	Online
Rip	Zombies	Online
Dip	Ghosts	Online
Bip	People	Online

Then my output with the same SQL will change like this.

BrandID	BrandName	Owner	Source
1	Bip	People	Online
2	Dip	Ghosts	Online
3	Pip	People	Online
3	Pip	Poeple	Offline
4	Rip	Zombies	Online
5	whip	people	Online
6	zip	Demons	Online

Basically the query changed the brandIDs.

If I had BrandID=2 for Pip, I want to keep the same number forever. How do I get it.

I want my output to look like this

BrandID	BrandName	Owner	Source
1	Dip	Ghosts	Online
2	Pip	People	Online
2	Pip	Poeple	Offline
3	Rip	Zombies	Online
4	whip	people	Online
5	zip	Demons	Online
6	Bip	People	Online

All the new brands should take newID numbers although the orderby caluse in Dense_Rank assigns a different ID.

I don't mind changing the table structure if auto Increment or any other type of settings make me achieve this.

Answer 1

Your database structure does not respect the fundamentals of the relational principles.

You need to have BrandName as a separate table with an ID that can be autogenerated.

The way to do that is :

SELECT DISTINCT IDENTITY(INT, 1, 1) AS BRAND_ID, BrandName
INTO BrandTable
FROM   MyTable:

ALTER TABLE BrandTable ADD PRIMARY KEY (BRAND_ID);

ALTER TABLE  MyTable ADD BRAND_ID INT;

UPDATE MT
SET    BRAND_ID = MT.BRAND_ID
FROM   MyTable AS MT
       JOIN BrandTable AS T 
          ON MT.BrandName = T.BrandName;

ALTER TABLE  MyTable ADD FOREIGN KEY (BRAND_ID) REFERENCES BrandTable (BRAND_ID);