Is It Time To Stop Using IsNumeric()?

The old system function IsNumeric() often causes exasperation to a developer who is unfamiliar with the quirks of Transact SQL. It seems to think a comma or a number with a 'D' in the midde of it is a number. Phil Factor explains that though IsNumeric has its bugs, it real vice is that it doesn't tell you which of the numeric datatypes the string parameter can be coerced into, and because it doesn't check for overflow. Phil comes to the rescue with a couple of useful alternatives, one of which works whatever version of SQL Server you have, and which tell you what datatype the string can be converted to.… Read more

Database Code Analysis

Database code analysis will reduce the number of 'code smells' that creep into your database builds. It will alert the team to mistakes or omissions, such as missing indexes, that are likely to cause performance problems in production. It will allow the Governance and Operations team visibility into production readiness of the code, warning them of security loopholes and vulnerabilities. William Brewer describes the two technical approaches to database code analysis, static and dynamic, and suggests some tools that can help you get started.… Read more

SQL Server R Services: The Basics

It is possible to do a great deal with R within SQL Server, but it is best to start by doing analysis in R on numeric data from SQL Server and returning the results to SQL Server. There is great value to be gained even with this basic foundation. Robert Sheldon is on hand to give you a kick start with the first in his series on beginning with R in SQL Server.… Read more

Filtering Errors in PowerBI

I just faced this problem when trying to analyze some information from google analytics in PowerBI. In my specific situation, I was trying to extract a number from some URL querystrings and filter the rows without the number. When I set the data type of the column to decimal, the rows without a number receive … Read more

How to Automatically Create and Refresh Development and Test Databases using SQL Clone and SQL Toolbelt

In order to be able to deliver database changes more quickly, there are several tasks that must be automated. It can be a daunting job to ensure that the whole team has the latest database build when there is a proliferation of copies, and the database is big. Phil illustrates a solution by taking a set of Redgate tools to show how they can be used together, via PowerShell, to build a database from object-level source, stock it with data, document it, and then provision any number of test and development servers with the database build, taking care to save any DDL changes to the existing copies of the database.… Read more

Developing Metadata Design Patterns in BIML

You may have already experienced ETL Hell, where you have a large number of similar of SSIS tasks, and a small change, such as an alteration to the network topology, means that they all need to be altered with the correct connection details. Perhaps you should consider creating design patterns for all the standard components of integration tasks in BIML, and generating the SSIS packages from these? Amarendra walks you through the process.… Read more

Query Store and Parameterization Problems

The query store gives us a novel way of identifying those queries that are causing performance problems when they are parameterized by SQL Server for reuse. Although it is relatively simple to ensure that certain troublesome queries avoid the problem, it is laborious to identify these queries. Additionally, Query Store gives us the means to fix the problem for groups of queries by means of plan guides without changing the DDL at all. Dennes Torres explains the details… Read more

The Basics of Good T-SQL Coding Style – Part 2: Defining Database Objects

Technical debt is a real problem in database development, where corners have been cut in the rush to keep to dates. The result may work but the problems are in the details: such things as inconsistent naming of objects, or of defining columns; sloppy use of data types, archaic syntax or obsolete system functions. With databases, technical debt is even harder to pay back. Robert Sheldon explains how and why you can get it right first time instead.… Read more

News for Differential Backup

SQL Server 2017 brings several improvements to us. Sometimes a simple new field in a DMV can turn possible interesting new functionalities. That’s what happens with differential backup in SQL Server 2017. The DMV sys.dm_db_file_space_usage has a new field: modified_extent_page_count . This new field tell us how many pages were changed since the last full backup. … Read more

Statistics in SQL: The Kruskal–Wallis Test

Before you report your conclusions about your data, have you checked whether your 'actionable' figures occurred by chance? The Kruskal-Wallis test is a safe way of determining whether samples come from the same population, because it is simple and doesn't rely on a normal distribution in the population. This allows you a measure of confidence that your results are 'significant'. Phil Factor explains how to do it.… Read more

SQL Server User-Defined Functions

User-Defined Functions (UDFs) are an essential part of the database developers' armoury. They are extraordinarily versatile, but just because you can even use scalar UDFs in WHERE clauses, computed columns and check constraints doesn't mean that you should. Multi-statement UDFs come at a cost and it is good to understand all the restrictions and potential drawbacks. Phil Factor gives an overview of User-defined functions: their virtues, vices and their syntax.… Read more

Simple SQL: Attribute Splitting

If the design of a relational database is wrong, no amount of clever DML SQL will make it work well. Dr. Codd’s Information Principle is that you have, inside the entity tables, the columns that model the attributes of that entity. The columns contain scalar values. Tables that model relationships can have attributes, but they must have references to entities in the schema. You split those attributes at your peril. Joe Celko explains the basics.… 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

Visual Checks on How Data is Distributed in SQL Server

There are many reasons for wanting to know how data is distributed. Sometimes you just want a rough idea of the way that data is distributed in a column. You may think, wouldn't it be nice to have a SQL function that just showed you roughly what the distribution was, graphically, in the results pane. Phil Factor thought that was well and turned the vague wish into reality.… 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

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