A Fine Slice Of SQL Server
I see a lot of scripts on the internet that use dynamic SQL, but leave people wide open to SQL injection attacks.
In many cases they’re probably harmless, hitting DMVs, object names, etc. But they set a bad example. From there, people will adapt whatever dynamic SQL worked elsewhere to something they’re currently working on.
Click through for a demonstration of the problem.
Comments closedChad Callihan shows us how to update the dbatools Powershell module:
The first sentence on the dbatools download page references the belief in releasing early and releasing often. While SQL Server and SQL Server Management Studio may get a handful up dates every year, dbatools averages a few every month. Fortunately, staying up to date with dbatools is easily manageable as we’ll see below.
Read on to see how you can tell which version of the module you have and then how to update it.
Comments closedRobert Sheldon gives us a primer on hyperconverged infrastructure:
A growing number of organizations have deployed hyperconverged infrastructure (HCI) systems in an effort to simplify IT operations, better utilize resources, and lower costs. They might house the systems in their own data centers, colocation facilities, edge environments, or office closets. Regardless of the location, many of the organizations are running SQL Server on their HCI systems, often alongside other applications. Although it means deploying SQL Server to a virtualized environment, such a practice has become fairly common, especially with the advent of the cloud. This article covers hyperconvergence, another option for SQL Server.
Despite how common hyperconvergence has become, some IT teams might still not be familiar with HCI or are familiar with HCI but have not deployed SQL Server to an HCI platform. In either case, they might now be considering HCI for SQL Server and need to better understand what this looks like before deciding on new infrastructure. Although HCI can make it easier to provide a platform for SQL Server, decision-makers should know what they’re getting into before going down this route.
Click through to learn more about hyperconverged infrastructure and where it can help (or hurt).
Comments closedKiana Bergsma announces a new tool:
Save yourself Time and Money with Steve Stedman’s new database comparison tool. Following in its cousin’s (Database Health Monitor) footsteps, SchemaDrift is FREE! It is currently in Beta phase but it is free to download for personal or business use.
We only ask that you give us feedback. Let us know what you like and dislike. How can we make this product even better and we’ll send you emails on update releases. Comment down below or message us through our website.
Click through for a download link, as well as a FAQ in video form.
Comments closedAaron Bertrand continues a series on the utility of calendar tables:
In two previous articles (part 1 and part 2), I showed some practical ways to use a SQL Server calendar table to solve business day and date range problems. Next, I’ll demonstrate how you can use this table to solve scheduling problems, like environment-wide server patch management.
When you have a handful of servers, managing patching is straightforward. When you have hundreds, it gets more complicated to balance keeping everything in sync and not spending all of your time organizing and performing patching.
This is a concrete example of where calendar tables can make life a lot easier.
Comments closedErik Darling shows how we can use FOR XML PATH on data which includes reserved XML characters:
The purpose of these queries is to show you hot to remove XML elements, and handle XML control characters like &, <, >, etc. All of these results return a single row, just to keep the examples simple.
Read on to learn more. One thing I’ve done in the past, when I know that there are specific reserved characters in use, is to run REPLACE() over the resultant data, changing < to < and so forth. But Erik shows us how to do it the best way.
Eitan Blumin opens Pandora’s Box:
In one of my previous posts, Fun with DATETIME Arithmetics, I introduced a way to use “math” to manipulate
datetimevalues for effectively generating, calculating, and displaying intervals (i.e. difference between twodatetimevalues). These mostly work with the addition and subtraction operators (+, -).In one of the paragraphs, I mentioned multiplication and division, and posed the question about why anyone would ever need to do this.
Read on for one not-quite-ordinary reason why you might need this.
Comments closedRandolph West shows us how the DATETIMEOFFSET type is stored in SQL Server:
Cast your mind back to our discussion on
DATETIME2. As you know,DATETIME2is basically the same as squishingDATE(3 bytes) andTIME(between 3 and 5 bytes depending on the scale) into the same column. You end up with a persisted value that is between 6 and 8 bytes wide.
DATETIMEOFFSETis kinda sorta the same thing, but with more bytes on the end. If you take a look at the Microsoft Docs page, the similar idea of a varied column size is retained. For a scale of 0 fractions of a second you only need 8 bytes to store your value, while the default scale of 7 decimal places for storing seconds requires the full 10 bytes.
Click through to understand how the sordid details.
Comments closedQQ-plots in R, first need to understand the Q-Q plot. The Q-Q plot is a graphical tool to help us examine if a set of data plausibly came from some theoretical distribution such as a Normal or not.
Suppose, if we are executing a statistical analysis the test comes under parametric methods assumes variable is Normally distributed, we can make use of a Q-Q plot to check that assumption.
It’s just a visual verification, not full proof, so we can make use of some other statistical test also. But Q-Qplot allows us to see at-a-glance if our assumption is valid or not.
Click through to learn more. H/T R-bloggers.
Comments closedI have been working with a customer where I have got data in AAS and in PPU for the same dataset.
What I have found is that when the data is loading it is very similar in terms of how long the data takes to load.
With one of my customers as an example the data was being curated in Asia, whilst the business was running things from Australia. By hosting AAS/PPU where the data was curated meant that the data loading was significantly faster. Yes while the reports would have to access the data across the ocean, this only sends the results, so the performance of the reports was and is still blazingly fast!
Click through for the full story.
Comments closed