Once you have completed the physical design phase of the database design process, the next step is to implement your proposed database solution.
The Structured Query Language (SQL) of Microsoft SQL Server is called Transact-SQL (T-SQL). The Transact-SQL statements have three categories: Data Definition Language (DDL) statements, Data Control Language (DCL) statements, and Data Manipulation Language (DML) statements that can be used to create, modify, and query SQL Server databases and tables.
In this chapter, we'll be covering the following topics:
- Understanding DDL, DCL, and DML language elements
- Understanding the purpose of SQL Server 2014 system databases
- Exploring database recovery models
- Creating and modifying databases
- Creating and modifying database schemas
- Creating and modifying tables
- Grating, revoking, and denying permissions to securables
As mentioned earlier, the Transact-SQL statements have three categories: DDL statements, DCL statements, and DML statements. Each of the commands in these categories include keywords and parameters that can be used to create, modify, and query SQL Server 2014 databases and tables. Let's have a quick look at the keywords and the purpose of each T-SQL statement type in the following sections.
The T-SQL DDL statements include keywords that you can use to create databases and database objects, modify databases and database objects, and remove databases and database objects. The DDL statements consist of the following keywords: CREATE, ALTER, and DROP. Using these DDL keywords, you can create and modify the structure of your databases and create and modify all kinds of database objects (tables, schemas, indexes, stored procedures, functions, views, triggers, login accounts, database users, server and database roles, credentials, extended events, event notifications, and service broker objects).
To execute DDL statements, you must have the appropriate permissions to SQL Server and database. By default, members of the sysadmin fixed server role and the db_owner fixed database role have permissions to execute DDL statements.
We use DML statements to insert, update, delete, and query data that is stored in SQL Server database tables. The DML statements consist of the following commands: SELECT, INSERT, BULK INSERT, UPDATE, MERGE, and DELETE.
To execute DML statements, you must have the appropriate permissions in the database. By default, members of the sysadmin fixed server role and the db_owner and db_writer fixed database roles have the permissions to execute DML statements.
DCL statements enable you to grant, deny, and revoke permissions on databases and database objects. The DCL statements include the standard GRANT and REVOKE keywords, as well as the T-SQL DENY statement.
To execute DCL statements, you must have the appropriate permissions for the database. By default, members of the sysadmin and securityadmin fixed server roles and the db_owner and db_securityadmin fixed database roles have the permissions to execute DCL statements.