THE SQL Server Blog Spot on the Web

Welcome to - The SQL Server blog spot on the web Sign in | |
in Search

Linchi Shea

Checking out SQL Server via empirical data points

Table or index that goes nowhere

SQL Server allows you to create a table or an index on a filegroup that has no file assigned to it. Because there is no data file to hold your data, the table or the index thus created cannot be used. This may not be a problem because often you would probably use the table or the index 'immeidately', and would realize the problem. Well, you wouldn't be able to go anywhere without at least one data file.

But there are cases, especially with an index, where the problem may not be discovered until some time much later, and that could cause some confusion. For instance, you may have a partition view that includes partitions for next month, next quater, or next year. And because there is no data for the next month, quater, or year, you could have an index that exists happily on a filegroup with no file, and you could happily query away the partition view. That is, until you try to insert data into the partition view, even though the data may not touch the next month, next quater, or next year.

Just a tidbit from field!

Published Thursday, April 29, 2010 10:17 AM by Linchi Shea
Filed under:

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS



retracement said:

Interesting, probably as you say get caught quite quickly in production but interesting nevertheless. I wonder if there is a good use that this behavior could be put to for a particular situation?

June 7, 2010 10:18 AM

Leave a Comment


About Linchi Shea

Checking out SQL Server via empirical data points

This Blog


Privacy Statement