Originally written in 2010, this Q&A urgently requires an update.
What SQL command do you use to pull and filter data from a table ?
SELECT [Column List] FROM [Table Name] WHERE [Condition = TRUE]
OR
SELECT * FROM [Table Name] WHERE [Condition = TRUE]
How do you update data (what command do you use)
Syntax to update data without join:
UPDATE TableA SET Field1 = X
WHERE Condition = True
Syntax to update data using a join:
UPDATE TableA SET TableA.Field2 = TableB.Field2
FROM TableA
INNER JOIN TableB ON TableB.Field1 = TableA.Field1
or (alias solution)
UPDATE a SET a.Field2 = b.Field2
FROM TableA AS a
INNER JOIN TableB AS b ON b.Field1=a.Field1
What is the Syntax to insert data in a table
INSERT [INTO] TableA [()] SELECT Field1 [, FieldN] FROM TableB WHERE Condition = True
INSERT [INTO] TableA [()] SELECT Constant
INSERT [INTO] TableA [()] VALUES (…)
INSERT [INTO] TableA [()] EXEC sp
SELECT Field1, Field2, … INTO [TableName] FROM Table WHERE Condition = True
What is the identity property?
Auto incremental numeric value, gets populated automatically by SQL Server
When inserting a record into a table having an identity column, how do you capture the newly-generated number?
You can use @@IDENTITY, IDENT_CURRENT(‘Table Name’) or SCOPE_IDENTITY() functions
How would you capture the last identity value created by your database session (assuming other connections are actively inserting records into the same table)?
SCOPE_IDENTITY() function
How can one insert data into a field declared as having the identity property?
Identity fields are populated automatically and you are not allowed to specify what that value should be. However there is a way where you could force a specific value manually .
Turn the identity insert on first
SET IDENTITY_INSERT TableA ON
Perform the insert
Turn off the identity insert
SET IDENTITY_INSERT TableA OFF
Syntax to remove record(s) from a table
DELETE FROM TableA WHERE Condition = True
DELETE FROM a FROM TableA AS a
INNER JOIN TableB AS b ON b.Field1 = a.Field1
There are other ways and variations to the above syntax
You can also use TRUNCATE command that wipes out everything from the table.
What is the difference between the DELETE statement (to empty a table) and the TRUNCATE TABLE statement?
DELETE statement is a logged operation whereas the TRUNCATE TABLE statement is a minimally logged operation (data page deallocations are recorded in the log).
TRUNCATE TABLE statement performs faster than the DELETE statement.
TRUNCATE TABLE cannot be used on tables that are referenced by a foreign key constraint.
TRUNCATE TABLE will not activate a trigger because it is not logged; however, the DELETE statement may fire a trigger.
TRUNCATE TABLE will reset a table’s identity value back to its seed (usually 1).
DELETE statement is DML whereas TRUNCATE TABLE is DDL.
Different Joins and Their Purpose
Inner –Match rows from 2 tables based on the same values in the matching fields
Left Outer – All rows from left table and those records that match in right table. If not match, then null.
Right Outer – All rows from right table and those records that match in left table. If no match, then null.
Full Outer – All rows from left and right tables. Any non matching rows will contain a NULL in either the left or right tables.
Cross – Cartesian product. Left table record is returned with all records from right table.
Natural – Not implemented in SQL Server but it is in the ANSI standard. It basically amounts to a syntax short cut when you have your foreign and primary constraints set up.
Theta – A through D represent Equi-Joins which are a subset of Theta Joins that use the equality comparison. However you can join with othe non-equality
Boolean operators. …. FROM TABLEA A JOIN TABLEB B ON A.ID > B.ID.
WHERE clause purpose?
To filter only the records of interest.HAVING clause purpose?
To filter the aggregation of interest.What keyword(s) allow 2 datasets to be returned as a single dataset? For example, query 1 pulls data from a table while another query pulls data from another table.
UNION and UNION ALL clauses
And what is the difference between UNION and UNION ALL?
UNION automatically performs a DISTINCT operation – ensuring unique records in a dataset. UNION ALL returns a dataset AS-IS.
What is aliasing?
Substituting a short-hand name for a table after defining it.
Why alias tables?
To make code less verbose, yet more readable. You may have to join to the same table multiple times.
What is a correlated sub-query?
correlated sub-queries references same table in its sub query
How would you determine records that are missing when comparing 2 tables:
NOT IN subquery solution
SELECT a.*
FROM TableA
WHERE PKID NOT IN ( SELECT PKID FROM TableB )
LEFT OUTER JOIN solution
SELECT a.*
FROM TableA a
LEFT OUTER JOIN TableB b ON b.PKID=a.PKID
WHERE b.PKID IS NULL
NOT EXISTS correlated subquery solution
SELECT a.*
FROM TableA a
WHERE NOT EXISTS ( SELECT NULL FROM TableB WHERE PKID = a.PKID )
What is the difference between CROSS APPLY and OUTER APPLY in SQL Server?
CROSS APPLY returns only rows from the outer table that produce a result set from the table-valued function. OUTER APPLY returns both rows that produce a result set, and rows that do not, with NULL values in the columns produced by the table-valued function.
What looping constructs are available in T-SQL?
WHILE Loop
Cursors Pros
Allows row-by-row processing
Cursors Cons
Poor performance
Best Practice – Avoid them if at all possible. 95% of the time, a set-oriented approach can be implemented with much better results.
What is a derived table?
Select statement defined as its own separate table.
what is syntax to define a derived table?
SELECT *
FROM (
SELECT Field1, Field2
FROM TableA
WHERE [Condition=TRUE] ) AS a
What is a benefit of using derived tables as compared to declaring and populating a temp table/ table variable?
Performance because it uses existing indexes on referenced tables as compared to a temp table that needs to have indexes created on it before using it.
What is a CTE?
It’s a more readable form of a derived table that can be referenced multiple times within the same query. Syntax is:
CTEs can have recursive qualities.
<CTE Name> [(Column1, Column2, ColumnN)] AS
(
<CTE Query Anchor Member SELECT statement>
UNION ALL
<CTE Query Recursive Member SELECT statement>
)
SELECT *
FROM <CTE Name>
What is the difference between a temp table and a table variable?
Table variables don’t support check constraints, default values, and computed columns cannot reference user-defined functions. They don’t allow named constraints, table truncation, the overriding of the identity property. They cannot be created on-the-fly using SELECT … INTO …
A common misconception is that Temp tables are physically stored in TempDB whereas table variables solely reside in memory. This is not the case. Both can be stored in memory, unless memory is insufficient. In that case both can be flushed to disk in TempDB.
Table variables are good for storing small datasets (about 100 records or so) – not large unless it is indexed via primary key or unique constraint that is proven to be used in subsequent queries.
A table variable limits recompiling events and avoids the statistics manager; whereas temp tables have statistics performed to help the optimizer determine the most efficient way of referencing the data.
At what point (if any) do you determine to use a temp table over a table variable?
It depends. Performance tests ultimately will prove or disprove the best solution of a temp table vs. table variable.
What is dynamic SQL?
A string that is assembled and executed at run-time.
How is dynamic SQL invoked?
EXECUTE (T-SQL Command String)
EXECUTE sp_ExecuteSQL @Stmt, @Params (declare), @Param1 (value)…
What is the difference between the above commands? EXECUTE runs a statement in Ad-Hoc fashion, requiring compiler to operate.
Sp_ExecuteSQL attempts to reuse query plans and therefore may skip compilation (provided a query plan exists).
When using sp_ExecuteSQL, do referenced tables need to have their database name fully qualified?
YES.
What happens if the database name is not fully qualified?
Then the query is treated like an ad-hoc query – requiring the use of the compiler.
What conditions are appropriate to use a dynamic SQL solution?
A query statement that must change to support a specific request; otherwise, multiple similar queries would need to be created statically. For example, a requirement may state that a query handle up to 3 filtering conditions in a WHERE clause. Each condition may be supplied by itself or in conjunction with others.
What functions types available?
User Defined Inline (returns a table of data), syntactically referenced as a table and achieves the functionality of a parameterized view
User Defined Multistatement table-valued functions
User Defined Scalar (returns a single data value)
Why is it preferable to NOT use the function?
Because the function is called for EACH record, the performance will be slower than if one were to write a CASE statement or a concatenated string (with the + sign).
what is a database trigger?
Essentially a special stored procedure installed on a table and is invoked by INSERT, UPDATE and/or DELETE actions taken on rows within the table.
What 2 special tables are present during the execution of a trigger?
Inserted & Deleted.