Current SQL Server Blog Articles
For fast and efficient search and retrieval all columns containing spatial data should have a spatial index created on them.
For Oracle an index can be created on any SDO_GEOMETRY column regardless as to whether the table has a primary key or not. This is because Oracle uses an internal, unique, ROWID pseudo-column in its indexes: the ROWID links a leaf in the spatial index to the table’s actual row on disk (in the table’s tablespace).
However, SQL Server 2008 “Katmai” requires that all tables must have a primary key before a spatial index can be constructed. The primary key can be based on one or more columns in the table with those columns being of any data type.
The table I loaded for Tip #1, TAS_LGA, can have its primary key constructed as follows:
1. Make the column that will become the primary key “NOT NULL”
2. Declaratively define the primary key on our not null column lga_pid as follows:
We are now in a position to create our spatial index. Microsoft’s spatial index requires you define the bounding box of the spatial index. For our TAS_LGA table this will be the complete extent of the data. This can be discovered as follows:
Now that we know the extent of our data we can create a spatial index as follows: