Indexed Views on Sql 2005

Question

Indexed Views on Sql 2005

mrinal.jaiswal

Grasshopper

Points: 13
More actions
February 12, 2010 at 5:40 am

#141281

Hi,
I want to create an Indexed view on my database. But the problem is the result set my query returns does not have any unique key, means the result set will have a composite key not a primary key.
Please help and let me know if its possible to create an indexed view without having a clustered index.
If this is not possible then please let me know how can i make my view fast and make use of indexes.
Thanks,
Mrinal Jaiswal

Viewing 12 posts - 1 through 11 (of 11 total)

You must be logged in to reply to this topic. Login to reply

Jeffrey Williams SSC Guru Points: 90257 More actions · Answer 1

There is nothing that states that a clustered index must be a primary key or unique. You can create your clustered index on whatever columns you need.

Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

― Charles R. Swindoll

How to post questions to get better answers faster
Managing Transaction Logs

Bhuvnesh SSC Guru Points: 59351 More actions · Answer 2

there are two things :

first since you have composite key , it means you can have clustered index on it

and second thing i guess , we can have non unique clustered index

but for assurance please see BOL

-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)

wschampheleer SSCertifiable Points: 5504 More actions · Answer 3

Jeffrey is right about the clustered index not having to be unique, but when it comes to indexed views, BOL says:

The first index created on a view must be a unique clustered index.

Regards,

Willem
http://wschampheleer.wordpress.com[/url]

Jeffrey Williams SSC Guru Points: 90257 More actions · Answer 4

Yes - I missed that...sorry, you do need a column that is going to be unique to build the indexed view.

Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

― Charles R. Swindoll

How to post questions to get better answers faster
Managing Transaction Logs

sqlvogel SSCrazy Eights Points: 9526 More actions · Answer 5

mrinal.jaiswal (2/12/2010)
Hi,
I want to create an Indexed view on my database. But the problem is the result set my query returns does not have any unique key, means the result set will have a composite key not a primary key.

A composite key is unique and that's all that is required for an indexed view.

Or maybe you meant you don't have a key at all. In that case, change your query so that it does return unique rows. You wouldn't want duplicate rows in your view, would you?

mrinal.jaiswal Grasshopper Points: 13 More actions · Answer 6

Hi,

Thanks for reply, as mentioned I just have a composite key in the view. But not able to create Clustered Index for Composite key, if its possible please send a small code snippet.

Thanks,

Mrinal Jaiswal

sqlvogel SSCrazy Eights Points: 9526 More actions · Answer 7

CREATE UNIQUE CLUSTERED INDEX indexname ON v1 (col1, col2, col3);

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 8

Why can you not create an index? Are you getting an error? If so what error?

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Paul White SSC Guru Points: 150467 More actions · Answer 9

-- Required connection settings for indexed views

SET ANSI_NULLS, ANSI_PADDING, ANSI_WARNINGS, ARITHABORT, CONCAT_NULL_YIELDS_NULL, QUOTED_IDENTIFIER ON;

SET NUMERIC_ROUNDABORT OFF;

GO

-- Database context

USE tempdb;

GO

-- Base table

CREATE TABLE dbo.Data (A INT NOT NULL, B INT NOT NULL, C INT NOT NULL, PRIMARY KEY (A, B));

GO

-- Sample data

INSERT dbo.Data (A, B, C) VALUES (1, 2, 3);

INSERT dbo.Data (A, B, C) VALUES (4, 5, 6);

INSERT dbo.Data (A, B, C) VALUES (7, 8, 9);

GO

-- View : must have WITH SCHEMABINDING specified

CREATE VIEW dbo.V WITH SCHEMABINDING AS SELECT A, B, C FROM dbo.Data;

GO

-- First index must be UNIQUE and CLUSTERED

CREATE UNIQUE CLUSTERED INDEX c ON dbo.V (A, B);

GO

-- Now we can create other indexes

CREATE NONCLUSTERED INDEX nc1 ON dbo.V (C) INCLUDE (B);

CREATE UNIQUE NONCLUSTERED INDEX nc2 ON dbo.V (A, C);

GO

-- Select from the view

SELECT A, B, C

FROM dbo.V WITH (NOEXPAND);

GO

-- Clean up

DROP VIEW dbo.V;

DROP TABLE dbo.Data;

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

Bhuvnesh SSC Guru Points: 59351 More actions · Answer 10

Paul nice example

but if the table is heavy transactional table and we have indexed view on that and then clustered index on it.

Will it improve the performance?

which will be better

select a, c from V where clause

select a, c from dbo.Data where clause

-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)

Paul White SSC Guru Points: 150467 More actions · Answer 11

Bhuvnesh,

Hello - and thanks. You seem to have a separate question there, so it would probably be better to discuss it on another thread rather than here 🙂

Paul

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi