Databases and Schemas
Feature compatibility | AWS SCT / AWS DMS automation level | AWS SCT action code index | Key differences |
---|---|---|---|
|
|
N/A |
N/A |
SQL Server Usage
Databases and schemas are logical containers for security and access control. Administrators can grant permissions collectively at both the databases and the schema levels. SQL Server instances provide security at three levels: individual objects, schemas (collections of objects), and databases (collections of schemas). For more information, see Data Control Language.
Note
In previous versions of SQL server, the term user was interchangeable with the term schema. For backward compatibility, each database has several built-in security schemas including guest
, dbo
, db_datareaded
, sys
, INFORMATION_SCHEMA
, and others. Most likely, you don’t need to migrate these schemas.
Each SQL Server instance can host and manage a collection of databases, which consists of SQL Server processes and the Master, Model, TempDB, and MSDB system databases.
The most common SQL Server administrator tasks at the database level are:
-
Managing physical files: add, remove, change file growth settings, and re-size files.
-
Managing filegroups: partition schemes, object distribution, and read-only protection of tables.
-
Managing default options.
-
Creating database snapshots.
Unique object identifiers within an instance use three-part identifiers: <Database name>.<Schema name>.<Objectname>.
The recommended way to view database object meta data, including schemas, is to use the ANSI standard information schema views. In most cases, these views are compatible with other ANSI-compliant Relational Database Management Systems (RDBMS).
To view a list of all databases on the server, use the sys.databases table.
Syntax
Simplified syntax for CREATE DATABASE
.
CREATE DATABASE <database name> [ ON [ PRIMARY ] <file specifications>[,<filegroup>] [ LOG ON <file specifications> [ WITH <options specification> ] ;
Simplified syntax for CREATE SCHEMA
.
CREATE SCHEMA <schema name> | AUTHORIZATION <owner name>;
Examples
The following example adds a file to a database and creates a table using the new file.
USE master;
ALTER DATABASE NewDB ADD FILEGROUP NewGroup;
ALTER DATABASE NewDB ADD FILE ( NAME = 'NewFile', FILENAME = 'D:\NewFile.ndf', SIZE = 2 MB ) TO FILEGROUP NewGroup;
USE NewDB; CREATE TABLE NewTable ( Col1 INT PRIMARY KEY ) ON NewGroup;
SELECT Name FROM sys.databases WHERE database_id > 4;
The following example creates a table within a new schema and database.
USE master CREATE DATABASE NewDB; USE NewDB; CREATE SCHEMA NewSchema; CREATE TABLE NewSchema.NewTable ( NewColumn VARCHAR(20) NOT NULL PRIMARY KEY );
This example uses default settings for the new database and schema.
For more information, see sys.databases (Transact-SQL)
PostgreSQL Usage
Amazon Aurora PostgreSQL-Compatible Edition (Aurora PostgreSQL) supports both the CREATE SCHEMA
and CREATE DATABASE
statements.
As with SQL Server, Aurora PostgreSQL does have the concept of an instance hosting multiple databases, which in turn contain multiple schemas. Objects in Aurora PostgreSQL are referenced as a three-part name: <database>.<schema>.<object>
.
A schema is essentially a namespace that contains named objects.
When database is created, it is cloned from a template.
Syntax
Syntax for CREATE DATABASE
.
CREATE DATABASE name [ [ WITH ] [ OWNER [=] user_name ] [ TEMPLATE [=] template ] [ ENCODING [=] encoding ] [ LC_COLLATE [=] lc_collate ] [ LC_CTYPE [=] lc_ctype ] [ TABLESPACE [=] tablespace_name ] [ ALLOW_CONNECTIONS [=] allowconn ] [ CONNECTION LIMIT [=] connlimit ] [ IS_TEMPLATE [=] istemplate ] ]
Syntax for CREATE SCHEMA
.
CREATE SCHEMA schema_name [ AUTHORIZATION role_specification ] [ schema_element [ ... ] ] CREATE SCHEMA AUTHORIZATION role_specification [ schema_element [ ... ] ] CREATE SCHEMA IF NOT EXISTS schema_name [ AUTHORIZATION role_specification ] CREATE SCHEMA IF NOT EXISTS AUTHORIZATION role_specification where role_specification can be: user_name | CURRENT_USER | SESSION_USER
Migration Considerations
Unlike SQL Server, Aurora PostgreSQL doesn’t support the USE
command to specify the default database or schema for missing object qualifiers. To use a different database, use a new connection, obtain the required permissions, and refer to the object using the database name.
For applications using a single database and multiple schemas, the migration path is the same and requires fewer rewrites because two-part names are already being used.
Query the postgres.pg_catalog.pg_database
table to view databases in Aurora PostgreSQL.
SELECT datname, datcollate, datistemplate, datallowconn FROM postgres.pg_catalog.pg_database; datname datcollate datistemplate datallowconn template0 en_US.UTF-8 true false rdsadmin en_US.UTF-8 false true template1 en_US.UTF-8 true true postgres en_US.UTF-8 false true
Examples
The following example creates a new database.
CREATE DATABASE NewDatabase;
The following example creates a schema for user testing.
CREATE SCHEMA AUTHORIZATION joe;
The following example creates a schema, a table and a view.
CREATE SCHEMA world_flights CREATE TABLE flights (flight_id VARCHAR(10), departure DATE, airport VARCHAR(30)) CREATE VIEW us_flights AS SELECT flight_id, departure FROM flights WHERE airport='United States';
For more information, see CREATE DATABASE