The Bit Bucket

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

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.

2019-12-11

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.

2019-12-10

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.

2019-12-09

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).

2019-12-06

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.

2019-12-04

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

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.

2019-12-03

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.

2019-12-02

In SQL Server, most tables benefit from having a clustering key i.e. the column or columns that the table is logically sorted by. (Note: much old training material and documentation used to say “physical order” and that’s never been true). To do that, SQL Server stores the data within the pages in a sorted way, and maintains a doubly-linked logical chain of pages. In addition, it maintains pointers to the data by creating an index on it.

2019-11-29

A forum poster was asking how to make a SQL Server column NOT NULL, but not apply it to old data. But when you add NOT NULL, there’s no option to say “Just new data”. However, you can work around it to achieve the same goal.

When you add a constraint to SQL Server, you can say WITH NOCHECK. That says to not check existing data. You can add a constraint to ensure a value isn’t NULL.

2019-11-28