What are the 4 ways to insert data into your tables?
(1) INSERT VALUES, (2) INSERT SELECT, (3) INSERT EXEC, (4) SELECT INTO
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’);
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.
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.
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.
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.
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’),
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.
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.
SET IDENTITY_INSERT Sales.MyOrders ON;
INSERT INTO Sales.MyOrders (orderid, custid, empid, orderdate)
SELECT orderid, custid, empid, orderdate
SET IDENTITY_INSERT Sales.MyOrders OFF
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.
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.
SET IDENTITY_INSERT Sales.MyOrders ON;
INSERT INTO Sales.MyOrders(orderid, custid, empid, orderdate)
@country = ‘Portugal’
SET IDENTITY_INSERT Sales.MyOrders OFF;
SET IDENTITY_INSERT Sales.MyOrders OFF;
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.
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.
SELECT orderid, custid, orderdate
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.
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.
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.
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.
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.
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.
Does standard SQL support an UPDATE statement that can include JOINs?
No. Standard SQL doesn’t, but T-SQL does.
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';
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.
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.
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 )
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.
) AS D;
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.
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;
SET = @newdiscount = discount += 0.5
How does the “all at once” concept have implications on the UPDATE statement?
As an example, consider an UPDATE statement such as:
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.
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.
What are the two statements supported by T-SQL to delete rows from a table?
DELETE and TRUNCATE.
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:
Note that if you don’t specify a predicate, all rows from the target table are deleted.
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.
How can you prevent table locks during a DELETE?
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;
Note that when you use the TOP filter, you cannot control which rows get chosen.
What does the TRUNCATE statement do?
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;
What are the differences between TRUNCATE and DELETE?
(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.
When you need to delete all rows from a table, which statement should you use: DELETE or TRUNCATE?
It is usually preferred to use TRUNCATE because it is significantly faster than DELETE; however, it does require stronger permissions and is more restricted.
How do you perform a DELETE based on joins?
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.
How do you perform a DELETE using a table expression?
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;
Does the DELETE statement support the ORDER BY clause?
No. The DELETE statement does not support the ORDER BY clause.