IN same as = ANY. NOT IN same as NOT = ALL. NOT = ANY is true for all except NULL
NOT IN ('a', 'b', 'c', NULL) will never return any rows
cast to integer truncates and cast to decimal rounds (up to even and down to even)
truncation of display data by cast is not permitted in ANSI mode
NUMBER data type is stored as floating point with 100 as exponent base
Has variable length storage: 1 byte exponent and up to 17 bytes of mantissa
Value of 0 and NULL are stored as 0 length
if COMPRESS is specified, needs 1 byte as length, otherwise 2 bytes
can have floating values: fixed scale NUMBER(*,m) or both floating NUMBER(*)
ANSI SQL, auto rollback only on failures (deadlock, failed DDL, ROLLBACK)
ANSI style CAST CHAR implicitly trims leading spaces irrespective of ANSI or BTET mode
TD style CAST CHAR front pads with spaces for max digits possible + 1 for the sign
Syntax error is a failure in BTET model, but an error in ANSI mode
MSR cannot contain a DDL statement and if it does, then it’s considered failure.
Sampling: FROM … SAMPLE [WITH REPLACEMENT] [RANDOMIZED ALLOCATION] WHEN … THEN n[,n…] … END. n: fraction or count
Stratified: multiple sampling group, each with different probability
WITH REPLACEMENT: non mutually exclusive groups
RANDOMIZED ALLOCATION: Sample across all AMPs instead of per AMP
SAMPLEID function returns the ID for each of the multiple sample sets
Order of evaluation
WHERE > GROUP BY/HAVING > OLAP/QUALIFY > SAMPLE/TOP > SELECT/ORDER BY
TOP N with no ORDER BY is always faster or equivalent to QUALIFY with ROW_NUMBER()
SUBSTRING can accept 0 or -ve values for start pos, but no -ve values for length
SUBSTRING works on INT by first converting them to string that has place of signs
Specification for Data Formatting (SDF)
G/D: number grouping/decimal character (,/. for USA)
I/F: # of Integer/Fractional digits
L/C/N-O/U/A: currency/iso/name-dual currency symbol. can appear either at beginning or end of the format string. LL/OO/--: floating symbols
EEE/EEEE: short/long day
HH/MI/SS/T/DS(n)/Z: hour/min/sec/(12 hour format with AM/PM)/fractional seconds/zone
UPDATE ELSE INSERT/MERGE must qualify PI and partitioning columns
A positioned UPDATE/DELETE on a table with triggers causes error
Time value is acquired at request level. All stmt in MSR will have same CURRENT_TIME
SELECT AND CONSUME:No WHERE clause, blocks on empty table, TOP 1 only, rollback only if explicit transaction rollbacks, no rollback if is part of MSR/Macro and fails
ROT: Frequent UPDATE and DELETE on queue table causes PEs to rebuild FIFO queues
SET tables only ignore duplicates in multi-row operations, give error on single row dups
An unusally long MERGE step indicates inserting into a SET table with large number of duplicates
IDENTITY col:
if GENERATED ALWAYS, user specified values ignored with a warning
if exceeds MAXVALUE and NO CYCLE, it’s failure
Each PE has range assigned, so it’s possible to exceed MAXVALUE with few rows
For bulk insert (load, insert-select?) each AMP reserves a block of IDs
GENERATED BY DEFAULT doesn’t consider any previously explicit values.
Search path for unqualified functions: current DB, SYSLIB and then TD_SYSFNLIB
TD_SYSFNLIB database contains Domain Specific functions
TD14+: Can create PI and SI on most UDT except LOB,PERIOD,VARIANT,ARRAY
TD_ANYTYPE in C UDFs, is resolved at run-time. (COLTYPE = ‘++’)
CURRENT_USER/CURRENT_ROLE, unlike USER/ROLE return proxy user/role for trusted session if set
NULL values are ordered first unless explicitly NULL LAST clause in ORDER BY
TT_TIMESTAMP is different for each row even within the same transaction, same statement
TEMPORAL_TIMESTAMP or TEMPORAL_DATE: is the value set at either first non-locking statement or when explicitly referencing these functions.
Feature
CURR
SEQ VT
SEQ TT
NONS VT
NONS TT
AS OF
NONT
Result Temporal?
-
Y
PA?Y:-
-
-
-
Update
-
VT-PA
-
-
Y
set sess
Y
VT
Y
Y
Y
Ins/Del/Mrg/Upd
VT
VT
VT
Y
Only 1 Tbl in qry
-
TT
-
-
-
STJI
Y
Y
-
Y
Y
-
MTJI
-
Y
-
Y
Y
Positioned Cursor
Y
-
-
-
-
-
non-RI Constraints
Y
Y
-
Y
-
-
-
RI Constraint
Y
Y
Y
non-temp parent
NONSEQUENCED DML do not create history rows in valid-time
Default PA for SEQUENCED queries is all times
FLOAD Yes, MLoad No
RI are not enforced. Child FK must include DATE/TIMESTAMP column. No gaps in VALIDTIME values in parent tables allowed.
Temporal Rel Const: When Child is non-temporal
NONTEMPORAL modifications are always logged in AccessLoggging tables
NONTEMPORAL cannot be used in SP
Can be disabled completely DBSControl flag
TT_TIMESTAMP is tracked individually for each row, even within the same transactions, unless a row modification results in multiple rows. e.g update causes insert of another row.
If PV isn’t specified explicitly, PV for the row is TEMPORAL_TIMESTAMP built-in function, which is the time temporal table is first non-locking referenced in a transaction.
Uniqueness: current, sequenced or nonsequenced uniqueness
To minimize high JI maintenance, use NONSEQUENCED/NONTEMPORAL DML
EXPAND ON can be interval, anchor point or anchor period.
EXPAND ON Period_Dt_Tm AS Day_Dt_Tm BY ANCHOR PERIOD DAY AT TIME '00:00:00'
Anchor can be day, month_begin, month_end, any week day
TD14+: Anchor can also be hour, minute, second, millisecond
For interval, expands on exact interval, last expansion might be partial
For anchor point, expands at anchor point if begin(a.p.) falls in the expanding period
For anchor period, expands when expanding period overlaps expanded period
Constraints:
Table/Column: CURRENT VT, SQUENCED VT, NONSEQUENCED VT
RI: CURRENT VT/TT, SEQUENCED VT/TT, NONSEQ VT/TT (only allowed bet temporal child and either non-temporal parent or different type temporal parent).
For RI constraints, only partial time of child row may be sufficient.
Temporal Relationship Constraint: When only parent is VT temporal.
child must include date/timestamp column as FK
parent VT must be non-overlapping and contiguous with no gaps
SEQUENCED VALIDTIME aggregates return values for each distinct validtime period (Solution for for Real-World Temporal Problems slide #53)
Suggested partitioning:
VT: CASE_N(<valid AT -‘12:59’> OR VCOL is NULL, NO CASE);
TT: CASE_N(<open>, NO CASE);
BT: CASE_N(<valid> AND <open>, <invalid> AND <open>, <closed>)
SELECT *: includes temporal columns only for non-sequenced queries