High Performance PowerShell with LINQ

PowerShell is a scripting language, and like all scripting languages it struggles to perform well with rapid iterative processes such as aggregation. It isn't well-known that PowerShell can use LINQ for many of those tasks which would otherwise use iteration, though somewhat awkwardly. However, some of the speed improvements you can get are startling. To get you well started, Michael explains every LINQ function , gives you example code in C#, The PowerShell way of getting the result, and finally Powershell's use of LINQ. This article could change the way you use PowerShell.… Read more

Deploying, Securing, and Updating Power BI Reports

Before Excel and other data-linked spreadsheets, business reports needed to be hand-crafted by IT experts, and changes were arbitrated slowly via change control. Power BI was designed to take the liberation that Excel pioneered to the max, by allowing 'power' users to not only to create Power BI desktop reports, graphics and visualisations in Power BI App, but then link and publish these to the Power BI app. These in turn can be given row level security and have their underlying data kept up-to-date so that users can log in and see the relevant and appropriate data in a browser. Saurabh Desai shows how.… Read more

Continuous Database Deployment with Confidence

To achieve gains from Database Lifecycle Management in general, and in Continuous Deployment in particular, it pays to aim for lightweight but consistent techniques and processes, refining them iteratively in the light of experience. Edward Elliott describes a Test-driven development process for developing SQL Server databases that worked for CD, based on a series of steps. Though the tools for CD may be necessary, they are not sufficient, without an effective structured process.… Read more

Personal Data, Privacy, and the GDPR

Now that there have been well-publicised examples of the awful consequences of data breaches and data misuse, there is increasing public pressure for legislation on privacy and personal data that has enough clout to prosecute serious offenders. In the vanguard has been the EU data protection regulation, soon to be succeeded by the GDPR. It defines IT practices for data that are likely to extend worldwide. William Brewer gives a rundown of what he sees as the implication for IT practice.… Read more

The Need for Database DevTest

The first and overriding thought of the experienced developer given any new development task is, or should be, ‘How the heck am I going to test this?’. I wonder, though, how often developers neglect to ask this question. Nowadays, with Visual Studio, the debugging facilities are so good you can muddle along happily for a … Read more

The Basics of Good T-SQL Coding Style

TSQL Code must work properly and efficiently. That's not enough though. Unless you are working alone, have perfect memory and plan to never change job, then you need to comment and document your code, it must be inherently readable, well laid out, use informative and obvious names, and it must be robust and resilient; written defensively. It must not rely on deprecated features of SQL Server, or assume particular database settings. Robert Sheldon starts a series of articles that explains the basics.… Read more

Python in SQL Server

Anyone using R in SQL Server employs the procedure sp_execute_external_script, the first parameter of this being the language to use. The documentation rather obliquely says that “the script must be written in a supported and registered language“. Until recently, the only language was R, but now a second supported language, Python, has appeared. Yes, at … Read more

A Visual Lexicon of LINQ

LINQ is best learned from examples, but few LINQ resources supply, along with the code, pictures that illustrate what each associated LINQ operator in the code is doing. This article is a visual index of all LINQ operators, that explain clearly with code and illustrations what even the most arcane LINQ operators actually do. To increase your enlightenment, it is accompanied with a reference chart to provide even more detail. Michael Sorens is, with these two articles, determined to persuade you of the power of LINQ.… Read more

A Visual Lexicon of LINQ: The Wallchart

Here is a handy wallchart that condenses the information contained in our article 'The Visual LINQ Lexicon' even further, but which adds some more technical specs that allow you, for example, to see at a glance which operators use deferred execution and which use immediate execution. You can use it to see what operators use lambda syntax and which ones are available in query syntax. You can see all the other key properties of all the LINQ operators, even including even how much of a sequence a given operator actually consumes. Click here to download the PDF version of the reference chart: … Read more

Azure Load Balancers and SQL Server

Load balancing in Azure has more importance for the DBA, because it is essential for Windows Server Failover Clustering in Azure, whether it is for AlwaysOn Availaiblity Groups, Failover Clustered Instances, or any other highly-available solution. Azure load balancing works out the location of the availability group, and routes traffic there. The load balancer detects a failure, and routes traffic to the new primary replica. Joshua Feierman gives an overview of what is required.… Read more

Improvements to Model Binding in ASP.NET Core

Model Binding has been a popular feature of ASP.NET MVC, but has, before now, had some minor restrictions. ASP.NET Core now has enhanced model binding: If a value passed via routes, posted data, or query strings couldn't be bound appropriately, it forced a global exception. Now, there are a number of ways of providing a more graceful reaction to incorrect data. You can now force the binding to a given source or create new sources for binding to. Dino Esposito explains.… Read more

How to Secure Your Azure Storage Infrastructure

Azure storage is an essential foundation for the more sophisticated services that Microsoft Azure provides. It is therefore important to understand how to make access to your data in Azure storage secure, to control access appropriately, to log activity and to get metrics on usage. Security in Azure can be easily managed and controlled via policies. There are a variety of ways to achieve the types of control over access that your applications need, as Christos Matskas explains.… Read more

Generating HTML from SQL Server Queries

You can produce HTML from SQL because SQL Server has built-in support for outputting XML, and HTML is best understood as a slightly odd dialect of XML that imparts meaning to predefined tags. There are plenty of edge cases where an HTML structure is the most obvious way of communicating tables, lists and directories. Where data is hierarchical, it can make even more sense. William Brewer gives a simple introduction to a few HTML-output techniques.… Read more

A DLM Approach to Database Testing

Database Lifecycle Management aims to make the development and modification of databases more predictable. Bugs are the source of more unpredictability than anything else, purely because it is so difficult to guess how long it will take to fix them. Good testing at all stages may take some time and effort, but it greatly reduces likelihood of the wildcard factor of the bug that is first detected during the deployment process; or worse, that gets into the production release.… Read more

Statistics in SQL: Kendall’s Tau rank correlation

Statistical calculations in SQL are often perfectly easy to do. SQL was designed to be a natural fit for calculating correlation, regression and variance on large quantities of data. It just isn't always immediately obvious how. In the second of a series of articles, Phil factor shows how calculating a non-parametric correlation via Kendall's Tau or Spearman's Rho can be stress-free.… Read more