CREATE TABLE t1 (a INT, b as 2*a); GO CREATE INDEX i1 ON t1 (b); GO in Visual Basic .NET

Creation UPCA in Visual Basic .NET CREATE TABLE t1 (a INT, b as 2*a); GO CREATE INDEX i1 ON t1 (b); GO

If any of your SET options does not have the correct value when you create the table, you get this message when you try to create the index:

Server: Msg 1935, Level 16, State 1, Line 2 Cannot create index. Object '<tname>' was created with the following SET options off: '<option(s)>'.

If more than one option has an incorrect value, the error message reports them all. Here s an example that creates a table with a nondeterministic computed column:

When you try to create the index on the computed column c, you get this error:

Msg 2729, Level 16, State 1, Line 1 Column 'c' in table 't2' cannot be used in an index or statistics or as a partition key because it is nondeterministic.

Column c is nondeterministic because the month value of DATENAME() can have different values depending on the language you re using.

6

Using the COLUMNPROPERTY Function You can use the IsDeterministic column property to determine before you create an index on a computed column (or on a view) whether that column is deterministic. If you specify this property, the COLUMNPROPERTY function returns 1 if the column is deterministic and 0 otherwise. The result is unde ned for columns that are neither computed columns nor columns in a view, so you should consider checking the IsComputed property before you check the IsDeterministic property. The following example detects that column c in table t2 in the previous example is nondeterministic:

SELECT COLUMNPROPERTY (OBJECT_ID('t2'), 'c', 'IsDeterministic');

The value 0 is returned, which means that column c is nondeterministic. Note that the COLUMNPROPERTY function requires an object ID for the rst argument and a column name for the second argument. However, the COLUMNPROPERTY function also has a property of IsIndexable. That s probably the easiest to use for a quick check, but it won t give you the reason if the column is not indexable. For that, you should check these other properties.

If you create a clustered index on a computed column, the computed column is no longer a virtual column in the table. The computed value physically exists in the rows of the table, which is the leaf level of the clustered index. Updates to the columns that the computed column is based on also update the computed column in the table itself. For example, in the t1 table created previously, if we insert a row with the value 10 in column a, the row is created with both the values 10 and 20 in the actual data row. If we then update the 10 to 15, the second column is updated automatically to 30. Persisted Columns The ability to mark a computed column as PERSISTED (a feature introduced in SQL Server 2005) allows storage of computed values in a table, even before you build an index. In fact, this feature was added to the product to allow columns of computed values from underlying table columns of type oat or real to have indexes built on them. The alternative, when you want an index on such a column, would be to drop and re-create the underlying column, which can involve an enormous amount of overhead on a large table. Here s an example. In the Northwind database, the Order Details table has a column called Discount that is of type real. The following code adds a computed column called Final that shows the total price for an item after the discount is applied. The statement to build an index on Final fails because the resultant column involving the real value is imprecise and not persisted:

USE Northwind; GO ALTER TABLE [Order Details] ADD Final AS (Quantity * UnitPrice) - Discount * (Quantity * UnitPrice); GO