The 15 most popular posts in 2016

Another year is almost over. As the last post of this year I decided to share with you the 15 most popular posts in 2016. I just used Google Analytics to grab this info, I excluded the home page from the top 15.  Four of these posts were written in 2006 and one was written in 2005

Here they are in order of popularity

1 Some cool SQL Server announcements SQL Graph, Adaptive Query Plan, CTP1 of SQL vNext, SQL Injection detection
This is my recap of the chalkboard session with the SQL Server team at the SQL Server PASS summit in Seattle.

2 Five Ways To Return Values From Stored Procedures
A very old post that shows you five ways to return values from a stored proc

3 SQL Server 2016 SP1 released, SQL Server vNext available for download
Posted this while the connect event was going on

4 Installing SQL Server on Linux, connecting and running queries from Linux
A quick walk through on how to install SQL Server on Ubuntu, I also included a video in the post

5 Are your foreign keys indexed? If not, you might have problems
A post showing you that indexing foreign keys might be beneficial if you do deletes

6 Convert Millisecond To "hh:mm:ss" Format
A very old post showing you how to convert from milliseconds to "hh:mm:ss" format

7 What's new in SQL Server 2016: CREATE OR ALTER
A post showing how the new CREATE OR ALTER syntax works

8 Why you need additional privileges to truncate tables compared to delete statements
The reason why you need these privileges is shown

9 What's new in SQL Server 2016: Truncate partitions
This post shows how truncate partitions works

10 What we learned from the reddit SQL Server AMA thread on /r/sqlserver
A recap of the AMA SQL Server thread on reddit

11 Playing around with the new stuff in SQL Server vNext and SQL Server 2016 SP1
Just a post showing some of the new stuff released in SP 1 of SQL Server 2016 and vNext

12 ISO-11179 Naming Conventions
A very old post linking to the ISO-11179 Naming Conventions document

13 Query Analyzer Trick
A very old post explaining how you can drag the columns into the query window from object explorer. I think by now everyone knows this one right?

14 What was the first computer you bought, what were some of your favorite games?
Mine was the Commodore 128, what about you?

15 Chaos Isolation Level In SQL Server
This is linked from  dba.stackexchange.com, it is kind of interesting because it was a silly post I made


That is all... see you in 2017...

Didn't go to PASS but want to watch the sessions?

You didn't go to the PASS summit this year, but you would still want to watch the sessions?  There is a way now, you can buy the USB stick with all the sessions, you can also download the sessions you are interested in. The passboutique site has the details, They also have a sale going on at the moment.

So if you got a bunch of money over the holidays, this would be a great investment.....

SQL Server vNext version Community Technology Preview 1.1 available

A nice holiday present for you all has just arrived: SQL Server next version Community Technology Preview 1.1


Here is what is new in terms of the SQL Engine

• Language and performance enhancements to natively compiled T-SQL modules, including support for OPENJSON, FOR JSON, JSON built ins as well as memory-optimized tables support for computed columns.
• Improved the performance of updates to non-clustered columnstore indexes in the case when the row is in the delta store.
• Batch mode queries now support “memory grant feedback loops,” which learn from memory used during query execution and adjusts on subsequent query executions; this can allow more queries to run on systems that are otherwise blocking on memory.
• New T-SQL language features:
• Introducing three new string functions: TRIM, CONCAT_WS, and TRANSLATE
• BULK IMPORT supports CSV format and Azure Blob Storage as file source
• STRING_AGG supports WITHIN GROUP (ORDER BY)s

TRIM

So finally we can write the following instead of doing LTRIM and RTRIM

SELECT TRIM( '     NoSPaces    ') AS Result;

That will return just the value NoSpaces

You can also specify what characters to trim

TRANSLATE 

This acts like a bunch of replace statements, instead of REPLACE(REPLACE(REPLACE(REPLACE(SomeVal,'[','('),']',,')'),'{','('),'}',,')') you can do the following which is much cleaner

SELECT TRANSLATE('2*[3+4]/{7-2}', '[]{}', '()()');

Running that will return 2*(3+4)/(7-2)

Everything that is new in version 1.1 can be found here https://msdn.microsoft.com/en-us/library/mt788653.aspx

What are you waiting for??..... pick where you want to install the next version.....

• Install on Red Hat Enterprise Linux
• Install on Ubuntu Linux
• Pull and run a Docker container on Linux, Windows, or macOS
• Download the preview for Windows
• Create a SQL Server on Linux virtual machine in Azure

See...you won't be bored this weekend.....

Interesting SQL related links for the week of Dec 16, 2016

Here are some interesting articles I read and tweeted about this past week, I think you will like these as well. If you are bored this weekend, some of these might be good for you to read


SQL Server + Python — What’s new

Python is one of the most popular and fastest-growing languages used today. Pyodbc (Python-SQL Server Connector) is an open source Python module maintained by Michael Kleehammer that uses ODBC Drivers to connect to SQL Server. It allows you to connect from the platform of your choice to SQL Server on-premises and in the cloud. Pyodbc is also cross platform and can be installed using pip.

We recently announced SQL Server v.Next CTP1 on Linux and Windows, which brings the power of SQL Server to both Windows and — for the first time ever — Linux. You can now connect your applications to SQL Server running on Linux, Windows and macOS (with Docker).

For our Python developers, we have a few updates that will improve Python connectivity with SQL Server. Pyodbc is now:

SQL Server 2016 SP1: Know your limits

With the recent announcement of SQL Server 2016 SP1, we announced the consistent programmability experience for developers and ISVs, who can now maintain a single code base and build intelligent database applications which scale across all the editions of SQL Server. The processor, memory and database size limits does not change and remain as–in all editions as documented in the SQL Server editions page. We have made the following changes in our documentation to accurately reflect the memory limits on lower editions of SQL Server. This blog post is intended to clarify and provide more information on the memory limits starting with SQL Server 2016 SP1 on Standard, Web and Express Editions of SQL Server.

Parameterization for Always Encrypted – Using SSMS to Insert into, Update and Filter by Encrypted Columns

SQL Server Management Studio 17.o (the next major update of SSMS, currently available as a Release Candidate) introduces two important capabilities for Always Encrypted:

• Ability to insert into, update and filter by values stored in encrypted columns from a Query Editor window.
• The new online encryption algorithm, exposed in the Set-SqlColumnEncryption PowerShell cmdlet, which makes tables available for both reads and writes during the initial encryption and column encryption key rotation.

This article addresses the first of the two enhancements.

Defending Accounts Against Common Attacks

While we often worry about sophisticated digital attacks, the most common attacks for accessing news organizations’ accounts depend on only a few simple weaknesses. These weaknesses are usually a combination of predictable passwords, phishing emails designed to steal login credentials, as well as malicious file attachments in email and elsewhere. While the attacks are simple, so are the defenses. This collection of resources and learning materials will walk you through practices recommended by security specialists for defending your newsroom against common attacks on your accounts.

SQL Server on Linux: How? Introduction

Making SQL Server run on Linux involves introducing what is known as a Platform Abstraction Layer (“PAL”) into SQL Server. This layer is used to align all operating system or platform specific code in one place and allow the rest of the codebase to stay operating system agnostic. Because of SQL Server’s long history on a single operating system, Windows, it never needed a PAL. In fact, the SQL Server database engine codebase has many references to libraries that are popular on Windows to provide various functionality. In bringing SQL Server to Linux, we set strict requirements for ourselves to bring the full functional, performance, and scale value of the SQL Server RDBMS to Linux. This includes the ability for an application that works great on SQL Server on Windows to work equally great against SQL Server on Linux. Given these requirements and the fact that the existing SQL Server OS dependencies would make it very hard to provide a highly capable version of SQL Server outside of Windows in reasonable time it was decided to marry parts of the Microsoft Research (MSR) project Drawbridge with SQL Server’s existing platform layer SQL Server Operating System (SOS) to create what we call the SQLPAL. The Drawbridge project provided an abstraction between the underlying operating system and the application for the purposes of secure containers and SOS provided robust memory management, thread scheduling, and IO services. Creating SQLPAL enabled the existing Windows dependencies to be used on Linux with the help of parts of the Drawbridge design focused on OS abstraction while leaving the key OS services to SOS. We are also changing the SQL Server database engine code to by-pass the Windows libraries and call directly into SQLPAL for resource intensive functionality.