July 6, 2010 at 10:45 am
When there are several data files, is it possible to determine which a table is saved to?
Many thanks for any input in advance.
July 6, 2010 at 10:50 am
It will be in all files are that are part of the filegroup (or filegroups) it is in.
Edit:
Just to clarify, a table can be in multiple filegroups by having indexes in different filegroups than the clustered index or the heap if there is no clustered index.
Database data files are part of only a single filegroup, but a filegroup can have many data files.
July 6, 2010 at 10:51 am
If there are multiple filegroups, you can fine out which one the table is in by querying sys.partitions and (I think) sys.data_spaces. If you're talking about multiple files in one filegroup, the table will likely be on all of them as allocations are done in a round-robin method for files in a filegroup.
There's undocumented features for seeing which pages are allocated to a table, but for all intents and purposes, if there are multiple files in a filegroup, consider a table on that filegroup to be spread across all the files.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
July 6, 2010 at 1:18 pm
Not sure if this helps
SELECT t1.TABLE_NAME,
FILEGROUP_NAME(t2.groupid) as FileGroup
FROM INFORMATION_SCHEMA.TABLES t1
INNER JOIN sysindexes t2
ON OBJECT_ID(t1.TABLE_NAME) = t2.[id]
July 6, 2010 at 1:30 pm
sysindexes is deprecated, included for backward compatibility with SQL 2000 and should not be used in new development.
To find what filegroup a table or is on, query sys.partitions and sys.data_spaces.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply