1

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?

GibboK
  • 71,848
  • 143
  • 435
  • 658

3 Answers3

4

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)

Kaf
  • 33,101
  • 7
  • 58
  • 78
0

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.

sgeddes
  • 62,311
  • 6
  • 61
  • 83
0

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

sofoo
  • 1