The Complete Weekly Roundup of SQL Server News

In this issue:

Tech News : The Lighter Side

Product Reviews

Blogs : Administration

Blogs : Analysis Services / BI

Blogs : Columnstore

Blogs : Computing in the Cloud

Blogs : Data Access / ORMs

Blogs : Data Mining/Data Analysis

Blogs : DMO/SMO/Powershell

Blogs : Events

Blogs : Hardware

Blogs : High Availability/Disaster Recovery

Blogs : Performance and Tuning

Blogs : R Language

Blogs : Security and Auditing

Blogs : Software Development

Blogs : SQL

Blogs : SQL Server 2016

Blogs : T-SQL

Blogs : XML, XPATH and XQUERY

Articles

Database Weekly - www.databaseweekly.com

The Complete Weekly Roundup of SQL Server News

Hand-picked content to sharpen your professional edge

SQL Server News for 2016-01-18

DLM Webinars Free Webinar: State vs. Migrations for Database Deployment
When it comes to designing and applying database changes, it’s not always clear whether a state-based or a migrations approach is best. The simplicity and declarative nature of a state-based approach, or the steady evolution of migrations? Join Grant Fritchey, Steve Jones, and Alex Yates to work out what's the best way to manage database changes. Register today.

SQL Toolbelt 14 essential SQL Server tools
In one installer, the award-winning SQL Toolbelt contains everything you need to work with SQL Server. "The SQL Toolbelt provides tools that database developers as well as DBAs should not live without." William Van Orden. Download a free trial.
DLM Whitepaper Want to include your database in your ALM processes?
With Database Lifecycle Management, you can add your database to your source control, continuous integration, and release management processes, to reduce risk and improve efficiency. See how with this free whitepaper.
Editorial - Muting the Immutable

The idea of 'static data', 'enumerations', reference data', 'seed data' or 'domain data' is rather alien to relational databases because it is unnecessary to invent such special categories. However, such data is easily accommodated, as are the many other types that the average application programmer will never come across. RDBMSs are naturally good at coping with data that is perceived as having special requirements, importance or significance. However, it is often raised as a problem when building and deploying databases.

A lot of database developers, for example, hit the problem of having data that is, or should be, immutable. You wouldn't, for example, want to change ISO values for countries, but you've got to have them for the database to work. It is basically part of the version of the database. Normally, we just use a post-build script that inserts the correct data into a table, and then, for 'reference' data, give all database roles read-only access to the table. By doing this we ensure that the version of the data is tied to the version of the database and can't be changed. 'Seed' data is treated much the same way but with different user-access.

However, this doesn't suit some build systems, such as SSDT. It makes the mistake of believing that a build should contain only DDL. It restricts the users to writing only DDL build scripts and spits out any DML it finds. It is an awkward restriction. We can insert the necessary static data as a separate post-deployment DML script, added to the DACPAC, but there is no User Interface to make it easy. We are at the level of .NET and DACfx, papering over the cracks.

There are, of course, ways round this problem. For example, it isn't hard to create a view that, in its DDL script, contains data

CREATE VIEW TheDaysOfWeek
AS
    SELECT  DayNumber ,
            TheDayName ,
            'Cornish' AS TheLanguage
    FROM    ( VALUES ( 1, 'dydd Sul'), ( 2, 'dydd Llun'), ( 3, 'dydd Mawrth'),
            ( 4, 'dydd Mercher'), ( 5, 'dydd Iau'), ( 6, 'dydd Gwener'),
            ( 7, 'dydd Sadwrn') ) f ( DayNumber, TheDayName );
GO

You won't be able to change the data without re-creating the view. This, of course, can get unwieldy and 1000 rows is a practical limit. However it is surprisingly efficient where the number of rows is reasonable. The point is that the data is in the source DDL of the object and so, again, the version of the data is automatically tied to the version of the database. However, though this helps 'enumerations' or 'reference data', this doesn't solve the problems of 'seed' data in SSDT. 

There are plenty of ways of circumventing local restrictions of what you can, or can't, do in the database build process, but it would be much better if these arbitrary restrictions weren't there in the first place. The build script must provide whatever is necessary for the data. If we understand the data, its restrictions, ownership, security, constraints, mutability, and so on in all its richness and variety, and deal with this in the build script, then so many such problems just seem to evaporate.

Phil Factor.

» Join the debate, and respond to today's editorial on the forums


The Weekly News

All the headlines and interesting SQL Server information that we've collected over the past week, and sometimes even a few repeats if we think they fit. These headlines are gathered throughout the week and are posted in real time at the website. Check there for information throughout the week or enjoy this weekly summary of the SQL Server world.

Tech News : The Lighter Side

On Wikipedia’s 15th birthday, Ars shares the entries that most fascinate us - Ars Technica staff on their favorite Wikipedia rabbit holes, from supranational European bodies to Waluigi....(more)

Product Reviews

Pro SQL Server Administration - This big book aims to explain SQL Server 2014’s administration capabilities. How does it fare? ...(more)

Blogs : Administration

Pause SQL Server Service Before Restarting - Most end-users would rather chew tinfoil than have a database server go offline during the day. In an ideal world there would be a way for you to (1) allow end-users to finish their work and (2) not allow any new users to connect to the instance until the reboot is completed.Turns out such an option already exists, and it’s been right in front of our nose the entire time....(more)

Use Case for SQL Server Table Partitioning - Are your current mechanisms for archiving or deletion of data causing blocks or deadlocks to other processes? You might consider implementing table partitioning....(more)

Blogs : Analysis Services / BI

Where Can I Learn Enough C# to Use Biml? - The answer is the C# Primer at BimlScript.com! Read, then test your knowledge! How cool is that?...(more)

Descriptive Statistics In Power BI/M With Table.Profile() - When you are exploring a new data set it can be useful calculate some basic descriptive statistics. One new M function that appeared in Power BI recently can help you to do this: Table.Profile(). ...(more)

Blogs : Columnstore

Can ColumnStore Help Pagination Workloads? - Based on Aaron Bertrand's experiment, in isolation, the answer to the title of this post is a resounding NO. ...(more)

Clustered Columnstore Index: Data Load Optimizations - Minimal Logging - SQL Server provides three key optimizations during bulk import (Minimal logging, Reduced overhead of locks and Concurrent Inserts). This series of blog explains each of these optimizations in the context of table with columnstore indexes....(more)

Blogs : Computing in the Cloud

Application Security with Azure Key Vault - The name 'Azure Key Vault' hides a valuable Azure service that allows us to easily protect our Cloud data by putting sound cryptography in Cloud applications without having to store or manage the keys or secrets. This makes it far easier to manage cloud data in applications in a way that that complies with industry-standards for sensitive data....(more)

Restoring a Database in Azure - One of the many small things you don’t have to worry about any more when working with Azure SQL Database are those pesky backups… Ha-ha-ha! Yeah, I can’t keep a straight face when I type it. ...(more)

Blogs : Data Access / ORMs

Preview the newest ODBC SQL Server Driver for Windows and Linux - The updated driver provides robust data access to Microsoft SQL Server and Microsoft Azure SQL Database via ODBC on Windows and Linux platforms....(more)

Entity vs Value Object: the ultimate list of differences - Vladimir Khorikov attempts a comprehensive description of the differences between entities and value objects....(more)

Blogs : Data Mining/Data Analysis

Databas(ics) - The beginnings of data science is data. Buck Woody starts with the basics, but things get tricky quite quickly....(more)

Blogs : DMO/SMO/Powershell

Getting Started with the PowerShell 5.0 Information Stream - One of the great additions to PowerShell 5.0 is the new information stream. You can use this stream as a logging mechanism in your scripts and functions. Jeff Hicks demonstrates....(more)

More Answers to Your PowerShell Problems: Using and Extending Objects - More possible answers to PowerShell questions about working with objects....(more)

Blogs : Events

Microsoft’s Build 2016 Registration Opens January 19th - Last year, the event sold out within minutes, which means getting a ticket to the show is not something that you can sit around and think about. ...(more)

Blogs : Hardware

We Saw Some Really Bad Intel CPU Bugs in 2015, and We Should Expect to See More in the Future - I don’t keep track of Intel bugs unless they’re so serious that people I know are scrambling to get a patch in because of the potential impact, and I still heard about three severe bugs in the last quarter of the year alone....(more)

Blogs : High Availability/Disaster Recovery

Enhance AlwaysOn Failover Policy to Test SQL Server Database Data and Log Drives - How to add a generic script resource as a dependency to your availability group resource to enhance AlwaysOn health detection with a basic disk health check....(more)

How to create a SQL Server Availability Group WITHOUT an Active Directory Domain - Over the last few years I have worked with a lot of different customers who were not able to deploy SQL Server Availability Groups because their nodes would be standalone workgroup servers that would not be part of any Active Directory domain. Things are different with SQL Server 2016 and Windows Server 2016. Let’s have a more detailed look at that....(more)

Blogs : Performance and Tuning

Plan cache pollution or how important it is to properly define parameters in code - Recently I was involved in couple of cases concerning plan cache taking more than 20% of the SQL Server Buffer Cache and although there is no fixed threshold/best practices value, I noticed that there were a lot of identical statements getting cached....(more)

The SQL Server 2016 Query Store: Built-in Reporting - One of the most important features of the SQL Server 2016's new Query Store is the reporting. With these features, it is now possible to get a wealth of information on how your query workload is performing, either aggregated for the entire query workload or for a single query. With this information, you can see the effects of 'forcing' an execution plan for specific queries and get feedback of the consequences....(more)

Cheat Sheet: How to Configure TempDB for Microsoft SQL Server - The short version: configure one volume/drive for TempDB. Divide the total space by 9, and that’s your size number. Create 8 equally sized data files and one log file, each that size. Presto, the drive is full and your TempDB is configured for easy performance. The long version is a little more complicated....(more)

SQL Server Diagnostic Information Queries Detailed, Day 13 - Glenn Berry continues his trawl through the details of his SQL Server diagnostic queries, here covering queries that break down CPU and IO resource usage, and more, by database....(more)

Blogs : R Language

Microsoft Announces R Server And R Server Developer Edition - R Server is the re-branding of Revolution R Enterprise for Hadoop, Linux, and Teradata. In addition to R server, the company has announced R Server Developer Edition, which has all the features of the commercial version, but as a free download....(more)

New Data Sources for R - Over the past few months, a number of new CRAN packages have appeared that make it easier for R users to gain access to curated data. Most of these provide interfaces to a RESTful API written by the data publishers while a few just wrap the data set inside the package. ...(more)

R coming to Visual Studio - You might have missed one significant bit of news tucked into yesterday's Microsoft R announcement: R is coming to Visual Studio. R Tools for Visual Studio (RTVS) follows the model of Python Tools for Visual Studio: it's an open-source plug-in to Visual Studio that makes it a complete IDE for R....(more)

Blogs : Security and Auditing

Best Practices for Moving Data Encrypted with Always Encrypted - Sometimes applications need to copy or move the encrypted data from one table to another table, but there is no business requirement to transform, or even look at the data during the migration. This scenario is quite common in Extract-Transform-Load (ETL) and data archival applications. In this article, we discuss this scenario in depth and provide the associated best practices....(more)

How to Talk People Out of the SA Account, Option 2 - Having explained the kind, gentle way to talk people out of the SA account, Brent Ozar describes a time when Option 1 didn’t work, and he had to get ugly....(more)

Blogs : Software Development

Real Developer Heroics - Team working requires great tact. Bugs attract blame. The more productive the developers are, the more bugs that are associated with them, the more maintenance tasks are required for their work, and the more likely the unintended consequences....(more)

Blogs : SQL

Outlier Detection in SQL - Inevitably, the unexpected happens. A historically low-traffic channel brings in 10x the normal amount of users. Or your user login rate drops by half. In either case, these are important events that are easy to miss in a sea of data....(more)

Blogs : SQL Server 2016

Effortlessly Analyze Data History Using Temporal Tables - Temporal Tables are a new feature in SQL Server 2016, designed to help develop or migrate applications that provide insights from historical data. They allow you to track the full history of changes without additional code and let you focus your data analysis on a specific point in time in a very simple and efficient way....(more)

SQL 2016 Perform Volume Maintenance Task - Excited to see that 'Perform Volume Maintenance' could now be enabled as part of the install process, Tom LaRock lists other features he’d like to see added to the install process before SQL 2016 goes RTM....(more)

This is how to fix R Services after an in-place SQL Server 2016 CTP 3.2 upgrade - If you were using CTP 3.0 and later ran an in-place upgrade to CTP 3.2 this will silently break R Services. Uninstalling and reinstalling the R component will not fix the problem, but it can be fixed. There are a few interrelated issues here so bear with me....(more)

Blogs : T-SQL

All about the Change - SQL Audit can be viable way to track the changes in SQL Server settings....(more)

T-SQL Tuesday #74: Ch-ch-changes - Using Query Store in SQL Server 2016 to identify queries or plans that have changed. ...(more)

T-SQL Tuesday #74 - Who Grew The Database? - The topic for T-SQL Tuesday #74 is Be the Change. More specifically, data changes. How do you track changing data? How do you do your ETL? How do you clean or scrub your data? Anything related to changing data....(more)

Declarative SQL: Using CHECK() & DEFAULT - SQL is unusual is that data is not passively stored. Instead you use declarative SQL to specify the rules that underlie the data and its integrity. When used properly, constraints can avoid having to provide a lot of logic elsewhere. CHECK() and DEFAULT can do a lot to ensure that your data is correct....(more)

Natively compiled user-defined functions - New in SQL Server 2016 is the ability to natively compile user-defined functions. Gail Shaw wonders whether natively compiling a scalar function reduces the overhead when calling that function within another query. ...(more)

Join Effects With UPDATE - A lot of people don’t like UPDATE with a FROM clause. Rob Farley explores what’s going on, both logically and within the query plan....(more)

Blogs : XML, XPATH and XQUERY

JSON parsing - performance comparison - One of the first questions that people asked once we announced JSON support in SQL Server 2016 was "Would it be slow?" and "How fast you can parse JSON text?". In this post, I will compare performance of JSON parsing with JSON_VALUE function with the XML and string functions. ...(more)

Articles

Learning R: Hitting the Books - Grant Fritchey and others provide some resources to get started with learning the R language....(more)


Administrative