You manage an instance of SQL Server 2008 named SQL1. SQL1 contains a database named HumanResource. The HumanResource database contains a table named Employees.
You want to create aspatial index on a column named EmpAddress in the Employees table. Which permissions will you require to perform this task? (Choose all that apply.)
A.
ALTER permission on the table
B.
ALTER permission on the database
C.
membership in the sysadmin fixed server role
D.
membership in the serveradmin fixed server role
Explanation:
You will require ALTER permission on the table and membership in the sysadmin fixed server role. You cancreate a spatial index on a spatial column of a table that contains spatial data. SQL Server 2008 allows you tocreate spatial indexes on columns that have the geography or geometry data type. To be able to create spatialindex on a table, you must ensure that the table has a primary key. To create a spatial index, you must have ALTER permission on the table or view. You must also have membership in the sysadmin fixed server role or the
db_ddladmin and db_owner fixed database roles. The options stating ALTER permission on the database and membership of the serveradmin fixed server roleare incorrect because these permissions are not required to create a spatial index.Objective:
Performing Data Management TasksSub-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) >Restrictions on Spatial Indexes