The Bit Bucket - Thoughts from Data Platform MVP and Microsoft RD

T-SQL 101: #48 Replacing characters in strings in SQL Server using REPLACE and STUFF

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.

The other function is STUFF. It is a bit similar but instead of telling it which substring to find, you tell it where the characters that you want to replace are. Check this example:

In this case, we've asked STUFF to remove 6 characters starting at position 3, and insert ZZ instead.

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.

Snowflake for SQL Server Users – Part 19 – Fail-Safe

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.

Fail-safe isn't intended for users to access like they do with Time Travel. Instead, Fail-safe data is held so that Snowflake support can recover data that has been lost or damaged.

What about backup?

With Snowflake, there is no concept of a standard backup. The argument is that their multi-data center and highly redundant architecture almost removes the need for backup. And the thinking is that Fail-safe removes that final risk.

The only issue I see with that logic, is the same as with most cloud-based databases and data warehouses: Users sometimes want to keep point in time backups over long periods. I don't currently see any option available for this within Snowflake. But as I mentioned, they aren't alone in that.

For an index to all posts in this series, see the first post here.

SQL: Does an indexed view always get updated for base table updates?

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

USE tempdb;

DROP VIEW IF EXISTS dbo.TestValueOnly;

DROP TABLE IF EXISTS dbo.Test;

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

);

INSERT dbo.Test (TestName, ValueToChange)

VALUES ('Hello', 12),

('There', 14),

('And', 16),

('Again', 18);

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

CREATE VIEW dbo.TestValueOnly
WITH SCHEMABINDING
AS
SELECT TestID, ValueToChange 
FROM dbo.Test;
GO

CREATE UNIQUE CLUSTERED INDEX CX_dbo_TestValueOnly
ON dbo.TestValueOnly (TestID);
GO

CREATE VIEW dbo.TestValueOnly

WITH SCHEMABINDING

SELECT TestID, ValueToChange

FROM dbo.Test;

CREATE UNIQUE CLUSTERED INDEX CX_dbo_TestValueOnly

ON dbo.TestValueOnly (TestID);

Finally, we'll try both types of updates:

UPDATE dbo.Test 
SET TestName = 'Changed' 
WHERE TestID = 14;

UPDATE dbo.Test 
SET ValueToChange = 17 
WHERE TestID = 14;

UPDATE dbo.Test

SET TestName = 'Changed'

WHERE TestID = 14;

UPDATE dbo.Test

SET ValueToChange = 17

WHERE TestID = 14;

The first updates only a column that's not part of the indexed view. The second updates a column that is part of it.

And, as expected, the SQL Server team was mighty clever, and it works exactly as you'd expect:

The first update only touches the base table. The second touches both.

SDU Tools: Script User Defined Server Roles in SQL Server

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 these out, we've added a tool called ScriptUserDefinedServerRoles.

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

You can use our tools as a set or as a great example of how to write functions like these.

Find out more

You can see it in action in the main image above, and in the video here:

Access to SDU Tools is one of the benefits of being an SDU Insider, along with access to our other free tools and eBooks. Please just visit here for more info:

http://sdutools.sqldownunder.com

Opinion: When did "sqls" become a thing?

Given that SQL is an acronym for Structured Query Language, a reference to "a SQL" is then a reference to a language. Or the term SQL just refers to the language.

So something that I find really odd now is the number of people using the word SQL as a synonym for a SQL statement. I keep hearing references like this:

We need to execute several SQLs against the server.

I need to run a few SQLs.

and so on.

I live in Australia where we're well known for mangling parts of the English language and, importantly, abbreviating almost everything. "McDonald" becomes "Macca", etc.

We don't do that with other languages. We don't write "I wrote four Spanishs" instead of saying "I wrote four Spanish sentences".

But I can't be the only one who finds the reference to SQLs as pretty jarring.

Would love to hear your thoughts.

T-SQL 101: #47 Determining the length of strings in SQL Server

You might need to work out how long a string is. There are two ways you could measure the length of a string. The first is the number of characters; the second is the number of bytes (i.e. the amount of data).

You can see both here:

Even though I've provided a Chinese string, LEN still returns 2 as the number of characters in the string.

But because they were each two-byte characters, the function DATALENGTH returns 4 as the total number of bytes.

One of the challenges with the LEN function is that it ignore trailing spaces. So the string 'hello' and the string 'hello ' are both length 5 according to the LEN function.

If you would prefer a function that returns the actual number of characters, including trailing spaces, I'd suggest you look at the StringLength function in our free SDU Tools for developers and DBAs.

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.

Snowflake for SQL Server Users – Part 18 – Time travel

One really important concept in data warehouses is the idea of versioning. Imagine that I have customers broken into a set of business categories. And then I change which customers are in which categories. For so many transactional systems, if I run a sales report for last year, the customers would now appear in the current business categories, rather than the ones they were part of last year.

In Kimbal-based designs, that's part of the rationale for what are called Slowly-Changing Dimensions (SCDs).

SQL Server Temporal Tables

SQL Server 2016 introduced temporal tables. Each temporal table has two components: one table that holds the current data, and another table that holds the history for the same data i.e. the previous versions of the rows. While the SQL Server implementation is interesting, in practice, I've found it lacking in many ways.

The biggest challenge is the lack of temporal joins. Even though I can view the contents of a table at a particular point in time, and I can view all the versions of rows from the table over a period of time, there are real challenges when you need to work across joins. There's no way to get rows from one table, based upon the timeframe of the rows from another table. When I created the WideWorldImporters sample databases for SQL Server 2016, you'll find a whole bunch of cursor-based code in the procedures that extract data changes. I really didn't want to do that, but couldn't see any other way to achieve it.

Snowflake and Time Travel

The standard mechanism for accessing historical data in Snowflake is to use what's called Time Travel. The core things that Time Travel lets you do are:

Run queries to see the previous version of data at a given time
Create a clone of a table based upon a previous version of its data (you can also do this for schemas and databases)
Recover previous versions of objects that have been dropped

Because this historical data is available, there is even an UNDROP command that works for tables, schemas, and databases.

When you want to use historical versions of the data, in a SELECT statement (or in a CREATE CLONE statement), you can specify AT for a specific time, or BEFORE to exclude the given time. Then when you specify the time, you have three options:

Specify a specific time via the TIMESTAMP option
Specify a relative time from the current time by using the OFFSET option with a number of seconds
Specify a particular statement via a Query ID

The queries look like this:

select * 
from customers 
at(timestamp => 'Tue, 03 December 2019 06:20:00 +1000'::timestamp);

select * 
from customers 
at(offset => -60 * 20);

select * 
from customers 
before(statement => '4f5d0db9-105e-45ec-3434-b8f5b37c5726');

select *

from customers

at(timestamp => 'Tue, 03 December 2019 06:20:00 +1000'::timestamp);

select *

from customers

at(offset => -60 * 20);

select *

from customers

before(statement => '4f5d0db9-105e-45ec-3434-b8f5b37c5726');

Query ID

This last option is a really curious one for SQL Server people. Snowflake keeps track of previous queries that it has executed. Each one is identified by a Query ID.

There are many things you can access via this Query ID like the query plan involved.

Retention of History

The retention period for Time Travel is based upon the edition of Snowflake that you are using.

All editions are able to use a retention period of one day
Enterprise Edition allows up to 90 days for permanent objects, but only one day for transient and temporary objects.

Also in Enterprise Edition, you can specify a specific period for specific objects by using the DATA_RETENTION_TIME_IN_DAYS parameter when you are creating the objects.

If you really don't want to use Time Travel (and the space it will use), you can set DATA_RETENTION_TIME_IN_DAYS to 0 at the account level.

For an index to all posts in this series, see the first post here.

SDU Tools: Checking the version of SQL Server (by name) in T-SQL

We're pleased to see that there are now a large number of people using our free SDU Tools for developers and DBAs, and that number grows every day. One of the surprising requests that we've constantly received is for something that simply shows the name SQL Server version.

We've seen people doing quite messy things to derive the name, like parsing the return value from @@VERSION and so on.

So we decided to make that easy.

We added a function SQLServerVersion.

It takes no parameters and returns the name as a string.

You can use our tools as a set or as a great example of how to write functions like these.

Find out more

You can see it in action in the main image above, and in the video here:

Access to SDU Tools is one of the benefits of being an SDU Insider, along with access to our other free tools and eBooks. Please just visit here for more info:

http://sdutools.sqldownunder.com

SQL: Finding the current default schema in a SQL Server session

Each SQL Server user has a default schema. This the used in several ways. If I execute code like this:

CREATE TABLE Blah
(
...
);

CREATE TABLE Blah

(

...

);

I haven't specified where that table will be created. People seem to assume it will end up in the dbo schema i.e. the table will be dbo.Blah but that's not the case. It depends upon the user's default schema. If that's Sales, then the table Sales.Blah would be created instead.

Similarly, if I execute code like this:

SELECT BlahID, BlahName FROM Blah;

1	SELECT BlahID, BlahName FROM Blah;

Again, I haven't said which schema the table Blah is in. So, SQL Server will first check my default schema, and if that's Sales, it will first look for a table called Sales.Blah.

The recommendation is to always use two-part names for all objects that live in a schema.

Default Schema

One of my MVP friends recently asked how you can find the name of the current default schema.

Now you can find a user's default schema by querying the sys.database_principals system view. It has a column default_schema_name that'll work.

However, what if the user connects via group membership?

How can you tell the name of the current default schema?

The answer is to do this:

SELECT SCHEMA_NAME();

1	SELECT SCHEMA_NAME();

The SCHEMA_NAME() function will return the name of a schema if you pass it the schema_id but if you don't pass anything, it will return the name of the current default schema.

T-SQL 101: #46 Using LEFT, RIGHT, and SUBSTRING to extract substrings in SQL Server

You might need to extract part of a string from a given string. T-SQL has a number of functions to help you do that. Take a look at these examples:

LEFT, RIGHT, and SUBSTRING are all functions that return parts of strings .

In the first example, we asked for 3 characters from the left of the string so SQL Server returned ABC.

The second example shows returning 2 characters from the right of the string, so we get KL.

The third example same to return 4 characters starting at position 5, so we get EFGH.

It's worth noting that unlike languages such as C#, SQL Server doesn't complain if you ask for more characters than are present in the string. It just returns as many as are available.

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.