Create Tablespace Syntax in MS SQL Server 2008

I want to create the tablespace for tables and constraints (indexes) in the database.  Please tell me the syntax for it.
tgatif2000Asked:
Who is Participating?
 
gothamiteCommented:
The equivalent of an Oracle tablespace in SQL Server is a Database Fillegroup. This is a group of files and your place your objects on a filegroup, which spreads them across the files. To add a filegroup do

ALTER DATABASE <your_db_name> ADD FILEGROUP <logical_name_for_filegroup>

you then do this command to add a file

ALTER DATABASE <your_db_name> ADD FILE ( NAME = N'<logical_name_for_file>', FILENAME = N'<full_path_to_file>.ndf' , SIZE = 3072KB , FILEGROWTH = 1024KB ) TO FILEGROUP [<filegroup_name>]



Then if you want (say) an index to be created on this filegroup you do

CREATE NONCLUSTERED INDEX myindex1 ON mytable (mycol) ON my_new_filegroup_name
1
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.