Dealing with SQL shows us some limitations and gives us an opportunity to imagine what could be.
Which improvements to SQL are you waiting for? Which would you put on top of the wish list?
I think it can be nice if you post in your answer the database your feature request lacks.
A decent way of walking a tree with hierarchical data. Oracle has CONNECT BY but the simple and common structure of storing an object and a self-referential join back to the table for 'parent' is hard to query in a natural way.
More SQL Server than SQL but better integration with Source Control. Preferably SVN rather than VSS.
T-SQL Specific: A decent way to select from a result set returned by a stored procedure that doesn't involve putting it into a temporary table or using some obscure function.
SELECT * FROM EXEC [master].[dbo].[xp_readerrorlog]
Support in SQL to specify if you want your query plan to be optimized to return the first rows quickly, or all rows quickly.
Oracle has the concept of FIRST_ROWS hint, but a standard approach in the language would be useful.
A way of dynamically specifying columns/tables without having to resort to full dynamic sql that executes in another context.
Clean way to enforce optimal execution plan.
There are hints in MSSQL and Oracle, but you need to persuade the DBMS to use them, they can be silently ignored, and this is cited as a feature, not a bug in documentation.
Improved pivot tables. I'd like to tell it to automatically create the columns based on the keys found in the data.
Implicit joins or what it should be called (That is, predefined views bound to the table definition)
SELECT CUSTOMERID, SUM(C.ORDERS.LINES.VALUE) FROM CUSTOMER C
A redesign of the whole GROUP BY thing so that every expression in the SELECT clause doesn't have to be repeated in the GROUP BY clause
Some support for let expressions or otherwise more legal places to use an alias, a bit related to the GROUP BY thing, but I find other times what I just hate Oracle for forcing me to use an outer select just to reference a big expression by alias.
Operator to manage range of dates (or numbers):
where interval(date0, date1) intersects interval(date3, date4)
EDIT: Date or numbers, of course are the same.
EDIT 2: It seems Oracle have something to go, the undocumented OVERLAPS predicate. More info here.
String Agregation on Group by (In Oracle is possible with this trick):
SELECT deptno, string_agg(ename) AS employees
FROM emp
GROUP BY deptno;
DEPTNO EMPLOYEES
---------- --------------------------------------------------
10 CLARK,KING,MILLER
20 SMITH,FORD,ADAMS,SCOTT,JONES
30 ALLEN,BLAKE,MARTIN,TURNER,JAMES,WARD
Optimizing QBE (Query By Example). Finally giving us a way to pass off responsibility to the database for all those queries that want to match on an arbitrary subset of columns.
EDIT: QBE is a non-SQL methodology for querying relational data. To add it to SQL would maybe look something like
SELECT columns
FROM table
WHERE EXAMPLE (firstname, lastname, birthdate) = ('Fred%', 'Jones', '20090209')
Functionally to support something like what Access has for the graphical querydef designer. You give it a list of fields, and collect a set of matching user inputs, and the optimizer would toss out the columns not specified and optimize on the rest.
Ability to define columns based on other columns ad infinitum (including disambiguation).
This is a contrived example and not a real world case, but I think you'll see where I'm going:
SELECT LTRIM(t1.a) AS [a.new]
,REPLICATE(' ', 20 - LEN([a.new])) + [a.new] AS [a.conformed]
,LEN([a.conformed]) as [a.length]
FROM t1
INNER JOIN TABLE t2
ON [a.new] = t2.a
ORDER BY [a.new]
instead of:
SELECT LTRIM(t1.a) AS [a.new]
,REPLICATE(' ', 20 - LEN(LTRIM(t1.a))) + LTRIM(t1.a) AS [a.conformed]
,LEN(REPLICATE(' ', 20 - LEN(LTRIM(t1.a))) + LTRIM(t1.a)) as [a.length]
FROM t1
INNER JOIN TABLE t2
ON LTRIM(t1.a) = t2.a
ORDER BY LTRIM(t1.a)
Right now, in SQL Server 2005 and up, I would use a CTE and build up in successive layers.
Foreing keys that manage interval dates on other tables
For instance, for tables:
TABLE_A(begin DATE, end DATE)
TABLE_B(day DATE)
Some way to define On TABLE_B:
FOREIGN KEY (day)
REFERENCES INTERVAL ON TABLE_A (begin, endd)
Efficient implementation of SQL standards, such as DOMAINs, for those platforms that don't support them.
Partial Foreing key constraint.
If have those tables:
create table prova_a (a number, b number);
alter table prova_a add primary key (a,b);
create table prova_b (a number, b number);
alter table prova_b add foreign key (a,b) references prova_a(a,b) ;
insert into prova_a values (1,2);
You can insert this without error:
insert into prova_b values (123,null);
insert into prova_b values (null,null);
insert into prova_b values (null,123);
I wish something to avoid this without using check constraints.
I would like to see the ability to use Regular Expressions in string handling.
On my wish list is a database supporting sub-queries in CHECK-constraints, without having to rely on materialized view tricks. And a database which supports the SQL standard's "assertions", i.e. constraints which may span more than one table.
Something else: A metadata-related function which would return the possible values of a given column, if the set of possible values is low. I.e., if a column has a foreign key to another column, it would return the existing values in the column being referred to. Of if the column has a CHECK-constraint like "CHECK foo IN(1,2,3)", it would return 1,2,3. This would make it easier to create GUI elements based on a table schema: If the function returned a list of two values, the programmer could decide that a radio button widget would be relevant - or if the function returned - e.g. - 10 values, the application showed a dropdown-widget instead. Etc.
I know it's wildly unrealistic, but I wish they'd make the syntax of INSERT and UPDATE consistent. Talk about gratuitous non-orthogonality.
More OOP features:
stored procedures and user functions
CREATE PROCEDURE tablename.spname ( params ) AS ...
called via
EXECUTE spname
FROM tablename
WHERE conditions
ORDER BY
which implicitly passes a cursor or a current record to the SP. (similar to inserted and deleted pseudo-tables)
table definition as derived from base table, inheriting common columns etc
Btw, this is not necessarily real OOP, but only syntactic sugar on existing technology, but it would simplify development a lot.
Abstract tables and sub-classing
create abstract table person
(
id primary key,
name varchar(50)
);
create table concretePerson extends person
(
birth date,
death date
);
create table fictionalCharacter extends person
(
creator int references concretePerson.id
);
select on recursive tables:
select * from rdfClass where rdfClass.uri is instance of "foaf:Person";
UPSERT or MERGE in PostgreSQL. It's the one feature whose absence just boggles my mind. Postgres has everything else; why can't they get their act together and implement it, even in limited form?
I'd like the vendors to actually standardise their SQL. They're all guilty of it. The LIMIT/OFFSET clause from MySQL and PostGresql is a good solution that no-one else appears to do. Oracle has it's own syntax for explicit JOINs whilst everyone else uses ANSI-92. MySQL should deprecate the CONCAT() function and use || like everyone else. And there are numerous clauses and statements that are outside the standard that could be wider spread. MySQL's REPLACE is a good example. There's more, with issues about casting and comparing types, quirks of column types, sequences, etc etc etc.
parameterized order by, as in:
select * from tableA order by @columName
Kevin beat me to it by a couple of seconds... but more generally,
select @columnName from @tableName
order by @otherColumnName
etc
It would render swathes of nasty string concatenation followed by
exec (@sql)
instantly unnecessary.
Increased temporal database support in Sql Server. Intervals, overlaps, etc.
Increased OVER support in Sql Server, including LAG, LEAD, and TOP.
SQL Server specific:
Some decent date functions, like TRUNC. Improvements to full text searching (better control over matching logic)
I would LOVE it if SQL server could store different databases within the same database and log files (shared FILEGROUPS) so I can backup the WHOLE server in one go.
Full syntax and error checking of a stored procedure when I compile it (not only when I run it)
Yes, this is starting to sound like "I want all the Oracle features in SQL Server without all the complexity (and cost!)"
I would love to see the ability to put a WHERE clause on at index creation time:
CREATE INDEX BAR ON FOO (FooName, FooId) WHERE FooEnabled = 1;
Then, the optimizer could use this when processing SQL like this:
SELECT FooId, FooName
FROM Foo
WHERE FooEnabled = 1
ORDER BY FooName;
This seems like a solvable problem from both the index creation and optimizer perspectives. Indeed, you can simulate the same thing using function based indexes and/or materialized views. This is messy and can involve changing the SQL issued by an application.
It would be nice to have a syntactic sugar above.
Arrays
I'm not sure what's holding this back but lack of arrays lead to temp tables and related mess.
Check constraints with subqueries, I mean something like:
CHECK ( 1 > (SELECT COUNT(*) FROM TABLE WHERE A = COLUMN))
Some kind of UPGRADE table which allows to make changes on the table to be like the given:
CREATE OR UPGRADE TABLE
(
a VARCHAR,
---
)
My wish list (for SQLServer)
Currently theres one plan - if it is no longer optimal its used anyway or a brand new one is computed in its place.
Native UTF-8 storage
Database mirroring with more than one standby server and the ability to use a recovery model approaching 'simple' provided of course all servers are up and the transaction commits everywhere.
PCRE in replace functions
Some clever way of reusing fragments of large sql queries, stored match conditions, select conditions...etc. Similiar to functions but actually implemented more like preprocessor macros.
These are all MS Sql Server/T-SQL specific:
Comments for check constraints. With this feature, an application (or the database itself when raising an error) can query the metadata and retrieve that comment to show it to the user.
WITH clause for other statements other than SELECT, it means for UPDATE and DELETE.
For instance:
WITH table as (
SELECT ...
)
DELETE from table2 where not exists (SELECT ...)
Something which I call REFERENCE JOIN. It joins two tables together by implicitly using the FOREIGN KEY...REFERENCES constraint between them.
Nested aggregate functions.
PostgreSQL doesn't allow something like SELECT MAX(COUNT(*))...
A CreateOrAlter command.
I am sick and tired of changing back and forth between "Create Proc" and "Alter Proc". I don't care if it is create or alter, I just want the end result to be what I put in the body of the procedure.
Automated dba notification in the case where the optimizer generates a plan different that the plan that that the query was tested with.
In other words, every query can be registered. At that time, the plan is saved. Later when the query is executed, if there is a change to the plan, the dba receives a notice, that something unexpected occurred.
1) [LEFT | RIGHT] SEMI JOIN and [LEFT | RIGHT] ANTI JOIN These would allow me to write something like
-- return customers who have placed at least one order
SELECT c.*
FROM Customers c
LEFT SEMI JOIN o ON o.CustomerId = c.Id
-- return customers who have NOT placed any order
SELECT c.*
FROM Customers c
LEFT ANTI JOIN o ON o.CustomerId = c.Id
This would have exactly the same result as
SELECT c.*
FROM Customers c
WHERE c.Id IN(SELECT CustomerId FROM Orders)
and
SELECT c.*
FROM Customers c
WHERE c.Id NOT IN(SELECT CustomerId FROM Orders)
, respectively. However the IN (or the pretty much equivalent EXISTS) syntax is much messier than my proposed syntax, especially in more complicated cases.
Of course, the semi/anti-joined table can not be referenced so this would be ILLEGAL:
-- Error, can't reference semi joined table.
SELECT c.*, o.OrderNumber
FROM Customers c
LEFT SEMI JOIN o ON o.CustomerId = c.Id
2) It woud be nice to have a good solution to the
WHERE Column IN('a', 'b', 'c')
problem when you don't know the number of values to search for. Perhaps it could be possible to allow
WHERE Column IN(ARRAY @array)
and the calling code would bind @array to an array.
Edit: I just thought of one more
3) Some kind of extensibility to the constraint system, which allows coding of constraints between tables which work perfectly in concurrent environments and which lets me manually do any locking and validation to ensure that the constraint is always satisfied. Triggers can be used currently, but they are very hard to get right considering concurrency.
A relational algebra DIVIDE operator. I hate always having to re-think how to do all elements of table a that are in all of given from table B.
Computed columns.
Hability to add columns on table based on an expression for another one without need to create a view.
MACRO SQL EXPRESSIONS
For instance:
SELECT *
FROM X
WHERE MACRO_1(A, B, C)
Where MACRO_1 can be something like:
A*2/B+C
or something like:
A=2 OR B<C
MACRO_1 is not a function, it's just a macro to be preprocessed and substituted before the final parsing.
They are like functions but because are substituted the optimizer can take advantage of indexes, not like funcions that the optimizer does not know what happens.