The Bit Bucket - Page 2 of 113 - Thoughts from Data Platform MVP and Microsoft RD

Snowflake SQL for SQL Server Users – Sequences: The good and the not so good

I've been working to convert our SQL Down Under sample databases like PopkornKraze across to Snowflake. One of the areas that I ran into real challenges with was sequences.

The good news

The syntax for creating and accessing them is basic enough.

CREATE SEQUENCE "SequenceName";

1	CREATE SEQUENCE "SequenceName";

is enough. (Note the double-quotes are mine as I don't want the name auto-wrapped to SEQUENCENAME).

And there are some of the same options that SQL Server has:

START WITH = 1 (you can even leave out the equals sign)
INCREMENT BY = 1 (again the equals is optional)
COMMENT = 'some comment'

Snowflake syntax also supports IDENTITY(seed, increment) if you want to define that at a table level. That is like a single table sequence but might help in migrating SQL Server code.

What I do really like are the options for replacing or only creating if not exists:

CREATE OR REPLACE SEQUENCE "SequenceName";

CREATE SEQUENCE "SequenceName" IF NOT EXISTS;

CREATE OR REPLACE SEQUENCE "SequenceName";

CREATE SEQUENCE "SequenceName" IF NOT EXISTS;

We can't do that in SQL Server today. (I've complained to the SQL Server team that these types of options don't exist for sequences).

Getting the next value for a sequence is easy enough:

SELECT "SequenceName".NEXTVAL;

1	SELECT "SequenceName".NEXTVAL;

Like SQL Server, there's no guarantee that you won't have gaps in your numbers that are allocated.

There is a table-level function called GETNEXTVAL() that takes the sequence name as a parameter. It's a one row table function that generates a unique value. It's used for precise control of sequence generation when many tables are involved.

The not so good news

First up, there's no concept of anything like currval in Oracle (current value), or @@IDENTITY, or SCOPE_IDENTITY() in SQL Server. The documentation argues that this is an intentional omission as it encourages row by row coding. Can't say I buy that argument.

The biggest challenge (by far) is that you can't reset the next value to be issued by either an IDENTITY or Sequence. This really needs to get fixed. Lots of people are complaining about it in the Snowflake Lodge (user forums) and it just isn't a good thing. Here's an example:

You define a table using an IDENTITY column or a Sequence, let's say for a primary key.
You need to load existing data into it. This works easily as there is no concept of something like SET IDENTITY INSERT ON/OFF. You can just insert.
Next row that gets added to the table will now probably have a duplicate primary key value added.
And as Snowflake also doesn't check primary keys (or most other constraints), you'll really just end up with multiple rows with the same primary key.

ALTER SEQUENCE does allow you to change the increment, but won't let you change the next value to be issued. (Or the seed).

You have to drop and recreate the sequence. And now here's the even-nastier part: if you drop the existing sequence, it doesn't stop you doing it, but the column that was using it still references it. So even when you recreate a sequence with the same name, your next insert will blow up with an object not found. (Again, people complaining about this in the Snowflake lodge).

This means that you can't add sequences as defaults to tables until you've finished loading all your data. That's just not sensible, and breaks all the rules about separating DDL and data. The DDL that you write for a table should not be dependent upon what data happens to be loaded into a table.

How could you then write DDL that's used in multiple places? (Unless you just always start with a gigantic seed value).

Opinion

This aspect of the product needs to be fixed. It's hard to believe it's so broken. Wish it wasn't so.

SQL: Passing different data types to a single stored procedure parameter

There was a recent question on the forums about if there was any way to create a sql parameter with defined type in typescript fashion. What they were after was something like this:

create procedure something
   @myparam 'int' | 'varchar(20)'
as
  ...

create procedure something

@myparam 'int' | 'varchar(20)'

...

They wanted a single parameter that could have different data types, but only from a specific list of data types.

SQL Server doesn't support that type of construct directly, but what it does do is support the sql_variant data type. So you could have defined the procedure like this instead:

CREATE PROCEDURE something
@MyParam sql_variant
AS
...

CREATE PROCEDURE something

@MyParam sql_variant

...

That allows you to send in any data type. What's then remaining to do is to make sure it's one of the allowed data types. You can do that by querying the SQL_VARIANT_PROPERTY function.

As an example, you can find the underlying data type like this:

DECLARE @DataType sysname = SQL_VARIANT_PROPERTY(@MyParam, 'BaseType');

1	DECLARE @DataType sysname = SQL_VARIANT_PROPERTY(@MyParam, 'BaseType');

And then just add logic to check the type name, and if correct, proceed onto casting the values.

SDU Tools: Version 17 shipped recently to SDU Insiders

Version 17 of our SDU Tools shipped out to all our SDU Insiders a few days ago.

It contains the following updates:

DatesInPeriod – Produces a table of dates from a starting date, for a number of intervals. For example, 3 weeks of dates from a starting date.

ServerMaximumDBCompatibilityLevel – Returns the maximum database compatibility level supported by the server.

SetDatabaseCompabilityForAllDatabasesToMaximum – Sets the database compatibility level of all databases to the maximum value supported by the server.

WeekdayOfMonth – Calculates a specific weekday of a month. For example, the 4th weekday of February 2020.

DayNumberOfMonth – Calculates a specific day within a month. For example, the 3rd Tuesday of March 2020.

Countries – A brand new view that provides details of countries, along with their ISO codes, and numbers, and the continent that they're part of.

Currencies – A new view that provides details of the currencies in use around the world, their ISO codes, their currency symbols, and details of any minor units (like Cents are to Dollars in AUD or USD). If there are minor units, it tells you how many per major unit.

CurrenciesByCountry – A new view that provides a list of which countries use which currencies. (Some use more than one).

I hope you find them useful.

If you don't get our notifications and you'd like a copy of the tools (free to our insiders), you'll find more info here:

http://sdutools.sqldownunder.com

Book Review: TED Talks – The Official Ted Guide to Public Speaking

I do a lot of speaking at conferences, user groups, online, and other sorts of events. It's important to constantly improve, so I take notice of any books released on the topic. I recently listened (via Audible) to TED Talks – The Official Ted Guide to Public Speaking by Chris Anderson.

I've been a fan of TED and their conference talks for a long time. The overall quality of the talks is very high, and some (like Ken Robinson's talk on schools killing creativity) set a really high bar.

TEDx talks are the extension talks. They are local events and the quality isn't quite at the same level, but still often very, very good.

So I was fascinated to see this book was by Chris Anderson. Chris is basically the guy who runs TED nowadays. He took it over from the original founder many years back, and has seen it go to great heights. I was keen to hear Chris' thoughts. If anyone has listened to a lot of presentations, it's Chris.

A real bonus was that he narrated the audio book that I listened to.

The book was a little slow getting started, almost to the point where I was going to give up, but I'm so glad I persisted. It's a truly wonderful book. If you don't gain insights from Chris' thoughts, you aren't trying.

I was also quite fascinated in some of the background info about TED, and how involved Chris and the team are with each presentation. They really help to improve the final product that you see. Not surprisingly too, there are sessions that just don't make it out the door.

The Financial Times review of the book said "Excellent; easily the best public speaking guide I have read".

Chris describes his five key techniques: Connection, Narration, Explanation, Persuasion and Revelation. He then goes on with additional ones to avoid. The book is really well constructed, and Chris is the ideal person to have written it.

Bottom line?

I really enjoyed this book. If you don't find the start of it all that compelling, I'd encourage you to keep at it. It's worth it. If you have any interest at all in public speaking, this is a wonderful book.

Greg's rating: 8 out of 10

T-SQL 101: #51 Splitting delimited strings in SQL Server by using STRING_SPLIT

For a long, long time, users of SQL Server had requested some way to split a string. That's a common need when working with rows from comma-delimited files (CSVs).

In the example below, I've asked it to break up the string 'Greg,Tom',Sandra' whenever it finds a comma. Notice I could use another delimiter like TAB or semicolon instead.

The values returned are in a table. This is a table-valued function.

The downside of this function is that it doesn't promise to return the rows in order (as tables don't have a default order), and it doesn't return a column that lets us know what order the columns were in. So it can be a bit awkward to work with.

If you want a function that works better than this, in our free SDU Tools for Developers and DBAs, we have a SplitStringByDelimiter function that does just that. I suggest you take a look at it.

Learning T-SQL

It's worth your while becoming proficient in SQL. If you'd like to learn a lot about T-SQL in a hurry, our Writing T-SQL Queries for SQL Server course is online, on-demand, and low cost.

SQL: Retrieve the results of executing a stored procedure in T-SQL

There was a recent question asked on Stack Overflow. The poster asked:

"I have this stored procedure and I need to fetch result of select query as output:"

USE abc
GO 

SET ANSI_NULLS ON 
SET QUOTED_IDENTIFIER ON 
GO 

CREATE OR ALTER PROCEDURE ABC_XYZ_LOAD 
AS 
BEGIN  
    SELECT * FROM [CNT_01] 
END 
GO

USE abc

SET ANSI_NULLS ON

SET QUOTED_IDENTIFIER ON

CREATE OR ALTER PROCEDURE ABC_XYZ_LOAD

BEGIN

SELECT * FROM [CNT_01]

END

Now, it always annoys me when the Stack Overflow rule enforcers decide that there wasn't enough info there and close it. (Particularly when it was a new contributor).

He really was asking a pretty common question about how you execute a stored procedure, and get access to the output of the procedure within the T-SQL code. People hope you can write code like this:

SELECT columns FROM (EXEC ABC_ZYZ_LOAD);

1	SELECT columns FROM (EXEC ABC_ZYZ_LOAD);

But that isn't valid syntax.

What you need to do

What you need to do instead is to create a table variable (or temp table) to hold the output, and to then use an INSERT … EXEC to get the values. You also want to avoid having an asterisk (*) in the SELECT clause in the procedure. Be explicit about what's being returned.

Here's an example:

The procedure sp_lock returns some locking info. I've shown it executed in the main image above. If we need to use the data that it returns in a T-SQL query, we just do this:

DECLARE @Locks TABLE
(
    [spid] int NULL,
    [dbid] int NULL,
    [ObjId] int NULL,
    [IndId] int NULL,
    [Type] sysname NULL,
    [Resource] sysname NULL,
    [Mode] nvarchar(max) NULL,
    [Status] nvarchar(max) NULL
);

INSERT @Locks 
(
  [spid], [dbid], [ObjId], [IndId],
  [Type], [Resource], [Mode], [Status]
)
EXEC sp_lock;

SELECT * FROM @Locks;

DECLARE @Locks TABLE

(

[spid] int NULL,

[dbid] int NULL,

[ObjId] int NULL,

[IndId] int NULL,

[Type] sysname NULL,

[Resource] sysname NULL,

[Mode] nvarchar(max) NULL,

[Status] nvarchar(max) NULL

);

INSERT @Locks

(

[spid], [dbid], [ObjId], [IndId],

[Type], [Resource], [Mode], [Status]

)

EXEC sp_lock;

SELECT * FROM @Locks;

And the output from the last SELECT statement is as expected:

Now I selected it at the end, but of course you don't need to do that. You could then just use the @Locks table variable in the rest of your code.

Note also: I used the column names that the stored procedure normally returns. Can't say I love them. You could have better names in the table variable.

Snowflake SQL for SQL Server Users – SQL Language Differences

I posted last time that I'd completed my Part 1 eBook on Core Concepts. The Part 2 eBook is on the differences in the SQL language between Snowflake and SQL Server.

This next series of blog posts will focus on the query language, particularly on DML (data manipulation language). DDL (data definition language i.e. CREATE, ALTER, DROP) differences will be covered in the Part 3 series on administration.

Snowflake has quite a rich SQL language but it's quite different to the T-SQL offered by SQL Server in many ways. I'm planning to work right through the Snowflake SQL language documentation and try everything in it, so you don't have to.

In this series of posts though, I'll look primarily at what's different, not on what's the same. (Detailed coverage of the language itself will be in our upcoming online course on Snowflake SQL. It's being built now).

This post will serve as an index to the other posts in this series.

You'll find the other posts in the list below:

Part 1: Sequences: The good and the not so good

SQL: Find nearest enabled parent in a hierarchy using T-SQL

I was recently answering a forum question about working with a hierarchy. The poster had a table of nodes but importantly, nodes could be disabled. If that happened, the children of the node would move up to their nearest parent that wasn't disabled.

That is, if their parent was disabled and their grandparent was also disabled, then their great-grandparent would become their effective parent.

I provided the following code example, and I hope it helps someone.

USE tempdb;
GO

DROP FUNCTION IF EXISTS dbo.GetParentNode;
GO

CREATE FUNCTION dbo.GetParentNode
( 
    @NodePath varchar(max)
)
RETURNS int
AS 
BEGIN
    /*
        SELECT dbo.GetParentNode('12/13/14');
        SELECT dbo.GetParentNode('12/14');
    */

    DECLARE @ReturnValue int;
    DECLARE @StringToProcess varchar(max) = REVERSE(@NodePath);
    DECLARE @DelimiterLocation int;

    SET @DelimiterLocation = CHARINDEX('/', @StringToProcess);

    IF @DelimiterLocation > 0
    BEGIN
        SET @StringToProcess 
           = SUBSTRING(@StringToProcess, @DelimiterLocation + 1, 
                       LEN(@StringToProcess));
        SET @DelimiterLocation = CHARINDEX('/', @StringToProcess);

        IF @DelimiterLocation = 0
        BEGIN
            SET @ReturnValue = CAST(REVERSE(@StringToProcess) AS int);
        END ELSE BEGIN
            SET @ReturnValue 
              = CAST(REVERSE(LEFT(@StringToProcess, @DelimiterLocation - 1)) 
                AS int);
        END;
    END;

    RETURN @ReturnValue;   
END;
GO

DROP TABLE IF EXISTS dbo.Nodes;
GO

CREATE TABLE dbo.Nodes
(
     NodeID int, 
     ParentNodeID int, 
     NodeName nvarchar(20),   
     IsDisabled bit
);

INSERT dbo.Nodes 
(
    NodeID, ParentNodeID, NodeName, IsDisabled
)
VALUES (1, NULL, 'node-1', 0),
       (2, 1, 'node-2', 1),
       (3, 1, 'node-3', 0),
       (4, 2, 'node-4', 0),
       (5, 2, 'node-5', 0),
       (6, 3, 'node-6', 0),
       (7, 4, 'node-6', 0),
       (8, 4, 'node-6', 0);

WITH AllNodes AS
(
    SELECT toplevel.NodeID, 
           toplevel.IsDisabled,
           toplevel.ParentNodeID, 
           1 AS NodeLevel, 
           toplevel.NodeName, 
           CAST(toplevel.NodeID AS varchar(max)) AS NodePath
    FROM dbo.Nodes AS toplevel 
    WHERE toplevel.NodeID = 1

    UNION ALL

    SELECT 
        n.NodeID, 
        n.IsDisabled, 
        n.ParentNodeID, 
        an.NodeLevel + 1, 
        n.NodeName,
        an.NodePath + CASE WHEN n.IsDisabled = 0 
                           THEN '/' + CAST(n.NodeID AS varchar(max))
                           ELSE ''
                      END
    FROM dbo.Nodes AS n
    INNER JOIN AllNodes AS an 
    ON an.NodeID = n.ParentNodeID
)
SELECT an.NodeID, an.IsDisabled, an.NodeName,
       an.ParentNodeID, an.NodeLevel, an.NodePath,
       dbo.GetParentNode(an.NodePath) AS TrueParentNodeID
FROM AllNodes AS an
WHERE an.IsDisabled = 0;

DROP TABLE dbo.Nodes;
GO

100

101

102

103

104

USE tempdb;

DROP FUNCTION IF EXISTS dbo.GetParentNode;

CREATE FUNCTION dbo.GetParentNode

(

@NodePath varchar(max)

)

RETURNS int

BEGIN

SELECT dbo.GetParentNode('12/13/14');

SELECT dbo.GetParentNode('12/14');

DECLARE @ReturnValue int;

DECLARE @StringToProcess varchar(max) = REVERSE(@NodePath);

DECLARE @DelimiterLocation int;

SET @DelimiterLocation = CHARINDEX('/', @StringToProcess);

IF @DelimiterLocation > 0

BEGIN

SET @StringToProcess

= SUBSTRING(@StringToProcess, @DelimiterLocation + 1,

LEN(@StringToProcess));

SET @DelimiterLocation = CHARINDEX('/', @StringToProcess);

IF @DelimiterLocation = 0

BEGIN

SET @ReturnValue = CAST(REVERSE(@StringToProcess) AS int);

END ELSE BEGIN

SET @ReturnValue

= CAST(REVERSE(LEFT(@StringToProcess, @DelimiterLocation - 1))

AS int);

END;

RETURN @ReturnValue;

END;

DROP TABLE IF EXISTS dbo.Nodes;

CREATE TABLE dbo.Nodes

(

NodeID int,

ParentNodeID int,

NodeName nvarchar(20),

IsDisabled bit

);

INSERT dbo.Nodes

(

NodeID, ParentNodeID, NodeName, IsDisabled

)

VALUES (1, NULL, 'node-1', 0),

(2, 1, 'node-2', 1),

(3, 1, 'node-3', 0),

(4, 2, 'node-4', 0),

(5, 2, 'node-5', 0),

(6, 3, 'node-6', 0),

(7, 4, 'node-6', 0),

(8, 4, 'node-6', 0);

WITH AllNodes AS

(

SELECT toplevel.NodeID,

toplevel.IsDisabled,

toplevel.ParentNodeID,

1 AS NodeLevel,

toplevel.NodeName,

CAST(toplevel.NodeID AS varchar(max)) AS NodePath

FROM dbo.Nodes AS toplevel

WHERE toplevel.NodeID = 1

UNION ALL

SELECT

n.NodeID,

n.IsDisabled,

n.ParentNodeID,

an.NodeLevel + 1,

n.NodeName,

an.NodePath + CASE WHEN n.IsDisabled = 0

THEN '/' + CAST(n.NodeID AS varchar(max))

ELSE ''

END

FROM dbo.Nodes AS n

INNER JOIN AllNodes AS an

ON an.NodeID = n.ParentNodeID

)

SELECT an.NodeID, an.IsDisabled, an.NodeName,

an.ParentNodeID, an.NodeLevel, an.NodePath,

dbo.GetParentNode(an.NodePath) AS TrueParentNodeID

FROM AllNodes AS an

WHERE an.IsDisabled = 0;

DROP TABLE dbo.Nodes;

The approach I took was to use a recursive CTE to traverse the nodes. As I passed through each node level, I'd add to a NodePath only if the node wasn't disabled. That way, I built up a NodePath of just the active nodes leading to the particular node.

To make it easier to then extract the parent node from the path, I added a function to do that. It reversed the string to make it easy to start at the end and find the nodes. It discarded the first node (the current node), then found the next node, reversed the string again, and returned it as an int.

You should be able to copy/paste the whole code and try it. I hope it helps someone or gives you some ideas.

BI: Wondering where you Integration Services Connector for Power Query has gone in Visual Studio 2019?

I wrote recently about menu items being missing from SSIS and other BI projects in VS2019, but another thing that's gone MIA is the Integration Services Connector for Power Query.

This is the connector that allows you to use Power Query (and the M language) to input data into SSIS.

The SSIS designer for VS2019 recently went GA (general availability). It's great that it's released, but if you install it, you'll find it removes the Power Query option for SSIS.

I imagine the reason for that is that the connector is still in preview, where the rest of the designer is now GA.

To get the functionality back, you need to separately download the Integration Services Connector for Power Query. You'll find both 2017 and 2019 versions of it here:

https://www.microsoft.com/en-us/download/details.aspx?id=100619

BI: Wondering why your SSRS, SSAS, and SSIS menus are missing in Visual Studio 2019?

If you've recently installed Visual Studio 2019 (VS2019), and then installed the SQL Server Reporting Services (SSRS), the SQL Server Analysis Services (SSAS), and/or SQL Server Integration Services (SSIS) extensions, when you open a project you might be wondering where your menus went.

How it was

For example, if you had a report open in the VS2017 report designer, this menu was present:

And if you had an AS model open in the VS2017 tabular designer, these menus were present:

And similarly, if you had an SSIS package open in the VS2017 designer, these menus were present:

How it now is

Now, when you open VS2019, no matter which of these you have open, you won't find those menus.

So where are they?

They're all hidden within the "EXTENSIONS" menu as these designers are all "Extensions". And if there were multiple menus, they're all in there separately:

Verdict?

I think this is a lousy idea for multiple reasons.

First, it means that when you're working on an object like a tabular data model, your menu bar is full of things unrelated to what you're working on, and the relevant menu items are hidden multiple levels down under "Extensions".

While I understand they're probably trying to keep the VS menu bar consistent, many people working with these projects could not care less about the rest of VS. That's just where these designers happen to be.

For the life of me, I can't see how that's good UI design.

Second, it makes the decades of blog posts and articles that have been written about these tools incorrect, and much harder to follow. The pain from this is hard to describe, and it's felt most by newcomers who will just be puzzled and not know what's going on.

I'd love to hear your thoughts. If you agree with mine, please tell Microsoft as well. I really hope they reconsider this.