The Transact-SQL language supports changing the structure of the following database objects, among others:
- Database
- Table
- Stored procedure
- View
- Schema
- Trigger
The following two sections describe, in turn, how you can alter a database and a table. The modification of the structure of each of the last four database objects is described in Chapters 8, 11, 12, and 14, respectively.
1. Altering a Database
The ALTER DATABASE statement changes the physical structure of a database. The Transact- SQL language allows you to change the following properties of a database:
- Add or remove one or more database files, log files, or filegroups
- Modify file or filegroup properties
- Set database options
- Change the name of the database using the sp_rename stored procedure (discussed a bit later, in the section “Altering a Table”)
The following subsections describe these different types of database alterations. In this section, we will also use the ALTER DATABASE statement to show how FILESTREAM data can be stored in files and filegroups and to explain the notion of contained databases.
1.1. Adding or Removing Database Files, Log Files, or Filegroups
The ALTER DATABASE statement allows the addition and removal of database files. The clauses ADD FILE and REMOVE FILE specify the addition of a new file and the deletion of an existing file, respectively. (Additionally, a new file can be assigned to an existing filegroup using the TO FILEGROUP option.)
Example 5.17 shows how a new database file can be added to the projects database.
Example 5.17
USE master;
GO
ALTER DATABASE projects
ADD FILE (NAME=projects_dat1,
FILENAME = ‘C:\temp\projects1.mdf’, SIZE = 10,
MAXSIZE = 100, FILEGROWTH = 5);
The ALTER DATABASE statement in Example 5.17 adds a new file with the logical name projects_dat1. Its initial size is 10MB, and this file will grow using units of 5MB until it reaches the upper limit of 100MB. (Log files are added in the same way as database files. The only difference is that you use the ADD LOG FILE clause instead of ADD FILE.)
The REMOVE FILE clause removes one or more files that belong to an existing database. The file can be a data file or a log file. The file cannot be removed unless it is empty.
The CREATE FILEGROUP clause creates a new filegroup, while DELETE FILEGROUP removes an existing filegroup from the system. Again, you cannot remove a filegroup unless it is empty.
1.2. Modifying File or Filegroup Properties
You can use the MODIFY FILE clause to change the following file properties:
- Change the logical name of a file using the NEWNAME option of the MODIFY FILE clause
- Increase the value of the SIZE property
- Change the FILENAME, MAXSIZE, or FILEGROWTH property
- Mark the file as OFFLINE
Similarly, you can use the MODIFY FILEGROUP clause to change the following filegroup properties:
- Change the name of a filegroup using the NAME option of the MODIFY FILEGROUP clause
- Mark the filegroup as the default filegroup using the DEFAULT option
- Mark the filegroup as read-only or read-write using the READ_ONLY or READ_ WRITE option, respectively
1.3. Setting Database Options
The SET clause of the ALTER DATABASE statement is used to set different database options. Some options must be set to ON or OFF, but most of them have a list of possible values. Each database option has a default value, which is set in the model database. Therefore, you can alter the model database to change the default values of specific options.
All options that you can set are divided into several groups. The most important groups are
- State options
- Auto options
- SQL options
The state options control the following:
- User access to the database (options are SINGLE_USER, RESTRICTED_USER, and MULTI_USER)
- The status of the database (options are ONLINE, OFFLINE, and EMERGENCY)
- The read/write modus (options are READ_ONLY and READ_WRITE)
The auto options control, among other things, the type of the database shutdown (the option AUTO_CLOSE) and how index statistics are built (the options AUTO_CREATE_ STATISTICS and AUTO_UPDATE_STATISTICS).
The SQL options control the ANSI compliance of the database and its objects. All SQL options can be edited using the DATABASEPROPERTYEX function and modified using the ALTER DATABASE statement.
1.4. Storing FILESTREAM Data
Chapter 4 explained what FILESTREAM data is and the reason for using it. This section discusses how FILESTREAM data can be stored as a part of a database. Before you can store FILESTREAM data, you have to enable the system for this task. The following subsection explains how to enable the operating system and the instance of your database system.
Enabling FILESTREAM Storage FILESTREAM storage has to be enabled at two levels:
- For the Windows operating system
- For the particular database server instance
You use SQL Server Configuration Manager to enable FILESTREAM storage at the OS level. To open SQL Server Configuration Manager, type SQLServerManager15.msc in the Search field for SQL Server 2019 or type SQLServerManager14.msc for SQL Server 2017.
In the list of services, right-click SQL Server Services, and then click Open. In the SQL Server Configuration Manager snap-in, choose your instance of the Database Engine, right-click the instance, and then click Properties. In the SQL Server Properties dialog box, click the FILESTREAM tab. Check the Enable FILESTREAM for Transact-SQL Access check box. If you want to read and write FILESTREAM data from Windows, check the Enable FILESTREAM for File I/O Access check box. Enter the name of the Windows share in the Windows Share Name box and click Apply to apply the changes. SQL Server Configuration Manager creates a new share with the specified name for the instance.
NOTE You need to be Windows Administrator on a local system and have administrator (sysadmin) rights to enable FILESTREAM storage. You also need to restart the instance for the changes to take effect.
The next step is to enable FILESTREAM storage for a particular database server instance. SQL Server Management Studio will be used to show this task. Right-click the instance in Object Explorer, click Properties, select Advanced in the left pane, and set Filestream Access Level to one of the following levels:
- Disabled FILESTREAM storage is not allowed.
- Transact-SQL Access Enabled FILESTREAM data can be accessed using T-SQL statements.
- Full Access Enabled FILESTREAM data can be accessed using T-SQL as well as from the OS.
The alternative way is to use the sp_configure system procedure with the filestream access level option:
EXEC sp_configure filestream_access_level, 2
RECONFIGURE
The last parameter has three different values, which correspond to the three levels mentioned previously. Therefore, the value 2 in this code means “Full Access Enabled.”
Adding a File to the Filegroup After you enable FILESTREAM storage for your instance, you can use the ALTER DATABASE statement first to create a filegroup for FILESTREAM data and then to add a file to that filegroup, as shown in Example 5.18. (Of course, you can also use the CREATE DATABASE statement to accomplish this task.)
NOTE Before you execute the statement in Example 5.18, change the name of the file in the FILENAME clause.
Example 5.18
USE master;
ALTER DATABASE sample
ADD FILEGROUP Employee_FSGroup CONTAINS FILESTREAM;
GO
ALTER DATABASE sample
ADD FILE (NAME= employee_FS,
FILENAME = ‘C:\temp\emp_FS’)
TO FILEGROUP Employee_FSGroup;
The first ALTER DATABASE statement in Example 5.18 adds a new filegroup called Employee_FSGroup to the sample database. The CONTAINS FILESTREAM option tells the system that this filegroup will contain only FILESTREAM data. The second ALTER DATABASE statement adds a new file to the existing filegroup.
Now you can create a table with one or more FILESTREAM columns. Example 5.19 shows the creation of a table with a FILESTREAM column.
Example 5.19
USE sample;
CREATE TABLE employee_info
(id UNIQUEIDENTIFIER ROWGUIDCOL NOT NULL UNIQUE,
filestream_data VARBINARY(MAX) FILESTREAM NULL)
The employee_info table in Example 5.19 contains the filestream_data columns, which must be of the VARBINARY(max) data type. Such a column includes the FILESTREAM attribute, indicating that a column should store data in the FILESTREAM filegroup. All tables that store FILESTREAM data require the existence of a UNIQUE ROWGUILDCOL. For this reason, the employee_info table has the id column, defined using these two attributes.
To insert data into a FILESTREAM column, you use the standard INSERT statement, which is described in Chapter 7. Also, to read data from a FILESTREAM column, you can use the standard SELECT statement, which is described in Chapter 6. The detailed description of read and write operations on FILESTREAM data is outside the scope of this book.
1.5. Contained Databases
One of the significant problems with SQL Server databases is that they cannot be exported (or imported) easily. As you already know from this chapter, you can attach and detach a database, but many important parts and properties of the attached database will be missing. (The main problem in such a case is database security in general and existing logins in particular, which are usually incomplete or wrong after the move.)
Microsoft solved such problems by introducing contained databases. A contained database comprises all database settings and data required to specify the database and is isolated from the instance of the Database Engine on which it is installed. In other words, this form of databases has no configuration dependencies on the instance and can easily be moved from one instance of SQL Server to another.
Generally, there are three forms of databases in relation to containment:
- Fully contained databases
- Partially contained databases
- Noncontained databases
Fully contained databases are those where database objects cannot cross the application boundary. (An application boundary defines the scope of an application. For instance, user- defined functions are within the application boundary, while functions related to server instances are outside it.)
Partially contained databases allow database objects to cross the application boundary, while noncontained databases do not support the notion of an application boundary at all.
Let’s take a look at how you can create a partially contained database. If a database called my_sample already exists, and it is created as a noncontained database (using the CREATE DATABASE statement, for instance), you can use the ALTER DATABASE statement to alter it to partial containment, as shown in Example 5.20.
Example 5.20
USE master;
EXEC sp_configure ‘show advanced options’ , 1;
RECONFIGURE WITH OVERRIDE;
EXEC sp_configure ‘contained database authentication’ , 1;
RECONFIGURE WITH OVERRIDE;
ALTER DATABASE my_sample SET CONTAINMENT = PARTIAL;
EXEC sp_configure ‘show advanced options’ , 0;
RECONFIGURE WITH OVERRIDE;
The ALTER DATABASE statement modifies the containment of the my_sample database from noncontained to partially contained. This means that the database system allows you to create both contained and noncontained database objects for the my_sample database. (All other statements in Example 5.20 just set the scene for the ALTER DATABASE statement.)
NOTE sp_configure is a system procedure that can be used to, among other things, change advanced configuration options, such as ‘contained database authentication’. To make changes to advanced configuration options, you first have to set the value of ‘show advanced options’ to 1 and reconfigure the system. At the end of Example 5.20, this option has been set again to its default value (0). The sp_configure system procedure is discussed in detail in the section “System Procedures” in Chapter 9.
For the my_sample database, you can now create a user that is not tied to a login. This will be described in detail in the “Managing Authorization and Authentication of Contained Databases” section of Chapter 12.
2. Altering a Table
The ALTER TABLE statement modifies the schema of a table. The Transact-SQL language allows the following types of alteration:
- Add or drop one or more new columns
- Modify column properties
- Add or remove integrity constraints
- Enable or disable constraints
- Rename tables and other database objects
The following sections describe these types of changes.
2.1. Adding or Dropping a Column
You can use the ADD clause of the ALTER TABLE statement to add a new column to the existing table. Only one column can be added for each ALTER TABLE statement. Example 5.21 shows the use of the ADD clause.
Example 5.21
USE sample;
ALTER TABLE employee
ADD telephone_no CHAR(12) NULL;
The ALTER TABLE statement in Example 5.21 adds the column telephone_no to the employee table. The Database Engine populates the new column either with NULL or IDENTITY values or with the specified default. For this reason, the new column must either be nullable or have a default constraint.
NOTE There is no way to insert a new column in a particular position in the table. The column, which is added using the ADD clause, is always inserted at the end of the table.
The DROP COLUMN clause provides the ability to drop an existing column of the table, as shown in Example 5.22.
Example 5.22
USE sample;
ALTER TABLE employee
DROP COLUMN telephone_no;
The ALTER TABLE statement in Example 5.22 removes the telephone_no column, which was added to the employee table with the ALTER TABLE statement in Example 5.21.
2.2. Modifying Column Properties
The Transact-SQL language supports the ALTER COLUMN clause of ALTER TABLE to modify properties of an existing column. The following column properties can be modified:
- Data type
- Nullability
Example 5.23 shows the use of the ALTER COLUMN clause.
Example 5.23
USE sample;
ALTER TABLE department
ALTER COLUMN location CHAR(25) NOT NULL;
The ALTER TABLE statement in Example 5.23 changes the previous properties (CHAR(30), nullable) of the location column of the department table to new properties (CHAR(25), not nullable).
2.3. Adding or Removing Integrity Constraints
A new integrity constraint can be added to a table using the ALTER TABLE statement and its option called ADD CONSTRAINT. Example 5.24 shows how you can use the ADD CONSTRAINT clause in relation to a check constraint.
Example 5.24
USE sample;
CREATE TABLE sales
(order_no INTEGER NOT NULL,
order_date DATE NOT NULL,
ship_date DATE NOT NULL);
GO
ALTER TABLE sales
ADD CONSTRAINT order_check CHECK(order_date <= ship_date);
The CREATE TABLE statement in Example 5.24 creates the sales table with two columns of the DATE data type: order_date and ship_date. The subsequent ALTER TABLE statement defines an integrity constraint named order_check, which compares both of the values and prevents the INSERT statement from successfully completing if the shipping date is earlier than the order date.
Example 5.25 shows how you can use the ALTER TABLE statement to additionally define the primary key of a table.
Example 5.25
USE sample;
ALTER TABLE sales
ADD CONSTRAINT primaryk_sales PRIMARY KEY(order_no);
The ALTER TABLE statement in Example 5.25 declares the primary key for the sales table. Each integrity constraint can be removed using the DROP CONSTRAINT clause of the ALTER TABLE statement, as shown in Example 5.26.
Example 5.26
USE sample;
ALTER TABLE sales
DROP CONSTRAINT order_check;
The ALTER TABLE statement in Example 5.26 removes the CHECK clause called order_ check, specified in Example 5.24.
NOTE You cannot use the ALTER TABLE statement to modify a definition of an integrity constraint.
In this case, the constraint must be re-created—that is, dropped and then added with the new definition.
2.4. Enabling or Disabling Constraints
As previously stated, an integrity constraint always has a name that can be explicitly declared using the CONSTRAINT option or implicitly declared by the system. The name of all (implicitly or explicitly) declared constraints for a table can be viewed using the system procedure sp_helpconstraint.
A constraint is enforced by default during future insert and update operations. Additionally, the existing values in the column(s) are checked against the constraint. Otherwise, a constraint that is created with the WITH NOCHECK option is disabled in the second case. In other words, if you use the WITH NOCHECK option, the constraint will be applied only to future insert and update operations. (Both options, WITH CHECK and WITH NOCHECK, can be applied only with the CHECK and FOREIGN KEY clauses.)
Example 5.27 shows how you can disable all existing constraints for a table.
Example 5.27
USE sample;
ALTER TABLE sales
NOCHECK CONSTRAINT ALL;
In Example 5.27, the keyword ALL is used to disable all the constraints on the sales table.
NOTE Use of the NOCHECK option is not recommended. Any constraint violations that are suppressed may cause future updates to fail.
2.5. Renaming Tables and Other Database Objects
The sp_rename system procedure modifies the name of an existing table (and any other existing database objects, such as databases, views, or stored procedures). Examples 5.28 and 5.29 show the use of this system procedure.
Example 5.28
USE sample;
EXEC sp_rename @objname = department, @newname = subdivision
Example 5.28 renames the department table to subdivision.
Example 5.29
USE sample;
EXEC sp_rename @objname = ‘sales.order_no’ , @newname = ordernumber
Example 5.29 renames the order_no column in the sales table. If the object to be renamed is a column in a table, the specification must be in the form table_name.column_name.
NOTE Do not use the sp_rename system procedure, because changing object names can influence other database objects that reference them. Drop the object and re-create it with the new name.
Source: Petkovic Dusan (2020), Microsoft SQL Server 2019: A Beginner’s Guide, Seventh Edition-McGraw-Hill Education.