The Complete Weekly Roundup of SQL Server News

Hand-picked content to sharpen your professional edge

A while back, I did a blog about SQL Code Smells, based on suggestions from a number of SQL experts. I later extended it and published it as a booklet. SQL Smells are just indications of where one should focus efforts on improving code. They are not supposed to be condemnations of a particular practice in SQL. More recently, I had to tidy up some databases that were sunk in 'technical debt', so I devised a simple query that checked for twenty obvious problems in tables. I ended up putting this in a Blog post on Table Smells.

Being an untidy and rather busy person, I forgot to put in a few obvious checks such as the use of VARCHAR(1) or VARCHAR(2), NTEXT, TEXT, MONEY, FLOAT, REAL or SQLVARIANT in a datatype, having too many indexes, or having columns in different tables with the same name but different datatypes. Someday, I'll get around adding them!

When adopting the practice of Continuous Integration, it is getting more common to do some sort of automated code review at the time of integration. In general, for application code, it is useful to have a measure of technical debt, or of cyclomatic complexity. There are several tools to determine some sort of measure of general code quality. However, there is not much around for SQL Server, though I've found SQL Enlight to be useful. Nevertheless, once a database is built from version control as part of CI, it is a great opportunity to do automated code reviews the SQL way.

I'm not a great fan of SQL code policies, best practices or the like. The reason I have SQL queries to find SQL Smells is purely for my own use, to check code for which I'm responsible. If I have to check for things that need dealing with, it is so much better to do it efficiently with code, rather than search a database through an IDE. To extend its use beyond this to enforcing standards across teams doesn't really appeal to my liberal instincts. The thing about SQL is that even the oddest SQL practices can be done deliberately for good reason.

So here comes the question: if you were do decide on SQL practices that were always wrong, what would you include? What practices could justifiably be prescribed for production use? I was pondering this the other day and thinking that maybe the use of ISNUMERIC() is never justified, and I've never found a good use for SQLVARIANT in a production table column. Am I wrong? Have you any other suggestions for anything else that should be banned from any respectable SQL Server database?

Phil Factor

Phil Factor 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

New – work better with Git using SQL Source Control 5 beta - Join the SQL Source Control 5 beta and get early access to the newest features: source control your database in Git and other version control systems, merge when working with multiple branches, and deploy data-only migration scripts....(more)

SQL Server News

In-database Advanced Analytics with R in SQL Server 2016 - SQL Server R Services is designed to help with challenges relating to scalability and performance, integration and data movement....(more)

SQL Server 2016 Release Candidate 2 now available - Includes enhancements to R Services setup, SQL Server Management Studio (SSMS) and Mobile reports....(more)

Microsoft Dropped the Cover Charge on SQL Server - Microsoft got rid of the cover charge for using SQL Server. SQL Server (Developer Edition) is now free....(more)

Microsoft News : Events

Build 2016 is a Peek at the Future of Microsoft - As tech enthusiasts, we often get bogged down in the details: A particular product release, a certain feature, the size of a smart phone screen. But at its Build 2016 opening keynote, Microsoft offered a broader vision for the future....(more)

Microsoft News

Microsoft’s Windows 10 Apps Grab Continues: Win32 And .NET Converter, Xamarin For Mobile, And Bash - Microsoft has made no secret of the fact that it wants Windows 10 to be not just a universal app platform, but the universal app platform....(more)

Bash on Windows–What it Means for Chocolatey - Microsoft announced the most amazing thing build, Bash on Windows 10. Not some sort of VM or container, but running native ELF binaries on Windows under an Ubuntu subsystem. Let me say that again slowly. Windows running native Linux binaries. Not recompiled....(more)

Blogs : .NET

CI with TeamCity and Docker – Part 2 - How to create and publish Docker images of ASP.NET core applications, as part of a CI/CD pipeline....(more)

Blogs : Administration

Eight Different Ways to Clear the SQL Server Plan Cache - Nearly anytime you see the command DBCC FREEPROCCACHE mentioned in a blog post, magazine article or book, you usually get some sort of a scary warning about how you should not use it on a production system, or else life as we know it will end....(more)

SQL Agent Job Schedules - SQL Agent job schedules should be easy to read for humans if for no other reason than you have to provide this information to the auditors from time to time. ...(more)

Training Plan for Junior DBAs Learning SQL Server - Kendra Little's learning plan and links to free articles and scripts that will equip you to tackle the three most critical skills to for DBAs....(more)

Takes as Long as it Takes SQL, Break on Me, I have a DAC… - The DAC, what is it? It is the Dedicated Administrator Console. Basically it is a way to connect to your SQL Server when all the other connections are tied up. But it takes a little bit of pre-planning so that you can use it when things go bad with your SQL Server. ...(more)

Blogs : Analysis Services / BI

Data Import Best Practices in Power BI - When you create a data model in Power BI, you should consider how to properly use naming convention and what columns to include, in order to improve usability and performance. This article provides a quick list of best practices valid for both Power BI and Power Pivot....(more)

Monitoring SSAS Multidimensional Non Empty Filtering Using Profiler, Part 2 - Chris Webb on how you can use Profiler to monitor the different types of non empty filtering that can occur in Analysis Services and MDX....(more)

Blogs : Azure SQL Database

Getting Started Tuning Performance in Azure SQL Database - Tim Radney considers wait statistics and high-cost queries in th context of Azure SQL Database. If you’re wondering if this aspect of your job as a production dba with on-premises databases will change when working with Azure SQL Database, the answer is not really....(more)

Blogs : Data Access / ORMs

Value Objects: Entity Framework vs NHibernate - Vladimir Khorikov discusses how Entity Framework and NHibernate allow us to deal with mapping of Value Objects in our domain models....(more)

Blogs : Database Design, Theory and Development

Clustered Indexes – Advantages & Disadvantages - Every time you create a Primary Key constraint in SQL Server, the constraint is, by default, enforced through a Unique Clustered Index. Let’s have again a look at the advantages and disadvantages of Clustered Indexes in SQL Server....(more)

Data Fundamentals for Analysts, Not Worth Repeating: Duplicates - Frequent hits @dbdebunk.com are driven by the question “Are keys mandatory?” Puzzlingly, many data professionals do not seem to understand why duplicates should be prohibited. This should worry analysts....(more)

Blogs : DMO/SMO/Powershell

Why Your PowerShell Scripts Break When You Install SQL Server Management Studio 2016 - If you’re like me, you’re pretty pumped for the release of SQL Server 2016. Chances are you’ve either downloaded the latest RC or the SSMS 2016 Preview Build. What you might not know (or have tried yet) is that your PowerShell scripts are probably going to break. ...(more)

Using PowerShell to set Extended Events Sessions to AutoStart - Using PowerShell to ensure that all SQL Servers have a certain Extended Event Session set to auto-start and that it was running. ...(more)

Blogs : Hardware

Hewlett Packard Enterprise Persistent Memory - Hewlett Packard Enterprise (HPE) has announced a new product that uses non-volatile DIMMs (NVDIMMs), which they are calling Persistent Memory....(more)

Blogs : High Availability/Disaster Recovery

Certain DBs in an AG not Backing Up - The Database Avenger investigates an alert that the percentage of transaction log in use on a production database, in an Always On Availability Group, was increasing more than it should have been....(more)

Blogs : NOSQL

Relational Algebra and its implications for NoSQL databases - With the rise of NoSQL databases that are exploiting aspects of SQL for querying, and are embracing full transactionality, is there a danger of the data-document model's hierarchical nature causing a fundamental conflict with relational theory? We asked our relational expert, Hugh Bin-Haad to expound a difficult area for database theorists....(more)

Blogs : Performance and Tuning

Understanding SQL Server Query Store - Query Store was introduced in SQL Server 2016 and on Azure SQL DB v12 as a way to track query execution statistics. When Query Store is enabled it will save the query SQL text, the execution plan and execution stats like number of executions, elapsed time, logical reads etc. The information is persisted in the database and allows for later analysis...(more)

SQL Server 2016 Query Store: How to fix problems you face for slow running queries - Discussing performance issues related to the query plan choice change and how the Query Store can help us to identify queries that have become slower....(more)

SQL Server 2016 features: Query Store - Gail Shaw takes an initial look at Query Store, the new flight recorder for a SQL database....(more)

Index Creation VS SSMS Query - Is creating an index is always faster than running the same SELECT statement. If so, why?...(more)

Indexes Helping Indexes - Missing index requests. While creating indexes. If that isn’t the ultimate cry for help, I don’t know what is....(more)

Can I Force A Hinted Plan with Query Store in SQL Server 2016? - At this point Query Store does kill off the old, frustrating Plan Guide feature. Plan Guides do let you add query and (some) table hints to queries, even if you can’t change the code for whatever reason. Query Store only replaces one of the three types of plan guide – the type of Plan Guide that freezes execution plans....(more)

NO_PLAN and NO_INDEX: Breaking a Forced Query Store Plan - With 2016 Query Store, a natural question is, “What happens if I force an execution plan, and that plan is no longer valid?” Kendra Little takes a tour and then sums up all the takeaways in a nice little list....(more)

Returning Values From Query Plans Using C# - Searching for values in a query plan may be useful when running unit tests for SQL: you may be using it to confirm that a certain operator is used in the query plan, or whether a seek or scan is used… the possibilities are really endless. ...(more)

Memory Pressure in SQL Server - Servers with memory pressure are a nuisance – be they badly specified or misconfigured. Either way, they’re not performing properly. But the question is how often and how long are they affected?...(more)

Blogs : Professional Development

Building a Company Is Like Multi-Player Katamari Damacy - When you’re starting a company by yourself, you start all alone with limited capabilities. You only have so many hours per day, and there’s a lot of skills you haven’t acquired yet. You tackle a lot of small tasks manually, doing them over and over again, until you build processes that help you conquer larger tasks....(more)

Blogs : Service Broker / SOA

SQL Server Service Broker – Service Architecture - Each year, we fill out that 1040 or 1040EZ tax form...it is received by the IRS and goes into a queue, awaiting review. At some point, our tax return is processed. If all goes well, our return is approved and the IRS cuts us a check. That is a Service Broker application....(more)

Blogs : SQL Server 2016

SQL Client Tools update for SQL Server 2016 - In SQL Server 2016 there have been several significant changes to the SQL Client Tools. Central to these changes are two primary goals: the ability to ship tooling updates on a regular, monthly cadence, and a single version of tools that work with all supported SQL Server versions on-premises or in a Virtual Machine, Azure SQL Database, and Azure SQL Data Warehouse....(more)

Considerations around validation errors 41305 and 41325 on memory optimized tables with foreign keys - While testing a customer workload we encountered a delete transaction that was under the Snapshot Isolation level that was still failing with one of the validation errors. This was puzzling, because under the Snapshot isolation level, you don’t expect to fail with repeatable read validation errors...(more)

SQL 2016 – It Just Runs Faster: Automatic Soft NUMA - Many of the SQL Server, common structures are designed with various partitioning schemes and rooted around the NUMA layout of the machine. ...(more)

SQL 2016 – It Just Runs Faster: Updated Scheduling Algorithms - SQL Server 2016 gets a scalability boost from scheduling updates. Testing uncovered issues with the percentile scheduling based algorithms in SQL Server 2012 and 2014. A large, CPU quantum worker and a short, CPU quantum worker can receive unbalanced access to the scheduling resources....(more)

Blogs : T-SQL

SQL Server System Functions: The Basics - Every SQL Server Database programmer needs to be familiar with the System Functions. These range from the sublime (such as @@rowcount or @@identity) to the ridiculous (IsNumeric()) Robert Sheldon provides an overview of the most commonly used of them....(more)

Blogs : Virtualization

DIY – Getting started with Hadoop and Virtual Machines - How to get started using Hadoop with SQL Server 2016. The first step is to create a virtual machine so that you can run a Linux instance for Hadoop. As I know that installing a virtual machine can be intimidating, this post explains what you need to do, and how to fix a problem you may run into when running a virtual machine....(more)

Docker For Windows Beta Released - Docker have announced and released a replacement for Docker Toolbox....(more)