Chapter 10 Inserting, Updating, and Deleting Data Flashcards Preview

70-461 > Chapter 10 Inserting, Updating, and Deleting Data > Flashcards

Flashcards in Chapter 10 Inserting, Updating, and Deleting Data Deck (37)
Loading flashcards...
1

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

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

2

What is the INSERT VALUES statement?

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

3

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

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.

4

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

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.

5

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

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.

6

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

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.

7

Can you insert multiple rows with INSERT VALUES?

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.

8

What is the INSERT SELECT statement?

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

9

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

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.

10

What is the INSERT EXEC statement?

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;

11

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

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.

12

What is the SELECT INTO statement?

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'

13

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

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.

14

What are the drawbacks of using the SELECT INTO statement?

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.

15

What happens while a SELECT INTO statement executes?

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.

16

What is the difference between SELECT INTO and INSERT SELECT?

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.

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?

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

18

What is the UPDATE statement?

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.

19

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

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

20

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

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

21

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

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

22

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

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.

23

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

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;

24

Are window functions allowed in the SET clause?

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.

25

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

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;

26

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

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.

27

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

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

28

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

DELETE and TRUNCATE.

29

What is the DELETE statement?

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.

30

Are DELETE statements fully logged?

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.