A Fine Slice Of SQL Server
Kevin Wilkie compares a couple of functions:
There is another function within SQL Server that many people think does the same thing, but with a slight nuance.
Sometimes, you just need to change out one character with another. For example, you need to make a “(” into a “[” to make everything consistent.
I’ve probably used REPLACE() two (or three?) orders of magnitude more often than TRANSLATE() but that’s mostly because I keep forgetting what the latter does.
Eitan Blumin looks at filtered indexes:
In fact, absolutely no functions of any kind can be used within the WHERE clause of a filtered index. Not even schema-bound user-defined scalar functions.
Unfortunately, as stated in the Microsoft Docs page about Filtered Indexes, the WHERE clause of a filtered index can only support simple comparison operators.
Well, it’s not entirely true, as you CAN actually use some functions, but on two conditions:
Read the whole thing. Eitan lays out one limitation of filtered indexes and provides a couple of potential workarounds.
Comments closedKenneth Fisher can’t go in dynamic SQL and neither can you:
This is one of those things that when I look back on it seems really obvious. Note: If at the end of this it isn’t overly obvious to you that’s ok too. I do a lot of dynamic SQL and GO is one of my favorite commands.
Read on to understand why. I was going to “One minor clarification…” Kenneth about it being an SSMS command (implying that it’s not available elsewhere) but he successfully parried the attack en passant.
1 CommentGauri Mahajan tries out different types of views in Azure Database for PostgreSQL:
A view can be considered as a dataset that has a pre-determined schema, the data is derived based on a specific criterion and is the source from one or more underlying tables. When a view is queried for data, it, in turn, queries the underlying tables and presents the intended dataset. At times, when the volume of data is very large, a view may start impacting query performance. In those cases, data from the underlying tables with the intended logic that makes up the view is physically stored in another table. This specific construct is called a materialized view. As the data from the source table gets updated, this materialized view needs to be refreshed from time to time to keep the data update in the materialized view, unlike a regular view. Azure’s offering of PostgreSQL database is Azure Database for PostgreSQL and it offers the same features as well.
Click through for more information on creating and working with views, materialized or not. Note that SQL Server’s indexed views are not the same as materialized views here.
Comments closedMarco Russo and Alberto Ferrari are linguists:
When calculation groups were introduced in 2019, we did not have a way to describe them in a textual form. A calculation group was represented as a table with one visible column and one or more rows, one for each calculated item. Each calculation item could have one or two DAX expressions associated with it – one for the calculation item itself and an optional one for the format string. Describing a calculation group in an article often required the writer to include screenshots of the Tabular Editor user interface, plus comments in the sample code to explain where each DAX expression should be placed in the user interface.
From the start we proposed a syntax to describe an entire calculation group in a textual form. However, there was no tool able to convert that syntax into the actual object in the Tabular model. For this reason, in the initial version of the articles about calculation groups we used a “pseudo-syntax” and we included comments that made the code more verbose and not necessarily easier to read. However, Tabular Editor 3 introduced the full DAX script syntax for calculation groups that we had hoped would be available in 2019. We decided to adopt that syntax in our content. We use this article as a guide to introduce and explain the DAX Script syntax for calculation groups.
Go check it out.
Comments closedMala Mahadevan continues a series on ScriptDOM:
In the last post I wrote about what ScriptDOM is and why it is useful. From this post, I will explain how it can be put to use. What it does when you pass a script to it is to parse it, check if it is free of syntax errors, and build what is called an ‘Abstract Syntax Tree’, which is a programmatic representation of the script, with nodes and branches for each code element. The rest of the usage/functionality is built around the Abstract Syntax Tree. So in this post let us look into how this is accomplished.
Read on to see what you need to do.
Comments closedMala Mahadevan gives us an idea of what ScriptDOM is:
I’ve been meaning to get a series of blog posts started on this topic. A twitter conversation from yesterday finally pushed me to it. Last year, I was tasked with finding a linting tool for the large t-sql code base we have at work. I looked into several tools – tsqllint, Sonarqube and several others. We ran into similar issues on all of them. Someone else defining rules for us didn’t work.
One tool called it wrong to use more than 3 tables in a query – we had several with 6-7, maybe even more. Another started to point out lack of indexes on temp tables as a problem (the rule was designed for table scripts but worked the same on code). Getting new rules that we wanted – such as not having unnaming primary keys on temp tables (Querystore doesn’t like them) or having our naming standards enforced meant extra work on someone else’s code. Our rules were custom to our environment. There were generic best practices for sure, such as finding the SELECT * or NOLOCK hints, but those were a small subset of what we needed. I then started looking for a tool with which I could make a custom linter. That’s when I discovered ScriptDOM, which has been around for a really long time with few people knowing or using it. It took me some time to understand how to put this to use. But after I figured it out it was really easy. Now I have a fairly robust, custom linter in place written in PowerShell and integrated well into our Azure DevOps Build process. It is easy to use and it is owned by us.
Read on to see what ScriptDOM can do and stay tuned to learn more.
Comments closedHadi Fadlallah looks at two methods of string distance:
Soundex is a phonetic algorithm developed by Robert C. Russell and Margaret King Odell in the early 1900s. This algorithm is used to index names as they are pronounced in English. The main goal of such an algorithm is to encode homophones to the same representation to be matched even if there are some slight spelling differences. As an example, consider the names “Smith” and “Smyth”, or “Mohamad” and “Mouhammad”. Soundex mainly encodes consonants and only encodes a vowel if it is the first letter of the name.
Being one of the most popular phonetic algorithms, Soundex was implemented in multiple database engines such as Oracle, SQL Server, MySQL, SQLite, and PostgreSQL.
These two methods are not perfect and they do really limit you to one word (or small word grouping), but they are useful.
Comments closedLukas Eder looks at a rarely-implemented keyword in SQL:
I recently stumbled upon a standard SQL feature that was implemented, to my surprise, in HSQLDB. The keyword is
CORRESPONDING, and it can be used with all set operations, includingUNION,INTERSECT, andEXCEPT.
Click through to see what it does. Be sure to check out the comments, where Joe Celko pops in to provide some additional historical context to explain why you won’t find this keyword in many implementations of the standard..
Comments closedJoe Obbish shows the difference between two functions:
There’s a lot of guidance out there that states that TRY_CAST is a faster, more modern version of TRY_PARSE and that TRY_PARSE should only be used if you need to set the optional culture parameter. However, the two functions can return different results in some cases, even without the culture parameter.
That guidance is blatantly wrong. TRY_CAST() and TRY_PARSE() both came out in SQL Server 2012. TRY_PARSE() uses .NET to perform parsing, which is going to have some edge case differences, especially around cultures and localization. TRY_CAST() is CAST() in an error-safe wrapper. If anything, TRY_CAST() is the “old” version and TRY_PARSE() the “new” version, with scare quotes in place because they both came out at the same time.
Both of them are useful, though I do agree with Joe’s advice of avoiding TRY_PARSE(), at least for larger datasets. If you’re parsing a single date or a small table of dates, TRY_PARSE() does an excellent job because TRY_PARSE('13/01/2019' AS DATE USING 'fr-fr') is not something you can easily do with TRY_CAST() in a US locale.