By: Daniel Calbimonte | Comments (2) | Related: > Other Database Platforms
Problem
If you are making the transition from MariaDB to SQL Server or vice versa it is helpful to have a comparison of similar commands between the two platforms. Sure you can do some searches to find the answer, but it would be helpful to have a list of common tasks and the equivalent SQL code for both database platforms.
Solution
The following tip will show a comparison of the T-SQL commands for SQL Server versus the SQL commands for MariaDB.
Requirements and setup
- SQL Server 2014, but 95% of the T-SQL commands will work on earlier versions as well
- The MariaDB should be installed. For this tip, I installed MariaDB and SQL Server on the same machine using the Windows OS. You can find the MariaDB installer here.
There are several ways to issue commands, but we will use the command line tools for both database systems.
How to start the command line in SQL Server
- SQL Server uses the sqlcmd as the command line to administer in SQL Server.
In order to start, in the cmd write sqlcmd.
How to start the command line in MariaDB
-
You can use the MySQL Client (MariaDB) to start the command line.
You will need to enter a password. This password was requested when installing the software.
Comparison of SQL Server vs. MariaDB Commands
How to create a database
|
SQL Server
|
MariaDB |
|---|---|
|
CREATE DATABASE TestDB Where TestDB is the new database created. In SQL Server when you create the database like this, you inherit the Database characteristics from the model database. The model database is a system database used as a template to create other databases. If you change the characteristics of the model database, the new databases created will inherit the changes. For more information about creating databases, review the CREATE DATABASES link. |
CREATE DATABASE TestDB2; For more information about creating Databases, review the CREATE DATABASES link, |
How to switch to a different database
|
SQL Server
|
MariaDB |
|---|---|
|
USE TestDB |
USE TestDB; Note that only the ; and go are different |
How to verify that the database was created
|
SQL Server
|
MariaDB |
|---|---|
|
exec sp_databases |
show databases; |
How to create a simple table
|
SQL Server
|
MariaDB |
|---|---|
|
create table customer(id int,name varchar(30)) For more information about creating tables and about SQL Server data types, refer to these links: |
create table customer(id int,name varchar(30)); For more information about creating tables and about MariaDB data types, refer to these links: |
How to insert data in a table
|
SQL Server
|
MariaDB |
|---|---|
|
insert into customer values (1,'John') More information: |
insert into customer values (1,'John'); |
How to create a simple stored procedure
|
SQL Server
|
MariaDB |
|---|---|
|
create procedure showcustomers More information: |
create procedure showcustomers() |
How to call a stored procedure
|
SQL Server
|
MariaDB |
|---|---|
| exec showcustomers GO |
call showcustomers; |
How to get the current date
|
SQL Server
|
MariaDB |
|---|---|
|
select CONVERT(date, GETDATE()) More information: |
select current_date; |
How to get the current time
|
SQL Server
|
MariaDB |
|---|---|
|
select CONVERT(time, GETDATE()) More information: |
select current_time; |
How to get the current date and time
|
SQL Server
|
MariaDB |
|---|---|
|
select getdate() More information: |
select current_date, current_time; |
How to modify the date format
|
SQL Server
|
MariaDB |
|---|---|
|
select FORMAT(getdate(),'MM-dd-yyyy') as date More information: |
select date_format(current_date,'%m%d%Y'); For more information, refer to this link: |
How to assign a value to a variable
|
SQL Server
|
MariaDB |
|---|---|
|
declare @var int=1 |
set @var=1; |
How to return the list of tables of the current database
|
SQL Server
|
MariaDB |
|---|---|
|
SELECT * FROM information_schema.tables |
SHOW TABLES; |
How to return the list of views of the current database
|
SQL Server
|
MariaDB |
|---|---|
|
SELECT * FROM information_schema.views |
SHOW TABLES; It shows tables and views. |
Next Steps
- For more information, refer to the following links:
- Stay tuned for more tips where we will cover functions and more queries
About the author
Daniel Calbimonte is a Microsoft SQL Server MVP, Microsoft Certified Trainer and 6-time Microsoft Certified IT Professional. Daniel started his career in 2001 and has worked with SQL Server 6.0 to 2022. Daniel is a DBA as well as specializes in Business Intelligence (SSIS, SSAS, SSRS) technologies.This author pledges the content of this article is based on professional experience and not AI generated.
View all my tips
