The Complete Weekly Roundup of SQL Server News

Hand-picked content to sharpen your professional edge

A few years ago I was listening to a presentation from Microsoft where they touted a customer that had real time analytics with Analysis Services. Since my exposure had been that cubes needed to be processed, a resource intensive process, I was wondering what this meant. Imagine my surprise to hear that the cube was being updated within 5 seconds of the changes in the OLTP system. Talk about real time.

Of course, the definition of real time isn't really well known. We linked a piece in this week's newsletter that looks at a survey of companies and their investment in real time, streaming, data movement pipelines. The definitions from companies about real time range from sub-second to 24 hours. I'm sure I think that 24 hours is real time, but if you're used to processing cubes weekly, maybe that feels real time. I tend to see real time on the order of minutes, maybe a few tens of minutes. Once we get to hours, that feels more like batch processing, albeit fairly rapid batch work.

I am sure there are systems that need real time analysis, especially in some areas where decisions must be made quickly. As we move to more and more machine learning algorithms and automated intelligence, there will certainly be more call for real-time data movement. Yet another opportunity for data professionals, especially those that work with ETL pipelines. There will be tough problems, not only with moving data, but tracking lineage, recovering from issues, even weeding out bad data quickly.

However, that's an opportunity for you, not me. I bet there are a few data professionals out there that feel the way I do. There are great challenges in solving real time problems, and building systems that can handle high volumes of data. The thing is, I value my sleep. I value not working all-nighters, I value a balance in my life. While I find the problems fascinating and the money involved tempting, I prefer to work on more pedestrian systems.

I'm glad there are people that want to work on very difficult problems, and I wish you all the best in taking advantage of these opportunities. I hope you're well paid, and you have a great time building these impressive systems. I also hope to read about some of the amazing things you do, so please, share the knowledge where you can. This is a great, exciting time to work with data, and it's one that I continue to enjoy every day.

Steve Jones from SQLServerCentral.com

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

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.

Vendors/3rd Party Products

Redgate’s 2016 roadmap - What do you get for your Support & Upgrades package? Read this blog post to learn about the new functionality and improvements Redgate is releasing this year. Find out more....(more)

Tech News : Conferences and Events

Redgate at SQLServerGeeks Annual Summit 2016 – Bangalore - For the first time this August, the Redgate team are heading to SQLServerGeeks Annual Summit 2016. Catch some great sessions from Grant Fritchey and Alex Yates over the three days, or just stop by and say hello at the booth. With 5 tracks, over 100 sessions, and multiple deep dive pre-con seminars, Asia’s only Data Platform conference is an event you do not want to miss! There’s only a few weeks to go, so get your tickets now....(more)

Tech News : Security

Why am I in a data breach for a site I never signed up to? - This question in the title of this post comes up after pretty much every data breach I load so I thought I'd answer it here once and for all then direct inquisitive Have I been pwned (HIBP) users when confusion ensues in the future. Let me outline a ......(more)

Microsoft Wins Major Legal Victory in Battle Over Data Searches - Tech giant Microsoft scored a major legal victory yesterday with a unanimous decision by an appeals court that ruled warrants issued by U.S. authorities do not extend to data stored in other countries. The ruling by the Second U.S. Circuit Court of Appeals ......(more)

Google Reports Record Number of Government Requests for User Data - The number of government requests for user information from Google reached new highs in the second half of last year, according to the company?EU?s latest transparency report. The United States was far and away the biggest source of information requests ......(more)

SQL Server News

Always Encrypted in Azure SQL Database is Generally Available - I’m happy to announce Always Encrypted in Azure SQL Database is now generally available! Always Encrypted is a feature designed to ensure sensitive data and its corresponding encryption keys are never revealed in plaintext to the database system. With ......(more)

SQL Server Migration Assistant for Datazen Preview now available - Today, we’re happy to announce the preview version of the SQL Server Migration Assistant for Datazen is now available from the Download Center. Download SQL Server Migration Assistant for Datazen This application is designed to help organizations migrate ......(more)

Cumulative Update #13 for SQL Server 2012 SP2 - Dear Customers, The 13th cumulative update release for SQL Server 2012 SP2 is now available for download at the Microsoft Support site. To learn more about the release or servicing model, please visit: CU#13 KB Article: https://support.microsoft.com/en-us/kb/3165266 Understanding IncrementalServicingModel for SQL ......(more)

Cumulative Update #4 for SQL Server 2012 SP3 - Dear Customers, The 4th cumulative update release for SQL Server 2012 SP3 is now available for download at the Microsoft Support site. To learn more about the release or servicing model, please visit: CU#4 KB Article: https://support.microsoft.com/en-us/kb/3165264 Understanding Incremental ......(more)

SQL Server 2014 SP2 is now available - The SQL Server team is excited to bring you SQL Server 2014 Service Pack 2 (SP2). This service pack is available on the Microsoft Download Center, MSDN, Eval Center, MBS/Partner Source and VLSC. As part of our continued commitment to software excellence ......(more)

Microsoft JDBC Driver 6.0 for SQL Server is now released! - This post was authored by Andrea Lam, Program Manager, SQL Server. We are pleased to announce the full release of the Microsoft JDBC Driver 6.0 for SQL Server! The updated driver provides robust data access to Microsoft SQL Server and Microsoft Azure ......(more)

Microsoft drivers 4.0 for PHP for SQL Server with PHP 7.0 support released - Dear PHP Community, We wanted to extend a massive ‘thank you’ for providing feedback for our preview releases over the last few weeks. We’ve been working hard to incorporate the feedback you have provided us. You will find that we’ve fixed many issues ......(more)

Product Upgrades and Releases

New SQL Server First Responder Kit for 2016-07 - SQL Server 2012 SP3, 2014 SP2, and 2016 users are going to find a lot of stuff to love in here. The new per-query memory grants fields in the plan cache are exposed in sp_BlitzCache, and sp_BlitzFirst also shows memory grants for live running queries ......(more)

Azure SQL Data Warehouse has landed. Now we’re going to make it fly. - Yesterday, Microsoft announced the general availability of Azure SQL Data Warehouse. But what does general availability actually mean and what makes it of news to Redgate? Good question. I’ll tackle general availability first. It’s another of those terms ......(more)

Product Reviews

Using SQL Compare with Row Level Security - SQL Server 2016 brings with it a slew of new features that will be both useful and appreciated by many customers. Quite a few of these features relate to providing additional security for databases, one of which is Row Level Security (RLS). This is a ......(more)

Microsoft News : Certification

Microsoft Announces Microsoft Professional Degree Program - by Angela Guess A new release out of Microsoft states, “On Wednesday at the Worldwide Partner Conference, Microsoft Corp. announced the Microsoft Professional Degree (MPD) program, the first program of its kind to offer employer-endorsed, university-caliber ......(more)

Hardware News

Seagate's New 'Guardian Series' Portfolio Brings 10TB Helium HDDs to Consumers - Our recent interview with Seagate's CTO, Mark Re, gave us an idea about of the future of the hard drive market. As late as Q1 2016, Seagate had considered helium-based drives suitable for high-end applications only. However, the fast-changing competitive ......(more)

Smallest Hard Disk to Date Writes Information Atom by Atom - by Angela Guess A new release out of the Delft University of Technology reports, “Every day, modern society creates more than a billion gigabytes of new data. To store all this data, it is increasingly important that each single bit occupies as little ......(more)

Blogs : Administration

Source Control for the Reluctant DBA - In the second part of his ongoing SQL Server and Continuous Integration series, James Anderson looks at using the version control system Git with SQL Server....(more)

Another Query to Help Find the Right Event - Finding the right event or combination of events to monitor may seem like quite a daunting task with so many events to explore and (frequently) too little familiarity with Extended Events. In this follow-up article to Extended Event Help Queries, I will ......(more)

Transaction dependency limits with memory optimized tables – Error 41839 - As we discussed in a prior blog on considerations around validation errors , part of transaction processing on memory optimized tables include transactions taking dependencies on other ......(more)

Where SQL Server does store the data of DMVs? - (Be sure to checkout the FREE SQLpassion Performance Tuning Training Plan - you get a weekly email packed with all the essential knowledge you need to know about performance tuning on SQL Server.) One of the questions that I get over and over again ......(more)

How to Change Fill Factor in SQL Server – Interview Question of the Week #080 - It is surprising to see that lots of people do not know that SQL Server Fill Factor 0 (which is the default value when SQL Server is installed) is equal to value as 100. If you did not know that value 0 of this setting is equal to value 100. I am very ......(more)

Default database file sizes - It’s Chris Yates (b/t) birthday! (I think he just tured 19.) And in honor of his birthday we are writing blog posts! Well, technically it’s T-SQL Tuesday again, but we can pretend we are writing for Chris’ birthday. In fact that’s the subject he’s selected. ...(more)

Altering a column in a large table: A case study - Fair warning, this is a discussion piece. I had a task, I went through several possibilities and I’m going to share each of them and the results of those I tried. I will not be giving any specific examples or code. Feel free to give me your opinion in ......(more)

Extended Event Help Queries - Enjoy this article re-publication from my original work at SQL Solutions Group. When working with Extended Events, there are times when a little more information is, well, helpful.  You know you want to use extended events to try and monitor for a specific ......(more)

Want to be able to read your database between restores? - When restoring a database there are times when it would nice to restore a bit, check what’s been restored so far, restore a bit more etc. However, traditionally most people only know RECOVERY and NORECOVERY. This means that you restore to a point in ......(more)

T-SQL Tuesday #80 – SQL Server 2016, using the Query Store for READ-ONLY databases - The new Query Store is one of the most exciting features coming with SQL Server 2016, but unfortunately comes with some limitations. Learn how we can overcome one of those    This post is part of the T-SQL Tuesday, which is a monthly blog party on the ......(more)

Improvements with SSMS 2016 - The staple of every SQL Server developer's world, SSMS has been impossible to usurp for the majority of us. However, it's also been behind the development cycle of visual studio, and didn't have continual updates. That changed recently, as I've previously ......(more)

Max Degree of Confusion (Dear SQL DBA Episode 8) - Learn how to configure the Max Degree of Parallelism and Cost Threshold for Parallelism settings in SQL Server – and how SQL Server 2014 SP2 and SQL Server 2016 change the way that SQL Server automatically configures some SQL Servers with lots of cores. This ......(more)

T-SQL Tuesday #80 - For T-SQL Tuesday #80, Chris Yates (b|t) asks "Perhaps there is a feature you would like to see added into SQL that just isn’t there yet – what is it?". That's a great question! In a recent post, I explored how to handle the startup event of a SQL Server ......(more)

Blogs : Analysis Services / BI

How Real is Real Time? - The key to implementing real-time analytics is understanding what "real-time" means for your organization and your application. ...(more)

What’s New In Analysis Services 2016 Multidimensional? - With the recent release of SQL Server 2016 some of you old faithful SSAS Multidimensional users out there might be wondering if it’s worth upgrading. There is an official page describing what’s new in Analysis Services 2016 here: https://msdn.microsoft.com/en-us/library/bb522628.aspx …but ......(more)

Deprecated And Discontinued Functionality In SSAS 2016 - Some time ago I blogged about the deprecated and discontinued functionality in SSAS 2014, so I thought it would be a good idea to follow my last post on what’s new in SSAS 2016 Multidimensional with a discussion of what’s going or gone from it. The same ......(more)

Blogs : Azure SQL Database

Use PowerShell to automate your Azure DB creation - Why create your databases manually if you don’t have to?  If there is one thing that I hate to do it is extra work when it’s not needed.  With the magic of PowerShell and Azure PowerShell modules you can easily automate the creation of your Azure Database.  ......(more)

Azure SQL Database waits you should be aware of… - I’ve been using Microsoft’s cloud database for some time now. I’ve had a few customers with various performance problems and thought I’d take a moment to highlight the interesting behavior you may run into as well. As you can expect with any type of ......(more)

Blogs : Backup and Recovery

Understanding SQL Server 2016 Backup Options - Maximum availability is the goal for almost all enterprise database implementations. To achieve that availability, you need to implement a layered approach to your infrastructure. First, you need to build your infrastructure out on an enterprise-level ......(more)

Blogs : Big Data

Big Data Ethics - Learn more about video blogger Stefan Groschupf. Over the course of the next few months we will be releasing insights in Machine Learning, the Cloud, and Big Data in this new video blog series presented by Stefan Groschupf, CEO of Datameer. Here’s Stefan’s ......(more)

Blogs : Career

Training To-Do List for New DBA - Are you an entry-level DBA, or looking to make the career switch? Feel like you are looking up from the bottom of a very tall ladder? Don't have much of a budget for training?Here's a list of resources I'd recommend, in no particular order.EDIT: I presented ......(more)

Benefits tell you a lot about a company - When you’re interviewing with a new company, pay attention to their benefits. Even the ones that aren’t important to you personally. Benefits don’t just include health/dental/vision insurance and retirement/pension (I’ll call these “core benefits”). It ......(more)

Job hunting for database professionals - Like most people, I’ve changed jobs a few times in my life. My path from teenage grocery store cashier to Database Architect was a complicated and multi-step journey. During that journey, I’ve made some good decisions, and some bad decisions. If you’re ......(more)

Blogs : Computing in the Cloud

Attach a Data Disk to a VM in the Azure Portal - In this Ask the Admin, I’ll show you how to attach a data disk to a virtual machine (VM) in the new Azure Management Portal. Adding an additional disk to an Azure VM was easy and intuitive in the classic Azure management portal, but while the new portal ......(more)

Multi-tenant databases in the cloud - For companies that sell an on-prem software solution and are looking to move that solution to the cloud, a challenge arises on how to architect that solution in the cloud.  For example, say you have a software solution that stores patient data for hospitals.  ......(more)

Blogs : Data Mining/Data Analysis

Creating Ranked Position Time Series with SQL - The Los Angeles Lakers were the top NBA team in 2008 but the last in 2016. We wanted to compare their downfall to the rise of other teams in the league, so we built a comparison of each team’s rank in the standings over the past ten years. Let’s explore ......(more)

Calculating SQL Saturday Distances - A couple of days ago, Argenis Fernandez put up a notice that PASS is changing the rules for SQL Saturdays: The first change we are going to make is to the amount of funding support we provide organizers. Currently any organizer, whatever the size of ......(more)

Blogs : Database Design, Theory and Development

The Guts n’ Glory of Database Internals: The communication protocol - With all the focus on writing to disk and ensuring the high speed of the database, I almost missed a very important aspect of databases, how do we actually communicate with the outside world. In many cases, there is no need for that. Many databases just ......(more)

The Guts n’ Glory of Database Internals: What the disk can do for you - I’m currently in the process of getting some benchmark numbers for a process we have, and I was watching some metrics along the way. I have mentioned that disk’s speed can be effected by quite a lot of things. So here are two metrics, taken about 1 minute ......(more)

Getting started with In Memory OLTP in SQL Server 2016 – Part 1 - In Memory OLTP (Hekaton) introduced in SQL Server 2014 is a new database engine component which is optimized for OLTP workloads to achieve the performance by storing data in memory and accessing data from memory. Memory optimized tables can be accessed ......(more)

Levels of Representation: Relationships, Rules, Relations and Constraints - What's Wrong with Last Week's Picture (Question about relational model )There are relationships at both the conceptual and logical representation levels. Confusing them is bad conceptual modeling and database design.Relationships, Rules and RelationsAT: ......(more)

Blogs : DMO/SMO/Powershell

Remove-SQLDatabaseSafely My First Contribution to DBATools - What is DBA Tools? A collection of modules for SQL Server DBAs. It initially started out as ‘sqlmigration’, but has now grown into a collection of various commands that help automate DBA tasks and encourage best practices. You can read more about here and ......(more)

Why I don’t use Invoke-SQLCmd - Unpopular Opinion Alert! I hope you’re wide awake and ready to get mad, because I have a very unpopular opinion regarding SQL Server and PowerShell: I don’t like using [crayon-578f38efd4a21683855789-i/] . That’s not to say... ...(more)

Blogs : Events

SQLSaturday #513 - Albany 2016 - Albany, NY. Jun 30, free training....(more)

Introducing the Microsoft Data Science Summit, Sep 26-27 - Microsoft has a brand-new conference, exclusively for data scientists, big data engineers, and machine learning practitioners. The Microsoft Data Science Summit, to be held in Atlanta GA, September 26-27, will feature talks and lab sessions from Microsoft ......(more)

Growth or Control – #sqlpass - I’m disturbed. I read an announcement from the PASS organization this week that bothers me. I am less upset with the content than the manner in which it was presented, though I am not pleased with either. Making SQL Saturday Sustainable was released ......(more)

Karla Set the Standard - Last week Karla Landrum announced that she will be leaving her position as Community Evangelist later this year after five years. That means change for PASS and for us and for Karla, but just the nature and scale of that pending change illustrates how ......(more)

SQL Saturday Louisville Precon – The Complete Primer to the Infrastructure Underneath SQL Server - I’m pleased to announce that I’m launching my next round of all-day precon training session at the upcoming SQL Saturday in Louisville on Friday, August 5 called ‘The Complete Primer to SQL Server Infrastructure‘. The focus of the course is to help those ......(more)

Blogs : Integration Services/ETL

SQL 2016 SSIS new feature AutoAdjustBufferSize - If you have not already seen in the What’s New in Integration Services list there is a new property for Data Flows that is called AutoAdjustBufferSize.  Why is this important and what does it actually do? First, let’s remember that in SQL 2014 we had ......(more)

Building SSIS packages using the Biml object model - Programmatically building SSIS packages via the Biml Object Model I thought it might be fun to try and figure out how to use the Biml Api to construct SSIS packages. This post is the first in the occasional series as I explore and find neat new things. ...(more)

Use Catalog Compare to Migrate to the SSIS 2016 Catalog - I recently tried to use the SSISDB Upgrade Wizard to upgrade a restored SSISDB (backed up in an earlier version) to SQL Server 2016. It didn’t go well. I decided to use SSIS Catalog Compare to generate the scripts and ISPAC files from the previous instance, ......(more)

Columnstore Index: Parallel load into clustered columnstore index from staging table - SQL Server has supported parallel data load into a table using BCP, Bulk Insert and SSIS. The picture below shows a typical configuration of a Data Warehouse where data is loaded from external files either using BCP or SSIS. SQL Server supports parallel ......(more)

Get Your Email Out of my ETL - Question from someone in one my recent classes: “What tool do you use to send email from ETL processes?” My response: “I don’t.” The tl;dr version of this post is I let my extract-transform-load processes do just ETL, and leave notifications to the scheduling ......(more)

Blogs : NOSQL

SQL and NoSQL - Despite the traditional relational DB world, a new trend is emerging which is called NoSQL. Its really hard to define NoSQL. You will get lot of information, opinions on what NoSQL is all about. This is just my understanding and research on NoSQL. Its ......(more)

Blogs : Performance and Tuning

Trace Flag 2389 and the new Cardinality Estimator - One of the SQL Server trace flags that’s been around for a while is 2389.  It’s often discussed with 2390, but I just want to focus on 2389 for this post.  The trace flag was introduced in SQL Server 2005 SP1, which was released on April 18, 2006 (according ......(more)

Why an Upgrade can Cause Performance Regressions (Dear SQL DBA Episode 9) - You finally got approval to move to new hardware and a fresher version of SQL Server. After months of work,  you do the migration and then… performance gets worse. What can cause this, and what do you look for? This is a “listen-able” 27 minute video. ...(more)

Paying Attention to Estimates - Last week I published a post called #BackToBasics : DATEFROMPARTS(), where I showed how to use this 2012+ function for cleaner, sargable date range queries. I used it to demonstrate that if you use an open-ended date predicate, and you have an index ......(more)

Why am I getting NULL values for query_plan from sys.dm_exec_query_plan? - Recently we got a customer who called in and wanted to know why he received NULL for query_plan when querying sys.dm_exec_query_plan.   This customer referenced a blog from https://dzone.com/articles/dmexecqueryplan-returning-null.  In that scenario, ......(more)

Can Adding an Index Make SQL Server 2016…Worse? - Using the StackOverflow database, let’s check out Krock’s query. He’s a competitive fella, and he’s looking to find users who signed up for StackOverflow after he did, but who have a higher reputation than he does. I’m going to simplify the query a little ......(more)

Why Not Just Create Statistics? - Here at Brent Ozar Unlimited We have a proud tradition of not blaming index fragmentation for everything. There are points you should deal with it, but they’re probably not 5% and 30% and 1000 pages. But that’s not what this blog post is about. I’m hoping ......(more)

SQL Server – How to retrieve SQL plans from Cache - Hi friends, in this blog post I will tell you how we can retrieve the SQL plan from the cache. In SQL Server there are multiple DMV (Dynamic Management Views) and DMO (Dynamic Management Objects) which can be used to retrieve the information about execution ......(more)

Performance Tuning Series – Main Part - Performance tuning is a tricky part. We need to analysis all points of view. Sometimes it is an easy fix and can be solved in a minute and sometimes it take a day or more to find and fix. I received many emails for performance tuning help. It has a variety ......(more)

Memory grant related diagnostics - Back in March I blogged about Addressing large memory grant requests from optimized Nested Loops. To further enhance discoverability of memory grant related issues, in SQL Server 2016 and 2014 SP2 we released a new xEvent (query_memory_grant_usage). This ......(more)

Blogs : PowerPivot/PowerQuery/PowerBI

SQL Server, Power BI, and R - by Sheri Gilley, Microsoft Senior Software Engineer SQL Server 2016 has reached general availability recently and one of the top new capabilities it features is SQL Server R Services --  advanced analytics in-database with the R language.   R has also ......(more)

Power BI Custom Visuals Class (Module 08 – Dot Plot) - In this module you will learn how to use the Dot Plot Power BI Custom Visual.  The Dot Plot is often used when visualizing a distribution of values or a count of an occurrence across different categorical data you may have.  Watch this module to learn ......(more)

Blogs : R Language

How R is used at Microsoft - At the useR! conference last month, I was pleased to be able to give a couple of talks about the ways that Microsoft is using and integrating R. In my first talk, Hear, See, Move, I shared how data scientists at Microsoft are working to help the disabled:   During ......(more)

The trick to understanding NAs (missing values) in R - Here's a little puzzle that might shed some light on some apparently confusing behaviour by missing values (NAs) in R: What is NA^0 in R? You can get the answer easily by typing at the R command line: > NA^0[1] 1 But the interesting question that arises ......(more)

An analysis of Pokémon Go types, created with R - As anyone who has tried Pokémon Go recently is probably aware, Pokémon come in different types. A Pokémon's type affects where and when it appears, and the types of attacks it is vulnerable to. Some types, like Normal, Water and Grass are common; others, ......(more)

Best practices for logging computational systems in R and Python - As is the case with most quant software, it’s a bit different from run-of-the-mill software. The somewhat prosaic world of logging is one such place where there are some differences. What’s different about quant systems? First, they have multiple run ......(more)

R:  The Basics Of Notebooks - This blog post is in anticipation of SQL Saturday Columbus, in which I’m going to give a talk introducing R to SQL Server developers.  My primary vehicle for explaining R will be notebooks. Notebook Basics I’ll start with two big questions:  what are ......(more)

R:  Database Access - In today’s post, I’m going to use RODBC to connect to SQL Server and perform a few operations. Pre-Requisites There are a couple of steps we’re going to have to do in order to get RODBC to work with SQL Server. Grab The Notebook The first step is to ......(more)

Spark, R, And Zeppelin - We are going to combine three of my interests today:  Spark, data analysis, and notebooks.  Unlike last week’s discussion of notebooks, I’m going to use Apache Zeppelin today.  In today’s post, we will load some restaurant data into Spark and then build ......(more)

Blogs : Reporting Services

Deliver reports via email using an email server outside your network - Today’s post is from Sundeep Meda, an engineer who’s been working on Reporting Services and other BI products here at Microsoft since 2010. As part of SQL Server 2016 we introduced a new feature to support delivering reports via email using an SMTP server ......(more)

Blogs : Security and Auditing

Getting Started with Always Encrypted Part 2 - In this blog post I am going to continue discussing the new Always Encrypted feature in SQL Server 2016.  There are 2 main aspects to Always Encrypted – first is generating the Column Master Key and Column Encryption Keys in the database where the encrypted ......(more)

Getting Started with Always Encrypted Part 1 - New to SQL Server 2016 are several new security features, each aimed at protecting your data in a very specific way.  Dynamic Data Masking allows you to create rules to mask data that you choose so that lower-security users do not see the actual data ......(more)

Why do I get the infrastructure error for login failures? - In the past few weeks, I saw this error come across quite a bit and thought I will provide an explanation for the reasons why we generate this error. It is very possible that you came across either one of the flavors of the two error messages shown below: 2016-07-08 ......(more)

SQL SERVER 2016 – Encrypt Your PII Data – Notes from the Field #132 - [Note from Pinal]: In this episode of the Notes from the Field series database expert Mike Lawell talks about his thoughts and observation about SQL Server 2016 Encryption. Security of the data is very important and we often spend so many hours securing ......(more)

Blogs : Software Development

Unit testing anti-patterns: Structural Inspection - This post is about the practice of Structural Inspection in unit testing and why I personally consider it an anti-pattern. Structural Inspection But first, let me explain Structural Inspection itself. Structural Inspection is generally about writing ......(more)

SQL Server and Continuous Integration - This is the 1st in a series of posts on SQL Server and Continuous Integration. Some of the tools I’ll be covering are: Git Visual Studio ReadyRoll GitLab tSQLt SQL Cover Three months ago I performed my first public talk at my local usergroup in Southampton. ...(more)

Source Control for the Reluctant DBA - This is the 2nd post in the series named SQL Server and Continuous Integration. The previous post explained the problems I encountered at work and how they provided the inspiration for using source control in my SQL Server projects. This post will focus ......(more)

Adding the ReadyRoll Extension in VSTS - As I’m doing a little development with ReadyRoll, I’m documenting some of the things I learn about the product. A few weeks ago I needed to do a demo with ReadyRoll for Redgate Software. I built a new project, and wanted to perform a build in Visual ......(more)

Day 1: Agile Fundamentals: Scrum, Kanban, Lean and XP - It's week 3 of my jammed packed training schedule this July. This week I'm producing course 918 Agile Fundamentals: Scrum, Kanban, Lean and XP After the pre-course meeting with Alan O'Callaghan last night I think this will be an interesting week for ......(more)

Is User Acceptance Testing Covered Under Developer Edition? - Holy mackerel, it is! I tend to think of development environments as SQL Servers where customers are surely never allowed to enter, for fear that the licensing dollars will pile up like banks of snow around the gates to the instance. But there’s an exception. ...(more)

Agile Fail: Funny User Story Edition - If you’ve ever worked on an “Agile” project, chances are you’ve come across some hilariously written user stories…perhaps even some that were so ridiculous you started questioning the string of choices and decisions you’d made that ultimately led you to ......(more)

Blogs : SQL Server 2016

Columnstore Index: Differences between Clustered/Nonclustered Columnstore Index - SQL Server 2016 provides two flavors of columnstore index; clustered (CCI) and nonclustered (NCCI) columnstore index. As shown in the simplified picture below, both indexes are organized as columns but NCCI is created on an existing rowstore table as ......(more)

Blogs : T-SQL

Simplified Order Of Operations - I recently learned that when combining multiple operators in a SQL expression, AND has a higher precedence than OR but & has the same precedence as |. I expected the precedence rules for the logical operators to be consistent with the bitwise operators. Even ......(more)

Comparing nullable columns - Do you ever compare the values of a lot of columns in two tables? Sure you do. Like, for instance, in a cross update, when you need to figure out which rows you should actually update. But it gets worse if the columns are nullable. The fact that any ......(more)

Using sp_executesql Parameters –#SQLNewBlogger - Another post for me that is simple and hopefully serves as an example for people trying to get blogging as #SQLNewBloggers. I haven’t used sp_executesql much. Instead, my habitually way of executing dynamic SQL has been with EXEC(). There are a few differences ......(more)

SQL Server – String_Escape function in SQL Server 2016 - Hi friends, in SQL server 2016 another string function that introduced is String_Escape. This function can escape special characters within texts and will return text with escaped characters. Syntax STRING_ESCAPE (expression, type) Currently only the ......(more)

Getting Table Change Scripts–#SQLNewBlogger - Another post for me that is simple and hopefully serves as an example for people trying to get blogging as #SQLNewBloggers. One of the really basic things I think everyone should understand is how to get scripts from Management Studio (SSMS) and saving ......(more)

Common Table Expression, Just a Name - The Common Table Expression (CTE) is a great tool in T-SQL. The CTE provides a mechanism to define a query that can be easily reused over and over within another query. The CTE also provides a mechanism for recursion which, though a little dangerous ......(more)

Building a Better Pokédex with SQL - Pokémon Go has taken over the world. In the moments I’m not exploring the city trying to catch ‘em all, I’ve found myself idly wondering about Pokémon Theory. What moves are super effective against which types again? What is a critical hit? And how did ......(more)

T-SQL Insert Into Table Examples - In T-SQL, to add records into a table (SQL Server) we use a Data Manipulation Language (DML) command known as Insert statement. In this article, we are going to explore the different capabilities of the T-SQL Insert statement.Let us create a table Table1 ......(more)