You are the database administrator of your company. The network contains an instance of SQL Server 2008. Yourun the following Transact-SQL statements to create a table named
PartnerInfo and a spatial index on the PartnerLocation column in the PartnerInfo table in a database named Partners :
CREATE TABLE PartnerInfo( id int primary key, PartnerLocation geography);
CREATE SPATIAL INDEX SI_PartnerInfo_PartnerLocation
ON PartnerInfo(PartnerLocation)
WITH (BOUNDING_BOX = (0, 0, 300, 100),
GRIDS = (LEVEL_3 = MEDIUM));
You want to rebuild the spatial index to change the grid density to high. You want to ensure that the table and itsassociated indexes are not available for queries while rebuilding the spatial index. Which Transact-SQL statement should you run?

A.
CREATE SPATIAL INDEX SI_PartnerInfo_PartnerLocation
ON PartnerInfo(PartnerLocation)
WITH (BOUNDING_BOX = (0, 0, 300, 100),
GRIDS = (LEVEL_3 = HIGH),
ALLOW_ROW_LOCKS = ON,
DROP_EXISTING = ON);

B.
CREATE SPATIAL INDEX SI_PartnerInfo_PartnerLocation
ON PartnerInfo(PartnerLocation)
WITH (BOUNDING_BOX = (0, 0, 300, 100),
GRIDS = (LEVEL_3 = HIGH),
ALLOW_PAGE_LOCKS = ON,
DROP_EXISTING = ON);

C.
CREATE SPATIAL INDEX SI_PartnerInfo_PartnerLocation
ON PartnerInfo(PartnerLocation)
WITH (BOUNDING_BOX = (0, 0, 300, 100),
GRIDS = (LEVEL_3 = HIGH),
ONLINE = OFF,
DROP_EXISTING = ON);

D.
CREATE SPATIAL INDEX SI_PartnerInfo_PartnerLocation
ON PartnerInfo(PartnerLocation)
WITH (BOUNDING_BOX = (0, 0, 300, 100),
GRIDS = (LEVEL_3 = HIGH),
STATISTICS_NORECOMPUTE = OFF,
DROP_EXISTING = ON);

Explanation:

You should run the following Transact-SQL statement:
CREATE SPATIAL INDEX SI_PartnerInfo_PartnerLocation
ON PartnerInfo(PartnerLocation)
WITH (BOUNDING_BOX = (0, 0, 300, 100),
GRIDS = (LEVEL_3 = HIGH),
ONLINE = OFF,
DROP_EXISTING = ON);
You can create a spatial index on a spatial column of a table that contains spatial data. SQL Server 2008 allowsyou to create spatial indexes on columns that have the geography
or geometry data type. To create a spatialindex on a table, you must ensure that the table has a primary key. SQL Server 2008 does not support onlineindex builds for spatial indexes. When you want to prevent users from querying the tables and their associatedindexes during the index operation, you should either specify the ONLINE = OFF parameter or omit the
ONLINE parameter. When you set the ONLINE parameter to ON, the index operation fails, and an error occurs. Whilerebuilding a spatial index with the same name, you must also specify the DROP_EXISTING = ON parameter. Thisparameter ensures that the existing spatial index is dropped and rebuilt. If you do not specify this parameter or setthis parameter to OFF, an error is raised when an existing spatial index with the same name is found. You should not run the following Transact-SQL statement:
CREATE SPATIAL INDEX SI_PartnerInfo_PartnerLocation
ON PartnerInfo(PartnerLocation)
WITH (BOUNDING_BOX = (0, 0, 300, 100),
GRIDS = (LEVEL_3 = HIGH),
ALLOW_ROW_LOCKS = ON,
DROP_EXISTING = ON);
The ALLOW_ROW_LOCKS = ON parameter specifies whether row locks are allowed when the index isaccessed. It cannot be used to ensure that the table and its associated indexes are not available for queriesduring an index operation. You should not run the following Transact-SQL statement:
CREATE SPATIAL INDEX SI_PartnerInfo_PartnerLocation
ON PartnerInfo(PartnerLocation)
WITH (BOUNDING_BOX = (0, 0, 300, 100),
GRIDS = (LEVEL_3 = HIGH),
ALLOW_PAGE_LOCKS = ON,
DROP_EXISTING = ON);
The
ALLOW_PAGE_LOCKS = ON
parameter specifies whether page locks are allowed when the index isaccessed. It cannot be used to ensure that the table and its associated indexes are not available for queriesduring an index operation. You should not run the following Transact-SQL statement:
CREATE SPATIAL INDEX SI_PartnerInfo_PartnerLocation
ON PartnerInfo(PartnerLocation)
WITH (BOUNDING_BOX = (0, 0, 300, 100),
GRIDS = (LEVEL_3 = HIGH),
STATISTICS_NORECOMPUTE = OFF,
DROP_EXISTING = ON);
The STATISTICS_NORECOMPUTE = OFF
parameter is used to re-compute the distribution statistics. When thisparameter is set to ON, out-of-date statistics are not automatically recomputed. This parameter cannot be used toensure that the table and its associated indexes are not available for queries during an index operation.

Objective:
Performing Data Management Tasks

Sub-Objective:
Maintain indexes.

References:
MSDN > MSDN Library > Servers and Enterprise Development > SQL Server > SQL Server 2008 > ProductDocumentation > SQL Server 2008 Books Online > Database Engine > Technical Reference > Transact-SQLReference > CREATE INDEX ( Transact-SQL) > CREATE SPATIAL INDEX (Transact-SQL) MSDN > MSDN Library > Servers and Enterprise Development > SQL Server > SQL Server 2008 > ProductDocumentation > SQL Server 2008 Books Online > Database Engine > Development > Designing andImplementing Spatial Storage (Database Engine) > Working with Spatial Indexes (Database Engine) > SpatialIndexing Overview