The Complete Weekly Roundup of SQL Server News

Hand-picked content to sharpen your professional edge

The comma-separated value (CSV) file format is the standard way of transferring data between databases, even different RDBMSs, and between applications and databases. When done properly, it is the best, probably the only, convention for representing tabular data in a text file. It works.

SQL Server does many things with great elegance, but it can't do CSV properly. A standard for CSV has been published since 2005, RFC 4180. Other RDBMSs can do it well. PostgreSQL's CSV support implements the RFC specification as well as a host of dialects and variants. If it detects the slightest problem, it abandons the import with a helpful error message. MongoDB imports CSV straight into binary JSON (BSON) without even blushing. I've never hit a problem with either.

MS SQL Server can neither bulk-import nor bulk-export CSV. As the documentation says:

"Comma-separated value (CSV) files are not supported by SQL Server bulk-import operations. However, in some cases you can use a CSV file as the data file for a bulk import of data into SQL Server. Note that the field terminator of a CSV file does not have to be a comma."

Yes. SQL Server has its own dialect of CSV that is vaguely described here. It is known by many DBAs and Database Developers as the 'Comedy-Limited' format. It can result in data being quietly corrupted in ways that are difficult to detect (a proper importer should 'fail fast' and volubly). Many database people waste a great deal of time writing PowerShell or Python routines to correct the CSV that SQL Server produces, or converting proper standard CSV into SQL Server 'Comedy-Limited' format.

SQL Server's failure to support CSV properly has allowed a whole third-party tool industry to thrive. There are organizations dedicated to providing tools, such as CSVLint, to validate your CSV and promote the use of schemas (ironically, in JSON format).

Just don't get me started about the tool in SSMS called the import flat file wizard (Tasks…| Import flat file …). I get a strange jolt of surprise when it even manages to detect CSV properly. So often, one reflexively reaches for PowerShell just to do the job properly, and for the purposes of anger-management.

I realise that the SQL Server team has problems with text-based bulk import. As well as its own backward-compatibility problems with BCP and other bulk import and export methods, it must be compatible with MS Office too. However, this is a fairly trivial problem, at least compared with hosting SQL Server on Linux, for example, and there must be enough bad-tempered veterans like me who would help out with an open source project. After all, we've all had plenty of experience fixing recalcitrant data files.

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.

Webinars

Database DevOPs September Training Schedule - Do you need to do more with less as a Database professional? Why not start your journey to Database DevOps nirvana today with our September Training schedule. You will learn Source Control, Continuous Integration & Continuous Delivery for the Database....(more)

Gene Kim joins Redgate to discuss The 2018 Accelerate State of DevOps Report - Microsoft MVP Steve Jones is joined by acclaimed author and researcher Gene Kim to discuss the latest in all things DevOps. They offer a closer look at the key findings in the 2018 Accelerate State of DevOps Report, and investigates the growing importance of the database in successful DevOps and IT performance....(more)

SQL in the City Streamed - The theme for September 2018's SQL in the City Streamed is Adapt and thrive as a data professional. Microsoft MVPs, Kathi Kellenberger, Grant Fritchey, Steve Jones and Rob Richardson will head up the speaker line-up, and will be joined by members of the team at Redgate and other technical experts....(more)

How to extend DevOps practices to the database - In this webinar Arneh Eskandari will walk you through Redgate’s Database DevOps solution, showing reliable, scalable and repeatable processes for automating your database development and deployment....(more)

Vendors/3rd Party Products

SQL Prompt Code Analysis: INSERT INTO a permanent table with ORDER BY (PE020) - Phil Factor brings us more SQL Prompt Code Analysis, this time INSERT INTO a permanent table with ORDER BY (PE020)...(more)

Documenting your Database with SQL Change Automation - Generating database documentation can be time consuming. Rather than abandoning it as a routine part of the process, try generating it separately, as a parallel process to the build, using the same tools. Phil Factor explains how....(more)

T-SQL

Using sys.sql_expression_dependencies as a Single Source to Find Referenced and Referencing Objects - Using the sys.sql_expression_dependencies catalog view to find things referencing tables and finding things that are referenced by a stored procedure or view...(more)

Remember the Default Window - Steve Jones demonstrates why you should always include the partition and the framing to avoid any issues, when writing Window functions....(more)

Actual Execution Plan Costs - If we execute a query at the same time we capture a plan, we have enabled SQL Server to also capture run-time metrics with that plan. So we end up with what is known as an actual plan, but it’s still just an estimated plan plus those run-time metrics....(more)

Is a SELECT statement always executed? - In some cases SQL Server is able to detect contradictions in your queries, and just doesn’t execute your queries anymore....(more)

SQL Prompt Code Analysis: Avoid Using ISNUMERIC Function (E1029) - Avoid using the IsNumeric() function, because it can often lead to data type conversion errors, when importing data. If you’re working on SQL Server 2012 or later, it’s much better to use the Try_Convert() or Try_Cast() functions instead. On earlier SQL Server versions, the only way to avoid it is by using LIKE expressions....(more)

Tall Tales From Table Variables - In narrow plans, the work SQL Server has to do to modify many indexes is hidden from you. However, these plan choices are prone to the same issues with estimates that any other plan choices are. During a conversation about when temp tables or table variables are appropriate, it came up that table variables are better for modification queries, because not all the indexes had to be updated at once....(more)

How to cause a simple spill to tempdb - I recently was going through some demos in SQL Operations Studio, and I found that a spill on a sort operator wasn’t causing a warning to visibly show in the graphic execution plan. To demo and report the problem, I set up a quick code sample to cause a spill that could be run in any database....(more)

SQL Server Security

Questions About Kerberos and SQL Server That You Were Too Shy to Ask - Kerberos authentication is a topic that many database administrators avoid. It’s really not that difficult to understand, but it’s also easy to get wrong. In this article, Kathi Kellenberger talks about what you need to know about configuring Kerberos for SSRS and SQL Server databases but were too shy to ask....(more)

PowerShell

Deploying To a Power Bi Report Server with PowerShell - I needed to automate the deployment of some Power Bi reports to a Power Bi Report Server PBRS using TFS. I had some modified historical validation dbachecks pbix files that I wanted to automate the deployment of and enable the client to be able to quickly and simply deploy the reports as needed....(more)

Creating a history timeline - Every DBA needs to know that scheduled jobs and backups ran, and whether or not they are successful. In the case of scheduled jobs, we want to make sure that there are no clashing and those heavy workloads such as ETL, backups, integrity checks and index maintenance run in isolation as much as possible....(more)

PowerPivot/PowerQuery/PowerBI

Find Mismatch Rows with Power Query in Power BI - You may have customer records coming from two sources, and want to find data rows that exist in one, but not the other. Reza Rad shows how to find out which records are missing, with Merge, and then report it in Power BI. ...(more)

Creating Map Small Multiples In Power BI With The Azure Maps API - Chris Webb shows how to use the Azure Maps API to create map small multiples.His example is a table from a sample report that displays crimes committed in London in June 2018, with one row for each crime and a map column displaying the location of the crime....(more)

Power BI Introduction: Building Reports in Power BI Desktop — Part 7 - Power BI contains rich visualizations that allow anyone to build dashboards. In this article, Robert Sheldon shows you how to create Power BI charts, tables, maps, and slicers....(more)

Showing KPI’s in a table or Matrix with Power BI - Can we do KPIs in a matrix or table, with Power BI, just like we can with PowerPivot?...(more)

Performance Tuning SQL Server

Performance Basics: Indexed views - A basic but illustrative example of a case where an indexed view can improve query performance....(more)

Showplan Enhancements for UDFs - SQL Server 2017 CU3 introduced UDF execution statistics into the QueryTimeStats node of the XML output, revealing the true impact of scalar UDF execution. However, there is an interesting catch: you have to collect the actual execution plan using an up to date version of SSMS, or using SentryOne Plan Explorer, or the information will be removed from the execution plan....(more)

MDX/DAX

Dax: Back to the basics - Matthew Brice attempts to provide a 10,000-foot view of DAX, stressing that it's all about manipulating the values that are filtering columns in the data model....(more)

Database Design, Theory and Development

Designing a database: 7 things you don't want to do - Your database design is awful. The reason nobody has told you this yet is for one of two reasons: ignorance or apathy. They either don’t know it’s bad, or they don’t care....(more)

Relational databases aren’t the problem - Randolph West offers his 'rebuttal' to a recent stream of articles regarding the problems with relational databases....(more)

Data Visualisation : Public Datasets

Analysis of Los Angeles Crime with R - Heat maps of crime in L.A., using R to mine data provided by the Los Angeles Police Department....(more)

Data Privacy, Complianace, and GDPR

8 practices for business data security - Is your data vulnerable to security breaches? Sophie Ross at Security Gladiators provides 8 rules you should be adhering to to ensure the safety of your data....(more)

What SOX means to the DBA - The worry that processes in place to ensure compliance can bottleneck the development process is a very real one. So how can you have the best of both worlds? In this article Rebecca Edwards discusses the implications and possible solutions. ...(more)

Data Mining/Data Analysis

Beyond Interactive: Notebook Innovation at Netflix - Notebooks have rapidly grown in popularity among data scientists to become the standard for quick prototyping and exploratory analysis. Data Engineers and Scientists from Netflix explains some of the novel ways they’re using Jupyter notebooks. ...(more)

Conferences, Classes, and Events

Learn from DevOps experts at Redgate’s SQL in the City Summits this October - Register for one of Redgate’s SQL in the City Summit’s this October, whether you choose to come to the bright lights of New York City, or want to be among the high rises in Canary Wharf, London, or overlooking the Millennium Park in Chicago. Now’s your time to make the step towards gaining the know-how you need to help you fully adopt DevOps in your organization....(more)

Bugs/Patches for SQL Server

Issue with security update for the Remote Code Execution vulnerability in SQL Server 2016 SP1 (GDR): August 14, 2018 - On Tuesday August 14 we published a Security Update for six different releases of SQL Server 2016 and 2017. For one of those releases, SQL Server 2016 SP1 GDR (KB4293801), an issue may occur after applying the update where the sqlceip.exe process experiences an unhandled exception. ...(more)

Resolved: Issue with security update for the Remote Code Execution vulnerability in SQL Server 2016 SP2 (CU): August 14, 2018 - We have replaced KB4293807 with KB4458621. If you have previously installed KB4293807 it is recommended that you install KB4458621 as soon as possible....(more)

Azure SQL Database

My Azure SQL Database Elastic Job is Broken! - Elastic pools are a fabulous way of saving money when running many Azure SQL Databases, that is assuming you understand the resource utilization patterns of the databases involved....(more)

Azure SQL Database high availability - James Serra explains how Azure SQL Database achieves high availability....(more)

The Good The Bad and The SLA - Azure SQL Database across all service tiers gives you, the customer, a SLA of 99.99% up-time. But there are some interesting reasons for downtime that DON'T count as availability issues....(more)

Administration of SQL Server

Data Compression + Backup Compression = Double Compression? - We don’t get double the compression by using both data and backup compression, but whether we use data compression or not within our database using backup compression will get you a pretty significant space saving when looking at the size of the backup file on disk....(more)

Foundational Material: Microsoft SQL Server Book and Blogs From The Past - Brent Ozar shares some of his favorite books and blogs from Microsoft, from the way-back machine. "I’ve learned a lot from them, and I think most people who use SQL Server regularly would benefit from reading them, if they haven’t already."...(more)

It’s Time to Improve DBCC CHECKDB - Admins should know how to set up and run corruption checking because it’s just so doggone important...but if it’s so important, why isn’t SQL Server doing it in the background automatically, like $30 RAID cards have been doing for decades?...(more)