Index on computed column
First, I would like to say that the "XML Best Practices for Microsoft SQL Server 2005" article is a must read for everyone interacting with this forum. It's a GREAT article!
There is a section on "Property Promotion", and everything is working fine until I get to the point where I am attempting to create a unique index on my promoted column. I do everything the same as the example below, and I get the following error when attempting to create the index:
Column 'ad_guid' in table 'employees' cannot be used in an index or statistics or as a partition key because it is non-deterministic.
The only difference between my case, and the one below, is that the field I am promoting is typed as a uniqueidentifier - rather than varchar.
Any ideas/suggestions?
Thanks,
Scott
-
http://msdn.microsoft.com/xml/default.aspx?pull=/library/en-us/dnsql90/html/sql25xmlbp.asp
CREATE FUNCTION udf_get_book_ISBN (@xData xml) RETURNS varchar(20) BEGIN DECLARE @ISBN varchar(20) SELECT @ISBN = @xData.value('/book[1]/@ISBN', 'varchar(20)') RETURN @ISBN END
Add a computed columnto the table for ISBN:
ALTER TABLE docs ADD ISBN AS dbo.udf_get_book_ISBN(xCol)
The computed column can be indexed in the usual way.
