How create a file for index
-
Hy guys,
I have a question:
I have to separate the database file in different phisical disk: I need to reserve a disk for index file. My problem is : how can I create a file only for index (from Enterprise Manager or TSql)?
(the other disks are for log and data, but this is OK)
Thank
-
We've not used SQL Server 2005 yet, but in 2000, you can move your non-clustered indexes into a filegroup and then place the physical files for that index filegroup on their own physical disk. See filegroups>>index placement in BOL. I believe this will cause you to rebuild all of your indexes, but once you've created the filegroup, you can stage the indexes in as you wish.
-
after you've created your new filegroup, this script might help: it writes the sql statements you could use to move some of your indexes; i've used it just for regular indexes and unique indexes. you could modify it to move your clustered indexes as well.
Create view V_IXCOL
as
select SIK.* , C.name as ColName
from dbo.sysindexkeys SIK
inner join dbo.syscolumns C
on SIK.id = C.id
and SIK.colid = C.colid
go
Declare @NewFG varchar(128)
set @NewFG = 'YourNewFilegroup'
print '-- Move NCI Indexes to new FG'
print '-- keep column-ordinal / order equal to PK column-ordinal / order (asc/desc)'
select 'Create ' + case when (SIX.status & 2)<>0 then 'UNIQUE ' else '' end + 'index [' + SIX.name + '] on [' + U.name + '].[' + O.name + '] ( '+
IX1.ColName
+ case when IX2.ColName is null then '' else ', ' + IX2.ColName end
+ case when IX3.ColName is null then '' else ', ' + IX3.ColName end
+ case when IX4.ColName is null then '' else ', ' + IX4.ColName end
+ case when IX5.ColName is null then '' else ', ' + IX5.ColName end
+ case when IX6.ColName is null then '' else ', ' + IX6.ColName end
+ case when IX7.ColName is null then '' else ', ' + IX7.ColName end
+ case when IX8.ColName is null then '' else ', ' + IX8.ColName end
+ case when IX9.ColName is null then '' else ', ' + IX9.ColName end
+ case when IX10.ColName is null then '' else ', ' + IX10.ColName end
+ case when IX11.ColName is null then '' else ', ' + IX11.ColName end
+ case when IX12.ColName is null then '' else ', ' + IX12.ColName end
+ case when IX13.ColName is null then '' else ', ' + IX13.ColName end
+ case when IX14.ColName is null then '' else ', ' + IX14.ColName end
+ case when IX15.ColName is null then '' else ', ' + IX15.ColName end
+ case when IX16.ColName is null then '' else ', ' + IX16.ColName end
+ case when IX17.ColName is null then '' else ', ' + IX17.ColName end
+ case when IX18.ColName is null then '' else ', ' + IX18.ColName end
+ ' ) WITH DROP_EXISTING on [' + @NewFG + '] ' + char(10) + 'GO '
from V_IXCOL IX1
inner join dbo.sysobjects O
on IX1.id = O.id
and o.xtype = 'U'
inner join dbo.sysUsers U
on O.Uid = U.Uid
inner join dbo.sysindexes SIX -- no INFORMATION_SCHEMA available for this info
on IX1.id = SIX.id and IX1.indid = SIX.indid
and SIX.indid between 2 and 254 -- Select only NCI
and SIX.name not like '[_]WA[_]%'
left join V_IXCOL IX2
on IX1.id = IX2.id and IX1.keyno = 1 and IX2.keyno = 2
left join V_IXCOL IX3
on IX1.id = IX3.id and IX1.keyno = 1 and IX3.keyno = 3
left join V_IXCOL IX4
on IX1.id = IX4.id and IX1.keyno = 1 and IX4.keyno = 4
left join V_IXCOL IX5
on IX1.id = IX5.id and IX1.keyno = 1 and IX5.keyno = 5
left join V_IXCOL IX6
on IX1.id = IX6.id and IX1.keyno = 1 and IX6.keyno = 6
left join V_IXCOL IX7
on IX1.id = IX7.id and IX1.keyno = 1 and IX7.keyno = 7
left join V_IXCOL IX8
on IX1.id = IX8.id and IX1.keyno = 1 and IX8.keyno = 8
left join V_IXCOL IX9
on IX1.id = IX9.id and IX1.keyno = 1 and IX9.keyno = 9
left join V_IXCOL IX10
on IX1.id = IX10.id and IX1.keyno = 1 and IX10.keyno = 10
left join V_IXCOL IX11
on IX1.id = IX11.id and IX1.keyno = 1 and IX11.keyno = 11
left join V_IXCOL IX12
on IX1.id = IX12.id and IX1.keyno = 1 and IX12.keyno = 12
left join V_IXCOL IX13
on IX1.id = IX13.id and IX1.keyno = 1 and IX13.keyno = 13
left join V_IXCOL IX14
on IX1.id = IX14.id and IX1.keyno = 1 and IX14.keyno = 14
left join V_IXCOL IX15
on IX1.id = IX15.id and IX1.keyno = 1 and IX15.keyno = 15
left join V_IXCOL IX16
on IX1.id = IX16.id and IX1.keyno = 1 and IX16.keyno = 16
left join V_IXCOL IX17
on IX1.id = IX17.id and IX1.keyno = 1 and IX17.keyno = 17
left join V_IXCOL IX18
on IX1.id = IX18.id and IX1.keyno = 1 and IX18.keyno = 18
where IX1.keyno = 1
and not exists (select *
from sysobjects PK
where xtype = 'PK'
and PK.parent_obj = O.id
and PK.name = SIX.name )
order by U.name , O.name, SIX.name
go
drop view V_IXCOL
go
RESULTS:
Create UNIQUE index [UQCNTTYPE] on [dbo].[CMTYPE] ( CONTACTTBLKEY, CONTACTTYPETBLKEY ) WITH DROP_EXISTING on [YourNewFilegroup] GO
Lowell
--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible! -
thank to everybody
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply