[dba-SQLServer] Indexes not all listed

Asger,

Thanks for that, I can do that!

Next question.  I am looking at doing a vertical partition of this table.  The table logically 
groups on a set of 84 fields (name / age / education etc, plus another set of about 150 fields that 
are heavily used, and the rest of the fields rarely (but occasionally) used.

Thus I could split this thing into at least three tables, related one to one.  In looking at it 
closely I might in fact "rearrange the columns" such that all of the heavily used fields are in a 
single table (including the name / age / education) and place all the rest into a single "other" table.

My question then is whether the indexes for each field of these vertical split tables should be in 
the same file as the data fields, or split out.  I guess I still don't have a handle on what part 
the data field plays.  If the table has a clustered index, and then an index on each field, with the 
data in the index, at what point is the actual data back in the field of the table ever used?  I 
thought that with the data in the index, if the index is used (and the index / field makes it a lot 
more likely) then I thought that the data itself was actually plucked out of the index leaf.

If that is the case, then having the index on a separate database file (without the table itself), 
and having that database file sitting on a flash drive, I would have the smallest possible footprint 
to store on the flash.

John W. Colby
www.ColbyConsulting.com


Asger Blond wrote:
>> On that note, I do have a question, whether it is possible to specify what
>> database files a specific index goes into.  It happens that this table from
>> hell uses specific columns much more frequently than others, and some
>> columns (so far) not at all, or only once or twice.  If I could create
>> multiple data files, and then move the indexes for the first 200 fields
> into one file and the rest into another, then I could move that heavily used
>> index file onto a flash and leave the rest on rotating media.
> 
> Yes, it's possible and IMO would be a good solution in your case.
> Like any object in a SQL Server database an index is created on a filegroup,
> and if you haven’t specifically created filegroups for your database it is
> created on a filegroup called Primary.
> 
> You can add a new filegroup and specify a file for it this way:
> ALTER DATABASE YourDB ADD FILEGROUP YourNewFilegroup
> GO
> ALTER DATABASE YourDB ADD FILE (NAME = 'YourDB_IndexFile', 
> FILENAME = 'X:\YourFolder\YourDB_IndexFile.ndf',
> SIZE = 500GB)
> TO FILEGROUP YourNewFilegroup
> GO
> 
> You then create the indexes on that filegroup (effectively on the specified
> file) this way:
> CREATE INDEX YourIndex ON YourTable(YourColumn) ON YourNewFilegroup
> GO
> 
> Asger