Sql-Server

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:

2018-06-07

While SQL Server includes an amazing array of built in functions and procedures, one that’s always puzzled me is the lack of a simple function to convert a database compatibility level to a SQL Server version ie: how do you know that 110 was SQL Server 2012?

And so, you don’t need to wait any longer.

One of the tools in our free SDU Tools collection for database developers and DBAs is SQLServerVersionForCompatibilityLevel.

2018-06-06

One thing that seems to get developers hot under the collar is whether their database columns should be nullable or not.

As mentioned in earlier posts, some try to just have everything NOT NULL to avoid dealing with the issue. But that can lead to the use of magic values where you store a value that means “there is no value” just to avoid the value being NULL. The problem with magic values is that every layer of code needs to know what’s happening. These values inevitably leak into the outside world.

2018-06-05

When developers are creating SQL Server stored procedures in T-SQL, I often see them embedding the name of the procedure as a hard-coded string within the procedure itself. Here’s a rough example:

The problem here is that if the procedure gets renamed, you then need to go and find every reference to it throughout the code.

The solution is to get the procedure name programmatically and use that:

2018-06-04

We’ve been using SQL Server database projects ever since they were called “Visual Studio Team Edition for Database Professionals”. That was back in 2005 and the name was quite a mouthful.

Until the last year or so though, we’ve only seen fairly patchy adoption of them among our customers. Over the last year or so, this has really changed. We’re seeing so many customers starting to use them. Why?

I’m guessing that it’s the increased interest in using DevOps.

2018-06-01

SQL Server Management Studio (SSMS) is based on the Visual Studio shell. What is puzzling sometimes is that features that are present in Visual Studio are not present in SSMS. One of these relates to themes. “Out of the box”, Visual Studio looks like this:

When you write code, the windows look like this:

Some people don’t like the “whiteness” of the whole screen and find darker colors easier to work with for long periods. In Visual Studio, you can change this. In Tools, then Options, you can change the theme:

2018-05-31

One of the things that I’ve always thought was missing in SQL Server and the T-SQL language was a built-in view that showed reserved words, and even better, future reserved words.

It’s one thing to say that you can create a policy to avoid people using reserved words for table, column, or object names, but how exactly do you know that the name you’ve chosen is one of those?

We decided to tackle that with one of our free SDU Tools for database developers and DBAs.

2018-05-30

I was asked about this error message by a client just last week. They had what they thought was straightforward code, and yet were getting the error above.

What they were trying to achieve was to calculate the time as 10 minutes past midnight on the same day. So, they’d used SYSDATETIME() to get the current time (it could have been GETDATE() but SYSDATETIME() is the current version that returns a datetime2 data type).

2018-05-28

In a post the other day, I described how to test if machine learning with R and/or Python was set up correctly within SQL Server 2017.

One of the comments on that post, said that the info was useful but they were still to be convinced why you’d want to have machine learning in the database in the first place.

Fair question.

I see several reasons for this. SQL Server Machine Learning Services is the result of embedding a predictive analytics and data science engine within SQL Server. Consider what happens in most data science groups today, where this type of approach isn’t used.

2018-05-25