NDF file

I have a database and trying to put couple of the tables into a ndf
file instead of mdf file, any ideas how can I achieve it?
Thanks a lot.1. Create a filegroup a put the NDF file in it.
2. Create/Alter the tables and point them to the new filegroup.
<huohaodian@.gmail.com> wrote in message
news:1184769009.426259.96790@.e16g2000pri.googlegroups.com...
>I have a database and trying to put couple of the tables into a ndf
> file instead of mdf file, any ideas how can I achieve it?
> Thanks a lot.
>|||Hi
create database test
on primary(name = 'datafile1', filename = 'c:\temp\datafile1'),
filegroup user_fg
(name = 'datafile2', filename = 'c:\temp\datafile2')
log on
(name = 'logfile1', filename = 'c:\temp\logfile1')
go
use test
go
create table t1(col1 int)
create table t2(col1 int) on [primary]
create table t3(col1 int) on user_fg
select
object_name(i.id) as table_name,
groupname as [filegroup]
from sysfilegroups s, sysindexes i
where i.id in (object_id('t1'), object_id('t2'), object_id('t3'))
and i.indid < 2
and i.groupid = s.groupid
<huohaodian@.gmail.com> wrote in message
news:1184769009.426259.96790@.e16g2000pri.googlegroups.com...
>I have a database and trying to put couple of the tables into a ndf
> file instead of mdf file, any ideas how can I achieve it?
> Thanks a lot.
>|||On Jul 18, 10:36 am, "Uri Dimant" <u...@.iscar.co.il> wrote:
> Hi
> create database test
> on primary(name = 'datafile1', filename = 'c:\temp\datafile1'),
> filegroup user_fg
> (name = 'datafile2', filename = 'c:\temp\datafile2')
> log on
> (name = 'logfile1', filename = 'c:\temp\logfile1')
> go
> use test
> go
> create table t1(col1 int)
> create table t2(col1 int) on [primary]
> create table t3(col1 int) on user_fg
> select
> object_name(i.id) as table_name,
> groupname as [filegroup]
> from sysfilegroups s, sysindexes i
> where i.id in (object_id('t1'), object_id('t2'), object_id('t3'))
> and i.indid < 2
> and i.groupid = s.groupid
> <huohaod...@.gmail.com> wrote in message
> news:1184769009.426259.96790@.e16g2000pri.googlegroups.com...
> >I have a database and trying to put couple of the tables into a ndf
> > file instead of mdf file, any ideas how can I achieve it?
> > Thanks a lot.
Is there a way in the enterprise manager I can manually specify tables
into secondary ndf filegroups?
Thanks,|||> Is there a way in the enterprise manager I can manually specify tables
> into secondary ndf filegroups?
You need to manually either create the table on a specific filegroup or
drop/re-create the clustered index there. See CREATE TABLE and CREATE INDEX
in Books Online.
A|||On Jul 19, 12:59 pm, "Aaron Bertrand [SQL Server MVP]"
<ten...@.dnartreb.noraa> wrote:
> > Is there a way in the enterprise manager I can manually specify tables
> > into secondary ndf filegroups?
> You need to manually either create the table on a specific filegroup or
> drop/re-create the clustered index there. See CREATE TABLE and CREATE INDEX
> in Books Online.
> A
I am still trying to find out where I can specific filegroup for the
table I manually created within enterprise manager.
The way I created table is right click Tables -> New Tables ...|||> I am still trying to find out where I can specific filegroup for the
> table I manually created within enterprise manager.
> The way I created table is right click Tables -> New Tables ...
Well, when you create a new table, do you see the properties window on the
right? Under "Table designer" there is a property called "Regular Data
Space Specification."
Anyway, don't do that. Open a query window and say:
CREATE TABLE dbo.foo (id INT) ON filegroup_name;
I guarantee you that you will not be worse off knowing and understanding the
CREATE TABLE syntax vs. being able to point and click around in a GUI.
--
Aaron Bertrand
SQL Server MVP