4

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.

0

1 Answer 1

3

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.

2
  • ALTER INDEX [YourPrimaryKey] ON [YourDatabase].[YourTable] REBUILD PARTITION = ALL WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) GO Sep 18, 2012 at 18:32
  • Thanks to @ChuckStraughn I've found the condition which makes the problem appear. Apart from a spatial column, you need to have a PERSISTED column referencing it for the problem to manifest itself. Don't know what to do...edit Chuck's answer or add a new answer myself... Sep 24, 2012 at 14:02

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.