SQL Server Create View Index which contains distinct or group by [closed]

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

Oswin

SQL Server does allow `GROUP BY' in indexed views. What version are you on? – Siderolite

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.

Oldcastle

when i use the group by and count_big(*) if get the following error when trying to create my index on the view: Cannot create the clustered index 'Idx_AddressId' on view 'AddressLookup' because the select list of the view contains an expression on result of aggregate function or grouping column. Consider removing expression on result of aggregate function or grouping column from select list. – Oswin

You must be using some other aggregate function beyond COUNT_BIG. You won't be able to use the AVG, MAX, MIN, STDEV, STDEVP, VAR, or VARP aggregate functions. See the documentation I linked to for full details. – Oldcastle

Hi Daniel i have no other aggregates in the view... as you can see in the post its when i concatenate columns into one that causes my issue – Oswin

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
)

Siderolite

i get errors when trying to create indexes my views which has a distinct or group by. Cannot create the clustered index 'Idx_AddressId' on view 'AddressLookup' because the select list of the view contains an expression on result of aggregate function or grouping column. Consider removing expression on result of aggregate function or grouping column from select list. – Oswin

@Cypher - Can you post your view definition? I think it probably just needs a slight tweak. – Siderolite

query as above, its the line where i append the address columns together that prevent the index being added – Oswin

Hi Martin, it still doesn't allow me to create the index with those Concatenating columns CREATE UNIQUE CLUSTERED INDEX Idx_Address_AddressId ON [AddressLookup] ([AddressId]) gives the error " Cannot create the clustered index 'Idx_AddressId' on view 'AddressLookup' because the select list of the view contains an expression on result of aggregate function or grouping column. Consider removing expression on result of aggregate function or grouping column from select list." – Oswin

@Cypher - Looks like if you push that into a computed column it's fine with it! – Siderolite

the existing table contains data that is bulk imported from file which i dont want to change, that why i thought the view would be ideal to dedupe the data and then index the deduped data. Maybe i should just transfer it to another table – Oswin

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.

Oswin

Answer 4

It is possible to have it on 2 columns:

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

Calf