1

Is it possible to index a persisted field?

When trying to change a primary key to a persisted field or add a primary key to a field I've made persisted field I get the following:

Cannot define PRIMARY KEY constraint on nullable column in table 'tblOpportunityRecords'.

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
aSystemOverload
  • 2,994
  • 18
  • 49
  • 73

2 Answers2

4

In order to male your computed field non-null, you need to help SQL Server figure out that no nulls are possible. Usually, this is done using the ISNULL function which can be used to ensure that the resulting expression is non-nullable.

ISNULL(my_computation, 0) --the second arg should have the same type as the first one

Note, that you can't use the COALESCE function (for a reason unknown to me - it just doesn't work).

usr
  • 168,620
  • 35
  • 240
  • 369
  • Interesting - with `ISNULL(...)` the column indeed becomes non-nullable and thus can be used for a primary key. I still wouldn't put my primary key on such a computed column - but it's technically possible. Thanks for sharing that! – marc_s Jun 19 '12 at 15:42
  • Not sure what you mean by "throws"? – Martin Smith Jun 19 '12 at 15:55
  • @MartinSmith That phrase was poorly chosen. I've replaced it with "raises an error". – usr Jun 19 '12 at 16:02
  • @usr - That's what I thought you might mean. It doesn't. It is just important that the second parameter is `NOT NULL`-able. `ISNULL(null,null)` doesn't raise an error and creates a nullable column. – Martin Smith Jun 19 '12 at 16:09
  • @MartinSmith I did not know that (I repro'd this in reaction to your comment and you're right). Good to know! I fixed my answer. Thanks! – usr Jun 19 '12 at 16:29
0

A primary key must be - by definition - NOT NULL and unique.

But you can index a persisted computed column with a regular non-clustered index - absolutely!

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
  • How can I make the persisted field no nulls as it won't let you change that for persisted fields. Both the fields it references to create it are NO NULL. – aSystemOverload Jun 19 '12 at 15:31
  • @aSystemOverload: I don't think there's any way to do this - plus I would never put my primary key onto a persisted column .... use some other good primary key - an `INT IDENTITY` or something - and index your computed column with a suitable non-clustered index! – marc_s Jun 19 '12 at 15:32
  • I did have it as an INT, but the number of records will increase substantially and MS ACcess which I use as a front end cannot access bigint datafields so thats what led me to consider a persisted field. The records are repeated historical records, so there is a unique value repeated in each DataSet so I created the persisted field as DataSetID + UNiqueValue, which would be table unique. – aSystemOverload Jun 19 '12 at 15:37