Next version of SQL Server - Wish List

This is from the Dark Omni :) Just thinking out loud what all I would like to have in the next version of SQL Server. This is in no particular order and some might seem totally impossible to implement and some, downright stupid. But, I leave the decision to Microsoft. They are the ones who often yell "SURPRISE!!!"


1. Pass object names as variables (table , column , function , database and server names).

2. Foreign key constraint can reference an unique index of an indexed view (good for referencing a subset of the master table).

3. A function called evaluate, that will accept a string and evaluate the expression in the string and give the result.
Like this: set @a = eval('1+2/100 - 400').

4. Have a way to loop through all the result sets obtained by executing a stored procedure in the backend.

5. Any query written should just be a way of expressing the result that is desired. There should be a real Query Analyzer that analyzes and get the best execution plan and reverse engineer the execution plan to get the best query. An example is, if its a very complicated select query, then you may write a cursor to split the logic to procedural code and SQL server should give back the SET based query for you :)

6. LIKE should support IN clause.
Eg: Select * from tbl1 where col1 like in ('%abc%','%def%')

7. You should be able to do
select * from EXEC stored_proc
It will return the first result set if one exists if not just select the return value of the SP.

8. You should able to do something like:
select Order_id,
( Corresponding -- Keyword :)
poduct_name,quantity,OrderDetailID for max(OrderDetailID)
)
from Orders A inner join OrderDetails B on A.OrderID = B.OrderID
group by Order_ID

Well, I just mean, I don't want to use a subselect for getting the MAX of a value and doing a join with that again.

9. Pivot should allow subqueries
Eg: PIVOT (Col1 for Col2 in (Select distinct Col2 from tlb1))

10. DATE and TIME datatypes.

11. Intellisense

12. Get the datatype for the variables from the columns (I think this is there in Oracle, I am not sure)
Eg:Declare @OrderID Datatype(Orders,OrderID)

13. An extended functionality of common table expression that will append columns to the resultset than rows.
For Eg: Say you have an employees Table which has 4 Emplevels. 1 Programmer,2 ML, 3 PL, 4 PM. You have the usual EmpID, MgrID in that table.

Then, my query will look like this (I cooked up the syntax of course)

with CTE as
(
select EmpID, EmpName,
CTE.EmpID as CTE.EmpLevel + 'ID' ,
CTE.EmpName as CTE.EmpLevel + 'Name'
from Employees A
CROSS APPLY
(Select EmpID, EmpName, Emplevel from Employees B, CTE C
where B.EmpID = C.MgrID) as D
where A.EmpLevel = 'Trainee'
)
select * from CTE

This will give me the result with this schema

EmpID__EmpName__MLID__MLName__TLID__TLName__PMID__PMName

Its basically a recursive self join till you reach a level :)

14. There should be an Excel Interface, where you can open a table, define how you want the data to look, use all the functions you have in excel, sorting and grouping, filtering and when you are done, you should be able to generate the query for that output and by miracle extend this to joining multiple tables (from madness to lunacy)

What do you think? Did I miss anything :) Feel free to comment