Sql Server on Docker

sqlserver_on_docker.md

Sql Server on Docker

Install

sudo docker run -e 'ACCEPT_EULA=Y' -e 'SA_PASSWORD=<PASSWORD>' -p 1433:1433 --name <IMAGE_NAME> -d mcr.microsoft.com/mssql/server:2019-GA-ubuntu-16.04

Verify Installation

$ docker ps -a

CONTAINER ID	IMAGE							COMMAND			CREATED		STATUS		PORTS			NAMES
43214f707189	mcr.microsoft.com/mssql/server:2019-GA-ubuntu-16.04	"/opt/mssql/bin/perm…"	23 seconds ago	Up 22 seconds	0.0.0.0:1433->1433/tcp	<IMAGE_NAME>

Uninstall

$ sudo docker stop <IMAGE_NAME>
$ sudo docker rm <IMAGE_NAME>

Connection

Key	Value
Username	sa
Password
Host	0.0.0.0
Port	1433
Url for driver	jdbc:sqlserver://0.0.0.0:1433

---

SQL

View list of databases on SQL Server

GO
SELECT name, database_id, create_date
FROM sys.databases ;
GO

Default Databases

• master
• tempdb
• model
• msdb

Create Database

CREATE DATABASE TestDB;

Delete Database

DROP DATABASE TestDB;

Create Table

With Single Primary Key

CREATE TABLE Persons (
    PersonID int NOT NULL PRIMARY KEY,
    FirstName varchar(255),
	LastName varchar(255),
    Address varchar(255),
    City varchar(255)
);

With Multiple Primary Keys

CREATE TABLE Persons (
    PersonID int NOT NULL,
    FirstName varchar(255),
	LastName varchar(255),
    Address varchar(255),
    City varchar(255),
    CONSTRAINT PK_Person PRIMARY KEY (ID,LastName)
);

Alter primary key for a created table

ALTER TABLE Persons
ADD PRIMARY KEY (ID);

or

ALTER TABLE Persons
ADD CONSTRAINT PK_Person PRIMARY KEY (ID,LastName);

Drop a Primary Key Constraint

ALTER TABLE Persons
DROP PRIMARY KEY;

or

ALTER TABLE Persons
DROP CONSTRAINT PK_Person;

Foreign Key

CREATE TABLE Orders (
    OrderID int NOT NULL PRIMARY KEY,
    OrderNumber int NOT NULL,
    PersonID int FOREIGN KEY REFERENCES Persons(PersonID)
);

or

CREATE TABLE Orders (
    OrderID int NOT NULL,
    OrderNumber int NOT NULL,
    PersonID int,
    PRIMARY KEY (OrderID),
    CONSTRAINT FK_PersonOrder FOREIGN KEY (PersonID)
    REFERENCES Persons(PersonID)
);

Alter foreign key for a created table

ALTER TABLE Orders
ADD FOREIGN KEY (PersonID) REFERENCES Persons(PersonID);

or

ALTER TABLE Orders
ADD CONSTRAINT FK_PersonOrder
FOREIGN KEY (PersonID) REFERENCES Persons(PersonID);

Drop a Foreign Key Constraint

ALTER TABLE Orders
DROP CONSTRAINT FK_PersonOrder;

Delete Table

DROP TABLE Orders;

Delete Table Data (Truncate)

TRUNCATE TABLE Orders;