SQL Server创建视图索引，其中包含distinct或group by

I have a table of address data in my SQL server database. This table is not normalized so it contain many addresses the are repeated. Each unique address can be identified by an Id field (these ids repeat often in the table).

我的SQL服务器数据库中有一个地址数据表。此表未规范化,因此它包含许多重复的地址。可以通过Id字段标识每个唯一地址(这些ID经常在表中重复)。

So i created a view on the table to extract all the unique addresses, using Select Distinct(AddressId) from the original table.

所以我在表上创建了一个视图,使用原始表中的Select Distinct(AddressId)提取所有唯一的地址。

Now i would like to create an index on this view to increase the speed of searching, but SQL server is not allowing me to create an index on the view as it contains a distinct or group by (i have tried both to see if it would allow me create index)

现在我想在这个视图上创建一个索引以提高搜索速度,但SQL服务器不允许我在视图上创建一个索引,因为它包含一个不同的或分组的(我已经尝试了两个,看它是否会允许我创建索引)

Has anyone got any solution around this? or any views to an alternate way to do this.

有人有解决方案吗?或任何其他方式来执行此操作。

I need to query this view based on address keywords and return the ones based on the matching count, i have this query in place i'm trying to speed it up by indexing fields in the view.

我需要根据地址关键字查询此视图,并根据匹配计数返回一个,我有这个查询,我试图通过索引视图中的字段加快它。

SQL Server 2008

SELECT      
    AddressId,  
    AddressNumber,  
    AddressName, 
    Town, 
        City,
        Country,
    COUNT_BIG(*) As AddCount--,
    --TRIM(AddressNumber + ' ') + LTRIM(AddressName + ' ')  + LTRIM(Town + ' ') + RTRIM(City + ' ') AS AddressLookup
FROM
    [Address] A
GROUP BY
    AddressId,
    AddressNumber, 
    AddressName, 
    Town, 
    City, 
    Country

is my query....

是我的询问....

if i take out the column with AddressLookup i can add the indexes

如果我用AddressLookup取出列,我可以添加索引

Cheers

4 个解决方案

#1

You can use GROUP BY in indexed views as long as:

您可以在索引视图中使用GROUP BY,只要:

If GROUP BY is specified, the view select list must contain a COUNT_BIG(*) expression, and the view definition cannot specify HAVING, ROLLUP, CUBE, or GROUPING SETS.

如果指定了GROUP BY,则视图选择列表必须包含COUNT_BIG(*)表达式,并且视图定义不能指定HAVING,ROLLUP,CUBE或GROUPING SETS。

Taken from MSDN

取自MSDN

Include but just ignore the necessary COUNT_BIG(*) column.

包括但只是忽略必要的COUNT_BIG(*)列。

#2

SQL Server does allow GROUP BY in indexed views even as far back as [SQL2000][1] Are you sure you weren't looking at the restrictions for updatable views?

SQL Server确实允许在索引视图中使用GROUP BY甚至可以追溯到[SQL2000] [1]你确定你没有看到可更新视图的限制吗?

Following your edit. Pushing the concatenation into the table as a computed column worked for me

在您编辑之后。将连接作为计算列推送到表中对我有用

CREATE TABLE [Address]
(
    AddressId INT ,
    AddressNumber INT, 
    AddressName VARCHAR(50), 
    Town VARCHAR(50), 
    City VARCHAR(50), 
    Country VARCHAR(50),
    AddressLookup AS LTRIM(AddressNumber + ' ') + LTRIM(AddressName + ' ')  + LTRIM(Town + ' ') + RTRIM(City + ' ')
)
GO

CREATE VIEW AV WITH SCHEMABINDING
AS
SELECT      
    AddressId,  
    AddressNumber,  
    AddressName, 
    Town, 
        City,
        Country,
    COUNT_BIG(*) As AddCount,
    AddressLookup
FROM
    dbo.[Address]
GROUP BY
    AddressId,
    AddressNumber, 
    AddressName, 
    Town, 
    City, 
    Country,
    AddressLookup

   go 

CREATE UNIQUE CLUSTERED INDEX [ix_clustered] ON [dbo].[AV] 
(
    [AddressId] ASC
)

#3

The problem is the number of columns in the group by.

问题是组中的列数。

I actually have 11 columns im trying to group by, if i take away one of these columns then everything works fine.

我实际上有11列我试图分组,如果我拿走其中一列,那么一切正常。

#4

It is possible to have it on 2 columns:

它可以在2列上:

CREATE UNIQUE CLUSTERED INDEX [ix_clustered] ON [dbo].[AV] 
(
    [AddressId] ASC, 
    [CityId] ASC, 
)

#1