SQLTeam.com | Weblogs | Forums

Add Identity column to a view in SQL Server 2016

This is my view:

SELECT
e.EmpId
,e.EmpNo
,FirstName
,LastName
,Email
d.DepartmantName
t.TeamName
FROM e.Employee e
JOIN EmployeeTeam et
ON e.EmpId = et.EmpId
JOIN Team t
ON eg.TeamId = t.TeamId
JOIN TeamArea ta
ON ta.TeamId = t.TeamId
JOIN Department d
ON d.DepartmentId = ta.DepartmentId

Where EmpId is primary key in Employee table and EmpNo is not unique and key field.
I need to add a new column named Id and I need to this column be unique so I think to add new column as identity.
If that is possible how to do this?

Thanks.

Why not use EmpId?

Hi yosiasz. Thanks for reply.

Actually I added EmpId just for testing. In the result of view it is the same for each Employee Name record. That why I need add Id.
Thanks.

how did you add it?

it should not be the same if it is a properly configured identity column

That exactly what I'm asking. Is possible to add Id to view. I found online
https://stackoverflow.com/questions/10382287/add-identity-column-to-a-view-in-sql-server-2008
But I don't know how to implement for my case.

Why do you need a unique ID in this view?

Is it possible there is a problem with your query that is causing duplicates? Either an incorrect join - or incorrect assumption about the data?

Do you know which table is causing 'duplicate' values to be returned?

The query result is fine. Except I don't have have unique identifier field in the result.
I tried to add ID like:
SELECT ID = ISNULL(cast(newid() as varchar(40)), ''),
and then rest the code of query.
But in that case I'm getting duplication for each row.
It needs me for Entity Framework.

Gotcha so currently you do not have an identity column on that table? Is that a correct assumption?

All my tables have Identity column. I don't have identity column in result of query.

Why do you need an identity column on the results of this query? What purpose does that serve - and what issue will it resolve?

1 Like

I have only one question. Is it possible to add ID column? If yes, how.

You can generate an unique number on the fly by using ROW_NUMBER().
If the order is unique then the result will be deterministic assuming the data does not change.

SELECT
	e.EmpId
	,e.EmpNo
	,FirstName
	,LastName
	,Email
	,d.DepartmantName
	,t.TeamName
	,ROW_NUMBER() OVER (ORDER BY e.EmpId, t.TeamId, ta.DepartmentId) AS rn
FROM e.Employee e
	JOIN EmployeeTeam et
		ON e.EmpId = et.EmpId
	JOIN Team t
		ON eg.TeamId = t.TeamId
	JOIN TeamArea ta
		ON ta.TeamId = t.TeamId
	JOIN Department d
		ON d.DepartmentId = ta.DepartmentId;

Hi Ifor. Thanks for reply.
Yesterday I tried ROW_NUMBER() also, but it destroyed query result.If you have idea how to add ID and keep correct result I will appreciate for help.
Thanks.

Not sure what you mean by "destroyed query result". The ROW_NUMBER() should just add a sequential number to the output, and it's the only way to do that thru within a view.

Hi ScottPletcher. Thanks for reply.
I expected the same like you said. But in actually I got in the result of query some record repeats up to 10 times, some less, some didn't duplicated. I got very bad result of the query when ROW_NUMBER() was added.

Using row_number() will not add rows to the result and 'create' duplicates. This leads me to believe the underlying query already has duplicate values and the row_number() exposed those duplicates.

If an Employee is in many teams - how do you want that information to be identified?

If you just want to assign an arbitrary ID value - then use: row_number() over(order by @@spid)

That will generate an ID value across the full set of data returned from the query. But - it will be arbitrary and it can (probably will) be different for every execution. Which brings us back to the original question - why do you need to do this and what problem are you trying to resolve?

1 Like

You are right. In the result of query the fields such as EmpId, EmpNo, FirstName, LastName, EmailAddress are duplicating as many time as many TeamName exists for according employee.

So - can you answer the questions that have been asked?