CODEWITHSUNDEEP
sqlsql-server
George Filippakos
George Filippakos

Reputation: 16569

SQL Server Include Geography data type in an index

Is it possible to add a geography data type column to an index as an included column?

I am using SQL Server 2008 and the option is greyed out!

It is really annoying as my query has to lookup each row using the clustered index instead of just getting the geography value from a covering index.

I can't find any information about this online.

Has this changed in SQL Server 2012?

Upvotes: 1

Views: 366

Answers (1)

George Filippakos
George Filippakos

Reputation: 16569

Seems that Management Studio 2008 is too dumb to recognize that Geography data types can be included in an index as an 'Included Column'

Scripting the index creation works fine for Geography types.

CREATE UNIQUE NONCLUSTERED INDEX [IX_mbrAccount_mbrID] ON [dbo].[mbrAccount] 
(
    [mbrID] ASC
)
INCLUDE ( 
    [mbrNickname],
    [geoPosition]
) 
WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON, FILLFACTOR = 95) ON [PRIMARY]

The execution plan confirms that the column is being used by the index.

Upvotes: 1

Related Questions