1. Deploy and connect to SQL Server Linux containers

In this quickstart, you’ll use Docker to pull and run the SQL Server 2022 (16.x) Linux container image, mssql-server-linux. Then you can connect with sqlcmd to create your first database and run queries.

1.1. Pull and run the SQL Server Linux container image

  1. Pull the SQL Server 2022 (16.x) Linux container image from the Microsoft Container Registry.

    sudo docker pull mcr.microsoft.com/mssql/server:2022-latest
  2. To run the Linux container image with Docker, you can use the following command from a bash shell or elevated PowerShell command prompt.

    The SA_PASSWORD environment variable is deprecated. Please use MSSQL_SA_PASSWORD instead.
    sudo docker run -e "ACCEPT_EULA=Y" -e "MSSQL_SA_PASSWORD=<YourStrong@Passw0rd>" \
       -p 1433:1433 --name sql1 --hostname sql1 \
       -d \

    Your password should follow the SQL Server default password policy, otherwise the container can’t set up SQL Server and will stop working. By default, the password must be at least eight characters long and contain characters from three of the following four sets: uppercase letters, lowercase letters, base-10 digits, and symbols.

    By default, this quickstart creates a container with the Developer edition of SQL Server. The process for running production editions in containers is slightly different.

  3. To view your Docker containers, use the docker ps command.

    docker ps

    You should see output similar to the following:

    CONTAINER ID   IMAGE                                        COMMAND                  CREATED          STATUS        PORTS                                       NAMES
    274554ac9f0d   mcr.microsoft.com/mssql/server:2022-latest   "/opt/mssql/bin/perm…"   42 seconds ago   Up 1 second>1433/tcp, :::1433->1433/tcp   sql1
  4. If the STATUS column shows a status of Up, then SQL Server is running in the container and listening on the port specified in the PORTS column. If the STATUS column for your SQL Server container shows Exited, see the Troubleshooting section of the configuration guide.

1.2. Connect to SQL Server

The following steps use the SQL Server command-line tool, sqlcmd, inside the container to connect to SQL Server.

  1. Use the docker exec -it command to start an interactive bash shell inside your running container. In the following example sql1 is name specified by the --name parameter when you created the container.

    sudo docker exec -it sql1 "bash"
  2. Once inside the container, connect locally with sqlcmd, using its full path.

    /opt/mssql-tools/bin/sqlcmd -S localhost -U SA -P "<YourStrong@Passw0rd>"
    You can omit the password on the command-line to be prompted to enter it. Here’s an example:
    /opt/mssql-tools/bin/sqlcmd -S localhost -U SA
  3. If successful, you should get to a sqlcmd command prompt: 1>.

1.3. Create and query data

The following sections walk you through using sqlcmd and Transact-SQL to create a new database, add data, and run a query.

1.3.1. Create a new database

The following steps create a new database named TestDB.

  1. From the sqlcmd command prompt, paste the following Transact-SQL command to create a test database:

  2. On the next line, write a query to return the name of all of the databases on your server:

    SELECT Name from sys.databases;
  3. The previous two commands weren’t run immediately. Type GO on a new line to run the previous commands:


1.3.2. Insert data

Next create a new table, Inventory, and insert two new rows.

  1. From the sqlcmd command prompt, switch context to the new TestDB database:

    USE TestDB;
  2. Create new table named Inventory:

    CREATE TABLE Inventory (id INT, name NVARCHAR(50), quantity INT);
  3. Insert data into the new table:

    INSERT INTO Inventory VALUES (1, 'banana', 150); INSERT INTO Inventory VALUES (2, 'orange', 154);
  4. Type GO to run the previous commands:


1.3.3. Select data

Now, run a query to return data from the Inventory table.

  1. From the sqlcmd command prompt, enter a query that returns rows from the Inventory table where the quantity is greater than 152:

    SELECT * FROM Inventory WHERE quantity > 152;
  2. Run the command:

  3. Exit the sqlcmd command prompt

    To end your sqlcmd session, type QUIT:

  4. To exit the interactive command-prompt in your container, type exit. Your container continues to run after you exit the interactive bash shell.

2. Databases

A database in SQL Server is made up of a collection of tables that stores a specific set of structured data. A tableontains a collection of rows, also referred to as records or tuples, and columns, also referred to as attributes. Each column in the table is designed to store a certain type of information, for example, dates, names, dollar amounts, and numbers.

2.1. Basic Information about Databases

A computer can have one or more than one instance of SQL Server installed. Each instance of SQL Server can contain one or many databases. Within a database, there are one or many object ownership groups called schemas. Within each schema there are database objects such as tables, views, and stored procedures. Some objects such as certificates and asymmetric keys are contained within the database, but are not contained within a schema. For more information about creating tables, see Tables.

SQL Server databases are stored in the file system in files. Files can be grouped into filegroups. For more information about files and filegroups, see {sqldb-file-filegroups}[Database Files and Filegroups].

When people gain access to an instance of SQL Server they are identified as a login. When people gain access to a database they are identified as a database user. A database user can be based on a login. If contained databases are enabled, a database user can be created that is not based on a login. For more information about users, see CREATE USER (Transact-SQL).

A user that has access to a database can be given permission to access the objects in the database. Though permissions can be granted to individual users, we recommend creating database roles, adding the database users to the roles, and then grant access permission to the roles. Granting permissions to roles instead of users makes it easier to keep permissions consistent and understandable as the number of users grow and continually change. For more information about roles permissions, see CREATE ROLE (Transact-SQL) and Principals (Database Engine).

2.2. Working with Databases

Most people who work with databases use the SQL Server Management Studio tool. The Management Studio tool has a graphical user interface for creating databases and the objects in the databases. Management Studio also has a query editor for interacting with databases by writing Transact-SQL statements. Management Studio can be installed from the SQL Server installation disk, or downloaded from MSDN. For more information about SQL Server Management Studio tool, see SQL Server Management Studio (SSMS).

2.3. System Databases

SQL Server includes the following system databases.

System database Description

master Database

Records all the system-level information for an instance of SQL Server.

msdb Database

Is used by SQL Server Agent for scheduling alerts and jobs.

model Database

Is used as the template for all databases created on the instance of SQL Server. Modifications made to the model database, such as database size, collation, recovery model, and other database options, are applied to any databases created afterward.

Resource Database

Is a read-only database that contains system objects that are included with SQL Server. System objects are physically persisted in the Resource database, but they logically appear in the sys schema of every database.

tempdb Database

Is a workspace for holding temporary objects or intermediate result sets.

For Azure SQL Database single databases and elastic pools, only master Database and tempdb Database apply.

2.3.1. master Database

The master database records all the system-level information for a SQL Server system. This includes instance-wide metadata such as logon accounts, endpoints, linked servers, and system configuration settings.

In SQL Server, system objects are no longer stored in the master database; instead, they are stored in the Resource database.

Also, master is the database that records the existence of all other databases and the location of those database files and records the initialization information for SQL Server. Therefore, SQL Server cannot start if the master database is unavailable.

2.4. Contained Databases

A contained database is a database that is isolated from other databases and from the instance of SQL Server that hosts the database. SQL Server helps user to isolate their database from the instance in 4 ways.

  • Much of the metadata that describes a database is maintained in the database. (In addition to, or instead of, maintaining metadata in the master database.)

  • All metadata are defined using the same collation.

  • User authentication can be performed by the database, reducing the databases dependency on the logins of the instance of SQL Server.

  • The SQL Server environment (DMV’s, XEvents, etc.) reports and can act upon containment information.

2.5. Database Files and Filegroups

At a minimum, every SQL Server database has two operating system files: a data file and a log file. Data files contain data and objects such as tables, indexes, stored procedures, and views. Log files contain the information that is required to recover all transactions in the database. Data files can be grouped together in filegroups for allocation and administration purposes.

2.5.1. Database Files

SQL Server databases have three types of files, as shown in the following table.

File Description


Contains startup information for the database and points to the other files in the database. Every database has one primary data file. The recommended file name extension for primary data files is .mdf.


Optional user-defined data files. Data can be spread across multiple disks by putting each file on a different disk drive. The recommended file name extension for secondary data files is .ndf.

Transaction Log

The log holds information used to recover the database. There must be at least one log file for each database. The recommended file name extension for transaction logs is .ldf.

For example, a simple database named Sales has one primary file that contains all data and objects and a log file that contains the transaction log information. A more complex database named Orders can be created that includes one primary file and five secondary files. The data and objects within the database spread across all six files, and the four log files contain the transaction log information.

By default, the data and transaction logs are put on the same drive and path to handle single-disk systems. This choice may not be optimal for production environments. We recommend that you put data and log files on separate disks.

2.5.2. Logical and Physical File Names

SQL Server files have two file name types:

logical_file_name: The logical_file_name is the name used to refer to the physical file in all Transact-SQL statements. The logical file name must comply with the rules for SQL Server identifiers and must be unique among logical file names in the database.

os_file_name: The os_file_name is the name of the physical file including the directory path. It must follow the rules for the operating system file names.

When multiple instances of SQL Server are running on a single computer, each instance receives a different default directory to hold the files for the databases created in the instance. For more information, see File Locations for Default and Named Instances of SQL Server.

3. Database Engine Permissions and Principals

Permissions in the Database Engine are managed at the server level through logins and server roles, and at the database level through database users and database roles. The model for SQL Database exposes the same system within each database, but the server level permissions aren’t available.

3.1. Security principals

Security principal is the official name of the identities that use SQL Server and that can be assigned permission to take actions. They are usually people or groups of people, but can be other entities that pretend to be people.

The security principals can be created and managed using the Transact-SQL listed, or by using SQL Server Management Studio.

3.1.1. Logins

Logins are individual user accounts for logging on to the SQL Server Database Engine. SQL Server and SQL Database support logins based on Windows authentication and logins based on SQL Server authentication.

For information about the two types of logins, see Choose an Authentication Mode.

3.1.2. Fixed server roles

In SQL Server, fixed server roles are a set of pre-configured roles that provide convenient group of server-level permissions. Logins can be added to the roles using the ALTER SERVER ROLE …​ ADD MEMBER statement.

For more information, see ALTER SERVER ROLE (Transact-SQL).

SQL Database doesn’t support the fixed server roles, but has two roles in the master database (dbmanager and loginmanager) that act like server roles.

3.1.3. User-defined server roles

In SQL Server, you can create your own server roles and assign server-level permissions to them. Logins can be added to the server roles using the ALTER SERVER ROLE …​ ADD MEMBER statement.

For more information, see ALTER SERVER ROLE (Transact-SQL).

SQL Database doesn’t support the user-defined server roles.

3.1.4. Database users

Logins are granted access to a database by creating a database user in a database and mapping that database user to sign in. Typically the database user name is the same as the login name, though it doesn’t have to be the same.

Each database user maps to a single login. A login can be mapped to only one user in a database, but can be mapped as a database user in several different databases.

Database users can also be created that don’t have a corresponding login. These users are called contained database users. Microsoft encourages the use of contained database users because it makes it easier to move your database to a different server. Like a login, a contained database user can use either Windows authentication or SQL Server authentication. For more information, see Contained Database Users - Making Your Database Portable.

There are 12 types of users with slight differences in how they authenticate, and who they represent. To see a list of users, see CREATE USER (Transact-SQL).

3.1.5. Fixed database roles

Fixed database roles are a set of pre-configured roles that provide convenient group of database-level permissions. Database users and user-defined database roles can be added to the fixed database roles using the ALTER ROLE …​ ADD MEMBER statement.

For more information, see ALTER ROLE (Transact-SQL).

3.1.6. User-defined database roles

Users with the CREATE ROLE permission can create new user-defined database roles to represent groups of users with common permissions. Typically permissions are granted or denied to the entire role, simplifying permissions management and monitoring. Database users can be added to the database roles by using the ALTER ROLE …​ ADD MEMBER statement.

For more information, see ALTER ROLE (Transact-SQL).

3.1.7. Other principals

Additional security principals not discussed here include application roles, and logins and users based on certificates or asymmetric keys.

For a graphic showing the relationships between Windows users, Windows groups, logins, and database users, see Create a Database User.

3.2. Assign permissions

Most permission statements have the format:


  • The PERMISSION establishes what action is allowed or prohibited. The exact number of permissions differs between SQL Server and SQL Database. The permissions are listed in the article Permissions (Database Engine) and in the chart referenced below.

  • ON SECURABLE::NAME is the type of securable (server, server object, database, or database object) and its name.

    Some permissions don’t require ON SECURABLE::NAME because it is unambiguous or inappropriate in the context. For example, the CREATE TABLE permission doesn’t require the ON SECURABLE::NAME clause (GRANT CREATE TABLE TO Mary; allows Mary to create tables).

  • PRINCIPAL is the security principal (login, user, or role) which receives or loses the permission. Grant permissions to roles whenever possible.

The following example grant statement, grants the UPDATE permission on the Parts table or view that is contained in the Production schema to the role named PartsTeam:

GRANT UPDATE ON OBJECT::Production.Parts TO PartsTeam;

The following example grant statement grants the UPDATE permission on the Production schema, and by extension on any table or view contained within this schema to the role named ProductionTeam, which is a more effective and salable approach to assigning permissions than on individual object-level:

GRANT UPDATE ON SCHEMA::Production TO ProductionTeam;

Permissions are granted to security principals (logins, users, and roles) by using the GRANT statement. Permissions are explicitly denied by using the DENY command. A previously granted or denied permission is removed by using the REVOKE statement. Permissions are cumulative, with the user receiving all the permissions granted to the user, login, and any group memberships; however any permission denial overrides all grants.

A common mistake is to attempt to remove a GRANT by using DENY instead of REVOKE. This can cause problems when a user receives permissions from multiple sources; which is quite common. The following example demonstrates the principal.

The Sales group receives SELECT permissions on the OrderStatus table through the statement GRANT SELECT ON OBJECT::OrderStatus TO Sales;. User Jae is a member of the Sales role. Jae has also been granted SELECT permission to the OrderStatus table under their own user name through the statement GRANT SELECT ON OBJECT::OrderStatus TO Jae;. Presume the administer wishes to remove the GRANT to the Sales role.

  • If the administrator correctly executes REVOKE SELECT ON OBJECT::OrderStatus TO Sales;, then Jae will retain SELECT access to the OrderStatus table through their individual GRANT statement.

  • If the administrator incorrectly executes DENY SELECT ON OBJECT::OrderStatus TO Sales; then Jae, as a member of the Sales role, will be denied the SELECT permission because the DENY to Sales overrides their individual GRANT.

Permissions can be configured using Management Studio. Find the securable in Object Explorer, right-click the securable, and then select Properties. Select the Permissions page.

For help on using the permission page, see Permissions or Securables Page.

3.3. Permission hierarchy

Permissions have a parent/child hierarchy. That is, if you grant SELECT permission on a database, that permission includes SELECT permission on all (child) schemas in the database. If you grant SELECT permission on a schema, it includes SELECT permission on all the (child) tables and views in the schema. The permissions are transitive; that is, if you grant SELECT permission on a database, it includes SELECT permission on all (child) schemas, and all (grandchild) tables and views.

Permissions also have covering permissions. The CONTROL permission on an object, normally gives you all other permissions on the object.

Because both the parent/child hierarchy and the covering hierarchy can act on the same permission, the permission system can get complicated. For example, let’s take a table (Region), in a schema (Customers), in a database (SalesDB).

  • CONTROL permission on table Region includes all the other permissions on the table Region, including ALTER, SELECT, INSERT, UPDATE, DELETE, and some other permissions.

  • SELECT on the Customers schema that owns the Region table includes the SELECT permission on the Region table.

So SELECT permission on the Region table can be achieved through any of these six statements:







3.4. Monitor permissions

The following views return security information.

  • The logins and user-defined server roles on a server can be examined by using the sys.server_principals view. This view isn’t available in SQL Database.

  • The users and user-defined roles in a database can be examined by using the sys.database_principals view.

  • The permissions granted to logins and user-defined fixed server roles can be examined by using the sys.server_permissions view. This view isn’t available in SQL Database.

  • The permissions granted to users and user-defined fixed database roles can be examined by using the sys.database_permissions view.

  • Database role membership can be examined by using the sys.database_role_members view.

  • Server role membership can be examined by using the sys.server_role_members view. This view isn’t available in SQL Database.

  • For additional security related views, see Security Catalog Views (Transact-SQL).

3.5. Principals (Database Engine)

Principals are entities that can request SQL Server resources. Like other components of the SQL Server authorization model, principals can be arranged in a hierarchy. The scope of influence of a principal depends on the scope of the definition of the principal: Windows, server, database; and whether the principal is indivisible or a collection. A Windows Login is an example of an indivisible principal, and a Windows Group is an example of a principal that is a collection. Every principal has a security identifier (SID).

3.5.1. SQL Server-level principals

  • SQL Server authentication Login

  • Windows authentication login for a Windows user

  • Windows authentication login for a Windows group

  • Azure Active Directory authentication login for a AD user

  • Azure Active Directory authentication login for a AD group

  • Server Role

3.5.2. Database-level principals

  • Database User (There are 12 types of users. For more information, see CREATE USER.)

  • Database Role

  • Application Role

3.5.3. sa Login

The SQL Server sa login is a server-level principal. By default, it is created when an instance is installed.

Beginning in SQL Server 2005 (9.x), the default database of sa is master. This is a change of behavior from earlier versions of SQL Server.

The sa login is a member of the sysadmin fixed server-level role.

The sa login has all permissions on the server and cannot be limited.

The sa login cannot be dropped, but it can be disabled so that no one can use it.

3.5.4. dbo User and dbo Schema

The dbo user is a special user principal in each database.

All SQL Server administrators, members of the sysadmin fixed server role, sa login, and owners of the database, enter databases as the dbo user.

The dbo user has all permissions in the database and cannot be limited or dropped.

dbo stands for database owner, but the dbo user account is not the same as the db_owner fixed database role, and the db_owner fixed database role is not the same as the user account that is recorded as the owner of the database.

The dbo user owns the dbo schema. The dbo schema is the default schema for all users, unless some other schema is specified. The dbo schema cannot be dropped.

3.5.5. public Server Role and Database Role

Every login belongs to the public fixed server role, and every database user belongs to the public database role.

When a login or user has not been granted or denied specific permissions on a securable, the login or user inherits the permissions granted to public on that securable.

The public fixed server role and the public fixed database role cannot be dropped. However you can revoke permissions from the public roles.

There are many permissions that are assigned to the public roles by default. Most of these permissions are needed for routine operations in the database; the type of things that everyone should be able to do.

Be careful when revoking permissions from the public login or user, as it will affect all logins/users. Generally you should not deny permissions to public, because the deny statement overrides any grant statements you might make to individuals.

4. SQL and T-SQL

SQL, From Wikipedia, the free encyclopedia, https://en.wikipedia.org/wiki/SQL

Structured Query Language, abbreviated as SQL (/ˌɛsˌkjuːˈɛl/ ) S-Q-L, sometimes /ˈsiːkwəl/ "sequel" for historical reasons), is a domain-specific language used in programming and designed for managing data held in a relational database management system (RDBMS), or for stream processing in a relational data stream management system (RDSMS). It is particularly useful in handling structured data, i.e. data incorporating relations among entities and variables.

Transact-SQL, From Wikipedia, the free encyclopedia, https://en.wikipedia.org/wiki/Transact-SQL

Transact-SQL (T-SQL) is Microsoft’s and Sybase’s proprietary extension to the SQL (Structured Query Language) used to interact with relational databases. T-SQL expands on the SQL standard to include procedural programming, local variables, various support functions for string processing, date processing, mathematics, etc. and changes to the DELETE and UPDATE statements.

Transact-SQL is central to using Microsoft SQL Server. All applications that communicate with an instance of SQL Server do so by sending Transact-SQL statements to the server, regardless of the user interface of the application.

Stored procedures in SQL Server are executable server-side routines. The advantage of stored procedures is the ability to pass parameters.

5. SQL Server Utilities Statements - GO

SQL Server provides commands that are not Transact-SQL statements, but are recognized by the sqlcmd and osql utilities and SQL Server Management Studio Code Editor. These commands can be used to facilitate the readability and execution of batches and scripts.

GO signals the end of a batch of Transact-SQL statements to the SQL Server utilities.


GO [count]



Is a positive integer. The batch preceding GO will execute the specified number of times.


GO is not a Transact-SQL statement; it is a command recognized by the sqlcmd and osql utilities and SQL Server Management Studio Code editor.

SQL Server utilities interpret GO as a signal that they should send the current batch of Transact-SQL statements to an instance of SQL Server. The current batch of statements is composed of all statements entered since the last GO, or since the start of the ad hoc session or script if this is the first GO.

A Transact-SQL statement cannot occupy the same line as a GO command. However, the line can contain comments.

Users must follow the rules for batches. For example, any execution of a stored procedure after the first statement in a batch must include the EXECUTE keyword. The scope of local (user-defined) variables is limited to a batch, and cannot be referenced after a GO command.

USE AdventureWorks2012;
SELECT @MyMsg = 'Hello, World.'
GO -- @MyMsg is not valid after this GO ends the batch.

-- Yields an error because @MyMsg not declared in this batch.

-- Yields an error: Must be EXEC sp_who if not first statement in
-- batch.

SQL Server applications can send multiple Transact-SQL statements to an instance of SQL Server for execution as a batch. The statements in the batch are then compiled into a single execution plan. Programmers executing ad hoc statements in the SQL Server utilities, or building scripts of Transact-SQL statements to run through the SQL Server utilities, use GO to signal the end of a batch.

Applications based on the ODBC or OLE DB APIs receive a syntax error if they try to execute a GO command. The SQL Server utilities never send a GO command to the server.

Do not use a semicolon as a statement terminator after GO.

-- Yields an error because ; is not permitted after GO

6. Transact-SQL syntax conventions (Transact-SQL)

The following table lists and describes conventions that are used in the syntax diagrams in the Transact-SQL reference.

Convention Used for


Transact-SQL keywords.


User-supplied parameters of Transact-SQL syntax.


Type database names, table names, column names, index names, stored procedures, utilities, data type names, and text exactly as shown.

| (vertical bar)

Separates syntax items enclosed in brackets or braces. You can use only one of the items.

[ ] (brackets)

Optional syntax item.

{ } (braces)

Required syntax items. Don’t type the braces.

[ , …​n ]

Indicates the preceding item can be repeated n number of times. The occurrences are separated by commas.

[ …​n ]

Indicates the preceding item can be repeated n number of times. The occurrences are separated by blanks.


Transact-SQL statement terminator. Although the semicolon isn’t required for most statements in this version of SQL Server, it will be required in a future version.


The name for a block of syntax. Use this convention to group and label sections of lengthy syntax or a unit of syntax that you can use in more than one location within a statement. Each location in which the block of syntax could be used is indicated with the label enclosed in chevrons: <label>.

A set is a collection of expressions, for example <grouping set>; and a list is a collection of sets, for example <composite element list>.

6.1. Multipart names

Unless specified otherwise, all Transact-SQL references to the name of a database object can be a four-part name in the following form:


| database_name.[schema_name].object_name

| schema_name.object_name

| object_name
  • server_name

    Specifies a linked server name or remote server name.

  • database_name

    Specifies the name of a SQL Server database when the object resides in a local instance of SQL Server. When the object is in a linked server, database_name specifies an OLE DB catalog.

  • schema_name

    Specifies the name of the schema that contains the object if the object is in a SQL Server database. When the object is in a linked server, schema_name specifies an OLE DB schema name.

  • object_name

    Refers to the name of the object.

When referencing a specific object, you don’t always have to specify the server, database, and schema for the SQL Server Database Engine to identify the object. However, if the object can’t be found, an error is returned.

To avoid name resolution errors, we recommend specifying the schema name whenever you specify a schema-scoped object.

To omit intermediate nodes, use periods to indicate these positions. The following table shows the valid formats of object names.

Object reference format Description


Four-part name.


Schema name is omitted.


Database name is omitted.


Database and schema name are omitted.


Server name is omitted.


Server and schema name are omitted.


Server and database name are omitted.


Server, database, and schema name are omitted.

SELECT @@SERVERNAME; -- 8a90e1fbcc1b
SELECT name FROM [8a90e1fbcc1b].[master].[sys].[servers];

7. Tutorial: Write Transact-SQL statements

This tutorial is intended as a brief introduction to the Transact-SQL language and not as a replacement for a Transact-SQL class. The statements in this tutorial are intentionally simple, and aren’t meant to represent the complexity found in a typical production database.

7.1. Lesson 1: Create and query database objects

Transact-SQL statements can be written and submitted to the Database Engine in the following ways:

  • By using SQL Server Management Studio.

  • By using the sqlcmd utility.

  • By connecting from an application that you create.

7.1.1. Create a database

Like many Transact-SQL statements, the CREATE DATABASE statement has a required parameter: the name of the database.

CREATE DATABASE also has many optional parameters, such as the disk location where you want to put the database files.

When you execute CREATE DATABASE without the optional parameters, SQL Server uses default values for many of these parameters.

  1. In a Query Editor window, type but don’t execute the following code:

  2. Use the pointer to select the words CREATE DATABASE, and then press F1.

  3. In Query Editor, press F5 to execute the statement and create a database named TestData.

When you create a database, SQL Server makes a copy of the model database, and renames the copy to the database name. This operation should only take several seconds, unless you specify a large initial size of the database as an optional parameter.

The keyword GO separates statements when more than one statement is submitted in a single batch. GO is optional when the batch contains only one statement.

7.1.2. Create a Table

To create a table, you must provide a name for the table, and the names and data types of each column in the table.

It is also a good practice to indicate whether null values are allowed in each column.

To create a table, you must have the CREATE TABLE permission, and the ALTER SCHEMA permission on the schema that will contain the table. The db_ddladmin fixed database role has these permissions.

Most tables have a primary key, made up of one or more columns of the table. A primary key is always unique. The Database Engine will enforce the restriction that any primary key value can’t be repeated in the table.

For a list of data types and links for a description of each, see Data Types (Transact-SQL).

The Database Engine can be installed as case sensitive or non-case sensitive. If the Database Engine is installed as case sensitive, object names must always have the same case. For example, a table named OrderData is a different table from a table named ORDERDATA. If the Database Engine is installed as non-case sensitive, those two table names are considered to be the same table, and that name can only be used one time. Switch the Query Editor connection to the TestData database

In a Query Editor window, type and execute the following code to change your connection to the TestData database.

USE TestData;
GO Create the table

In a Query Editor window, type and execute the following code to create a table named Products.

The columns in the table are named ProductID, ProductName, Price, and ProductDescription. The ProductID column is the primary key of the table. int, varchar(25), money, and varchar(max) are all data types. Only the Price and ProductionDescription columns can have no data when a row is inserted or changed. This statement contains an optional element (dbo.) called a schema. The schema is the database object that owns the table. If you are an administrator, dbo is the default schema. dbo stands for database owner.

CREATE TABLE dbo.Products
    (ProductID int PRIMARY KEY NOT NULL,
    ProductName varchar(25) NOT NULL,
    Price money NULL,
    ProductDescription varchar(max) NULL);

7.1.3. Insert and update data in a table

Now that you have created the Products table, you are ready to insert data into the table by using the INSERT statement. After the data is inserted, you will change the content of a row by using an UPDATE statement. You will use the WHERE clause of the UPDATE statement to restrict the update to a single row. The four statements will enter the following data.

ProductID ProductName Price ProductDescription




Workbench clamp




Flat head


Tire Bar

Tool for changing tires.





The basic syntax is: INSERT, table name, column list, VALUES, and then a list of the values to be inserted. The two hyphens in front of a line indicate that the line is a comment and the text will be ignored by the compiler. In this case, the comment describes a permissible variation of the syntax. Insert data into a table
  1. Execute the following statement to insert a row into the Products table that was created in the previous task.

    -- Standard syntax
    INSERT dbo.Products (ProductID, ProductName, Price, ProductDescription)
        VALUES (1, 'Clamp', 12.48, 'Workbench clamp')

If the insert succeeds, proceed to the next step.

If the insert fails, it may be because the Product table already has a row with that product ID in it. To proceed, delete all the rows in the table and repeat the preceding step. TRUNCATE TABLE deletes all the rows in the table.

  1. Run the following command to delete all the rows in the table:

    TRUNCATE TABLE TestData.dbo.Products;

    After you truncate the table, repeat the INSERT command in this step.

  2. The following statement shows how you can change the order in which the parameters are provided by switching the placement of the ProductID and ProductName in both the field list (in parentheses) and in the values list.

    -- Changing the order of the columns
    INSERT dbo.Products (ProductName, ProductID, Price, ProductDescription)
        VALUES ('Screwdriver', 50, 3.17, 'Flat head')
  3. The following statement demonstrates that the names of the columns are optional, as long as the values are listed in the correct order. This syntax is common but isn’t recommended because it might be harder for others to understand your code. NULL is specified for the Price column because the price for this product isn’t yet known.

    -- Skipping the column list, but keeping the values in order
    INSERT dbo.Products
        VALUES (75, 'Tire Bar', NULL, 'Tool for changing tires.')
  4. The schema name is optional as long as you are accessing and changing a table in your default schema. Because the ProductDescription column allows null values and no value is being provided, the ProductDescription column name and value can be dropped from the statement completely. SQL

    -- Dropping the optional dbo and dropping the ProductDescription column
    INSERT Products (ProductID, ProductName, Price)
        VALUES (3000, '3 mm Bracket', 0.52)
    GO Update the products table

Type and execute the following UPDATE statement to change the ProductName of the second product from Screwdriver, to Flat Head Screwdriver.

UPDATE dbo.Products
    SET ProductName = 'Flat Head Screwdriver'
    WHERE ProductID = 50

7.1.4. Read data from a table

Use the SELECT statement to read the data in a table. The SELECT statement is one of the most important Transact-SQL statements, and there are many variations in the syntax. For this tutorial, you will work with five simple versions. Read the data in a table
  1. Type and execute the following statements to read the data in the Products table.

    -- The basic syntax for reading data from a single table
    SELECT ProductID, ProductName, Price, ProductDescription
        FROM dbo.Products
  2. You can use an asterisk (*) to select all the columns in the table. The asterisk is for ad hoc queries. In permanent code, provide the column list so that the statement returns the predicted columns, even if a new column is added to the table later.

    -- Returns all columns in the table
    -- Does not use the optional schema, dbo
    SELECT * FROM Products
  3. You can omit columns that you don’t want to return. The columns will be returned in the order that they are listed.

    -- Returns only two of the columns from the table
    SELECT ProductName, Price
        FROM dbo.Products
  4. Use a WHERE clause to limit the rows that are returned to the user.

    -- Returns only two of the records in the table
    SELECT ProductID, ProductName, Price, ProductDescription
        FROM dbo.Products
        WHERE ProductID < 60
  5. You can work with the values in the columns as they are returned. The following example performs a mathematical operation on the Price column. Columns that have been changed in this way won’t have a name unless you provide one by using the AS keyword.

    -- Returns ProductName and the Price including a 7% tax
    -- Provides the name CustomerPays for the calculated column
    SELECT ProductName, Price * 1.07 AS CustomerPays
        FROM dbo.Products
    GO Useful functions in a SELECT statement

For information about some functions that you can use to work with data in SELECT statements, see the following articles:

7.1.5. Create views and stored procedures

A view is a stored SELECT statement, and a stored procedure is one or more Transact-SQL statements that execute as a batch.

Views are queried like tables and don’t accept parameters. Stored procedures are more complex than views. Stored procedures can have both input and output parameters and can contain statements to control the flow of the code, such as IF and WHILE statements. It is good programming practice to use stored procedures for all repetitive actions in the database.

For this example, you will use CREATE VIEW to create a view that selects only two of the columns in the Products table. Then, you will use CREATE PROCEDURE to create a stored procedure that accepts a price parameter and returns only those products that cost less than the specified parameter value. Create a view

Execute the following statement to create a view that executes a select statement, and returns the names and prices of our products to the user.

   SELECT ProductName, Price FROM Products;
GO Test the view

Views are treated just like tables. Use a SELECT statement to access a view.

SELECT * FROM vw_Names;
GO Create a stored procedure

The following statement creates a stored procedure name pr_Names, accepts an input parameter named @VarPrice of data type money. The stored procedure prints the statement Products less than concatenated with the input parameter that is changed from the money data type into a varchar(10) character data type. Then, the procedure executes a SELECT statement on the view, passing the input parameter as part of the WHERE clause. This returns all products that cost less than the input parameter value.

CREATE PROCEDURE pr_Names @VarPrice money
      -- The print statement returns text to the user
      PRINT 'Products less than ' + CAST(@VarPrice AS varchar(10));
      -- A second statement starts here
      SELECT ProductName, Price FROM vw_Names
            WHERE Price < @VarPrice;
GO Test the stored procedure

To test the stored procedure, type and execute the following statement. The procedure should return the names of the two products entered into the Products table in Lesson 1 with a price that is less than 10.00.

EXECUTE pr_Names 10.00;

7.2. Lesson 2: Configure permissions on database objects

Granting a user access to a database involves three steps.

  • First, you create a login. The login lets the user connect to the SQL Server Database Engine.

  • Then you configure the login as a user in the specified database.

  • And finally, you grant that user permission to database objects.

This lesson shows you these three steps, and shows you how to create a view and a stored procedure as the object.

7.2.1. Create a login

To access the Database Engine, users require a login. The login can represent the user’s identity as a Windows account or as a member of a Windows group, or the login can be a SQL Server login that exists only in SQL Server. Whenever possible you should use Windows Authentication. Create a new Windows account

By default, administrators on your computer have full access to SQL Server. For this lesson, we want to have a less privileged user; therefore, you will create a new local Windows Authentication account on your computer.

To do this, you must be an administrator on your computer. Then you will grant that new user access to SQL Server.

  1. Select Start, select Run, in the Open box, type %SystemRoot%\system32\compmgmt.msc /s and then select OK to open the Computer Management program.

  2. Under System Tools, expand Local Users and Groups, right-click Users, and then select New User.

  3. In the User name box type Mary.

  4. In the Password and Confirm password box, type a strong password, and then select Create to create a new local Windows user. Create a SQL login using Windows Authentication with T-SQL

In a Query Editor window of SQL Server Management Studio, type and execute the following code replacing computer_name with the name of your computer. FROM WINDOWS indicates that Windows will authenticate the user. The optional DEFAULT_DATABASE argument connects Mary to the TestData database, unless her connection string indicates another database. This statement introduces the semicolon as an optional termination for a Transact-SQL statement.

CREATE LOGIN [computer_name\Mary]

This authorizes a user name Mary, authenticated by your computer, to access this instance of SQL Server. If there is more than one instance of SQL Server on the computer, you must create the login on each instance that Mary must access.

Because Mary is not a domain account, this user name can only be authenticated on this computer. Create a login using SQL Server Authentication with T-SQL
-- Creates the user "shcooper" for SQL Server using the security credential "RestrictedFaculty"
-- The user login starts with the password "Baz1nga," but that password must be changed after the first login.

   CREDENTIAL = RestrictedFaculty;

7.2.2. Grant access to a database

Mary now has access to this instance of SQL Server, but doesn’t have permission to access the databases. She doesn’t even have access to her default database TestData until you authorize her as a database user.

To grant Mary access, switch to the TestData database, and then use the CREATE USER statement to map her login to a user named Mary. Create a user in a database

Type and execute the following statements (replacing computer_name with the name of your computer) to grant Mary access to the TestData database.

USE [TestData];

CREATE USER [Mary] FOR LOGIN [computer_name\Mary];

Now, Mary has access to both SQL Server and the TestData database. Create views and stored procedures

As an administrator, you can execute the SELECT from the Products table and the vw_Names view, and execute the pr_Names procedure; however, Mary can’t. To grant Mary the necessary permissions, use the GRANT statement.

Grant permission to stored procedure

Execute the following statement to give Mary the EXECUTE permission for the pr_Names stored procedure.


In this scenario, Mary can only access the Products table by using the stored procedure. If you want Mary to be able to execute a SELECT statement against the view, then you must also execute GRANT SELECT ON vw_Names TO Mary. To remove access to database objects, use the REVOKE statement.

If the table, the view, and the stored procedure are not owned by the same schema, granting permissions becomes more complex. About GRANT

You must have EXECUTE permission to execute a stored procedure. You must have SELECT, INSERT, UPDATE, and DELETE permissions to access and change data. The GRANT statement is also used for other permissions, such as permission to create tables.

7.3. Lesson 3: Delete database objects

This short lesson removes the objects that you created in Lesson 1 and Lesson 2, and then drops the database.

Before you delete objects, make sure you are in the correct database:

USE TestData;

7.3.1. Revoke stored procedure permissions

Use the REVOKE statement to remove execute permission for Mary on the stored procedure:


7.3.2. Drop permissions

  1. Use the DROP statement to remove permission for Mary to access the TestData database:

    DROP USER Mary;
  2. Use the DROP statement to remove permission for Mary to access this instance of SQL Server 2005 (9.x):

    DROP LOGIN [<computer_name>\Mary];
  3. Use the DROP statement to remove the store procedure pr_Names:

    DROP PROC pr_Names;
  4. Use the DROP statement to remove the view vw_Names:

    DROP VIEW vw_Names;

7.3.3. Delete table

  1. Use the DELETE statement to remove all rows from the Products table:

    DELETE FROM Products;
  2. Use the DROP statement to remove the Products table:

    DROP TABLE Products;

7.3.4. Remove database

You can’t remove the TestData database while you are in the database; therefore, first switch context to another database, and then use the DROP statement to remove the TestData database: