Final Study Topics

Question 1

What are the 4 types of table expressions?

Answer 1

Derived Tables
CTE
View
Inline Table-Valued Functions

Question 2

What are the 3 millisecond values that datetime can have without rounding?

Answer 2

0, 3, 7

Question 3

What is the only type of function that can be used and still be a SARG?

Answer 3

CAST(@DateVar as Date)

Question 4

What datatypes can JSON elements contain?

Answer 4

String
Number
Boolean
object
array
null

Question 5

Where does WITH CHECK OPTION go within a View Creation Statement?

Answer 5

At the end of the statement:

Create view ViewName
as
Select * from table
with check option;

Question 6

Which function statement is more efficient?

Answer 6

Inline Table Value Function (MultiStatement Table Value Function is slower)

Question 7

What are the steps needed to insert into an identity field on a table?

Answer 7

SET IDENTITY_INSERT ON;
INSERT INTO TABLE (IDENTITY) VALUES (VALUE)
SET IDENTITY_INSERT OFF;

Question 8

How can you get a the time in a different timezone when you have a datetime?

Answer 8

SWITCHOFFSET(DateTime AT TIME ZONE TimeZone, 0)

Examples of TimeZone are: ‘Pacific Standard Time’, ‘Eastern Standard Time’

Question 9

What is the syntax for datefromparts?

Answer 9

DATEFROMPARTS(YEAR, MONTH, DAY, HOUR, MINUTES, SECONDS)

Question 10

What does the EOMONTH function do? What is the syntax?

Answer 10

It gives the last day of the month passed to it:

EOMONTH(DateVar, MonthOffset)
IE: dateadd(day, 1, EOMONTH(sysdatetime(), -1)) gets the first day of the month

Question 11

What is the syntax for creating a temporal table?

Answer 11

CREATE TABLE dbo.TableName
(
[ValidFrom] datetime2 GENERATED ALWAYS AS ROW START
, [ValidTo] datetime2 GENERATED ALWAYS AS ROW END
, PERIOD FOR SYSTEM_TIME (ValidFrom, ValidTo)
)
WITH (SYSTEM_VERSIONING = ON (HISTORY_TABLE = dbo.TableHistory));

Question 12

How do you query data from a temporal table?

Answer 12

SELECT * FROM dbo.TemporalTable
FOR SYSTEM_TIME
BETWEEN ‘2019-01-01 00:00:00.0000000’ AND ‘2020-01-01 00:00:00.0000000’
ORDER BY ValidFrom;