CODEWITHSUNDEEP
sql-server
Pingpong
Pingpong

Reputation: 8009

Generate a primary key on index view using T SQL

I try to create an index view, and unique clustered index on the view. My problem is that how to generate a primary key within a select clause. E.g.

Create view ssrs.vMyView
with schemabinding
as
  select firstname, lastname, other columns  --example columns
  from mytable

how to generate a primary key for each row on the fly?

Update

The problem is that it does NOT have unique columns or combination of columns, so I need to generate a unique id on the fly. Firstname and lastname are just example. There are primary key for the base table.

Thanks in advance!

Upvotes: 2

Views: 11035

Answers (2)

marc_s
marc_s

Reputation: 754438

Once you've created this view, if you obeyed all the rules and requirements for an indexed view, you should be able to just create the clustered index like this:

CREATE CLUSTERED INDEX cix_vMyView ON dbo.vMyView(....)

You need to choose a good, valid clustering key - preferably according to the NUSE principle:

  • narrow
  • unique
  • static
  • ever-increasing

An INT IDENTITY would be perfect - or something like a BIGINT or a combination of INT and DATETIME.

Update: seeing that your base table doesn't even have a primary key (THAT's a much bigger problem you'll need to fix ASAP!! If it doesn't have a primary key, it's not a table), you could use something like ROW_NUMBER() in your view definition:

CREATE VIEW ssrs.vMyView
WITH SCHEMABINDING
AS
  SELECT firstname, lastname,
     ROW_NUMBER() OVER(ORDER BY Lastname, FirstName) AS 'ID'
  FROM dbo.mytable

to give you an "artificial" unique, ever-increasing primary key.

(Update 2014-Apr-25: unfortunately, contrary to my belief at the time of posting this, this won't work since you cannot create a clustered index on a view that contains a ranking function like ROW_NUMBER .....). Thanks to @jspaey for pointing that out. So this makes it even more important to have a primary key on the base tables and include that in your view definition!)

But again: if your base table doesn't have a primary key - fix that first !!

Update #2: ok, so your base table(s) does have a primary key after all - then why isn't that part of your view definition? I would always include all the primary keys from all base tables in my views - only those PK enable you to clearly identify rows from the base table, and they allow you to make your views updateable.

Upvotes: 7

TimothyAWiseman
TimothyAWiseman

Reputation: 14863

Pingpong, Marc is right that you need something that is unique to add a primary key. Remember that this does not need to be a single column, so if you have two columns that are unique together that would work perfectly well.

If no combination of columns is unique, you probably wish to rethink your view or even add columns so that there is something unique.

As a related note, remember that Enterprise edition will take advantage of indexed views automatically. But outside of Enterprise Edition, you may need to explicitly tell the optimizer to use the index through the noexpand hint. I wrote about that previous at On Indexes and Views

Upvotes: 2

Related Questions