Top 5 features you would like to see in the next version of SQL Server

Here is an opportunity to discuss about the top 5 features that you would like to see in the next version of SQL Server. It would be nice if you can include a sentence for each feature explaining why it is required. Please use features of SQL Server 2005 as starting point meaning do not ask for those which are already in SQL Server 2005. And the features should be restricted to the query language (SQL) and the programming language (TSQL) only. Examples include enhancements to DDLs, new relational features, new programming capabilities or data types.

Lastly, note that this is just an informal request from me. This doesn’t mean that these features will get into the next version of SQL Server. But I will take these requests and file them along with existing suggestions/sqlwish emails that many in our team maintain. And it can help prioritize the list for the product eventually.


Summary of the responses received so far can be found at

Comments (82)

  1. I’m afraid I don’t know if this is already in sql 2005, but the very top item on my list is to get rid of the stupid "This might cause cycles or multiple cascade paths" error message when trying to add a foreign key constraint.

    Not sure if that counts as part of SQL / TSQL or not (it’s an error message that shows up when you do ALTER TABLE but the language can’t do much if the engine doesn’t support it I guess).

    Not supporting cascade cycles I can handle, but multiple acyclic cascade paths is a very common requirement in what I do and I’m always stuck implementing poor, inferior, hacky workarounds, or just punting on the issue entirely and leaving possible scenarios entirely unhandled.

  2. sqletips says:

    Yes, ALTER TABLE definitely counts or in this case creation of any FK constraint that can introduce cycles. Unfortunately this is not fixed in SQL Server 2005 also. We only added the SET NULL and SET DEFAULT options for the cascading constraints.

  3. Matt says:

    I too am sorry that I am not as familiar with SQL 2005, but here goes…

    * I would like to segment db objects by functional group, much the same way you can do (dbo.Authors), I would like

    * Have FTS more integrated into the table and column structure so that the queries will run against a regular index or FTS index to produce the quickest result via the optimizer all transparent to the user.

  4. Cory Nelson says:

    I know it’s simple, but it’s the one thing I really miss from other DBs that makes paging through data so much easier: the LIMIT clause.

  5. sqletips says:

    Re: "would like to segment db"

    Please look at the new CREATE SCHEMA feature. You can create schemas that correspond to your functional groups. See also: the data model in the AdventureWorks database.

    Let us know what you think and how that works for you.

    Clifford Dibble,

    SQL Server Engine, Program Manager

  6. Just in case I wasn’t clear – the ability to support cycles would be nice, but the case I really care about is this, which isn’t a cycle but is still prohibited in SQL2K:

    Table A has a foreign key (on delete cascade) to table B

    Table B has a foreign key (on delete cascade) to table C

    Table A has another foreign key (on delete cascade) directly to table C.

    I run into this at least once a month and it drives me crazy. It’s by far my biggest complaint about SQL2K.

    There’s one other thing I’d like to see, which is the ability to create a unique index on a nullable field which uses the standard SQL meaning of null, that is, treats multiple nulls as if they were *different* from each other. In other words, the field must be unique if present, but there can be as many records as you like that don’t have it at all.

    (By the way I very much appreciate you asking for feedback like this; I’ve been really impressed with the responsiveness and accessibility of the C# and VS.NET teams but up until now I hadn’t been so impressed with the SQL team; this entry and your responses have changed that :) )

  7. Kelly Summerlin says:

    I hate this error message:

    Conversion failed when converting a value of type varchar to type int. Ensure that all values of the expression being converted can be converted to the target type, or modify query to avoid this type conversion.

    You get this when trying to alter a table converting the data or when you are inserting from one table to another. The only clue that I have as to which column is causing the problem is that I know it is a varchar column going into an int.

    I see this all the time when loading and converting questionable data. It would help greatly to know the name of the column. It would be nice if it could give the row or PK value for the row causing the problem as well.

  8. JasonF says:

    1) How about some automation in the creation of CRUD stored procedures? Right now, that’s totally the responsibility of a developer or external tool (VS or a code generator).

    Maybe even virtual stored procedures that are just automatically there for each and every table in a database. So, as a developer, all that I need to do is create (or alter) a table, and then I get corresponding Create, Read, Update, and Delete procs for that table. I say virtual because I’m not picturing any actual T-SQL that can be viewed or modified–the SP just exists.

    If they are virtual, in the same aspect as the C# keyword, then also allow the developer to override functionality. Perhaps this is just as simple as creating a new SP with the same name. But, maybe it could also be via a new OVERRIDE clause in T-SQL, still allowing for the base/virtual SP to be invoked by the overriding SP.


    2) I’d like to see PIVOT extended to be more dynamic, so that instead of having to explicitly list the values that are being pivoted to become columns, I could do something like:





    FOR Quarter

    IN (select distinct quarter from sales)

    ) AS p

    Or, perhaps just make the IN() clause optional so that the QP just automatically grab all values for Quarter (throw error if the distinct count is > some threshhold).


    3) Peer relationships. Right now, we can create Primary-Foreign Key relationships that give us Parent/Child capabilities. But, when we start talking about table-to-table relationships, we have to manually introduce a bridge table that contains both of the primary keys in the tables being peer-linked.

    I always thought of the bridge table concept as being a sort of a hack, but I have to admit that I don’t have a better solution at the moment. But, I would like the database to provide me with some sort of functionality automatically.


    4) Array fields/multi-valued data capable of being stored in a single field of a single row in a table. C’mon, you already gave us XML, which can represent hierarchical data. Let’s extend the concept to arrays of scalar types now.

    This could actually lead into solving the previous feature request as well (using the PK-FK concept where the bridge table is more or less assumed by one array field in one of the tables).

  9. sqletips says:

    Got it. This is just one of the several restrictions we have on cascading relationships.

    Regarding your comment about unique index allowing multiple NULLs, it is a valid request. ANSI SQL standard which doesn’t cover indexes but talks about UNIQUE constraints that can allow only one NULL or multiple NULLs. They leave it upto the implementation and in fact the one NULL implementation is considered entry level compliance for this feature.

    You can however use an indexed view in SQL Server 2000/2005 to workaround this limitation. This provides similar behavior in that the data in the table will follow the rules of the constraint. Here is a simple example:

    set nocount on;

    use tempdb;


    create table T (

    i int not null primary key

    , j int null /* need to make this unique only for non-null values. */



    create view T_j

    with schemabinding


    select j

    from dbo.T

    where j is not null;


    create unique clustered index UQ_T_j on T_j(j);


    insert into T values(1,1);

    insert into T values(2,NULL);

    insert into T values(3,NULL);

    insert into T values(4,1); — this will fail


    select * from T;


    drop view T_j;


    drop table T;



  10. sqletips says:

    >> You get this when trying to alter a table

    >> converting the data or when you are

    >> inserting from one table to another. The

    >> only clue that I have as to which column is

    >> causing the problem is that I know it is a

    >> varchar column going into an int.

    The reason why this error message is obscure is to prevent information disclosure. Considering the widespread use of databases and their exposure over the internet for example it is critical that we help mitigate the risks. Every database developer must be are aware of the risks posed by SQL injection and it cannot be overstated. The other day I got a letter from one of my VISA credit card companies stating that my card number was among a database list that was breached. The first question that came to my mind was if this was related to a SQL injection which could have been prevented by better coding practice for example. I will never know. But the point is that we had remove exposing information about schema/data in these type of runtime error messages that can result due to bad input and exposed directly to end user due to improper error handling by the client for example. It is a question of whether we can expect all users to write robust code or control the areas in the engine that can mitigate security risks due to insufficient error handling.

    Having said all this, it is still valid to be able to see better error messages for these type of conditions during say development or testing phase to improve productivity. We have had some discussions about providing a knob for DBAs to redirect maybe the complete error message with data into the ERRORLOG. Maybe the new exception handling mechanisms in SQL Server 2005 reduces this requirement since you could trap exceptions on the server and take appropriate action like logging some of the context information.

    In any case, feedback like this is much appreciated since it shows areas in which the developer productivity can be affected. Having developed OLTP/data warehouse solutions for the last 10 years before joining Microsoft, I can see where you are coming from.


  11. DOMAIN support.

    That’s all :)

    Thank you.

  12. Erik Ejlskov says:

    Real date and time types, for natural separation of the two.

  13. Mark Pokorny says:


    I would like to see more choices for saving results in Query Analyzer. For instance it would be nice to save the grid results directly to an .xls, .html file etc.

    Currently you have to save the results as a text file via menu Results in Text, or File which is limited import the results to Excel.

    So how about saving the results as insert statements also?


    Is there a way to reverse engineer a dbms schema in Enterprise Manager, or Query Analyzer? It would be nice to generate the ddl of a selected dbms.


    I have noticed that specific terms change in the dbms and in the books online (updated).

    In Enterprise Manager you see the term Role, but if you run:

    EXEC sp_helpuser you see group name listed. In the books online (updated) it lists:

    GroupName sysname Roles to which UserName belongs.

    Group_name sysname Name of the role in the current database.

    So why are the terms role and group used interchangeably? This is confusing.


    Mark P

  14. Row value constructors, which would in turn let me do: WHERE (a, b) IN (SELECT c, d FROM e)

  15. David Portas says:

    Row-value constructors

    ANSI-compliant UNIQUE indexes/constraints (allows multiple NULLs)

    Native DATE and TIME types

    Autonomous transactions – the ability to commit data in a nested transaction outside the scope of an outer transaction

    Deferrable constraints

  16. Hugo Kornelis says:

    Hi Umachandar,

    Thanks for asking! It’s great to experience, again and again, that the folks in MS’ SQL dev team are actually listening to their customers.

    My main wishes (some of them might already be in 2005, some of them are already mentioned by others, and I already requested most of them through, but I’ll repeat them anyway):

    1. Support for ANSI standard row constructor, so that we can use ANSI standard UPDATE without repeating same subquery over and over again and getting inefficient plan.


    UPDATE Table1

    SET (Col1, Col2) =

    (SELECT Col1, Col2

    FROM complicated subquery)

    WHERE ….

    2. NULLs should be disregarded in a UNIQUE index.

    3. Datatypes for DATE and TIME in addition to (not instead of!) the current DATETIME.

    4. Remove the cycles/multiple paths restriction on foreign key constraints.

    5a. Deferred constraint checking and deferred trigger execution.

    5b. Support for CREATE ASSERTION.

    That’s my (slightly crammed <g>) top 5. Just outside the top 5 falls:

    6. Add UPSERT / INDATE or MERGE command.

    Best, Hugo

  17. Erland Sommarskog says:

    1) DATE and TIME, compatible with existing DATETIME, not the mess that was in Beta 1.

    2) ALTER TABLE to insert a colunm at any place, or move a column.

    3) A function that returns the full call-stack, @@procid is not enough.

    4) SET STRICT ON, under which it’s an error to refer a table that does not exist, under which many implicit conversions are disallowed, calls to stored procedures are checked for matching parameters stc.

    5) Deferred constraints and triggers.

    6) UPSERT/MERGE command.

  18. Axel Dahmen says:

    I too have a couple of urgent wishes for a new SQL Server edition:

    1) allow to use expressions for Stored Procedure call parameters

    in MSSQL 2005, e.g.: EXEC myProc(@myVar + ‘ ‘ + @myVar2)

    I could have made use of this feature over and over if SQL Server would

    allow me to. (See for full discussion.)

    2) Allow foreign keys across databases (across servers). It’s really awkward (and inefficient) to have to program triggers to substitute FKs in a multi-database design. (See news://microsoft.private.sqlserver2005.relationalserver.tsql/ "Foreign Keys across databases" for full discussion.)

    moreover, also:

    3) NULLs should be disregarded in a UNIQUE index.

    4) Datatypes for DATE and TIME in addition to (not instead of!) the current DATETIME.

    5) Remove the cycles/multiple paths restriction on foreign key constraints.

    Umachandar, it’s great that you give us the opportunity to share our wishes with you.

  19. 1) Separation of DATE and TIME types and support for temporal features

    2) Regular Expressions

    3) Row-value constructors

    4) Full support of OVER clause for both ranking functions and aggregates:





  20. M A Srinivas says:

    I have not come across any database having this feature . This may be difficult to implement if not impossible

    Need to run through all SPs and provide table names and columns used in where, order by , group by for each SP. This is needed for impact analysis and performance tuning .

  21. I’ll have to add a +1 on the deferred constraints.

  22. Eric says:

    a) defered constraints

    b) row constructors

    c) real date & time datatypes

    d) much better support for distributed transactions (it is hell to support this)

    e) SSMS must completely support TFC for source control. Not a VSS subset of the features.

    f) intellisense (even freeware tools support it! And Oracle tools for Visual Studio! But not SSMS)

    CREATE ASSERTION and CREATE DOMAIN would be real nice;-)

  23. Eric says:

    Another one;)

    Expose Extended Properties through updateble system views instead of system functions.

    and another (which should be done anyway):

    The INFORMATION_SCHEMA views should be updated. SQL92 doesn’t exists anymore…its been replaced by SQL99. Those views should at least be SQL99 compliant. SQL2003 would be even better.

  24. My Fridays’ desired features are: 1) T-SQL arrays (I don´t want to implement them using SQLCLR code :-)) 2) Restore of tables by their names, sort of RESTORE TABLE FROM (I don’t want to organize my table on filegroups to implement it). 3) TempDb not a global resource, TempDb for each database if I need a db with intensive tempdb usage.

    Best regards!


  25. Linchi Shea says:

    1. I’d like to add a vote for Itzik’s native support for regular expressions in T-SQL, if that’s what he meant.


    3. Ability to freeze/bind plans

    4. Recycle bin by default, similar to Oracle’s Flashback technology

    5. Logical DB mirroring (right now DB mirroring is only physical, i.e. you can’t generate SQL statements from the log records and apply the SQL statements instead)


  26. The top 5 features:

    (A) Support intellisense in SSMS. Please!

    (B) A nice GUI for Service Broker Management from SSMS.

    (C) Separate DATE and TIME data types.

    (D) HTTP support in Service Broker messaging so that we do not have to play around with TCP.

    (E) Ability to read mails using the new DBMail feature.

  27. Wrda says:

    1) More analitical functions (like Oracle)

    2) Use of external certificates or smartcards with data encryprion

    3) Arrays

    4) Intellisense

    5) Ability that user can build his/her navigation tree with desired DB objects. I have problem with browsing stored procedures because I have more than 3K SPs in DB, and I want to work with only few SPs and tables.

  28. Merrill Aldrich says:

    1) Date and Time separate data types

    2) Duration data type for date / time arithmetic, e.g. "two days" not "September 18

    3) REGEX capability within T-SQL

    4) ANSI std row constructor

    5) For me the most exciting, but perhaps pie-in-the-sky, request: even more fully object/relational integration. CLR inside SQL Server is a wonderful start; it’d be really incredible to get to a full object-relational model in the future.

  29. I like a lot of what has been posted (row constructors, date and time datatypes, UPSERT, etc. Mine (many of which I entered into the Feedback Center:)

    1. Natural Join


    Natural Join TableB

    –(automatically joins on the keys)

    Outstanding for ad-hoc access

    2. Allowing dropping objects/columns/etc and deleting associated objects.

    For example, dropping a column should also have an option to drop CHECK and DEFAULT constraints without complaining. By default complaining is nice.

    3. Turn off Delayed name resolution by default. It is annoying that a procedure can be created with invalid table names being used. Just a SET command to allow this wouuld be nice:


    4. SCOPE_IDENTITY fixed to return the identity that was inserted in an instead of trigger. (or some form of INSTEAD_OF_TRIGGER_IDENTITY())

    5. Constraints that allow queries:



    value CHECK (EXISTS (select * from test2 where test2.value = test.value))


    5. Database Constraints ( to give support for real 1 to one relationships )

  30. Rob Volk says:

    I’ll echo some other’s requests:



    3. Queries in CHECK constraints

    4. Dynamic PIVOT

    5. Database-specific error messages

    And for a real pie-in-the-sky request, I’d love to be able to define custom errors for constraint violations:

    ALTER TABLE myTable ADD CONSTRAINT CHK_Amount CHECK(Amount BETWEEN 1 AND 10) RAISERROR(‘Amount must be between 1 and 10.’, 16, 1)

    Of course I could also ask for TRY…CATCH support on constraint violations, but that’s pushing it. :)


  31. Erland Sommarskog says:

    I already one added one too many, but I forgot a very important

    thing, which makes about #2 on my wishlist after date/time:

    Being able to pass table variables as parameters. Suggestively you can define a table type, and then you can declare tables and parameters of that type. This is an essential feature to make i tpossible to pass tabular data between stored procedure between stored procedures.

  32. Don Watters says:

    Automatic promotion of a secondary standby database to the primary database, and vice versa.

  33. Eric Hanson (Microsoft) says:

    Regarding Gustavo Larriera’s comment:

    You can freeze/bind plans for specific queries in SQL Server 2005 using plan guides (sp_create_plan_guide) and plan forcing (the USE PLAN <xml_plan> query hint).

  34. Tibor Karaszi says:

    Thanks for asking UC :-)

    Separate date and time datatypes. Same basic semantics and rules as current ones.

    Row value constructors

    Some stricter level for implicit datatype conversion. Say you define a parm as nvarchar and use parm in WHERE and col is varchar. No SARG. And this doesn’t only have perf implications. Often it is a mistake to use different datatypes, and I want to get help finding those mistakes.

    A function to return datatype from an expression. IsWhatDatatype(expression) can return ‘int’, ‘datetime’, etc. Expression can be constant, column name, variable, literal etc. Engine has rules for this, why not expose these rules? Do all reader here know what datatypes below are of?





    DOMAIN as per ANSI SQL. If not doable, I suggest SIMILAR predicate (reg exp).

  35. gp says:

    The way the table datatypes are implemented needs to be expanded so that it is possible to define the table datatype as a replica of an existing table. The sps become really clumsy with all the definitions.

    The second thing is respect to differed resolution. The thing differed resolution is not necessarily a good thing. Atleast there should be a way to enforce differed resolution by means of a switch. I know we do have sp_dbcompatibilitylevel. I am not sure if that is the right way to use. We have faced numerous problems in Production because of this.

  36. Ananth Shankar says:

    I think allowing to insert an identity column on the fly should be a good enhancement.Right now as an alter statement we cannot add identity , although it allows through Enterprise.

  37. Ernst Hoefer says:

    Hi Umachandar

    1. CREATE DOMAIN and ALTER DOMAIN (without losing existing data)

    2. delay enforcing of foreign key constraints until COMMIT (deffered constraints)

    3. Reporting Services: KeepTogether flag on subreports AND textboxes (so you can turn it off)

    4. Report Builder: allow other data source than SQL Server/Analysis Services (esp. own providers)

    5. Profiler: allow sorting by clicking on columns (like in VS and Outlook)

  38. w says:

    1. Deferred constraint checking, like in Oracle. These would include foreign key, unique key, and check constraints.

    2. Remove limitation on foreign keys with potential multiple cascade paths and cycles.

    3. Support DML event notifications in a high-performance way, so that database change tracking can be done without triggers. (This feature was present in early betas of SQL Server 2005 but was later dropped.)

    4. Full support for table-level and database-level declarative constraints. For instance, a CHECK constraint that can reference other rows within the same table, or even rows in other tables.

    5. Flashback Queries and Flashback Database, like in Oracle.

  39. Patrik says:

    1. Native date and time, to support milliseconds with 1ms as accuracy.

    2. Regular expressions.

    3. Intellisense.

    4. Array datatype.

    5. Easier ways to locate table scans.

  40. It would be interesting to see that results summary ordered by total score (either just ordered by count or ordered by sum(6 minus preference)).

    I know that this poll doesn’t actually mean anything as far as what will be implemented but it would be nice to be able get an idea of which features "won" 😉

  41. Joanna says:

    I’ll add another vote to deferrable constraints.

  42. sqletips says:


    Good idea! I sorted the features by count instead of category. I also moved the tools and other requests to a different table since that is not of interest to me (I have however forwarded some of the requests to people in the other teams).

    And don’t underestimate the importance of getting these results. Lot of people who are involved in the planning of features for next release of SQL Server are aware of the list. It will help in strengthening some of the existing requests and provide additional validation. So don’t be surprised when you see some items from here make it to a future version of SQL Server.


  43. Sharon F. Dooley says:

    Well, I am so busy trying to understand all the new features that are there that I’m not ready to say what I want next (except I want the migration tools to deal with logins from prior versions). But I’ll repeat what I said on the beta news group: what I want more than anything is to not have the next release be 5 years away. This release is huge — and I have been working with it since beta 1. I think in some ways it is worse than the 6.x – 7 changes in terms of the learning curve. Many of the changes are far more subtle, which makes them a) harder to discover and b) harder to understand


  44. Dave Wong says:

    I would like folders for DTS packages, i have too many of them and very hard to keep track of.

  45. sql says:

    Here’s four date/time-related ones, and a full-text one.

    1. Datetime data type with multi-timezone support, along with timezone conversion operators.

    2. Increase the resolution of the datetime data type. 3.33 ms is just unacceptable, as many Microsoft APIs have 0.1 ms (100 ns) resolution for their datetime data types (i.e NTFS and Exchange/MAPI), making storage of those values in SQL Server rather error-prone.

    3. An "interval" data type, for storing intervals in terms of minutes, days, or other time units.

    4. Date-only and time-only data types. The time-only data type should have at least 1 second resolution.

    5. Full-text indexing that has built-in support for a wide variety of file formats, not just Microsoft formats.

  46. Hal says:

    1. ability to chose temporal/lossless storage. (Create database mydb with storage=lossless)

    2. Unique Transaction stamps akin to timestamps but identical per transaction.

    3. query enhancements for lossless schemas whereby one might ask:

    select … from … join … join … where … and DataBasePointInTime = dateadd(Y,-1,getutcdate())

  47. smobrien says:

    1. Variable table and field names in select statements, etc.


    declare @theTable varchar(50) — (or some other data type like TableName)

    declare @aField varchar(50) — (or datatype FieldName)

    set @theTable = ‘Customers’

    set @aField = ‘LastName’

    select @aField from @theTable

    2. A way to return a range of rows in the middle of a query (like TOP 10, but the 6th group of 10)

    e.g. SELECT ROWS 51 TO 60 FROM Customers ORDER BY LastName.

  48. smobrien says:

    Ability to use IN with a variable.


    SET @Pets = ‘(”cat”, ”dog”, ”fish”)’

    SELECT * FROM animals WHERE species IN @Pets

    To give the ability to pass a list in to a stored procedure in a parameter.

  49. Marc Ziss says:

    1) The Ability to work with arrays in Stored Procs in T-SQL including being able to pass them in as params via ADO


    3) Real Temporal support

    4) Intellisense in tools

    5) A Whitehorse based IDE for DB modeling instead of Visio or any other stop gap diagraming solution

    ps great job on 2005!

  50. Andy Reilly says:

    1) Intellisense

    2) The ability to track what procedures are run – How often / Last run / etc – Pain in the nexk to find obsolete SPs

    3) UPSERT

    4) Pivot table with non specified values – ie from a select statement and not supplied using in and hard coding the values

    5) Natural Join

  51. Thanks for asking! I’m looking at your question from an ISV perspective (and for instance not from a DBA perspective).

    Here’s my list:

    1) Table names as variables in T-SQL stored procedures and functions, so that the table to work on can be passed as a parameter to the stored procedure/function, and the following can be written inside the procedure/function:

    SELECT * FROM @MyTable

    Today, I am writing a lot of stored procedures and functions that build the query in a string and then execute the string, which is both slow (interpreted) and requires ugly code.

    2) Member stored procedures/scalar functions,

    These can be called on a record. When the stored procedure/scalar function is executed, it has access to the record using something similar syntax to "Inserted" and "Deleted" in triggers (for instance “Self” or “This”).

    The advantage would be that the record can do operations without having to look up the record. Today, the only way to achieve a similar result is to pass the key of the record to the stored procedure/function, which looks up the record and starts from there.

    The idea of using member stored procedures/scalar functions is off course to get closer to our object-oriented designs, while still sticking with the relational model that underlies SQL Server.

    If member stored procedures/functions are there, then I guess it would also make sense to allow private fields. A private field is just an ordinary field in a table, except that it cannot be used by select/insert/update queries. The only way to access the field, is using a member stored procedure/function. Implemented this way, private fields would not be complex to implement from your teams point of view, but they would provide encapsulation (when used together with the stored procedures/scalar functions)

    3) Peer connections (also known as many-to-many relations)

    Today, one needs to create an auxiliary table with keys to both tables. I’m creating lots of such tables, and they are really just complicating otherwise clean designs. The concept of a peer connection is that the auxiliary table is created by the system, and the logic is hidden.

    I did not include the duration date type, the bigdatetime data type (high accuracy), the time date types, etceteras, in my list, because they are easy to create today using CLR UDT’s. So as an ISV, the new SQL Server 2005 features are sufficient for us.

    4) Folders for Tables

    In the tables of a database, there is a folder System Tables. It would be nice if there were also an “Application Tables” folder, where ISVs could put tables that are only to be changed under control of the application.

    The client/consultant using/configuring our product can access all the normal tables, but not the ones we put in the Application Tables table.

    While we’re at it, it might be nice to have a more general principle that allows to create folders, and to assign rights to accessing the folders (and any table that is in there).

    Non-T-SQL requests that are very high on our agenda:

    5) Server-side LINQ

    Today, when writing stored procedures using C#, we still have to use cumbersome ADO.NET methods. It would be a GREAT relief to have clean code by using LINQ inside the C# stored procedures. I’m hoping LINQ is part of C# 3.0, and that C# 3.0 or higher can be used inside SQL Server as soon as possible.

    6) A much improved and more intelligent SHFT-CRTL-M feature.

  52. Oh, and I would also like to vote on Intellisense, also inside the Management Studio. You can put that in position 4.


    1) Table names as variables in T-SQL queries

    2) Member functions

    3) Peer connections

    4) Intellisense

    5) Table Folders

    Non T-SQL:

    6) LINQ in C# stored procedures

    7) More functionality added to SHFT-CTRL-M

  53. Tim Toennies says:

    Using pooled Windows Authentication. Oracle does this with their Proxy Authentication feature.

  54. Dieter Van Hauwaert says:

    1) How about before and after triggers (both row and statement if possible), and allowing to change the inserted table in before triggers

    2) some equivalent of Oracle’s autonomous transactions

    3) MERGE statement

    4) automatic audit columns

    5) allowing query in check constraints

  55. sudheerpalyam says:

    I would like see "Intellisense" in my SQL Server Managment Studio, the same way i have in Visual Studio.

  56. Anders Borum says:

    I agree with the following:

    1) Table names as variables in T-SQL queries

    2) Member functions

    3) Peer connections

    4) Intellisense

    5) Table Folders

    With regards


  57. JohnC says:

    I would love to see cross database foreign keys. As larger clients start to use sql server it becomes more important to break the data down into manageable db sizes. I want to have an orders db and a customer db and not have to write triggers to make sure I can’t delete a customer that has orders in the orderdb.

  58. Marc Jacobs says:

    I would like to see an ANY aggregate function instead of having to use MIN/MAX in a GROUP BY query where multiple rows in the current grouping all have the same values. It provides better documentation of the intent of the query, and it may also allow for faster query execution since the optimizer may be able to eliminate a sort.

    Similarly, I’d like to see a PREDOMINANT aggregate function that returns the value that occurs most frequently in the current grouping. If there are several values who tie for PREDOMINANT, the query processor returns an ANY on the most PREDOMINANT values. There are occasions when a grouping will have a large number of one value and one or two outliers. MIN/MAX/ANY will return non-deterministic results. A PREDOMINANT aggregate function would return the value most likely intended. It is possible to write this type of query now, but it is quite verbose and not clear.

  59. Nele says:

    I have a top two :-) (although not t-sql related)

    * help ‘in’ the management studio

    * IntelliSense when writing queries

    Kind regards.

  60. Scott says:

    From a data warehousing perspective, the following additionals in the relational engine would provide great benefits.

    1) Bitmap indexes

    2) UPSERT / MERGE statement

  61. Fred Brouard says:

    Here is my request about a future release of SQL Server :

    1) date / time types

    DATE type

    TIME type

    INTERVAL type

    DATETIME that accuracy is 0.001 second and include TIMEZONE

    and all operators to do with (like ‘2005-01-01’ + 6 MONTH)

    2) UNIQUE ISO SQL constraint (accept multiple NULLs)


    4) Deffered constraints


    6) BEFORE Triggers

    7) SIMILAR predicate (SQL:1999 regular expression)


    9) MERGE statement (SQL:1999 UPDATE/INSERT)


    11) RETURN NULL ON NULL INPUT statement in stored procedure and UDF

    I read some demands wich I desagree completely :

    Dynamic Pivot :

    I am completly afraid to see such a non relational operator in a great RDBMS. Microsoft would be condamned for that… So Dynamic Pivot is a pure nightmare !

    Use DMX queries !

    Variable table and field names in select statements :

    This is also a pure nightmare. Object’s name cannot be a variable…

    ALTER TABLE to insert a colunm at any place, or move a column :

    This is an anti relationnal feature. There is absolutly no order at any place in a RDBMS. Only the order you set it. Remember that a table is a bag of marble. ISO SQL make ordinal position of column only to simplify INSERT statement and this is a mess ! Also a mess the ability to specify the ordinal position of the expression in the clause SELECT of the SELECT statement for the ORDER BY clause. Thoses two SQL ISO features are stupids.

    And some other that is not necessary :

    paging through data : easy in 2005 by the use of ROW_NUMBER()

    DOMAIN : can be done by sp_addtype, CREATE RULE, CREATE DEFAULT, sp_bindrule, sp_bindefault…

    wich I think is much more interresting in a matter of chaching rules

    A +

  62. furmangg says:

    stored procs and functions should accept table variable parameters… SQL needs this for Reporting Services reports to be able to use multivalued parameters with stored procs with out kludgy string split functions in the stored procs

  63. Ivan Arjentinski says:

    I copied this directly from a post of Hugo Cornelis, just to add +1 for these features:

    1. Support for ANSI standard row constructor, so that we can use ANSI standard UPDATE without repeating same subquery over and over again and getting inefficient plan.


    UPDATE Table1

    SET (Col1, Col2) =

    (SELECT Col1, Col2

    FROM complicated subquery)

    WHERE ….

    2. NULLs should be disregarded in a UNIQUE index.

    3. Datatypes for DATE and TIME in addition to (not instead of!) the current DATETIME.

    4. Remove the cycles/multiple paths restriction on foreign key constraints.

    5. Deferred constraint checking and deferred trigger execution.

    My top 5 is exactly the same :)

    Thanks for asking, Umachandar

  64. Tj says:

    There is only one I want to add that hasnt already been mentioned.

    The ability to get a native sql statement for a smdl query.

    The report builder available for 2005 is incredibly useful, but could be tremdously valuable for people wanting to integrate the resulting datasets with other applications (MS, .Net, ODBC, or otherwise).

    See this google posting…

  65. PH says:

    Better statistics, ability to create larger histograms to get a more accurate representation of the data in a table, applies to very large tables.

  66. Rajeev Kadam says:


    ROW_NUMBER is a hassle to code in web applications.  It’s that simple.

  67. Andy Ball says:

    Ability to easily measure Server usage @ DB Level from a performance, memory, utilisation point of view without having to use Profiler and Analyse. This is important in consolidated environments

  68. Ryan M. Hager says:

    1.  A way to find the list of all table/procedures names that a stored procedure is planning to use "deferred name resolution" on.  That way we can at lease process the list to see if all of them are ok, or there are ones we should look at.

    2.  Indexes on functions of columns.  That way, when you have a several million row table with an index varchar column and you are querring the table with a parameterized querry that is using a Nvarchar parameter, it will not do an index scan and conversion on all the rows for each querry.  (Third Party Apps.)

    Ryan M. Hager

    (r) hager (A)(T) hager1 (D) (O) (T) Com with no "(",")", or spaces and AT=@

  69. DW says:

    FK across DB’s

  70. steve says:

    – Identity values with caches, also supporting

     order (best if both cases are supported

    – Additional partitioning options (list based and

     hash parititiong)

    – Full cluster support (like RAC)

    – Capture workload profiles for performance

     tuning, including the capability to have

     automatic baselines

  71. PP says:

    I would suggest your team should look into discussion-goups for the most repeated questions (pattern study) and fix those ASAP.

    Examples in BOL are pathetic, those needs a real improvement.  If you guys DO NOT want to write any examples then you should tei-up with some partner who have good examples on their site :)

  72. AC van Bers says:

    Support cascading cyclic and multipath FK-references…

    please… 😛

  73. Edgardo says:

    I vote for the LIMIT keyword, it would make things much easier…

  74. daveg says:

    1) +1 for johnc’s post about GROUP BY allowing ANY or PREDOMINANT to choose a value – it happens when you have a true key with corresponding unqiue non-key values that must be emitted

    2) LIMIT

    3) ability to ignore NULLs in indexes

    4) value set comparisons (is this ANSI defined? Oracle supports it…)

      SELECT * FROM Foo

      WHERE (a, b) IN

      ((1,2), (3,4))


      SELECT * FROM Foo

      WHERE (a, b) IN

      (SELECT c, d FROM Bar)

  75. Chad Price says:

    Allow expression list as a valid expression.  This allows for non-scalar subquery comparisons

    … WHERE (a,b) IN (SELECT x,y)

    I gather this is in the official SQL standard (ISO/IEC 9075-2:2003 section 8.4)

    There really seems to be no workaround to not having this when as bind variables tuples are provided:


       WHERE (a,b) IN ((1,2), (1,3))

    Oracle has supported this atleast since version 7, so I expect most DBs already have this functionality.

  76. Greg says:

    Better date time formatting options. Rather than the numbers in the convert statement allow passing a mask to format date times. eg select convert(varchar,getdate(),"dd/m/yy hh:mm")

  77. why this stored procedure is not working plz tell me correct code—————————


    ————-run this code ——————–

    use northwind


    ———–table created———-

    create table companey


       compname varchar(20),

       price  integer



    ———–value inserted—————

    insert into companey values(‘sskEnterprises’, 200000);

    insert into companey values(‘sskGoods’, 100000);

    insert into companey values(‘sskBaveries’, 14700000);

    insert into companey values(‘sskFoods’, 4100000);

    insert into companey values(‘mhkEnterprises’, 74500000);

    insert into companey values(‘mhkfoods’, 145000);

    insert into companey values(‘mhkgoods’, 16000);

    insert into companey values(‘sskshipping’, 145800);


    ———————–check the value—-

    select * from companey where compname like ‘mhk%’;


    ——————–procedure created———–

    create procedure sp_like

    @cn varchar(20)


    select * from companey where companey.compname like @cn


    ————– procedure droped—————

    drop procedure sp_like


    —————–procedure executed———–

    exec sp_like mhk;

  78. Chris J says:

    A more intuituve way than CTE’s for hierarchial queries, something like Oracle’s “CONNECT BY”.

  79. Vincent Rainardi says:

    1. MPP architecture

    2. Populate tables with test data (words or numbers which mean nothing)

    3. Upsert command

    4. Enable SCD type 3 on SSIS’ SCD Wizard.

    5. Bitmap index

    6. Perl-like regex string manipulation

    7. Oracle-like to_date function

    8. Like bulk insert but to export data out (not OS command like bcp -out but a standard T-SQL command)

  80. Dating says:

    Here is an opportunity to discuss about the top 5 features that you would like to see in the next version of SQL Server. It would be nice if you can include a sentence for each feature explaining why it is required. Please use features of SQL Server 200

  81. Weddings says:

    Here is an opportunity to discuss about the top 5 features that you would like to see in the next version of SQL Server. It would be nice if you can include a sentence for each feature explaining why it is required. Please use features of SQL Server 200