A Fine Slice Of SQL Server
Erik Darling shows off one way to search for strings in T-SQL:
First, I want to advise you to avoid doing things as much as possible like this in T-SQL. Use CLR, if you can. Or do it outside of your really expensive database server.
You know, rational choices.
YOU’RE NOT MY REAL DAD, ERIK!
Do read the post, especially because it’s not just slapping a LIKE '%x%' on there and calling it a day.
Tom Zika takes away the scalars:
I’m still surprised many people don’t realise how lousy Scalar functions are. So because it’s my current focus in work and this Stack Overflow question, I’ll be revisiting this topic.
The focus of part one is parallelism. Unfortunately, parallelism often gets a bad rep because of the prominent wait stats. Also, if there is a skew, it can run slow. But for the most part, it’s advantageous.
Whether or not you want parallelism should be an informed choice. But Scalar functions will enforce the query to run serially, even if you are unaware. That’s why I want to shine a light on this.
Read on for a demo of how even a no-op scalar function can affect query performance. Given the mess we normally see in scalar functions, it’s all downhill from there.
Leave a CommentChad Callihan protects the reputation of SELECT *:
There are plenty of scenarios where using SELECT * can be an issue. Using SELECT * with EXISTS isn’t one of them.
When using EXISTS and SELECT * together, SQL Server is smart enough to realize what you’re doing and knows you don’t care about what’s in the SELECT.
Read on for an example. I’ve trained myself (been trained?) still to use SELECT 1. The reason is, I know SELECT * works exactly the same way but the benefit of using SELECT 1 is that doing this consistently allows you to do a search for SELECT * in your code base to find actual perpetrators (people writing queries expecting to return the entire result set and which may be susceptible to performance problems or future maintainability problems). Using SELECT 1 in the EXISTS clause means you get fewer false positives in that search as a result.
That said, Joe Celko chimes in to provide some of the history behind SELECT * as the convention for references in the EXISTS clause.
Lee Markum has a few tips for you:
SQL Server Developers are under-rated.
That’s right! I’m a DBA and I said, “SQL Developers are under-rated.” Dedicated SQL Developers help I.T. teams by writing efficient code that gets just the data that is needed and in a way that leverages how the database engine works best. How do you ensure you’re doing great work for your company and building code that will stand the test of time?
I’m so glad you asked!
Click through for Lee’s advice. One big thing I’d add to Lee’s list is to understand the domain. Query writing skills are quite fungible across domains—moving from health care to auto parts sales, you don’t need to re-learn SQL—but understanding some of the arcana of the organization and its industry makes it a lot easier to know you’re writing good queries, getting valid data back, and not forgetting some important business rule.
Leave a CommentHasan Savran starts bucketing:
The Date_Bucket function is introduced in Azure SQL Edge which is mainly used by IoT devices. This useful function returns the date-time value corresponding to the start of each date-time bucket from the timestamp defined by the origin parameter, or the default origin value of 1900-01-01 00:00:00.000. In other words, it lets you arrange data into groups that represent fixed intervals of time. SQL Server 2022 includes this useful function in its database engine.
Results of Data_Bucket might be confusing, Let’s look at its syntax first.
Hasan is quite right here: the results of DATE_BUCKET() are not intuitive, though they do make some sense…eventually…
Tom Zika writes out a list of coding standards:
No magic constants
What is
4? Just set a variable (constant) from a lookup table. Or write a comment with an explanation.
It’s the least you can do.
Read on for a set of things I generally agree with. This one’s a little bit of a tough case, as in extremely high-performance systems where the cost of a lookup is just too expensive, I’d prefer to use keys rather than values and save the compute cost and possible worse query plan. That said, even in that case, yes, definitely write a comment explaining what 4 is. That said, had I picked any of the other dozen or so recommendations, I wouldn’t have had anything interesting to say other than “go read this.”
2 CommentsKevin Chant values consistency:
Over the years I have worked a lot with automating T-SQL updates across multiple databases. During that time, it has become very clear that both consistency and comments are essential to make the process smooth.
Naming conventions of objects created using T-SQL should be consistent. Along with comments in your code for colleagues or anybody else who picks up your code.
Especially if you are working in a team who want to work in a DevOps related way.
I value consistency a lot but I’ve found I’m too inconsistent to be consistent.
Leave a CommentArvind Shyamsundar keeps track of changes:
Till such time that we have a detailed, fully updated change log, this blog post is being written as an unofficial change log for ScriptDom at least. Hopefully it will help readers understand when certain T-SQL grammar was added to ScriptDom, etc. I hope to keep it updated as we have later releases of DacFx and thereby, ScriptDom. If you have questions / feedback for me, please do leave a comment in this blog post and I will try to address it in due course of time.
Note that if there are no functionality changes for a given DacFx release, it will not feature in this table.
And again, this is an unofficial change log, so it is provided as-is and should not be in any way construed as an official Microsoft statement.
Click through for this unofficial log. As you can see, ScriptDom is still under active development with SQL Server 2022.
Comments closedHasan Savran takes us through a rather useful pair of functions:
GREATEST and LEAST functions are available in Azure SQL and SQL Server 2022. GREATEST returns the maximum value from a list of expressions. LEAST returns the minimum value from a list of expressions.
The Highest number of expressions you can pass to these functions is 254. All expressions must be comparable for these functions to work. For example, SQL Server can automatically convert the following string to an integer and find the greatest number.
These kinds of row-wise comparisons cut out a lot of writing OR clauses. Though standard function rules apply: if you put these in the WHERE clause of queries against large tables and expect them to be your primary filters and you’ll probably be in for a nasty surprise.
Comments closedChad Baldwin checks out what’s new:
I’ve been exicted to play around with some of the new features and language enhancements that are available in SQL Server 2022 so I’ve been keeping an eye on the Microsoft Docker repository for a new 2022 image. Well, they finally added it to Docker Hub! I immediately pulled the image and started playing with it.
I want to focus on the language enhancements as those are the easiest to demonstrate, and I feel that’s what you’ll be able to take advantage of the quickest after upgrading.
Read on for a dozen or so language enhancements. This isn’t as big a change as what 2012 brought but there is a lot of useful stuff in here, as well as more that has been publicly announced like APPROX_PERCENTILE_CONT() (and _DISC(), yeah, but bah humbug).