Sign in
 
Home20122008 R2200820052000LibraryForums
SQL Server >
> 
> 
Sql server standard edition 2008 r2 file group’s(.ndf) purpose?

Answered Sql server standard edition 2008 r2 file group’s(.ndf) purpose?

  • Monday, February 18, 2013 2:34 PM
     
     
    Sign In to Vote
    0

    Hi All,

    I need to understood what purpose .ndf file is used in sql server 2008 r2 standard edition.

    While partition tables .ndf file used. I hope table partition only possible in sql server Enterprise edition.

    Table partition is not possible in standard edition.  Than what purpose .ndf file used in sql server standard edition.

    Please help me on this. Advance thanks.

    Thanks

    Venkadesan.E

     

All Replies

  • Monday, February 18, 2013 2:40 PM
     
     Answered
    Sign In to Vote
    1

    .ndf files are called 2ndry datafile.Yes, table partition is an Enterprise only feature.

    its best practice to keep the system tables in primary and keep user tables in 2ndry.  some time for performance benefit you can keep the base table and index on separately. some time its used for data archiving..

    it doesn't have to be .ndf it can be anything but not .mdf

    vt 

    Please mark answered if I've answered your question and vote for it as helpful to help other user's find a solution quicker


     
  • Monday, February 18, 2013 2:42 PM
     
     
    Sign In to Vote
    1
    The reason for ndf files is for secondary files(in primary filegroup) and  for all files in other filegroups. a database will have only one mdf which isstores all the metadata of the database
    and all other information can be stored either in mdf or additional files/filegroups. these additional files/filegroups are saved with extension .ndf (by default).

    table partition is one way you can split the data across multiple files but you can also create tables/indexes on different filegroups, so these files under the filegroups will use .ndf extension(by default).So, the files inside these filegroups will also have.ndf file extension. 

    Hope it Helps!!



    • Edited by Stan210 Monday, February 18, 2013 2:50 PM
    •  
     
  • Monday, February 18, 2013 3:00 PM
     
     
    Sign In to Vote
    0

    Hi Vt,

    Thanks for valuable information.

    Can you please tell me how to store particular tables and index in .ndf file?

    Thanks

    Venkadesan.E  

     
  • Monday, February 18, 2013 3:07 PM
     
     Answered
    Sign In to Vote
    0

    Please read

    http://decipherinfosys.wordpress.com/2007/08/14/moving-tables-to-a-different-filegroup-in-sql-2005/

    vt

    Please mark answered if I've answered your question and vote for it as helpful to help other user's find a solution quicker

     
  • Monday, February 18, 2013 3:29 PM
     
     
    Sign In to Vote
    0

    you cannot tell sql server to store a table/index on a particular file. you can only store them on a filegroup. internally sql server splits it proportionally between the files present inside the filegroup. 

    Example: create table Test (sno int, sname varchar(20)) on Secondary

    This creates a table on the secondary filegroup.

    Hope it Helps!!



    • Edited by Stan210 Monday, February 18, 2013 3:30 PM
    •