Search code examples
sqlsql-server-2008indexingpersisted-column

SQL Server 2008 - Can you index a persisted column

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'.

Solution

  • 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).