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 \
   mcr.microsoft.com/mssql/server:2022-latest

    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   0.0.0.0:1433->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-tools18/bin/sqlcmd -S localhost -U SA -P "<YourStrong@Passw0rd>" -No
    You can omit the password on the command-line to be prompted to enter it. Here’s an example: 
    /opt/mssql-tools18/bin/sqlcmd -S localhost -U SA -No

  3. If successful, you should get to a sqlcmd command prompt: 1>.

    1> :help
:!! [<command>]
  - Executes a command in the command shell.
:connect server[\instance] [-l timeout] [-U user [-P password]]
  - Connects to a SQL Server instance.
:error <dest>
  - Redirects error output to a file, stderr, or stdout.
:exit
  - Quits sqlcmd immediately.
:exit()
  - Execute statement cache; quit with no return value.
:exit(<query>)
  - Execute the specified query; returns numeric result.
go [<n>]
  - Executes the statement cache (n times).
:help
  - Shows this list of commands.
:list
  - Prints the content of the statement cache.
:listvar
  - Lists the set sqlcmd scripting variables.
:on error [exit|ignore]
  - Action for batch or sqlcmd command errors.
:out <filename>|stderr|stdout
  - Redirects query output to a file, stderr, or stdout.
:perftrace <filename>|stderr|stdout
  - Redirects timing output to a file, stderr, or stdout.
:quit
  - Quits sqlcmd immediately.
:r <filename>
  - Append file contents to the statement cache.
:reset
  - Discards the statement cache.
:serverlist
  - Lists local and SQL Servers on the network.
:setvar {variable}
  - Removes a sqlcmd scripting variable.
:setvar <variable> <value>
  - Sets a sqlcmd scripting variable.
    $ /opt/mssql-tools18/bin/sqlcmd -S localhost -U SA -P "<YourStrong@Passw0rd>" -No -Q "SELECT @@version"

------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Microsoft SQL Server 2022 (RTM-CU16) (KB5048033) - 16.0.4165.4 (X64)
	Nov  6 2024 19:24:49
	Copyright (C) 2022 Microsoft Corporation
	Developer Edition (64-bit) on Linux (Ubuntu 22.04.5 LTS) <X64>

(1 rows affected)

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:

    CREATE DATABASE TestDB;

  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:

    GO

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:

    GO

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:

    GO

  3. Exit the sqlcmd command prompt

    To end your sqlcmd session, type QUIT:

    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 table contains 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

2.1.1. Servers, Databases, Schemas and Tables

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.

Machine -> * Servers (instances) -> * Databases -> * Schemas -> * Tables, * Views

2.1.2. Files and FileGroups

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 Database Files and Filegroups.

2.1.3. Logins and Database Users

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).

Logins <-> Servers (instances)
Database Users <-> Databases

2.1.4. Roles and Permissions

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

Primary

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.

Secondary

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.

$ ls /var/opt/mssql/data/
Entropy.bin  Sales_log.ldf  mastlog.ldf  model_msdbdata.mdf  model_replicatedmaster.ldf  modellog.ldf  msdblog.ldf  tempdb2.ndf  tempdb4.ndf
Sales.mdf    master.mdf     model.mdf	 model_msdblog.ldf   model_replicatedmaster.mdf  msdbdata.mdf  tempdb.mdf   tempdb3.ndf  templog.ldf

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. Server-level roles

SQL Server provides server-level roles to help you manage the permissions on a server. These roles are security principals that group other principals. Server-level roles are server-wide in their permissions scope.

3.2. Database-level roles

Database-level roles are database-wide in their permissions scope.

There are two types of database-level roles: fixed-database roles that are predefined in the database and user-defined database roles that you can create.

Fixed-database roles are defined at the database level and exist in each database. Members of the db_owner database role can manage fixed-database role membership. There are also some special-purpose database roles in the msdb database.

You can add any database account and other SQL Server roles into database-level roles.

3.3. Contained database users: Make your database portable by using contained databases

Use contained database users to authenticate SQL Server and Azure SQL Database connections at the database level. A contained database is a database that’s isolated from other databases and from the instance of SQL Server or SQL Database (and the master database) that hosts the database.

3.3.1. Traditional login and user model

In the traditional connection model, Windows users or members of Windows groups connect to the Database Engine by providing user or group credentials authenticated by Windows. Or users can provide both a name and password and connect by using SQL Server authentication. In both cases, the master database must have a login that matches the connecting credentials.

After the Database Engine confirms the Windows authentication credentials or authenticates the SQL Server authentication credentials, the connection typically attempts to connect to a user database. To connect to a user database, the login must be mapped to (that is, associated with) a database user in the user database. The connection string might also specify connecting to a specific database, which is optional in SQL Server but required in SQL Database.

The important principle is that both the login (in the master database) and the user (in the user database) must exist and be related to each other. The connection to the user database has a dependency upon the login in the master database. This dependency limits the ability of the database to be moved to a different hosting SQL Server instance or Azure SQL Database server.

If a connection to the master database is not available (for example, a failover is in progress), the overall connection time will increase, or the connection might time out. An unavailable connection might reduce connection scalability.

3.3.2. Contained database user model

In the contained database user model, the login in the master database is not present. Instead, the authentication process occurs at the user database. The database user in the user database doesn’t have an associated login in the master database.

The contained database user model supports both Windows authentication and SQL Server authentication. You can use it in both SQL Server and SQL Database.

To connect as a contained database user, the connection string must always contain a parameter for the user database. The Database Engine uses this parameter to know which database is responsible for managing the authentication process.

The activity of the contained database user is limited to the authenticating database. The database user account must be independently created in each database that the user needs. To change databases, SQL Database users must create a new connection. Contained database users in SQL Server can change databases if an identical user is present in another database.

3.4. 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.4.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.4.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.4.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.4.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.4.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.4.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.4.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.5. Assign permissions

Every SQL Server securable has associated permissions that can be granted to a principal. Permissions in the Database Engine are managed at the server level assigned to logins and server roles, and at the database level assigned to database users and database roles. The model for Azure SQL Database has the same system for the database permissions, but the server level permissions aren’t available.

Most permission statements have the format:

AUTHORIZATION PERMISSION ON SECURABLE::NAME TO PRINCIPAL;

  • AUTHORIZATION must be GRANT, REVOKE or DENY.

  • 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.6. 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:

GRANT SELECT ON OBJECT::Region TO Jae;

GRANT CONTROL ON OBJECT::Region TO Jae;

GRANT SELECT ON SCHEMA::Customers TO Jae;

GRANT CONTROL ON SCHEMA::Customers TO Jae;

GRANT SELECT ON DATABASE::SalesDB TO Jae;

GRANT CONTROL ON DATABASE::SalesDB TO Jae;

3.7. 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.8. 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.8.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.8.2. Database-level principals

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

  • Database Role

  • Application Role

3.8.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.8.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.8.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.

Syntax

GO [count]

Arguments

count

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

Remarks

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;
GO
DECLARE @MyMsg VARCHAR(50)
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.
PRINT @MyMsg
GO

SELECT @@VERSION;
-- Yields an error: Must be EXEC sp_who if not first statement in
-- batch.
sp_who
GO

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
SELECT @@VERSION;
GO;

The GO statement is not a SQL statement; rather, it is a command recognized by the SQL Server Management Studio (SSMS), sqlcmd, and other SQL Server utilities.

In the context of these utilities, GO signals the end of a batch of statements and is used to separate batches of statements from each other. However, when you are performing database operations from ADO.NET (or any other context where you’re sending SQL statements directly to SQL Server), batch separation is not needed, and the GO statement is not recognized.

If you try to include the GO statement in your SQL strings in ADO.NET, you’ll likely encounter a SQL Server error that 'GO' is not a recognized built-in function name. Instead, just end each of your SQL statements with a semicolon (;).

6. Collation and Unicode support

Collations in SQL Server provide sorting rules, case, and accent sensitivity properties for your data. Collations that are used with character data types, such as char and varchar, dictate the code page and corresponding characters that can be represented for that data type.

Whether you’re installing a new instance of SQL Server, restoring a database backup, or connecting server to client databases, it’s important to understand the locale requirements, sorting order, and case and accent sensitivity of the data that you’re working with. To list the collations that are available on your instance of SQL Server, see sys.fn_helpcollations (Transact-SQL).

When you select a collation for your server, database, column, or expression, you’re assigning certain characteristics to your data. These characteristics affect the results of many operations in the database. For example, when you construct a query by using ORDER BY, the sort order of your result set might depend on the collation that’s applied to the database or dictated in a COLLATE clause at the expression level of the query.

Setting collations are supported at the following levels of an instance of SQL Server:

  • Server-level collations

    The default server collation is determined during SQL Server setup, and it becomes the default collation of the system databases and all user databases.

    To query the server collation for an instance of SQL Server, use the SERVERPROPERTY function:

    SELECT CONVERT(nvarchar(128), SERVERPROPERTY('collation'));
-- SQL_Latin1_General_CP1_CI_AS

    * SQL_Latin1_General_CP1_CI_AS is a collation setting in SQL Server. Here’s what each part of it means:

    • SQL indicates that it is a SQL Server-defined collation.

    • Latin1_General is the base language and alphabet, which in this case is the Latin 1 (Western European) alphabet.

    • CP1 stands for Code Page 1, which implies that the collation is using code page 1252. This page includes all ASCII characters and additional characters used in English and several other Western European languages.

    • CI stands for Case Insensitive. This means the collation doesn’t recognize case differences. When CI is specified, 'a' is considered equal to 'A'.

    • AS stands for Accent Sensitive. This means the collation does recognize accent differences. When AS is specified, 'résumé' is not equal to 'resume'.

    * If you want to use unicode (UTF-8) character sets in SQL Server, you can use the newer collations introduced starting from SQL Server 2019 onward. The new collations are named as _SC or _SC_UTF8.

    The SC stands for Supplementary Characters, which supports UCS-2 or UTF-16 characters. If you want to particularly use UTF-8, you can use _utf8 postfix.

    So if you need to define columns that can store unicode (UTF-8) strings from any language, you can use something like Latin1_General_100_CI_AS_SC_UTF8. _100 here corresponds to newer set of collations in SQL Server 2019.

    Please note that UTF-8 collations are only available to char and varchar data types, not to text data type. And if you’re dealing with wider unicode character sets and more complex translations, it may be better to use nchar or nvarchar data types with either UTF-16 or supplementary character collations.

    * The Case Insensitive (CI) property in a SQL Server collation does not modify the actual data stored in a cell of a table. If a username is stored as 'JohnDoe', it will be stored as 'JohnDoe' regardless of whether the collation is Case Sensitive (CS) or Case Insensitive (CI).

    The collation only affects how SQL Server compares and sorts that data during queries. In a Case Insensitive collation, SQL Server considers 'JohnDoe' and 'johndoe' to be equal. If the collation were Case Sensitive, 'JohnDoe' and 'johndoe' would be considered as two different values.

    So to put it diagrammatically:

    • With Case Insensitive (CI) collation :

      SELECT * FROM Users WHERE user_name = 'johndoe' will return 'JohnDoe', 'JOHNDOE', 'johndoe', etc.

    • With Case Sensitive (CS) collation :

      SELECT * FROM Users WHERE user_name = 'johndoe' will only return 'johndoe' and not 'JohnDoe' or 'JOHNDOE'.

    This comparison and sorting behaviour applies both to operations you perform in queries (like WHERE, ORDER BY, JOIN, etc.), as well as to indexes that SQL Server uses to optimize query performance.

    To query the server for all available collations, use the following fn_helpcollations() built-in function:

    SELECT * FROM sys.fn_helpcollations();

  • Database-level collations

    When you create or modify a database, you can use the COLLATE clause of the CREATE DATABASE or ALTER DATABASE statement to specify the default database collation. If no collation is specified, the database is assigned the server collation.

    You can retrieve the current collation of a database by using a statement similar to the following code sample:

    SELECT CONVERT (nvarchar(128), DATABASEPROPERTYEX('database_name', 'collation'));

  • Column-level collations

    When you create or alter a table, you can specify collations for each character-string column by using the COLLATE clause. If you don’t specify a collation, the column is assigned the default collation of the database.

  • Expression-level collations

    Expression-level collations are set when a statement is run, and they affect the way a result set is returned. This enables ORDER BY sort results to be locale-specific. To implement expression-level collations, use a COLLATE clause such as the following code sample:

    SELECT name FROM customer ORDER BY name COLLATE Latin1_General_CS_AI;

A locale is a set of information that’s associated with a location or a culture. The information can include the name and identifier of the spoken language, the script that’s used to write the language, and cultural conventions. Collations can be associated with one or more locales. For more information, see Locale IDs Assigned by Microsoft.

A code page is an ordered set of characters of a given script in which a numeric index, or code point value, is associated with each character. A Windows code page is typically referred to as a character set or a charset. Code pages are used to provide support for the character sets and keyboard layouts that are used by different Windows system locales.

Sort order specifies how data values are sorted. The order affects the results of data comparison. Data is sorted by using collations, and it can be optimized by using indexes.

Unicode is a standard for mapping code points to characters. Because it’s designed to cover all the characters of all the languages of the world, you don’t need different code pages to handle different sets of characters.

Storing data in multiple languages within one database is difficult to manage when you use only character data and code pages. It’s also difficult to find one code page for the database that can store all the required language-specific characters. Additionally, it’s difficult to guarantee the correct translation of special characters when they’re being read or updated by a variety of clients that are running various code pages. Databases that support international clients should always use Unicode data types instead of non-Unicode data types.

The code pages that a client uses are determined by the operating system (OS) settings. To set client code pages on the Windows operating system, use Regional Settings in Control Panel.

It would be difficult to select a code page for character data types that will support all the characters that are required by a worldwide audience. The easiest way to manage character data in international databases is to always use a data type that supports Unicode.

If you store character data that reflects multiple languages in SQL Server (SQL Server 2005 (9.x) and later), use Unicode data types (nchar, nvarchar, and ntext) instead of non-Unicode data types (char, varchar, and text).

Alternatively, starting with SQL Server 2019 (15.x), if a UTF-8 enabled collation (_UTF8) is used, previously non-Unicode data types (char and varchar) become Unicode data types using UTF-8 encoding. SQL Server 2019 (15.x) doesn’t change the behavior of previously existing Unicode data types (nchar, nvarchar, and ntext), which continue to use UCS-2 or UTF-16 encoding. For more information, see Storage differences between UTF-8 and UTF-16.

7. Database identifiers

The database object name is referred to as its identifier. Everything in Microsoft SQL Server can have an identifier. Servers, databases, and database objects, such as tables, views, columns, indexes, triggers, procedures, constraints, and rules, can have identifiers. Identifiers are required for most objects, but are optional for some objects such as constraints.

An object identifier is created when the object is defined. The identifier is then used to reference the object. For example, the following statement creates a table with the identifier TableX, and two columns with the identifiers KeyCol and Description:

CREATE TABLE TableX
(KeyCol INT PRIMARY KEY, Description nvarchar(80));

This table also has an unnamed constraint. The PRIMARY KEY constraint has no identifier.

The collation of an identifier depends on the level at which it is defined.

  • Identifiers of instance-level objects, such as logins and database names, are assigned the default collation of the instance.

  • Identifiers of objects in a database, such as tables, views, and column names, are assigned the default collation of the database.

    For example, two tables with names that differ only in case can be created in a database that has case-sensitive collation, but cannot be created in a database that has case-insensitive collation.

There are two classes of identifiers:

  • Regular identifiers

    Comply with the rules for the format of identifiers. Regular identifiers are not delimited when they are used in Transact-SQL statements.

    USE AdventureWorks2022;
GO
SELECT *
FROM HumanResources.Employee
WHERE NationalIDNumber = 153479919

  • Delimited identifiers

    Are enclosed in double quotation marks (") or brackets ([ ]). Identifiers that comply with the rules for the format of identifiers might not be delimited. For example:

    USE AdventureWorks2022;
GO
SELECT *
FROM [HumanResources].[Employee] --Delimiter is optional.
WHERE [NationalIDNumber] = 153479919 --Delimiter is optional.

    Identifiers that do not comply with all the rules for identifiers must be delimited in a Transact-SQL statement. For example: SQL

    USE AdventureWorks2022;
GO
CREATE TABLE [SalesOrderDetail Table] --Identifier contains a space and uses a reserved keyword.
(
    [Order] [int] NOT NULL,
    [SalesOrderDetailID] [int] IDENTITY(1,1) NOT NULL,
    [OrderQty] [smallint] NOT NULL,
    [ProductID] [int] NOT NULL,
    [UnitPrice] [money] NOT NULL,
    [UnitPriceDiscount] [money] NOT NULL,
    [ModifiedDate] [datetime] NOT NULL,
  CONSTRAINT [PK_SalesOrderDetail_Order_SalesOrderDetailID] PRIMARY KEY CLUSTERED
  ([Order] ASC, [SalesOrderDetailID] ASC)
);
GO

SELECT *
FROM [SalesOrderDetail Table]  --Identifier contains a space and uses a reserved keyword.
WHERE [Order] = 10;            --Identifier is a reserved keyword.

> What’s the recommend naming convention in SQL Server?

* Naming conventions in SQL Server are mostly a matter of personal preference or organizational standards. However, there are some commonly followed best practices that can increase the readability and manageability of your database. Here are a few recommended conventions:

  1. Meaningful and Descriptive Names: Names should clearly indicate what the object represents. For example, a table storing customer information should be named Customers or CustomerData, not Table1 or t1.

  2. Prefixes for Different Object Types: Using prefixes to denote the type of object can be useful. For example, stored procedures could be named with the sp_ prefix (like sp_GetCustomerDetails), views with vw_ prefix (like vw_CustomerOrders), and tables could be named without any prefix for clarity (like Orders).

  3. Avoid SQL Keywords: Avoid using SQL Server reserved words. This can cause confusion and sometimes lead to syntax errors.

  4. Be Consistent: Whichever convention you decide on, be consistent with it across all your SQL projects.

  5. PascalCase or snake_case: For multiple word names, use PascalCase (no spaces and each word capitalized like CustomerOrders) or snake_case (words separated by underscores like customer_orders). Avoid using spaces in object names.

  6. Singular or Plural Names: There’s much debate about whether to use singular or plural names for table names. Choose whichever makes the most logical sense in your context and be consistent.

  7. Avoid Special Characters and Spaces: With the exception of the underscore character, avoid using special characters, dots, hyphens, spaces etc. in names.

Remember, the most important goal of any naming convention is to make the code and database structure easily understandable and manageable for the developers and administrators working on it.

Note: In SQL Server, the sp_ prefix is reserved for system stored procedures. Some developers prefer not to use it for user-defined stored procedures to avoid potential naming conflicts and small performance penalties when the system searches the master database first for such procedures. They may use usp_ for user stored procedures instead.

8. 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

UPPERCASE

Transact-SQL keywords.

italic

User-supplied parameters of Transact-SQL syntax.

bold

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>.

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

server_name.[database_name].[schema_name].object_name

| 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

server_name.database_name.schema_name.object_name

Four-part name.

server_name.database_name..object_name

Schema name is omitted.

server_name..schema_name.object_name

Database name is omitted.

server_name…​object_name

Database and schema name are omitted.

database_name.schema_name.object_name

Server name is omitted.

database_name..object_name

Server and schema name are omitted.

schema_name.object_name

Server and database name are omitted.

object_name

Server, database, and schema name are omitted.

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

9. 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.

9.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.

9.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:

    CREATE DATABASE TestData;
GO

  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.

9.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.
9.1.2.1. 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
9.1.2.2. 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);
GO

9.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

1

Clamp

12.48

Workbench clamp

50

Screwdriver

3.17

Flat head

75

Tire Bar

Tool for changing tires.

3000

3

mm

Bracket

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.

9.1.3.1. 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')
GO

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;
GO

    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')
GO

  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.')
GO

  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
9.1.3.2. 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
GO

9.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.

9.1.4.1. 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
GO

  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
GO

  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
GO

  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
GO

  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
9.1.4.2. 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:

9.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.

9.1.5.1. 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.

CREATE VIEW vw_Names
   AS
   SELECT ProductName, Price FROM Products;
GO
9.1.5.2. Test the view

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

SELECT * FROM vw_Names;
GO
9.1.5.3. 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
   AS
   BEGIN
      -- 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;
   END
GO
9.1.5.4. 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;
GO

9.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.

9.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.

9.2.1.1. 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.

9.2.1.2. 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]
    FROM WINDOWS
    WITH DEFAULT_DATABASE = [TestData];
GO

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.
9.2.1.3. 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.

CREATE LOGIN shcooper
   WITH PASSWORD = 'Baz1nga' MUST_CHANGE,
   CREDENTIAL = RestrictedFaculty;
GO

9.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.

9.2.2.1. 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];
GO

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

Now, Mary has access to both SQL Server and the TestData database.

9.2.2.2. 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.

GRANT EXECUTE ON pr_Names TO Mary;
GO

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.
9.2.2.3. 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.

9.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;
GO

9.3.1. Revoke stored procedure permissions

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

REVOKE EXECUTE ON pr_Names FROM Mary;
GO

9.3.2. Drop permissions

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

    DROP USER Mary;
GO

  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];
GO

  3. Use the DROP statement to remove the store procedure pr_Names:

    DROP PROC pr_Names;
GO

  4. Use the DROP statement to remove the view vw_Names:

    DROP VIEW vw_Names;
GO

9.3.3. Delete table

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

    DELETE FROM Products;
GO

  2. Use the DROP statement to remove the Products table:

    DROP TABLE Products;
GO

9.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:

USE MASTER;
GO
DROP DATABASE TestData;
GO

10. References