Chapter 10 Inserting, Updating, and Deleting Data

Question 1

What are the 4 ways to insert data into your tables?

Answer 1

(1) INSERT VALUES, (2) INSERT SELECT, (3) INSERT EXEC, (4) SELECT INTO

Question 2

What is the INSERT VALUES statement?

Answer 2

With the INSERT VALUES statement, you can insert one or more rows into a target table based on value expressions, e.g.

INSERT INTO Sales.MyOrders(custid, empid, orderdate)
VALUES(2, 19, ‘20120620’);

Question 3

With INSERT VALUES, why should you specify the target column names after the table name?

Answer 3

Specifying the target column names after the table name is optional but considered a best practice because it allows you to control the source value to target column association. They can be ordered irrespective of the order in which the columns are defined in the table. Without the target column list, you must specify the values in column definition order. If the underlying table definition changes, but the INSERT statements aren’t modified, this can result in errors or values written to the wrong column.

Question 4

How does INSERT VALUES handle values for columns with the INDENTITY property?

Answer 4

A value for INDENTITY columns is usually not specified in the INSERT VALUES statement because the property generates the value for the column automatically. If you want to provide your own value instead of letting the IDENTITY property do it for you, you need to first turn on a session option called IDENTITY_INSERT, e.g.

SET IDENTITY_INSERT ON;
…
SET IDENTITY_INSERT OFF;

Note that in order to use this option, you need quite strong permissions. You need to be the owner of the table or have ALTER permissions on the table.

Question 5

How does INSERT VALUES handle values for columns with default constraints?

Answer 5

The INSERT VALUES statement can specify a value for this column explicitly and override the default; otherwise, SQL Server will use the default expression to generate that value.

Another way to achieve the same behavior is to specify the column name in the names list and the keyword DEFAULT in the respective element in the VALUES list.

Question 6

If you don’t specify a value for a column within an INSERT VALUES statement, what does SQL Server do?

Answer 6

SQL Server will first check whether the column get it’s value automatically - for example from an IDENTITY or a default constraint. If that’s not the case, SQL Server will check whether the column allows NULLs, in which case it will assume a NULL. If that’s not the case, SQL Server will generate an error.

Question 7

Can you insert multiple rows with INSERT VALUES?

Answer 7

Yes. Simply separate the rows with commas:

INSERT INTO Sales.MyOrders(custid, empid, orderdate)
VALUES (2, 11, ‘20120620’),
(5,13, ‘20120621’),
(6,12, ‘20120622’);

Note that when using multiple rows, the entire statement is considered one transaction. If any row fails to enter the target table, the entire statement will fail and no row is inserted.

Question 8

What is the INSERT SELECT statement?

Answer 8

The INSERT SELECT statement inserts the result set returned by a query into the specified target table. INSERT SELECT also supports optionally specifying the target column names. Also, you can omit the columns that get their values automatically from an IDENTITY property, default constraint, or when allowing NULLs.

e.g.

SET IDENTITY_INSERT Sales.MyOrders ON;

INSERT INTO Sales.MyOrders (orderid, custid, empid, orderdate)
SELECT orderid, custid, empid, orderdate
FROM Sales.Orders
WHERE shipcountry=’Norway’

SET IDENTITY_INSERT Sales.MyOrders OFF

Question 9

Does INSERT SELECT result in less logging than other insert operations?

Answer 9

Yes. In certain conditions, the INSERT SELECT statement can benefit from minimal logging which could result in improved performance when compared to a fully logged operation.

Question 10

What is the INSERT EXEC statement?

Answer 10

With the INSERT EXEC statement, you can insert the result set (or sets) returned by a dynamic batch or a stored procedure into the specified target table. INSERT EXEC supports specifying an optional target column list and allows omitting columns that accept their values automatically.

e.g.

SET IDENTITY_INSERT Sales.MyOrders ON;

INSERT INTO Sales.MyOrders(orderid, custid, empid, orderdate)
EXEC Sales.OrdersForCountry
@country = ‘Portugal’

SET IDENTITY_INSERT Sales.MyOrders OFF;
SET IDENTITY_INSERT Sales.MyOrders OFF;

Question 11

Does INSERT EXEC work when more than one query is returned?

Answer 11

Yes. INSERT EXEC works even when the source dynamic batch or stored procedure has more than one query. But that’s as long as all queries return result sets that are compatible with the target table’s definition.

Question 12

What is the SELECT INTO statement?

Answer 12

The SELECT INTO statement involves a query (SELECT) and a target table (INTO). It creates the target table based on the definition of the source query and inserts the result rows from the query into that table.

The statement copies from the source some aspects of the data definition such as column names, types, nullability, and IDENTITY property. Other aspects like indexes, constraints, triggers, permissions are not copied. If you want to include the latter aspects, you need to script them from the source and apply them to the target manually.

e.g.

SELECT orderid, custid, orderdate
INTO Sales.MyOrders
FROM Sales.Orders
WHERE shipcountry=’norway’

Question 13

When using SELECT INTO, do you have direct control over the definition of the target?

Answer 13

No. You don’t have direct control over the definition of the target. If you want target columns to be defined differently than the source, you need to apply some manipulation.

For example, if the source orderid column is IDENTITY and you don’t want the target column to have this property, you can apply a manipulation like orderid + 0 AS orderid. This will remove the IDENTITY property, but will cause the target column to allow NULLs. If you want the target column to be defined as not allowing NULLs, you need to use the ISNULL function to return a non-NULL value in case the source is NULL, e.g. ISNULL(orderid + 0, -1) AS orderid.

If you want the target column’s type to be different than the source, you can use the CAST/CONVERT functions, but remember that this will cause the target column to allow NULLs.

Question 14

What are the drawbacks of using the SELECT INTO statement?

Answer 14

You have limited control over the definition of the target table. Some things you can control indirectly such as column data types and nullability, but some things you simply can’t control such as the file group of the target table.

Question 15

What happens while a SELECT INTO statement executes?

Answer 15

SELECT INTO involves both creating a table and populating it with data. This means that the target table’s metadata and the actual data are exclusively locked until the SELECT INTO transaction finishes. As a result, you can run into blocking situations due to conflicts related to both data and metadata access.

Question 16

What is the difference between SELECT INTO and INSERT SELECT?

Answer 16

SELECT INTO creates the target table and inserts into it the result of a query. INSERT SELECT inserts the result of the query into an already existing table.

Question 17

When using any of the INSERT statements, can you still specify a target column when the column has a default constraint associated with it?

Answer 17

Yes. You can indicate your own value and override the default.

Question 18

What is the UPDATE statement?

Answer 18

T-SQL supports the standard update statement which enables you to update existing rows in a table. The UPDATE statement has the following form:

UPDATE target table
   SET col1 = expression1,
      col2 = expression2,
      coln = expressionn
WHERE predicate;

Only rows for which the predicate evaluates to true are updated. Rows for which the predicate evaluates to false or unknown are not affected. An UPDATE statement without a WHERE affects all rows. Values are assigned to the target columns using the SET clause. The source expressions can involve columns from the table itself in which case the values before the update are used.

Question 19

Does standard SQL support an UPDATE statement that can include JOINs?

Answer 19

No. Standard SQL doesn’t, but T-SQL does.

Question 20

What is an example of an UPDATE statement with a JOIN?

Answer 20

UPDATE OD
SET OD.discount += 0.5
FROM Sales.MyCustomers AS C
   INNER JOIN Sales.MyOrders AS O
      ON C.custid = o.custid
   INNER JOIN Sales.MyOrderDetails AS OD
      ON O.orderid = OD.orderid
WHERE C.country = 'Norway';

Question 21

How many tables are you allowed to update in an UPDATE statement with a JOIN?

Answer 21

You can refer to elements from all tables involved in the source expressions, but you can modify only one target table at a time.

Question 22

What happens when an UPDATE statement with a JOIN has multiple source rows that match one target row?

Answer 22

In this case, the statement is nondeterministic as far as which source row is used. SQL Server does not generate an error or a warning. Instead it silently performs a nondeterministic UPDATE where one of the source rows arbitrarily “wins”. The row that “wins” is optimization dependent.

To avoid this scenario, you should write UPDATEs that include tie breaking logic.

Instead of using a JOIN, you can utilize the APPLY operator to build the tie breaking logic, e.g.

UPDATE C
SET C.postalcode=A.shippostalcode
   FROM Sales.MyCustomers AS C
CROSS APPLY
(
   SELECT TOP (1) O.shippostalcode
   FROM Sales.MyOrders AS O
   WHERE O.custid = C.custid
   ORDER BY orderdate, orderid) AS A;
)

Also, if you use the standard MERGE statement, it will generate an error if multiple source rows match the target row and requires you to make your code deterministic.

Question 23

Can you modify data through table expressions like CTE’s and derived tables?

Answer 23

Yes, this can be useful when you want to be able to see which rows are going to be modified and with what data before you actually apply the update.

WITH C AS
(
   SELECT TGT.custid,
      TGT.country AS tgt_country, SRC.country AS src_country, TGT.postalcode AS tgt_postalcode, SRC.postalcode AS src_postalcode
   FROM Sales.MyCustomers AS TGT
   INNER JOIN Sales.Customers AS SRC
      ON TGT.custid = SRC.custid
)

UPDATE C
SET tgt_country = src.country,
tgt_postalcode = src_postalcode;

Behind the scenes, the Sales.MyCustomers table gets modified. You can always highlight the inner SELECT query to run it independently.

You can also achieve the same result using a derived table, but you need to include the FROM clause.

UPDATE D
SET …
FROM
(

) AS D;

Question 24

Are window functions allowed in the SET clause?

Answer 24

No, window functions are not supported in the SET clause. The workaround is to use an update through a table expression and invoke the window function in the inner query’s SELECT list and to assign a column alias to the result column. Then in the outer UPDATE statement, you can refer to the column alias as a source expression in the SET clause.

Question 25

How can you modify a row with an UPDATE statement and also collect the result of the modified columns into variables?

Answer 25

You can handle such a need with a combination of UPDATE and SELECT statements, but this would require two visits to the row. T-SQL supports a specialized UPDATE syntax that allows achieving the task by using one statement:

DECLARE @newdiscount AS NUMERIC(4,3) = NULL;

UPDATE Sales.MyOrderDetails
SET = @newdiscount = discount += 0.5
WHERE orderid=10250
AND productid=51;

SELECT @newdiscount;

Question 26

How does the “all at once” concept have implications on the UPDATE statement?

Answer 26

As an example, consider an UPDATE statement such as:

UPDATE dbo.T1
SET col1 += @add, col2 = col1
WHERE keycol = 1;

All assignments use the original values of the row as the source values irrespective of their order of appearance. So the assignment col2=col1 doesn’t get the col1 value after the change, but rather before the change.

Question 27

Can you update rows in more than one table in a single UPDATE statement?

Answer 27

No. You can use columns from multiple tables as the source, but updates can only be applied to one table at a time.

Question 28

What are the two statements supported by T-SQL to delete rows from a table?

Answer 28

DELETE and TRUNCATE.

Question 29

What is the DELETE statement?

Answer 29

With the DELETE statement, you can delete rows from a table. You can optionally specify a predicate to restrict the rows to be deleted. The general form of a DELETE statement looks like the following:

DELETE FROM
WHERE

Note that if you don’t specify a predicate, all rows from the target table are deleted.

Question 30

Are DELETE statements fully logged?

Answer 30

Yes. A DELETE statement is fully logged and as a result, large deletes can take a long time to complete. Such large deletes can cause the transaction log to increase in size dramatically during the process. They can also result in lock escalation, meaning that SQL Server escalates fine-grained locks like row locks to a full-blown table lock. Such escalation may result in blocking access to all table data by other processes.

Question 31

How can you prevent table locks during a DELETE?

Answer 31

You can split your large delete into smaller chunks. You can achieve this by using the DELETE statement with a TOP option that limits the number of affected rows in a loop, e.g.

WHILE 1 = 1
BEGIN
   DELETE TOP (1000) FROM Sales.MyOrderDetails
   WHERE productid=12;

IF @@rowcount < 1000 BREAK;
END

Note that when you use the TOP filter, you cannot control which rows get chosen.

Question 32

What does the TRUNCATE statement do?

Answer 32

TRUNCATE deletes all rows from the target table. Unlike the DELETE statement, it does not have an optional filter, so it’s all or nothing.

e.g. TRUNCATE TABLE Sales.MyOrderDetails;

Question 33

What are the differences between TRUNCATE and DELETE?

Answer 33

(1) DELETE writes significantly more to the transaction log compared to the TRUNCATE statement. For DELETE, SQL Server records in the log the actual data that was deleted. For TRUNCATE, SQL records information only about which pages were deallocated. As a result, TRUNCATE tends to be much faster.
(2) DELETE doesn’t attempt to reset an IDENTITY property if one is associated with a column in the target table. The TRUNCATE statement does. If you use TRUNCATE and you prefer not to reset the IDENTITY, you need to store the current identity value + 1 in a variable (using IDENT_CURRENT function) and then reseed the property with the stored value after the TRUNCATE.
(3) DELETE is supported if there’s a FK pointing to the table in question as long as there are no related rows in the referencing table. TRUNCATE is not allowed if an FK is pointing to the table - even if there are no related rows and even if the FK is disabled.
(4) DELETE is allowed against a table involved in an indexed view. A TRUNCATE statement is not allowed in such as case.
(5) DELETE requires DELETE permissions on the target table. TRUNCATE requires ALTER permissions on the target table.

Question 34

When you need to delete all rows from a table, which statement should you use: DELETE or TRUNCATE?

Answer 34

It is usually preferred to use TRUNCATE because it is significantly faster than DELETE; however, it does require stronger permissions and is more restricted.

Question 35

How do you perform a DELETE based on joins?

Answer 35

DELETE O
FROM Sales.MyOrders AS O
INNER JOIN Sales.MyCustomers AS C
   ON O.custid = C.Custid
WHERE C.country = 'USA';

You can implement the same task by using a subquery instead of a join:

DELETE FROM Sales.MyOrders
WHERE EXISTS
(
   SELECT *
   FROM Sales.MyCustomers
   WHERE MyCustomers.custid=MyOrders.custid
      AND MyCustomers.country='USA'
);

Note that the subquery version is standard where as the join version is not. So if standard compliance is priority, you want to use the subquery version.

Question 36

How do you perform a DELETE using a table expression?

Answer 36

T-SQL supports deleting rows by using table expressions. The idea is to use a table expression (CTE or derived table) to define the rows that you want to delete and then issue a DELETE statement against the table expression.

e.g.

WITH OldestOrders AS
(
   SELECT TOP(100) *
   FROM Sales.MyOrders
   ORDER BY orderdate, orderid
)
DELETE FROM OldestOrders;

Question 37

Does the DELETE statement support the ORDER BY clause?

Answer 37

No. The DELETE statement does not support the ORDER BY clause.