Storing indexes

[djhawthorn]

Is there a way to store SQL database indexes on a dedicated disk?

If so, how do you go about doing it?

MCSE NT4/W2K

 

[DrSql]

Yes you can. Here is a simple example

Right click on PUBS db properties
Create a file group (PUBIDX)
Then Create a seconday data file to your new drive and select PUBIDX the File group
AUX_PUB E:\MSSQL\AUX_PUB.ndf PUBIDX

And create the New Index, and last option in the screen is to select the FileGroup and select PUBIDX.

All steps can be scripted also.

Dr.Sql
Good Luck.

 

[SQLDenis]

Keep in mind that with clustered indexes all the data will be moved also (since the xlustered indes is part of the table and vice-versa)
A non clustered index has a pointer to the table and can be seperated from the table by placing it on a different file group



Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/

 

[djhawthorn]

Ok, so I take it its a per-database setting, not a global SQL one?

Basically we are setting up a 2-instance SQL cluster, and on the SAN we want 5 drives; quorum, data, logs, indexes, and temp, with all databases residing on the data drive, all transaction logs on the logs drive... etc.

Thanks for the help.

MCSE NT4/W2K

 

[djhawthorn]

Related questions: when you create a table, it creates some sort of index I assume? Is that a clustered index (i.e., built into the mdf database file?)

Is there any pages that explain the different types of SQL table indexes? Clustered/Non-Clustered/Full-text/.../etc?

I've had a brief hunt around but can't find anything that gives a general overview/explaination of SQL indexes, the types available etc.


MCSE NT4/W2K