patternsqlMinor
Why would a SQL Server 2008 R2 database disable a table index when upgraded to SQL Server 2012
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:
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.
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.
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.