SKILL 2.4. Query Temporal data and non-relational data

Question 1

Temporal Tables - Requirements

Answer 1

A primary key constraint

Two DATETIME2 columns with chosen precision to store the start and the end of the validity period of the row

The start column needs to be marked with the clause GENERATED ALWAYS AS ROW START

The end column needs to be marked with GENERATED ALWAYS AS ROW END

The table option SYSTEM_VERSIONING needs to be set to ON

A linked history table which SQL server creates for you

PERIOD FOR SYSTEM_TIME(“startcolumn”,”endcolumn”)

Question 2

Temporal Tables - format

Answer 2

CREATE TABLE dbo.Products

(

productid INT NOT NULL

CONSTRAINT PK_dboProducts PRIMARY KEY(productid),

productname NVARCHAR(40) NOT NULL,

supplierid INT NOT NULL,

categoryid INT NOT NULL,

unitprice MONEY NOT NULL,

– below are additions related to temporal table

validfrom DATETIME2(3)

GENERATED ALWAYS AS ROW START HIDDEN NOT NULL,

validto DATETIME2(3)

GENERATED ALWAYS AS ROW END   HIDDEN NOT NULL,

PERIOD FOR SYSTEM_TIME (validfrom, validto)

)

WITH ( SYSTEM_VERSIONING = ON ( HISTORY_TABLE = dbo.ProductsHistory ) );

Question 3

What does HIDDEN property do in a Temporal table

Answer 3

When using SELECT * it does not return that property

Question 4

FOR SYSTEM_TIME AS OF

Answer 4

returns a table with rows which are between SysStartTime and SystemEndTime

Question 5

FOR SYSTEM_TIME FROM datetime TO datetime

Answer 5

Returns table with values for all row versions that were active any time during the time specified, excluding the upper boundary

Question 6

FOR SYSTEM_TIME BETWEEN datetime AND datetime

Answer 6

Returns table with values for all row versions that were active any time during the time specified, including the upper boundary

Question 7

FOR SYSTEM_TIME CONTAINED IN(datetime,datetime)

Answer 7

Returns a table with the values for all row versions that were opened and closed within the specified range

Question 8

FOR SYSTEM_TIME ALL

Answer 8

returns the union of rows that belong to the current history table

Question 9

AT TIMEZONE

Answer 9

Period coulumns store time in the UTC time zone

Question 10

XML document - characteristics

Answer 10

It is “well-formed”
XML documents are ordered - means not ordered by a criteria but the position of the elements matter
Case-Sensitive Unicode text

Question 11

Difference between a Document and a Fragment

XML

Answer 11

The document has a single root node. If you delete that element you get a XML fragment

Question 12

Most used metadata description of XML documents

Answer 12

XML Schema Description (XSD)

Question 13

FOR XML RAW - output

Question 14

FOR XML RAW - charcteristics

Answer 14

Its not a XML document because the root node is missing.
In RAW mode every row from the returned result set converts to a single element named row, and columns translate t attributes of this element

Question 15

FOR XML AUTO

Answer 15

easy to use and returns query results as nested XML elements

Question 16

What is important in a XML statement

Answer 16

Use ORDER BY statement because without the ORDER BY statement the order of the elements is unpredictable

Order of the SELECT statement defines the returned XML

Question 17

XQuery

Answer 17

is a standard language for browsing XML

Case sensitive

Question 18

FOR JSON AUTO

Answer 18

formats the JSON automatically.

You do not have much influence on the format of JSON

Question 19

FOR JSON PATH

Answer 19

You have much more influence than in JSON AUTO
You can nest elements with a “dot”

SELECT contactname AS [Contact.Name]
FROM Sales.Customers
FOR JSON PATH;

Result:
[

{

  "CustomerId":1, 

  "Company":"Customer NRZBB",

  "Contact":{ 

     "Name":"Allen, Michael"

  }

}

]

Question 20

FOR JSON - additional clauses

Answer 20

ROOT - adds single, top level member
INCLUDE_NULL_VALUES - includes nulls in the output - they are excluded by default
WITHOUT_ARRAY_WRAPPER - removes square brackets

Question 21

SWITCHOFFSET

Answer 21

SWITCHOFFSET function takes 2 arguments, the data type to be returned and the timezone required