How do you assign a particular table to a .mdf or .ndf file?

Posted on 2006-04-24
Last Modified: 2012-06-21
How do you assign the actual storage mechanism of a SQL Server Table (i.e. an .mdf or .ndf) to a particular .mdf?

In other words:
You have 2 tables in a given database but they are on separate OS files (.mdf or .ndf)?
Question by:michaelrobertfrench
    LVL 142

    Expert Comment

    by:Guy Hengel [angelIII / a3]
    add 2 filegroups to the database, and add 2 data files to the database, each one to a different filegroup.
    create the table into the filegroup.
    if the table is already created, add a clustered index to the table and drop it afterwards.
    if you have already a clustered index, drop it and recreate it to the filegroup

    Author Comment

    OK - how do you "create the table into the filegroup"?

    Author Comment

    I can see from the TSQL syntax of Create Table that there is an option that allows this but how can this be done for the GUI.  

    Further, how does the DBA know where the tables or pointing - to which filegroups?
    LVL 142

    Accepted Solution

    >OK - how do you "create the table into the filegroup"?
    in the Enterprise Manager, when you create a table / index, you have the "filegroup" combo box with the list of the filegroups (at least containing the PRIMARY filegroup).

    >Further, how does the DBA know where the tables or pointing - to which filegroups?
    1 table is in EXACTLY 1 filegroup. in the properties it is shown.

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Looking for New Ways to Advertise?

    Engage with tech pros in our community with native advertising, as a Vendor Expert, and more.

    Introduced in Microsoft SQL Server 2005, the Copy Database Wizard ( is useful in copying databases and associated objects between SQL instances; therefore, it is a good migration and upgrade tool…
    I wrote this interesting script that really help me find jobs or procedures when working in a huge environment. I could I have written it as a Procedure but then I would have to have it on each machine or have a link to a server-related search that …
    Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
    Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed

    779 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    17 Experts available now in Live!

    Get 1:1 Help Now