I have to admit, I’ve been pleased with the developments with SQL Server 2016. In addition to all of the new features, they brought SQL Server to Linux and with SP1 they opened all the goodies up to Standard Edition. So when Brent Ozar asked us to scour Microsoft Connect for even more improvements for T-SQL Tuesday #86 (#tsql2sday), I wondered if we wouldn’t tempt fate. But as IT folks we love to complain about our tools, so here goes.

Now I’ve never been impressed with the Connect site. For years, it’s been clunky and slow. (Though it seems speedy today. Perhaps Brent’s focus brought some attention to the performance issues.) Its dated style hasn’t been updated since I started using it years ago. It has a poor mobile user experience, and it has an odd display quirk where sometimes the description gets cut off. Perhaps recognizing its deficiencies, Microsoft actually suggested to Chrissy LeMaire that Trello be used to organize community requests about SSMS, so she set up a SQL Server Management Studio 2016 Enhancements board which has over 50 community contributors, including a Microsoft developer, David Shiflet.

And Connect has a problem with duplication. There’s no stopping we humans from creating duplicate requests, but Microsoft needs to do a better job of (1) detecting the duplication using human review; (2) closing the new issue as a duplicate; (3) officially marking a particular other issue as the official version; (4) making the link to the new issue prominent; (5) moving all votes to the official, open issue; and (6) disallowing new votes on the closed issue. Otherwise, you get this mess (relating to the concept of adding a `CREATE OR ALTER` syntax):

• The original issue 127219 has been deleted. Issues should never be deleted.
• Issue 2558884 opened in 2016 which was closed as “Resolved / Fixed” with a Microsoft comment that it’s closed as a duplicate of issue 344991.
• Issue 351217, opened in 2008 which has a Microsoft comment that there’s “another request for the same feature, already ‘in-the-system'” and references the original, deleted issue 127219 *and* has another Microsoft comment saying it’s a duplicate and referring to 344991, but the issue is still “Active”.
• Issue 448946, opened in 2009, which has a Microsoft comment from 2010 that it was a resolved as a duplicate of the deleted issue 127219, yet it remains “Active”.
• What now appears to be the official issue 344991 which has a Microsoft comment from 2010 that it’s resolved as a duplicate of the deleted issue 127219, yet it remains “Active”.

(Kudos to Aaron Bertrand for policing this as best a user can.)

But still, Brent said Connect. And Thomas LaRock says Connect works (slowly and frustratingly). And despite being “irritated” about the process, Jonathan Kehayias also says filing bugs works. And let’s consider the SQL Server 2016 additions which might have been due to Connect:

• JSON Support – Issue 673824 (previously the top-rated issue; and still “Active” by the way)
• String Splitting – Issues 285681 and 600037, though they were both closed as Won’t Fix.
• String Aggregation (also known as grouped concatenation a la MySQL’s group_concat) – Issue 427987 and others (more duplication).
• DROP IF EXISTS – Issue 742136, closed as a duplicate of– you guessed it– the deleted Issue 127219 (see above).
• CREATE OR ALTER – See the mess above.

So even when Microsoft deems it “unlikely that we will address [a] suggestion”, sometimes they still deliver. So in hopes that this won’t be in vain, despite the same request having been dismissed in 2011, I submit:

Named Parameters for User Defined Functions without EXEC.

Using named parameters makes code easier to understand and more flexible. They provide information to the code reader. They allow us to rearrange parameters as we see fit in the calling code and the function definition. We’ve taken them for granted with stored procedures for years. But we can’t use them as easily with user-defined functions. (They can be used only when calling a scalar UDF using the EXEC statement.)

To be clear, this is using ordered parameters:

EXEC myProc 1, 2, 3;

It provides no information to the reader about what those parameters are doing. (The writer might get the benefit of IntelliSense or other IDE hints.)

This is using named parameters:

EXEC myProc @Trees = 1, @Flowers = 2, @Birds = 3;

The reader now immediately understands what a beautiful, bucolic scene we are painting.

But it doesn’t just make meaning clear, it also makes mistakes obvious (or prevents them from occurring).

Consider the following simple structure:

USE tempdb;
GO

DROP FUNCTION IF EXISTS dbo.GetOrderID;
DROP TABLE IF EXISTS OrdersTest;
GO

CREATE TABLE OrdersTest (OrderID int IDENTITY, OrderType int, Qty int, ServiceSpeed int);
GO

CREATE FUNCTION GetOrderID
(
	@OrderType int = 0,
	@ServiceSpeed int = 0,
	@Qty int = 0
)
RETURNS int AS
BEGIN
	RETURN
	(
		SELECT
			TOP 1 OrderID
		FROM		OrdersTest
		WHERE		OrderType = @OrderType
		AND			ServiceSpeed = @ServiceSpeed
		AND			Qty = @Qty
		
	);
END
GO

Now let’s do some basic interaction with it, using the EXEC statement:

DECLARE
	@OrderID int = NULL,
	@OrderType int = 1,
	@Qty int = 2,
	@ServiceSpeed int = 3;

INSERT INTO OrdersTest (OrderType, Qty, ServiceSpeed)
	VALUES (@OrderType, @Qty, @ServiceSpeed);

EXEC @OrderID = dbo.GetOrderID
	@OrderType = @OrderType,
	@Qty = @Qty,
	@ServiceSpeed = @ServiceSpeed;

SELECT @OrderID 'Using EXEC Syntax';

Assuming your OrdersTest table is clean, you’ll get the value 1 back from that final SELECT. Everything makes sense so far.

Now try this:

DECLARE
	@OrderID int = NULL,
	@OrderType int = 1,
	@Qty int = 2,
	@ServiceSpeed int = 3;

SET @OrderID = dbo.GetOrderID (@OrderType, @Qty, @ServiceSpeed);

SELECT @OrderID 'Using SET Syntax';

Now you get a NULL back from the final SELECT. What happened? If you are a careful code reviewer, you might have spotted that the function definition has the @Qty and @ServiceSpeed parameters flipped as compared to the table definition and how we’re calling the function.

But this isn’t an error. There’s no obvious indication that anything is wrong. Imagine if instead of NULL, which would probably break something, you got a different order ID back. Your program would silently continue, oblivious to what is essentially data corruption.

Using named parameters makes the order irrelevant, and it makes mistakes obvious. You wouldn’t have had much trouble finding the problem with this code, right:

EXEC @OrderID = dbo.GetOrderID
	@OrderType = @OrderType,
	@ServiceSpeed = @Qty,
	@Qty = @ServiceSpeed;

Plus, named parameters allow us to omit parameters when their default values would be fine, without having something like:

SET @OrderID = dbo.GetOrderID (default, default, default);

That in turn allows us to add optional parameters to functions, enhancing their capabilities, without adjusting all of the calling code.

So maybe we should all just use the EXEC statement when calling scalar user-defined functions. But that’s so limiting. The power of UDFs is their syntactic flexibility. The can be sprinkled among other SQL statements where they are needed, on the fly. With the EXEC statement, you have to create a separate variable to store the ID and then have a separate statement to gather the information.

Well then perhaps we should just avoid UDFs altogether. After all, we know what a bad habit they are.

But in-line table-valued user-defined functions (iTVFs) don’t have that same issue. Their power comes from their transparency to the optimizer. Their code is essentially read into the calling statement. They let you have your cake and eat it too. But the named parameter EXEC trick doesn’t work for them; they don’t return a single, assignable value. And having a separate EXEC statement would destroy their value anyway.

So in order to use iTVFs for the powerful code encapsulation tools that they are without subjecting ourselves to a vast list of unidentified parameters, the SQL Server engine must support named parameters for user-defined function calls (even when not called with EXEC).

Now, go vote it up!

Update 2018-04-10: Microsoft has discontinued Connect and migrated most content to a new feedback system. You can still vote for Named Parameters on the new system!