SQL Server Create View Index which contains distinct or group by

Question

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.