Spatial Indexing

Question

Spatial Indexing

Steve Jones - SSC Editor

SSC Guru

Points: 734418
More actions
September 4, 2008 at 11:29 pm

#63528

Comments posted to this topic are about the item Spatial Indexing

Viewing 15 posts - 1 through 15 (of 16 total)

You must be logged in to reply to this topic. Login to reply

AJ-148218 SSC Enthusiast Points: 188 More actions · Answer 1

The MSDN article referred to is somewhat ambiguous, or perhaps the question is not formulated correctly to support the correct answer.

Ref. MSDN: "You can create up to 249 spatial indexes on any of the spatial columns in a supported table"

Does this mean you can create up to 249 spatial indexes on a table, or 249 spatial indexes on every spatial column in the table?

Jan Van der Eecken SSCrazy Eights Points: 9029 More actions · Answer 2

In BOL under ms-help://MS.SQLCC.v10/MS.SQLSVR.v10.en/s10de_6tsql/html/ee6b9116-a7ff-463a-a9f0-b360804d8678.htm

it is worded slightly differently in that it says

You can create up to 249 spatial indexes on each spatial column in a table.

That also leads be to believe that the question is incorrectly worded.

--------------------------------------------------------------------------
A little knowledge is a dangerous thing (Alexander Pope)
In order for us to help you as efficiently as possible, please read this before posting (courtesy of Jeff Moden)[/url]

Miles Neale SSChampion Points: 13147 More actions · Answer 3

Got it right, have no problem with the wording. The article is a good and helpful.

Great Question/Thanks.

Miles...

Not all gray hairs are Dinosaurs!

Spence L SSC Veteran Points: 228 More actions · Answer 4

The question is definately worded incorrectly.

"A spatial index can be created only on a spatial column. You can create spatial indexes on any spatial column in a table that supports spatial indexes, and you can create multiple spatial indexes on a given spatial column."

http://technet.microsoft.com/en-us/library/bb964712.aspx

Spence L SSC Veteran Points: 228 More actions · Answer 5

While you may have answered the question correctly, as did I, the question is undoubtedly worded incorrectly for the correct answer to be true.

The question was : "What is the maximum number of spatial indexes on a table in SQL Server 2008?"

Now if the question asked was: "What is the maximum number of spatial indexes on a spatial column in SQL Server 2008?" then 249 would be the correct answer.

However, 249 technically is not the correct answer for the question given because "You can create up to 249 spatial indexes on any of the spatial columns in a supported table." (http://technet.microsoft.com/en-us/library/bb964740.aspx). So, that means you would have to determine how many spatial columns you have in a given table to be able to determine how many spatial indexes the table would have.

Jim SSCrazy Eights Points: 8718 More actions · Answer 6

AJ (9/5/2008)
The MSDN article referred to is somewhat ambiguous, or perhaps the question is not formulated correctly to support the correct answer.
Ref. MSDN: "You can create up to 249 spatial indexes on any of the spatial columns in a supported table"
Does this mean you can create up to 249 spatial indexes on a table, or 249 spatial indexes on every spatial column in the table?

The wording in the article very clearly states that "you can create up to 249 spatial indexes ... in a supported table."

The question is worded perfectly based on the referenced article. If you were allowed to create 249 spatial indexes on every spatial column in the table, the article would be reworded to say:

"You can create up to 249 spatial indexes on each (as opposed to any) of the spatial columns in a supported table."

Spence L SSC Veteran Points: 228 More actions · Answer 7

OK have it your way:

"You can create up to 249 spatial indexes on each spatial column in a table."

http://msdn.microsoft.com/en-us/library/bb934196.aspx

Jim SSCrazy Eights Points: 8718 More actions · Answer 8

Spence L (9/5/2008)

OK have it your way:

"You can create up to 249 spatial indexes on each spatial column in a table."

http://msdn.microsoft.com/en-us/library/bb934196.aspx%5B/quote%5D

OK, my foot is securely located in my mouth now. 😀

Sounds like Microsoft needs to to decide how many spatial indexes they are going to allow in a table.

Shaun McGuile SSCarpal Tunnel Points: 4111 More actions · Answer 9

http://msdn.microsoft.com/en-us/library/bb964740.aspx

"You can create up to 249 spatial indexes on any of the spatial columns in a supported table."

you can have more than one spatial column therefore 249 * the spatial columns might be the correct answer, though it could mean the 249 might be the limit of indexes over the spatial columns. Hmmm....

Someone needs to test it........volunteers? 😉 (how does 249 logically fit in as a number in a computer system (255 - 6)?)

but hey I picked the 249 answer and got a point (plus 1 for this post, as well) 😀

Hiding under a desk from SSIS Implemenation Work :crazy:

Jerry Hung SSChampion Points: 12968 More actions · Answer 10

My curiosity is on "who picked the number 249?" and why?

Is there a significance that it's 255 - 6?

SQLServerNewbieMCITP: Database Administrator SQL Server 2005

Christian Buettner-167247 SSChampion Points: 13729 More actions · Answer 11

The QOTD is correct. You cannot create more than 249 indexes on a table.

The referenced MSDN docs are incorrect or at least badly worded.

http://technet.microsoft.com/en-us/library/ms190197.aspx

(And I also tested it)

Best Regards,

Chris Büttner

AJ-148218 SSC Enthusiast Points: 188 More actions · Answer 12

Christian Buettner (9/5/2008)
The QOTD is correct. You cannot create more than 249 indexes on a table.
The referenced MSDN docs are incorrect or at least badly worded.
http://technet.microsoft.com/en-us/library/ms190197.aspx
(And I also tested it)

Good job Chris, thanks for testing it!

Shaun McGuile SSCarpal Tunnel Points: 4111 More actions · Answer 13

Cheers Chris! If this forum had rep it would be +rep for you dude! 😀

Thanks for clarifying the QOTD for us.

Microsoft - your documentation is ****. 😛

--Shaun

Hiding under a desk from SSIS Implemenation Work :crazy:

Spence L SSC Veteran Points: 228 More actions · Answer 14

Christian Buettner (9/5/2008)
The QOTD is correct. You cannot create more than 249 indexes on a table.
The referenced MSDN docs are incorrect or at least badly worded.
http://technet.microsoft.com/en-us/library/ms190197.aspx
(And I also tested it)

Yes great job Chris! Well it appears microsoft's documentation on spatial indexing is misleading at best. The page you referenced however clearly states 249 indexes per table. I'll be more cautious of their documentation in the future for sure.