Enhanced DML and DDL statements

In this section, you will explore enhancements in Data Manipulation Language (DML) and Data Definition Language (DDL) that are not part of new features or improved features from previous SQL Server versions.

The section starts with a small syntax extension that you will use often in the code examples in this book.

The conditional DROP statement (DROP IF EXISTS)

With a conditional DROP statement, you can avoid getting an exception if the object you want to drop does not exist. If, for instance, the T1 table has already been removed or it was not created at all, the following statement will fail:

DROP TABLE dbo.T1; 

Here is the error message:

Msg 3701, Level 11, State 5, Line 5
Cannot drop the table 'dbo.T1', because it does not exist or you do not have permission.

SQL Server 2016 introduces the conditional DROP statement for most of the database objects. The conditional DROP statement is a DROP statement extended with the IF EXISTS part. Repeat the preceding command with this extended syntax:

DROP TABLE IF EXISTS dbo.T1; 

You can execute this statement any number of times and you will not get an error. To achieve this prior to SQL Server 2016, you had to check the existence of the object before you removed it, as shown in this code example:

IF OBJECT_ID('dbo.T1','U') IS NOT NULL 
  DROP TABLE dbo.T1;  

You had to write one code line more and in addition, it is also error prone---you have to write the name of the object twice. It's not a big deal, but this new form is shorter and is not error prone.

You can use the following code to remove the stored procedure dbo.P1 from the system:

DROP PROCEDURE IF EXISTS dbo.P1; 

As mentioned earlier, you could use the conditional DROP statement in SQL Server 2016 to remove most of the database objects. The following objects are supported: AGGREGATE, ASSEMBLY, COLUMN, CONSTRAINT, DATABASE, DEFAULT, FUNCTION, INDEX, PROCEDURE, ROLE, RULE, SCHEMA, SECURITY POLICY, SEQUENCE, SYNONYM, TABLE, TRIGGER, TYPE, USER, and VIEW.

If you want, for instance, to remove a partitioned function or schema, DROP IF EXISTS won't work. The following command will fail:

DROP PARTITION FUNCTION IF EXISTS PartFunc1; 

And here is the error message:

Msg 156, Level 15, State 1, Line 1
Incorrect syntax near the keyword 'IF'

To (conditionally) remove a partitioned function, you still need to write your own code to check the existence of the object.

How does IF EXISTS work? It simply suppresses the error message. This is exactly what you need if the reason for the error is the nonexistence of the object. However, if the user who wants to drop the object does not have appropriate permission, you would expect an error message. The command is executed successfully and the caller does not get an error regardless of the object's existence and user permissions! Here are the results when a user wants to drop an object using the conditional DROP statement:

• The object exists; user has permissions: When the object is removed, everything is fine
• The object does not exist; user has permissions: There are no error messages displayed
• The object exists; user does not have permissions: When the object is not removed, no error messages are displayed. The caller does not get that the object still exists; its DROP command has been executed successfully!
• The object does not exist; user does not have permissions: There are no error messages displayed.

You can read more about this inconsistency in the blog post DROP IF EXISTS aka D.I.E . at https://milossql.wordpress.com/2016/07/04/drop-if-exists-aka-d-i-e/.

This enhancement is handy; it helps you to abbreviate your code and it is intensively used by consultants, trainers, and conference speakers. They usually create database objects to demonstrate a feature, code technique, or behavior and then drop them from the system. And they do this again and again. However, conditional DROP statements will not be used so often in production systems. How often do you remove database objects from SQL Server? Very rarely, right? When you perform a cleanup or remove some intermediate database objects. In most cases, you add new objects or change the existing ones.

Therefore, I would like to see similar a implementation for the object's creation or updating. To extend Transact-SQL syntax with, for instance, the CREATE OR ALTER or CREATE OR REPLACE command. This would be more important for script deployment than DROP statement extensions. If you want to update a stored procedure with the latest version and you don't know whether the previous version has been installed or not, or you simply execute the same script twice without errors, the following code must be used:

IF OBJECT_ID(N'dbo.uspMyStoredProc','P') IS NULL 
  EXEC('CREATE PROCEDURE dbo.uspMyStoredProc AS SELECT NULL'); 
GO 
ALTER PROCEDURE dbo.uspMyStoredProc 
AS... 

This piece of code is error prone, awkward and even uses dynamic SQL because CREATE PROC needs to be the first statement in a batch. CREATE or ALTER PROCEDURE would be handy. And some other vendors support it.

More often, you use it to create or alter objects than to remove them from a production system; therefore, it would be more useful to implement this extension. I hope we'll see it in the next SQL Server version.

CREATE OR ALTER

At the time of writing this chapter, SQL Server 2016 RTM has been released. In the meantime, Microsoft came with the SQL Server 2016 Service Pack 1. This package not only includes hotfixes from the RTM version, but also some new features. One of them is exactly described as the CREATE OR ALTER statement. It creates an object if it does not exist, or alters it if it is already there. You can use it with stored procedures, functions, view, and triggers. Here is an example of creating or altering a scalar user-defined function:

CREATE OR ALTER FUNCTION dbo.GetWorldsBestCityToLiveIn() 
RETURNS NVARCHAR(10) 
AS 
BEGIN 
    RETURN N'Vienna'; 
END 

We were waiting for a long time for this feature and it is most probably the first feature you will adopt in SQL Server 2016.

Online Alter Column

Sometimes you might need to change the attributes of a table column, for instance, to increase the column capacity due to changed requirements, increased data amount, or lack of data capacity planning. Here are the typical actions for altering a column in a SQL Server table:

• Change the data type: This is usually when you come close to the maximum value supported by the actual data type (typically from smallint to int, or from int to bigint)
• Change the size: This is a common case for poorly planned string columns; the current column size cannot accept all the required data
• Change the precision: This is when you need to store more precise data; usually due to changed requirements
• Change the collation: This is when you have to use a different (usually case sensitive) collation for a column due to changed requirements
• Change the null-ability: This is when the requirements are changed

To demonstrate what happens when you perform an alter column action, you first need to create a sample table. Run the following code to accomplish this:

USE WideWorldImporters; 
DROP TABLE IF EXISTS dbo.Orders; 
CREATE TABLE dbo.Orders( 
id INT IDENTITY(1,1) NOT NULL, 
custid INT NOT NULL, 
orderdate DATETIME NOT NULL, 
amount MONEY NOT NULL, 
rest CHAR(100) NOT NULL DEFAULT 'test', 
CONSTRAINT PK_Orders PRIMARY KEY CLUSTERED (id ASC) 
); 
GO 

To populate the table efficiently, you can use the GetNums function created by Itzik Ben-Gan. The function is available at http://tsql.solidq.com/SourceCodes/GetNums.txt. Here is the function definition:

------------------------------------------------------------- 
-- ? Itzik Ben-Gan 
------------------------------------------------------------- 
CREATE OR ALTER FUNCTION dbo.GetNums(@low AS BIGINT, @high AS BIGINT) RETURNS TABLE 
AS 
RETURN 
  WITH 
    L0   AS (SELECT c FROM (SELECT 1 UNION ALL SELECT 1) AS D(c)), 
    L1   AS (SELECT 1 AS c FROM L0 AS A CROSS JOIN L0 AS B), 
    L2   AS (SELECT 1 AS c FROM L1 AS A CROSS JOIN L1 AS B), 
    L3   AS (SELECT 1 AS c FROM L2 AS A CROSS JOIN L2 AS B), 
    L4   AS (SELECT 1 AS c FROM L3 AS A CROSS JOIN L3 AS B), 
    L5   AS (SELECT 1 AS c FROM L4 AS A CROSS JOIN L4 AS B), 
    Nums AS (SELECT ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) AS rownum 
             FROM L5) 
  SELECT TOP(@high - @low + 1) @low + rownum - 1 AS n 
  FROM Nums 
  ORDER BY rownum; 

Now you can run the following code to populate the table with 10 million rows:

INSERT INTO dbo.Orders(custid,orderdate,amount) 
SELECT  
  1 + ABS(CHECKSUM(NEWID())) % 1000 AS custid, 
  DATEADD(minute,    -ABS(CHECKSUM(NEWID())) % 5000000,  '20160630') AS orderdate, 
  50 + ABS(CHECKSUM(NEWID())) % 1000 AS amount 
FROM dbo.GetNums(1,10000000); 

Now, once you have created and populated the table, suppose you have a requirement to change the data type for the column amount to decimal. To see what happens during the alter column action, you need to open two connections. In the first connection, you will have the code to change the data type of the column:

ALTER TABLE dbo.Orders ALTER COLUMN amount DECIMAL(10,2) NOT NULL; 

In the second one, you will simply try to return the last two rows from the table just to check whether the table is available for querying. Use the following code for the query in the second connection:

USE WideWorldImporters; 
SELECT TOP (2) id, custid, orderdate, amount 
FROM dbo.Orders ORDER BY id DESC; 

Now, execute the code from the first connection and then from the second. You can see that both commands are running. Actually, the ALTER COLUMN command is running, while the second query is simply waiting---it is blocked by the ALTER command. During the change of the data type, the table is not available for querying. You can see additional details; you would need to establish a third connection and put the following code there (you need to replace 66 with the session ID from the second connection) and repeat the previous two steps by changing the data type of the column amount to money.

SELECT request_mode, request_type, request_status, request_owner_type 
FROM sys.dm_tran_locks WHERE request_session_id = 66; 

The following is the result of this command:

request_mode request_type request_status request_owner_type
------------ ------------- -------------- ---------------- 
S LOCK GRANT SHARED_TRANSACTION_WORKSPACE 
IS LOCK WAIT TRANSACTION

You can see that the query could not get IS lock because the ALTER column action is performed as a transaction. Therefore, the table is not available for querying and the query from the second connection has to wait until the ALTER command is done.

Note

You might think that a query with the NOLOCK hint would return results even when an alter column action is performed, because NOLOCK obviously means there is no lock at the table. This is not completely true. It is true that shared lock or intentional shared lock are not acquired, but even with NOLOCK statements we need to acquire a stability schema lock. You can repeat all the three steps with a small modification in the second query to include the NOLOCK hint and the actions will end up with the same results and behavior. The only difference is that in the result set of the third connection, instead of IS the mentioned Sch-S appears.

This behavior is analog to creating nonclustered indexes offline. While creating an index online has been available since 2005, altering column was the only operation that was offline until SQL Server 2016.

When you specify the ONLINE = ON option, SQL Server 2016 creates a new shadow table with the requested change, and when it's finished, it swaps metadata with very short schema locks. This leaves the table available, even for changes, except those which could create a dependency for the altering column.

Now you will repeat the three steps from the preceding example (assuming the column amount has money data type), but this time with the ONLINE = ON option. You need to modify the command from the first connection to the following code:

USE WideWorldImporters; 
ALTER TABLE dbo.Orders ALTER COLUMN amount DECIMAL(10,2) NOT NULL WITH (ONLINE = ON); 

The code in the second connection does not need to be changed:

SELECT TOP (2) id, custid, orderdate, amount 
FROM dbo.Orders ORDER BY id DESC; 

Now, execute the command all over again and then run the query from the second connection. You can see that the first command is running, and that the query from the second connection instantly returns results. The table is available for querying although the data type for one column is being changed. This is a very important feature for systems that need to be continually available.

Altering online capabilities does not remove the usual limitations for changing column attributes. If a column is used in an index or expression in a filtered index or filtered statistics, you still cannot change its data type.

However, with the ONLINE = ON option, you can alter a column even if user-created statistics on this column exist. This was not possible prior to SQL Server 2016 (and it is still not possible in the offline mode). To demonstrate this, you will create a user statistic object on the column amount:

USE WideWorldImporters; 
CREATE STATISTICS MyStat ON dbo.Orders(amount); 

An attempt to change the data type of the column amount should fail:

ALTER TABLE dbo.Orders ALTER COLUMN amount DECIMAL(10,3) NOT NULL; 

This command immediately generates the following error message:

Msg 5074, Level 16, State 1, Line 76
The statistics 'MyStat' is dependent on column 'amount'.
Msg 4922, Level 16, State 9, Line 76
ALTER TABLE ALTER COLUMN amount failed because one or more objects access this column. 

However, when you specify the ONLINE = ON option, the same command will work:

ALTER TABLE dbo.Orders ALTER COLUMN amount DECIMAL(10,3) NOT NULL WITH (ONLINE = ON); 

The statistics are available to the query optimizer during the command execution; it is invalidated after the change is done and it must be updated manually.

Similar to the online index (re)build option, this excellent feature is available in the Enterprise Edition only.

TRUNCATE TABLE

The TRUNCATE TABLE statement is the most efficient way to remove all rows from a table. Logically, this statement is identical to the DELETE statement without the WHERE clause, but unlike the DELETE statement, when the TRUNCATE TABLE has been issued, SQL Server does not log inpidual row deletion in the transaction log. Therefore, the TRUNCATE TABLE statement is significantly faster. This performance difference can be very important with large tables. However, if you want to enjoy its efficiency, you have to remove all rows from a table. Even if the table is partitioned, you still need to remove all rows from all the partitions. Well, unless you have SQL Server 2016.

In SQL Server 2016, the TRUNCATE TABLE statement has been extended so that you can specify the partitions from which rows have to be removed. You can specify the comma-separated list or the range of partition numbers. Here is a code example showing how to remove all rows from the partitions 1, 2, and 4.

TRUNCATE TABLE dbo.T1 WITH (PARTITIONS (1, 2, 4));   

In the next example, you will see how to specify the range of partition numbers. From a table with the maximum number of supported partitions, you want to remove a lot of data, but not all of it—you want to leave data in partitions 1 and 2. Here is the code that implements this request:

TRUNCATE TABLE dbo.T2 WITH (PARTITIONS (3 TO 15000)); 

You can also combine two input formats in one expression. The following code removes all rows from a table with eight partitions, except from the partitions 1 and 3:

TRUNCATE TABLE dbo.T1 WITH (PARTITIONS (2, 4 TO 8)); 

Specifying partitions in the TRUNCATE TABLE statement is possible even if the database is not in compatibility level 130.

To simulate truncate table for a specific partition in previous SQL Server versions, you need to perform the following steps:

1. Create a staging table with the same indexes as in a partitioned table.
2. Using the SWITCH PARTITION statement to move data from the partitioned to the staging table.
3. Removing the staging table from the system.

Now, you need a single and very efficient statement—a nice and handy feature.

Maximum key size for nonclustered indexes

In previous SQL Server versions, the total size of all index keys could not exceed the limit of 900 bytes. You were actually allowed to create a nonclustered index, even if the sum of the maximum length of all its key columns exceeded this limit. The limit affects only columns used as index keys; you can use very large columns in a nonclustered index as included columns.

To demonstrate this, we will create a sample table. Note that this code should be executed in a SQL Server 2014/2012/2008 instance:

USE tempdb; 
CREATE TABLE dbo.T1(id INT NOT NULL PRIMARY KEY CLUSTERED, c1 NVARCHAR(500) NULL, c2 NVARCHAR(851) NULL); 

As you can see in the code, the maximal data length of the column c1 is 1000 bytes. Let's now try to create a nonclustered index on this column:

CREATE INDEX ix1 ON dbo.T1(c1); 

Since the table is empty, the command has been executed successfully with no errors, but with the following warning message:

Warning! The maximum key length is 900 bytes. The index 'ix1' has maximum length of 1000 bytes. For some combination of large values, the insert/update operation will fail

As the message says, you can live with the index in harmony if the size of the actual data in the index columns does not exceed the limit of 900 bytes. The query optimizer will even use it in execution plans and it will behave as a normal index. Adding a row with data within the maximum key length will be successful, as shown in the following code:

INSERT INTO dbo.T1(id,c1, c2) VALUES(1, N'Mila', N'Vasilije'); 

However, when you try to insert or update a row with data longer than the key size limit, the statement will fail:

INSERT INTO dbo.T1(id,c1, c2) VALUES(2,REPLICATE('Mila',113), NULL); 

This action in SQL Server 2014 ends up with an error message and the INSERT statement fails, as follows:

Msg 1946, Level 16, State 3, Line 7
Operation failed. The index entry of length 904 bytes for the index 'ix1' exceeds the maximum length of 900 bytes.

In SQL Server 2016, the behavior remains the same with the difference that the maximum index key size for nonclustered indexes has been increased from 900 to 1,700 bytes. Let's repeat the previous steps, but this time in an instance running SQL Server 2016.

DROP TABLE IF EXISTS dbo.T1; 
CREATE TABLE dbo.T1(id INT NOT NULL PRIMARY KEY CLUSTERED, c1 NVARCHAR(500) NULL, c2 NVARCHAR(851) NULL); 
GO 
CREATE INDEX ix1 ON dbo.T1(c1); 

There is no warning after this action, since the new limit is 1,700 bytes. However, when you add an index on the c2 column using this code:

CREATE INDEX ix2 ON dbo.T1(c2); 

You get the well-known warning message, but with a different limit:

Warning! The maximum key length for a nonclustered index is 1700 bytes. The index 'ix2' has maximum length of 1702 bytes. For some combination of large values, the insert/update operation will fail.

As you can see, the only difference is the different maximum number of bytes.

Note

The maximum key size for clustered indexes remains 900 bytes. For memory-optimized tables, the limit is 2,500 bytes.

Now, you can index wider columns than you could in previous versions. For instance, a text column with 500 Unicode characters can be used as a key in a nonclustered index in SQL Server 2016.