Database – Create Table Syntax in different Databases

Preface

Wanted to cover a couple of databases and show by example how the “Create table” implementation differ.

 

Databases

Couple of unfortunates:

  1. Will only cover two of the many database flavors
  2. Syntactically create table is very broad and so will not cover the many options

We will only cover what it took to convert a table that was originally in MS SQL Server to MySQL.

 

MS SQL Server




SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
SET ANSI_PADDING ON
GO

CREATE TABLE [Admin].[ErrorLog]
(
	[id] [bigint] IDENTITY(1,1) NOT NULL,
	[record_created] [datetime] NOT NULL 
            CONSTRAINT [DF_ErrorLog_record_created]  DEFAULT (getdate()),
	[exceptionType] [varchar](100) NULL,
	[message] [varchar](2000) NULL,
	[statement] [varchar](max) NULL,
	[stack] [varchar](max) NULL,
	[customData] [varchar](max) NULL,
	[userID] [varchar](300) NULL,
	[machineName] [varchar](100) NULL,
	[appID] [varchar](50) NULL
	, CONSTRAINT [PK_ErrorLog] PRIMARY KEY CLUSTERED 
	(
	    [id] ASC
	) ON [PRIMARY]
	
) ON [PRIMARY]

GO

SET ANSI_PADDING OFF
GO

CREATE NONCLUSTERED INDEX [INDX_DBA_RECORDCREATED] 
ON [Admin].[ErrorLog] 
(
	[record_created] ASC
)
INCLUDE 
( 
	[message],
	[statement],
	[id],
	[appID],
	[machineName]
)  
ON [Indexes]
GO


 

MySQL



/*
   Drop Table ErrorLog
	a) If exists
*/
DROP TABLE IF EXISTS ErrorLog
;

/*
	Create Table ErrorLog
		a) If not exists
*/
CREATE TABLE IF NOT EXISTS ErrorLog
(
	`id` bigint AUTO_INCREMENT NOT NULL,
	`record_created` datetime NOT NULL
       DEFAULT NOW(),
	`exceptionType` varchar(100) NULL,
	`message` varchar(2000) NULL,
	`statement` Longtext NULL,
	`stack` Longtext NULL,
	`customData` Longtext NULL,
	`userID` varchar(300) NULL,
	`machineName` varchar(100) NULL,
	`appID` varchar(50) NULL
        , CONSTRAINT `PK_ErrorLog` PRIMARY KEY 
        (
	    `id` ASC
       ) 
)
engine = InnoDB
;

/*
	Add Insert Trigger
       Set Record_Created to current date
*/
CREATE TRIGGER `TR_ErrorLog_Insert` 
BEFORE INSERT ON  `ErrorLog` 
FOR EACH ROW 
SET NEW.`record_created` = NOW()
;


/*
	Add Index INDX_DBA_RECORDCREATED
       On record_created
*/
CREATE INDEX `INDX_DBA_RECORDCREATED` 
ON ErrorLog
(
	`record_created` ASC
)
;


commit;

Comparison

MySQL

  1. Schema
    • Does not support Schema in the way MS SQL does
      • MSSQL has Database\Schema\Table
      • MySQL has Database or Schema \ Table
  2. Filegroups
    • Filegroups and Files
      • MSSQL
        • Targeted Filegroups can be explicitly mentioned when creating Tables and Indexes
      • MySQL
        • InnoDB File-Per-Table Tablespaces
          Link
          Historically, all InnoDB tables and indexes were stored in the system tablespace. This monolithic approach was targeted at machines dedicated entirely to database processing, with carefully planned data growth, where any disk storage allocated to MySQL would never be needed for other purposes. InnoDB’s file-per-table tablespace feature provides a more flexible alternative, where each InnoDB table and its indexes are stored in a separate .ibd data file. Each such .ibd data file represents an individual tablespace. This feature is controlled by the innodb_file_per_table configuration option, which is enabled by default in MySQL 5.6.6 and higher.
  3.  Index
    • Included columns
      • MSSQL has included Columns
      • MySQL does not not
  4. Storage Engine
    • MySQL
      • MySQL supports two popular Storage Engines, MyISAM and INNODB
      • If you do not specify via the engine keyword, the one specified in your configuration will be used
      • In our case, we specified INNODB
    • MSSQL
      • There are two Storage Engines, and those are the traditional Disk based, and the In-Memory ( v2014+)
      • We are using disk based
      • The syntax for In-Memory is quite different

2 thoughts on “Database – Create Table Syntax in different Databases

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s