Create Transact-SQL SELECT queries Flashcards
Ways to assign alias in SELECT statement
- column AS alias
- column alias
- alias = column
When do you need to delimit identifiers?
When it doesn’t comply with the rules:
- First character must be a letter, underscore, @, or #.
- Subsequent characters can include letters, decimal numbers, @, #, $, or underscore.
- Cannot be a reserved keyword in T-SQL
- Cannot have embedded spaces
- Must not include supplementary characters
Two ways to delimit identifiers
- “2017”
2. [2017]
Why shouldn’t you use = when looking for a NULL? What should you use instead?
Because nothing is considered equal to a NULL–not even another NULL.
You should use IS NULL or IS NOT NULL operators
e.g.
WHERE region IS NULL;
What is the order of operations for AND, OR, and NOT?
NOT, AND, OR
Note: can use parentheses to specify order
How should you identify Unicode character strings?
N’string’
Syntax for pattern matching
What do these wildcards mean?
%, _, [characters], [character-range], [^characters]
column LIKE pattern
% : matches any string including empty one
_ : a single character
[characters] : a single character from a list of characters
[character-range] : a single character from a range
[^characters] : a single character that is not in the list or range
How to look for a character that is considered a wildcard?
- Use escape character: col1 LIKE ‘!_%’ ESCAPE ‘!’ looks for strings that start with an underscore
- Use square brackets: col1 LIKE ‘[_]%’
Which date form is considered language-neutral for all date and time types?
‘YYYYMMDD’
You’re trying to find orders placed in April. What is wrong with the following and what should you do instead?
SELECT orderid, orderdate, empid
FROM Sales.Orders2
WHERE orderdate BETWEEN ‘20160401’ AND ‘20160430 23:59:59.999’
The value is rounded up to the next millisecond. This also returns orders placed in May 1, 2016.
SELECT orderid, orderdate, empid
FROM Sales.Orders2
WHERE orderdate >= ‘20160401’ AND orderdate < ‘20160501’
Can you order by columns that are not in the SELECT statement?
When would this fail?
Yes
It can fail when DISTINCT is used because since duplicates are removed, result rows don’t necessarily map to source rows in a one-to-one manner
In what order does SQL process the components of a query?
FROM WHERE GROUP BY HAVING SELECT ORDER BY
Syntax for TOP for both number of rows and percent
Does TOP PERCENT round up or down the number of rows?
SELECT TOP (4) col1, col2
FROM Sales.Orders
ORDER BY orderdate DESC;
Note: Can also specify number of rows without parentheses, but correct syntax is with
Note: Can use expressions as inputs
SELECT TOP (4) PERCENT col1, col2
FROM Sales.Orders
ORDER BY orderdate DESC;
Note: TOP PERCENT rounds up for number of rows
What does OFFSET do?
Syntax for OFFSET-FETCH
Which clause is required for OFFSET-FETCH?
Do you always need to use OFFSET-FETCH together?
What if want to filter a certain number of rows in arbitrary order?
After which statement is OFFSET-FETCH processed in a query?
It skips rows
SELECT col1, col2
FROM Sales.Orders
ORDER BY orderdate
OFFSET 50 ROWS FETCH NEXT 25 ROWS ONLY;
ORDER BY is required to use OFFSET-FETCH
FETCH clause required OFFSET, but OFFSET doesn’t require FETCH
Note: If skipping 0 rows, use FETCH FIRST instead of NEXT
Note: can use expressions as inputs
For arbitrary order use ORDER BY (SELECT NULL)
OFFSET-FETCH is processed after SELECT. Think of it as an extension of the ORDER BY clause
Between TOP and OFFSET-FETCH, which one is standard and which one is not?
OFFSET-FETCH is standard
Guidelines for set operators: UNION, UNION ALL, INTERSECT, and EXCEPT
- About number of columns and column types
- Set operators use distinctness-based comparison rather than equality-based. What does this mean?
- These operators are set operators and not cursor operators. What restriction does this impose on the individual queries?
- Where can you have an ORDER BY clause?
- How are column names of the result columns determined?
- Number of columns between the queries has to be the same and the column types of corresponding columns need to be compatible (implicitly convertible)
- A comparison between two NULLs yields true. Comparison between NULL and non-NULL yields false.
- Individual queries cannot have an ORDER BY clause
- You can add an ORDER BY clause at the end of the query so that it acts on the result of the set operator
- Columns names are determined by the first query.
What does UNION do?
Syntax for UNION
When do you use UNION ALL?
If the sets you’re unifying don’t have duplicate rows, should you use UNION or UNION ALL?
Combines results of queries vertically
SELECT country, region
FROM HR.Employees
UNION
SELECT country, region
FROM Sales.Customers;
Use UNION ALL when you want to keep duplicate rows (They are excluded if just use UNION)
If no duplicates, use UNION ALL so that SQL doesn’t have to check for duplicates and thus save computation cost
What does INTERSECT do?
Syntax for INTERSECT
Returns distinct rows that are in both queries
SELECT country, region, city
FROM HR.Employees
INTERSECT
SELECT country, region, city
FROM Sales.Customers;
What does EXCEPT do?
Syntax for EXCEPT
Returns rows that are in the first query but not in the second.
SELECT country, region, city
FROM HR.Employees
EXCEPT
SELECT country, region, city
FROM Sales.Customers;
What is the order of operations for the set operators?
INTERSECT precedes UNION and EXCEPT
UNION and EXCEPT are evaluated left to right based on their position in the expression.
Note: can use parenthesis to specify order
What is a CROSS JOIN?
CROSS JOIN syntax
It produces the Cartesian product of the two tables
SELECT D.n AS theday, S.n AS shiftno
FROM dbo.Nums AS D
CROSS JOIN dbo.Nums AS S
Consider this query:
SELECT D.n AS theday, S.n AS shiftno
FROM dbo.Nums AS D
CROSS JOIN dbo.Nums AS S
WHERE D.n <= 7
AND S.n <= 3
ORDER BY theday, shiftno;
SQL Server performs optimization technique called predicate pushdown. What does this mean?
SQL Server knows that with a cross join followed by a filter it can evaluate the filters first.
What is an INNER JOIN?
INNER JOIN syntax
What’s equivalent to INNER JOIN?
Combines tables horizontally based on matching values in both tables.
SELECT column_name(s)
FROM table1
INNER JOIN table2
ON table1.column_name = table2.column_name;
JOIN is equivalent to INNER JOIN
For inner joins, what’s the difference between ON and WHERE clauses?
What about outer joins?
There’s no difference. Instead of using a WHERE clause, you can simply put the expression on the ON clause
In outer joins, ON and WHERE play different roles. ON matches while WHERE filters.
What is a left join?
What is a right join?
Syntax
Left join returns all records from the left table (table1), and the matched records from the right table (table2).
Right join returns all records from the right table (table2), and the matched records from the left table (table1)
SELECT column_name(s)
FROM table1
LEFT JOIN table2
ON table1.column_name = table2.column_name;
Note: can also say LEFT OUTER JOIN
What is a full join?
Syntax
A full join returns matched rows plus all the unmatched rows from the left and right tables.
SELECT column_name(s) FROM table1 FULL OUTER JOIN table2 ON table1.column_name = table2.column_name WHERE condition;
Note: can also just say FULL JOIN
Between a unique constraint and a primary key constraint, which allows NULLs?
Only the unique constraint allows NULLs
What does the ISNULL function do?
Syntax
The ISNULL function checks whether an expression is NULL and if so, replaces it by the value specified. Otherwise, it returns the expression
ISNULL(expression, value)
e.g.
ISNULL(col1, N’N/A’)
This table has a clustered index. It also has NULLs in the ‘region’ column. What’s the problem with the following query (Two cases: when SQL Server uses nested loops and when it uses merge for join)? What should you do instead?
SELECT EL.country, EL.region, EL.city, EL.numemps, CL.numcusts
FROM dbo.EmpLocations AS EL
INNER JOIN dbo.CustLocations AS CL
ON EL.country = CL.country
AND ISNULL(EL.region, N’N/A’) = ISNULL(CL.region, N’N/A’)
AND EL.city = CL.city;
The problem is that once you manipulate a column with the ISNULL function, SQL Server cannot trust that the result values preserve the same ordering as the original values.
For nested loops, it will make the region and city predicates appear as residual predicates rather than as seek predicates. This increases computational cost.
For merge joins, after scanning the clustered index, SQL Server has to sort the results before merging. Also increases computational cost.
The following query does the same but is order-preserving:
SELECT EL.country, EL.region, EL.city, EL.numemps, CL.numcusts
FROM dbo.EmpLocations AS EL
INNER JOIN dbo.CustLocations AS CL
ON EL.country = CL.country
AND(EL.region = CL.region OR (EL.region IS NULL AND CL.region IS NULL))
AND EL.city = CL.city;
Then all predicates will appear under seek predicates.
Consider the following query. Is it order-preserving or not?
Explain what happens in the ON clause
What is the advantage of using INTERSECT instead of = here?
SELECT EL.country, EL.region, EL.city, EL.numemps, CL.numcusts
FROM dbo.EmpLocations AS EL
INNER JOIN dbo.CustLocations AS CL
ON EXISTS (SELECT EL.country, EL.region, EL.city
INTERSECT
SELECT CL.country, CL.region, CL.city);
Yes, it is order-preserving.
For each row evaluated by the join, the set operator performs an intersection of the employee location attributes and the customer location attributes using FROM-less SELECT statements, each producing one row. If the locations intersect, the result is one row, in which case the EXISTS predicate returns true. If the locations don’t intersect, the result is an empty set, in which case the EXISTS predicate returns false.
This take advantage of the distinctness-based comparison of set operators. This query will return rows where NULLs match between the two tables.
In what order does SQL Server evaluate multi-join queries?
From left to right, so the result of one join is used as the left input for the next join
Assume that not all suppliers have products and therefore no categoryid. What is the problem with this query?
SELECT companyname AS supplier, S.country, P.productid, P.productname, P.unitprice, C.categoryname
FROM Production.Suppliers AS S
LEFT OUTER JOIN Production.Products AS P
ON S.supplierid = P.supplierid
INNER JOIN Production.Categories AS C
ON C.categoryid = P.categoryid
WHERE S.country = N’Japan’;
Provide two solutions to the problem. One using outer joins and one separating joins into their own independent logical phase.
The inner join that followed the outer join nullified the outer part of the join. The first join returns all suppliers, including NULLs for those without products. Then the inner join compared the NULLs in the categoryid column in the outer rows to categoryid values in Production.Categories and discarded those rows.
Solution 1: outer joins
SELECT companyname AS supplier, S.country, P.productid, P.productname, P.unitprice, C.categoryname
FROM Production.Suppliers AS S
LEFT OUTER JOIN Production.Products AS P
ON S.supplierid = P.supplierid
LEFT OUTER JOIN Production.Categories AS C
ON C.categoryid = P.categoryid
WHERE S.country = N’Japan’;
Solution 2: separating into own phase
SELECT companyname AS supplier, S.country, P.productid, P.productname, P.unitprice, C.categoryname
FROM Production.Suppliers AS S
LEFT OUTER JOIN
(Production.Products as P
INNER JOIN Production.Categories as C
ON C.categoryid = P.categoryid)
ON S.supplierid = P.supplierid
WHERE S.country = N’Japan’;
CAST function syntax
CONVERT function syntax
Which one is standard and which one is proprietary in T-SQL?
When is it useful to use CONVERT over CAST?
CAST(source_expression AS target_type)
e.g. CAST(‘100’ AS INT)
CONVERT(target_type, source_expression [,style_number])
e.g. CONVERT(DATE, ‘01/02/2018’, 101)
CAST is standard and CONVERT is proprietary in T-SQL.
It’s useful to use CONVERT when you need to specify a style for the conversion.
PARSE function syntax
How is it different from CONVERT?
What is one drawback of this function?
PARSE(string_input AS target_type [USING style]
Instead of style numbers, it uses the more user-friendly .NET culture names.
e.g. PARSE(‘01/02/2019’ AS DATE USING ‘en-US’)
A drawback is that it’s significantly slower than CONVERT.
What is a problem with CAST, CONVERT, and PARSE?
What can you do about it?
If the function fails to convert a value within a query, the whole query fails and stops processing.
Instead, can use TRY_CAST, TRY_CONVERT, and TRY_PARSE, which return a NULL when the conversion isn’t valid.
FORMAT function syntax
FORMAT(value, format)
e.g. FORMAT(SYSDATETIME(), ‘yyyy-MM-dd’)
Uses a .NET format string
Note: this function is pretty slow
Which functions return the current date and time?
In what format do they give you the date and time?
Which one is T-SQL and which one is standard?
GETDATE() and CURRENT_TIMESTAMP
Note: no parenthesis for CURRENT_TIMESTAMP
DATETIME format
GETDATE() is T-SQL and CURRENT_TIMESTAMP is standard.
Which date functions return the current date and time in the more precise DATETIME2 and DATETIMEOFFSET data types?
SYSDATETIME()
SYSDATETIMEOFFSET()
Which functions return the current date and time in UTC terms?
In what format do they give you the date and time?
GETUTCDATE() and SYSUTCDATETIME()
DATETIME and DATETIME2, respectively.
How do you get only the current time or only the current date?
Use cast on the SYSDATETIME function to DATE or TIME
CAST(SYSDATETIME() as DATE)
Which 2 functions allow you to extract a date or time part from a date?
Syntax for extract year from ‘01/02/2019’
What’s the difference between the 2 functions?
DATEPART and DATENAME
DATEPART(year, ‘01/02/2019’)
DATEPART returns integers and DATENAME returns strings.
Note: T-SQL provides YEAR, MONTH, and DAY functions as abbreviations to DATEPART.
What are six functions that construct a desired date and time value from its numeric parts.
Syntax for function with DATE format
DATEFROMPARTS, DATETIME2FROMPARTS, DATETIMEFROMPARTS, DATETIMEOFFSETFROMPARTS, SMALLDATETIMEFROMPARTS, TIMEFROMPARTS.
DATEFROMPARTS(year, month, day)
Which function calculates the end of month date for the input date?
Syntax for function
EOMONTH(‘01/02/2015’)
Which function can you use to add a specified number of date units to a date and time value?
Syntax
DATEADD(year, 1, ‘20170212’) adds one year to the date.
Which function returns the difference in terms of a requested part between two date and time values?
Syntax
If we were looking for the difference in years between 01/03/2015 and 03/03/2016, would we get a decimal value?
DATEDIFF(day, ‘20150212’, ‘20160212’)
No, this function only looks at the requested part and above in the date and time hierarchy
What should you use instead of DATEDIFF if the difference doesn’t fit in a four-byte integer?
Use DATEDIFF_BIG instead. It returns the result as a BIGINT type.
SWITCHOFFSET function syntax
What does it do?
SWITCHOFFSET(SYSDATETIMEOFFSET(), ‘-08:00’)
It takes a DATETIMEOFFSET value and adjusts it to a requested offset. For example, if the system’s offset is ‘-05:00’, the function compensates for this by subtracting 3 hours from the input value.
TODATETIMEOFFSET function syntax
What does it do?
TODATETIMEOFFSET(‘DATETIME2 value’, ‘-08:00’)
It takes a DATETIME2 value and translates it into a DATETIMEOFFSET value. It basically puts the datetime and offset together.
Which T-SQL function can you use instead of SWITCHOFFSET and TODATETIMEOFFSET?
Function syntax
What does it do?
inputdate AT TIME ZONE timezone
e.g. SYSDATETIME() AT TIME ZONE ‘Pacific Standard Time’
It works 2 ways. When the input is of a DATETIMEOFFSET type, it works like SWITCHOFFSET. When the input is not an offset-aware value, it works like TODATETIMEOFFSET.
What are two ways to concatenate a string?
What is the difference between them?
Can use + operator or CONCAT function.
eg.
SELECT country + N', ' + region + N', ' + city AS location
SELECT CONCAT('US', ' ', 'TX', ' ', 'Dallas')
When any input is NULL, the default behavior of the + operator is to return a NULL. The CONCAT function substitutes NULLs with empty strings.
Which function allows you to extract a substring from a string?
Syntax
SUBSTRING(‘string’, start, length)
where start is index of the starting position and length is the length of the string you want extracted.
Which functions allow you to extract a requested number of characters from the left and right ends of the input string?
Syntax
LEFT and RIGHT functions
LEFT(‘string’, num)
where num is the number of characters to extract.
What does CHARINDEX do?
Syntax
It returns the position of the first occurrence of a substring within a string.
CHARINDEX(‘substring’, ‘stringtosearch’)
What does PATINDEX do?
Syntax
It locates the first position of a pattern within a string.
PATINDEX(‘pattern’, ‘stringtosearch’)
What are two functions that measure the length of a string?
Syntax
What’s the difference between them?
LEN and DATALENGTH
LEN(‘string’)
LEN returns length in terms of the number of characters, and it removes trailing spaces. DATALENGTH returns length in terms of number of bytes, and it does not remove trailing spaces.
Note: for unicode characters, DATALENGTH counts 2 bytes per character/space. Regular characters are 1 byte.
What does the REPLACE function do?
Syntax
Within the string provided as the first argument, it replaces all occurrences of string provided in second argument with the string provided as the third argument
REPLACE(‘stringtosearch’, ‘substringtoreplace’, ‘replacement’)
Which function allows you to replicate an input string a certain number of times?
Syntax
REPLICATE(‘string’, num)
where num is number of times to replicate
What does the STUFF function do?
Syntax
Searches string provided in first argument. Then, from the character position indicated as the second argument, deletes the number of characters indicated by the third argument. Then it inserts in that position the string specified as the fourth argument.
STUFF(‘stringtosearch’, start, num, ‘stringtoinsert’)
Which functions remove leading or trailing spaces?
How do you remove both leading and trailing spaces?
LTRIM and RTRIM
RTRIM(LTRIM(‘string’))
What table-valued function can be used to split strings?
Syntax
What does it return?
STRING_SPLIT(‘stringtosplit’, ‘delimiter’)
where the stringtosplit will be split at the delimiter.
It returns a column named “value” with the split strings.
Syntax for simple form of CASE expression.
Syntax for searched form of CASE expression.
SELECT col1, col2,
CASE col1
WHEN expression THEN expression
WHEN expression THEN expression
ELSE expression
END AS newcol
FROM table;
SELECT col1, col2,
CASE
WHEN predicate THEN expression
WHEN predicate THEN expression
ELSE expression
END AS newcol
FROM table;
predicate could be col1 < 20
Note: The first predicate that evaluates to true determines which when expression is returned.
What does the COALESCE function do?
Which function is it similar to? Which one is standard and which one is non-standard?
3 differences between these functions
It accepts a list of expressions as input and returns the first that is not NULL, or NULL if all are NULLs. Can use it to substitute a NULL with something else, e.g. COALESCE(region, ‘ ‘) returns region if not NULL and returns an empty string if NULL.
It’s similar to ISNULL function. COALESCE is standard, and ISNULL is T-SQL.
Differences:
- ISNULL can only take 2 inputs
- The type of output for COALESCE is determined by the returned element, but for ISNULL, it’s determined by the first input
- When you use the SELECT INTO statement, if col1 is defined as NOT NULL, both COALESCE(col1, 0) and ISNULL(col1, 0) will produce output defined as NOT NULL. If col1 is defined as allowing NULLs, COALESCE will create output that allows NULLs, but ISNULL will create one that disallows NULLs.
How does the NULLIF function work?
It accepts 2 input expressions. If they are equal, it returns NULL. Returns the first input if not equal.
What does the @@ROWCOUNT function do?
What if the row count exceeds the max INT value?
It returns the number of rows affected by the last statement that you executed.
If row count exceeds the max INT value, use ROWCOUNT_BIG function.
In what format is the result of the COMPRESS function?
The result is a binary string
What does the NEWID() function do?
It can be used to generate a key that is globally unique as a UNIQUEIDENTIFIER typed value
What does the NEWSEQUENTIALID system function do?
Generates GUIDs that always increase within the machine
What is a search argument, or SARG?
What are two requirements of a sargable filter?
It is a filter predicate that enables the optimizer to rely on index order.
Such a filter is sargable if:
- You don’t apply manipulation to the filtered columns
- The operator identifies a consecutive range of qualifying rows in the index.
What are the 3 main categories of function determinism?
- Functions that are always deterministic, i.e. the function is guaranteed to return the same result given the same set of input values, e.g. all string functions, COALESCE, ISNULL, ABS, SQRT
- Functions that are deterministic or not depending on how they are used, e.g. CAST function might return different values when converting from a string to a date based on the login’s language. Also, CONVERT, RAND (seed vs no seed).
- Functions that are always nondeterministic, e.g. SYSDATETIME and NEWID.
Most nondeterministic functions are invoked once per query, what is an exception?
NEWID gets invoked once per row
Suppose you want to return a random set of 3 employees, and you use the following query:
SELECT TOP (3) empid, firstname, lastname
FROM HR.Employees
ORDER BY RAND();
What is the problem with this query? How would you solve it?
If you run this code repeatedly, you would keep getting the same result because the RAND function returns the same value in all rows.
Solution:
SELECT TOP (3) empid, firstname, lastname
FROM HR.Employees
ORDER BY CHECKSUM(NEWID());
When creating a table, what does IDENTITY do?
orderid INT NOT NULL IDENTITY(1, 1)
This is the identity property with a seed 1 and an increment 1. It will generate the values in this column automatically when rows are inserted.
Syntax for inserting values into a table with INSERT INTO
What happens if you don’t specify a value for a column?
INSERT INTO table(col1, col2, col3) VALUES
(val1, val2, val3),
(val4, val5, val6),
(val7, val8, val9);
If you don’t specify a value for a column, SQL Server first checks whether the column gets its value automatically, e.g. from an identity property or a default constraint. If that’s not the case, it checks whether the column allows NULLs, in which case it assumes a NULL. If that’s not the case, SQL Server generates an error.
Syntax for inserting values into a table with INSERT SELECT statement
INSERT INTO table(col1, col2, col3)
SELECT col1, col2, col3
FROM othertable
WHERE condition;
This inserts the result of the query into the table.
How do you insert your own values into a column with an identity property?
SET IDENTITY_INSERT tablename ON;
INSERT ….
SET IDENTITY_INSERT tablename OFF;
INSERT EXEC helps you insert the result set of what into a table?
Syntax
It helps you insert the result set returned by a dynamic batch or a stored procedure.
INSERT INTO table(col1, col2, col3)
EXEC procedurename
@variable = expression
where the procedure is something like:
CREATE PROC procedurename
@variable AS NVARCHAR(15)
AS
SELECT col1, col2, col3
FROM othertable
WHERE col3 = @variable;
GO
Syntax for creating a new table and populate it with values using SELECT INTO statement.
Which things are copied from the original table. Which things are not?
SELECT col1, col2, col3
INTO NewTable
FROM OtherTable
WHERE condition
Things copied: column names, types, nullability, and identity property
Things not copied: indexes, constraints, triggers, permissions, and others.
UPDATE statement syntax
Does standard SQL or T-SQL support using joins in UPDATE statements?
UPDATE target-table
SET col1 = expression
WHERE predicate;
Only T-SQL supports using joins in UPDATE statements
What are two ways to delete rows from a table?
Syntax for each one
How are they different?
DELETE and TRUNCATE TABLE
DELETE FROM tablename
WHERE predicate;
TRUNCATE TABLE tablename;
TRUNCATE TABLE doesn’t support a filter. It also uses an optimized logging mode that makes it way faster than DELETE.
Use TRUNCATE TABLE when you need to delete all rows from a table or a partition but leave the table definition in place.
Syntax for DELETE statement when using a join
DELETE FROM alias1
FROM table1 AS alias1
INNER JOIN table2 AS alias2
ON alias1.col1 = alias2.col1
WHERE predicate;MERGE statement - what does it do and syntax
It allows you to merge data from a source table into a target table
MERGE INTO targettable as TGT
USING sourcetable AS SRC
ON mergepredicate
WHEN MATCHED (AND predicate)
THEN action*
WHEN NOT MATCHED (BY TARGET) (AND predicate)
THEN INSERT ...
WHEN NOT MATCHED BY SOURCE (AND predicate)
THEN action*
*action can be either UPDATE or DELETE. Can repeat clause to use one with UPDATE and another with DELETE.
INSERT with OUTPUT
Syntax when you just want to return info
Syntax when you want to store the result in a table
INSERT INTO tablename(col1, col2, col3)
OUTPUT
inserted.col1, inserted.col2, inserted.col3
SELECT col1, col2, col3
FROM anothertable
WHERE predicate;
To store result in a table:
OUTPUT
inserted.col1, inserted.col2, inserted.col3
INTO sometable(col1, col2, col3)
DELETE with OUTPUT syntax
DELETE FROM tablename
OUTPUT deleted.col1
WHERE empid=1;
UPDATE with OUTPUT syntax
UPDATE tablename
SET col2 = expression
OUTPUT
inserted.col1, deleted.col2, inserted.col2
WHERE predicate;
Note: can use inserted or deleted to prefix col names with an UPDATE statement
What does the $action function do? Which statement would you use it with?
It returns a string (insert, update, or delete) indicating the action on a row. You can use in with a MERGE statement.
Where would OUTPUT go relative to a MERGE statement?
Use the $action function and OUTPUT to return the ID of an affected row and how it was affected.
After the MERGE statement
OUTPUT
$action AS the_action
COALESCE(inserted.id, deleted.id) as ID;
Note: Rows affected by an INSERT action have values in the inserted row and NULLs in the deleted row. Rows affected by a DELETE action have NULLs in the inserted row and values in the deleted row. Rows affected by an UPDATE action have values in both. So using coalesce helps you return the id of the affected row.
Considering the INSERT, UPDATE, DELETE, and MERGE statements, in which ones can you refer to columns from both the target and the source tables in the OUTPUT clause?
Suppose you want the OUTPUT clause to return the both the source and target keys from a multi-row insert. How would you achieve this?
You can only refer to source table columns in the MERGE statement.
You would need to use the MERGE statement instead of INSERT. Because an INSERT action is only allowed when the merge predicate is false, simply use a condition that is always false e.g.
MERGE INTO targettable AS TGT
USING sourcetable AS SRC
ON 1 = 2
WHEN NOT MATCHED THEN
INSERT(col1, col2, col3) VALUES(col1, col2, col3)
OUTPUT
SRC.key AS srckey, inserted.key AS tgtkey,
inserted.col1, inserted.col2, inserted.col3
What is the following query doing? What is it an example of?
INSERT INTO sometable(orderid, custid, empid, orderdate)
SELECT orderid, custid, empid, orderdate
FROM (MERGE INTO Sales.MyOrders AS TGT
USING sourcetable as SRC
ON SRC.orderid = TGT.orderid
WHEN MATCHED AND EXISTS( SELECT SRC.* EXCEPT SELECT TGT.* ) THEN
UPDATE SET TGT.custid = SRC.custid
TGT.empi = SRC.empid
TGT.orderdate = SRC.orderdate
WHEN NOT MATCHED THEN
INSERT VALUES(SRC.orderid, SRC.custid, SRC.empid, SRC.orderdate)
WHEN NOT MATCHED BY SOURCE THEN
DELETE
OUTPUT
$action AS the_action, inserted.*) AS D
WHERE the_action = ‘INSERT’;
This is an example of nested DML. With the MERGE and OUTPUT clause, you’re creating something that looks like a derived table. Then the INSERT SELECT statement is filtering the output rows from the derived table.
Basically use this when want to capture the output from a modification statement, but you’re only interested in a subset of the output rows.
How do you add a column to a table?
Can you add a column that doesn’t allow NULLs to any table?
ALTER TABLE tablename
ADD newcol datatype
(CONSTRAINT) (WITH VALUES)
No, table must be empty to add column that doesn’t allow nulls, or the column must get values automatically.
Note: WITH VALUES is used when want to apply a default constraint to new rows and column defined as nullable.
How do you drop a column?
When does dropping a column fail?
ALTER TABLE tablename
DROP col1;
It fails when the column is used in an index, is used in a default, check, foreign key, unique, or primary key constraint, or is bound to a default object or a rule
How do you alter a column?
ALTER TABLE tablename
ALTER COLUMN col1 datatype;
How do you add a default constraint to an existing column in a table?
How do you add a new column with a default constraint to an existing table?
How do you drop a constraint?
ALTER TABLE tablename
ADD CONSTRAINT DF_tablename_col1
DEFAULT defaultvalue FOR col1
ALTER TABLE tablename
ADD newcol datatype NULL/NOT NULL
CONSTRAINT DF_tablename_newcol DEFAULT defaultvalue
ALTER TABLE tablename
DROP constraint constraintname
What is a CHECK constraint?
Syntax to add a check constraint
CHECK constraint is used to limit the range of values that can be entered for a column.
ALTER TABLE tablename
ADD CONSTRAINT CK_tablename_col CHECK (boolean expression with col)
What is an identity column?
Syntax to create a table with an identity column
How do you explicitly provide a value for an identity column?
If you have deleted all the rows in a table, how do you reset the identity column so that it starts at the beginning value again?
An identity column has its value automatically generated.
CREATE TABLE tablename
(col1 datatype IDENTITY(startnum, incrementby) PRIMARY KEY,
col2…)
To explicitly provide a value for an identity column, you turn on identity insert:
SET IDENTITY_INSERT tablename ON
And in the insert query, make sure to specify the column list.
To reset identity column:
DBCC CHECKIDENT('tablename', RESEED, 0)
Syntax for adding a unique key constraint on an existing table
ALTER TABLE tablename
ADD CONSTRAINT UQ_tablename_col UNIQUE (col)
Create a new, empty table having the same set of columns as an existing table
SELECT *
INTO newtable
FROM oldtable
WHERE 1 = 0
Use the INTO clause with a WHERE clause so that it returns no rows.
True or False: If you insert rows into a view, the rows will also be inserted into the underlying table
TRUE
How do you embed a quote mark within string literals? e.g. Susan’s
Use 2 quotes:
‘Susan’’s’
