# Migrating ANSI SQL features
ANSI SQL

This chapter provides reference information for ANSI SQL operations required to migrate from Microsoft SQL Server 2019 and Amazon Aurora MySQL. You can use this information to understand the key differences and similarities in areas such as object name case sensitivity, constraint compatibility, table creation, Common Table Expressions (CTEs), data type compatibility, GROUP BY operations, table joins, views, window functions, and temporary tables.

**Topics**
+ [

# Case sensitivity differences for ANSI SQL
](chap-sql-server-aurora-mysql.sql.casesensitivity.md)
+ [

# Constraints for ANSI SQL
](chap-sql-server-aurora-mysql.sql.constraints.md)
+ [

# Creating tables for ANSI SQL
](chap-sql-server-aurora-mysql.sql.creatingtables.md)
+ [

# Common table expressions for ANSI SQL
](chap-sql-server-aurora-mysql.sql.cte.md)
+ [

# Data types for ANSI SQL
](chap-sql-server-aurora-mysql.sql.datatypes.md)
+ [

# GROUP BY for ANSI SQL
](chap-sql-server-aurora-mysql.sql.groupby.md)
+ [

# Table JOIN for ANSI SQL
](chap-sql-server-aurora-mysql.sql.tablejoin.md)
+ [

# Views for ANSI SQL
](chap-sql-server-aurora-mysql.sql.views.md)
+ [

# Window functions for ANSI SQL
](chap-sql-server-aurora-mysql.sql.windowfunctions.md)
+ [

# Temporary tables for ANSI SQL
](chap-sql-server-aurora-mysql.sql.temporarytables.md)

# Case sensitivity differences for ANSI SQL


Object name case sensitivity is different for SQL Server and Amazon Aurora MySQL-Compatible Edition (Aurora MySQL). SQL Server object names case sensitivity is being determined by the collection. Aurora MySQL names are case sensitive and can be adjusted based on the parameter mentioned following.

In Aurora MySQL, the case sensitivity is determined by the `lower_case_table_names` parameter value. In general, you can use one of the three possible values for this parameter. To avoid issues, we recommend that you use only the two following values for `lower_case_table_names`:
+ 0 — names stored as given and comparisons are case-sensitive. You can choose this value for all Amazon Relational Database Service (Amazon RDS) for MySQL versions.
+ 1 — names stored in lowercase and comparisons aren’t case-sensitive. You can choose this value for Amazon RDS for MySQL version 5.6, version 5.7, and version 8.0.19 and higher 8.0 versions.

In Aurora MySQL version 2.10 and higher 2.x versions, make sure to reboot all reader instances after changing the `lower_case_table_names` setting and rebooting the writer instance. For details, see [Rebooting an Aurora MySQL cluster (version 2.10 and higher)](https://docs.aws.amazon.com/AmazonRDS/latest/AuroraUserGuide/USER_RebootCluster.html#aurora-mysql-survivable-replicas).

In Aurora MySQL version 3, the value of the `lower_case_table_names` parameter is set permanently at the time when you create the cluster. If you use a nondefault value for this option, set up your Aurora MySQL version 3 custom parameter group before upgrading, and specify the parameter group during the snapshot restore operation that creates the version 3 cluster.

With an Aurora global database based on Aurora MySQL, you can’t perform an in-place upgrade from Aurora MySQL version 2 to version 3 if the `lower_case_table_names` parameter is turned on. For more information on the methods that you can use, see [Major version upgrades](https://docs.aws.amazon.com/AmazonRDS/latest/AuroraUserGuide/aurora-global-database-upgrade.html#aurora-global-database-upgrade.major).

We recommend that you don’t changE the `lower_case_table_names` parameter for existing database instances. Doing so can cause inconsistencies with point-in-time recovery backups and read replica DB instances.

Read replicas should always use the same `lower_case_table_names` parameter value as the source DB instance.

By default, object names are being stored in lowercase for MySQL. In most cases, you’ll want to use AWS Database Migration Service transformations to change schema, table, and column names to lowercase.

## Examples


For example, to create a table named EMPLOYEES in uppercase in MySQL, you should use the following:

```
CREATE TABLE EMPLOYEES (
    EMP_ID NUMERIC PRIMARY KEY,
    EMP_FULL_NAME VARCHAR(60) NOT NULL,
    AVG_SALARY NUMERIC NOT NULL);
```

The following command creates a table named employees in lowercase.

```
CREATE TABLE employees (
    EMP_ID NUMERIC PRIMARY KEY,
    EMP_FULL_NAME VARCHAR(60) NOT NULL,
    AVG_SALARY NUMERIC NOT NULL);
```

MySQL will look for objects names in with the exact case sensitivity as written in the query.

You can turn off table name case sensitivity in MySQL by setting the parameter `lower_case_table_names` to 1. Column, index, stored routine, event names, and column aliases aren’t case sensitive on either platform.

For more information, see [Identifier Case Sensitivity](https://dev.mysql.com/doc/refman/5.7/en/identifier-case-sensitivity.html) in the *MySQL documentation*.

# Constraints for ANSI SQL


This topic provides reference information about constraint compatibility between Microsoft SQL Server 2019 and Amazon Aurora MySQL. You can use this guide to understand the similarities and differences in how these two database systems handle various types of constraints, including check constraints, unique constraints, primary key constraints, and foreign key constraints.


| Feature compatibility |  AWS SCT / AWS DMS automation level |  AWS SCT action code index | Key differences | 
| --- | --- | --- | --- | 
|   ![\[Four star feature compatibility\]](http://docs.aws.amazon.com/dms/latest/sql-server-to-aurora-mysql-migration-playbook/images/pb-compatibility-4.png)   |   ![\[Four star automation level\]](http://docs.aws.amazon.com/dms/latest/sql-server-to-aurora-mysql-migration-playbook/images/pb-automation-4.png)   |   [Constraints](chap-sql-server-aurora-mysql.tools.actioncode.md#chap-sql-server-aurora-mysql.tools.actioncode.constraints)   |  Unsupported `CHECK`. Indexing requirements for `UNIQUE`.  | 

## SQL Server Usage


Column and table constraints are defined by the SQL standard and enforce relational data consistency. There are four types of SQL constraints: check constraints, unique constraints, primary key constraints, and foreign key constraints.

### Check Constraints


```
CHECK (<Logical Expression>)
```

Check constraints enforce domain integrity by limiting the data values stored in table columns. They are logical Boolean expressions that evaluate to one of three values: `TRUE`, `FALSE`, and `UNKNOWN`.

**Note**  
Check constraint expressions behave differently than predicates in other query clauses. For example, in a `WHERE` clause, a logical expression that evaluates to `UNKNOWN` is functionally equivalent to `FALSE` and the row is filtered out. For check constraints, an expression that evaluates to `UNKNOWN` is functionally equivalent to `TRUE` because the value is permitted by the constraint.

You can assign multiple check constraints to a single column. A single check constraint may apply to multiple columns. In this case, it is known as a table-level check constraint.

In ANSI SQL, check constraints can’t access other rows as part of the expression. In SQL Server, you can use user-defined functions in constraints to access other rows, tables, or even databases.

### Unique Constraints


```
UNIQUE [CLUSTERED | NONCLUSTERED] (<Column List>)
```

Unique constraints should be used for all candidate keys. A candidate key is an attribute or a set of attributes such as columns that uniquely identify each row in the relation or table data.

Unique constraints guarantee that no rows with duplicate column values exist in a table.

A unique constraint can be simple or composite. Simple constraints are composed of a single column. Composite constraints are composed of multiple columns. A column may be a part of more than one constraint.

Although the ANSI SQL standard allows multiple rows having NULL values for unique constraints, in SQL Server, you can use a NULL value for only one row. Use a `NOT NULL` constraint in addition to a unique constraint to disallow all NULL values.

To improve efficiency, SQL Server creates a unique index to support unique constraints. Otherwise, every `INSERT` and `UPDATE` would require a full table scan to verify there are no duplicates. The default index type for unique constraints is non-clustered.

### Primary Key Constraints


```
PRIMARY KEY [CLUSTERED | NONCLUSTERED] (<Column List>)
```

A primary key is a candidate key serving as the unique identifier of a table row. Primary keys may consist of one or more columns. All columns that comprise a primary key must also have a NOT NULL constraint. Tables can have one primary key.

The default index type for primary keys is a clustered index.

### Foreign Key Constraints


```
FOREIGN KEY (<Referencing Column List>)
REFERENCES <Referenced Table>(<Referenced Column List>)
```

Foreign key constraints enforce domain referential integrity. Similar to check constraints, foreign keys limit the values stored in a column or set of columns.

Foreign keys reference columns in other tables, which must be either primary keys or have unique constraints. The set of values allowed for the referencing table is the set of values existing the referenced table.

Although the columns referenced in the parent table are indexed (since they must have either a primary key or unique constraint), no indexes are automatically created for the referencing columns in the child table. A best practice is to create appropriate indexes to support joins and constraint enforcement.

Foreign key constraints impose DML limitations for the referencing child table and for the parent table. The constraint’s purpose is to guarantee that no orphan rows with no corresponding matching values in the parent table exist in the referencing table. The constraint limits `INSERT` and `UPDATE` to the child table and `UPDATE` and `DELETE` to the parent table. For example, you can’t delete an order having associated order items.

Foreign keys support cascading referential integrity (CRI). CRI can be used to enforce constraints and define action paths for DML statements that violate the constraints. There are four CRI options:
+  **NO ACTION** — When the constraint is violated due to a DML operation, an error is raised and the operation is rolled back.
+  **CASCADE** — Values in a child table are updated with values from the parent table when they are updated or deleted along with the parent.
+  **SET NULL** — All columns that are part of the foreign key are set to NULL when the parent is deleted or updated.
+  **SET DEFAULT** — All columns that are part of the foreign key are set to their DEFAULT value when the parent is deleted or updated.

These actions can be customized independently of others in the same constraint. For example, a cascading constraint may have `CASCADE` for `UPDATE`, but `NO ACTION` for `UPDATE`.

### Examples


The following example creates a composite non-clustered primary key.

```
CREATE TABLE MyTable
(
    Col1 INT NOT NULL,
    Col2 INT NOT NULL,
    Col3 VARCHAR(20) NULL,
    CONSTRAINT PK_MyTable
    PRIMARY KEY NONCLUSTERED (Col1, Col2)
);
```

The following example creates a table-level check constraint.

```
CREATE TABLE MyTable
(
    Col1 INT NOT NULL,
    Col2 INT NOT NULL,
    Col3 VARCHAR(20) NULL,
    CONSTRAINT PK_MyTable
    PRIMARY KEY NONCLUSTERED (Col1, Col2),
    CONSTRAINT CK_MyTableCol1Col2
    CHECK (Col2 >= Col1)
);
```

The following example creates a simple non-null unique constraint.

```
CREATE TABLE MyTable
(
    Col1 INT NOT NULL,
    Col2 INT NOT NULL,
    Col3 VARCHAR(20) NULL,
    CONSTRAINT PK_MyTable
        PRIMARY KEY NONCLUSTERED (Col1, Col2),
    CONSTRAINT UQ_Col2Col3
        UNIQUE (Col2, Col3)
);
```

The following example creates a foreign key with multiple cascade actions.

```
CREATE TABLE MyParentTable
(
    Col1 INT NOT NULL,
    Col2 INT NOT NULL,
    Col3 VARCHAR(20) NULL,
    CONSTRAINT PK_MyTable
    PRIMARY KEY NONCLUSTERED (Col1, Col2)
);
```

```
CREATE TABLE MyChildTable
(
    Col1 INT NOT NULL PRIMARY KEY,
    Col2 INT NOT NULL,
    Col3 INT NOT NULL,
    CONSTRAINT FK_MyChildTable_MyParentTable
        FOREIGN KEY (Col2, Col3)
        REFERENCES MyParentTable (Col1, Col2)
        ON DELETE NO ACTION
        ON UPDATE CASCADE
);
```

For more information, see [Unique Constraints and Check Constraints](https://docs.microsoft.com/en-us/sql/relational-databases/tables/unique-constraints-and-check-constraints?view=sql-server-ver15) and [Primary and Foreign Key Constraints](https://docs.microsoft.com/en-us/sql/relational-databases/tables/primary-and-foreign-key-constraints?view=sql-server-ver15) in the *SQL Server documentation*.

## MySQL Usage


Similar to SQL Server, Aurora MySQL supports all ANSI constraint types, except check.

**Note**  
You can work around some of the functionality of `CHECK (<Column>) IN (<Value List>)` using the `SET` and `ENUM` data types. For more information, see [Data Types](chap-sql-server-aurora-mysql.sql.datatypes.md).

Unlike SQL Server, constraint names, or symbols in Aurora MySQL terminology, are optional. Identifiers are created automatically and are similar to SQL Server column constraints that are defined without an explicit name.

### Unique Constraints


Unlike SQL Server, where unique constraints are objects supported by unique indexes, Aurora MySQL only provides unique indexes. A unique index is the equivalent to a SQL Server unique constraint.

As with SQL Server, unique indexes enforce distinct values for index columns. If a new row is added or an existing row is updated with a value that matches an existing row, an error is raised and the operation is rolled back.

Unlike SQL Server, Aurora MySQL permits multiple rows with NULL values for unique indexes.

**Note**  
If a unique index consists of only one `INT` type column, you can use the `_rowid` alias to reference the index in `SELECT` statements.

### Primary Key Constraints


Similar to SQL Server, a primary key constraint in Aurora MySQL is a unique index where all columns are NOT NULL. Each table can have only one primary key. The name of the constraint is always `PRIMARY`.

Primary keys in Aurora MySQL are always clustered. They can’t be configured as `NON CLUSTERED` like SQL Server. For more information, see [Indexes](chap-sql-server-aurora-mysql.indexes.md).

Applications can reference a primary key using the `PRIMARY` alias. If a table has no primary key, which isn’t recommended, Aurora MySQL uses the first NOT NULL and unique index.

**Note**  
Keep the primary key short to minimize storage overhead for secondary indexes. In Aurora MySQL, the primary key is clustered. Therefore, every secondary or nonclustered index maintains a copy of the clustering key as the row pointer. It is also recommended to create tables and declare the primary key first, followed by the unique indexes. Then create the non-unique indexes.

If a primary key consists of a single `INTEGER` column, it can be referenced using the `_rowid` alias in `SELECT` commands.

### Foreign Key Constraints


**Note**  
MySQL doesn’t support foreign key constraints for partitioned tables. For more information, see [Storage](chap-sql-server-aurora-mysql.storage.md).

 Aurora MySQL supports foreign key constraints for limiting values in a column, or a set of columns, of a child table based on their existence in a parent table.

Unlike SQL Server and contrary to the ANSI standard, Aurora MySQL allows foreign keys to reference nonunique columns in the parent table. The only requirement is that the columns are indexed as the leading columns of an index, but not necessarily a unique index.

 Aurora MySQL supports cascading referential integrity actions using the `ON UPDATE` and `ON DELETE` clauses. The available referential actions are `RESTRICT`, `CASCADE`, `SET NULL`, and `NO ACTION`. The default action is `RESTRICT`. `RESTRICT` and `NO ACTION` are synonymous.

**Note**  
SET DEFAULT is supported by some other MySQL Server engines. Aurora MySQL uses the InnoDB engine exclusively, which doesn’t support `SET DEFAULT`.

**Note**  
Some database engines support the ANSI standard for deferred checks. `NO ACTION` is a deferred check as opposed to `RESTRICT`, which is immediate. In MySQL, foreign key constraints are always validated immediately. Therefore, `NO ACTION` is the same as the `RESTRICT` action.

 Aurora MySQL handles foreign keys differently than most other engines in the following ways:
+ If there are multiple rows in the parent table that have the same values for the referenced foreign key, Aurora MySQL foreign key checks behave as if the other parent rows with the same key value don’t exist. For example, if a `RESTRICT` action is defined and a child row has several parent rows, Aurora MySQL doesn’t permit deleting them.
+ If `ON UPDATE CASCADE` or `ON UPDATE SET NULL` causes a recursion and updates the same table that has been updated as part of the same cascade operation, Aurora MySQL treats it as if it was a `RESTRICT` action. This effectively turns off self-referencing `ON UPDATE CASCADE` or `ON UPDATE SET NULL` operations to prevent potential infinite loops resulting from cascaded updates. A self-referencing `ON DELETE SET NULL` or `ON DELETE CASCADE` are allowed because there is no risk of an infinite loop.
+ Cascading operations are limited to 15 levels deep.

### Check Constraints


Standard ANSI check clauses are parsed correctly and don’t raise syntax errors. However, they are ignored and aren’t stored as part of the Aurora MySQL table definition.

 **Syntax** 

```
CREATE [TEMPORARY] TABLE [IF NOT EXISTS] <Table Name>
(
    <Column Definition>
    [CONSTRAINT [<Symbol>]]
        PRIMARY KEY (<Column List>)
    | [CONSTRAINT [<Symbol>]]
        UNIQUE [INDEX|KEY] [<Index Name>] [<Index Type>] (<Column List>)
    | [CONSTRAINT [<Symbol>]]
        FOREIGN KEY [<Index Name>] (<Column List>)
            REFERENCES <Table Name> (<Column List>)
                [ON DELETE RESTRICT | CASCADE | SET NULL | NO ACTION | SET DEFAULT]
                [ON UPDATE RESTRICT | CASCADE | SET NULL | NO ACTION | SET DEFAULT]
);
```

### Migration Considerations

+  Aurora MySQL doesn’t support check constraints. The engine parses the syntax for check constraints, but they are ignored.
+ Consider using triggers or stored routines to validate data values for complex expressions.
+ When using check constraints for limiting to a value list such as `CHECK (Col1 IN (1,2,3))`, consider using the `ENUM` or `SET` data types.
+ In Aurora MySQL, the constraint name (symbol) is optional, even for table constraints defined with the `CONSTRAINT` keyword. In SQL Server, it is mandatory.
+  Aurora MySQL requires that both the child table and the parent table in foreign key relationship are indexed. If the appropriate index doesn’t exist, Aurora MySQL automatically creates one.

### Examples


The following example creates a composite primary key.

```
CREATE TABLE MyTable
(
    Col1 INT NOT NULL,
    Col2 INT NOT NULL,
    Col3 VARCHAR(20) NULL,
    CONSTRAINT PRIMARY KEY (Col1, Col2)
);
```

The following example creates a simple non-null unique constraint.

```
CREATE TABLE MyTable
(
    Col1 INT NOT NULL,
    Col2 INT NOT NULL,
    Col3 VARCHAR(20) NULL,
    CONSTRAINT PRIMARY KEY (Col1, Col2),
    CONSTRAINT UNIQUE (Col2, Col3)
);
```

The following example creates a named foreign key with multiple cascade actions.

```
CREATE TABLE MyParentTable
(
    Col1 INT NOT NULL,
    Col2 INT NOT NULL,
    Col3 VARCHAR(20) NULL,
    CONSTRAINT PRIMARY KEY (Col1, Col2)
);
```

```
CREATE TABLE MyChildTable
(
    Col1 INT NOT NULL PRIMARY KEY,
    Col2 INT NOT NULL,
    Col3 INT NOT NULL,
    FOREIGN KEY (Col2, Col3)
    REFERENCES MyParentTable (Col1, Col2)
    ON DELETE NO ACTION
    ON UPDATE CASCADE
);
```

## Summary


The following table identifies similarities, differences, and key migration considerations.


| Feature | SQL Server |  Aurora MySQL  | Comments | 
| --- | --- | --- | --- | 
|  Check constraints  |   `CHECK`   |  Not supported  |   Aurora MySQL parses `CHECK` syntax, but ignores it.  | 
|  Unique constraints  |   `UNIQUE`   |   `UNIQUE`   |  | 
|  Primary key constraints  |   `PRIMARY KEY`   |   `PRIMARY KEY`   |  | 
|  Foreign key constraints  |   `FOREIGN KEY`   |   `FOREIGN KEY`   |  | 
|  Cascaded referential actions  |   `NO ACTION`, `CASCADE`, `SET NULL`, `SET DEFAULT`   |   `RESTRICT`, `CASCADE`, `SET NULL`, `NO ACTION`   |   `NO ACTION` and `RESTRICT` are synonymous.  | 
|  Indexing of referencing columns  |  Not required  |  Required  |  If not specified, an index is created silently to support the constraint.  | 
|  Indexing of referenced columns  |   `PRIMARY KEY` or `UNIQUE`   |  Required  |   Aurora MySQL doesn’t enforce uniqueness of referenced columns.  | 
|  Cascade recursion  |  Not allowed, discovered at `CREATE` time  |  Not allowed, discovered at run time.  |  | 

For more information, see [CREATE TABLE Statement](https://dev.mysql.com/doc/refman/5.7/en/create-table.html), [How MySQL Deals with Constraints](https://dev.mysql.com/doc/refman/5.7/en/constraints.html), and [FOREIGN KEY Constraints](https://dev.mysql.com/doc/refman/5.7/en/create-table-foreign-keys.html) in the *MySQL documentation*.

# Creating tables for ANSI SQL


This topic provides reference content comparing the creation of tables in Microsoft SQL Server 2019 and Amazon Aurora MySQL. You can understand the similarities and differences in table creation syntax, features, and capabilities between these two database systems.


| Feature compatibility |  AWS SCT / AWS DMS automation level |  AWS SCT action code index | Key differences | 
| --- | --- | --- | --- | 
|   ![\[Four star feature compatibility\]](http://docs.aws.amazon.com/dms/latest/sql-server-to-aurora-mysql-migration-playbook/images/pb-compatibility-4.png)   |   ![\[Four star automation level\]](http://docs.aws.amazon.com/dms/latest/sql-server-to-aurora-mysql-migration-playbook/images/pb-automation-4.png)   |   [Creating Tables](chap-sql-server-aurora-mysql.tools.actioncode.md#chap-sql-server-aurora-mysql.tools.actioncode.tables)   |   `IDENTITY` and `AUTO_INCREMENT`. Primary key is always clustered. `CREATE TEMPORARY TABLE` syntax. Unsupported `@table` variables.  | 

## SQL Server Usage


Tables in SQL Server are created using the `CREATE TABLE` statement and conform to the ANSI and ISO entry level standard. The basic features of `CREATE TABLE` are similar for most relational database management engines and are well defined in the ANSI and ISO standards.

In its most basic form, the `CREATE TABLE` statement in SQL Server is used to define:
+ Table names, the containing security schema, and database.
+ Column names.
+ Column data types.
+ Column and table constraints.
+ Column default values.
+ Primary, unique, and foreign keys.

### T-SQL Extensions


SQL Server extends the basic syntax and provides many additional options for the `CREATE TABLE` or `ALTER TABLE` statements. The most often used options are:
+ Supporting index types for primary keys and unique constraints, clustered or non-clustered, and index properties such as `FILLFACTOR`.
+ Physical table data storage containers using the `ON <File Group>` clause.
+ Defining `IDENTITY` auto-enumerator columns.
+ Encryption.
+ Compression.
+ Indexes.

For more information, see [Data Types](chap-sql-server-aurora-mysql.sql.datatypes.md), [Column Encryption](chap-sql-server-aurora-mysql.security.columnencryption.md), and [Databases and Schemas](chap-sql-server-aurora-mysql.tsql.databasesschemas.md).

### Table Scope


SQL Server provides five scopes for tables:
+ Standard tables are created on disk, globally visible, and persist through connection resets and server restarts.
+ Temporary tables are designated with the `#` prefix. Temporary tables are persisted in TempDB and are visible to the run scope where they were created and any sub-scope. Temporary tables are cleaned up by the server when the run scope terminates and when the server restarts.
+ Global temporary tables are designated by the `##` prefix. They are similar in scope to temporary tables, but are also visible to concurrent scopes.
+ Table variables are defined with the `DECLARE` statement, not with `CREATE TABLE`. They are visible only to the run scope where they were created.
+ Memory-Optimized tables are special types of tables used by the In-Memory Online Transaction Processing (OLTP) engine. They use a nonstandard `CREATE TABLE` syntax.

### Creating a Table Based on an Existing Table or Query


In SQL Server, you can create new tables based on `SELECT` queries as an alternate to the `CREATE TABLE` statement. A `SELECT` statement that returns a valid set with unique column names can be used to create a new table and populate data.

 `SELECT INTO` is a combination of DML and DDL. The simplified syntax for `SELECT INTO` is:

```
SELECT <Expression List>
INTO <Table Name>
[FROM <Table Source>]
[WHERE <Filter>]
[GROUP BY <Grouping Expressions>...];
```

When creating a new table using `SELECT INTO`, the only attributes created for the new table are column names, column order, and the data types of the expressions. Even a straight forward statement such as `SELECT * INTO <New Table> FROM <Source Table>` doesn’t copy constraints, keys, indexes, identity property, default values, or any other related objects.

### TIMESTAMP Syntax for ROWVERSION Deprecated Syntax


The `TIMESTAMP` syntax synonym for `ROWVERSION` has been deprecated as of SQL Server 2008 R2. For more information, see [Deprecated Database Engine Features in SQL Server 2008 R2](https://docs.microsoft.com/en-us/previous-versions/sql/sql-server-2008-r2/ms143729(v=sql.105)) in the *SQL Server documentation*.

Previously, you could use either the `TIMESTAMP` or the `ROWVERSION` keywords to denote a special data type that exposes an auto-enumerator. The auto-enumerator generates unique eight-byte binary numbers typically used to version-stamp table rows. Clients read the row, process it, and check the `ROWVERSION` value against the current row in the table before modifying it. If they are different, the row has been modified since the client read it. The client can then apply different processing logic.

Note that when you migrate to Amazon Aurora MySQL-Compatible Edition (Aurora MySQL) using AWS Schema Conversion Tool (AWS SCT), neither `ROWVERSION` nor `TIMESTAMP` are supported. AWS SCT raises the following error: `706 — Unsupported data type …​ of variable/column was replaced. Check the conversion result`.

To maintain this functionality, add customer logic, potentially in the form of a trigger.

### Syntax


Simplified syntax for `CREATE TABLE`.

```
CREATE TABLE [<Database Name>.<Schema Name>].<Table Name> (<Column Definitions>)
[ON{<Partition Scheme Name> (<Partition Column Name>)];
```

```
<Column Definition>:
<Column Name> <Data Type>
[CONSTRAINT <Column Constraint>
[DEFAULT <Default Value>]]
[IDENTITY [(<Seed Value>, <Increment Value>)]
[NULL | NOT NULL]
[ENCRYPTED WITH (<Encryption Specifications>)
[<Column Constraints>]
[<Column Index Specifications>]
```

```
<Column Constraint>:
[CONSTRAINT <Constraint Name>]
{{PRIMARY KEY | UNIQUE} [CLUSTERED | NONCLUSTERED]
[WITH FILLFACTOR = <Fill Factor>]
| [FOREIGN KEY]
REFERENCES <Referenced Table> (<Referenced Columns>)]
```

```
<Column Index Specifications>:
INDEX <Index Name> [CLUSTERED | NONCLUSTERED]
[WITH(<Index Options>]
```

### Examples


The following example creates a basic table.

```
CREATE TABLE MyTable
(
    Col1 INT NOT NULL PRIMARY KEY,
    Col2 VARCHAR(20) NOT NULL
);
```

The following example creates a table with column constraints and an identity.

```
CREATE TABLE MyTable
(
    Col1 INT NOT NULL PRIMARY KEY IDENTITY (1,1),
    Col2 VARCHAR(20) NOT NULL CHECK (Col2 <> ''),
    Col3 VARCHAR(100) NULL
    REFERENCES MyOtherTable (Col3)
);
```

The following example creates a table with an additional index.

```
CREATE TABLE MyTable
(
    Col1 INT NOT NULL PRIMARY KEY,
    Col2 VARCHAR(20) NOT NULL
    INDEX IDX_Col2 NONCLUSTERED
);
```

For more information, see [CREATE TABLE (Transact-SQL)](https://docs.microsoft.com/en-us/sql/t-sql/statements/create-table-transact-sql?view=sql-server-ver15) in the *SQL Server documentation*.

## MySQL Usage


Like SQL Server, Aurora MySQL provides ANSI/ISO syntax entry level conformity for `CREATE TABLE` and custom extensions to support Aurora MySQL specific functionality.

**Note**  
Unlike SQL Server that uses a single set of physical files for each database, Aurora MySQL tables are created as separate files for each table. Therefore, the SQL Server concept of File Groups doesn’t apply to Aurora MySQL. For more information, see [Databases and Schemas](chap-sql-server-aurora-mysql.tsql.databasesschemas.md).

In its most basic form, and very similar to SQL Server, you can use the `CREATE TABLE` statement in Aurora MySQL to define:
+ Table name, containing security schema, and database.
+ Column names.
+ Column data types.
+ Column and table constraints.
+ Column default values.
+ Primary, unique, and foreign keys.

### Aurora MySQL Extensions


 Aurora MySQL extends the basic syntax and allows many additional options to be defined as part of the `CREATE TABLE` or `ALTER TABLE` statements. The most often used options are:
+ Defining `AUTO_INCREMENT` properties for auto-enumerator columns.
+ Encryption.
+ Compression.
+ Indexes.

### Table Scope


 Aurora MySQL provides two table scopes:
+ Standard tables are created on disk, visible globally, and persist through connection resets and server restarts.
+ Temporary tables are created using the `CREATE TEMPORARY TABLE` statement. A temporary table is visible only to the session that creates it and is dropped automatically when the session is closed.

### Creating a Table Based on an Existing Table or Query


 Aurora MySQL provides two ways to create standard or temporary tables based on existing tables and queries.

 `CREATE TABLE <New Table> LIKE <Source Table>` creates an empty table based on the definition of another table including any column attributes and indexes defined in the original table.

 `CREATE TABLE …​ AS <Query Expression>` is similar to `SELECT INTO` in SQL Server. You can use this statement to create a new table and populate data in a single step. Unlike SQL Server, you can combine standard column definitions and additional columns derived from the query in Aurora MySQL. This statement doesn’t copy supporting objects or attributes from the source table, similar to SQL Server. For example:

```
CREATE TABLE SourceTable
(
    Col1 INT
);
```

```
INSERT INTO SourceTable
VALUES (1)
```

```
CREATE TABLE NewTable
(
    Col1 INT
)
AS
SELECT Col1 AS Col2
FROM SourceTable;
```

```
INSERT INTO NewTable (Col1, Col2)
VALUES (2,3);
```

```
SELECT * FROM NewTable
```

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

```
Col1  Col2
NULL  1
2     3
```

### Converting TIMESTAMP and ROWVERSION columns


**Note**  
 Aurora MySQL has a `TIMESTAMP` data type, which is a temporal type not to be confused with `TIMESTAMP` in SQL Server. For more information, see [Data Types](chap-sql-server-aurora-mysql.sql.datatypes.md).

SQL server provides an automatic mechanism for stamping row versions for application concurrency control.

Consider the following example.

```
CREATE TABLE WorkItems
(
    WorkItemID INT IDENTITY(1,1) PRIMARY KEY,
    WorkItemDescription XML NOT NULL,
    Status VARCHAR(10) NOT NULL DEFAULT ('Pending'),
    -- other columns...
    VersionNumber ROWVERSION
);
```

The `VersionNumber` column automatically updates when a row is modified. The actual value is meaningless, just the fact that it changed is what indicates a row modification. The client can now read a work item row, process it, and ensure no other clients updated the row before updating the status.

```
SELECT @WorkItemDescription = WorkItemDescription,
    @Status = Status,
    @VersionNumber = VersionNumber
FROM WorkItems
WHERE WorkItemID = @WorkItemID;

EXECUTE ProcessWorkItem @WorkItemID, @WorkItemDescription, @Status OUTPUT;

IF (
        SELECT VersionNumber
        FROM WorkItems
        WHERE WorkItemID = @WorkItemID
    ) = @VersionNumber;
    EXECUTE UpdateWorkItems @WorkItemID, 'Completed'; -- Success
ELSE
    EXECUTE ConcurrencyExceptionWorkItem; -- Row updated while processing
```

In Aurora MySQL, you can add a trigger to maintain the updated stamp for each row.

```
CREATE TABLE WorkItems
(
    WorkItemID INT AUTO_INCREMENT PRIMARY KEY,
    WorkItemDescription JSON NOT NULL,
    Status VARCHAR(10) NOT NULL DEFAULT 'Pending',
    -- other columns...
    VersionNumber INTEGER NULL
);

CREATE TRIGGER MaintainWorkItemVersionNumber
AFTER UPDATE
ON WorkItems FOR EACH ROW
SET NEW.VersionNumber = OLD.VersionNumber + 1;
```

For more information, see [Triggers](chap-sql-server-aurora-mysql.tsql.triggers.md).

### Syntax


```
CREATE [TEMPORARY] TABLE [IF NOT EXISTS] <Table Name>
(<Create Definition> ,...)[<Table Options>];
```

```
<Create Definition>:
<Column Name> <Column Definition> | [CONSTRAINT [symbol]]
[PRIMARY KEY | UNIQUE | FOREIGN KEY <Foreign Key Definition> | CHECK (<Check Predicate>)]
(INDEX <Index Column Name>,...)
```

```
<Column Definition>:
<Data Type> [NOT NULL | NULL]
[DEFAULT <Default Value>]
[AUTO_INCREMENT]
[UNIQUE [KEY]] [[PRIMARY] KEY]
[COMMENT <comment>]
```

### Migration Considerations


Migrating `CREATE TABLE` statements should be mostly compatible with the SQL Server syntax when using only ANSI standard syntax.

 `IDENTITY` columns should be rewritten to use the Aurora MySQL syntax of `AUTO_INCREMENT`. Note that similar to SQL Server, there can be only one such column in a table, but in Aurora MySQL it also must be indexed.

Temporary table syntax should be modified to use the `CREATE TEMPORARY TABLE` statement instead of the `CREATE #Table` syntax of SQL Server. Global temporary tables and table variables aren’t supported by Aurora MySQL. For sharing data across connections, use standard tables.

 `SELECT INTO` queries should be rewritten to use `CREATE TABLE …​ AS` syntax. When copying tables, remember that the `CREATE TABLE …​ LIKE` syntax also retains all supporting objects such as constraints and indexes.

 Aurora MySQL doesn’t require specifying constraint names when using the CONSTRAINT keyword. Unique constraint names are created automatically. If specifying a name, the name must be unique for the database.

Unlike SQL Server `IDENTITY` columns, which require `EXPLICIT SET IDENTITY_INSERT ON` to bypass the automatic generation, Aurora MySQL allows inserting explicit values into the column. To generate an automatic value, insert a NULL or a 0 value. To reseed the automatic value, use `ALTER TABLE` as opposed to `DBCC CHECKIDENT` in SQL Server.

In Aurora MySQL, you can add a comment to a column for documentation purposes, similar to SQL Server extended properties feature.

**Note**  
Contrary to the SQL standard, foreign keys in Aurora MySQL can point to non-unique parent column values. In this case, the foreign key prohibits deletion of any of the parent rows. For more information, see [Constraints](chap-sql-server-aurora-mysql.sql.constraints.md) and [FOREIGN KEY Constraint Differences](https://dev.mysql.com/doc/refman/5.7/en/ansi-diff-foreign-keys.html) in the *MySQL documentation*.

### Examples


The following example creates a basic table.

```
CREATE TABLE MyTable
(
    Col1 INT NOT NULL PRIMARY KEY,
    Col2 VARCHAR(20) NOT NULL
);
```

The following example creates a table with column constraints and an auto increment column.

```
CREATE TABLE MyTable
(
    Col1 INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    Col2 VARCHAR(20) NOT NULL
    CHECK (Col2 <> ''),
    Col3 VARCHAR(100) NULL
    REFERENCES MyOtherTable (Col3)
);
```

The following example creates a table with an additional index.

```
CREATE TABLE MyTable
(
    Col1 INT NOT NULL PRIMARY KEY,
    Col2 VARCHAR(20) NOT NULL,
    INDEX IDX_Col2 (Col2)
);
```

## Summary


The following table identifies similarities, differences, and key migration considerations.


| Feature | SQL Server |  Aurora MySQL  | Comments | 
| --- | --- | --- | --- | 
|  ANSI compliance  |  Entry level  |  Entry level  |  Basic syntax is compatible.  | 
|  Auto generated enumerator  |   `IDENTITY`   |   `AUTO_INCREMENT`   |  Only one allowed for each table. In Aurora MySQL, insert NULL or 0 to generate a new value.  | 
|  Reseed auto generated value  |   `DBCC CHECKIDENT`   |   `ALTER TABLE`   |  For more information, see [ALTER TABLE Statement](https://dev.mysql.com/doc/refman/5.7/en/alter-table.html).  | 
|  Index types  |   `CLUSTERED`, `NONCLUSTERED`   |  Implicit — primary keys use clustered indexes.  |  For more information, see [Indexes](chap-sql-server-aurora-mysql.indexes.md).  | 
|  Physical storage location  |   `ON <File Group>`   |  Not supported  |  Physical storage is managed by AWS.  | 
|  Temporary tables  |  \$1TempTable  |   `CREATE TEMPORARY TABLE`   |  | 
|  Global temporary tables  |   `##GlobalTempTable`   |  Not supported  |  Use standard tables to share data between connections.  | 
|  Table variables  |   `DECLARE @Table`   |  Not supported  |  | 
|  Create table as query  |   `SELECT…​ INTO`   |   `CREATE TABLE…​ AS`   |  | 
|  Copy table structure  |  Not supported  |   `CREATE TABLE…​ LIKE`   |  | 
|  Memory-optimized tables  |  Supported  |  Not supported  |  For workloads that require memory resident tables, consider using Amazon ElastiCache (Redis OSS). For more information, see [Amazon ElastiCache for Redis](https://aws.amazon.com/elasticache/redis/).  | 

For more information, see [CREATE TABLE Statement](https://dev.mysql.com/doc/refman/5.7/en/create-table.html) in the *MySQL documentation*.

# Common table expressions for ANSI SQL


This topic provides reference information about Common Table Expressions (CTEs) and their compatibility between Microsoft SQL Server 2019 and Amazon Aurora MySQL. You can understand the differences in CTE support between these database systems, which is crucial when migrating from SQL Server to Aurora MySQL.


| Feature compatibility |  AWS SCT / AWS DMS automation level |  AWS SCT action code index | Key differences | 
| --- | --- | --- | --- | 
|   ![\[Two star feature compatibility\]](http://docs.aws.amazon.com/dms/latest/sql-server-to-aurora-mysql-migration-playbook/images/pb-compatibility-2.png)   |   ![\[No automation\]](http://docs.aws.amazon.com/dms/latest/sql-server-to-aurora-mysql-migration-playbook/images/pb-automation-0.png)   |   [Common Table Expressions](chap-sql-server-aurora-mysql.tools.actioncode.md#chap-sql-server-aurora-mysql.tools.actioncode.commontableexpressions)   |  Rewrite non-recursive CTE to use views and derived tables. Redesign recursive CTE code.  | 

## SQL Server Usage


Common Table Expressions (CTE), which have been a part of the ANSI standard since SQL:1999, simplify queries and make them more readable by defining a temporary view, or derived table, that a subsequent query can reference. SQL Server CTEs can be the target of DML modification statements and have similar restrictions as updateable views.

SQL Server CTEs provide recursive functionality in accordance with the ANSI 99 standard. Recursive CTEs can reference themselves and re-run queries until the data set is exhausted, or the maximum number of iterations is exceeded.

### Simplified CTE Syntax


```
WITH <CTE NAME>
AS
(
SELECT ....
)
SELECT ...
FROM CTE
```

### Recursive CTE syntax


```
WITH <CTE NAME>
AS (
<Anchor SELECT query>
UNION ALL
<Recursive SELECT query with reference to <CTE NAME>>
)
SELECT ... FROM <CTE NAME>...
```

### Examples


Create and populate an `OrderItems` table.

```
CREATE TABLE OrderItems
(
    OrderID INT NOT NULL,
    Item VARCHAR(20) NOT NULL,
    Quantity SMALLINT NOT NULL,
    PRIMARY KEY(OrderID, Item)
);
```

```
INSERT INTO OrderItems (OrderID, Item, Quantity)
VALUES
(1, 'M8 Bolt', 100),
(2, 'M8 Nut', 100),
(3, 'M8 Washer', 200),
(3, 'M6 Washer', 100);
```

Define a CTE to calculate the total quantity in every order and then join to the `OrderItems` table to obtain the relative quantity for each item.

```
WITH AggregatedOrders
AS
(
    SELECT OrderID, SUM(Quantity) AS TotalQty
    FROM OrderItems
    GROUP BY OrderID
)
SELECT O.OrderID, O.Item, O.Quantity,
    (O.Quantity / AO.TotalQty) * 100 AS PercentOfOrder
FROM OrderItems AS O
    INNER JOIN
    AggregatedOrders AS AO
    ON O.OrderID = AO.OrderID;
```

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

```
OrderID  Item       Quantity  PercentOfOrder
1        M8 Bolt    100       100.0000000000
2        M8 Nut     100       100.0000000000
3        M8 Washer  100       33.3333333300
3        M6 Washer  200       66.6666666600
```

Using a recursive CTE, create and populate the `Employees` table with the `DirectManager` for each employee.

```
CREATE TABLE Employees
(
    Employee VARCHAR(5) NOT NULL PRIMARY KEY,
    DirectManager VARCHAR(5) NULL
);
```

```
INSERT INTO Employees(Employee, DirectManager)
VALUES
('John', 'Dave'),
('Jose', 'Dave'),
('Fred', 'John'),
('Dave', NULL);
```

Use a recursive CTE to display the employee-management hierarchy.

```
WITH EmpHierarchyCTE AS
(
-- Anchor query retrieves the top manager
SELECT 0 AS LVL,
    Employee,
    DirectManager
FROM Employees AS E
WHERE DirectManager IS NULL
UNION ALL
-- Recursive query gets all Employees managed by the previous level
SELECT LVL + 1 AS LVL,
    E.Employee,
    E.DirectManager
FROM EmpHierarchyCTE AS EH
INNER JOIN
Employees AS E
ON E.DirectManager = EH.Employee
)
SELECT *
FROM EmpHierarchyCTE;
```

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

```
LVL  Employee  DirectManager
0    Dave      NULL
1    John      Dave
1    Jose      Dave
2    Fred      John
```

For more information, see [Recursive Queries Using Common Table Expressions](https://docs.microsoft.com/en-us/previous-versions/sql/sql-server-2008-r2/ms186243(v=sql.105)) in the *SQL Server documentation*.

## MySQL Usage


 Amazon Aurora MySQL-Compatible Edition (Aurora MySQL) 5.7 doesn’t support Common Table Expressions (CTE).

**Note**  
 Amazon Relational Database Service (Amazon RDS) for MySQL 8 supports common table expressions both nonrecursive and recursive. Common table expressions enable use of named temporary result sets implemented by permitting a `WITH` clause preceding `SELECT` statements and certain other statements. As of MySQL 8.0.19, the recursive `SELECT` part of a recursive common table expression supports a `LIMIT` clause. `LIMIT` with `OFFSET` is also supported. For more information, see [Recursive Common Table Expressions](https://dev.mysql.com/doc/refman/8.0/en/with.html#common-table-expressions-recursive) in the *MySQL documentation*.

### Migration Considerations


As a workaround, use views or derived tables in place of non-recursive CTEs.

Since non-recursive CTEs are more convenient for readability and code simplification, You can convert the code to use derived tables, which are a subquery in the parent query’s `FROM` clause. For example, replace the following CTE:

```
WITH TopCustomerOrders
(
    SELECT Customer,
    COUNT(*) AS NumOrders
    FROM Orders
    GROUP BY Customer
)
SELECT TOP 10 *
FROM TopCustomerOrders
ORDER BY NumOrders DESC;
```

With the following subquery:

```
SELECT *
FROM (
    SELECT Customer,
    COUNT(*) AS NumOrders
    FROM Orders
    GROUP BY Customer
) AS TopCustomerOrders
ORDER BY NumOrders DESC
LIMIT 10 OFFSET 0;
```

When using derived tables, the derived table definition must be repeated if multiple instances are required for the query.

Converting the code for recursive CTEs isn’t straight forward, but you can achieve similar functionality using loops.

### Examples


 **Replacing non-recursive CTEs** 

Use a derived table to replace non-recursive CTE functionality as shown following.

Create and populate an `OrderItems` table.

```
CREATE TABLE OrderItems
(
    OrderID INT NOT NULL,
    Item VARCHAR(20) NOT NULL,
    Quantity SMALLINT NOT NULL,
    PRIMARY KEY(OrderID, Item)
);
```

```
INSERT INTO OrderItems (OrderID, Item, Quantity)
VALUES
(1, 'M8 Bolt', 100),
(2, 'M8 Nut', 100),
(3, 'M8 Washer', 200),
(3, 'M6 Washer', 100);
```

Define a derived table for `TotalQty` of every order and then join to the `OrderItems` to obtain the relative quantity for each item.

```
SELECT O.OrderID,
    O.Item,
    O.Quantity,
    (O.Quantity / AO.TotalQty) * 100 AS PercentOfOrder
FROM OrderItems AS O
    INNER JOIN
    (
        SELECT OrderID,
        SUM(Quantity) AS TotalQty
        FROM OrderItems
        GROUP BY OrderID
    ) AS AO
    ON O.OrderID = AO.OrderID;
```

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

```
OrderID  Item       Quantity  PercentOfOrder
1        M8 Bolt    100       100.0000000000
2        M8 Nut     100       100.0000000000
3        M8 Washer  100       33.3333333300
3        M6 Washer  200       66.6666666600
```

 **Replacing recursive CTEs** 

Use recursive SQL code in stored procedures and SQL loops to replace a recursive CTEs.

**Note**  
Stored procedure and function recursion in Aurora MySQL is turned off by default. You can set the server system variable `max_sp_recursion_depth` to a value of 1 or higher to enable recursion. However, this approach isn’t recommended because it may increase contention for the thread stack space.

Create and populate an `Employees` table.

```
CREATE TABLE Employees
(
    Employee VARCHAR(5) NOT NULL PRIMARY KEY,
    DirectManager VARCHAR(5) NULL
);
```

```
INSERT INTO Employees (Employee, DirectManager)
VALUES
('John', 'Dave'),
('Jose', 'Dave'),
('Fred', 'John'),
('Dave', NULL);
```

Create an `EmpHierarchy` table.

```
CREATE TABLE EmpHierarchy
(
    LVL INT,
    Employee VARCHAR(5),
    Manager VARCHAR(5)
);
```

Create a procedure that uses a loop to traverse the employee hierarchy. For more information, see [Stored Procedures](chap-sql-server-aurora-mysql.tsql.storedprocedures.md) and [Flow Control](chap-sql-server-aurora-mysql.tsql.flowcontrol.md).

```
CREATE PROCEDURE P()
BEGIN
DECLARE var_lvl INT;
DECLARE var_Employee VARCHAR(5);
SET var_lvl = 0;
SET var_Employee = (
    SELECT Employee
    FROM Employees |
    WHERE DirectManager IS NULL
);
INSERT INTO EmpHierarchy
VALUES (var_lvl, var_Employee, NULL);
WHILE var_lvl <> -1
DO
INSERT INTO EmpHierarchy (LVL, Employee, Manager)
SELECT var_lvl + 1,
    Employee,
    DirectManager
FROM Employees
WHERE DirectManager IN (
    SELECT Employee
    FROM EmpHierarchy
    WHERE LVL = var_lvl
);
IF NOT EXISTS (
    SELECT *
    FROM EmpHierarchy
    WHERE LVL = var_lvl + 1
)
THEN SET var_lvl = -1;
ELSE SET var_lvl = var_lvl + 1;
END IF;
END WHILE;
END;
```

Run the procedure.

```
CALL P()
```

Select all records from the `EmpHierarchy` table.

```
SELECT * FROM EmpHierarchy;
```

```
Level  Employee  Manager
0      Dave
1      John      Dave
1      Jose      Dave
2      Fred      John
```

## Summary



| SQL Server |  Aurora MySQL  | Comments | 
| --- | --- | --- | 
|  Non recursive CTE  |  Derived table  |  For multiple instances of the same table, the derived table definition subquery must be repeated.  | 
|  Recursive CTE  |  Loop inside a stored procedure or stored function.  |  | 

For more information, see [WITH (Common Table Expressions)](https://dev.mysql.com/doc/refman/8.0/en/with.html) in the *MySQL documentation*.

# Data types for ANSI SQL


This topic provides reference content about data type compatibility when migrating from Microsoft SQL Server 2019 to Amazon Aurora MySQL. You can use this information to understand how different data types in SQL Server map to their counterparts in Aurora MySQL.


| Feature compatibility |  AWS SCT / AWS DMS automation level |  AWS SCT action code index | Key differences | 
| --- | --- | --- | --- | 
|   ![\[Four star feature compatibility\]](http://docs.aws.amazon.com/dms/latest/sql-server-to-aurora-mysql-migration-playbook/images/pb-compatibility-4.png)   |   ![\[Four star automation level\]](http://docs.aws.amazon.com/dms/latest/sql-server-to-aurora-mysql-migration-playbook/images/pb-automation-4.png)   |   [Data Types](chap-sql-server-aurora-mysql.tools.actioncode.md#chap-sql-server-aurora-mysql.tools.actioncode.datatypes)   |  Minor syntax and handling differences. No special `UNICODE` data types.  | 

## SQL Server Usage


In SQL Server, each table column, variable, expression, and parameter has an associated data type.

SQL Server provides a rich set of built-in data types as summarized in the following table.


| Category | Data Types | 
| --- | --- | 
|  Numeric  |   `BIT`, `TINYINT`, `SMALLINT`, `INT`, `BIGINT`, `NUMERIC`, `DECIMAL`, `MONEY`, `SMALLMONEY`, `FLOAT`, `REAL`   | 
|  String and character  |   `CHAR`, `VARCHAR`, `NCHAR`, `NVARCHAR`   | 
|  Temporal  |   `DATE`, `TIME`, `SMALLDATETIME`, `DATETIME`, `DATETIME2`, `DATETIMEOFFSET`   | 
|  Binary  |   `BINARY`, `VARBINARY`   | 
|  Large Object (LOB)  |   `TEXT`, `NTEXT`, `IMAGE`, `VARCHAR(MAX)`, `NVARCHAR(MAX)`, `VARBINARY(MAX)`   | 
|  Cursor  |   `CURSOR`   | 
|  GUID  |   `UNIQUEIDENTIFIER`   | 
|  Hierarchical identifier  |   `HIERARCHYID`   | 
|  Spatial  |   `GEOMETRY`, `GEOGRAPHY`   | 
|  Sets (table type)  |   `TABLE`   | 
|  XML  |   `XML`   | 
|  Other specialty types  |   `ROW VERSION`, `SQL_VARIANT`   | 

**Note**  
You can create custom user-defined data types using T-SQL, and the .NET framework. Custom data types are based on the built-in system data types and are used to simplify development. For more information, see [User-Defined Types](chap-sql-server-aurora-mysql.tsql.udt.md).

### TEXT, NTEXT, and IMAGE Deprecated Data Types


The `TEXT`, `NTEXT`, and `IMAGE` data types have been deprecated as of SQL Server 2008 R2. For more information, see [Deprecated Database Engine Features in SQL Server 2008 R2](https://docs.microsoft.com/en-us/previous-versions/sql/sql-server-2008-r2/ms143729(v=sql.105)) in the *SQL Server documentation*.

These data types are legacy types for storing `BLOB` and `CLOB` data. The `TEXT` data type was used to store ASCII text `CLOBS`, the `NTEXT` data type to store `UNICODE CLOBS`, and `IMAGE` was used as a generic data type for storing all `BLOB` data. In SQL Server 2005, Microsoft introduced the new and improved `VARCHAR (MAX)`, `NVARCHAR(MAX)`, and `VARBINARY(MAX)` data types as the new `BLOB` and `CLOB` standard. These new types support a wider range of functions and operations. They also provide enhanced performance over the legacy types.

If your code uses `TEXT`, `NTEXT`, or `IMAGE` data types, AWS SCT automatically converts them to the appropriate Amazon Aurora MySQL-Compatible Edition (Aurora MySQL) `BLOB` data type. `TEXT` and `NTEXT` are converted to `LONGTEXT` and image to `LONGBLOB`. Make sure you use the proper collations. For more details, see the [Collations](chap-sql-server-aurora-mysql.tsql.collations.md).

### Examples


Define table columns.

```
CREATE TABLE MyTable
(
    Col1 AS INTEGER NOT NULL PRIMARY KEY,
    Col2 AS NVARCHAR(100) NOT NULL
);
```

Define variable types.

```
DECLARE @MyXMLType AS XML,
    @MyTemporalType AS DATETIME2
```

```
DECLARE @MyTableType
AS TABLE
(
    Col1 AS BINARY(16) NOT NULL PRIMARY KEY,
    Col2 AS XML NULL
);
```

For more information, see [Data types (Transact-SQL)](https://docs.microsoft.com/en-us/sql/t-sql/data-types/data-types-transact-sql?view=sql-server-ver15) in the *SQL Server documentation*.

## MySQL Usage


 Amazon Aurora MySQL-Compatible Edition (Aurora MySQL) supports the following data types:


| Category | Data Types | 
| --- | --- | 
|  Numeric  |   `BIT`, `INTEGER`, `SMALLINT`, `TINYINT`, `MEDIUMINT`, `BIGINT`, `DECIMAL`, `NUMERIC`, `FLOAT`, `DOUBLE`   | 
|  String and character  |   `CHAR`, `VARCHAR`, `SET`   | 
|  Temporal  |   `DATE`, `DATETIME`, `TIMESTAMP`, `TIME`, `YEAR`   | 
|  Binary  |   `BINARY`, `VARBINARY`   | 
|  Large Object (LOB)  |   `BLOB`, `TEXT`   | 
|  Cursor  |   `CURSOR`   | 
|  Spatial  |   `GEOMETRY`, `POINT`, `LINESTRING`, `POLYGON`, `MULTIPOINT`, `MULTILINESTRING`, `MULTIPOLYGON`, `GEOMETRYCOLLECTION`   | 
|  JSON  |   `JSON`   | 

Be aware that Aurora MySQL uses different rules than SQL Server for handling out-of-range and overflow situations. SQL Server always raises an error for out-of-range values. Aurora MySQL exhibits different behavior depending on run time settings.

For example, a value may be clipped to the first or last value in the range of permitted values for the data type if `STRICT SQL` mode isn’t set.

For more information, see [Out-of-Range and Overflow Handling](https://dev.mysql.com/doc/refman/5.7/en/out-of-range-and-overflow.html) in the *MySQL documentation*.

### Converting from TEXT, NTEXT, and IMAGE SQL Server Deprecated Data Types


The legacy SQL Server types for storing LOB data are deprecated as of SQL Server 2008 R2.

When you convert from these types to Aurora MySQL using the AWS Schema Conversion Tool (AWS SCT, they are converted as shown following:


| SQL Server LOB Type | Converted to Aurora MySQL data type | Comments | 
| --- | --- | --- | 
|   `TEXT`   |   `LONGTEXT`   |  Make sure to choose the right collation. For more information, see [Collations](chap-sql-server-aurora-mysql.tsql.collations.md).  | 
|   `NTEXT`   |   `LONGTEXT`   |  Make sure to choose the right collation. For more information, see [Collations](chap-sql-server-aurora-mysql.tsql.collations.md).  | 
|   `IMAGE`   |   `LONGBLOB`   |  | 

The size cap for all of these types is compatible and is capped at 2 GB of data, which may allow less characters depending on the chosen collation.

**Note**  
 Aurora MySQL supports UCS-2 collation, which is compatible with SQL Server `UNICODE` types.

While it is safe to use the default conversion types, remember that, unlike SQL Server, Aurora MySQL also provides smaller `BLOB` and `CLOB` types, which may be more efficient for your data.

Even the basic VARCHAR and VARBINARY data types can store strings up to 32 KB, which is much longer than SQL Server 8 KB limit. If the strings or binary data that you need to store don’t exceed 32 KB, it may be more efficient to store these as non-LOB types in Aurora MySQL.

## Summary


The following table summarizes the key differences and migration considerations for migrating from SQL Server data types to Aurora MySQL data types.


| SQL Server Data Type | Convert to MySQL Data Type | Comments | 
| --- | --- | --- | 
|   `BIT`   |   `BIT`   |   Aurora MySQL also supports `BIT(m)`, which can be used to store multiple bit values. In SQL Server, literal bit notation uses the numerical digits 0 and 1. Aurora MySQL uses `b'<value>` or `0b<value>` notations. For more information, see [Bit-Value Type - BIT](https://dev.mysql.com/doc/refman/5.7/en/bit-type.html) and [Bit-Value Literals](https://dev.mysql.com/doc/refman/5.7/en/bit-value-literals.html) in the *MySQL documentation*.  | 
|   `TINYINT`   |   `TINYINT`   |  SQL Server only supports unsigned `TINYINT`, which can store values between 0 and 255. Aurora MySQL supports both signed `TINYINT` and `TINYINT UNSIGNED`. The latter can be used to store values between -128 and 127. The default for integer types in Aurora MySQL is to use signed integers. For compatibility, explicitly specify `TINYINT UNSIGNED`. For more information, see [Integer Types (Exact Value)](https://dev.mysql.com/doc/refman/5.7/en/integer-types.html) in the *MySQL documentation*.  | 
|   `SMALLINT`   |   `SMALLINT`   |  Compatible type. SQL Server supports only signed `SMALLINT`. Aurora MySQL also supports `SMALLINT UNSIGNED`, which can store values between 0 and 65535. The default for integer types in Aurora MySQL is to use signed integers. Consider using unsigned integers for storage optimization. For more information, see [Integer Types (Exact Value)](https://dev.mysql.com/doc/refman/5.7/en/integer-types.html) in the *MySQL documentation*.  | 
|   `INTEGER`   |   `INTEGER`   |  Compatible type. SQL Server supports only signed `INTEGER`, which can store values between -2147483648 and 2147483647. Aurora MySQL also supports `INTEGER UNSIGNED`, which can store values between 0 and 4294967295. The default for integer types in Aurora MySQL is to use signed integers. Consider using unsigned integers for storage optimization.  Aurora MySQL also supports a `MEDIUMINT` type, which uses only three bytes of storage vs. four bytes for `INTEGER`. For large tables, consider using `MEDIUMINT` instead of `INT` if the value range is within -8388608 to -8388607 for a `SIGNED` type, or 0 to 16777215 for `UNSIGNED` type. For more information, see [Integer Types (Exact Value)](https://dev.mysql.com/doc/refman/5.7/en/integer-types.html) in the *MySQL documentation*.  | 
|   `BIGINT`   |   `BIGINT`   |  Compatible type. SQL Server supports only signed `BIGINT`. Aurora MySQL also supports `BIGINT UNSIGNED`, which can store values between 0 and 2^64-1. The default for integer types in Aurora MySQL is to use signed integers. Consider using unsigned integers for storage optimization. For more information, see [Integer Types (Exact Value)](https://dev.mysql.com/doc/refman/5.7/en/integer-types.html) in the *MySQL documentation*.  | 
|   `NUMERIC` / `DECIMAL`   |   `NUMERIC` / `DECIMAL`   |  Compatible types. DECIMAL and NUMERIC are synonymous.  | 
|   `MONEY` / `SMALLMONEY`   |  N/A  |   Aurora MySQL doesn’t support dedicated monetary types. Use `NUMERIC` / `DECIMAL` instead. If your application uses literals with monetary signs (for example, \$150.23), rewrite to remove the monetary sign.  | 
|   `FLOAT` / `REAL`   |   `FLOAT` / `REAL` / `DOUBLE`   |  Compatible types. In SQL Server, both `REAL` and `FLOAT(n)`, where `n⇐24`, use 4 bytes of storage, are equivalent to `FLOAT` and `REAL` in Aurora MySQL. In SQL Server, `FLOAT(n)`, where `n>24`, uses 8 bytes. The Aurora MySQL `DOUBLE PRECISION` type always uses 8 bytes.  Aurora MySQL also supports the nonstandard `FLOAT(M,D)`, `REAL(M,D)` or `DOUBLE PRECISION(M,D)` where `(M,D)` indicates values can be stored with up to M digits in total with D digits after the decimal point. For more information, see [Floating-Point Types (Approximate Value)](https://dev.mysql.com/doc/refman/5.7/en/floating-point-types.html) in the *MySQL documentation*.  | 
|   `CHAR`   |   `CHAR` / `VARCHAR`   |  Compatible types up to 255 characters only. SQL Server supports `CHAR` data types up to 8,000 characters. The Aurora MySQL `CHAR` data type is limited to a maximum of 255 characters. For strings requiring more than 255 characters, use `VARCHAR`. When converting from `CHAR` to `VARCHAR`, exercise caution because `VARCHAR` behaves differently than `CHAR`; trailing spaces are trimmed. For more information, see [The CHAR and VARCHAR Types](https://dev.mysql.com/doc/refman/5.7/en/char.html) in the *MySQL documentation*.  | 
|   `VARCHAR`   |   `VARCHAR`   |  Compatible types. SQL Server supports `VARCHAR` columns up to 8,000 characters. Aurora MySQL can store up to 65,535 characters with regard to the maximal row size limit. For more information, see [The CHAR and VARCHAR Types](https://dev.mysql.com/doc/refman/5.7/en/char.html) in the *MySQL documentation*.  | 
|   `NCHAR`   |   `CHAR`   |   Aurora MySQL doesn’t require the use of specific data types for storing `UNICODE` data. Use the `CHAR` data type and define a `UNICODE` collation using the `CHARACTER SET` or `COLLATE` keywords. For more information, see [Unicode Character Sets](https://dev.mysql.com/doc/refman/5.7/en/charset-unicode-sets.html) in the *MySQL documentation*.  | 
|   `NVARCHAR`   |   `VARCHAR`   |   Aurora MySQL doesn’t require the use of specific data types for storing `UNICODE` data. Use the `VARCHAR` data type and define a `UNICODE` collation using the `CHARACTER SET` or `COLLATE` keywords. For more information, see [Unicode Character Sets](https://dev.mysql.com/doc/refman/5.7/en/charset-unicode-sets.html) in the *MySQL documentation*.  | 
|   `DATE`   |   `DATE`   |  Compatible types. The range for SQL Server `DATE` data type is '0001-01-01' through '9999-12-31'. The range for Aurora MySQL is '1000-01-01' through '9999-12-31'.  Aurora MySQL doesn’t support dates before 1000 AD. For more information, see [Date and Time Functions](chap-sql-server-aurora-mysql.tsql.datetime.md). For more information, see [The DATE, DATETIME, and TIMESTAMP Types](https://dev.mysql.com/doc/refman/5.7/en/datetime.html) in the *MySQL documentation*.  | 
|   `TIME`   |   `TIME`   |  Compatible types. SQL Server supports explicit fractional seconds using the format `TIME(n)` where n is between 0 to 7. Aurora MySQL doesn’t allow explicit fractional setting.  Aurora MySQL supports up to 6 digits for microsecond resolution of fractional seconds. SQL Server provides one more digit to support a resolution of up to 100 nanoseconds. If your application uses the `TIME(n)` format, rewrite to remove the `(n)` setting.  Aurora MySQL also supports `TIME` values that range from `-838:59:59` to `838:59:59`. You can use the hours part to represent the time of day, where hours must be less than 24, or to represent a time interval, which can be greater than 24 hours and have negative values. For more information, see [The TIME Type](https://dev.mysql.com/doc/refman/5.7/en/time.html) in the *MySQL documentation*.  | 
|   `SMALLDATETIME`   |   `DATETIME` / `TIMESTAMP`   |   Aurora MySQL doesn’t support `SMALLDATETIME`. Use `DATETIME` instead. Use `SMALLDATETIME` for storage space optimization where lower ranges and resolutions are required. For more information, see [Date and Time Functions](chap-sql-server-aurora-mysql.tsql.datetime.md).  | 
|   `DATETIME`   |   `DATETIME`   |  In SQL Server, the `DATETIME` data type supports a value range between `1753-01-01` and `9999-12-31` with a resolution of up to 3.33ms. Aurora MySQL `DATETIME` supports a wider value range between `1000-01-01 00:00:00` and `9999-12-31 23:59:59` with a higher resolution of microseconds and optional six fractional second digits. For more information, see [Date and Time Functions](chap-sql-server-aurora-mysql.tsql.datetime.md). For more information about `DATETIME`, see [The DATE, DATETIME, and TIMESTAMP Types](https://dev.mysql.com/doc/refman/5.7/en/datetime.html) in the *MySQL documentation*.  | 
|   `DATETIME2`   |   `DATETIME`   |  In SQL Server, the `DATETIME2` data type supports a value range between `0001-01-01` and `9999-12-31` with a resolution of up to 100 nanoseconds using seven fractional second digits. Aurora MySQL `DATETIME` supports a narrower value range between `1000-01-01 00:00:00` and `9999-12-31 23:59:59` with a lower resolution of microseconds and optional six fractional second digits. For more information, see [Date and Time Functions](chap-sql-server-aurora-mysql.tsql.datetime.md). For more information about `DATETIME`, see [The DATE, DATETIME, and TIMESTAMP Types](https://dev.mysql.com/doc/refman/5.7/en/datetime.html) in the *MySQL documentation*.  | 
|   `DATETIMEOFFSET`   |   `TIMESTAMP`   |   Aurora MySQL doesn’t support full time zone awareness and management functions. Use the `time_zone` system variable in conjunction with `TIMESTAMP` columns to achieve partial time zone awareness. For more information, see [Server Options](chap-sql-server-aurora-mysql.configuration.serveroptions.md). In Aurora MySQL, `TIMESTAMP` isn’t the same as in SQL Server. The latter is a synonym for `ROW_VERSION`. Aurora MySQL `TIMESTAMP` is equivalent to the `DATETIME` type with a smaller range. With Aurora MySQL `DATETIME`, you can use values between `1000-01-01 00:00:00` and `9999-12-31 23:59:59`. TIMESTAMP is limited to values between `1970-01-01 00:00:01` and `2038-01-19 03:14:07`.  Aurora MySQL converts `TIMESTAMP` values from the current time zone to UTC for storage and back from UTC to the current time zone for retrieval. For more information, see [MySQL Server Time Zone Support](https://dev.mysql.com/doc/refman/5.7/en/time-zone-support.html) in the *MySQL documentation*.  | 
|   `BINARY`   |   `BINARY` / `VARBINARY`   |  In Aurora MySQL, the `BINARY` data type is considered to be a string data type and is similar to `CHAR`. `BINARY` contains byte strings rather than character strings and uses the binary character set and collation. Comparison and sorting are based on the numeric values of the bytes in the values. SQL Server supports up to 8,000 bytes for a `BINARY` data types. Aurora MySQL `BINARY` is limited to 255 characters, similar to `CHAR`. If larger values are needed, use `VARBINARY`. Literal assignment for Aurora MySQL `BINARY` types use string literals, unlike SQL Server explicit binary `0x` notation. For more information, see [The BINARY and VARBINARY Types](https://dev.mysql.com/doc/refman/5.7/en/binary-varbinary.html) and [The binary Collation Compared to bin Collations](https://dev.mysql.com/doc/refman/5.7/en/charset-binary-collations.html) in the *MySQL documentation*.  | 
|   `VARBINARY`   |   `VARBINARY`   |  In Aurora MySQL, the `VARBINARY` data type is considered a string data type, similar to VARCHAR. `VARBINARY` contains byte strings rather than character strings and has a binary character set. Collation, comparison, and sorting are based on the numeric values of the bytes in the values.  Aurora MySQL `VARBINARY` supports up to 65,535 characters, significantly larger than the 8,000 byte limit in SQL Server. Literal assignment for Aurora MySQL BINARY types use string literals, unlike SQL Server explicit binary `0x` notation. For more information, see [The BINARY and VARBINARY Types](https://dev.mysql.com/doc/refman/5.7/en/binary-varbinary.html) and [The binary Collation Compared to bin Collations](https://dev.mysql.com/doc/refman/5.7/en/charset-binary-collations.html) in the *MySQL documentation*.  | 
|   `TEXT` / `VARCHAR (MAX)`   |   `VARCHAR` / `TEXT` / `MEDIUMTEXT` / `LONGTEXT`   |  In SQL Server, a `TEXT` data type is a variable-length ASCII string data type with a maximum string length of 2^31-1 (2 GB). Use the following list to determine the optimal Aurora MySQL data type: [\[See the AWS documentation website for more details\]](http://docs.aws.amazon.com/dms/latest/sql-server-to-aurora-mysql-migration-playbook/chap-sql-server-aurora-mysql.sql.datatypes.html) For more information, see [The BLOB and TEXT Types](https://dev.mysql.com/doc/refman/5.7/en/blob.html) and [Data Type Storage Requirements](https://dev.mysql.com/doc/refman/5.7/en/storage-requirements.html#data-types-storage-reqs-string) in the *MySQL documentation*.  | 
|   `NTEXT` / `NVARCHAR (MAX)`   |   `VARCHAR` / `TEXT` / `MEDIUMTEXT` / `LONGTEXT`   |   Aurora MySQL doesn’t require the use of specific data types for storing `UNICODE` data. Use the `TEXT` compatible data types listed earlier and define a `UNICODE` collation using the `CHARACTER SET` or `COLLATE` keywords. For more information, see [Unicode Character Sets](https://dev.mysql.com/doc/refman/5.7/en/charset-unicode-sets.html) in the *MySQL documentation*.  | 
|   `IMAGE` / `VARBINARY (MAX)`   |   `VARBINARY` / `BLOB` / `MEDIUMBLOB` / `LONGBLOB`   |  In SQL Server, an `IMAGE` data type is a variable-length binary data type with a range of 0 through 2^31-1 (2 GB). Similar to the `BINARY` and `VARBINARY` data types, the `BLOB` data types are considered string data types. `BLOB` data types contain byte strings rather than character strings and use a binary character set. Collation, comparison, and sorting are based on the numeric values of the bytes in the values. Use the following list to determine the optimal Aurora MySQL data type: [\[See the AWS documentation website for more details\]](http://docs.aws.amazon.com/dms/latest/sql-server-to-aurora-mysql-migration-playbook/chap-sql-server-aurora-mysql.sql.datatypes.html) For more information, see [The BLOB and TEXT Types](https://dev.mysql.com/doc/refman/5.7/en/blob.html), [String Type Storage Requirements](https://dev.mysql.com/doc/refman/5.7/en/storage-requirements.html#data-types-storage-reqs-strings), and [The binary Collation Compared to bin Collations](https://dev.mysql.com/doc/refman/5.7/en/charset-binary-collations.html) in the *MySQL documentation*.  | 
|   `CURSOR`   |   `CURSOR`   |  Types are compatible, although in Aurora MySQL a cursor isn’t really considered to be a type. For more information, see [Cursors](chap-sql-server-aurora-mysql.tsql.cursors.md).  | 
|   `UNIQUEIDENTIFIER`   |  N/A  |   Aurora MySQL doesn’t support a native unique identifier type. Use `BINARY(16)`, which is the same base type used for the `UNIQUEIDENTIFIER` type in SQL Server. It generates values using the `UUID()` function, which is the equivalent of the `NEW_ID` function in SQL Server. UUID returns a Universal Unique Identifier generated according to RFC 4122. For more information, see [A Universally Unique IDentifier (UUID) URN Namespace](https://www.ietf.org/rfc/rfc4122.txt). For more information, see [UUID()](https://dev.mysql.com/doc/refman/5.7/en/miscellaneous-functions.html#function_uuid) in the *MySQL documentation*.  | 
|   `HIERARCHYID`   |  N/A  |   Aurora MySQL doesn’t support native hierarchy representation. Rewrite functionality with custom application code using one the common SQL hierarchical data representation approaches: [\[See the AWS documentation website for more details\]](http://docs.aws.amazon.com/dms/latest/sql-server-to-aurora-mysql-migration-playbook/chap-sql-server-aurora-mysql.sql.datatypes.html) For more information, see [Adjacency list](https://en.wikipedia.org/wiki/Adjacency_list) and [Nested set model](https://en.wikipedia.org/wiki/Nested_set_model).  | 
|   `GEOMETRY`   |   `GEOMETRY`   |  In SQL Server, the `GEOMETRY` type represents data in a Euclidean (flat) coordinate system. SQL Server supports a set of methods for this type, which include methods defined by the Open Geospatial Consortium (OGC) standard, and a set of additional extensions.  Aurora MySQL supports `GEOMETRY` spatial data, although the syntax and functionality may differ significantly from SQL Server. A rewrite of the code is required. For more information, see [Spatial Data Types](https://dev.mysql.com/doc/refman/5.7/en/spatial-types.html) in the *MySQL documentation*.  | 
|   `TABLE`   |  N/A  |   Aurora MySQL doesn’t support a `TABLE` data type. For more information, see [User-Defined Types](chap-sql-server-aurora-mysql.tsql.udt.md).  | 
|   `XML`   |  N/A  |   Aurora MySQL doesn’t support a native XML data type. However, it does provide full support for JSON data types, which SQL Server doesn’t. Because XML and JSON are text documents, consider migrating the XML formatted documents to JSON or use string BLOBs and custom code to parse and query documents. For more information, see [The JSON Data Type](https://dev.mysql.com/doc/refman/5.7/en/json.html) in the *MySQL documentation*.  | 
|   `ROW_VERSION`   |  N/A  |   Aurora MySQL doesn’t support a row version. Use triggers to update a dedicated column from a primary sequence value table.  | 
|   `SQL_VARIANT`   |  N/A  |   Aurora MySQL doesn’t support a hybrid, all-purpose data type similar to `SQL_VARIANT` in SQL Server. Rewrite applications to use explicit types.  | 

For more information, see [Data Types](https://dev.mysql.com/doc/refman/5.7/en/data-types.html) in the *MySQL documentation*.

# GROUP BY for ANSI SQL


This topic provides reference content comparing the GROUP BY functionality in Microsoft SQL Server 2019 and Amazon Aurora MySQL. It explores the similarities and differences in syntax, supported features, and aggregate functions between the two database systems.


| Feature compatibility |  AWS SCT / AWS DMS automation level |  AWS SCT action code index | Key differences | 
| --- | --- | --- | --- | 
|   ![\[Four star feature compatibility\]](http://docs.aws.amazon.com/dms/latest/sql-server-to-aurora-mysql-migration-playbook/images/pb-compatibility-4.png)   |   ![\[Four star automation level\]](http://docs.aws.amazon.com/dms/latest/sql-server-to-aurora-mysql-migration-playbook/images/pb-automation-4.png)   |   [GROUP BY](chap-sql-server-aurora-mysql.tools.actioncode.md#chap-sql-server-aurora-mysql.tools.actioncode.groupby)   |  Basic syntax compatible. Advanced options such as `ALL`, `CUBE`, `GROUPING SETS` will require rewrites to use multiple queries with `UNION`.  | 

## SQL Server Usage


 `GROUP BY` is an ANSI SQL query clause used to group individual rows that have passed the `WHERE` filter clause into groups to be passed on to the `HAVING` filter and then to the `SELECT` list. This grouping supports the use of aggregate functions such as `SUM`, `MAX`, `AVG` and others.

### Syntax


ANSI compliant `GROUP BY` syntax:

```
GROUP BY
[ROLLUP | CUBE]
<Column Expression> ...n
[GROUPING SETS (<Grouping Set>)...n
```

Backward compatibility syntax:

```
GROUP BY
    [ ALL ] <Column Expression> ...n
    [ WITH CUBE | ROLLUP ]
```

The basic ANSI syntax for `GROUP BY` supports multiple grouping expressions, the `CUBE` and ROLLUP keywords, and the `GROUPING SETS` clause; all used to add super-aggregate rows to the output.

Up to SQL Server 2008 R2, the database engine supported a legacy, proprietary, and not ANSI-compliant syntax using the `WITH CUBE` and `WITH ROLLUP` clauses. These clauses added super-aggregates to the output.

Also, up to SQL Server 2008 R2, SQL Server supported the `GROUP BY ALL` syntax, which was used to create an empty group for rows that failed the `WHERE` clause.

SQL Server supports the following aggregate functions: `AVG`, `CHECKSUM_AGG`, `COUNT`, `COUNT_BIG`, `GROUPING`, `GROUPING_ID`, `STDEV`, `STDEVP`, `STRING_AGG`, `SUM`, `MIN`, `MAX`, `VAR`, `VARP`.

### Examples


 **Legacy CUBE and ROLLUP Syntax** 

```
CREATE TABLE Orders
(
    OrderID INT IDENTITY(1,1) NOT NULL
    PRIMARY KEY,
    Customer VARCHAR(20) NOT NULL,
    OrderDate DATE NOT NULL
);
```

```
INSERT INTO Orders(Customer, OrderDate)
VALUES ('John', '20180501'), ('John', '20180502'), ('John', '20180503'),
    ('Jim', '20180501'), ('Jim', '20180503'), ('Jim', '20180504')
```

```
SELECT Customer,
    OrderDate,
    COUNT(*) AS NumOrders
FROM Orders AS O
GROUP BY Customer, OrderDate
WITH ROLLUP
```

```
Customer  OrderDate   NumOrders
Jim       2018-05-01  1
Jim       2018-05-03  1
Jim       2018-05-04  1
Jim       NULL        3
John      2018-05-01  1
John      2018-05-02  1
John      2018-05-03  1
John      NULL        3
NULL      NULL        6
```

The rows with NULL values were added as a result of the `WITH ROLLUP` clause and contain super aggregates for the following:
+ All orders for Jim and John regardless of `OrderDate`.
+ A super aggregated for all customers and all dates.

Using `CUBE` instead of `ROLLUP` adds super aggregates in all possible combinations, not only in group by expression order.

```
SELECT Customer,
    OrderDate,
    COUNT(*) AS NumOrders
FROM Orders AS O
GROUP BY Customer, OrderDate
WITH CUBE
```

```
Customer  OrderDate   NumOrders
Jim       2018-05-01  1
John      2018-05-01  1
NULL      2018-05-01  2
John      2018-05-02  1
NULL      2018-05-02  1
Jim       2018-05-03  1
John      2018-05-03  1
NULL      2018-05-03  2
Jim       2018-05-04  1
NULL      2018-05-04  1
NULL      NULL        6
Jim       NULL        3
John      NULL        3
```

The additional four rows where the value for Customer is set to NULL, were added by `CUBE`. These rows provide super aggregates for every date for all customers that were not part of the `ROLLUP` results.

 **Legacy GROUP BY ALL** 

Use the Orders table from the preceding example.

```
SELECT Customer,
    OrderDate,
    COUNT(*) AS NumOrders
FROM Orders AS O
WHERE OrderDate <= '20180503'
GROUP BY ALL Customer, OrderDate
```

```
Customer  OrderDate   NumOrders
Jim       2018-05-01  1
John      2018-05-01  1
John      2018-05-02  1
Jim       2018-05-03  1
John      2018-05-03  1
Jim       2018-05-04  0
Warning: Null value is eliminated by an aggregate or other SET operation.
```

The last row for 2018-05-04 failed the `WHERE` clause and was returned as an empty group as indicated by the warning for the empty `COUNT(*) = 0`.

 **Use GROUPING SETS** 

The following query uses the ANSI compliant `GROUPING SETS` syntax to provide all possible aggregate combinations for the `Orders` table, similar to the result of the `CUBE` syntax. This syntax requires specifying each dimension that needs to be aggregated.

```
SELECT Customer,
    OrderDate,
    COUNT(*) AS NumOrders
FROM Orders AS O
GROUP BY GROUPING SETS (
    (Customer, OrderDate),
    (Customer),
    (OrderDate),
    ()
    )
```

```
Customer  OrderDate   NumOrders
Jim       2018-05-01  1
John      2018-05-01  1
NULL      2018-05-01  2
John      2018-05-02  1
NULL      2018-05-02  1
Jim       2018-05-03  1
John      2018-05-03  1
NULL      2018-05-03  2
Jim       2018-05-04  1
NULL      2018-05-04  1
NULL      NULL        6
Jim       NULL        3
John      NULL        3
```

For more information, see [Aggregate Functions (Transact-SQL)](https://docs.microsoft.com/en-us/sql/t-sql/functions/aggregate-functions-transact-sql?view=sql-server-ver15) and [SELECT - GROUP BY- Transact-SQL](https://docs.microsoft.com/en-us/sql/t-sql/queries/select-group-by-transact-sql?view=sql-server-ver15) in the *SQL Server documentation*.

## MySQL Usage


 Amazon Aurora MySQL-Compatible Edition (Aurora MySQL) supports only the basic ANSI syntax for `GROUP BY` and doesn’t support `GROUPING SETS` or the standard `GROUP BY CUBE` and `GROUP BY ROLLUP`. Aurora MySQL supports the `WITH ROLLUP` non-ANSI syntax like SQL Server, but not the `CUBE` option.

 Aurora MySQL supports a wider range of aggregate functions than SQL Server: `AVG`, `BIT_AND`, `BIT_OR`, `BIT_XOR`, `COUNT`, `GROUP_CONCAT`, `JSON_ARRAYAGG`, `JSON_OBJECTAGG`, `MAX`, `MIN`, `STD`, `STDDEV`, `STDDEV_POP`, `STDDEV_SAMP`, `SUM`, `VAR_POP`, `VAR_SAMP`, `VARIANCE`.

The bitwise aggregates and the JSON aggregates not available in SQL Server may prove to be very useful in many scenarios. For more information, see [MySQL Handling of GROUP BY](https://dev.mysql.com/doc/refman/5.7/en/group-by-handling.html) in the *MySQL documentation*.

Unlike SQL Server, in Aurora MySQL you can’t use `ROLLUP` and `ORDER BY` clauses in the same query. As a workaround, encapsulate the `ROLLUP` query as a derived table and add the `ORDER BY` clause to the outer query.

```
SELECT *
FROM (
    SELECT Customer,
        OrderDate,
        COUNT(*) AS NumOrders
    FROM Orders AS O
    GROUP BY Customer, OrderDate
    WITH ROLLUP
)
ORDER BY OrderDate, Customer;
```

Additionally, rows produced by `ROLLUP` can’t be referenced in a `WHERE` clause or in a `FROM` clause as a join condition because the super aggregates are added late in the processing phase.

Even more problematic is the lack of a function equivalent to the `GROUPING_ID` function in SQL Server, which can be used to distinguish super aggregate rows from the base groups. Unfortunately, it is currently not possible to distinguish rows that have NULLs due to being super aggregates from rows where the NULL is from the base set.

Until SQL92, column expressions not appearing in the `GROUP BY` list were not allowed in the `HAVING`, `SELECT`, and `ORDER BY` clauses. This limitation still applies in SQL Server today. For example, the following query isn’t legal in SQL Serve since a customer group may contain multiple order dates.

```
SELECT Customer,
    OrderDate,
    COUNT(*) AS NumOrders
FROM Orders AS O
GROUP BY Customer
```

However, in some cases, when the columns that don’t appear in the GROUP BY clause are functionally dependent on the `GROUP BY` columns, it does make sense to allow it and ANSI SQL optional feature T301 does allow it. Aurora MySQL can detect such functional dependencies and allows such queries to run.

**Note**  
To use non-aggregate columns in the `HAVING`, `SELECT`, and `ORDER BY` clauses, turn on the `ONLY_FULL_GROUP_BY` SQL mode.

### Syntax


```
SELECT <Select List>
FROM <Table Source>
WHERE <Row Filter>
GROUP BY <Column Name> | <Expression> | <Position>
    [ASC | DESC], ...
    [WITH ROLLUP]]
```

### Migration Considerations


For most aggregate queries that use only grouping expressions without modifiers, the migration should be straightforward. Even the `WITH ROLLUP` syntax is supported as is in Aurora MySQL. For more complicated aggregates such as `CUBE` and `GROUPING SETS`, a rewrite to include all sub-aggregate queries as `UNION ALL` sets is required.

Because Aurora MySQL supports a wider range of aggregate functions, the migration shouldn’t present major challenges. Some minor syntax changes, for example replacing `STDEVP` with `STDDEV_POP`, can be performed automatically by the AWS Schema Conversion Tool (AWS SCT. Some may need manual intervention such as rewriting the `STRING_AGG` syntax to `GROUP_CONCAT`. Also consider using Aurora MySQL additional aggregate functions for query optimizations.

If you plan to keep using the `WITH ROLLUP` groupings, you must consider how to handle NULLS since Aurora MySQL doesn’t support an equivalent function to `GROUPING_ID` in SQL Server.

### Examples


Rewrite SQL Server WITH CUBE modifier for migration.

```
CREATE TABLE Orders
(
    OrderID INT NOT NULL AUTO_INCREMENT
    PRIMARY KEY,
    Customer VARCHAR(20) NOT NULL,
    OrderDate DATE NOT NULL
);
```

```
INSERT INTO Orders(Customer, OrderDate)
VALUES ('John', '20180501'), ('John', '20180502'), ('John', '20180503'),
    ('Jim', '20180501'), ('Jim', '20180503'), ('Jim', '20180504')
```

```
SELECT Customer,
    OrderDate,
    COUNT(*) AS NumOrders
FROM Orders AS O
GROUP BY Customer, OrderDate
WITH ROLLUP
UNION ALL -- Add the super aggregate rows for each OrderDate
SELECT NULL,
    OrderDate,
    COUNT(*) AS NumOrders
FROM Orders AS O
GROUP BY OrderDate
```

```
Customer  OrderDate   NumOrders
Jim       2018-05-01  1
Jim       2018-05-03  1
Jim       2018-05-04  1
Jim       NULL        3
John      2018-05-01  1
John      2018-05-02  1
John      2018-05-03  1
John      NULL        3
NULL      NULL        6
NULL      2018-05-01  2
NULL      2018-05-02  1
NULL      2018-05-03  2
NULL      2018-05-04  1
```

Rewrite SQL Server `GROUP BY ALL` for migration.

```
SELECT Customer,
    OrderDate,
    COUNT(*) AS NumOrders
FROM Orders AS O
WHERE OrderDate <= '20180503'
GROUP BY Customer, OrderDate
UNION ALL -- Add the empty groups
SELECT DISTINCT Customer,
    OrderDate,
    NULL
FROM Orders AS O
WHERE OrderDate > '20180503';
```

```
Customer  OrderDate   NumOrders
Jim       2018-05-01  1
Jim       2018-05-03  1
John      2018-05-01  1
John      2018-05-02  1
John      2018-05-03  1
Jim       2018-05-04  NULL
```

## Summary


Table of similarities, differences, and key migration considerations.


| SQL Server feature |  Aurora MySQL feature | Comments | 
| --- | --- | --- | 
|   `MAX`, `MIN`, `AVG`, `COUNT`, `COUNT_BIG`   |   `MAX`, `MIN`, `AVG`, `COUNT`   |  In Aurora MySQL, `COUNT` returns a `BIGINT` and is compatible with `COUNT` and `COUNT_BIG` in SQL Server.  | 
|   `CHECKSUM_AGG`   |  N/A  |  Use a loop to calculate checksums.  | 
|   `GROUPING`, `GROUPING_ID`   |  N/A  |  Reconsider query logic to avoid having NULL groups that are ambiguous with the super aggregates.  | 
|   `STDEV`, `STDEVP`, `VAR`, `VARP`   |   `STDDEV`, `STDDEV_POP`, `VARIANCE`, `VAR_POP`   |  Rewrite keywords only.  | 
|   `STRING_AGG`   |   `GROUP_CONCAT`   |  Rewrite syntax.  | 
|   `WITH ROLLUP`   |   `WITH ROLLUP`   |  Compatible  | 
|   `WITH CUBE`   |  N/A  |  Rewrite using UNION ALL.  | 
|   `ANSI CUBE` / `ROLLUP`   |  N/A  |  Rewrite using `WITH ROLLUP` and using `UNION ALL` queries.  | 
|   `GROUPING SETS`   |  N/A  |  Rewrite using `UNION ALL` queries.  | 
|  N/A  |  Non-aggregate columns in `HAVING`, `SELECT`, `ORDER BY`   |  Requires to turn off the `ONLY_FULL_GROUP_BY` SQL mode. Functional dependencies are evaluated by the engine.  | 

For more information, see [MySQL Handling of GROUP BY](https://dev.mysql.com/doc/refman/5.7/en/group-by-handling.html) in the *MySQL documentation*.

# Table JOIN for ANSI SQL


This topic provides reference content comparing table join functionality between Microsoft SQL Server 2019 and Amazon Aurora MySQL. You can understand the similarities and differences in join syntax and support between these two database systems.


| Feature compatibility |  AWS SCT / AWS DMS automation level |  AWS SCT action code index | Key differences | 
| --- | --- | --- | --- | 
|   ![\[Four star feature compatibility\]](http://docs.aws.amazon.com/dms/latest/sql-server-to-aurora-mysql-migration-playbook/images/pb-compatibility-4.png)   |   ![\[Four star automation level\]](http://docs.aws.amazon.com/dms/latest/sql-server-to-aurora-mysql-migration-playbook/images/pb-automation-4.png)   |   [Table Joins](chap-sql-server-aurora-mysql.tools.actioncode.md#chap-sql-server-aurora-mysql.tools.actioncode.tablejoins)   |  Basic syntax compatible. `FULL OUTER`, `APPLY`, and `ANSI SQL 89` outer joins will need to be rewritten.  | 

## SQL Server Usage


SQL Server supports the standard ANSI join types:
+  `<Set A> CROSS JOIN <Set B>` — Results in a Cartesian product of the two sets. Every `JOIN` starts as a Cartesian product.
+  `<Set A> INNER JOIN <Set B> ON <Join Condition>` — Filters the cartesian product to only the rows where the join predicate evaluates to `TRUE`.
+  `<Set A> LEFT OUTER JOIN <Set B> ON <Join Condition>` — Adds to the `INNER JOIN` all the rows from the reserved left set with NULL for all the columns that come from the right set.
+  `<Set A> RIGHT OUTER JOIN <Set B> ON <Join Condition>` — Adds to the `INNER JOIN` all the rows from the reserved right set with NULL for all the columns that come from the left set.
+  `<Set A> FULL OUTER JOIN <Set B> ON <Join Condition>` — Designates both sets as reserved and adds non matching rows from both, similar to a `LEFT OUTER JOIN` and a `RIGHT OUTER JOIN`.

### APPLY


SQL Server also supports the `APPLY` operator, which is somewhat similar to a join. However, `APPLY` operators enable the creation of a correlation between `<Set A>` and `<Set B>` such as that `<Set B>` may consist of a subquery, a `VALUES` row value constructor, or a table valued function that is evaluated for each row of `<Set A>` where the `<Set B>` query can reference columns from the current row in `<Set A>`. This functionality isn’t possible with any type of standard `JOIN` operator.

There are two `APPLY` types:
+  `<Set A> CROSS APPLY <Set B>` — Similar to a `CROSS JOIN` in the sense that every row from `<Set A>` is matched with every row from `<Set B>`.
+  `<Set A> OUTER APPLY <Set B>` — Similar to a `LEFT OUTER JOIN` in the sense that rows from `<Set A>` are returned even if the sub query for `<Set B>` produces an empty set. In that case, NULL is assigned to all columns of `<Set B>`.

### ANSI SQL 89 JOIN Syntax


Up until SQL Server version 2008 R2, SQL Server also supported the old style `JOIN` syntax including `LEFT` and` RIGHT OUTER JOIN`.

The ANSI syntax for a `CROSS JOIN` operator was to list the sets in the `FROM` clause using commas as separators. Consider the following example:

```
SELECT *
FROM Table1,
    Table2,
    Table3...
```

To perform an `INNER JOIN`, you only needed to add the `JOIN` predicate as part of the `WHERE` clause. Consider the following example:

```
SELECT *
FROM Table1,
    Table2
WHERE Table1.Column1 = Table2.Column1
```

Although the ANSI standard didn’t specify outer joins at the time, most RDBMS supported them in one way or another. T-SQL supported outer joins by adding an asterisk to the left or the right of equality sign of the join predicate to designate the reserved table. Consider the following example:

```
SELECT *
FROM Table1,
    Table2
WHERE Table1.Column1 *= Table2.Column1
```

To perform a `FULL OUTER JOIN`, asterisks were placed on both sides of the equality sign of the join predicate.

As of SQL Server 2008R2, outer joins using this syntax have been deprecated. For more information, see [Deprecated Database Engine Features in SQL Server 2008 R2](https://docs.microsoft.com/en-us/previous-versions/sql/sql-server-2008-r2/ms143729(v=sql.105)) in the *SQL Server documentation*.

**Note**  
Even though inner joins using the ANSI SQL 89 syntax are still supported, they are highly discouraged due to being notorious for introducing hard to catch programming bugs.

### Syntax


 **CROSS JOIN** 

```
FROM <Table Source 1>
    CROSS JOIN
    <Table Source 2>
```

```
-- ANSI 89
FROM <Table Source 1>,
    <Table Source 2>
```

 **INNER / OUTER JOIN** 

```
FROM <Table Source 1>
    [ { INNER | { { LEFT | RIGHT | FULL } [ OUTER ] } }] JOIN
    <Table Source 2>
    ON <JOIN Predicate>
```

```
-- ANSI 89
FROM <Table Source 1>,
    <Table Source 2>
WHERE <Join Predicate>
<Join Predicate>:: <Table Source 1 Expression> | = | *= | =* | *=* <Table Source 2 Expression>
```

 **APPLY** 

```
FROM <Table Source 1>
    { CROSS | OUTER } APPLY
    <Table Source 2>
<Table Source 2>:: <SELECT sub-query> | <Table Valued UDF> | <VALUES clause>
```

### Examples


Create the `Orders` and `Items` tables.

```
CREATE TABLE Items
(
Item VARCHAR(20) NOT NULL
    PRIMARY KEY
Category VARCHAR(20) NOT NULL,
Material VARCHAR(20) NOT NULL
);
```

```
INSERT INTO Items (Item, Category, Material)
VALUES
('M8 Bolt', 'Metric Bolts', 'Stainless Steel'),
('M8 Nut', 'Metric Nuts', 'Stainless Steel'),
('M8 Washer', 'Metric Washers', 'Stainless Steel'),
('3/8" Bolt', 'Imperial Bolts', 'Brass')
```

```
CREATE TABLE OrderItems
(
    OrderID INT NOT NULL,
    Item VARCHAR(20) NOT NULL
    REFERENCES Items(Item),
    Quantity SMALLINT NOT NULL,
    PRIMARY KEY(OrderID, Item)
);
```

```
INSERT INTO OrderItems (OrderID, Item, Quantity)
VALUES
(1, 'M8 Bolt', 100),
(2, 'M8 Nut', 100),
(3, 'M8 Washer', 200)
```

 **INNER JOIN** 

```
SELECT *
FROM Items AS I
    INNER JOIN
    OrderItems AS OI
    ON I.Item = OI.Item;

-- ANSI SQL 89
SELECT *
FROM Items AS I,
    OrderItems AS OI
WHERE I.Item = OI.Item;
```

 **LEFT OUTER JOIN** 

Find Items that were never ordered.

```
SELECT Item
FROM Items AS I
    LEFT OUTER JOIN
    OrderItems AS OI
    ON I.Item = OI.Item
WHERE OI.OrderID IS NULL;

-- ANSI SQL 89
SELECT Item
FROM
(
    SELECT I.Item, O.OrderID
    FROM Items AS I,
        OrderItems AS OI
    WHERE I.Item *= OI.Item
) AS LeftJoined
WHERE LeftJoined.OrderID IS NULL;
```

 **FULL OUTER JOIN** 

```
CREATE TABLE T1(Col1 INT, COl2 CHAR(2));
CREATE TABLE T2(Col1 INT, COl2 CHAR(2));

INSERT INTO T1 (Col1, Col2)
VALUES (1, 'A'), (2,'B');

INSERT INTO T2 (Col1, Col2)
VALUES (2,'BB'), (3,'CC');

SELECT *
FROM T1
    FULL OUTER JOIN
    T2
    ON T1.Col1 = T2.Col1;
```

```
Result:
Col1  COl2  Col1  COl2
1     A     NULL  NULL
2     B     2     BB
NULL NULL 3 CC
```

For more information, see [FROM clause plus JOIN, APPLY, PIVOT (Transact-SQL)](https://docs.microsoft.com/en-us/sql/t-sql/queries/from-transact-sql?view=sql-server-ver15) in the *SQL Server documentation*.

## MySQL Usage


 Amazon Aurora MySQL-Compatible Edition (Aurora MySQL) supports the following types of joins in the same way as SQL Server, except for `FULL OUTER JOIN`:
+  `<Set A> CROSS JOIN <Set B>` — Results in a Cartesian product of the two sets. Every `JOIN` starts as a Cartesian product.
+  `<Set A> INNER JOIN <Set B> ON <Join Condition>` — Filters the Cartesian product to only the rows where the join predicate evaluates to `TRUE`.
+  `<Set A> LEFT OUTER JOIN <Set B> ON <Join Condition>` — Adds to the `INNER JOIN` all the rows from the reserved left set with NULL for all the columns that come from the right set.
+  `<Set A> RIGHT OUTER JOIN <Set B> ON <Join Condition>` — Adds to the `INNER JOIN` all the rows from the reserved right set with NULL for all the columns that come from the left set.

In addition, Aurora MySQL supports the following join types not supported by SQL Server:
+  `<Set A> NATURAL [INNER | LEFT OUTER | RIGHT OUTER ] JOIN <Set B>` — Implicitly assumes that the join predicate consists of all columns with the same name from `<Set A>` and `<Set B>`.
+  `<Set A> STRAIGHT_JOIN <Set B>` — Forces `<Set A>` to be read before `<Set B>` and is used as an optimizer hint.

 Aurora MySQL also supports the `USING` clause as an alternative to the `ON` clause. The `USING` clause consists of a list of comma separated columns that must appear in both tables. The join predicate is the equivalent of an `AND` logical operator for equality predicates of each column. For example, the following two joins are equivalent:

```
FROM Table1
    INNER JOIN
    Table2
    ON Table1.Column1 = Table2.column1;
```

```
FROM Table1
    INNER JOIN
    Table2
    USING (Column1);
```

If `Column1` is the only column with a common name between `Table1` and `Table2`, the following statement is also equivalent:

```
FROM Table1
    NATURAL JOIN
    Table2
```

**Note**  
 Aurora MySQL supports the ANSI SQL 89 syntax for joins using commas in the `FROM` clause, but only for inner joins.

**Note**  
 Aurora MySQL supports neither `APPLY` nor the equivalent `LATERAL JOIN` used by some other database engines.

### Syntax


```
FROM
    <Table Source 1> CROSS JOIN <Table Source 2>
    | <Table Source 1> INNER JOIN <Table Source 2>
        ON <Join Predicate> | USING (Equality Comparison Column List)
    | <Table Source 1> {LEFT|RIGHT} [OUTER] JOIN <Table Source 2>
        ON <Join Predicate> | USING (Equality Comparison Column List)
    | <Table Source 1> NATURAL [INNER | {LEFT|RIGHT} [OUTER]] JOIN <Table Source 2>
    | <Table Source 1> STRAIGHT_JOIN <Table Source 2>
    | <Table Source 1> STRAIGHT_JOIN <Table Source 2>
        ON <Join Predicate>
```

### Migration Considerations


For most joins, the syntax should be equivalent and no rewrites should be needed.
+  `CROSS JOIN` using either ANSI SQL 89 or ANSI SQL 92 syntax.
+  `INNER JOIN` using either ANSI SQL 89 or ANSI SQL 92 syntax.
+  `OUTER JOIN` using the ANSI SQL 92 syntax only.

 `FULL OUTER JOIN` and `OUTER JOIN` using the pre-ANSI SQL 92 syntax aren’t supported, but they can be easily worked around.

 `CROSS APPLY` and `OUTER APPLY` aren’t supported and need to be rewritten.

### Examples


Create the `Orders` and `Items` tables.

```
CREATE TABLE Items
(
    Item VARCHAR(20) NOT NULL
    PRIMARY KEY
    Category VARCHAR(20) NOT NULL,
    Material VARCHAR(20) NOT NULL
);
```

```
INSERT INTO Items (Item, Category, Material)
VALUES
('M8 Bolt', 'Metric Bolts', 'Stainless Steel'),
('M8 Nut', 'Metric Nuts', 'Stainless Steel'),
('M8 Washer', 'Metric Washers', 'Stainless Steel'),
('3/8" Bolt', 'Imperial Bolts', 'Brass')
```

```
CREATE TABLE OrderItems
(
    OrderID INT NOT NULL,
    Item VARCHAR(20) NOT NULL
    REFERENCES Items(Item),
    Quantity SMALLINT NOT NULL,
    PRIMARY KEY(OrderID, Item)
);
```

```
INSERT INTO OrderItems (OrderID, Item, Quantity)
VALUES
(1, 'M8 Bolt', 100),
(2, 'M8 Nut', 100),
(3, 'M8 Washer', 200)
```

 **INNER JOIN and OUTER JOIN** 

```
SELECT *
FROM Items AS I
    INNER JOIN
    OrderItems AS OI
    ON I.Item = OI.Item;

-- ANSI SQL 89
SELECT *
FROM Items AS I,
    Orders AS O
WHERE I.Item = OI.Item;
```

 **LEFT OUTER JOIN** 

```
SELECT Item
FROM Items AS I
    LEFT OUTER JOIN
    OrderItems AS OI
    ON I.Item = OI.Item
WHERE OI.OrderID IS NULL;
```

 **Rewrite for FULL OUTER JOIN** 

```
CREATE TABLE T1(Col1 INT, COl2 CHAR(2));
CREATE TABLE T2(Col1 INT, COl2 CHAR(2));

INSERT INTO T1 (Col1, Col2)
VALUES (1, 'A'), (2,'B');

INSERT INTO T2 (Col1, Col2)
VALUES (2,'BB'), (3,'CC');

SELECT *
FROM T1
    LEFT OUTER JOIN
    T2
    ON T1.Col1 = T2.Col1
UNION ALL
SELECT NULL, NULL, Col1, Col2
FROM T2
WHERE Col1 NOT IN (SELECT Col1 FROM T1);
```

```
Result:
Col1  COl2  Col1  COl2
1     A     NULL  NULL
2     B     2     BB
NULL  NULL  3     CC
```

## Summary


Table of similarities, differences, and key migration considerations.


| SQL Server |  Aurora MySQL  | Comments | 
| --- | --- | --- | 
|   `INNER JOIN` with `ON` clause or commas  |  Supported  |  | 
|   `OUTER JOIN` with `ON` clause  |  Supported  |  | 
|   `OUTER JOIN` with commas  |  Not supported  |  Requires T-SQL rewrite post SQL Server 2008 R2.  | 
|   `CROSS JOIN` or using commas  |  Supported  |  | 
|   `CROSS APPLY` and `OUTER APPLY`   |  Not Supported  |  Rewrite required.  | 
|  Not Supported  |   `NATURAL JOIN`   |  Not recommended, may cause unexpected issues if table structure changes.  | 
|  Not Supported  |   `STRAIGHT_JOIN`   |  | 
|  Not Supported  |   `USING` clause  |  | 

For more information, see [JOIN Clause](https://dev.mysql.com/doc/refman/5.7/en/join.html) in the *MySQL documentation*.

# Views for ANSI SQL


This topic provides reference information about views in Microsoft SQL Server and Amazon Aurora MySQL, comparing their features and usage. You can use this content to understand the similarities and differences between views in these two database systems, which is valuable for planning and executing migrations from SQL Server to Aurora MySQL.


| Feature compatibility |  AWS SCT / AWS DMS automation level |  AWS SCT action code index | Key differences | 
| --- | --- | --- | --- | 
|   ![\[Four star feature compatibility\]](http://docs.aws.amazon.com/dms/latest/sql-server-to-aurora-mysql-migration-playbook/images/pb-compatibility-4.png)   |   ![\[Four star automation level\]](http://docs.aws.amazon.com/dms/latest/sql-server-to-aurora-mysql-migration-playbook/images/pb-automation-4.png)   |  N/A  |  Minor syntax and handling differences. Indexes, triggers, and temporary views aren’t supported.  | 

## SQL Server Usage


Views are schema objects that provide stored definitions for virtual tables. Similar to tables, views are data sets with uniquely named columns and rows. With the exception of indexed views, view objects don’t store data. They consist only of a query definition and are reevaluated for each invocation.

Views are used as abstraction layers and security filters for the underlying tables. They can `JOIN` and `UNION` data from multiple source tables and use aggregates, window functions, and other SQL features as long as the result is a semi-proper set with uniquely identifiable columns and no order to the rows. You can use distributed views to query other databases and data sources using linked servers.

As an abstraction layer, a view can decouple application code from the database schema. The underlying tables can be changed without the need to modify the application code, as long as the expected results of the view don’t change. You can use this approach to provide backward compatible views of data.

As a security mechanism, a view can screen and filter source table data. You can perform permission management at the view level without explicit permissions to the base objects, provided the ownership chain is maintained.

For more information, see [Overview of SQL Server Security](https://docs.microsoft.com/en-us/previous-versions/dotnet/framework/data/adonet/sql/overview-of-sql-server-security?view=sql-server-ver15) in the *SQL Server documentation*.

View definitions are evaluated when they are created and aren’t affected by subsequent changes to the underlying tables.

For example, a view that uses `SELECT *` doesn’t display columns that were added later to the base table. Similarly, if a column was dropped from the base table, invoking the view results in an error. Use the `SCHEMABINDING` option to prevent changes to base objects.

### Modifying Data Through Views


Updatable views can both `SELECT` and modify data. For a view to be updatable, the following conditions must be met:
+ The DML targets only one base table.
+ Columns being modified must be directly referenced from the underlying base tables. Computed columns, set operators, functions, aggregates, or any other expressions aren’t permitted.
+ If a view is created with the `CHECK OPTION`, rows being updated can’t be filtered out of the view definition as the result of the update.

### Special View Types


SQL Server also provides three types of special views:
+  **Indexed views** (also known as materialized views or persisted views) are standard views that have been evaluated and persisted in a unique clustered index, much like a normal clustered primary key table. Each time the source data changes, SQL Server re-evaluates the indexed views automatically and updates the indexed view. Indexed views are typically used as a means to optimize performance by pre-processing operators such as aggregations, joins, and others. Queries needing this pre-processing don’t have to wait for it to be reevaluated on every query run.
+  **Partitioned views** are views that rejoin horizontally partitioned data sets from multiple underlying tables, each containing only a subset of the data. The view uses a UNION ALL query where the underlying tables can reside locally or in other databases (or even other servers). These types of views are called Distributed Partitioned Views (DPV).
+  **System views** are used to access server and object meta data. SQL Server also supports a set of standard `INFORMATION_SCHEMA` views for accessing object meta data.

### Syntax


```
CREATE [OR ALTER] VIEW [<Schema Name>.] <View Name> [(<Column Aliases> ])]
[WITH [ENCRYPTION][SCHEMABINDING][VIEW_METADATA]]
AS <SELECT Query>
[WITH CHECK OPTION][;]
```

### Examples


Create a view that aggregates items for each customer.

```
CREATE TABLE Orders
(
    OrderID INT NOT NULL PRIMARY KEY,
    OrderDate DATETIME NOT NULL
    DEFAULT GETDATE()
);
```

```
CREATE TABLE OrderItems
(
    OrderID INT NOT NULL
        REFERENCES Orders(OrderID),
    Item VARCHAR(20) NOT NULL,
    Quantity SMALLINT NOT NULL,
    PRIMARY KEY(OrderID, Item)
);
```

```
CREATE VIEW SalesView
AS
SELECT O.Customer,
    OI.Product,
    SUM(CAST(OI.Quantity AS BIGINT)) AS TotalItemsBought
FROM Orders AS O
    INNER JOIN
    OrderItems AS OI
        ON O.OrderID = OI.OrderID;
```

Create an indexed view that pre-aggregates items for each customer.

```
CREATE VIEW SalesViewIndexed
AS
SELECT O.Customer,
    OI.Product,
    SUM_BIG(OI.Quantity) AS TotalItemsBought
FROM Orders AS O
    INNER JOIN
    OrderItems AS OI
        ON O.OrderID = OI.OrderID;
```

```
CREATE UNIQUE CLUSTERED INDEX IDX_SalesView
ON SalesViewIndexed (Customer, Product);
```

Create a partitioned view.

```
CREATE VIEW dbo.PartitioneView
WITH SCHEMABINDING
AS
SELECT *
FROM Table1
UNION ALL
SELECT *
FROM Table2
UNION ALL
SELECT *
FROM Table3
```

For more information, see [Views](https://docs.microsoft.com/en-us/sql/relational-databases/views/views?view=sql-server-ver15), [Modify Data Through a View](https://docs.microsoft.com/en-us/sql/relational-databases/views/modify-data-through-a-view?view=sql-server-ver15), and [CREATE VIEW (Transact-SQL)](https://docs.microsoft.com/en-us/sql/t-sql/statements/create-view-transact-sql?view=sql-server-ver15) in the *SQL Server documentation*.

## MySQL Usage


Similar to SQL Server, Amazon Aurora MySQL-Compatible Edition (Aurora MySQL) views consist of a `SELECT` statement that can references base tables and other views.

 Aurora MySQL views are created using the `CREATE VIEW` statement. The `SELECT` statement comprising the definition of the view is evaluated only when the view is created and isn’t affected by subsequent changes to the underlying base tables.

 Aurora MySQL views have the following restrictions:
+ A view can’t reference system variables or user-defined variables.
+ When used within a stored procedure or function, the `SELECT` statement can’t reference parameters or local variables.
+ A view can’t reference prepared statement parameters.
+ All objects referenced by a view must exist when the view is created. If an underlying table or view is later dropped, invoking the view results in an error.
+ Views can’t reference `TEMPORARY` tables.
+  `TEMPORARY` views aren’t supported.
+ Views don’t support triggers.
+ Aliases are limited to a maximum length of 64 characters (not the typical 256 maximum alias length).

 Aurora MySQL provides additional properties not available in SQL Server:
+ The `ALGORITHM` clause is a fixed hint that affects the way the MySQL query processor handles the view physical evaluation operator.

  The MERGE algorithm uses a dynamic approach where the definition of the view is merged to the outer query.

  The TEMPTABLE algorithm materializes the view data internally. For more information, see [View Processing Algorithms](https://dev.mysql.com/doc/refman/5.7/en/view-algorithms.html) in the *MySQL documentation*.
+ The `DEFINER` and `SQL SECURITY` clauses can be used to specify a specific security context for checking view permissions at run time.

Similar to SQL Server, Aurora MySQL supports updatable views and the ANSI standard `CHECK OPTION` to limit inserts and updates to rows referenced by the view.

The `LOCAL` and `CASCADED` keywords are used to determine the scope of violation checks. When using the `LOCAL` keyword, the `CHECK OPTION` is evaluated only for the view being created. `CASCADED` causes evaluation of referenced views. The default is `CASCADED`.

In general, only views having a one-to-one relationship between the source rows and the exposed rows are updatable.

Adding the following constructs prevents modification of data:
+ Aggregate functions.
+  `DISTINCT`.
+  `GROUP BY`.
+  `HAVING`.
+  `UNION` or `UNION ALL`.
+ Subquery in the select list.
+ Certain joins.
+ Reference to a non-updatable view.
+ Subquery in the `WHERE` clause that refers to a table in the `FROM` clause.
+  `ALGORITHM = TEMPTABLE`.
+ Multiple references to any column of a base table.

A view must have unique column names. Column aliases are derived from the base tables or explicitly specified in the `SELECT` statement of column definition list. `ORDER BY` is permitted in Aurora MySQL, but ignored if the outer query has an `ORDER BY` clause.

 Aurora MySQL assesses data access privileges as follows:
+ The user creating a view must have all required privileges to use the top-level objects referenced by the view.

  For example, for a view referencing table columns, the user must have privilege for each column in any clause of the view definition.
+ If the view definition references a stored function, only the privileges needed to invoke the function are checked. The privileges required at run time can be checked only at run time because different invocations may use different run paths within the function code.
+ The user referencing a view must have appropriate `SELECT`, `INSERT`, `UPDATE`, or `DELETE` privileges, as with a normal table.
+ When a view is referenced, privileges for all objects accessed by the view are evaluated using the privileges for the view `DEFINER` account, or the invoker, depending on whether `SQL SECURITY` is set to `DEFINER` or `INVOKER`.
+ When a view invocation triggers the call of a stored function, privileges are checked for statements that run within the function based on the function’s `SQL SECURITY` setting. For functions where the security is set to `DEFINER`, the function runs with the privileges of the `DEFINER` account. For functions where it is set to `INVOKER`, the function runs with the privileges determined by the view’s `SQL SECURITY` setting as described before.

### Syntax


```
CREATE [OR REPLACE]
    [ALGORITHM = {UNDEFINED | MERGE | TEMPTABLE}]
    [DEFINER = { <User> | CURRENT_USER }]
    [SQL SECURITY { DEFINER | INVOKER }]
    VIEW <View Name> [(<Column List>)]
    AS <SELECT Statement>
    [WITH [CASCADED | LOCAL] CHECK OPTION];
```

### Migration Considerations


The basic syntax for views is very similar to SQL Server and is ANSI compliant. Code migration should be straightforward.

 Aurora MySQL doesn’t support triggers on views. In SQL Server, `INSTEAD OF` triggers are supported. For more information, see [Triggers](chap-sql-server-aurora-mysql.tsql.triggers.md).

In Aurora MySQL, `ORDER BY` is permitted in a view definition. It is ignored if the outer `SELECT` has its own `ORDER BY`. This behavior is different than SQL Server where `ORDER BY` is allowed only for `TOP` filtering. The actual order of the rows isn’t guaranteed.

Security context is explicit in Aurora MySQL, which isn’t supported in SQL Server. Use security contexts to work around the lack of ownership-chain permission paths.

Unlike SQL Server, a view in Aurora MySQL can invoke functions, which in turn may introduce a change to the database. For more information, see [User-Defined Functions](chap-sql-server-aurora-mysql.tsql.udf.md).

The `WITH CHECK` option in Aurora MySQL can be scoped to `LOCAL` or `CASCADED`. The `CASCADED` causes the `CHECK` option to be evaluated for nested views referenced in the parent.

Indexed views aren’t supported in Aurora MySQL. Consider using application maintained tables instead. Change application code to reference those tables instead of the base table.

### Examples


Create and populate the `Invoices` table.

```
CREATE TABLE Invoices(
InvoiceID INT NOT NULL PRIMARY KEY,
Customer VARCHAR(20) NOT NULL,
TotalAmount DECIMAL(9,2) NOT NULL);

INSERT INTO Invoices (InvoiceID,Customer,TotalAmount)
VALUES
(1, 'John', 1400.23),
(2, 'Jeff', 245.00),
(3, 'James', 677.22);
```

Create the `TotalSales` view.

```
CREATE VIEW TotalSales
AS
SELECT Customer,
    SUM(TotalAmount) AS CustomerTotalAmount
GROUP BY Customer;
```

Invoke the view.

```
SELECT * FROM TotalSales
ORDER BY CustomerTotalAmount DESC;

Customer  CustomerTotalAmount
John      1400.23
James     677.22
Jeff      245.00
```

## Summary



| Feature | SQL Server |  Aurora MySQL  | Comments | 
| --- | --- | --- | --- | 
|  Indexed views  |  Supported  |  N/A  |  | 
|  Partitioned views  |  Supported  |  N/A  |  You can create partitioned views in the same way as SQL Server, they won’t benefit from the internal optimizations such as partition elimination.  | 
|  Updateable views  |  Supported  |  Supported  |  | 
|  Prevent schema conflicts  |   `SCHEMABINDING` option  |  |  | 
|  Triggers on views  |   `INSTEAD OF`   |  N/A  |  For more information, see [Triggers](chap-sql-server-aurora-mysql.tsql.triggers.md).  | 
|  Temporary views  |   `CREATE VIEW #View…​`   |  N/A  |  | 
|  Refresh view definition  |   `sp_refreshview` / `ALTER VIEW`   |   `ALTER VIEW`   |  | 

For more information, see [CREATE VIEW Statement](https://dev.mysql.com/doc/refman/5.7/en/create-view.html), [Restrictions on Views](https://dev.mysql.com/doc/refman/5.7/en/view-restrictions.html), and [Updatable and Insertable Views](https://dev.mysql.com/doc/refman/5.7/en/view-updatability.html) in the *MySQL documentation*.

# Window functions for ANSI SQL


This topic provides reference information about window functions in Microsoft SQL Server and their compatibility with Amazon Aurora MySQL. You can understand the differences in support for window functions between SQL Server and Aurora MySQL, which is crucial for planning database migrations.


| Feature compatibility |  AWS SCT / AWS DMS automation level |  AWS SCT action code index | Key differences | 
| --- | --- | --- | --- | 
|   ![\[Two star feature compatibility\]](http://docs.aws.amazon.com/dms/latest/sql-server-to-aurora-mysql-migration-playbook/images/pb-compatibility-2.png)   |   ![\[No automation\]](http://docs.aws.amazon.com/dms/latest/sql-server-to-aurora-mysql-migration-playbook/images/pb-automation-0.png)   |   [Window Functions](chap-sql-server-aurora-mysql.tools.actioncode.md#chap-sql-server-aurora-mysql.tools.actioncode.windowfunctions)   |  Rewrite window functions to use alternative SQL syntax.  | 

## SQL Server Usage


Window functions use an `OVER` clause to define the window and frame for a data set to be processed. They are part of the ANSI standard and are typically compatible among various SQL dialects. However, most database engines don’t yet support the full ANSI specification.

Window functions are a relatively new, advanced, and efficient T-SQL programming tool. They are highly utilized by developers to solve numerous programming challenges.

SQL Server currently supports the following window functions:


| Window function category | Examples | 
| --- | --- | 
|  Ranking functions  |   `ROW_NUMBER`, `RANK`, `DENSE_RANK`, and `NTILE`   | 
|  Aggregate functions  |   `AVG`, `MIN`, `MAX`, `SUM`, `COUNT`, `COUNT_BIG`, `VAR`, `STDEV`, `STDEVP`, `STRING_AGG`, `GROUPING`, `GROUPING_ID`, `VAR`, `VARP`, and `CHECKSUM_AGG`   | 
|  Analytic functions  |   `LAG`, `LEAD`, `FIRST_Value`, `LAST_VALUE`, `PERCENT_RANK`, `PERCENTILE_CONT`, `PERCENTILE_DISC`, and `CUME_DIST`   | 
|  Other functions  |   `NEXT_VALUE_FOR`. For more information, see [Identity and Sequences](chap-sql-server-aurora-mysql.tsql.identitysequences.md).  | 

### Syntax


```
<Function()>
OVER
(
[ <PARTITION BY clause> ]
[ <ORDER BY clause> ]
[ <ROW or RANGE clause> ]
)
```

### Examples


Create and populate the `OrderItems` table.

```
CREATE TABLE OrderItems
(
    OrderID INT NOT NULL,
    Item VARCHAR(20) NOT NULL,
    Quantity SMALLINT NOT NULL,
    PRIMARY KEY(OrderID, Item)
);
```

```
INSERT INTO OrderItems (OrderID, Item, Quantity)
VALUES
(1, 'M8 Bolt', 100),
(2, 'M8 Nut', 100),
(3, 'M8 Washer', 200),
(3, 'M6 Locking Nut', 300);
```

Use a window ranking function to rank items based on the ordered quantity.

```
SELECT Item,
    Quantity,
    RANK() OVER(ORDER BY Quantity) AS QtyRank
FROM OrderItems;
```

```
Item            Quantity  QtyRank
M8 Bolt         100       1
M8 Nut          100       1
M8 Washer       200       3
M6 Locking Nut  300       4
```

Use a partitioned window aggregate function to calculate the total quantity for each order (without using a `GROUP BY` clause).

```
SELECT Item,
    Quantity,
    OrderID,
    SUM(Quantity)
    OVER (PARTITION BY OrderID) AS TotalOrderQty
FROM OrderItems;
```

```
Item            Quantity  OrderID  TotalOrderQty
M8 Bolt         100       1        100
M8 Nut          100       2        100
M6 Locking Nut  300       3        500
M8 Washer       200       3        500
```

Use an analytic `LEAD` function to get the next largest quantity for the order.

```
SELECT Item,
    Quantity,
    OrderID,
    LEAD(Quantity)
    OVER (PARTITION BY OrderID ORDER BY Quantity) AS NextQtyOrder
FROM OrderItems;
```

```
Item            Quantity  OrderID  NextQtyOrder
M8 Bolt         100       1        NULL
M8 Nut          100       2        NULL
M8 Washer       200       3        300
M6 Locking Nut  300       3        NULL
```

For more information, see [SELECT - OVER Clause (Transact-SQL)](https://docs.microsoft.com/en-us/sql/t-sql/queries/select-over-clause-transact-sql?view=sql-server-ver15) in the *SQL Server documentation*.

## MySQL Usage


 Aurora MySQL version 5.7 doesn’t support Window functions.

**Note**  
 Amazon Relational Database Service (Amazon RDS) for MySQL 8 supports window functions that for each row from a query perform a calculation using rows related to that row. These include functions such as `RANK()`, `LAG()`, and `NTILE()`. In addition, several existing aggregate functions now can be used as window functions, for example, `SUM()` and `AVG()`. For more information, see [Window Functions](https://dev.mysql.com/doc/refman/8.0/en/window-functions.html) in the *MySQL documentation*.

### Migration Considerations


As a temporary workaround, rewrite the code to remove the use of Window functions, and revert to using more traditional SQL code solutions.

In most cases, you can find an equivalent SQL query, although it may be less optimal in terms of performance, simplicity, and readability.

See the following examples for migrating Window functions to code that uses correlated subqueries.

**Note**  
You may want to archive the original code and then reuse it in the future when Aurora MySQL is upgraded to version 8. The documentation for version 8 indicates the Window function syntax is ANSI compliant and will be compatible with SQL Server T-SQL syntax.

For more information, see [Window Functions](https://dev.mysql.com/doc/refman/8.0/en/window-functions.html) in the *MySQL documentation*.

### Examples


The following examples demonstrate ANSI SQL compliant subquery solutions as replacements for the two example queries from the previous SQL Server section.

Create and populate an OrderItems table.

```
CREATE TABLE OrderItems
(
    OrderID INT NOT NULL,
    Item VARCHAR(20) NOT NULL,
    Quantity SMALLINT NOT NULL,
    PRIMARY KEY(OrderID, Item)
);
```

```
INSERT INTO OrderItems (OrderID, Item, Quantity)
VALUES
(1, 'M8 Bolt', 100),
(2, 'M8 Nut', 100),
(3, 'M8 Washer', 200),
(3, 'M6 Locking Nut', 300);
```

Rank items based on ordered quantity. The following example is a workaround for the window ranking function.

```
SELECT Item,
Quantity,
(
    SELECT COUNT(*)
    FROM OrderItems AS OI2
    WHERE OI.Quantity > OI2.Quantity) + 1
    AS QtyRank
FROM OrderItems AS OI;
```

```
Item            Quantity  QtyRank
M8 Bolt         100       1
M8 Nut          100       1
M6 Locking Nut  300       4
M8 Washer       200       3
```

Calculate the grand total. The following example is a workaround for a partitioned Window aggregate function.

```
SELECT Item,
Quantity,
OrderID,
(
    SELECT SUM(Quantity)
    FROM OrderItems AS OI2
    WHERE OI2.OrderID = OI.OrderID)
    AS TotalOrderQty
FROM OrderItems AS OI;
```

```
Item            Quantity  OrderID  TotalOrderQty
M8 Bolt         100       1        100
M8 Nut          100       2        100
M6 Locking Nut  300       3        500
M8 Washer       200       3        500
```

Get the next largest quantity for the order. The following example is a workaround for the `LEAD` analytical function.

```
SELECT Item,
Quantity,
OrderID,
(
    SELECT Quantity
    FROM OrderItems AS OI2
    WHERE OI.OrderID = OI2.OrderID
        AND
        OI2.Quantity > OI.Quantity
    ORDER BY Quantity
        LIMIT 1
    )
    AS NextQtyOrder
FROM OrderItems AS OI
```

```
Item            Quantity  OrderID  NextQtyOrder
M8 Bolt         100       1        [NULL]
M8 Nut          100       2        [NULL]
M6 Locking Nut  300       3        [NULL]
M8 Washer       200       3        300
```

## Summary



| SQL Server |  Aurora MySQL  | Comments | 
| --- | --- | --- | 
|  Window functions and `OVER` clause.  |  Not supported yet.  |  Convert code to use traditional SQL techniques such as correlated sub queries.  | 

For more information, see [Window Function Descriptions](https://dev.mysql.com/doc/refman/8.0/en/window-function-descriptions.html) in the *MySQL documentation*.

# Temporary tables for ANSI SQL


This topic provides reference content for temporary table functionality between Microsoft SQL Server and MySQL, specifically in the context of migrating from SQL Server 2019 to Amazon Aurora MySQL. You’ll gain insight into how temporary tables are created, stored, and managed in both database systems.


| Feature compatibility |  AWS SCT / AWS DMS automation level |  AWS SCT action code index | Key differences | 
| --- | --- | --- | --- | 
|   ![\[Three star feature compatibility\]](http://docs.aws.amazon.com/dms/latest/sql-server-to-aurora-mysql-migration-playbook/images/pb-compatibility-3.png)   |   ![\[Three star automation level\]](http://docs.aws.amazon.com/dms/latest/sql-server-to-aurora-mysql-migration-playbook/images/pb-automation-3.png)   |  N/A  |  N/A  | 

## SQL Server Usage


SQL Server temporary tables are stored in the `tempdb` system database. There are two types of temporary tables: local and global. They differ from each other in their names, their visibility, and their availability. Local temporary tables have a single number sign `#` as the first character of their names; they are visible only to the current connection for the user, and they are deleted when the user disconnects from the instance of SQL Server.

Global temporary tables have two number signs `##` as the first characters of their names; they are visible to any user after they are created, and they are deleted when all users referencing the table disconnect from the instance of SQL Server.

```
CREATE TABLE #MyTempTable (col1 INT PRIMARY KEY);
```

For more information, see [Tables](https://docs.microsoft.com/en-us/sql/relational-databases/tables/tables?view=sql-server-ver15) and [Temporary Tables](https://docs.microsoft.com/en-us/sql/t-sql/statements/create-table-transact-sql?redirectedfrom=MSDN&view=sql-server-ver15#temporary-tables) in the *SQL Server documentation*.

## MySQL Usage


In MySQL, the table structure (DDL) of temporary tables isn’t stored in the database. When a session ends, the temporary table is dropped.
+  **Session-Specific** — In MySQL, each session is required to create its own temporary tables. Each session can create its own private temporary tables using identical table names.
+  **In SQL Server**, the default behavior when the `ON COMMIT` clause is omitted is `ON COMMIT DELETE ROWS`. In MySQL, the default is `ON COMMIT PRESERVE ROWS` and it can’t be changed.

**Note**  
In Amazon Relational Database Service (Amazon RDS) for MySQL 8.0.13, user-created temporary tables and internal temporary tables created by the optimizer are stored in session temporary tablespaces that are allocated to a session from a pool of temporary tablespaces. When a session disconnects its temporary tablespaces are truncated and released back to the pool. In previous releases temporary tables were created in the `ibtmp1` global temporary tablespace which did not return disk space to the operating system after temporary tables were dropped. The `innodb_temp_tablespaces_dir` variable defines the location where session temporary tablespaces are created. The default location is the `#innodb_temp` directory in the data directory. The `INNODB_SESSION_TEMP_TABLESPACES` table provides metadata about session temporary tablespaces. The `ibtmp1` global temporary tablespace now stores rollback segments for changes made to user-created temporary tables.

### Examples


```
CREATE TEMPORARY TABLE EMP_TEMP (
    EMP_ID INT PRIMARY KEY,
    EMP_FULL_NAME VARCHAR(60) NOT NULL,
    AVG_SALARY INT NOT NULL1;
```

## Summary



| Feature | SQL Server |  Aurora MySQL  | 
| --- | --- | --- | 
|  Semantic  |  Global temporary table  |  Temporary table  | 
|  Create table  |   `CREATE GLOBAL TEMPORARY…`   |   `CREATE TEMPORARY…`   | 
|  Accessible from multiple sessions  |  Yes  |  No  | 
|  Temporary table DDL persist after session end or database restart user-managed datafiles  |  Yes  |  No (dropped at the end of the session)  | 
|  Create index support  |  Yes  |  Yes  | 
|  Foreign key support  |  Yes  |  Yes  | 
|   `ON COMMIT` default  |   `COMMIT DELETE ROWS`   |   `ON COMMIT PRESERVE ROWS`   | 
|   `ON COMMIT PRESERVE ROWS`   |  Yes  |  Yes  | 
|   `ON COMMIT DELETE ROWS`   |  Yes  |  Yes  | 
|  Alter table support  |  Yes  |  Yes  | 
|  Gather statistics  |   `dbms_stats.gather_table_stats`   |   `ANALYZE`   | 
|  Oracle 12c `GLOBAL_TEMP_TABLE_STATS`   |   `dbms_stats.set_table_prefs`   |   `ANALYZE`   | 

For more information, see [CREATE TEMPORARY TABLE Statement](https://dev.mysql.com/doc/refman/5.7/en/create-temporary-table.html) in the *MySQL documentation*.