0

I have such a table structure:

CREATE TABLE Table1 (
    Id1 INT IDENTITY(1,1) NOT NULL PRIMARY KEY,
    Value VARCHAR(50)
) ON [PRIMARY]
GO

CREATE TABLE Table2 (
    Id2 INT IDENTITY(1,1) NOT NULL PRIMARY KEY,
    Value VARCHAR(50)
) ON [FILE_GROUP_2]
GO

CREATE TABLE Table3Link (
    Id3 INT IDENTITY(1,1) NOT NULL PRIMARY KEY,
    Id1 INT NOT NULL,
    Id2 INT NOT NULL
) ON [PRIMARY]
GO

ALTER TABLE Table3Link ADD CONSTRAINT FK_Table3Link_Table1 FOREIGN KEY(Id1)
REFERENCES Table1 (Id1)
ON DELETE CASCADE
GO

ALTER TABLE Table3Link ADD CONSTRAINT FK_Table3Link_Table2 FOREIGN KEY(Id2)
REFERENCES Table2 (Id2)
ON DELETE CASCADE
GO

Filegroup [PRIMARY] is online.

Filegroup [FILE_GROUP_2] is offline.

When I run query select * from Table1, I get following error:

The query processor is unable to produce a plan for the table or view 'Table2' because the table resides in a filegroup which is not online.

How can I ignore this integrity validation without making [FILE_GROUP_2] online?

GriGrim
  • 2,891
  • 1
  • 19
  • 33
  • I've tried to create a database with two FG and I've tried to run your script. Executing the select on Table1 works for me. I get that error when executing `SELECT * FROM Table2`, but this is obvious since the table is on the offline FG. What SQL Server Version are you speaking about? I've scripted the snippet on a SQL Server 2012 SP2 (+ hotfix). – Alessandro Alpi Jul 21 '14 at 13:14
  • [tag:sql-server-2012] When I created new test database, there was no such an error. But on production database it occurred that `select top 355209 * from Table1` works fine. Whereas query `select top 355210 * from Table1` returns an error. Other strange behaviour is that query from another database works without errors: `use other_db; go; select * from error_db.dbo.Table1`. – GriGrim Jul 21 '14 at 14:24
  • Yes, it's strange.. I've sql server 2012 too, but I cannot reproduce the issue. – Alessandro Alpi Jul 29 '14 at 16:01

0 Answers0