Skip to main content
Share Your Experience: Take the 2024 Developer Survey
57 votes

What does the word "SARGable" really mean?

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 ...
mustaccio's user avatar
  • 26.1k
51 votes
Accepted

Why is COALESCE not a function?

It's generally a mistake to look for consistency in SQL Server, even (or especially) in the documentation. You have to remember SQL Server is a mature product that has been developed (and documented) ...
Paul White's user avatar
  • 85.7k
36 votes
Accepted

PostgreSQL: Immutable, Volatile, Stable

IMMUTABLE must be a pure function, whose results depend only on its inputs. This is a very strict requirement; they cannot call other non-immutable functions, they cannot access tables, they cannot ...
Craig Ringer's user avatar
  • 56.6k
33 votes
Accepted

Why the term "relation(al)"?

First of all, I highly recommend the scientific paper in which Dr. Edgar Frank Codd published the relational framework to the general public in 1970, i.e., A Relational Model of Data for Large Shared ...
MDCCL's user avatar
  • 8,520
33 votes

What exactly is the 'online' in OLAP and OLTP?

It is simply a remnant of olden times, when it was used in contrast to batch processing. "Online" here means "interactive", that is, requests to the database are processed as they come and responses ...
mustaccio's user avatar
  • 26.1k
32 votes

Is there a generic term for tables and views?

Postgres (and also relational algebra) calls them relations - tabular things of rows and columns that you can query from. This includes tables, views, materialised views, temporary tables, or even ...
Bergi's user avatar
  • 514
24 votes

What does the word "SARGable" really mean?

For me, SARGable means that SQL Server can perform an index seek using your search predicates. You can't just say the DBMS can "take advantage" of an index, because with a non-sargable predicate, SQL ...
Brent Ozar's user avatar
24 votes
Accepted

What is it called when you search the middle of a string instead of the beginning?

It's called an "un-anchored search pattern", and it looks like this in SQL. foo LIKE '%bar%' If you lack a % on either side, it is said that the search pattern anchors to the start or end of the ...
Evan Carroll's user avatar
  • 63.5k
22 votes
Accepted

What is a SQL Server "GDR"?

A General Distribution Release (GDR) is simply a kind of hotfix, or patch that contains specific fixes, and is intended for installation on all SQL instances. This is in contrast to a Quick Fix ...
David Browne - Microsoft's user avatar
21 votes
Accepted

Is there a generic term for tables and views?

A "thing with columns and rows" is a relation. This is the word Codd used in his seminal paper, starting the RDBM revolution. A "thing that can change but retain its identity" - is ...
fdreger's user avatar
  • 691
16 votes

Why the term "relation(al)"?

The interesting thing behind 'relational database' is, that it does not (primarily) refer to the relations between tables, as you might expect, but it refers to the relation of multiple properties (...
eckes's user avatar
  • 1,456
16 votes
Accepted

In PostgreSQL, what is the difference between a "Stored Procedure" and other types of functions?

Since Postgres functions (CREATE FUNCTION) only run in a (single) transaction context, several important commands cannot be executed inside a function body. Like CREATE DATABASE or CREATE INDEX ...
Erwin Brandstetter's user avatar
16 votes

Why is COALESCE not a function?

According to the SQL standard (ISO/IEC 9075-2:2023(E)), case is an expression, and coalesce is a case abbreviation. <case expression> ::= <case abbreviation> | <case specification&...
jarlh's user avatar
  • 161
12 votes

What does the word "SARGable" really mean?

According to Pro SQL Server Internals by Dmitri Korotkevitch: A Search ARgument ABLE predicate is one where SQL SERVER can utilize an index seek operation, if an index exists. A SARGable predicate is ...
Vic Work's user avatar
  • 465
12 votes

Standard Meaning of "Bounce the Database"

It's been consistent for me: bounce generally means restart - Bounce SQL Server = restart the service, normally - Bounce the server = reboot - Bounce the PC = reboot - Bounce IIS = run iisreset I'd ...
gbn's user avatar
  • 69.9k
12 votes
Accepted

What is a "hashed transaction" in SQL Server Replication terminology?

In reality, this DMV (Dynamic Management View) won't be very helpful for a DBA or Developer and is geared to SQL server Product Support and Product Group. You can ignore this DMV. Hash tables work ...
Sean Gallardy's user avatar
10 votes

Why the term "relation(al)"?

The term "relational" comes from mathematics and has nothing to do with relationships between entities. I'm not a mathematician (whereas Codd had a PhD in Mathematics) and so won't elaborate, but ...
Todd Everett's user avatar
  • 3,548
10 votes
Accepted

What are "folded" and "pivoted" tables?

It's just a different way of representing the data. Also known as wide (pivoted) and long (folded). As an example and to keep it simple, imagine that we have a record with 3 properties: id, name, ...
Mr.Brownstone's user avatar
10 votes
Accepted

Are PostgreSQL clusters and servers the same thing?

tl;dr Yes. Confusion arises from the official terms used in the SQL standard versus more commonly used terminology. Cluster = database server Catalog = a database Schema = a namespace, a grouping of ...
Basil Bourque's user avatar
9 votes
Accepted

What does "ISO standard equivalent" actually mean?

It just means that operator SOME is not some MS proprietary extension. It is part of the (ISO/ANSI) SQL standard.
ypercubeᵀᴹ's user avatar
9 votes

Scale vs. Precision - Why this definition?

Precision and Scale are based on the mathematical concept of Significant Figures. You're not alone in that the current definition/usage for these terms is confusing. How does the total number of ...
John Eisbrener's user avatar
9 votes
Accepted

Difference between UPSERT and MERGE?

Generally, UPSERT is built off of INSERT MERGE focuses on merging/synchronizing tables and provides conditionality (where clauses) DELETE support. UPSERT lacks conditionality, In PostgreSQL, you ...
Evan Carroll's user avatar
  • 63.5k
9 votes
Accepted

What is a "PARTITIONED OUTER JOIN"?

{1} Partitioned Outer Join: Definition ... "Such a join extends the conventional outer join syntax by applying the outer join to each logical partition defined in a query. Oracle logically partitions ...
stefan's user avatar
  • 2,507
8 votes

Name for table with metric_name and metric_value columns

Unpivot Table Being the product of a transformation, your desired form here is called an "Unpivot table". The form you're starting from here is either normalized data, or the result of a ...
Evan Carroll's user avatar
  • 63.5k
8 votes

What does it mean to "materialize"?

Contrary to popular misconceptions, "materializing" has nothing to do with writing anything to disk or a "storage" layer. During the process of executing a query you have two ...
Evan Carroll's user avatar
  • 63.5k
7 votes

What is the difference between a connection and a session?

Connection represents connection to the server over a network or locally through shared memory . A session represents a user process within SQL Server. A connection may be linked with zero or more ...
Mihir's user avatar
  • 71
7 votes
Accepted

I cannot grasp the definition of serializable

Which "same result" do we have in the example? Which reordering can we have to produce the same result? The opening quote is an incorrect statement of serializable isolation. The definition of the ...
Paul White's user avatar
  • 85.7k
7 votes
Accepted

What is the difference between integrity and consistency?

I think the difference is that the two terms refer to different things: Consistency refers to Transactions (it's the C in ACID). Integrity refers to Databases. A transaction is said to be consistent ...
ypercubeᵀᴹ's user avatar
7 votes

Why the term "relation(al)"?

It's an intuitive name when you think of them with natural keys. You can think of a cell value as representing an entity. Relation: Employee |--------+------------+--------| | name | job | ...
JoL's user avatar
  • 289
7 votes
Accepted

Understanding data abstraction levels

Although the definitions of the schemas or levels of representation of the ANSI/SPARC architecture did not reach the desired precision, they are only three, and they are named external, conceptual and ...
MDCCL's user avatar
  • 8,520

Only top scored, non community-wiki answers of a minimum length are eligible