Need help

Can we make a Index on 2 or more tables

Table a:
Col1 int
col2 varchar

Table b:
Col1 int
col2 varchar

can you have a single index for two tables a and b on the column Col1.
Is this possible in SQL-Server.

As far as i know you can make an index only on one Table.[posted and mailed, please reply in news]

bhushanvinay (bhushanvinay@.mail.com) writes:
> Can we make a Index on 2 or more tables
> Table a:
> Col1 int
> col2 varchar
> Table b:
> Col1 int
> col2 varchar
> can you have a single index for two tables a and b on the column Col1.
> Is this possible in SQL-Server.
> As far as i know you can make an index only on one Table.

You can create an indexed view. I don't know the relation between a and b,
but assuming that they are partitions of some sort, you could do:

CREATE VIEW ab (Col1, Col2) WITH SCHEMABINDING AS
SELECT Col1, Col2 FROM a
UNION ALL
SELECT Col1, Col2 FROM b
go
CREATE UNIQUE CLUSTERED INDEX ON ab (Col1)

Note that there are a couple SET options that must be ON, and one that
must be OFF for indexed views to be available. Check the topics for
CREATE VIEW and CREATE INDEX in Books Online for more details.

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp