SQL scripts are an essential tool for making complex database changes in SQL Server. In this article, we will explore what SQL scripts are and how they can be used effectively.

Desired Changes

Before diving into SQL scripts, it is important to clearly define the desired changes in the database. This involves understanding the current data model and identifying the areas that need improvement. For example, if we have a simple data model consisting of tables for countries and cities, we may want to expand it to include tables for employees, customers, call outcomes, and calls.

What are SQL Scripts?

In programming, scripts are a series of commands or instructions that are executed in another program. SQL scripts are a specific type of script that contains SQL commands. These commands can be a combination of Data Definition Language (DDL) or Data Manipulation Language (DML) commands.

DDL commands are used to create, alter, or drop database objects such as tables, constraints, and indexes. DML commands, on the other hand, are used to manipulate data in the database, such as inserting, updating, or deleting records.

Benefits of Using SQL Scripts

Using SQL scripts has several benefits, especially when making significant changes to the database:

• Consistency: By using scripts, you can ensure that the changes are applied consistently across different environments.
• Version Control: Scripts can be stored in version control systems, allowing you to track changes and easily revert them if needed.
• Data Preservation: When deploying a new version of the database, scripts can help preserve existing data by performing necessary changes without losing any information.

Backup and Restore

Before making any major changes to the database, it is always recommended to create a backup. This ensures that you have a copy of the database in case anything goes wrong during the script execution.

In SQL Server, you can easily create a backup by right-clicking on the database, selecting the “Tasks” option, and choosing “Back Up”. You can then restore the backup if needed.

Example SQL Script

Let’s take a look at an example SQL script that creates new tables and populates them with data:

-- tables
CREATE TABLE call (
  id int NOT NULL IDENTITY (1, 1),
  employee_id int NOT NULL,
  customer_id int NOT NULL,
  start_time datetime NOT NULL,
  end_time datetime NULL,
  call_outcome_id int NULL,
  CONSTRAINT call_pk PRIMARY KEY (id)
);

CREATE TABLE call_outcome (
  id int NOT NULL IDENTITY (1, 1),
  outcome_text char(128) NOT NULL,
  CONSTRAINT call_outcome_pk PRIMARY KEY (id)
);

CREATE TABLE customer (
  id int NOT NULL IDENTITY (1, 1),
  customer_name varchar(255) NOT NULL,
  city_id int NOT NULL,
  customer_address varchar(255) NOT NULL,
  CONSTRAINT customer_pk PRIMARY KEY (id)
);

CREATE TABLE employee (
  id int NOT NULL IDENTITY (1, 1),
  first_name varchar(255) NOT NULL,
  last_name varchar(255) NOT NULL,
  CONSTRAINT employee_pk PRIMARY KEY (id)
);

-- foreign keys
ALTER TABLE call ADD CONSTRAINT call_call_outcome FOREIGN KEY (call_outcome_id) REFERENCES call_outcome (id);
ALTER TABLE call ADD CONSTRAINT call_customer FOREIGN KEY (customer_id) REFERENCES customer (id);
ALTER TABLE call ADD CONSTRAINT call_employee FOREIGN KEY (employee_id) REFERENCES employee (id);

-- insert values
INSERT INTO call_outcome (outcome_text) VALUES ('call started');
INSERT INTO call_outcome (outcome_text) VALUES ('finished - successfully');
INSERT INTO call_outcome (outcome_text) VALUES ('finished - unsuccessfully');

INSERT INTO employee (first_name, last_name) VALUES ('Thomas (Neo)', 'Anderson');
INSERT INTO employee (first_name, last_name) VALUES ('Agent', 'Smith');

INSERT INTO customer (customer_name, city_id, customer_address) VALUES ('Jewelry Store', 4, 'Long Street 120');
INSERT INTO customer (customer_name, city_id, customer_address) VALUES ('Bakery', 1, 'Kurfürstendamm 25');
INSERT INTO customer (customer_name, city_id, customer_address) VALUES ('Café', 1, 'Tauentzienstraße 44');
INSERT INTO customer (customer_name, city_id, customer_address) VALUES ('Restaurant', 3, 'Ulica lipa 15');

INSERT INTO call (employee_id, customer_id, start_time, end_time, call_outcome_id) VALUES (1, 4, '2020/1/11 9:0:15', '2020/1/11 9:12:22', 2);
INSERT INTO call (employee_id, customer_id, start_time, end_time, call_outcome_id) VALUES (1, 2, '2020/1/11 9:14:50', '2020/1/11 9:20:1', 2);
INSERT INTO call (employee_id, customer_id, start_time, end_time, call_outcome_id) VALUES (2, 3, '2020/1/11 9:2:20', '2020/1/11 9:18:5', 3);
INSERT INTO call (employee_id, customer_id, start_time, end_time, call_outcome_id) VALUES (1, 1, '2020/1/11 9:24:15', '2020/1/11 9:25:5', 3);
INSERT INTO call (employee_id, customer_id, start_time, end_time, call_outcome_id) VALUES (1, 3, '2020/1/11 9:26:23', '2020/1/11 9:33:45', 2);
INSERT INTO call (employee_id, customer_id, start_time, end_time, call_outcome_id) VALUES (1, 2, '2020/1/11 9:40:31', '2020/1/11 9:42:32', 2);
INSERT INTO call (employee_id, customer_id, start_time, end_time, call_outcome_id) VALUES (2, 4, '2020/1/11 9:41:17', '2020/1/11 9:45:21', 2);
INSERT INTO call (employee_id, customer_id, start_time, end_time, call_outcome_id) VALUES (1, 1, '2020/1/11 9:42:32', '2020/1/11 9:46:53', 3);
INSERT INTO call (employee_id, customer_id, start_time, end_time, call_outcome_id) VALUES (2, 1, '2020/1/11 9:46:0', '2020/1/11 9:48:2', 2);
INSERT INTO call (employee_id, customer_id, start_time, end_time, call_outcome_id) VALUES (2, 2, '2020/1/11 9:50:12', '2020/1/11 9:55:35', 2);

This script creates the necessary tables, defines the relationships between them using foreign keys, and inserts sample data into the tables.

Conclusion

SQL scripts are powerful tools for making complex database changes in SQL Server. By using scripts, you can ensure consistency, preserve data, and easily track and revert changes. Understanding how to write and execute SQL scripts is essential for any SQL Server developer or administrator.