了解 SQL Server 2008 R2 索引视图中的列类型
我们通过这样的sql创建了一个索引视图：
Select Table1_ID, Count_BIG(*) as Table2TotalCount from Table2 inner join
Table1 inner join... where...
同样在创建视图之后，我们在列Table1_ID上设置聚集唯一索引。
因此 View 由两列组成：
Table1_ID
Table2TotalCount
我们有两个问题：
- 如果我们使用
Count(1)或Count_BIG(1)而不是Count_BIG(*)>，SQL不允许我们在Table1_ID上设置索引 - 我们如何控制第二列的类型（
Table2TotalCount） - SQL给出一个 此列的 long nullable 类型。但我们想为此列设置 int not nullable 类型。创建View后，我们找不到任何方法来更改列类型。 此外，我们用于创建视图的 t-sql 始终将 int 值返回到 Table2TotalCount。
We created an indexed view by such sql:
Select Table1_ID, Count_BIG(*) as Table2TotalCount from Table2 inner join
Table1 inner join... where...
Also after creating the view, we set clustered unique index on column Table1_ID.
So View consists of two columns:
Table1_ID
Table2TotalCount
We have two questions:
- If we use
Count(1)orCount_BIG(1)instead ofCount_BIG(*), SQL doesn't allow us to set index on Table1_ID - How can we control the type of second column (
Table2TotalCount) - SQL give a
long nullable type to this column. But we want to set int not nullable type to this column. After creating View, we can't find any way to change column type.
Also t-sql we use for creating view, always return int values to Table2TotalCount.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（2）
您必须使用 COUNT_BIG(*)。这是创建索引视图的限制之一：
您可以创建第二个未建立索引的视图，该视图引用第一个视图，并转换以下数据类型：将计数列设置为您所需的类型（并且可能使用 ISNULL() 来防止可为 null 的属性）
You have to use COUNT_BIG(*). It's one of the restrictions on creating an indexed view:
You could create a second view that isn't indexed, that references this first view, and casts the data type of the count column to your desired type (and possibly uses an ISNULL() to prevent the nullable property)
这可能是因为您依赖的是表达式而不是字段或
*。使用count(1)得到的结果与count(*)相同，即计算记录数，因此没有理由使用count(1).使用
count而不是count_big来获取int而不是bigint（long）。 （文档明确指出它们之间的唯一区别是返回类型。）我不知道为什么该值可以为空，因为count永远不会返回 null，但这对于所有聚合可能都是相同的，并且可能无法控制。That's probably because you are counting on an expression instead of a field or
*. Usingcount(1)gives the same result ascount(*), i.e. counting the number of records, so there is no reason to usecount(1).Use
countinstead ofcount_bigto getintinstead ofbigint(long). (The documentation specifically says that the only difference between them is the return type.) I don't know why the value is nullable ascountnever returns null, but that might be the same for all aggregates, and probably not possible to control.