如何将模式绑定到SQL Server 2008中的视图中

Question

我正在尝试在我的SQL Server 2008数据库中创建索引。

，但是当我不按WITH SCHEMABINDING击退时，我会面临错误。

USE UNIVERSITY
GO
CREATE VIEW dbo.Major_Education_Category
WITH SCHEMABINDING
AS
   SELECT CA.CName, MA.MName
   FROM 
     (SELECT M.Major_ID MID , Major_Name MName
      FROM Major M, Registration R
      WHERE M.Major_ID = R.Major_ID) MA,
     (SELECT C.Category_ID CId, C.Category_Name CName
      FROM Education_Category C, Registration R
      WHERE C.Category_ID = R.Category_ID) CA
   GROUP BY 
      CA.CName, MA.MName
CREATE UNIQUE CLUSTERED INDEX UCI_Major_Id_By_Education_Category
ON dbo.Major_Education_Category (MName)
GO
CREATE UNIQUE CLUSTERED INDEX UCI_Category_Id_By_Major
ON dbo.Major_Education_Category  (CName)
GO

错误：

msg 4512，第16级，状态3，过程major_education_category，第6行
无法架构绑定视图" dbo.major_education_category"，因为名称"主要"无效架构绑定。名称必须以两部分格式，并且对象不能引用本身。

Answer 1

您的从子句中的表格需要指定为两个部分{owner}.{tablename}。除非您另行知道，否则最有可能是dbo。因此，尝试dbo.Major，dbo.Registration等

放置dbo.tablename。更改为：

(SELECT M.Major_ID MID , Major_Name MName
    FROM dbo.Major M, dbo.Registration R
    WHERE M.Major_ID = R.Major_ID) MA,
(SELECT C.Category_ID CId, C.Category_Name CName
    FROM dbo.Education_Category C, dbo.Registration R
    WHERE C.Category_ID = R.Category_ID) CA