When reading SQL standard text (SQL:2003), I can not get the meaning of following sentence (in "Cluase 4.3 Tables" in SQL:2003-1):

SQL-data consists entirely of table variables, called base tables.

I can not find the definition of "table variable" in the standard text, am I miss something obvious?


Community wiki answer:

A table variable is an instance of a table. Tables are called relations in relational algebra - hence relational variable (or relvar for short).

Base table means that they are not views which are derived from base tables.


SQL-data consists entirely of table variables, called "base tables".

"Table variables" is a totally stupid and ridiculously silly phrase. A junior DBA would never use mutter such a silly thing. There is no dodging it. It's also not a term. It's not in italics, or quotes. "Base Tables" is a term, at least insofar as it's used elsewhere,

A base table is a site that holds a table value (see Subclause 4.3, “Tables”). All SQL-data is held in base tables.

So I'm not guessing "table variables" is supposed to be a phrase meaning things that hold state and mutate (variable). And, a base table is the lowest form of storing data without any higher abstractions.

The only place I've seen "base table" used is in SQL Server. An important aside is that the SQL spec differentiates between a few types of Base Tables but they're in the weeds in their parlance, an example is a "regular persistent base table" which is just what most people think of when they say "table".

Yes there is a "relation", and you can even argue for "relation variable". Show me a "Table Variable" -- outside of the Microsoft parlance which is in reference to a T-SQL data type that has 0-todo with this context here.

  • SQL Server documentation: INFORMATION_SCHEMA.TABLES.TABLE_TYPE: varchar(10) Type of table. Can be VIEW or BASE TABLE. – ypercubeᵀᴹ May 1 '18 at 23:08
  • Postgres documentation: information_schema.tables.table_type : character_data Type of the table: BASE TABLE for a persistent base table (the normal table type), VIEW for a view, FOREIGN TABLE for a foreign table, or LOCAL TEMPORARY for a temporary table – ypercubeᵀᴹ May 1 '18 at 23:11
  • I'm not sure your point I'm just saying no one uses "regular persistent base table" (in the weeds) like "table variable", "base table" is occasionally used but if you're using that term, then afaik you're also open to a VIEW being a TABLE which is weird because it's defined as a stored query (afaik) everywhere and not a "irregular table" – Evan Carroll May 1 '18 at 23:14
  • According to the standard: "A view is a named query, which can be invoked by use of this name. The result of such an invocation is called a viewed table." Nothing irregular about it. I agree that when most people use "table" they mean "base table". My point is that the term (base table) is used, even if not very much, in the documentation of both SQL Server and Postgres. – ypercubeᵀᴹ May 1 '18 at 23:18

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.