I'm looking into optimizing the spatial indexes on some of our tables (stored in SQL Server 2008 R2), so I ran
exec sp_help_spatial_geometry_index @tabname='myTable', @indexname='IX_myTable_Shape', @verboseoutput=1, @query_sample=@geomString
and that returned (among other stuff)
Number_Of_SRIDs_Found: 2. Troubled by that, I immediately did a
select count(*) from myTable where Shape.STSrid <> @mySrid and saw 0. Everything in the table is apparently using the correct SRID, so where could sp_help_spatial_geometry_index be finding another SRID?
I ran other spatial indexes through the same sproc and found 2 SRIDs for them as well. Getting paranoid, I threw this together to look through all spatial columns in the entire database...
declare @tablename varchar(50) declare @fieldname varchar(10) DECLARE cSpatialColumns CURSOR STATIC LOCAL FOR SELECT table_name [Table Name], column_name [Column Name] FROM information_schema.columns c where data_type = 'geometry' and TABLE_NAME NOT LIKE 'vw%' OPEN cSpatialColumns FETCH FROM cSpatialColumns INTO @tablename, @fieldname WHILE @@FETCH_STATUS = 0 BEGIN declare @myquery nvarchar(1000) set @myquery = 'select count(*) as ' + @tablename + 'Count from ' + @tablename + ' where ' + @fieldname + '.STSrid <> 102605' exec sp_executesql @myquery FETCH FROM cSpatialColumns INTO @tablename, @fieldname END
... and found no spatial objects at all that don't use our SRID of choice. So I'm at a loss. Could it be a matter of this being a bug in 2008 R2?
I tried looking at the SQL for that sproc but it simply executes another sproc to which I apparently don't have access.