HiveBrain v1.2.0
Get Started
← Back to all entries
patternsqlMinor

Why would a SQL Server 2008 R2 database disable a table index when upgraded to SQL Server 2012

Submitted by: @import:stackexchange-dba··
0
Viewed 0 times
why20082012sqldisableupgradeddatabasewouldwhenserver

Problem

I've restored a database backup in a new SQL Server 2012 server from a .bak made in SQL Server 2008 R2, and one of the tables has it's primary key index disabled. This doesn't happen when I restore to a 2008R2 server.

The structure of the table is:

CREATE TABLE RouteSegment (
   RouteSegmentID bigint IDENTITY(1,1) NOT NULL,
   Representation geography            NOT NULL,
   LastTime                            int NOT NULL,
   SourceStop1ID                       bigint NULL,
   SourceStop2ID                       bigint NULL,
   Length  as ([Representation].[STLength]()) PERSISTED,
   CONSTRAINT PK_RouteSegment PRIMARY KEY CLUSTERED (RouteSegmentID ASC)
 )


Is this a known problem in SQL Server 2012, or is there something I've missed?

I know that I can fix the problem with an index rebuild, I ask because it's surprising behavior, and want to know how to prevent it.

Solution

This has to do with any table which contains a geospatial field. I'm not sure why yet (is it a bug or a feature lol), but that's the cause.

Update: the problem only appears when there is a geospatial field and a PERSISTED column that uses it.

Context

StackExchange Database Administrators Q#20753, answer score: 3

Revisions (0)

No revisions yet.