Aaron Bertrand gives us the highlights:
Certificate Management in Config Manager View and validate all of your certificates from a single interface, and manage and deploy certificate changes across all of the replicas in an Availability Group or all of the nodes in a Failover Cluster Instance.
Built-in data classification A new
ADD SENSITIVITY CLASSIFICATIONstatement helps you identify and automatically audit sensitive data, a huge step up from the previous SSMS wizard (which just used extended properties).
Aaron also digs into the engine a bit:
APPROX_COUNT_DISTINCT
This new aggregate function is designed for data warehouse scenarios, and is an equivalent for
COUNT(DISTINCT()). Instead of performing expensive distinct sort operations to determine actual counts, it relies instead on statistics to get something relatively accurate. You should find that the margin of error is within 2% of the precise count, 97% of the time, which is usually fine for high-level analytics, values that populate a dashboard, or quick estimates.On my system I created a table with integer columns ranging from 100 to 1,000,000 unique values, and string columns ranging from 100 to 100,000 unique values. There were no indexes other than a clustered primary key on the leading integer column. Here are the results of
COUNT(DISTINCT())vs.APPROX_COUNT_DISTINCT()against those columns, so you can see where it is off by a bit (but always well within 2%):
By the way, APPROX_COUNT_DISTINCT() is a really good idea, and I’m glad it’s here.
Doug Lane walks us through various scenarios with updates including the same column multiple times:
An application developer came to me with this question recently: “Can I use the same column twice in a SQL UPDATE statement?”
Yes and no.
It depends on what you mean by “use”.
Read on to see what Doug means.
Randolph West looks at the AT TIME ZONE clause when working with a specific time zone in SQL Server:
The time zone name is taken from a list maintained in the following Windows registry hive:
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Windows NT\CurrentVersion\Time ZonesNote: For SQL Server on Linux (and Docker containers), a registry shim performs the same function as the Windows registry by intercepting the API calls and returning the expected value(s).
We can also use a Transact-SQL (T-SQL) query against the system view
sys.time_zone_info, which uses the information from the registry hive. This is the recommended method if you do not have access to the registry hive.
Click through for a couple of examples.
Bert Wagner shows off the FIRST_VALUE window function and walks us through a case it struggles with:
The SQL Server FIRST_VALUE function makes it easy to return the “first value in an ordered set of values.”
The problem is that if that first value happens to be a NULL, there is no easy, built-in way to skip it.
While a UserVoice item exists to add the ability to ignore nulls (go vote!), today, we’re going accomplish that end result with some alternative queries.
Click through for the demo, as well as a video version of the post.
Ryan Desmond demonstrates the purpose of GOTO in T-SQL:
So I was playing around at work today and decided for whatever reason to see how I could get the code I was writing to fire off only in certain situations.
If it’s Sunday maybe, or if this is in a particular environment, or if a record in an admin table was something specific. I’m not sure how I’ll use this but I stumbled on Labels and decided to play with them.
Ok, so how to get to know labels. Well, in order to get them to work sometimes I have to create labels that are based on some criteria.
I do try to avoid these as much as possible, but they are valid syntax and I’ve seen a couple of cases where it makes sense to use GOTO.
Steve Jones reminds us that when running a window function, there is a default window in place:
What I want to do is compare the passing yards each year with the most current value for that player, showing the plus or minus. This means that for Aaron Rodgers, who threw for 1675 yards in 2017, I’d want to show this for the first few years of his career:
This shows me an easy view of the years where he was better in his career than he is now. Last year was likely a down year because of injury, but we’ll see this year.
In any case, if I run this query using LAST_VALUE() for the final year of his career, I don’t get the right results.
It’s good to keep in mind the full syntax for a window function for just this reason.
Hugo Kornelis has a brain-teaser for us:
The query below can be executed in any version of the AdventureWorks sample database. Don’t bother understanding the logic, there is none. It is merely constructed to show how SQL Server handles what appears to be an impossible situation.
If you look at the descriptions of the various join operators in the Execution Plan Reference, you will see that this query poses the optimizer for what appears to be an insolvable problem: none of the join operators can be used for this query!
But it’s possible, and Hugo explains exactly what happens, as well as places where the optimizer could be better at solving the impossible (or at least marginally difficult).
In SQL Server, if you write this query:
SQL Server builds a query plan for the entire operation at once, and passes the WHERE clause filter into the CTE. The resulting query plan is efficient, doing just a single clustered index seek.
In Postgres, CTEs are processed separately first, and subsequent WHERE clauses aren’t applied until later. That means the above query works just fine – but performs horribly. You’ll get much better results if you include your filters inside each CTE, like this:
That’s less than ideal.
The comments are valuable here as well.
Kendra Little talks about one of my favorite T-SQL operators:
Here’s my top 3 favorite uses for CROSS APPLY and OUTER APPLY:
APPLY is fantastic for calling table valued functions. I didn’t include questions about those in the quiz, simply for the purposes of keeping the code simple, and because I wanted the quiz to be about thinking through how apply works — but it’s still the #1 use.
Another thing I might use it for is when a query needs a correlated subquery — somewhat like an inline function.
And I also like it for queries that have a calculation that needs to be done and which is referenced in multiple columns in the query, or perhaps also in a predicate and the select. You can perform the computation once in the apply and then reference it multiple times. That way if you have to change the formula later on, you only have to change it in once place, plus I find it’s easier to read in some cases.
Item number three is something I learned from an older blog post Kendra wrote and regularly use APPLY for this purpose.
Jeanne Combrinck looks at inline table-valued functions in SQL Server:
Lets start off with what is a table-valued function (TVF)? A TVF is a dynamic table produced at the time of execution, depending on parameters. Like a view, a TVF creates a result set only when it’s executed, but, unlike a view, it can be parameterized.
You get two types of TVFs, an Inline Table-Valued Function (ITVFs) and Multi-statement Table-Valued Function (MTVFs). I find them easy to remember, think of the “I” in ITVF as 1 (single statement) and the “M” in MTVF as “many” (multiple statements).
As you can imagine, a TVF produces a result set that can be used as a virtual table or view. Yes, you can actually select data from a TVF, or join it with some other tables, views, or even other TVFs. In this post I will go into more detail on ITVFs.
In my experience, the best part about using inline table-valued function is the 3-9x performance improvement you get when removing them and inlining their code. It’s a great idea but the performance cost is just too high for me.
Kevin Feasel
2018-09-25
Security, Syntax, T-SQL, Versions
No Comment