Press "Enter" to skip to content

Author: Kevin Feasel

Why Window Functions Can’t Appear In The WHERE Clause

Published 2017-10-03 by Kevin Feasel

Doug Lane explains why window functions like ROW_NUMBER() cannot appear in the WHERE clause (or FROM, GROUP BY, or HAVING):

SQL Server doesn’t process parts of a query in the same order they’re written. Rather than start with SELECT the way we read and write it, here’s the order SQL Server progresses through:

  1. FROM
  2. WHERE
  3. GROUP BY
  4. HAVING
  5. SELECT
  6. ORDER BY
  7. TOP

The first four steps are all about getting the source data and reducing the result set down. Steps 5 & 6 determine which columns are presented and in which order. Step 7 (TOP) is only applied at the end because you can’t say which rows are in the top n rows until the set has been sorted. (You can read Itzik Ben-Gan’s explanation of this process in way more detail here.)

Definitely worth reading.  Doug also shows how to get around this fact of life and get the equivalent of a window function inside a WHERE clause, at least in terms of function if not necessarily performance.

Comments closed

Connecting To Named Instances Sans Name

Published 2017-10-03 by Kevin Feasel

Derek Colley shows what to do when trying to connect to a named instance without specifying its name:

This is a good question.  There’s a few ways to address it:

  • We could configure two different connection strings and on failover, the alternative is used (by any arbitrary application mechanism)
  • For mirroring, we can use the Failover Partner= element in the connection string – but this is transactional replication being used for DR, and failover should be manual
  • We could set the instance names to be the same – however in this case it was too late since changing the instance name cleanly in SQL Server is practically impossible and breaks all sorts of things

None of these solutions was suitable.  However, on each server, there was only one named instance and no default instance.  What can we do?

Read on for a fourth solution.

Comments closed

Pausing Versus Stopping SQL Servers

Published 2017-10-03 by Kevin Feasel

Arun Sirpal demonstrates what the Pause option does on a SQL Server:

As stated via official Microsoft documentation “Pausing the Database Engine service prevents new users from connecting to the Database Engine, but users who are already connected can continue to work until their connections are broken. Use pause when you want to wait for users to complete work before you stop the service. This enables them to complete transactions that are in progress”.

Very handy! Let’s see it in action and compare it to a STOP.

Read on for more.

Comments closed

Regular Expressions With R

Published 2017-10-03 by Kevin Feasel

Dave Mason looks at using SQL Server R Services to execute regular expressions against a T-SQL data set:

Have you ever had the need to use Regular Expressions directly in SQL Server? I sometimes hear or see others refer to using RegEx in TSQL. But I always assume they’re talking about the TSQL LIKE operator, because RegEx isn’t natively supported. In TSQL’s defence, you can get a lot of mileage out of LIKE and some clever pattern matching strings, even though it’s not authentic RegEx. You can leverage RegEx libraries in the .NET Framework via a CLR stored procedure. You should also be able to do something similar with an old-school extended stored procedure.

I discussed all of this during a recent interview. It was a day or two afterwards (of course) when it dawned on me that there’s another way to leverage RegEx from TSQL: the R language. Prior to this mini-revelation, I had always thought of R (and Python) as strictly a means to an end for Data Science and related disciplines. Now I am thinking I’ve been looking at R and Python through too narrow of a lens and I should take a larger view.

I think I’d prefer CLR for this because there’s additional overhead to making R Services calls, but it’s a clever use of R Services.

Comments closed

Changes To SQL Server’s Servicing Model

Published 2017-10-03 by Kevin Feasel

Pedro Lopes announces changes to SQL Server’s servicing model:

Starting with SQL Server 2017, we are adopting a simplified, predictable mainstream servicing lifecycle:

  • SPs will no longer be made available. Only CUs, and GDRs when needed.
  • CUs will now accommodate localized content, allowing new feature completeness and supportability enhancements to be delivered faster.
  • CUs will be delivered more often at first and then less frequently. Every month for the first 12 months, and every quarter for the remainder 4 years of the full 5-year mainstream lifecycle.
  • CUs are delivered on the same week of the month: week of 3rd Tuesday.

Note: the Modern Servicing Model (MSM) only applies to SQL Server 2017 and future versions.

If you’re the type who waits for SP1 to drop, you’ll be waiting for Godot.  Who should be here any minute now.

Comments closed

SQL Server 2017 Now Generally Available

Published 2017-10-03 by Kevin Feasel

Travis Wright announces that SQL Server 2017 has gone GA:

Today, October 2nd, we are excited to announce that SQL Server 2017 is generally available for purchase and download! The new release is available right now for evaluation or purchase through the Microsoft Store, and will be available to Volume Licensing customers later today. Customers now have the flexibility for the first time ever to run industry-leading SQL Server on their choice of Linux, Docker Enterprise Edition-certified containers and, of course, Windows Server. It’s a stride forward for our modern and hybrid data platform across on-premises and cloud.

In the 18 months since announcing our intent to bring SQL Server to Linux, we’ve been focused on making SQL Server perform and scale to the industry-leading levels customers expect from SQL Server, making SQL Server feel familiar yet native to Linux, and ensuring compatibility between SQL Server on Windows and Linux. With all the enterprise database features you rely on, from Active Directory authentication, to encryption, to Always On availability groups, to record-breaking performance, SQL Server is at parity on Windows and Linux. We have also brought SQL Server Integration Services to Linux so that you can perform data integration just like on Windows. SQL Server 2017 supports Red Hat Enterprise Linux, SUSE Linux Enterprise Server, and Ubuntu.

It’s only been a year, but there’s a lot in this new version.  Click through for a high-level summary.

Comments closed

Using Kafka To Drive Machine Learning

Published 2017-10-02 by Kevin Feasel

Kai Waehner has a nice architectural post on using Kafka as the focal point for machine learning training and prediction:

The essence of this architecture is that it uses Kafka as an intermediary between the various data sources from which feature data is collected, the model building environment where the model is fit, and the production application that serves predictions.

Feature data is pulled into Kafka from the various apps and databases that host it. This data is used to build models. The environment for this will vary based on the skills and preferred toolset of the team. The model building could be a data warehouse, a big data environment like Spark or Hadoop, or a simple server running python scripts. The model can be published where the production app that gets the same model parameters can apply it to incoming examples (perhaps using Kafka Streams to help index the feature data for easy usage on demand). The production app can either receive data from Kafka as a pipeline or even be a Kafka Streams application itself.

This is approximately 80% of my interests wrapped up in one post, so of course I’m going to read it…

Comments closed

Non-Cost-Based Optimizations In Relational Databases

Published 2017-10-02 by Kevin Feasel

Lukas Eder has a big article on ten query optimizations that don’t involve looking at statistics or query costs:

This optimisation is really silly, but hey, why not. If users write impossible predicates, then why even execute them? Here are some examples:

-- "Obvious"
SELECT * FROM actor WHERE 1 = 0

-- "Subtle"
SELECT * FROM actor WHERE NULL = NULL

The first query should obviously never return any results, but the same is true for the second one, because while NULL IS NULL yields TRUE, always, NULL = NULL evaluates to NULL, which has the same effect as FALSE according to three-valued logic.

This doesn’t need much explanation, so let’s immediately jump to see which databases optimise this:

I was a bit surprised at how well DB2 did in this set.

Comments closed

SQL Server R Services Troubleshooting

Published 2017-10-02 by Kevin Feasel

Ginger Grant walks us through a few troubleshooting tactics with SQL Server 2016 R Services:

Much to my surprise after this I received an error

Msg 39019, Level 16, State 1, Line 1
An external script error occurred:
Unable to launch the runtime. ErrorCode 0x80070490: 1168(Element not found.).
Msg 11536, Level 16, State 1, Line 1
EXECUTE statement failed because its WITH RESULT SETS clause specified 1 result set(s), but the statement only sent 0 result set(s) at run time.

I looked in the log files and didn’t find any errors.  I checked the configuration manager to ensure that I had some user ids configured in the configuration manager.  Nothing seemed to make any difference.  Looking online, the only error that I saw which might possibly be close was a different error message about 8.3 naming and the working directory.

This service is somewhat finicky to set up in my experience, though once you have it configured, it tends to be pretty stable.

Comments closed