Chapter 10 Inserting, Updating, and Deleting Data

Question 1

What are the 4 ways to insert data into tables?

Answer 1

1. INSERT INTO Schema.Table VALUES(N’FT’, N’Feet’, 2540), (N’KM’, N’Kilo Meters’, 12)
2. INSERT INTO Schema.Table (Col1, Col2) SELECT Col1, Col2 FROM Schema.Table
3. INSERT INTO Schema.Table EXEC (@SQL) OR INSERT INTO Schema.Table EXEC SP_EXECUTE_SQL @SQL
4. SELECT * INTO Schema.Table FROM ( select… from ) AS T1

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.

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

OR

INSERT INTO Sales.MyOrders(custid, empid, orderdate)

VALUES(2, 19, ‘20120620’), (1, 21, ‘20150516’); –> Note the comma seperation.

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 and 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 table’s column definition order.

If the underlying table definition changes, and the INSERT statement is not modified, the result is either an error or values written to the wrong column or both.

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.

SET IDENTITY_INSERT ON;

INSERT INTO Schema.Table VALUES(1, 2000)

SET IDENTITY_INSERT OFF;

Note that in order to use this option, 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.

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;

Question 11

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

Answer 11

Yes.

INSERT EXEC works when the source dynamic batch or stored procedure has more than one query.

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

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

This can 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 value 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 support Updates with Joins

However, T-SQL does.

UPDATE T1

SET T1.Col1 = 12

FROM Schema.Table1 AS T1

JOIN Schema.Table2 AS T2

ON T1.Col5 = T2.Col5

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

One.

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

SQL Server does not generate an error or a warning. Instead, since the result set is non-deterministic a row from the set is arbitrarily chosen based on Optimization.

To avoid this scenario, 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 the standard MERGE statement is used, 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 is useful when data modification needs to occur before updating the table data.

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

UPDATE dbo.T1

SET

col1 += @add,

col2 = col1

WHERE keycol = 1;

All assignments use their original values.

Therefore, 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 remove 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 some or all 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.

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

Split large delete into smaller chunks.

Use the DELETE statement with a TOP option to limit the number of affected rows in a loop.

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.

@@ROWCOUNT returns the number of rows affected by the previous statement

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 is much faster.
2. DELETE doesn’t 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 on tables involved in indexed views. 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

The preference is to 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.

Therefore, if standard compliance is a priority, 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.

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.