Clustered index behavior with computed columns

• razzgor

  Mr or Mrs. 500

  Points: 577

  More actions

  October 4, 2013 at 3:10 pm

  #298590

  Hi all, I was working with an idea for searching through IP ranges efficiently and ran into a problem with building out the indexes required for the table.

  Here's the general structure:

  

  After INSERT INTO'ing the base table (red, blue columns)

  I've been using the following ALTER command to add into the table definition the spatial data I need.

  ALTER TABLE Junk..foo

  ADD geo_range AS geometry::STGeomFromText('LINESTRING('+CONVERT(VARCHAR(20),IPStartNumber)+' 0,'+CONVERT(VARCHAR(20),IPEndNumber)+' 0)',0

  )

  As you can see, it's fairly straightforward-- I just want the column to store a 2-D geometric representation of the numbers in the blue column.

  After running that alter command (the entire table is un-indexed at this point) SELECT * works and returns the expected data.

  After creating a clustered index on the red columns however, the computed column breaks. It gives the error that 2 distinct points were not supplied for the LINESTRING function.

  Any thoughts as to why this might be occurring? I know the creation of a clustered index rearranges the data, but why would that affect a column that is built by looking at the values of 2 un-indexed columns? Moreover, why would it affect the syntax of a command already in place in the table definition?

  Thanks in advance!