What is Oracle Tablespace equivalent in SQL Server

Question

I've been tasked with converting an Oracle database to SQL Server based on a DDL. An example Oracle create table statement is as follows:

  CREATE TABLE "RENTAL"."ADDRESS" 
   (    "ID" NUMERIC(8,0), 
    "TOWN" NVARCHAR(40), 
    "COUNTY" NVARCHAR(40), 
    "POSTCODE" NVARCHAR(10)
   ) SEGMENT CREATION IMMEDIATE 
  PCTFREE 10 PCTUSED 40 INITRANS 10 MAXTRANS 255 
 NOCOMPRESS LOGGING
  STORAGE(INITIAL 163840 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "AAA_123" ;

What is the TABLESPACE here and does it have an equivalent in SQL Server? There seems to be 6 different tablespaces used in the DB.

(I always thought the part of the table name preceding the dot (RENTAL in this case) was called the tablespace)

Answer 1

Tablespaces are physical structures on disk, directories or files, that hold table data, indexes, and other things. One tablespace can hold objects that belong to multiple tables. Each tablespace can reside on a different physical device and have different configuration parameters, allowing a DBA to fine-tune I/O performance and use storage devices more efficiently (e.g. put data that is rarely accessed onto slower, cheaper disks).

The SQL Server's equivalent of Oracle tablespaces are filegroups.

Answer 2

The equivalent for TABLESPACEs in oracle are FILEGROUPs in MS SQL Server. Generally the word "storage" is the one used in RDBMS litterature. For a table you can have two different storages :

• the FILEGROUP for relationnel data
• the FILEGROUP for LOBs data

As an example, this is a script that I usually uses to create all my databases :

CREATE DATABASE MY_DATABASE
ON PRIMARY  
   (NAME       = F_sys,  
    FILENAME   = 'C:\SQL_Server\Databases\MY_DATABASE_METADATA.mdf',  
    SIZE       = 128 MB,  
    FILEGROWTH = 64 MB ),
FILEGROUP DATAS DEFAULT
   (NAME       = F_data1,  
    FILENAME   = 'C:\SQL_Server\Databases\MY_DATABASE_DATA1.ndf',  
    SIZE       = 10 GB,  
    FILEGROWTH = 64 MB ),
   (NAME       = F_data2,  
    FILENAME   = 'C:\SQL_Server\Databases\MY_DATABASE_DATA2.ndf',  
    SIZE       = 10 GB,  
    FILEGROWTH = 64 MB ),
FILEGROUP LOBS
   (NAME       = F_lobs,  
    FILENAME   = 'C:\SQL_Server\Databases\MY_DATABASE_LOBS.ndf',  
    SIZE       = 10 GB,  
    FILEGROWTH = 64 MB)
LOG ON  
   (NAME       = F_tran,  
    FILENAME   = 'C:\SQL_Server\Databases\MY_DATABASE_TRAN.ldf', 
    SIZE       = 10 GB,  
    FILEGROWTH = 64 MB );  
GO  

This create a storage for system tables (FILEGROUP PRIMARY which is mandatory), then a file group for all table and indexes (FILEGROUP DATAS which is by default for all objectc, the a storage for all LOBs (FILEGROUP LOBS) and finaly the storage, which is not a filegroup, for the transaction log.

When creating a table that does not contain any LOBs I can write :

CREATE TABLE table_name ( <table_description> );

And that's all to create the table into the DATAS filegroup.

When creating a table that contaisn some LOBs I must write :

CREATE TABLE table_name ( <table_description> ) 
   ON DATAS
      TEXTIMAGE ON LOBS;

This wil store all pure relational data into the DATAS storage and all LOBs data into the storage LOBS...