Sql-Server

There has been quite a bit of discussion online lately about which version of SQL Server new applications should target. Members of the SQL Server product group were saying they can’t see any reason why new applications should use anything less than SQL Server 2016 as a real base line.

I’d love to see any new application using the absolute latest version of SQL Server. Unlike the bad old days where you needed to wait for a service pack before using the product, the best tested version of SQL Server is invariably the one that’s just released. (And there aren’t service packs any more anyway).

2018-06-25

When I’m doing a lot of query editing, I often get a bit mesmerized, particularly if there’s a lot of manual copy and paste or cut and paste going on.

One thing that often drives me crazy is when I use Ctrl-C (ie: copy) when I meant to use Ctrl-V (ie: paste). Invariably, I do this when I have nothing highlighted at all. So not only did I not get the value pasted, I just copied an empty value into the clipboard.

2018-06-21

Service Broker is one of my favorite tools in SQL Server. So many applications need to have a transactional queue, and many people try to build them using tables and other objects. But SQL Server has Service Broker and with it, you get the beauty of using the knowledge of someone who already knows about queues having thought about how they need to work.

But while Service Broker is quite forgiving, it’s common while developing Service Broker applications to make mistakes and end up with messages in queues that will never be delivered because you had some bug in your code. Service Broker doesn’t want to ever just throw your data away.

2018-06-20

Recently, I posted about the templates folder for SQL Server Reporting Services designer moving in Visual Studio 2017. You’ll find that blog post here.

I’ve also now found that the Private Assemblies folder has moved too. I do wish these locations would be fixed, shared, and known rather than needing to play hide and seek when updates are made to the SSRS designer tooling.

The Private Assemblies folder was used to hold a copy of any .NET assemblies that are referenced in your SSRS projects. By using .NET assemblies, you can share logic that’s used in reports. It’s worth noting that wherever we can, we try to avoid using .NET assemblies for this, and wish that SSRS had a way of dealing with common code inclusions, but that’s a topic for another day.

2018-06-18

Visual Studio is a very configurable tool, and particularly in the area of keyboard shortcuts. Because SQL Server Management Studio (SSMS) is based on Visual Studio, it inherits many of these configuration options. SSMS has a very rich set of keyboard shortcuts. Without trying to cover most of them, I do want to highlight a few that I think are really important to know how to use.

Let’s start with an easy set of commands:

2018-06-14

I often need to convert values from one data type to another, particularly when working with strings. One function that I’m surprised wasn’t already built in to SQL Server was one that converts a character to its hexadecimal representation.

There is an ASCII function. You can see it here:

This converts a character to its ASCII value (see http://asciitable.com )

We’ve added a function to our free SDU Tools collection for developers and DBAs. It’s called CharToHexadecimal and you can see what it does in the main image above.

2018-06-13

I’m always telling students that one of the first things you need to learn to do when working with SQL Server Reporting Services (SSRS) is to create report templates.

Now I really wish that SSRS had a real templating system, but what we have at present is just a folder that contains the items that we can create. For example, in an SSRS project, if I right-click Reports, and click Add, then New Item:

2018-06-12

We help a lot of customers upgrade their systems. Part of that job involves looking for code smells before starting the upgrades. If you aren’t familiar with code smells, this Wikipedia article explains it pretty well: https://en.wikipedia.org/wiki/Code_smell.

Specifically, they describe a code smell as any characteristic in the source code of a program that possibly indicates a deeper problem.

One of these is the presence of TOP 100 PERCENT or perhaps written as TOP (100) PERCENT in SELECT queries. Almost always, we see this in the definition of views.

2018-06-11

In a recent post, I discussed issues I found when testing the Machine Learning Services setup in SQL Server 2017.

After that, my old friend Niels Berglund also posted about issues he found after installing CU7 (cumulative update 7) and how he solved them. Niels’ article is here: http://www.nielsberglund.com/2018/06/01/sql-server-machine-learning-services-and-sql-server-2017-cu-7/

What each of those articles discussed though was detail on how temporary files are used by Machine Learning Services in SQL Server to hold R or Python data for sessions. By default, SQL Server configures itself to hold data for up to 20 concurrent sessions.

2018-06-08

If you ever have long scripts in SQL Server Management Studio and need to refer to a particular line, it can be helpful to have line numbers shown. This is even more useful if you ever need to write a set of instructions for someone on how to modify a query, or if you are trying to describe how a query works.

Instead of having a query like this: