A Fine Slice Of SQL Server
Tom Collins sets up column-level encryption:
How do I set up the SQL Server Column Level Encryption ?
This is quite different from Always Encrypted—it’s the encryption process which we saw with SQL Server going back (at least) to 2000. It’s also (currently?) the only available way manually to encrypt columns in an Azure Synapse Analytics dedicated SQL pool. I’d say it’s not trivial to set up and use but neither is it a huge challenge.
Comments closedPaul White shows how not-simple simple parameterization really is:
The output of parsing is a logical representation of the statement called a parse tree. This tree does not contain SQL language elements. It’s an abstraction of the logical elements of the original query specification.
It’s frequently possible to write the same logical requirement in different ways. Using a SQL analogy,
x IN (4, 8)is logically the same as writingx = 4 OR x = 8. This flexibility can be useful when writing queries, but it makes implementing an optimizer more difficult.In general terms, normalization is an attempt to standardize. It recognises common variations expressing the same logic, and rewrites them in a standard way. For example, normalization is responsible for turning
x BETWEEN y AND zintox >= y AND x <= z. The normalized, or standardized, form chosen is one the query processor finds convenient to work with internally.
This has been a very interesting series and Paul does promise one more article.
Comments closedArun Sirpal explains the benefit behind scale sets in Azure:
I really like scale sets. It lets you create and manage up to 1000 load balanced VMs per availability zone using windows or Linux images. (We can have flexible or uniforms modes for orchestration which dictates if you go down the homogenous VM route or a mix, where a mix is the flexible option.
There are many other benefits too apart from scaling, such as built-in load balancing options, increased resiliency via 3 Availability Zones and from a cost perspective you can couple scale sets with Azure Hybrid benefit or even use reserved instances – cost is important in the cloud!
Read the whole thing.
Comments closedJim calculates running totals in R:
Cumulative Sum calculation in R, using the dplyr package in R, you can calculate the cumulative sum of a column using the following methods.
Click through for examples of ungrouped and grouped operations. H/R R-Bloggers
Comments closedArvind 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 closedAndy Leonard beats the brakes off of some stuff:
It may help to define reference first. An SSIS Catalog Reference is a relationship between an SSIS Project and an SSIS Catalog Environment in the SSIS Catalog. One or more references to environments may be configured for a project. At execution time one and only one reference may be selected – like Highlander, “There can be only one.”
A broken reference occurs when one configures a reference between a project and an environment, and then the environment is deleted.
Read on to see what this looks like in the SSIS Catalog Browser and SISS Catalog Compare tools, as well as how you can prevent accidentally creating broken references.
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 closedMarc Lelijveld walks us through an implementation of the Power BI composite model:
Have you been working with Composite Models in Power BI? Did you run into challenges while you did? Then this blog is for you! In this blog I will further elaborate on what the composite models (including DirectQuery for Power BI datasets) are and everything that comes into play when you start implementing them.
During this blog I will introduce you to topics like source groups, storage modes and relationship evaluation. All different topics that come into play when you start building composite models.
If composite models sound interesting, be sure to check this out.
Comments closedMarlon Ribunal gets us started with a Docker container:
Maybe you want to get your hands dirty with the bells and whistles of the latest iteration of SQL Server, but you don’t have an extra bare metal or Azure or GCP based VM. Well, you’re in luck because Microsoft just released container images for SQL Server 2022.
Here are few steps to get you started with SQL Server 2022:
At this point, it’s quite easy to give new versions of SQL Server a try, even when they’re in preview. That said, some of the features make it to containers later so you might want to spin up a virtual machine and install it if there’s something you can’t get right now in the container.
Comments closedLiliam Leme has a pair of queries for us:
Consider a scenario where you are trying to monitor the connections from other applications to serverless SQL. I hit this need while trying to understand how many connections opened I had coming from an application. Had I hit some kind of limitation on serverless SQL or not. Spoiler: There is no limit for connections on Synapse serverless SQL as you would find with a dedicated SQL pool (formerly SQL DW).
Alternatively, the limit to the number of serverless SQL pool connections is how much cash you have in your bank account…though given that it’s $5 per TB processed, if you’re writing good queries, that’s a lot of queries and connections.
Comments closed