SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Index Location?


Index Location?

Author
Message
luckysql.kinda
luckysql.kinda
Hall of Fame
Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)

Group: General Forum Members
Points: 3166 Visits: 659
Index Location?

I have four filegroup FG1, FG2, FG3, FG4
Can anyone tell me in which filegroup my index will be located. I think this would be in the same filegroup where my table is located. M I correct?
This is told that the system file reside in primary filegroup only. This there any chance that the system files can be on any other secondary file?

-LK
Adi Cohn
Adi Cohn
SSChampion
SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)

Group: General Forum Members
Points: 14832 Visits: 6621
When you create the index, you can specify the file group that you want it be on. If you don’t specify where to create the index, it will be created on the same file group that the index’s table exists on.
Most system objects will be on the primary file group, but there are some system objects that will be on every file. For example GAM pages, SGAM pages, etc’ will be on every data file. System tables (such as sys.objects, sys.database_files, etc)’ will always reside on the primary file group.

Adi

--------------------------------------------------------------
To know how to ask questions and increase the chances of getting asnwers:
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
luckysql.kinda
luckysql.kinda
Hall of Fame
Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)

Group: General Forum Members
Points: 3166 Visits: 659
Adi here is one twist according to BOL:

"Because the leaf level of a clustered index and the data pages are the same by definition, creating a clustered index and using the ON partition_scheme_name or ON filegroup_name clause effectively moves a table from the filegroup on which the table was created to the new partition scheme or filegroup."

It means that the filegroup for clustered index and table will remain same. The non clustered indexes can be placed on a different filegroup.

-LK
PaulB-TheOneAndOnly
PaulB-TheOneAndOnly
One Orange Chip
One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)

Group: General Forum Members
Points: 26531 Visits: 4639
luckysql.kinda (9/14/2009)
Adi here is one twist
that's not a twist, that's a "by design feature" :-D

_____________________________________
Pablo (Paul) Berzukov

Author of Understanding Database Administration available at Amazon and other bookstores.

Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.
Adi Cohn
Adi Cohn
SSChampion
SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)

Group: General Forum Members
Points: 14832 Visits: 6621
This is done on purpose. When you create a clustered index on a heap you might want to move the table to a different file group, but you might want to keep the table on the same file group that it was created on. The table and clustered index have to be on the same file group, but when you create the clustered index if you won’t specify it’s location, it will be created on the same file group as the table. If you will specify a specific file group, it will be created on that file group and the table will be moved to this file group. The only difference when you create a clustered index or a nonclustered index, is that the table might be moved, but this has nothing to do with the way that the server “decides” on which file group the index will be created. At both cases (creating clustered and nonclustered index) the way this “decision” is dictated to the server by the user’s input.

Adi

--------------------------------------------------------------
To know how to ask questions and increase the chances of getting asnwers:
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
PaulB-TheOneAndOnly
PaulB-TheOneAndOnly
One Orange Chip
One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)

Group: General Forum Members
Points: 26531 Visits: 4639
Adi Cohn (9/14/2009)
This is done on purpose. When you create a clustered index on a heap you might want to move the table to a different file group, but you might want to keep the table on the same file group that it was created on. The table and clustered index have to be on the same file group, but when you create the clustered index if you won’t specify it’s location, it will be created on the same file group as the table. If you will specify a specific file group, it will be created on that file group and the table will be moved to this file group. The only difference when you create a clustered index or a nonclustered index, is that the table might be moved, but this has nothing to do with the way that the server “decides” on which file group the index will be created. At both cases (creating clustered and nonclustered index) the way this “decision” is dictated to the server by the user’s input.

Adi


Pinch I'm sorry but as written this is wrong.

The reason why a clustered index ALWAYS goes to the same filegroup the table goes is because the leaf level of the index is actually sitting inside the base table.

_____________________________________
Pablo (Paul) Berzukov

Author of Understanding Database Administration available at Amazon and other bookstores.

Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.
Adi Cohn
Adi Cohn
SSChampion
SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)

Group: General Forum Members
Points: 14832 Visits: 6621

Pinch I'm sorry but as written this is wrong.

The reason why a clustered index ALWAYS goes to the same filegroup the table goes is because the leaf level of the index is actually sitting inside the base table.


I never claimed that the clustered index will be on a different file group then the table. I do claim however that that when I create a clustered index, it doesn’t have to be created the same file group that the table already exists on. Of course when I create it on a different file group, the table will be transferred to the file group that the new clustered index will be created on. This means that sometime the clustered index will be created on the same file group as the table, but other times it will be created on a different file group and the table will be moved to this file group. Here is a small demo, that shows it:

--Creating a database with few file groups
CREATE DATABASE TestDB
ON
PRIMARY
(NAME = PrimaryFile,
FILENAME = 'c:\TestDB.mdf',
SIZE = 100MB),
FILEGROUP DefaultFileGroup
( NAME = DefaultFile,
FILENAME = 'c:\DefaultFile.ndf',
SIZE = 100MB)
LOG ON
(NAME = DemoDBLog,
FILENAME = 'c:\TestDB.ldf',
SIZE = 100MB)
go


use TestDB
go
-- Setting the default file group
ALTER DATABASE TestDB
MODIFY FILEGROUP DefaultFileGroup default
go

--Creating a demo table in the default file group
create table MyTable (i int not null) on DefaultFileGroup
go

--see that the table is created on the DefaultFileGroup
exec sp_help MyTable

--Adding a clustered index without speciying where it will be located
create clustered index cix_MyTable_i on MyTable (i) on [primary]
go

--The table is now on the primary data file and not on the default data file
sp_help MyTable

--cleanup
use master
go
drop database TestDB



Adi

--------------------------------------------------------------
To know how to ask questions and increase the chances of getting asnwers:
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
luckysql.kinda
luckysql.kinda
Hall of Fame
Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)

Group: General Forum Members
Points: 3166 Visits: 659
Awesome example Adi. Thanks

-lk
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum







































































































































































SQLServerCentral


Search