不确定我是否理解 SqlServer 中索引视图在幕后的作用

发布于 2024-08-03 08:50:51 字数 730 浏览 7 评论 0原文

我正在使用 sql server 2008，并开始发现使用索引视图可以帮助我加快一些查询的速度……因为我的架构不是基本的。

因此，如果我有一个如下所示的表...

**ParentTable
ParentId INT PK IDENTITY
Name VARCHAR(MAX)

**ChildTable
ChildId INT PK IDENTITY
ParentId INT (FK to the table above)
Name VARCHAR(MAX)
Boundary GEOGRAPHY
CentrePoint GEOGRAPHY
CentrePointFlattened GEOMETRY

因此，对于父表中的每一行，它可以有零到多个子项。

首先，如果我创建子级的索引视图，如果 ParentId 字段可以为空，则该视图将不起作用。所以我需要将其设为必需。

现在问题来了。

我有一个子表内部连接到父表的索引视图（注意我只是索引任一表中的某些字段）...

(pseduo sql code)
ParentId INT
Name VARCHAR(MAX) AS ParentName
ChildId INT
Name VARCHAR(MAX) as ChildName
Boundary GEOGRAPHY

现在，这 5 个字段的数据是否已序列化/复制到另一个位置再次？或者索引视图是否只创建一些作为表数据的索引 id？

原文

I'm using sql server 2008 and have started to find using Indexed Views are helping me speed up some of my queries ... as my schema isn't basic.

So, if i have a table that is the following...

**ParentTable
ParentId INT PK IDENTITY
Name VARCHAR(MAX)

**ChildTable
ChildId INT PK IDENTITY
ParentId INT (FK to the table above)
Name VARCHAR(MAX)
Boundary GEOGRAPHY
CentrePoint GEOGRAPHY
CentrePointFlattened GEOMETRY

So for each row in the parent table, it can have zero to many children.

Firstly, if I make an index'd view of the children, it won't work if the ParentId filed can be nullable. So i need to make it required.

Now the question.

I have an index view of the children table inner join'd to the parent table (note i'm only indexing some of the fields from either table)...

(pseduo sql code)
ParentId INT
Name VARCHAR(MAX) AS ParentName
ChildId INT
Name VARCHAR(MAX) as ChildName
Boundary GEOGRAPHY

Now, are the data for these 5 fields serialized/copied to ANOTHER location again ? or does the index view only create some index id's that is the data for the table?

分享到QQ

分享到微博