User terms surrogate key and natural key for primary key as a variant.
Surrogate Key:
Surrogate keys are keys that have no
“business” meaning and are solely used
to identify a record in the table.
Such keys are either database
generated (example: Identity in SQL
Server, Sequence in Oracle,
Sequence/Identity in DB2 UDB etc.) or
system generated values (like
generated via a table in the schema).
Natural Key:
Keys are natural if the attribute it
represents is used for identification
independently of the database schema.
What this basically means is that the
keys are natural if people use them
example: Invoice-Numbers, Tax-Ids, SSN
etc.
Surrogate Keys vs Natural Keys for Primary Key
Etymology
"Collation" is probably best defined on etymology.com,
late 14c., "act of bringing together and comparing,"
It hasn't changed at all in the past 600 years. "collate" means "to bring together" and whether it's the ordering of a book, chronologically or otherwise in a database, it's all the same.
Computer Science
As it applies to Computer Science, what we're doing is separating the storage mechanism from the ordering. You may have heard of ASCII-betical. That refers to a comparison based on the binary encoding of ASCII characters. In that system, storage and collation are two in the same. If the ASCII-standardized "encoding" ever changed the "collation" (order) would change too.
POSIX started to break that with LC_COLLATE
. But as we move into Unicode a consortium emerged to standardize collations as well: ICU.
SQL
In the SQL spec there are two extensions to provide collations,
F690
: “Collation support:
Includes collation name
, collation clause
, collation definition
and drop collation
.
F692
: Extended collation support,
Includes attaching a different default collation to a column in the schema.
Essentially these provide the ability to CREATE
and DROP
collations, to specify them for operators and sorts, and to define a default for columns.
For more information on what SQL has to offer check out,
Examples
Rather than pasting a limited example, here is the PostgreSQL test suite it's pretty extensive. Check out at least the first link and look for 'Türkiye' COLLATE "tr-x-icu" ILIKE '%KI%' AS "false"
Best Answer
The term "sargable" was first introduced by P. Griffiths Selinger et al. in their 1979 paper "Access Path Selection in a Relational Database Management System", published by ACM. For non-ACM members there's a copy of that paper at http://cs.stanford.edu/people/chrismre/cs345/rl/selinger.pdf
The term is defined in this paragraph:
In other words, a sargable predicate is such that can be resolved by the storage engine (access method) by directly observing the table or index record. A non-sargable predicate, conversely, requires a higher level of the DBMS to take action. For example, the outcome of
WHERE lastname = 'Doe'
can be decided by the storage engine by simply looking at the contents of the fieldlastname
of each record. On the other hand,WHERE UPPER(lastname) = 'DOE'
requires execution of a function by the SQL engine, which means the storage engine will have to return all rows it reads (provided they match possible other, sargable predicates) back to the SQL engine for evaluation, incurring additional CPU costs.You can see from the original definition that sargable predicates can apply not only to index scans, but also to table (segment in System R terminology) scans, as long as the conditions "column comparison-operator value" are met and therefore they can be evaluated by the storage engine. This is indeed the case with Db2, a descendant of System R in many ways:
The fact that in SQL Server-speak sargable predicates are only those that can be resolved using index seeks is probably determined by its storage engine's inability to apply such predicates during table scans.
Sargable and non-sargable predicates are sometimes described as "stage 1" and "stage 2" predicates respectively (this also comes from Db2 terminology). Stage 1 predicates can be evaluated at the lowest level of query processing, while reading table or index records. Rows that match stage 1 conditions, if any, are sent to the next level, stage 2, of evaluation.
1 -- Segment in System R is the physical storage of a table's tuples; a segment scan is somewhat equivalent to a table scan in other DBMSes.
2 -- RSI -- RSS3 Interface, a tuple-oriented query interface. The interface function relevant to this discussion is NEXT, which returns the next row matching query predicates.
3 -- RSS, or Research Storage System, the storage subsystem of System R.