Databases and schemas for T-SQL

This topic provides reference content comparing database and schema concepts between Microsoft SQL Server 2019 and Amazon Aurora MySQL. You can gain insight into how these two database systems differ in their approach to logical containers, security, and object hierarchies.

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.

Each SQL Server instance can host and manage a collection of databases, which consist 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:

Unique object identifiers within an instance use three-part identifiers: <Database name>.<Schema name>.<Object name>.

The recommended way to view the metadata of database objects, including schemas, is to use the ANSI standard Information Schema views. In most cases, these views are compatible with other ANSI compliant 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

Add a file to a database and create 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;

Create 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
);

For more information, see sys.databases (Transact-SQL), CREATE SCHEMA (Transact-SQL), and CREATE DATABASE in the SQL Server documentation.

MySQL Usage

Amazon Aurora MySQL-Compatible Edition (Aurora MySQL) supports both the CREATE SCHEMA and CREATE DATABASE statements. However, in Aurora MySQL, these statements are synonymous.

Unlike SQL Server, Aurora MySQL doesn’t have the concept of an instance hosting multiple databases, which in turn contain multiple schemas. Objects in Aurora MySQL are referenced as a two part name: <schema>.<object>. You can use the term database in place of schema, but it is conceptually the same thing.

Aurora MySQL doesn’t have the concept of a schema owner. Permissions must be granted explicitly. However, Aurora MySQL supports a custom default collation at the schema level, whereas SQL Server supports it at the database level only. For more information, see Collations.

Syntax

Syntax for CREATE DATABASE:

CREATE {DATABASE | SCHEMA} <database name>
[DEFAULT] CHARACTER SET [=] <character set>|
[DEFAULT] COLLATE [=] <collation>

Migration Considerations

Similar to SQL Server, Aurora MySQL supports the USE command to specify the default database or schema for missing object qualifiers.

The syntax is identical to SQL Server:

USE <database name>;

After you run the USE command, the default database for the calling scope is changed to the specified database.

There is a relatively straightforward migration path for a class of common application architectures that use multiple databases but have all objects in a single schema (typically the default dbo schema) and require cross database queries. For these types of applications, create an Aurora MySQL Instance and then create multiple databases as you would in SQL Server using the CREATE DATABASE command.

Reference all objects using a two-part name instead of a three-part name by omitting the default schema identifier. For application code using the USE command instead of a three-part identifier, no rewrite is needed other than replacing the double dot with a single dot.

SELECT * FROM MyDB..MyTable -> SELECT * FROM MyDB.MyTable

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.

Applications that use multiple schemas and multiple databases will need to use multiple instances.

Use the SHOW DATABASES command to view databases or schemas in Aurora MySQL.

SHOW DATABASES;

For the preceding example, the result looks as shown following.

database

information_schema
Demo
mysql
performance_schema
sys

Aurora MySQL also supports a CREATE DATABASE syntax reminder command.

SHOW CREATE DATABASE Demo;

For the preceding example, the result looks as shown following.

Database  Create Database
Demo      CREATE DATABASE `Demo` /*!40100 DEFAULT CHARACTER SET latin1 */

Examples

The following examples create a new table in a new database.

CREATE DATABASE NewDatabase;

USE NewDatabase;

CREATE TABLE NewTable
(
    NewColumn VARCHAR(20) NOT NULL PRIMARY KEY
);

INSERT INTO NewTable VALUES('NewValue');

SELECT * FROM NewTable;

Summary

The following table summarizes the migration path for each architecture.

For more information, see CREATE DATABASE Statement in the MySQL documentation.