Power Query M Primer (part 3):
Functions: Function Values, Passing, Returning, Defining Inline, Recursion

• , , , ,

Not only can you define and invoke functions (as we covered in part 2), you can also pass them around. The ability to pass a function around without invoking it gives lots of flexibility.

Sounds complex? Yes, in words, but not necessarily in practice. If you’ve touched the Power Query M language, you’ve probably already passed functions around—just perhaps without realizing about it.

Then there’s the mysterious each. It shows up a lot in code generated by the query editor. What does it actually mean or do? Turns out, it’s a handy shortcut that can simplify code you write.

We have ground to cover. Let’s get going!
Continue reading →

Power Query M Primer (part 2):
Functions: Defining

• , , , ,

If you read part 1 in this series, you may have picked up a theme about expressions that produce values: A simple statement, like 1, is an expression that produces a value. let is also an expression that produces a value.

Guess what? A function is an expression that ultimately produces a value. Unlike the expressions we looked at last time, a function only produces this value when it’s invoked.

Usually, parameters are passed to a function when it’s invoked. The function can reference these inputs as it computes what it will return. Continue reading →

Lightning Talk: SELECT Doesn’t Always Return the Expected Number of Columns

• , ,

Last night, I had the privilege of sharing a lightning talk at the OKC SQL Server User Group (OKCSQL). This 17-minute presentation touches on four scenarios where the way columns are returned by SELECT might not match what you expect.

When you write SELECT statements, you probably have expectations like:

  • SELECT * returns all columns in the referenced object(s).
  • Each column is returned in a separate column.
  • Each column returned in visible in the result set.

However—at least in the world of Microsoft SQL Server/Transact SQL (T-SQL)—these assumptions aren’t always true.


Continue reading →

Power Query M Primer (part 1):
Introduction, Simple Expressions & let

• , , ,

Sooner or later, you may find yourself working directly with the Power Query M formula language. Tools like Microsoft Excel’s Get & Transform Data and Microsoft Power BI provide point-and-click interfaces allowing you to build data transformation sequences which behind-the-scenes are implemented in Power Query M. However, these query editors can’t do everything you might need. Sometimes direct editing and authoring of M is required.

Search the Internet and you’ll find many examples showing how to use this language to solve one problem or another but little is out there describing the syntax and paradigm of the language itself. A limited knowledge of these details may be insignificant when simply copying and pasting samples and editing variable and column names. Move beyond this to weaving and writing your own solutions directly in Power Query M and a solid understanding of the language’s syntax, rules and capabilities becomes most helpful.
Continue reading →

Reflections on SOLID Principles

• , ,

Object-oriented is a methodology that uses a language feature known as objects as its fundamental unit of expression. The acronym SOLID identifies foundational principles differentiating between an object-oriented design and a design that simply uses objects.

#ObjectOriented uses #Objects but using #Objects isn’t always #ObjectOriented.
– #Programming #Architecture #ObjectOrientedDesign – @bgribaudo (original Tweet)

Recently, I read “Uncle Bob” (Robert C.) Martin’s exposition of the SOLID in his book Agile Software Development, Principles, Patterns, and Practices (Pearson, 2002, ISBN: 978-0135974445). Below is a combination of notes from his explanation and thoughts of my own that came as I contemplated what Uncle Bob wrote. Continue reading →

Python for Non-Data Analytics?

• , , ,

Microsoft SQL Server 2017’s support for Python had me curious. Would this integration make it practical to use Python in non-data analytic scenarios where a general-purpose programming language—like Python—is more suitable than set-based T-SQL?

The answer to this question hinges around the integration’s design. Presumably, the new Python integration is designed to work well for data analysis, as this seems to be the prime impetus motivating it. An integration that excels in one scenario, like the assemble data -> process & analyze -> return results flow common in data analytics, may or may not be a good fit for other use cases.

To help us evaluate when and where SQL Server’s Python support may be helpful for non-data analytics applications, let’s compare it—or, more specifically, let’s compare the underlying external script execution environment that powers it—with two integrations that have been included with SQL Server for some time: Common Language Runtime (CLR) and xp_cmdshell.
Continue reading →

Passing a String Where It Isn’t Expected:
Exploring the Implicit Conversion Alternative

• , ,

Let’s say you maintain a class whose constructor expects a configuration object:

class MyDbConnection {
  public MyDbConnection(MyDbConfiguration config) { … }
}
...
var config = new MyDbConfiguration { Server = "SuperFastDbServer", User = "jsmith", Password = … };
var connection = new MyDbConnection(config);

Along the way, developers asked for a simple, textual way to set configuration options. To accommodate this, you gave the configuration class a constructor that accepts a settings string as its argument:

var connection = new MyDbConnection(new MyDbConfiguration("server=SuperFastDbServer;user=jsmith;password=…"));

Now, you’ve received a request to further streamline usage by allowing the configuration string to be passed directly to the main class, bypassing the need to reference the settings class:

var connection = new MyDbConnection("server=SuperFastDbServer;user=jsmith;password=…");

Since the goal is to construct instances of the main class by passing a string, it seems the way to implement this request is to give the main class a constructor that accepts a string as its argument.

Surprisingly, adding a constructor isn’t the only way to achieve the desired effect! In fact, it’s possible to satisfy this request without any modifications to the main class. Likely, you’ve already used the functionality that makes this possible—though perhaps without realizing you could use it with classes and structs you create.

However, there is a philosophical question about the appropriateness of applying this technique in this scenario. We’ll touch on this question later. Even if you decide against using the technique in this case, knowing about it hopefully will come in handy down the road when you encounter other, unquestionably appropriate situations where it can be used. Continue reading →

Security Concern: Hidden Resultset Columns

• ,

The resultset grid shows all columns sent by the server, right? Not always! Under certain circumstances, Microsoft® SQL Server® transmits columns that weren’t referenced in the SELECT statement and that don’t show up in tools like SSMS.

How many columns does the below view return? Four seems like the obvious answer but it’s only sometimes correct. This view sometimes returns four columns—and other times returns nine!

CREATE VIEW dbo.EmployeeSummary AS
SELECT
  e.FirstName,
  e.LastName,
  dl1.MonthName + ' ' + dl1.DayString AS Birthday,
  dl2.YearString AS EmployedSince
FROM Employee e
  JOIN UserAccount u ON e.UserName = u.UserName
  JOIN EmploymentHistory h ON e.SSN = h.SSN
  JOIN DateLookup dl1 ON h.HireDate = dl1.Date
  JOIN DateLookup dl2 ON u.Birthdate = dl2.Date;

The extra five columns (shaded in yellow, below) are returned as hidden columns. They won’t appear in most query tools, yet the data they contain still crosses the wire and can be accessed programmatically on the client-side.

Resultset Continue reading →

Why You Can’t Directly Bulk Load Values
or See Them in Traces

• ,

Bulk loading seems shrouded in mystery. You can’t directly bulk insert values using a query. However, you can bulk load using a tool like BCP, an API like SqlBulkCopy or via a query that tells SQL Server to read the rows out of a file and insert them (BULK INSERT or OPENROWSET(BULK…)). Then, if you use one of these means to do a bulk load and watch server activity using a Profiler trace or extended events, you’ll see an insert query but no row data. What’s going on?! Continue reading →