Loading ...
Sorry, an error occurred while loading the content.

4909Creating Computed Persisted column

Expand Messages
  • Shadab Mustafa
    Jun 6 10:45 AM
      Hi Guys!

      I've a column with CSV value, but for some queries I need to split them and use the values in joining with other tables. The tables involved in joins typically have records in millions. I have a UDF to split the values and give me the value based on position I pass into the function as parameter like [GetSplittedEntry](@SourceString, @SplitBy, @EntryNo). What I want is to create persistable computed columns with some specific positioned values from the CSV string (like 3rd and 4th value in CSV) so that I can create indexes on them. But when creating computed column like following, this gives me error that the column cannot be persisted because the column is non-deterministic.

      ALTER TABLE MyTable
      ADD MyColumn AS rems.GetSplittedEntry(CSV,3) PERSISTED

      I also tried splitting the values by converting them into XML and then using that into computed column expression like following but that also fails, stating that 'Xml data type methods are not supported in computed column definitions. Create a scalar user-defined function to wrap the method invocation.'

      I tried doing this like following;
      ALTER TABLE MyTable
      ADD MyColumn AS CONVERT(XML,'<r><s>' + REPLACE('123,123,123,123,123',',','</s><s>') + '</s></r> ').value('/r[1]/s[3]','VARCHAR(16)') PERSISTED

      Then I tried by putting the same into a scalar UDF and use that scalar UDF to create computed column and that gave me the same error relating to non-deterministic column.

      So... Is there any other way I can achieve what I'm trying to do???

      Shadab Mustafa
      Sr. Software Engineer
    • Show all 3 messages in this topic