The Bit Bucket

One issue that comes up time and again in the forums is when an INSERT statement fails with a violation of a UNIQUE or PRIMARY KEY constraint but when the user checks the existing table, the value that’s being complained about isn’t already in the table.

The Symptom

For example, a table might have primary key values of 2, 12, and 14.

When an INSERT is performed, there is an error telling you that you can’t insert a duplicate key value of say 15.

2019-12-26

Hi folks,

2019 has been another really big year. The number of readers of this blog has really increased in the last year and I’m pleased you’re here as one of them.  You make it all worthwhile.

Thanks to so many who reached out during the year, with wonderful insights and ideas.

Just wanted to wish you all the best for the holiday and Christmas season, from here down under.

2019-12-25

REPLICATE is an easy function to work with. It takes a string and a number of times you want it repeated. Here’s an example:

In this case, I’ve asked it to replicate (i.e. duplicate many times) an A character. I’ve asked for 5 of them. I put an X at each end so you could see the effect more clearly.

There are many things this can be used for, but for example, if I wanted to draw a line with 100 dashes, I could just ask for REPLICATE(’-’, 100).

2019-12-23

As with most products today, Snowflake has substantial handling of encryption to protect client data.

All editions claim to provide “Always-on enterprise grade encryption in transit and at rest”.

In Transit

First, for connections, HTTPS and TLS 1.2 are used throughout the system.

If the customer is using external stages (holding locations in cloud-storage accounts), the data can be encrypted while stored in the stage, and then travel encrypted into Snowflake. To do that, you create an “encrypted stage” where you tell Snowflake the master encryption key (i.e. the client side key) when creating the stage:

2019-12-20

As part of our free SDU Tools for developers and DBAs, we have many scripting functions. SQL Server allows you to create your own server roles, and generally, you should do this instead of using the fixed server roles, as it lets you allocate just the required permissions. To allow scripting the permissions that have been assigned to the roles, we’ve added a tool called ScriptUserDefinedServerRolePermissions.

It’s a function and doesn’t require any parameters.

2019-12-18

It’s that time of year where people give each other presents. Why not give yourself a present and learn something new while you’ve got a chance?

Our latest course release is: SQL Server Integration Services for Developers and DBAs.

You’ll find it, and our other courses here now:

https://sqldownunder.com/

• 4 Steps to Faster SQL Server Applications (Free)
• Protecting SQL Server Data with Encryption
• Writing T-SQL Queries for SQL Server (Low cost)
• SQL Server Reporting Services for Developers and DBAs
• SQL Server Spatial for Developers and DBAs
• SQL Server Indexing for Developers
• SQL Server Service Broker for Developers and DBAs
• SQL Server Service Broker for Developers and DBAs
• Working with SQL Server Replication

And many more coming soon.

2019-12-18

I’m pleased to announce the release of another SQL Down Under podcast.

Show 77 features Cloud and Datacenter Management MVP Rob Sewell discussing the popular Powershell dbatools.

In the show, Rob discusses the background of the tools, how they’ve evolved, how to install them, and how you can get involved in contributing to them and other open source projects.

Rob and Chrissy Lemaire (the initial author of the tools) have written a new book on the tools and you can find it here:

2019-12-17

I often need to replace characters in one string with other characters. There are three basic functions in T-SQL for doing that. The most common two functions for this are REPLACE and STUFF.

REPLACE is a simple function. You tell it which string to work on, the substring to find, and which string to replace it with. You can see it here:

I asked it to replace all occurrences of DEF with HELLO. Note that the replacement doesn’t need to be the same length as what it’s replacing. A very common use of REPLACE is to remove characters by making the replacement string just a blank string.

2019-12-16

In the previous post in this series, I talked about how historical data in Snowflake can be accessed by a concept called Time Travel. But Time Travel isn’t the only way to get to historical data.

In Snowflake, Fail-safe is a system that provides protection against system failures and other nasty events like hardware failures or serious security breaches.

Immediately after the Time Travel retention period ends, a Fail-safe period begins.  It lasts for a non-configurable period of seven days.

2019-12-13

I had a forum question recently that asked if indexed views get updated when the base table gets updated, but the columns that are part of the view are unchanged.

The easiest way to check this is to try it. Let’s start with this code to create a table and put some data in it:

USE tempdb;
GO

DROP VIEW IF EXISTS dbo.TestValueOnly;
GO

DROP TABLE IF EXISTS dbo.Test;
GO

CREATE TABLE dbo.Test
(
    TestID int IDENTITY(1,1) NOT NULL 
        CONSTRAINT PK_dbo_Test PRIMARY KEY,
    TestName varchar(50) NOT NULL,
    ValueToChange int NOT NULL
);
GO

INSERT dbo.Test (TestName, ValueToChange)
VALUES ('Hello', 12),
       ('There', 14),
       ('And', 16),
       ('Again', 18);
GO

Then we’ll create a schema bound view on the table, and a clustered index on the view:

2019-12-12