CODEWITHSUNDEEP
sql-serversql-server-2008
GibboK
GibboK

Reputation: 73918

Creating a View with an uniqueidentifier column

I need to take the data from a table in the Database, in my case dbo.EventsCollage and create a View for it.

The View should contain all the records for table to which is based plus I should add a Column called Id of datatype uniqueidentifier.

A uniqueidentifier value (random generated) should be created automatically for every records display in the View

Here how I am creating the View

CREATE VIEW testView
AS
SELECT
x.Name
FROM dbo.EventsCollage as x;

Could you please point me out a sample of code?

Upvotes: 1

Views: 5762

Answers (3)

sofoo
sofoo

Reputation: 1

You can also do:

SELECT ROW_NUMBER() OVER( ORDER BY col1 ) AS id, col1, col2, col3
FROM MyResults

See: Add Identity column to a view in SQL Server 2008

Upvotes: 0

Kaf
Kaf

Reputation: 33809

Use newId() . (Note: this will give you a new Id for each row each time you select)

CREATE VIEW testView
AS
SELECT
newId() Id, x.Name
FROM dbo.EventsCollage as x;

(DEMO - 1)

Or if you need each row having same uniqueidentifier everytime, try (Note: this will work only up to 100mil records)

CREATE VIEW testView
AS

SELECT convert(uniqueidentifier,
       stuff('62799568-6EF2-4C95-84E7-4953A6959C99',1,len(rn),convert(varchar,rn))) id,
       T.Name
FROM ( 
  select x.Name,  ROW_NUMBER() over (order by x.Name) rn
FROM dbo.EventsCollage as x ) T;

(DEMO - 2)

Upvotes: 5

sgeddes
sgeddes

Reputation: 62841

I think you need to look into using NEWID():

CREATE VIEW testView
AS
SELECT
x.Name, NEWID() as youruniquecol
FROM dbo.EventsCollage as x;

Here is the SQL Fiddle.

Good luck.

Upvotes: 0

Related Questions