September Question: What feature is missing from SQL Server that you would most like to see included in the next release?
Question of the Month | September 1, 2010 | 1:00 amPost your responses to the above SQL Aloha Question of the Month in the comments section below (at www.bradmcgehee.com if you are viewing this from a syndicated newsfeed). And don’t forget to enter your e-mail address when you post your response, so I can contact you if you win.
This month, because of the nature of the question, which has no right or wrong answer, the winner will be selected randomly from all the entries that are received. Please share with us what one (or more) features that you feel are missing from SQL Server and should be included in the next release. Think of this as your wishlist to the Microsoft SQL Server division.
For more information, click on the Question of the Month tab above.
This Month’s Prizes
|
The prizes this month are an Amazon.com voucher worth US$50.00 and a license for new SQL Storage Compress 5.0 with one year of support and upgrades worth US$1,994. Not familiar with SQL Storage Compress? It provides silent data compression, so you can reduce the storage footprint of live SQL Server databases by up to 90% to save space and help you cut down on hardware investments. To find out more, visit the Red Gate website. |
- January Question: As a DBA, how do you manage your disk space?
- January Question: What feature(s) of SQL Server would you like to learn more about?
- Winner of the July Question of the Month
- Winner of the June SQL Aloha Contest
- October Question: What one thing do you feel has the greatest impact on SQL Server’s performance?
RSS
Twitter
Linked In
RAC-like functionality, and further improvements on scaling-out federated databases.
This one is easy…. sub-partitions.
Partitioning is great…. but in a warehouse enviroment it is EASY to have partitions that are still simply too large. Sub-partitioning should allow for physical separation of the data, as well as parallelism and opitmization.
Server Level Extended Properties
Create Or Replace
CREATE DOMAIN support
‘Create or Replace ‘ – so many people will be grateful for this one
My request is something that virtually every client of SQL Server can benefit from: full support for Windowing Functions. This isn’t some high-end niche offering that 0.2% of the world can benefit from. Virtually every client I interact with as a consultant does processing that could be DRAMATICALLY improved by this single thing.
Regular Expressions!
LIKE is not nearly powerfull enough, we need something that draws on the Unix traditions to bring the full power of regular expressions to SQL Server.
Support for bidirectional ( OUTPUT ) table parameter in stored procedures.
Sorry, the DATABASEPROPERTYEX in previous comment was really stupid as required feature is server-related. Could you replace it with the following message:
“Built-in property or function for determining default database and transaction log path, e.g. sp_configure ‘DefaultDatabasePath’ (‘DefaultLogPath’)”?
The ability to have custom Server Roles or at least new roles beyond the 8 fixed servers that have been there since SQL Server 6.x
The ability to RIGHT click on the ‘Logins’ folder under ‘Security’ in Object Explorer and have a ‘Script Login(s) As’ option.
Michael, you can do this for individual Logins now. I assume you want ALL of them though, in which case just write a quickie SMO/Powershell script to do it for you. One 15-30 min effort (if that) and then you are done with it forever.
Appropriate way to shrink database or free the unused space without negative or minimal impact. Technically Shrinking database is bad, but user perspective, once you delete big chunk of data, a user wants the space back . I would like to see improved shrinking algorithm which releases certain % of space as long as there is no impact.
just i thought..
Thanks
Mush
I would like to see a permission that would Grant the execute right (and only that right) to every stored procedure and function.
“I would like to see a permission that would Grant the execute right (and only that right) to every stored procedure and function.”
that is very simple to do with a sql script against system tables. generate the grant statement string and execute it…
I would like to see the ability to grant some of the admin rights to users without making them a member of one of the admin roles. This would allow for someone to be able to do certain maintenance tasks without being able to do things that you didn’t want them to like creating logins or dropping databases.
Multipel log files per database since this gives the possibility to spread the load between backup and the writing/reading of the log files.
Mirroring of log files since mirroring in the SAN / controller dos not protect from DBA making mistakes and it
will make it possible speed up a recovery that is IO bound
Often we compare(at least I do) MSSQL with Oracle. Both have borrowed features from each other. Hence my wish list for MSSQL include:
A) An ASM(Oracle Automatic Storage Management feature) like filesystem which can take i/o tuning out of the DBA hands
B) Extend the Clustering features to resemble like Oracle RAC like features where shutdown of a cluster node is transparent to the end user
C) Feature resembling Oracle’s Flashback functionality.
Cheers.
Shankar
Insteading of depending 3rd party controls, i wish if SSMS had the feature of
1. Source Safe,
2. Intelligent Query editor (somewhat same like Visual Studio .net IDE which has auto code formatting,
comment option which will also display the comment when we use a table/function/procedure etc etc in
some other places like a tooltip also intellience box),
3. Option to create Folders to organize tables, queries (sp, functions etc etc)
Source safe integration
Auto Query formatting
Intellience box
Option to create folders to Organize database objects inside a database(mainly for categorizing
query/table/view/procedure/function)
Option to search objects in the Object explore in SSMS (as the ownername is in the front(dbo), its not always
possible to do a search by typing its name)
Debuggine support from client side also.
Thanks
The ability to have a statement or table option/hint that will allow that statement operation (ins, del, upd) not to be logged in the database transaction log. For example, useful for staging or temporary table stores where one is not cared if the data is not logged as it gets truncated/cleared with every run.
A way of copying production statistics to a non production environment so that query execution plans can be generated as per production data volumes.
Currently unless a full size database is used in a developmenet environment the query execution plan may not reflect what happens in production.
Support for the Scalar Expression Function – a T-SQL scalar function that was marked as an expression, and treated by the optimiser as such – thereby reducing the ‘scalar function pain’. Connect item is here: https://connect.microsoft.com/SQLServer/feedback/details/273443/the-scalar-expression-function-would-speed-performance-while-keeping-the-benefits-of-functions
I would like to see paging functionality such as in mysql or postgres
Select column from table name limit 50 skip 100
This would return 50 records from record 101 to 150
This would help all programmers
Thanks
How about a high-performance subsystem (and T-SQL extensions) for automatically providing temporal support to records in tables? Rolling my own audit and history system for each new database I develop is rather tedious, and the SQL for dealing with time span queries where multiple “history” records are involved quickly gets messy.
Ability to log the exact SQL statement with passed parameter values even if executing within a large stored procedure within a sproc that a timeout occured at so it is easier to help pinpoint what needs to be optimized.
Many large applications do not do the work of gnerating a traceback and even if they do it is only to the level of the sproc call not the individual SQL statement within the call that generated the timeout..
Better tracing for the full impact of a transaction, including what happens in the triggers.
I’d really like to see a full featured email formating, queuing tool similar to SQLAnswers Mail (www.sqlanswers.com). That developer has sadly stopped development, but the functionality was way beyond SQL native tools and yet compatible with xp_sendmail.
Recently, I came across references to the following Oracle ranking functions: First, Last, Lead, Lag. Once I learned what they actually did, for the first time in my career I felt actuall jealousy towards my Oracle counterparts.
Select only a specific statements from a collection of T-SQL statements to run in SSMS.
Simple example: In “MyT-SQL.sql” file I have saved 12 statements. I open the file in SSMS. I wish to highlight statement #3, [holding Ctrl], highlight statement #5, [holding Ctrl], highlight statement #9, > F5 (Execute!) to view the results, before running the last statement in the file to delete specific records in a table.
1. SELECT * FROM dbo.resource_checklist
2. SELETE FROM dbo.resource_checklist WHERE mine_code LIKE ‘%’
3. SELECT * FROM dbo.resource_category WHERE mine_code != ‘GKU’**
4. DELETE FROM dbo.resource_category WHERE mine_code != ‘GKU’
5. SELECT * FROM dbo.mine_resource WHERE mine_code != ‘SNL’
6. DELETE FROM dbo.mine_resource WHERE mine_code != ‘SNL’
7. SELECT * FROM dbo.mine_master WHERE mine_code != ‘SNL’
8. DELETE FROM dbo.mine_master WHERE mine_code != ‘SNL’
9. SELECT * FROM dbo.db_company WHERE comp_code != ‘DBCA’
10. DELETE FROM dbo.db_company WHERE comp_code != ‘DBCA’ SELECT * FROM dbo.classif_result WHERE res_code LIKE ‘k10′ AND classif_num NOT IN (6,7,9,11,12,13)
11. DELETE FROM dbo.classif_result WHERE res_code LIKE ‘k10′ AND classif_num NOT IN (6,7,9,11,12,13)
Microsoft Access has a very useful keyboard shortcut (SHIFT+F2) known as ‘Zoom Box’. In the ‘data sheet view’ of a table, clicking in a cell and pressing (SHIFT+F2) brings up a zoom box, making the content of any cell (especially long text/memo fields) easier to view and edit. There is not a similar keyboard shortcut when opening a table in SQL 2K5.
In SSMS’s “Tasks > Import Data” wizard function, when one goes through all the steps of selecting data and table source/destination, and successfully imports the data into SQL, you would get the famous “The execution was successful” message. Understanding that this is the end of the requested job in the SSMS, the “close” button would be a logical end for the process.
However, sometimes one has multiple data tables from a specific source to import to a specific destination and likes to import them one by one in a certain order to full fill the dependency requirements etc (i.e. FK data). Wouldn’t the implementation of a [Back] button be helpful in that step? Currently one must close the “successful execution” confirmation box and go back to start the process all over again for importing data table 2, 3, 4, etc. (and the “last selected options” in the wizard steps don’t get “auto displayed” to ease the recurring processes either).
This is how I usually do it.
/* Create a new role for executing stored
procedures */
CREATE ROLE db_executor
/* Grant stored procedure execute rights
to the role */
GRANT EXECUTE TO db_executor
Then you just add users to the role and away you go!
Parsing a string is not very easy in SQL. A function that would prevent having to find the best way to accomplish that would be helpful. The best solution I’ve seen (IMHO) to split the string into separate rows involves a tally table. (http://ask.sqlservercentral.com/questions/18759/find-substring-saved-between-2-characters/18804)
This is not a blatant attempt to curry favor. It’s just the truth.
I’d like to see Microsoft fold the functionality of Red-Gate SQL Backup into native SQL Server Maintenance plans. Being able to keep the last X backups, network copies of backups, auto-retry logic, emailing that actually works & doesn’t require enabling mail on the server, compression for all versions of SQL Server. etc.
That would make me happy.
Very annoying to have to dig through tons of stored procs or tables when you work on a specfic area of an application that uses only 3-4 specific stored procs, udfs or tables… I’d like to have a way to add these objects to my user preferences so I could quickly find these objects when I reopen Management Studio.
Allow ordering by @parameter list – Ex: Order by @ParamColumnList
Better string functions:
Trim() function instead doing ltrim(rtrim())
Native regular expressions instead of using CLR
Native transform/format function – Ex: transform(’6195555555′, ‘(999) 999-9999′)
It would be great if they make SQL aware for MountPoints, MountVolumes for xp_cmdshell functions, etc.
This will make other things easier like the @DataAndBackupOnSeparateLogicalVolumes option in Policy Based Management which cannot determine MountPoints or MountVolumes.
Could not agree more
“‘Create or Replace ‘ – so many people will be grateful for this one”
and my share
ranking function “RunningSUM”
In-line scalar functions
Dynamic column lists in PIVOT statements
I would like to Add following features.
1>. Ability to create Object Oriented data structures which can directly bind to a C#.net Class.
2>. Ability to DYNAMIC PIVOT and UNPIVOT
3>. Ability to Pass an @XML parameter between one SQL SERVER to Another.
4>. Ability to CREATE FILTERED TRIGGER (with WHERE Condition)
5>. Ability to Create Packages like in Oracle which could be distributed and loads all together in the memory.
And Adding OUTPUT Table variable to Stored procedure,and my-SQl like “LIMIT” are also essential.
And I would also like to modify (or say rectify ) following feature.
DBCC SHRINK –without increasing any kind table’s fragmentations.
For this we have to add a new physical level layer in SQL Engine.
Right now it is like following.
8kb Pages (Both Index page and Data Page) >> Extents
Now we should add a new layer called “tablespace”
8kb Pages (Both Index page and Data Page) >> Extents >> TableSpaces
And Dbcc SHRINK should move or rearrange these “TableSpace” instead of rearranging Pages.
So we could also be enable to ISSUE
DBCC SHRINK
in place of DBCC SHRINK
Thanks
Sanjay S.
I would like to see an built-in stored procedure to list the un-used stored procedures in a database.
I would love to see one minor improvement to SSMS;
That “Log file viewer” would remember my preferred window size for the “Selected row details”.
Well, that goes for all windows in SSMS; please make them remember my preferred window size.
It’s a really small thing and completely un-functionality related but I would love to see CTRL-B back to resize the results pane. If it is possible to “miss” a keyboard shortcut then I miss that one
Thanks,
James
Improved Resource Govenor:
How about binding specific CPU’s to a specific resource pool?..we can already manage memory per resource pool and CPU in a way ….but this will enable us the DBA dudes to manage CPU more accurately based on CPU quantity and % per resource pool.
Allow all escape sequences in sqlxml. Currently all characters below char(32) except tab, newline and linefeed are not allowed in xml, plus all codes above the defined unicode table. For these characters neither the characters themselves nor their escape sequences are allowed in the sqlxml parser. The xml definition published by the w3c can however easily be explained so that it is not allowed to put a char(2) in your xml text, but that it’s escape sequence
is allowed. The current implementation where also the escape sequences are not allowed is too restrictive.For example char(2) would be escaped as “”. So that:
declare @xml xml;
select @xml = N''
does not give error message “XML parsing: line 1, character 10, illegal xml character”
And of course, that:
select char(2) as [xml]
for xml path(''), type
results in this output:

And furthermore: can you please not give an error message if the xml text has an xml prolog claiming encoding=”UTF-8″ while it is stored in an nvarchar datatype? I know it is not correct to store UTF-8 encoded xml text in an nvarchar data type, but many times the column/variable has to be ready to accept any encoding (as we simply need to accept what was delivered to us). This limitation makes that I now have to first parse the xml text myself to strip off any prologs before I can go ahead and query the contents of the xml text through xquery.
Open Pivot command in TSQL to have an open column numbers as needed by the query data , not a predefined number of named columns like now , so it works as Excel pivot tables where you drag a field in the column area then data is displayed with that field horizontally as needed by that data itself , that would give huge benefit and instant reports , and if Excel engine can do it , SQL Server surely can !
1. Allow expressions for stored procedure and function arguments etc. It would make code much more elegant.
2. Apply Oracle type referencing to a variable. Let’s face it, every variable is somehow related to a field so every one could be defined in the form:
declare @ExampleVar SourceTable.FieldName%TYPE
Completely self documenting and dynamic. User defined types become redundant.
(Don’t get me started on the pain experienced when changing a udt’s definition…)
3. Let Convert() use the type definition in 2 or a udt, rather than only the native types.
4. Provide a mechanism to create missing logins on the current server from a restored database’s users. (I know there are ‘fixes’ and SPs to do this, but should I have to do this every time?)
Remove the ansi setting limitations on filtered indexes (ANSI_WARNINGS being the biggest headache).
Or an easy way to find the existing ansi settings (only some are easily queryable and only on certain objects) on all objects.
ANSI_NULLS
ANSI_PADDING
ANSI_WARNINGS
CONCAT_NULL_YIELDS_NULL
QUOTED_IDENTIFIER
NUMERIC_ROUNDABORT
Being able to use filtered indexes would be fanatastic, but on legacy systems with tons of code there are significant barriers to implementing them.
Add TRANSFORM/PIVOT in T-SQL as it works in MS Access (no need to specify columns).
Ability to easily defragment tables and shrink database to recover free space
A “CONCAT” string aggregate. I know there are several ways to do this now, but it would be nice for it to just be part of the language.
“SELECT triggers”, for auditing data reads, without having to use profiler.
Better DATE functionality to make it easier to determine if a date range contains, or is contained by other date ranges in the table, that way we could create a CHECK constraint that doesn’t just ensure that the start and end date combination is unique, but the actual data range is unique.
Ability to write actual queries inside of a CHECK constraint, instead of having to create a schema bound UDF.
Other than Matt Whitfield, did anybody else submit anything to Connect? Don’t see any other links…
Table backups
Pie-in-the-sky: multiple physical servers running a single instance (R2 has it for the DW folks, how about OLTP now?)
DR: easier (automated) way to mirror multiple databases from one server to another (closer to the logshipping wizard). Bonus points: synchronize logins / tasks / configuration / etc between a mirrored pair (assumes whole-server mirroring, which is all that we do here)
Tools: catch SSMS up to the engine. For example it’s still not possible to create a database snapshot via the GUI. Decouple the tools development cycle from the engine dev cycle so that incremental releases of the client tools can be pushed out on a different (faster) schedule than the major or SP releases of SQL Server itself.
Little gripe: add an option to the sp_cycle_errorlog command to write the normal log header into to every new log. We cycle logs every 24h and it becomes a pain to go back to the log written at service restart to get details recorded only in that particular log file (and often pain = impossible if the logs have rolled over and history lost)
We really need to see active/active clustering features. Active/Passive is useless, it means that I have to buy powerful hardware that never gets used (though I do get a break on the licensing).
More built-in audit function… who log, when, are long, frequency…
The ability to exclude certain objects from SQL Audit. For example, I have a number of users that execute certain objects often and I would rather exclude them from my audit logging. At the moment, and from what I understand, I have to either log all users or explicitly identify the ones I wish to log.
Expanded database mirroring to support multiple mirrors. Obviously only one principal/mirror pair could have HA via witness but the others could be updated more real-time than log shipping.
Allow:
Nested Insert / Exec commands for SPs Eg an SP ‘A’ can capture the output of another SP ‘B’ into a table, but you can’t capture the output of ‘A’ into another table.
Row size over 8192 bytes. I know you can use Text datatypes, and that the page size would need to be made larger than 8k. But this shouldn’t be a big deal in the days of 1TB disks.
Lots of great suggestions but I can’t believe no one has mentioned better date handling. SQL’s CONVERT function only gives us 21 predefined formats from which to choose. If you want to format a date into something like, ‘SEP-2010′, you have use a big nasty concatenated expression. Oracle’s TO_CHAR function makes this a no-brainer.
A native database structure compare tool, with generation of scripts to make the two compared databases identical.
A “Database Deployment Tool” to be executed in the server of my client, to make ALL the necessary installations to turn the database available. The tool must check all necessary installations in the host operating system, including the SQLServer itself, and then automatically install all, including my database structure and data, in a unassisted way.
Bring back DTS ! Much easier to use than SSIS.
In support of other comments -> ability to define our own system and database roles, specifically a role that has all DML access to all tables in its host database, plus execute access to all SPs in that database. Yes you can write a script to do it – boring, manual and error-prone.
ALL WINDOWS RESIZABLE – and remember what size they were. Some of these are insane!
In SSMS, table design, put more column properties in first pane – Identity and default at least ?!
SSMS also display the create date for database objects, include the LastUpdatedDate ! (this is for DDL changes not DML).
Fix the resizable windows thing first – thats the one thing I hate the most. Otherwise SQL Server is a brilliant product.
1. Proper dependency checking on SP’s, Triggers, UDF’s which is included in the compilation process.
2. A describe equivalent (like Oracle)
Tempdb per User DB
I would like to see the ability to have a TempDB, optionally, per user DB or give the User Database the option to use the Instance Level TempDB. This way you could allocate disk space, and have a dedicated TempDB source for Databases that are more TempDB heavy than others.
Stored Procedure GUI Sub Folders
It would be nice to be able to use Schema’s not just as logical containors but as physical one’s within the GUI to be able to orginize procedures at a folder level instead of all of them lumped together.
Combination of High Availability Technologies
Clustered Mirrors, Log Shipping from Mirrors, and I’m sure there are others that could be bundled together. There are white papers on the designs, why not make them and support them as HA combos.
Make the Default Recovery Model Simple
Tons has been blogged about this. It would be nice non-DBA’s stopped accidentally creating time bombs for the rest of us.
I would like to see Object Level restore from a backup file within SQL Server.
I sure wish people would stop asking Microsoft to add things into the SQL Server Sphere that ALREADY EXIST via third party apps, addins (often FREE) or using simple workarounds. Come on – there are a TON of things that are BROKEN, SUBOPTIMAL, COMPLETELY MISSING from all components of the SQL Server product collection. Lets focus on making Microsoft provide/fix THOSE THINGS FIRST!!
Ability for to use version control with SVN.
Or ability to read the log file will be good to have.
I would like to have Auto_Shrink Option, Removed From Sql Server:
Thanks to everyone who contributed to the September Question of the Month. The randomly selected winner is Jenny, who will receive a $50 Amazon gift certificate and a license for SQL Storage Compress 5.0 with one year of support and upgrades worth US$1,994. Be sure to check out the October Question of the Month at: http://www.bradmcgehee.com/2010/10/october-question-tell-us-about-your-favorite-experience-as-a-participant-in-the-sql-server-community/.