SQL Server vs Oracle Database Creation Similarities and Differences

By:   |   Updated: 2022-02-09   |   Comments   |   Related: More > Other Database Platforms


Problem

You might think creating a database for SQL Server versus Oracle would be somewhat similar, but in this tutorial we look at the differences of creating new databases in Oracle and then creating new databases in SQL Server to give you an idea of what needs to be done if you are ever tasked with creating a database for either of these database platforms.

Solution

There are different steps to be performed in each database system, you will see them described in the following sections and remember not all are mandatory and this is not a definitive guide on how this needs to be done. This is just a guideline and reference you can use to see the differences in the two database platforms. The below is done after the database system has been installed. Also, the below shows how databases can be created with commands so you learn SQL, not using a GUI.

Create Databases in Oracle

To create an Oracle database, you must choose the instance name (8 chars), create a parameter file (PFILE), create an instance (ORADIM), and finally create the database using PL/SQL. The official documentation is located here for administration and here for installation, these are both for Oracle version 19c.

The first step is choosing the instance name (SID), which is limited to 8 chars. This is the Site Identifier used to uniquely attach the shared memory segment called System Global Area (SGA). It must be unique in your network, changing it later is hard to do since it is referenced in all future steps regarding the database, so choose wisely. The equivalent in SQL Server can be up to 128 characters.

The second step is to create the parameter file, you can look at the sample located in the folder C:\app\Oracle19c\dbs\init.ora, DB_NAME doesn't need to match the SID but it's easier to manage if it does, and the parameter file name must be initSID.ora. The official documentation about the initialization parameters can be found here, and the available/deprecated parameters, their sample usage and their recommended values is in an article I've written which can be found here. The equivalent in SQL Server are "alter server", "sp_configure", "dbcc traceon", "alter database", and "alter database scoped configuration". In my case I created the simplest parameter file with the PowerShell commands below (define a multiline variable, add the only mandatory parameter, and write the file):

[email protected]" 
db_name=ORCL 
"@ 
Add-Content C:\APP\ORACLE19C\DATABASE\INITORCL.ORA $init 

The third step is to create a Windows service to run the database, in SQL Server the services are already created and running after you installed the software. To create the service for Oracle you need to run ORADIM from the command prompt which creates the Oracle Database, VSS Writer and Oracle Scheduler services, the documentation can be found here and the syntax is as follows:

oradim [-NEW -SID SID] | -SRVC service_name | -ASMSID SID | -ASMSRVC service_name [-SYSPWD password][-MAXUSERS number][-STARTMODE auto | manual] [-SRVCSTART system | demand] [-PFILE filename | -SPFILE] [-SHUTMODE normal | immediate | abort] [-TIMEOUT secs] [-RUNAS osusr[/ospass]]

Example:

oradim -NEW -SID prod -STARTMODE manual -PFILE "C:\app\username\admin\prod\pfile\init.ora"

Note you can omit the parameter file if it's already in the default location which it is in my case, so the command I ran was:

ORADIM -NEW -SID ORCL

Then you can verify the service is created by running this PowerShell command:

Get-Service -DisplayName '*Oracle*'

The output in my case is as follows:

Get-Service -DisplayName

If any service is stopped, you can start it with this PowerShell command:

net START OracleServiceORCL

Or you can start it with the PowerShell command below:

Start-Service OracleServiceORCL

Or you can start it using ORADIM, the documentation can be found here and the syntax is as follows:

oradim -STARTUP -SID SID | -ASMSID SID [-SYSPWD password] [-STARTTYPE srvc | inst | srvc,inst] [-PFILE filename | -SPFILE]

Example:

oradim -STARTUP -SID puma -STARTTYPE inst -PFILE C:\app\username\admin\prod\pfile\init.ora

Once the service has been created and is running, you can set the ORACLE_SID variable to the SID you chose in the first step, login to the database, and start the SGA and background processes as follows:

set ORACLE_SID=ORCL 
sqlplus / as sysdba 
STARTUP NOMOUNT 

Finally, you can proceed to the last step: creating the database.

Below you will find a table with various options for creating the database. On the left are the commands and on the right is a description and some examples. Whatever options you use, they all must be a single command. Although the instructions contain multiple parameters not all of them are mandatory; they depend on what action you're performing and if you want to override the defaults or not.

In Oracle there is no "MODEL" database like in SQL Server, because you only create one database per instance, but you can choose either container (CDB) and pluggable (PDB) databases. The documentation can be found here.

Command Description
CREATE DATABASE <DbName> DbName must match DB_NAME init param (mandatory) or be omitted, can be up to 8 bytes containing alphanumeric characters and _#$, no other characters allowed neither European/Asian, must start with a letter, and will be converted to uppercase.
USER SYS IDENTIFIED BY <SysPwd> Optional unless you specify "USER SYSTEM", if not specified "change_on_install" is assigned. The equivalent in SQL Server is the "sa" user whose password is chosen at the time of the software installation.
USER SYSTEM IDENTIFIED BY <SystemPwd> Optional unless you specify "USER SYS"
CONTROLFILE REUSE Use to overwrite files specified in CONTROL_FILES init param when they exist. Invalid when you specify MAXLOGFILES, MAXLOGMEMBERS, MAXLOGHISTORY, MAXDATAFILES and MAXINSTANCES.
MAXDATAFILES <MaxDataFiles> MaxDataFiles: integer, limited by DB_FILES init param. Default 32, max 65534.
MAXINSTANCES <MaxInstances> MaxInstances: integer, takes precedence over INSTANCES init param, min 1, max 1055.
CHARACTER SET <CharSet> CharSet: any from this list except AL16UTF16. Default is US7ASCII but the recommended is AL32UTF8 suitable for practically any written language of the world, the equivalent in SQL Server is COLLATE.
NATIONAL CHARACTER SET <AL16UTF16|UTF8> For NCHAR, NCLOB or NVARCHAR2.
SET DEFAULT <BIGFILE|SMALLFILE> TABLESPACE For SYSTEM, SYSAUX and any newly created tablespace. BIGFILE: one data/temp file, max 128TB in 32K blocks or 32TB in 8K blocks, it can be backed up by RMAN in parallel using multiple channels, simplifies management and improves performance since checkpoint operations no longer must update so many data file headers; SMALLFILE (default): 1022 data/temp files, each containing 128GB in 32K blocks or 32GB in 8K blocks.
LOGFILE GROUP <RedoFileGroup> RedoFileName|(+DiskGroupName (/ DbName / ONLINELOG / group_group# . FileNumber . IncarnationNumber) | (ONLINELOG) | AliasName)) SIZE <RedoSize> BLOCKSIZE <RedoBlockSize> REUSE For redo log files. RedoFileGroup: integer; RedoFileName: path+filename, filename or nothing as seen by the OS; DiskGroupName: as seen in V$ASM_DISKGROUP; TemplateName: as seen in V$ASM_TEMPLATE; AliasName: as seen in V$ASM_ALIAS; RedoSize: integer, default 100M; RedoBlockSize: integer, overrides OS sector size.

Examples:

LOGFILE GROUP 1 ('diska:log1.log', 'diskb:log1.log') SIZE 50K, GROUP 2 ('c:\oracle\dbs\log1c.rdo') SIZE 50K BLOCKSIZE 4096 REUSE, GROUP 3 ('+DATA/orcl/onlinelog/group_3.263.685366213', '+FRA/orcl/onlinelog/group_3.259.685366215') SIZE 50M BLOCKSIZE 512, GROUP 4 ('+DATA(ONLINELOG)', '+FRA/ONLINEDF')
MAXLOGFILES <MaxLogFiles> For redo log files. MaxLogFiles: integer, max number of redo log file groups ever to be created. Default 32, max 255.
MAXLOGMEMBERS <MaxLogMembers> For redo log files. MaxLogMembers: integer, max number of copies for each redo log file group.
MAXLOGHISTORY <MaxLogHistory> For redo log files. MaxLogHistory: integer, only for ARCHIVELOG in RAC for automatic media recovery.
ARCHIVELOG|NOARCHIVELOG For redo log files. ARCHIVELOG: archive redo log group before it can be reused, for media recovery; NOARCHIVELOG is the default.
FORCE LOGGING For redo log files. Forces logging all changes except in TEMP, use with caution as it has performance effects.
SET STANDBY NOLOGGING FOR <DATA AVAILABILITY|LOAD PERFORMANCE> For redo log files. Disables logging in the standby, DATA AVAILABILITY: guarantees full data synchronization; LOAD PERFORMANCE: maintains performance of primary and synchronizes standby later, incompatible with FORCE LOGGING.
EXTENT MANAGEMENT LOCAL For all tablespaces. Specify for extent allocation and unallocated extents metadata stored in the tablespace header as a bitmap, which reduces contention, coalesces free space and does not generate rollback information.
DATAFILE FileName|(+DiskGroupName (/ DbName / DATAFILE / SYSTEM. FileNumber . IncarnationNumber) | (DATAFILE) | AliasName)) SIZE <Size> REUSE AUTOEXTEND (OFF|ON NEXT <NextSize> MAXSIZE UNLIMITED|<MaxSize>) For SYSTEM tablespace (core database functionality). FileName: path+filename, filename or nothing as seen by the OS; DiskGroupName: as seen in V$ASM_DISKGROUP; TemplateName: as seen in V$ASM_TEMPLATE; AliasName: as seen in V$ASM_ALIAS; Size: integer, default 100M; AUTOEXTEND: files grow automatically; NextSize: bytes to allocate when it grows; MaxSize: integer.

Examples:

DATAFILE 'disk1:df1.dbf' AUTOEXTEND ON, 'disk2:df2.dbf' AUTOEXTEND ON NEXT 10M MAXSIZE UNLIMITED

DATAFILE 'c:\oracle\dbs\system01.dbf' SIZE 700M REUSE AUTOEXTEND ON NEXT 10240K MAXSIZE UNLIMITED

DATAFILE '+DATA/orcl/datafile/system.256.685366089'

DATAFILE '+DATA(DATAFILE)' size 100M

DATAFILE '+DATA/SYSTEMDF'
SYSAUX DATAFILE <file_specification> For SYSAUX tablespace which manages Analytical Workspace Object Table, Enterprise Manager Repository, LogMiner, Logical Standby, OLAP API History Tables, Data Mining, Spatial, Streams, Text, Ultra Search, InterMedia ORD, Server Manageability, Statspack, Scheduler, and Workspace Manager features; file_specification is the same as for DATAFILE.

Examples:

SYSAUX DATAFILE 'c:\oracle\dbs\sysaux01.dbf' SIZE 550M REUSE AUTOEXTEND ON NEXT 10240K MAXSIZE UNLIMITED

SYSAUX DATAFILE '+DATA/orcl/datafile/sysaux.256.685366089'

DATAFILE '+DATA/SYSAUXDF'
DEFAULT TABLESPACE <TablespaceName> DATAFILE <file_specification> EXTENT MANAGEMENT LOCAL AUTOALLOCATE|(UNIFORM SIZE <DefaultSize>) For DEFAULT tablespace to not use the SYSTEM tablespace for non-SYSTEM users; file_specification is the same as for DATAFILE; AUTOALLOCATE: Oracle choose optimal next extent size for low or unmanaged environment; UNIFORM SIZE: uniform extents as specified, default 1M.

Examples:

DEFAULT TABLESPACE deftbs DATAFILE 'c:\oracle\dbs\deftbs01.dbf' SIZE 500M REUSE AUTOEXTEND ON MAXSIZE UNLIMITED

DEFAULT TABLESPACE ts2 DATAFILE 'c:\oracle\dbs\ts2_01.dbf' SIZE 50M EXTENT MANAGEMENT LOCAL AUTOALLOCATE
BIGFILE|SMALLFILE DEFAULT (TEMPORARY TABLESPACE|LOCAL TEMPORARY TABLESPACE FOR ALL|LEAF) <TablespaceName> TEMPFILE <file_specification> EXTENT MANAGEMENT LOCAL (AUTOALLOCATE|UNIFORM SIZE <TempSize>) For TEMP tablespace to not use the SYSTEM tablespace in user operations like sort and hash joins, file_specification is the same as for DATAFILE. TEMPORARY TABLESPACE: shared among instances; LOCAL TEMPORARY TABLESPACE: local to each instance, used in RAC and Flex clusters improving I/O performance, must be BIGFILE; FOR ALL: for HUB and LEAF nodes; FOR LEAF: only LEAF nodes. Best performance is obtained when there are multiple files, and the space is pre-allocated.

Examples:

DEFAULT TEMPORARY TABLESPACE tempts1 TEMPFILE 'c:\oracle\dbs\temp01.dbf' SIZE 20M REUSE AUTOEXTEND ON NEXT 640K MAXSIZE UNLIMITED

BIGFILE DEFAULT LOCAL TEMPORARY TABLESPACE FOR ALL tempts2 TEMPFILE 'c:\oracle\dbs\ts2_01.dbf' SIZE 50M EXTENT MANAGEMENT LOCAL AUTOALLOCATE
BIGFILE|SMALLFILE UNDO TABLESPACE <UndoTablespace> DATAFILE <file_specification> For UNDO tablespace when UNDO_MANAGEMENT init param is set to AUTO instead of using rollback segments and instead of UNDO_TABLESPACE init param; file_specification is the same as for DATAFILE. Undo is used for: rollback transaction when requested, recover database, provide read consistency maintaining a before data image for users while others are changing it, analyze data in earlier point in time (Flashback Query) and recover from logical corruption.

Examples:

UNDO TABLESPACE undotbs1 DATAFILE 'c:\oracle\dbs\undotbs01.dbf' SIZE 200M REUSE AUTOEXTEND ON NEXT 5120K MAXSIZE UNLIMITED
SET TIMEZONE=(DateFormat | time_zone_region) It is recommended to set the database time zone to UTC (0:00) to improve performance especially across databases, as no conversion is required. If not specified, the OS time zone is used.
<BIGFILE|SMALLFILE> USER_DATA TABLESPACE <TablespaceName> DATAFILE <file_specification> For storing user data and options such as XML DB; file_specification is the same as for DATAFILE.

Examples:

USER_DATA TABLESPACE usertbs DATAFILE 'c:\oracle\dbs\usertbs01.dbf' SIZE 200M REUSE AUTOEXTEND ON MAXSIZE UNLIMITED
ENABLE PLUGGABLE DATABASE SEED FILE_NAME_CONVERT = (NONE | ('filename_pattern', 'replacement_filename_pattern') SYSTEM DATAFILES (SIZE <Size> | (AUTOEXTEND (OFF | ON NEXT <Size> MAXSIZE <Size> | UNLIMITED))) SYSAUX DATAFILES (SIZE <Size> | (AUTOEXTEND (OFF | ON NEXT <Size> MAXSIZE <Size> | UNLIMITED))) LOCAL UNDO (ON|OFF) To create a container database, requires ENABLE_PLUGGABLE_DATABASE=TRUE init param; LOCAL UNDO ON for each PDB (pluggable database) to have their own undo; the equivalent in SQL Server is "CONTAINMENT".

Examples:

ENABLE PLUGGABLE DATABASE SEED FILE_NAME_CONVERT = ('c:\oracle\dbs\cdb\', 'c:\oracle\dbs\pdb\') SYSTEM DATAFILES SIZE 125M AUTOEXTEND ON NEXT 10M MAXSIZE UNLIMITED SYSAUX DATAFILES SIZE 100M
<new_database_name> USING MIRROR COPY <mirror_name> To create a new database using the prepared files of the mirror copy.

Examples:

CREATE PLUGGABLE DATABASE pdb1 FROM pdb USING MIRROR COPY pdbcopy

Example 1: Create a non-CDB (non-container) create database statement with all defaults:

CREATE DATABASE ORCL;

Example 2: Create a non-CDB (non-container) database with default options for SYSAUX, DEFAULT, DEFAULT TEMPORARY and USER_DATA. Note how you need to specify each system file with their size and increment. The log will be replicated in three files.

CREATE DATABASE prod 
USER SYS IDENTIFIED BY sys_password 
USER SYSTEM IDENTIFIED BY system_password 
MAXLOGFILES 5 
MAXDATAFILES 100 
DATAFILE 'C:\app\username\oradata\prod\system01.dbf' SIZE 325M REUSE 
AUTOEXTEND ON NEXT 10240K MAXSIZE UNLIMITED 
UNDO TABLESPACE "UNDOTBS" DATAFILE 'app\username\oradata\prod\undotbs01.dbf' 
SIZE 200M REUSE AUTOEXTEND ON NEXT 5120K MAXSIZE UNLIMITED 
CHARACTER SET AL32UTF8 
LOGFILE 'C:\app\username\oradata\prod\redo01.log' size 100M reuse, 
        'C:\app\username\oradata\prod\redo02.log' size 100M reuse, 
        'C:\app\username\oradata\prod\redo03.log' size 100M reuse 
EXTENT MANAGEMENT LOCAL; 

Example 3: Create a CDB (container) database with three log groups each consisting of two files. The PDB (pluggable) databases need to be created later, which are the ones that will contain the data.

CREATE DATABASE newcdb 
  USER SYS IDENTIFIED BY sys_password 
  USER SYSTEM IDENTIFIED BY system_password 
  LOGFILE GROUP 1 ('C:\redo01a.log','D:\redo01b.log') 
             SIZE 100M BLOCKSIZE 512, 
          GROUP 2 ('C:\redo02a.log','D:\redo02b.log') 
             SIZE 100M BLOCKSIZE 512, 
          GROUP 3 ('C:\redo03a.log','D:\redo03b.log') 
             SIZE 100M BLOCKSIZE 512 
  MAXLOGHISTORY 1 
  MAXLOGFILES 16 
  MAXLOGMEMBERS 3 
  MAXDATAFILES 1024 
  CHARACTER SET AL32UTF8 
  NATIONAL CHARACTER SET AL16UTF16 
  EXTENT MANAGEMENT LOCAL 
  DATAFILE 'C:\system01.dbf' 
    SIZE 700M REUSE AUTOEXTEND ON NEXT 10240K MAXSIZE UNLIMITED 
  SYSAUX DATAFILE ' C:\sysaux01.dbf' 
    SIZE 550M REUSE AUTOEXTEND ON NEXT 10240K MAXSIZE UNLIMITED 
  DEFAULT TABLESPACE deftbs 
    DATAFILE 'C:\deftbs01.dbf' 
    SIZE 500M REUSE AUTOEXTEND ON MAXSIZE UNLIMITED 
  DEFAULT TEMPORARY TABLESPACE tempts1 
    TEMPFILE 'C:\temp01.dbf' 
    SIZE 20M REUSE AUTOEXTEND ON NEXT 640K MAXSIZE UNLIMITED 
  UNDO TABLESPACE undotbs1 
    DATAFILE 'C:\undotbs01.dbf' 
    SIZE 200M REUSE AUTOEXTEND ON NEXT 5120K MAXSIZE UNLIMITED 
  ENABLE PLUGGABLE DATABASE 
    SEED 
    FILE_NAME_CONVERT = (' C:\app\oracle\oradata\newcdb\', 
                         ' C:\app\oracle\oradata\pdbseed\') 
    SYSTEM DATAFILES SIZE 125M AUTOEXTEND ON NEXT 10M MAXSIZE UNLIMITED 
    SYSAUX DATAFILES SIZE 100M 
  USER_DATA TABLESPACE usertbs 
    DATAFILE 'C:\usertbs01.dbf' 
    SIZE 200M REUSE AUTOEXTEND ON MAXSIZE UNLIMITED; 

I just used the simplest way possible with all the defaults as shown below:

CREATE DATABASE ORCL;

At this point your database has been created, but you need to manually create system objects running the scripts below:

@?/rdbms/admin/catalog.sql --create data dictionary views and dynamic performance views 
@?/rdbms/admin/catproc.sql --scripts for the procedural option (PL/SQL) 
@?/sqlplus/admin/pupbld.sql --re-create the PRODUCT_USER_PROFILE table, allowing to disable commands on a user-by-user basis 
@?/rdbms/utlrp.sql -- validate and recompile invalid objects 

After the scripts have been run, you can view the database configuration with queries below.

You can view the control file location with the query below, note it's binary so you can't read its content with a text editor:

SHOW PARAMETER CONTROL_FILES
oracle query results

You can view the MAXDATAFILES with the query below, the output is 32:

SELECT RECORDS_TOTAL FROM V$CONTROLFILE_RECORD_SECTION WHERE TYPE='DATAFILE';

You can view the MAXINSTANCES with the query below, the output is 16:

SELECT RECORDS_TOTAL FROM V$CONTROLFILE_RECORD_SECTION WHERE TYPE='REDO THREAD';

You can view the CHARACTER SET with the query below (you can also use SYS.DATABASE_PROPERTIES), the output is US7ASCII:

SELECT VALUE$ FROM SYS.PROPS$ WHERE NAME='NLS_CHARACTERSET';

You can view the default tablespace with the query below (you can also use SYS.DATABASE_PROPERTIES), the output is SYSTEM:

SELECT VALUE$ FROM SYS.PROPS$ WHERE NAME='DEFAULT_PERMANENT_TABLESPACE';

You can view the created log groups and files with the query below:

COL GROUP# FOR 99 
COL MEMBER FOR A50 
SELECT GROUP#, MEMBER FROM V$LOGFILE; 
oracle query results

You can view the MAXLOGFILES and MAXLOGHISTORY with the query below, the output is 32 and 1752 respectively:

SELECT TYPE, RECORDS_TOTAL FROM V$CONTROLFILE_RECORD_SECTION WHERE TYPE IN ('REDO LOG', 'LOG HISTORY');

You can view the created tablespaces and files with the query below:

SET LINESIZE 300 
COL FILE_NAME FOR A40 
COL TABLESPACE_NAME FOR A15 
COL BLOCKS FOR 999999 
COL MAXBYTES FOR 999999999999 
SELECT FILE_NAME, TABLESPACE_NAME, BYTES, BLOCKS, AUTOEXTENSIBLE, MAXBYTES, MAXBLOCKS, INCREMENT_BY FROM DBA_DATA_FILES;
oracle query results

And the rest of the default parameters are in one of these tables already described, you can explore them to see what you can find.

Create Database Statement in SQL Server

To create a Microsoft SQL Server 2019 database, you only need to create the database using T-SQL run from SQL Server Management Studio (SSMS) with the proper permissions. The SQL Server instance is already running when you installed SQL Server, but there may be additional steps needed to access additional features.

Below you will find a similar table with SQL CREATE DATABASE commands on the left and a description and examples on the right. Although the instructions contain multiple parameters not all of them are mandatory; they depend on what action you're performing and if you want to override the defaults or not. Also, all command options need to be run at once to create the database. For the SQL Server database engine, the "model" database is used as the example for new databases so all settings are copied unless specified along with any tables, views, stored procedures, data types that exist in the model database. The documentation can be found here: CREATE DATABASE

Command Description
CREATE DATABASE <database_name> <database_name> must start with a letter or any of the following (not recommended): _ @ #, then any letter, number or symbol including $, max 128 characters.
CONTAINMENT = NONE | PARTIAL PARTIAL: contained database, like Oracle PDB (pluggable database). Note you don't need to create a database like Oracle CDB (container database).
ON PRIMARY NAME = <logical_file_name>, FILENAME = 'os_file_name' | 'filestream_path', SIZE = <size>, MAXSIZE = <max_size> | UNLIMITED, FILEGROWTH = <growth_increment> | % PRIMARY for the primary file, there can be only one; in Oracle you create this datafile separately in one of the existing tablespaces. <logical_file_name> is the identifier to assign. 'os_file_name' is a unique file, 'filestream_path' is a directory where you will store documents, audio, video files, etc. <size> integer, default 8 MB, max 16 TB, recommended to set as large as possible based on the amount of data expected. <max_size> integer, if not specified it is UNLIMITED. <growth_increment> integer, default 64 MB, 0 to not grow, % is the amount of growth at the time the increment occurs rounded to the nearest 64 K.
FILEGROUP filegroup_name CONTAINS FILESTREAM | DEFAULT | CONTAINS MEMORY_OPTIMIZED_DATA NAME = logical_file_name, FILENAME = 'os_file_name' | 'filestream_path', SIZE = <size>, MAXSIZE = <max_size> | UNLIMITED, FILEGROWTH = growth_increment | % CONTAINS FILESTREAM to store binary large objects (BLOBs) in the file system; CONTAINS MEMORY_OPTIMIZED_DATA for in-memory (only one filegroup allowed); DEFAULT to indicate it is the default filegroup. In Oracle you manage this using data types, i.e. BFILE, ORDSYS.ORDImage, etc.
LOG ON NAME = logical_file_name, FILENAME = 'os_file_name' | 'filestream_path', SIZE = <size>, MAXSIZE = <max_size> | UNLIMITED, FILEGROWTH = growth_increment | % If not specified, one file with 25% of the sum of the data files is created; <size> integer, default 8 MB, max 2 TB; <growth_increment> integer, default 64 MB; it is recommended to have only 1 log file in the database. In Oracle this is managed in the online redo log files.
COLLATE collation_name Any of the ones specified here except for contained databases. In Oracle this is the CHARACTER SET.
WITH <option>; <option> can be any of the items below.
FILESTREAM NON_TRANSACTED_ACCESS = (OFF | READ_ONLY | FULL) DIRECTORY_NAME = 'directory_name' OFF: non-transactional access disabled; READONLY: can be read by non-transactional processes; FULL: access enabled. 'directory_name' is the parent (and is required) for FileTables which allow you to create indexes, constrains, and triggers for documents, audio, video files, etc.
DEFAULT_FULLTEXT_LANGUAGE = lcid | language_name | language_alias For linguistic analysis, see details here.
DEFAULT_LANGUAGE = lcid | language_name | language_alias For default language.
NESTED_TRIGGERS = OFF | ON To control if AFTER trigger can cascade, up to 32 levels.
TRANSFORM_NOISE_WORDS = OFF | ON To suppress an error message (ignore and continue) if noise words (stopwords) cause a Boolean operation on full-text query with CONTAINS or NEAR to return zero rows.
TWO_DIGIT_YEAR_CUTOFF = two_digit_year_cutoff To handle two-digit years.
DB_CHAINING = OFF | ON Allow cross-database ownership chain (either source or target).
TRUSTWORTHY = OFF | ON Allow an impersonation context to access resources outside the database.
PERSISTENT_LOG_BUFFER = ON DIRECTORY_NAME = <filepath to folder on DAX formatted volume> Accelerate transaction commit time (log) in high-frequency low-latency update transactions and in-memory tables.

Example 1: SQL command to create a database with all default options (using model database settings).

CREATE DATABASE mytest; 

Example 2: SQL command to specify the data and log files with their size, max size, and increment.

CREATE DATABASE Sales 
ON 
    ( NAME = SalesData, FILENAME = 'C:\Data\SalesData.mdf', SIZE = 10, MAXSIZE = 50, FILEGROWTH = 5 ) 
LOG ON 
    ( NAME = SalesLog, FILENAME = 'D:\Log\SalesLog.ldf', SIZE = 5MB, MAXSIZE = 25MB, FILEGROWTH = 5MB); 

Example 3: SQL command to create a database with two data files, two file groups and each filegroup with two data files.

CREATE DATABASE Sales 
ON PRIMARY 
    ( NAME = SalesData1, FILENAME = 'C:\Data\SalesData1.mdf', SIZE = 10, MAXSIZE = 50, FILEGROWTH = 15% ), 
    ( NAME = SalesData2, FILENAME = 'C:\Data\SalesData2.ndf', SIZE = 10, MAXSIZE = 50, FILEGROWTH = 15% ), 
FILEGROUP SalesFileGroup1 
    ( NAME = SalesData3, FILENAME = 'C:\Data\SalesData3.ndf', SIZE = 10, MAXSIZE = 50, FILEGROWTH = 5 ), 
    ( NAME = SalesData4, FILENAME = 'C:\Data\SalesData4.ndf', SIZE = 10, MAXSIZE = 50, FILEGROWTH = 5 ), 
FILEGROUP SalesFileGroup2 
    ( NAME = SalesData5, FILENAME = 'C:\Data\SalesData5.ndf', SIZE = 10, MAXSIZE = 50, FILEGROWTH = 5 ), 
    ( NAME = SalesData6, FILENAME = 'C:\Data\SalesData6.ndf', SIZE = 10, MAXSIZE = 50, FILEGROWTH = 5 ) 
LOG ON 
    ( NAME = SalesLog, FILENAME = 'D:\Log\SalesLog.ldf', SIZE = 5MB, MAXSIZE = 25MB, FILEGROWTH = 5MB ); 

Example 4: Create a SQL contained database in a specific language that can take ownership and impersonate in other databases.

CREATE DATABASE Contained 
CONTAINMENT=PARTIAL 
COLLATE French_CI_AI 
WITH TRUSTWORTHY ON, DB_CHAINING ON; 

Conclusion

As you can see, there are several differences, one of them being the max database size: in SQL Server 524 TB, in Oracle 8 PB (smallfile and 32K block size) or 8589 PB (bigfile and 32K block size).

In Oracle the database creation process allows you to truly separate Log, Data, SYSAUX/USER_DATA (additional functionality), user space, Temp and Undo. If you know the database will be big, it's better to create tablespaces as BIGFILE which is like how SQL Server manages data, in a single file; note it still can be backed up by RMAN in parallel using multiple channels, simplifies management and improves performance (checkpoint operations no longer must update too many data file headers).

In SQL Server, one of the interesting things is that you can specify the file growth in percentage (which isn't available in Oracle) and the system objects are already created for you, there's no need to create them or re-compile them.

Next Steps





get scripts

next tip button



About the author
MSSQLTips author Pablo Echeverria Pablo Echeverria worked for more than 10 years as a software programmer and analyst. In 2016, I switched jobs to a DBA position, where I have implemented new processes, created better monitoring tools and grown my data scientist skills.

View all my tips


Article Last Updated: 2022-02-09

Comments For This Article

















get free sql tips
agree to terms