Questions tagged [sql-standard]

For questions concerning the SQL Standard itself (also known as the ANSI or ISO SQL Standard) including writing queries that conform to that standard. This tag is NOT to be used for questions about SQL Server Standard Edition.

Filter by
Sorted by
Tagged with
6
votes
2answers
2k views

Is Microsoft SQL Server 2016 fully ANSI SQL-92 compliant?

I am trying to find a compliance confirmation for MS SQL 2016 - if it is fully compliant with ANSI SQL-92 starndard. I found this article on Microsoft Docs which states it is not, but it refers to ...
2
votes
2answers
367 views

Creating same unique constraint with different names

In MySQL 5.7 alter table mydb.roles add constraint uk1 unique (role_name); alter table mydb.roles add constraint uk2 unique (role_name); The 2nd alter command threw me a warning stating: ...
0
votes
2answers
121 views

What is the F301: the CORRESPONDING clause in query expression?

The SQL 2011 Spec supports a CORRESPONDING clause, Feature F301, “CORRESPONDING in query expressions”: What is this feature? How is it used? And, is it supported by any industry RDBMs?
-2
votes
1answer
2k views

What is a “clob” or Character Large Object?

I see in this post on listagg — Rows to Delimited Strings talking about SQL's LISTAGG() clause ON OVERFLOW The return type of listagg is either varchar or clob with an implementation defined length ...
2
votes
1answer
370 views

What is the SQL Standard method of doing LIMIT OFFSET?

I know LIMIT and OFFSET are pretty common but unstandardized methods to page through a result set. I know there is a standardized method, but I can never find this when I'm going to look it up, The ...
1
vote
2answers
295 views

Are duplicates in Read Commited standard behaviour?

This refers to this question: Duplicate records returned from table with no duplicates The "read commited" isolation level assumes that one transaction could read same record differently, but it does ...
2
votes
1answer
1k views

How do I do a JOIN in ANSI SQL 89 syntax?

In Oracle, I use SELECT * FROM table1...JOIN... where the dots represent either the type of join or the condition on which to be joined. Is this ANSI 89 syntax? If not, then how would I perform a ...
2
votes
1answer
456 views

Is INSERT INTO … SET standard SQL?

I'm just wondering if this syntax is standard or where it comes from? For example INSERT INTO gtest3 SET gDateTime='2017-07-16 01:00:00' You can see it documented in MySQL but PostgreSQL does not ...
6
votes
1answer
599 views

Where can I find the SQL-89 standard?

This question is different but similar to this request looking for SQL-86. SQL 1989 is almost 30 years old. Is this spec freely available anywhere. The standard does not have a Wikipage or the like? ...
1
vote
1answer
163 views

How does the SQL Spec specify literals with a decimal should be typed?

Different databases implicitly type numeric literals differently, MySQL types 1.0 as decimal(2,1) PostgreSQL types 1.0 as numeric Which one of these is correct?
0
votes
2answers
157 views

Postgres CTE execution guarantees, inlining

Following query with non deterministic function yields two same rows - the CTE is computed and re-used. WITH foo AS (SELECT uuid_generate_v4() AS id) SELECT id FROM foo UNION ALL SELECT id FROM foo |...
7
votes
2answers
811 views

Does SQL Server support custom DOMAINs?

PostgreSQL supports the DOMAIN specification, from SQL 2011 working draft spec, A domain is a named user-defined object that can be specified as an alternative to a data type in certain places ...
2
votes
2answers
209 views

Definition of “table variable” and “base table”

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. ...
7
votes
1answer
1k views

Why does information_schema have “YES” and “NO” character strings rather than booleans?

I was taken by surprise to find that the is_nullable column for this query is typed as character varying (3) rather than boolean: select column_name, data_type, is_nullable from ...
22
votes
2answers
1k views

Where can I find the first standardization of SQL, SQL-86?

This question is different but similar to this request looking for SQL-89. The first draft of SQL is labeled SQL-86. Numerous references are made to it. Is this available to download? Wikipedia doesn'...
5
votes
1answer
4k views

Using a CREATE TABLE AS SELECT how do I specify a WITH condition (CTE)?

There is an old and deprecated command in PostgreSQL that predates CREATE TABLE AS SELECT (CTAS) called SELECT ... INTO .... FROM, it supports WITH clauses / Common Table Expressions (CTE). So, for ...
0
votes
2answers
2k views

What does `<>` mean?

What does <> do in the following WHERE clause, WHERE posn_id <> rid
0
votes
1answer
178 views

How does the spec store JSON on a table?

From this question, I quoted the docs, "MariaDB implements this as a LONGTEXT rather, as the JSON data type contradicts the SQL standard, and MariaDB's benchmarks indicate that performance is at ...
2
votes
2answers
2k views

Can IDENTITY COLUMNS generate UUIDs?

I'm just wondering if either the spec of the implementation (PostgreSQL) provides for generating identity columns from UUIds or the like. Is there an alternative to, CREATE TABLE f ( id uuid ...
1
vote
0answers
1k views

Why Turning ANSI_WARNINGS Off Causes Error Updating Col Used as a Source of a Computed Col?

I have a stored procedure that updates the source column used in a computed column. That stored procedure has SET ANSI_WARNINGS OFF;. When the procedure executes, and tries to update the source ...
3
votes
1answer
513 views

Tool to test RDBMS SQL dialect for ANSI SQL standard compliance

We are developing an interface to our internal data storage and manipulation tool that would allow people to use SQL dialect. We decided that it would be perfect to make our SQL dialect as close to ...
1
vote
1answer
55 views

Does ANSI SQL standard inlcude FORALL predicate?

I need to get all predicate of SQL (that was included in ANSI standard any version). Some databases (Oracle) have FORALL predicate. But I am not sure if that is part of the SQL specifications. Does ...
11
votes
1answer
1k views

Does the SQL Spec require a GROUP BY in EXISTS ()

Microsoft currently permits this syntax. SELECT * FROM ( VALUES (1) ) AS g(x) WHERE EXISTS ( SELECT * FROM ( VALUES (1),(1) ) AS t(x) WHERE g.x = t.x HAVING count(*) > 1 ); Notice ...
11
votes
2answers
2k views

Are there standard SQL functions with side effects?

Do the SQL standards define functions with side-effects? For example, do they have functions for writing into files* or to update values in certain columns of a table when you do something like ...
4
votes
2answers
114 views

How do i crack this non-ansi code blob

I've just started a new job about a month ago working for a software company that is really understaffed. I've been tasked with modernizing some of this old non-ansi SQL code and I'm having a ...
0
votes
2answers
102 views

Is it possible to reliably number rows in some order conforming to SQL standard?

For example, if I have a table with single column numbers and I want to 1) sort them in asc/desc order 2) (re) number them from 1 to n, using that order (so I can say that this number is ith biggest/...
8
votes
4answers
459 views

Which SQL standard is most broadly and completely supported?

I want to concentrate on one of the SQL language specs for learning and reference purposes. Which version of the SQL standard is most broadly supported across DBMS'es so that I can focus my time and ...
12
votes
1answer
3k views

ANSI/ISO plans for LIMIT standardization?

Are there currently plans to standardize one best way of limiting the number of results returned by a query? The stack overflow question at Is there an ANSI SQL alternative to the MYSQL LIMIT ...
2
votes
1answer
1k views

What does “ISO standard equivalent” actually mean?

I'm reading about comparison operators. In the documentation, Comparison Operators Modified by ANY, SOME, or ALL it says (emphasis added): Comparison operators that introduce a subquery can be ...
1
vote
1answer
32 views

Is the everything-is-arithmetic MySQL specific?

Today I learned that SELECT f1,EXISTS(SELECT ...),f2 is valid in MySQL and, reading from StackOverflow, apparently in PostgreSQL as well and the EXISTS part is 0 when the subquery result is empty and ...
0
votes
1answer
93 views

Reason of poor performance of ANSI joins in Oracle 9i

Please before flagging as duplicate, read the last paragraphs. In an Oracle 9i database this query runs in 0.18 seconds: select count(*) from dba_synonyms s,dba_objects t where s....
1
vote
2answers
3k views

SQL Text data type deprecated?

I have read that Microsoft SQL Server has deprecated the text data type in favour of varchar(max). PostGreSQL already allows varchar (without a length) as an alternative for text. The question is, ...
1
vote
0answers
36 views

What are all the catalogs that RDBMS have to implement to be ANSI compliant?

My goal is to be able to go into any environment, and immediately get a general idea of what is going on in a database server. I know most RDBMS implement INFORMATION_SCHEMA, which has a lot of useful ...
4
votes
2answers
5k views

Converting Tables to Ansi Padding On

I am currently working with a SQL Server 2008 R2 Server and a database which uses a lot of tables that are a mixed bag of ANSI_PADDED Settings. 90% of the tables have Columns with Ansi Padding on AND ...
2
votes
1answer
864 views

query that does functionality of listagg without use of it [closed]

I want to write a query so that it does functionality of listagg in oracle but I don't want to use it. Actually I don't want any function like lag and lead also. My table is- Emp_id ...
7
votes
1answer
2k views

Which DBMS are more standard compliant? [closed]

I find it frustrating that the same queries don't work in different DBMS. Example, some DBMS (such as Microsoft SQL Server and MySQL) seems to support the information_schema table, while others (such ...
0
votes
2answers
140 views

Where to place a foreign key column? [closed]

This isn't exactly related to modeling the database but rather about "standardizing" column placement. Where should I generally put my FK column? As the last column of the table? At the start just ...
4
votes
3answers
483 views

Obtaining gratis copy of the SQL specification?

There used to be a public link to the JTC 1/SC 32 working drafts of 9075:2003, but the link I had has not worked since they started on the post-2003 revisions. Given that there are publicly available ...
6
votes
5answers
5k views

How different are the top DB vendor's SQL (DML-) dialects from each other?

Does someone who has worked extensibly SQL-wise with at least two top DB products (such as Oracle, SQL Server, Informix, Sybase, DB2, Teradata) know how different the DB vendor's SQL dialects are from ...
7
votes
2answers
2k views

Single data type for imprecise date values, as allowed by ISO 8601

How can I store date and time values with reduced precision in a PostgreSQL type, and have them behave as date and/or time values? ISO 8601 allows date values with reduced precision. ‘1964’, ‘1964-05’...
2
votes
1answer
546 views

SQL ISO Standard for group concatenation

Is there an operator in the latest standard SQL:2011 (or earlier) that will handle group concatenation the likes of which we currently have to resort to these tricks for? GROUP_CONCAT in MySQL FOR ...
5
votes
1answer
284 views

Are view references in a query properly called “derived tables” as such?

While answering a question on stackoverflow, I presented a definition of derived tables: A derived table is a complete query, inside of parentheses, that is used as if it were a real table. But a ...
1
vote
1answer
74 views

What are the SQL standards [closed]

Some job descriptions mention required knowledge of SQL standards. What do they mean by that? I found something on wiki http://en.wikipedia.org/wiki/SQL#Standardization but I don't really know what ...
4
votes
1answer
187 views

SQL “Standard” guide?

I am currently learning TSQL and am learning that were possible, I should use "standard" SQL dialect. Where can I find out which commands are "standard" and which are TSQL?
14
votes
2answers
1k views

Where can I find the SQL standard document?

Where can I find a legal copy of the ISO SQL 2008 standard?
2
votes
2answers
6k views

MySQL case sensitivity for table names

Why is it that different platforms (linux vs windows) does not support the same naming conventions? I'm stunned by the fact that the documentation uses file names as an excuse for this. Yes, windows ...
0
votes
1answer
75 views

Does SQL/MED (ISO/IEC 9075-9) defines a writable foreign data wrapper interface

I just can't find anything about it. I don't have the paper or draft of the SQL:2011, and have almost only drafts from SQL:2008, 2006, 2003. Does anyone know if "SQL/MED (ISO/IEC 9075-9)" defines a ...
2
votes
2answers
1k views

Equality in JOIN or WHERE clause? Efficiency and style

Of the two queries below, which is the most efficient for postgresql? And which has better style in terms of readability, etc. The difference is the placement of the statement doctor.type != 'surgeon'...
1
vote
1answer
131 views

What relevance does the table-level ANSI_NULL setting have?

Apparently, SQL Server stores the current ANSI_NULL setting when creating a table (see related questions here and here). However, when I execute SELECT * FROM myVeryOldTable WHERE ...
3
votes
1answer
2k views

Converting multiple SQL implicit joins into explicit joins

I am trying to convert several queries that use implicit joins into explicit joins, as per the standard ANSI/ISO Entry SQL-92 [1]. But after a lot of googling I could not find any examples or guides ...