We have a table that has a geometry column that is filled by a stored procedure from two x and y values whenever they're changed

( SW_GEOMETRY = GEOMETRY::STGeomFromText('POINT('+ CONVERT(VARCHAR(50),@xval) + ' ' + CONVERT(VARCHAR(50),@yval) +')',27700) )

The table has a spatial index (SPIX_BLPUS) on it on the SW_GEOMETRY column.

For some specific value changes of, for example, a y value, we get the error:

Cannot insert duplicate key row in object 'sys.extended_index_462728801_384000' with unique index 'SPIX_BLPUS'. The duplicate key value is (0x0119251c04, 10033223837).

I don't understand why this is happening, as the index shouldn't care about uniqueness? It doesn't happen for all value changes, just specific ones that I can only assume already exist in the index - but duplicates should be allowed.

  • Dropping and recreating the spatial index does solve the issue, but I'd still like to know what could cause this when the index shouldn't care about uniqueness in the first place? Also, it takes a long time to recreate this spatial index, so a solution that would avoid that would be great. – Silmaril.SE Sep 14 '15 at 10:10
  • Can you make a script that sets up a copy of this table and reproduces the error? If you add that to your question, either inline or via a SQL Fidde, that might make it easier to help. – Geoff Patterson Sep 14 '15 at 19:00
  • Someone didn't create the index as unique by mistake? – Colin 't Hart Sep 16 '15 at 9:09
  • Not that Colin. The index is created by script, and I checked that the Spatial index was not set to unique (don't think a spatial can be unique in any case) We think we've tracked the issue down to the database being restored to 2012 from a 2008 copy which we didn't know at the time. – Silmaril.SE Sep 17 '15 at 10:12

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.