SQL Server Include Geography data type in an index

Question

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?

Have you tried writing the CREATE INDEX statement yourself? msdn.microsoft.com/en-us/library/ms188783(v=sql.100).aspx All data types are allowed except text, ntext, and image. The index must be created or rebuilt offline (ONLINE = OFF) if any one of the specified non-key columns are varchar(max), nvarchar(max), or varbinary(max) data types. — ta.speot.is
– ta.speot.is, Commented Jul 2, 2014 at 8:54

George Filippakos · Accepted Answer · 2014-07-02 09:10:55Z

1

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.

edited Jul 2, 2014 at 9:10

answered Jul 2, 2014 at 9:01

George Filippakos

16.6k16 gold badges84 silver badges93 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

SQL Server Include Geography data type in an index

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related