10 SQL Server Feature Requests I’ve Upvoted Recently
In no particular order:
- Database Level Option for Storing Wait Stats – just like we can store query plan history in Query Store, it’d be awesome to get performance history over time.
- Give more info on the “String or Binary Data Would be Truncated” error – tell us which column, and what row tried to violate it.
- Sync instance-level objects in an AG – logins, linked servers, Agent jobs & schedules, encryption keys, certs, etc. Doesn’t have to be all objects – could be just objects created with a FOR REPLICATION option kinda like how replication does it, but I understand that it’d be a pretty big change to pull that off. Make no little plans.
- SSMS keyboard shortcut to execute just one statement – execute the line where my cursor is on. Man, I adore this feature in the PostgreSQL client that I use, and every time I come back to SSMS, I cringe that I don’t have it.
- Query stats DMV doesn’t include batch mode spills – columnstore is still a bit of a niche product, but as batch mode processing inevitably comes for rowstore tables, this bug is going to become a much bigger deal.
- Add a built-in numbers table – these things are fantastically useful, and having a built-in standard one would make code portability better.
- Unlogged tables – there are some tables you just don’t need to cleanly recover (like staging tables in a data warehouse) and you’d be willing to sacrifice recovery for faster loading. No, non-durable tables aren’t the answer because those are in-memory only.
- Minimal logging problems in 2017 – minimal logging has never been easy to achieve, but now even when you play by the rules, it’s getting harder.
- SSMS edit-query-text doesn’t show you the real query text – this bug bugs the hell out of me.
- SSMS add a search box to Object Explorer – on databases with hundreds of objects, this would make life way easier.
Honorable mention:
- Automatic updating of SSMS – most new apps have a check-for-update link built in these days, but I understand why Microsoft chose not to do this. Updating SSMS isn’t that hard.
- Batch mode support for rowstore queries – I’m guessing this only has 3 votes because people know it’s already coming, maybe?
- Build a distributed version – how hard can it be? (giggles)
- HTTP Request Function – wants the ability to query web services to get data. I can’t imagine how that could possibly turn out badly.
Free, 3× a week
Get my new posts by email
Three posts a week, plus a Monday roundup of the best database news from around the web.
Doesn’t shift F5 execute the command where your cursor is? Or is that an SSMS Boost feature?
Ah, Redgate SQL Prompt
I don’t have SSMS Boost and I tried Shift F5, it didn’t work for me.
Well Ctrl + E executes current selection.
Yep, but you have to manually select what you want to run first.
Tools -> Check for Updates
Redgate fixes a lot of the SSMS request. I actually do hope that SSMS or at least the SQL Operations Studio will be a Store App, so it will be updated automatically.
SSMS 16 and 17 both check for updates automatically…I don’t recall having to turn that on. Either way, I’m split on if having to manually download the update is good or bad…my work laptop is locked down, so if SSMS auto-downloads the update and tries to apply it, it won’t work, unless I opened SSMS in elevated rights mode (which I don’t normally do)…so being able to manually go get the update and manually apply it is nice. I’d be open to SSMS telling me a new update is available and asking if I wanted to launch the website, or auto download and install or skip.
SSMS, Object Explorer, have a mechanism to allow multiple object Filters.
I tried using those filters once. Once!
I use AutoHotKey to create a macro {Shift-F5} that does “execute the cursor line” (by the sequence {Home},{Shift-End},{F5}). There are LOTS of things you can do with it (I also use it to create short aliases for server names). https://autohotkey.com/
Your queries are one line long, eh?
Cherish the job you have. Cherish it.
I have plenty of one-line SQL statements. ¯\_(?)_/¯
For statements with more than one line, I surround them in {} and use a macro assigned to {Alt-F5} to auto-select the block of text and execute it. That macro uses the EXCELLENT ViEmu plug-in to provide Vim emulation. The macro code is {ESC}vi{{}{F5}, which means:
1. {ESC} = Make sure you’re in command mode, not insert.
2. ‘v’ = Visually select…
3. ‘i’ = inside…
4. {{} = braces
5. {F5} = execute the selected text
A neat trick:
/* { Update F
SET Bar=’Baz!’
–*/ SELECT *
FROM dbo.Foo F
WHERE F.Futz = 12345
–}
Run that with nothing selected and you see the record(s) you will be changing. Put the cursor inside the {} and hit {Alt-F5}, and the target gets updated.
I also have a “gaming mouse”, with {Shift-F5} assigned to button G15 and {Alt-F5} assigned to G16.
Wow, I love the gaming mouse idea!
2) Yes, I’ve spent a while tracking these down…
6) Yes!
4) Could be dangerous in the ‘wrong’ hands 🙂
Can I vote for these?
1) SQL formatting tool
2) SQL summarise script
Good list.
After the SSMS 17.8 release problems, I’m kind of glad that SSMS doesn’t automatically update.
How about the ability to quickly switch the current file’s db connection in SSMS 17.x by using Registered Servers or Object Explorer? E.g. https://visualstudio.uservoice.com/forums/121579/suggestions/34479154 It’s such low hanging fruit; they could bang this out in under 2 hours.
You know, every time anyone has ever told me something can be done that quick I’ve looked under the hood and it’s been a nightmare that would take me days to decipher.
For instance, in this case, those particular UI parts could be completely decoupled from the form viewer. It’s fine if you’re creating a new query – it will obey what’s on the UI. We know switching data sources runs a “USE [db]” statement that works with the query. But, the actual connection may not be linked in, and because it’s in Registered Servers, and Object Explorer, and possibly other parts of the UI – to do it completely they need to change a dozen workflows, each that could have large repercussions – because it’s literally how you connect to your databases.
Or, I could be totally wrong and they could bang it out in under 2 hours 🙂
Just saying, it’s really easy to underestimate the horrors other programmers have left behind for you, you’ve left for yourself, or you’re going to leave for others 😉
Did I mention I use AutoHotKey? 🙂
I have “hotstrings” defined for our servers, e.g., “/dbd/” auto-expands to “DBDEV_INTRANET.___.com” for the development environment database server and “/dut/” becomes “DBTST_SQLUtility.___.com” for the test environment utility server. All our systems authenticate against AD, so a simple {Alt-C} and then /dXY/{CR} gets me on the server I want.
I think Active-Active Read/Write nodes is more important for SQL Server
https://feedback.azure.com/forums/908035-sql-server/suggestions/34774930-multi-read-write-nodes-in-always-on-read-write-l
Can you describe the workload you have where one SQL Server can’t keep up with the writes?
Yeah, and new SSMS should be based on VS 2017, so that only one VS is installed on a machine… Or better yet, when I already have VS 2017, only required SSMS parts should be copied in by the SSMS installer.
a PRODUCT aggregate function gets my vote.
SQL Server to supports SUM aggregates but it would be really handy to have a native PRODUCT aggregate function that multiplies all values. This would save resorting to logarithmic maths.
Create “between” index for optimize queries like this: select x, y, z from mytable where getdate() between col_from_date and col_to_date
I wish for varying fill factors for partitioned tables. That way the older partitions fill factors could be set to 100% and the newer, more active partitions set to a smaller value.
How about the below new features
1) Packages like in Oracle.
2) Constant, variable to be as the same type as defined earlier or tagged to a database column.
Both the above are oracle and this can be available in SQL Server.
Thanks & Regards,
Srinivas M
Hey Brent, I know this is an old post, but there is an intrinsic problem with the way MSSQL (and lots of other databases) implement NULL values. If you create a procedure that can update every column in table t1, and you use it to update column c1 such that you run
exec dbo.my_proc @c1=NULL, there is an ambiguity here. Is that NULL a “missing value?” If it is, then if the table already has a value present, it’s possible that the “correct” action is for the procedure should leave it there, because the (non-missing) actual value is known. On the other hand, if that NULL is to be taken more literally, such that the procedure should “null out” the column t1.c1, how can we tell that unambiguously?I actually filed a feature request (https://feedback.azure.com/forums/908035-sql-server/suggestions/43030731-there-is-an-intrinsic-ambiguity-about-null-values) but this is a subtle issue that doesn’t jump out at people like “MAKE QUERIES FASTER!!!” does. Can you either explain to me why this isn’t a problem? Or, if it is a problem, can you help promote a feature change?
Btw, I know that one “safe” way to do this is to include a flag for each column, such that you might call
exec dbo.my_proc @c1=NULL, @c1_type='NULL'orexec dbo.my_proc @c1=NULL, @c1_type='value'. But that’s a fairly painful solution, both for the person writing the procedure and for the people calling it.For general Q&A, head to a Q&A site like https://dba.stackexchange.com or https://sqlservercentral.com. For my personal help, click Consulting at the top of the site, or attend a related training class. Thanks!