SQL Server Create View Index which contains distinct or group by

Question

Hi there,

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).

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

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)

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

Cheers

Answer 1

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

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.

Taken from MSDN

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

Answer 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?

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
)

Answer 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.