For this week’s What If series, we’re exploring what would happen if you had access to SQL Server’s source code – like if you got a job at Microsoft, signed a partner NDA, if the code leaked, or if it went open source.
Today’s question is, “What would you fix or change?”
Erik says: Dynamic SQL
I love dynamic SQL, I really do. But it’s a real pain in the butt sometimes!
- If you’re using NVARCHAR strings, you need to prefix all of your string concatenations with N, or you could end up silently truncating your final product.
- If you use CASE to branch concatenation paths, it can also silently truncate a string.
- Figuring out how many single quotes you need to properly quote things is awful, sometimes.
- PRINT is limited to 8000 characters, RAISERROR even fewer.
- If you’re using sp_executesql, it’s a real chore to get the variables as the query ran without additional logging.
- Concatenating non-string data requires CAST/CONVERT.
- Sometimes surprise NULL concatenations leave you with EMPTY strings.
There’s a lot that could be done to make dynamic SQL easier (and safer!) to use. This may not be terribly popular, but hey, if I take the popular stuff, you won’t have anything to comment on.
You’re welcome!
Brent says: I’d Fix the Database Tuning Advisor
And by “fix,” I mean castrate it to avoid any unfortunate offspring.
The DTA is one of those great ideas that only works if the developers are allowed to continue to put work into improving it. v1.0 simply doesn’t cut it for a product like this. While Microsoft has put more work in, it’s only been to suggest even more kinds of indexes, not make smarter index recommendations.
So it’s time to get out the knife and do the needful. Sorry, Clippy – your parents just never could afford to send you to college, and I can’t let your greasy hands on my database anymore.
Tara says: I’d get rid of two deprecated features
Microsoft deprecated Database Mirroring and SQL Server Profiler in SQL Server 2012. Both are still available in SQL Server 2017! That’s 4 major versions.
I’m still recommending Database Mirroring to some clients because Basic Availability Groups requires a Failover Cluster. I tell the clients that Database Mirroring is deprecated, is still fully supported and still available in SQL Server 2017, but that I’m recommending Database Mirroring because of its simplicity. If it meets their RPO/RTO goals, why should we complicate things?
Though I know we should be using Extended Events instead of Profiler, Microsoft is going to have to cut the cord. But before they do that, I hope they provide a simpler GUI for Extended Events. Keep the flexibility of the current GUI, but also provide a simple version of it that Accidental DBAs can run. Most companies don’t have the luxury of having a very experienced DBA.
What about you? Leave your answer in the comments.
36 Comments. Leave new
for sure the auto shrink option never a great idea;)
It’s good for Emergencies™ though! Perhaps there should be a required password to use it, that you only get once you successfully join the Database “Magic Circle” (or should it be “Venn Diagram”?)
I would:
* Start documenting the deeper internals of replication and availability groups. Most real technical documentation on AG internals comes from a tiny handful of blog posts pre-Denali and even then didn’t go into much detail about its interaction with lease timeouts (except to say they exist), replica timeouts, and so forth. I think a lot of us rely on the magic working but when something awful happens it’s hard to troubleshoot without calling Microsoft. Replication is even worse – with SO many flags that have very little explanation as to how they work or interact with each other when things go wrong (which they often do, at scale).
* Replace RMO (the replication equivalent of SMO) which was deprecated a while ago but has no replacement except “use T-SQL!” So much of SQL Server management at scale relies on SMO and RMO and it was shortsighted of Microsoft to tell everyone to roll their own SQL when we had something already that almost worked. This is almost begging for someone to write their own free software replacement but… huge time investment.
* I would go through SSMS and call out every part that hardcodes T-SQL and ask them why it isn’t in SMO and force them to move it over. SMO was meant to be all about “this is the same thing SSMS uses and so we expose all the functionality for your own tools!” Over time it seems it has been very difficult for their programmers to update SMO so they’ve forced more into SSMS again thus killing the ability for the rest of us to roll our own tools. One recent example from SirSQL was read-only routing group lists… SSMS has hardcoded functionality for it which isn’t included in SMO so you can’t manage them in your own code without rewriting a whole mini library in T-SQL.
* One of those things? The report that’s used to generate in-memory OLTP recommendations. It exists but there’s next to nothing on how it works, it’s hard to use, and so excruciatingly slow (if you’ve ever tried it, you may be waiting minutes between each click). It’d be nice if that information was exposed properly.
* Update all of the components SSRS, MDS and DQS to expose their configurations over WMI for inventory purposes. Okay currently you can work out half of SSRS configuration through WMI but for the rest (URLs and such) you need filesystem access to parse the XML. For MDS you need access to WinRM to query IIS along with making educated guesses based on registry keys and file paths to reach an XML file that matches URLs to databases. And for DQS (like you know running those command line -upgradedlls etc you’re meant to do after every patch), you’re SOL.
* Actually, we can kill a few birds with one stone, and you know how in the Setup program it has an inventory tool? Expose THAT data over WMI. That would be great!
* Speaking of DQS, I’d make it so you can run DQSInstaller.exe -upgradedlls on the command line without it prompting for keyboard input. Currently it prompts which causes problems with automation BECAUSE if you also redirect (DQSInstaller.exe -upgradedlls < always_answer_yes.txt" it will crash on exit… making it even more difficult to automate and work out if it was successful or not.
* I would apply patches all the way back to SQL 2012 to remove the requirement to drop SSISDB from an AG during patching because it has caused everyone SO MUCH NEEDLESS PAIN AND FRUSTRATION. Also it would upgrade MDS automatically instead of forcing you to do it in the GUI (also preventing automation of patching). Ditto for DQS. None of these should require the kind of mass manual intervention, astrology and fortune telling that they currently do.
* Make SSMS backwards compatible with every version of SSIS (at least back to 2012). Because why not. It doesn't even do much and it shouldn't be rocket science, and yet, the two are glued together version by version as if they were made of concrete. It's a little weird. I think .NET SSAS management libraries have the same issue?
I think that's about it. I know it's a bit long but… I think they need to be more open about internals and put more thought into automation and remote management through .NET. Unless .NET is dying on the vine or something.
I’m always afraid to comment on things like this, because I usually realize right after that there’s an easy answer and I’m an idiot 😉
That having been said, I create and execute a lot of migration scripts (moving one of our product databases from one release to another).
It’s REALLY annoying to me that it’s so difficult to just get output that sort of says here’s the statement you ran, and here’s the result of that statement. DB2 & Oracle just do this.
Example:
update table1 set value1 = ‘xyz’ where table1_PK = 37;
Msg: Foregn key violation. Table2 has dependency on table1. (paraphrased, obviously)
What tends to happen is these messages can (but don’t always) appear just about anywhere, even if you configure SSMS or sqlcmd to output the statement.
A recent example: I executed a very large script that disable and enabled some FK’s, recently. I got messages saying the FK couldn’t be enabled because the parent table was missing proper values, but even with -e option onf sqlcmd or configuring ssms to output query with results, I just had a huge pile of Msg statements at the end of my output. Not very useful.
Ugh…. Get rid of profiler? I still love it a lot of times for quick and dirty traces. Incoming Bud Light – Less Filling debate. 😛
Don’t hate me, but a few modest T-SQL features borrowed from Oracle would be awesome, for example:
* anchored types – I want to be able to declare a row type variable based on the current schema of a table, or based on the current datatype of a column in a table. That would make everybody’s code just a little less brittle when a table changes. How about declaring a row type variable based on a declared cursor? (Yeah, I use cursors when a gun is pointed at my head… or I have to do something egregious like call a stored procedure in a loop. Same diff.) A row type variable based on a declared cursor would release us from the tedium of declaring and fetching every stupid variable in a large result set.
* SQL packages – This is a crude but useful attempt at adding encapsulation, data hiding, data persistence, and even a little polymorphism to SQL. In a package, you can declare private variables and procedures that are not exposed outside of the package. You can logically combine functions and procedures that operate on the same type of data or are used by the same process. You can persist session-specific data. You can have two different packages with the same name but different interface specifications. Comes in handy.
* greatest and least functions – Yet another Oracle feature. I am really tired of writing stuff like CASE WHEN A > isNull(B,A-1) AND A > isNull(C,A-1) THEN A WHEN B > isNull(C,B-1) THEN B ELSE C END when I could write Greatest(A,B,C). Easier to read, too!
* multiple-column criteria – How about being able to write … WHERE (a,b) IN (SELECT c, d FROM mytable)?
I would like anchored types too.
greatest and least can be done with a cross apply to unpivot and min/max.
with data as (
select a = 1, b = 3, c = 2
)
select greatest
from data
cross apply (
select max(value)
from (values (a), (b), (c)) T1(value)
) T2(greatest)
multiple column criteria WHERE EXSITS (SELECT 1 FROM mytable WHERE c = a and d = b) or if they are nullable WHERE EXSITS (SELECT c, d FROM mytable INTERSECT SELECT a, b)
It’s not overly efficient though. Unpivoting the row and then re-aggregating them is more expensive than using a case expression, for example… but… the CASE expression becomes a little painful after 4 or 5 items in the comparisons. +1000 for GREATEST & LEAST functions.
Jason – sort of a coincidence you just replied to that. It recently came up as a question, and a blog post about work arounds in SQL Server is on the way.
Get T-SQL with steroids, like PL/SQL or pgPL/SQL languages.
when you work with PL Languages , you see a powerful language for SQL programming. That´s all.
>I love dynamic SQL..
With respect, and apologies for being contrary, but I hate dynamic SQL. It’s an egregiously awful kludge. I was browsing through Adam Machanic’s whoisactive recently. It’s completely demoralizing to see the extent to which his code relies on horrifically complex dynamic SQL.
I have only been working with SQL Server for a few years. From what I can tell, dynamic SQL exists in large part because T-SQL does not support basic indirection on key language primitives such as column and table names. It’s great that scripting variables (which facilitate column and table indirection) are supported in sqlcmd. But scripting variables are still obviously not first-class elements of the T-SQL language, or else we would see dramatically more usage examples. Please correct me if I’m wrong on any of the above.
If I had access to the code base, or even a chance to talk to one of the technical leads, I would like to understand in better detail : if/why the T-SQL language can’t be developed further, to support more relatively basic use cases that currently seem to require dynamic SQL.
Msg 8152, Level 16, State 4
String or binary data would be truncated.
SQL Management Studio
Options –> Query Results –> Results To Grid –> Retain CR/LF copy save
This option saved me actually today 8/17 because I was trying to debug an issue where some records are being exluded.
But when I initially ran the print @sql statement it was truncated because toooooo long. I tried to use SELECT @sql.
This also resulted in weird output.. I could not format. But the savior.
SQL Management Studio
Options –> Query Results –> Results To Grid –> Retain CR/LF copy save
I am in Erik’s camp, dynamic sql can be good. Most of us SQL devs use dynamic sql so we do not have to duplicate code and have multiple places to change.
But debugging does take some time and understanding.
Some more content: The WHERE clause was being excluded because the dynamic sql output was too long on the PRINT. This was the most important part.
Small improvement on the availability group.
I whold add the ability for each agent job ,to choose if to run on slaves/master only, both .. like with backups .
SQL Reporting Services Subscription – Job Agent Job Identifier
I would like to see a way to identify jobs other than a GUID in the job agent. Currently I have to add this to the description every time I create a subscription to make it palatable to find the related job AND even then I use a script because I don’t feel like opening each job to find the right one.
@Erik: you can use the CONCAT() function to address your last 2 bullets (Concatenating non-string data requires CAST/CONVERT; Sometimes surprise NULL concatenations leave you with EMPTY strings)
I’d change the product to include real database sharding, proper active-active clusters, ORACLE RAC style thing.
Similar to HP Vertica with their projections/super-projections and k-safety.
You try explaining to an Oracle DBA a SQL Server active-active cluster, they just frown “tut” and walk away.
Hope it’s not to late to comment. I would add Object Level Restore similar to Litespeed’s OLR.
Ooh that’s a good one! Surprised it took this long to come up.
To be able to do dml operation without logging.
For example: deleting 100 million rows from a table and without using the transaction log .
Maybe by trace flag or something ?
A specific parameter on TRANSACTION calling, to set the transaction to only log the T-SQL in the transaction itself and not all the underlying deletes or updates that it is comprised of?
Surely the need to know, in advance, the column names when pivoting? If you don’t, you have to resort to DSQL. (Or am I missing something? Again.)
@Erik: “Concatenating non-string data requires CAST/CONVERT” , ever tried CONCAT() ?
Of course, but it’s 2012+, and our procs have to support 2008/R2.
Concat is also sort of clunky for stuff like SELECT CONCAT(‘Really long string’, ‘Thing I want to concat’, ‘Rest of really long string)
Overall, I’m not really blown away by it.
Visual this, that and the other is a pain in the butt. Diagrams of arrows linking various boxes is useful for teaching programming but not useful for professional work. I want a textual programming approach, just like PL/SQL in Oracle and PL/pgSQL in Postgres, then I can at least search the source code with a text editor.
Oops… I just noticed Ricardo Munoz’s comments above. I think I just repeated what he said. Lo siento Ricardo!
The first thing that comes to my mind: I would fix the JSON support, having a JSON datatype, which would allow real indexing support.
That would be closer to a re-design than a fix but I think it deserves to be mentioned here.
I want COMMENT ON rather than sp_addextendedproperty/sp_updateextendedproperty.
I want an explicit security role that allows the reading of metadata but not the reading of data
CREATE IF NOT EXISTS on all objects requiring a CREATE.
I’d like to be able to group by and order by column aliases. It would be nice to be able to use variables for column aliases as well.
Good news!
SELECT COUNT(*) AS [Records]FROM (SELECT 1 UNION ALL SELECT 2) AS x (whatever)
ORDER BY [Records];
Data Collections should improved – this has the potential to help SME customers a lot, and save on expensive licensing for third-party performance monitoring software.
I may be behind the times now with all the recent releases over the last decade, but I would love to have a simple and consistent way to script out any database ‘object’ even if it’s not an object.
I know we have object_definition – but for some reason that doesn’t cover tables and indexes and gok what else.
Having a flag for the command to ‘create’, ‘drop and create’, ‘alter’, ‘create or alter’ would be nice too.
Yes, visual studio allows you to generate a script, but that’s not entirely handy all the time…
Personally, I think it will be a sorry day when they get rid of SQL Server Profiler.
Put Databases in folders in SSMS?
I know its fun to have everything just there, but with the DWH databases from Polybase, MDM, DQS, SSISDB, ReportServer, ReportServerTemp, I already have way to many I want to hide for daily operations.
And then grouping them by functional domain/application group would be awesome.
I dare not even hope to be able to set Owner privilege on a group to have it apply to all DBs in the group. You know, for easier management.
Freakin’ AutoShrink BEGONE! 😀