The Complete Weekly Roundup of SQL Server News

Hand-picked content to sharpen your professional edge

What's the oldest system you work with? In any technology-led business, there's at least one chunk of hardware or code that barely anyone understands. It's why people who know COBOL can command high salaries maintaining critical systems that have built up a gnarled shell of interoperability frameworks over the years. It's why people maintain ancient server versions, just to support that one vital piece of software that won't work on anything more recent.

It's the same virtually everywhere – time spent on maintenance or improvement of existing features is time that can't be spent on that vital feature that the business or customers need. So that hack that makes it work just for the project, the one that absolutely will be replaced as soon as everything's up and running makes it into the final build, and from there into "I can't believe anyone would choose to do that" legend.

I'm quite lucky in that I work for a company that has relatively little obstructive cruft lurking around the backend – in fact the oldest code I work with is ASP.NET 2, on the SSC forums (supporting IE6 notwithstanding). But even if you keep up to date, it's hard to keep that maintenance burden low. Automation can help, but it has to be widely-understood so that it can be maintained or altered when the original author moves elsewhere.

DBAs might have it easy comapared to some software disciplines though - I like to ponder the issues future code archaeologists are going to have with ever-changing web frameworks they encounter when the time comes to update them. Maybe there'll be some equivalent of the Wayback Machine that captures freeze-frames of trendy web development environments at a given point in time?

What business-critical logic is stored in Access on an old laptop in a cupboard somewhere? And what do you do to make sure you've got time to make improvements to your systems?

Dave Convery 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

Exports results to Excel in two clicks with SQL Prompt - In tip #5 of the Super SQL Tips series, Data Platform MVP Cathrine Wilhelmsen explains how to use SQL Prompt to quickly work with data from the Results grid in SQL Server Management Studio or Visual Studio. Learn how to copy results as an IN clause, scripts as an INSERT statement, or open in Excel, in just a couple of clicks. Watch now....(more)

Join Redgate at PASS Summit 2016 - This year Redgate have a breakout session featuring SQL Clone, as well as a booth in the exhibit hall. They'll be giving away all sorts of prizes including SQL-themed playing cards, flying sheep, and a year's worth of the world's strongest coffee! To find out the details of what they'll be up to between October 25 - 28, and to find out how you can attend the exclusive #RedgateRocks party, read this blog post....(more)

How to create a directory of object-level scripts using SQL Compare - It’s often useful to be able to create a directory of object-level scripts from an existing database, for example to put a database into version control, or search through a directory of scripts. In this simple ‘how to’ article, Feodor Georgiev expands on the four most common reasons, and shows how simple it is using SQL Compare....(more)

Developing Redgate’s SQL Toolbelt - Take a look behind the scenes to learn more about Redgate’s development challenges, what role the SQL Toolbelt plays, and which releases are coming up....(more)

Microsoft News

SQL Server 2016 Express Edition in Windows containers - Microsoft announce the public availability of SQL Server 2016 Express Edition in Windows Containers. The image is now available on Docker Hub and the build scripts are hosted on their SQL Server Samples GitHub repository. Find out more in this blog post....(more)

Blogs : Administration

Should You Rebuild or Reorganize Indexes on Large Tables? - The bigger your indexes are, the harder your index maintenance falls. Is it better to rebuild big indexes? Or should you reorganize? Kendra Little answers this question in Dear SQL DBA episode 19....(more)

Too many indexes! - You can have up to 999 non-clustered indexes per table and one clustered for a total of 1000 indexes - but should you? Kenneth Fisher doesn't think so....(more)

Can I Use Statistics to Design Indexes? - "I’ve noticed that many indexes in my data warehouse aren’t used frequently. Is there a way to use the automatically generated statistics to make useful indexes?" Kendra Little answers the question in episode 18 of Dear SQL DBA....(more)

Reading Backup History Information for a Database - There are times when you might want to read backup/restore history information to identify a backup’s start and finish date/time, the location where the backup was written, the size of the database backup, etc. Greg Larsen shows you how to do this in SQL Server....(more)

List and Execute SQL Server Stored Procedures with PowerShell - Stored procedures allow the DBA to automate a certain task by bundling up a query and executing as a single set of logic. This is considerable progress in including more automation, but why not automate the automation? Adam Bertram explains how....(more)

Blogs : Analysis Services / BI

Eek! There’s a Human in Your Data - Tricia Aanderud talks about data narratives, and the human side of analytics....(more)

Removing the Clutter from Graphs - You can quickly get an audience to see patterns and trends in data if you present that data graphically. Data visualization is often the most persuasive of mediums; and yet, it’s so easy to get it very wrong....(more)

Blogs : Backup and Recovery

Questions You Should Be Asking About Your Backups - Covering the basics, Erik Darling lists his top five most important questions to be asking about your backups....(more)

Blogs

Why I Practice Presentations - After a technical hiccup at IT/DevConnections this week, Steve talks about the importance of rehearsing as a speaker, and shares his own routine for presentation preparation....(more)

Spec Fixation - Anders Pedersen shares some examples of 'spec fixation', where things are developed exactly to specification without any thought to how the system might actually be used....(more)

Installation defaults - Kevin Hill responds to this week's T-SQL Tuesday topic of 'we're still dealing with the same old problems' by talking about the need to read installation defaults before hitting OK....(more)

Systems Monitoring Systems - What do you do when you have an important IT system that needs to be running smoothly and continuously? Naturally, you monitor it. Whether it's a commercial solution, open-source, or something home grown, your monitoring system plays a very important role. What do you do when the monitoring system fails?...(more)

Blogs : Professional Development

Your Professional Development and the PASS Organization - There are so many ways of keeping your technical skills and knowledge up-to-date. There are books, articles, conferences, video courses and so on, but nothing beats discussion and debate with professional people in other organisations facing a similar range of technical challenges, some of which you share, and others you maybe haven't yet. PASS is unique in providing just this vital ingredient for database professionals using the Microsoft Data Stack....(more)

Blogs : Security and Auditing

Passwords – a secret you have no right to share - Rob Farley argues that a password is not just your secret, but also a secret belonging to the bank/website/program that the password is for....(more)

What Every Accidental DBA Needs to Know Now: Basics of SQL Security - A basic understanding of SQL Server security involves knowing the differences between logins, users, schemas and roles. It also means you can tell the difference between SQL Server security and Trusted Authentication. Tim Ford breaks down the essentials....(more)

Stop Thinking of Compliance as a Checkbox - Matt Ploessel explains that checking off your compliance checkbox isn’t the same thing as having a proven security strategy in place or meeting a reliable security maturity level....(more)

Blogs : SQL

The Sort that Spills to Level 15,000 - Paul White shows that SQL Server sometimes produces very misleading information about the impact of sort operations....(more)

Step by Step: WHERE, GROUP BY, and HAVING - Jen McCown presents some examples to illustrate key differences between the three separate clauses in the SELECT statement: WHERE, GROUP BY, and HAVING....(more)

Logical Query Processing Part 7: GROUP BY and HAVING - Itzik Ben-Gan describes the logical and conceptual interpretations of GROUP BY and HAVING queries....(more)

Blogs : SQL Server 2016

An Overview of SQL Server 2016 Licensing - Licensing is always one of the more confusing aspects of any product, and SQL Server 2016 is no exception to that rule. In this post, Michael Otey takes a closer look at the SQL Server 2016 editions and licensing models, and examines how licensing works in virtualization and high-availability scenarios....(more)

9 amazing features of SQL Server 2016 - Rajat Jaiswal briefly introduces his favourite new features in SQL Server 2016....(more)

Articles : Administration

Find and Remove Duplicate Rows from a SQL Server Table - Though duplicate rows are bad practice, sometimes they're necessary for a short time. But how do you clean your tables when they're no longer needed? Sergey Gigoyan provides a few different solutions....(more)

Representing Hierarchical Data for Mere Mortals - Why is it that we use XML, but with so little enthusiasm when it does so much, and is so feature-rich? Phil Factor argues that there are better ways of doing it, more complete than JSON, but easier to read than XML. To try to convince you, he gives a set of flying demos, using PowerShell and his PSYaml module, to illustrate how YAML can let you work faster, and more accurately....(more)

Who the Devil Wrote This SQL Code? - The way that you format T-SQL code can affect the productivity of the people who have to subsequently maintain your work. It is never a good experience to see SQL Code, cry out “Who the devil wrote this code?”, and then realise that it was you. Grant gives some examples of bad formatting and explains why you should never check-in badly-formatted SQL code....(more)

Articles : Development

Which Edition of SQL Server is Best for Development Work? - You might think, as a developer, that nothing but the best is good enough as a development database. You might be mistaken. There is a lot to be said for LocalDB, but Ed Elliott argues that every edition has its pros and cons, and you need to consider Cloud-based resources, VMs and Containerised databases too. There is a whole range of alternatives and how you choose depends on the type of database you are developing, but for Ed, LocalDB gets the five-star accolade....(more)