360 Questions
We’ve been asking you folks a lot about what you’d add to SQL Server, and we’ve gotten some great answers. There are even some that have been answered in the last couple versions of SQL Server.
For instance, 2016 brought us STRING_SPLIT!
SQL Server 2017 has a whole bunch of neat stuff:
And while I’m thrilled with the new functionality (haw haw haw), there’s still a lot of stuff other platforms can do that we need weird workarounds for. I started thinking about this while answering a question on Stack Exchange about how SQL Server users mimic the greatest and least functionality available in other products.
Generally
As SQL Server developers, what weird workarounds do you have to implement due to missing functionality?
Here’s the thing: I don’t want you to leave a bunch of gripes and code in the comments. Comments like that are hard to read and search and typically don’t help other people out.
Rather than do that, I want you to do something more beneficial for everyone (yourself included)
-
- Blog about your workaround
- Head over to dba.stackexchange.com and do a Self-Answered Q&A
- Head over to another forum like SQL Server Central and post your own Q&A
After you do that, head back here and post a link to wherever you chose to do your business. I’ll do a roundup of all the best ones and add them to the post.
Thanks for reading!
14 Comments. Leave new
I’ve got a post perfect for this: Forcing a view to return a static value as a nullable column.
http://lowlydba.com/faking-null-values/
Very cool! Thanks for sharing!
There’s still no great way to trigger Agent jobs to run when an Availability Group fails over. I managed to do it via our monitoring system, but it feels like it should be something that’s baked in considering things like CDC break until the capture job is triggered.
I didn’t read the prompt very well- I haven’t blogged about my solution, but I DID open a Connect issue to try and have the function implemented.
https://connect.microsoft.com/SQLServer/feedback/details/3137800/sql-server-agent-add-aoag-aware-schedule-types
God bless, it probably took you longer to file a Connect item than it would take to start a blog and write a post 😉
John,
We have set up a job on our SQL 2012 AG servers so that a job gets executed when the replica state changes. You can create a SQL Agent alert for error 19406 and set the alert response to execute a SQL Agent job. It works fine in our environment, and perhaps that’s what you meant by your “monitoring system”.
“wherever you chose to do your business”
Well according to Brent that’d be tempdb now, wouldn’t it?
Not sure if it’s missing functionality or “working as designed”, but often have to run crazy queries to link a GUID job name to an SSRS data-driven subscription to trigger it manually – usually after something has changed warranting a new report.
Also have to run some custom SQL to extract SSIS Environment settings so I can set them up on a new SSIS machine. This happens more often than I’d like, though mostly when moving from Test to Prod or some similar situation. It’s great being able to pick up all of the Environment setting names and most of their values to move them over. Still have to map them, but that’s somewhat minor. Got the script here: https://thefirstsql.com/2013/05/28/ssis-2012-easily-copy-environment-variables-to-new-servers-or-new-environments/
I’ve had to write many a work around for list_agg in sql server. This involves STUFF(FOR XML PATH (”)) wrapped in a replace for &. Excited to make changes to that code.
Funny coincidence, I was just trying to use STRING_AGG today SSMS 17 was suggesting it, even though I was on SQL 2016 still: ended up with the coalesce approach from here: https://stackoverflow.com/a/194887/383710 – It wasn’t for production use, so I was happy it worked 🙂
I wrote this script once to help find the row in a view that had a calculation error because I couldn’t seem to find it by trial and error:
http://www.trycatchfinally.net/2013/04/identifying-row-in-sql-view-or-table-with-a-calculation-error/
I’ve also used the same script to find out which town causing a “string or binary data would be truncated” error, my least favorite of all errors. The connect request to import it has been open more than 10 years, but yet it hasn’t changed…
In Brent’s post “What Would You Put in SQL Server 2018?”, someone mentioned Oracle’s NULLS FIRST/LAST.
So I thought it might be interesting to write a bit about ordering within an order in T-SQL.
https://andyhogg.wordpress.com/2017/10/22/youre-out-of-order-youre-out-of-order-the-whole-result-is-out-of-order/
Pretty cool! Thanks for sharing.
I would love to see built in regex capabilities for check constraints, LIKE, etc…. Sorry for comment only, don’t blog atm. 🙁