May 9, 2019 at 12:47 pm
Hello.
I have 2 DBs (MS SQL Server 2016 Standard Edition). I want to create indexing view to implement some indexes. The view must be able to select data from other database on same server. How can do it ?
Thanks in advance)
May 9, 2019 at 1:09 pm
in general DB.schema.object naming convention will help you to get the data you need
like the following:
select 'db1' [db], name , type_desc
from DB1.sys.objects
union all
select 'db2' [db], name, type_desc
from DB2.sys.objects
however, it's not quite clear what do you mean by the following:
to create indexing view to implement some indexes
May 9, 2019 at 1:24 pm
I think the OP is saying that want to create an index on a view, but that the view will be returning data from other databases. Simply put, you can't. If you to do so you either get the error
Cannot create index on view '<ObjectName>' because the view is not schema bound.
Or, if you try to schema bind you will get an error like:
Cannot schema bind view '<ObjectName>' because name 'DB1.dbo.Table1' is invalid for schema binding. Names must be in two-part format and an object cannot reference itself.
If you want to have an indexed view there are several requirements you must meet, and one of those is that the object is schema bound. To schema bind the objects must be in the same database, which makes the task impossible.
If you must have an indexed view, you will have to migrate the objects to the same database; otherwise you cannot do it.
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
May 9, 2019 at 1:42 pm
perhaps SQL synonyms could help, but i'm not sure and it needs testing
May 9, 2019 at 1:59 pm
perhaps SQL synonyms could help, but i'm not sure and it needs testing
That would give you the error:
Synonyms are invalid in a schemabound object or a constraint expression.
Here's a few tests to show what fails, and succeeds (note that nothing where another database is referenced is successful):
USE master;
GO
IF NOT EXISTS (SELECT 1 FROM sys.databases WHERE [name] = N'Sandbox')
CREATE DATABASE Sandbox;
GO
CREATE DATABASE DB1;
GO
USE DB1;
CREATE TABLE dbo.Table1 (ID int IDENTITY PRIMARY KEY CLUSTERED, SomeString varchar(20));
GO
CREATE DATABASE DB2;
GO
USE DB2;
CREATE TABLE dbo.Table2 (ID int IDENTITY PRIMARY KEY CLUSTERED, SomeString varchar(20));
GO
USE Sandbox;
GO
CREATE TABLE dbo.Table3 (ID int IDENTITY PRIMARY KEY CLUSTERED,SomeString varchar(20));
GO
CREATE VIEW dbo.T3 AS
SELECT ID,
SomeString
FROM Table3;
GO
CREATE UNIQUE CLUSTERED INDEX IX_T3 ON dbo.T3 (ID); --fails
GO
ALTER VIEW dbo.T3 WITH SCHEMABINDING AS
SELECT ID,
SomeString
FROM dbo.Table3;
GO
CREATE UNIQUE CLUSTERED INDEX IX_T3 ON dbo.T3 (ID); --Suceeds
GO
--Fails
CREATE VIEW T1T2 WITH SCHEMABINDING AS
SELECT ID,
SomeString
FROM DB1.dbo.Table1
UNION ALL
SELECT ID,
SomeString
FROM DB2.dbo.Table2;
GO
CREATE SYNONYM dbo.Table1 FOR DB1.dbo.Table1;
CREATE SYNONYM dbo.Table2 FOR DB2.dbo.Table2;
GO
--Fails
CREATE VIEW T1T2 WITH SCHEMABINDING AS
SELECT ID,
SomeString
FROM dbo.Table1
UNION ALL
SELECT ID,
SomeString
FROM dbo.Table2;
GO
DROP VIEW T3;
DROP TABLE dbo.Table3;
DROP SYNONYM dbo.Table1;
DROP SYNONYM dbo.Table2;
GO
USE master;
GO
DROP DATABASE DB1;
DROP DATABASE DB2
Output:
Msg 1939, Level 16, State 1, Line 33
Cannot create index on view 'T3' because the view is not schema bound.
Msg 4512, Level 16, State 3, Procedure T1T2, Line 4 [Batch Start Line 43]
Cannot schema bind view 'T1T2' because name 'DB1.dbo.Table1' is invalid for schema binding. Names must be in two-part format and an object cannot reference itself.
Msg 2788, Level 16, State 1, Procedure T1T2, Line 4 [Batch Start Line 57]
Synonyms are invalid in a schemabound object or a constraint expression.
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply