Mar 20

How to move data or a table from one data file to another in the same filegroup! – Part 1 of 2

Sorry! This isn’t something you can do. I wanted to title this post in a way that would draw people to it for my rant. I am ranting because I had a friend who went to an interview where he was asked how you move data from one file to another. He didn’t know, so he asked me…

Friend: Do you know how to move data from one file to another?

Me: You mean between filegroups. Yes.

Friend: No, between files in the same filegroup.

Me: You don’t. Why?

Friend: That’s what I thought too, but I learned at an interview today.

Me (laughing): Really? Can you share this knowledge with the rest of the SQL Server community? Well… start with me.

Friend: You rebuild the clustered index on the other file or you limit the growth of the file you want to move off of and then rebuild the clustered index so that it moves to the other file.

Me: They told you this? Have you ever tried to create an object on a file?

Friend: No.

Me: Did you test it out? Because, it can’t be done…

Friend: No, but…

Let me tell you friends… that as far as I know, this cannot be done. Whoever asked this question doesn’t understand the file structure in SQL Server or how data gets allocated. This conversation made me SOOOO mad because it means that there are people out there asking bad interview questions or they clearly explained the question incorrectly. If you are interviewing someone, PLEASE make sure you know the correct answer to your own questions and make sure you are right. In the next post, I will post the DDL and DML to show this.

Leave a Reply