Voice of the DBA

Tag: SQLNewBlogger

Defining FKs in CREATE TABLE–#SQLNewBlogger
Another post for me that is simple and hopefully serves as an example for people trying to get blogging as #SQLNewBloggers.

One of the important things that a database developer can do is to define Foreign Keys (FK) at table creation. This is a good time to do this as the referential integrity gets setup before any data is added and this can prevent issues later.

This post shows the syntax for defining the FKs and adding them to your tables immediately.

Build the Reference

The first step is to ensure you have a table with a Primary Key (PK) that will be referenced. Let’s do that first. I’ve been looking to provide a database of SQL Server Builds, so let’s start with a table of versions.
```
CREATE TABLE SQLServerVersion

( SQLServerVersionKey INT IDENTITY(1,1)

, VersionName VARCHAR(200)

, CONSTRAINT SQLServerVersionPK PRIMARY KEY (SQLServerVersionKey)

)

GO
```
The PK in this table is what is referenced in the next table. This is required as a FK must refer to a PK in another table.

Add the Reference

When you build a child table, you may write the code like this:
```
CREATE TABLE [dbo].[SQLServerBuilds]

(

[BuildKey] [int] NOT NULL IDENTITY(1, 1),

[BuildNumber] [varchar] (30)  NULL,

[BuildDescription] [varchar] (100)  NULL,

[BuildKBArticleNumber] [varchar] (50) NULL,

[BuildKBArticleURL] [varchar] (1000)  NULL,

[SQLServerVersionKey] [int]  NULL

) ON [PRIMARY]

GO
```
However, in this case, you’ve ignored the FK that might link this table to the versions table. This means that a value could be entered in this table that doesn’t exist in the SQLServerVersion table.

You might think this won’t happen with your application, but thousands, maybe millions, of developers have felt the same way. And they have junk data in their databases because of this.

If there is a strong relationship, add the FK.

Here’s how we do that in the CREATE TABLE statement. I’ll add a comma at the end and include a CONSTRAINT clause. I add the name and then the FOREIGN KEY keywords. Next I include the column from this table that is the FK with the References and the other table and column.
```
CREATE TABLE dbo.SQLServerBuild

(
     BuildKey INT NOT NULL IDENTITY(1, 1) ,
     BuildNumber VARCHAR(30) NULL ,
     BuildDescription VARCHAR(100) NULL ,
     BuildKBArticleNumber VARCHAR(50) NULL ,
     BuildKBArticleURL VARCHAR(1000) NULL ,
     SQLServerVersionKey INT NULL ,
     CONSTRAINT SQLServerBuild_Version_FK
         FOREIGN KEY (SQLServerVersionKey)
         REFERENCES dbo.SQLServerVersion (SQLServerVersionKey)

) ON [PRIMARY];

GO
```
This is the structure I tend to use, though sometimes I’ll move the CONSTRAINT clause directly below the actual column. This lets me see right away this is related to that column.

I also avoid using this inline in the column as I can’t specify the constraint name, which I always want to do.

SQLNewBlogger

This is a core skill that database developers needed. If you know the syntax, this post would take about 10 minutes to structure and write. If not, maybe it’s 10 more to learn a bit.. Write your own and show you understand the design concepts.

Reference

Creating Foreign Key Relationships – https://docs.microsoft.com/en-us/sql/relational-databases/tables/create-foreign-key-relationships?view=sql-server-2017
May 3, 2018
I learned about the order of logical operations #SQLNewBlogger
Another post for me that is simple and hopefully serves as an example for people trying to get blogging as #SQLNewBloggers.

I had logic in a CS curriculum many years ago and I’ve worked with AND and OR statements for years. I’ve sometimes confused myself, but I usually ensure I have parenthesis included to clarify the code. Not just for me, but for anyone that might glance at the code later.

As a side note, I also try to format code so a quick glance can reveal what happens.

However, I learned something new this week. I saw a question about the order of logical operations in this form: a or b and c.

I had somewhat assumed, like math, we’d use a left to right evaluation. However, that’s not correct. Look at this snippet:

If we went left to write, we’d have two rows from the OR (n=1, n=2) and then an AND that produces no rows. So no results?

That’s not correct. According to BOL for OR, the AND operations occur first. So n=2 AND n > 3 occurs, with 0 rows. Then the OR with n=1 is evaluated to return 1 row.

Fascinating.

At least to me. I’ve never thought because I’d write
```
WHERE (n = 1 OR n = 2) AND n > 3
```
or
```
WHERE n = 1 OR (n = 2 AND n > 3)
```
and be sure that what I wanted to occur would occur.

A quick lesson. While it’s good to know what the order or evaluation is for your platform, don’t count on this. If there is a chance for confusion or unintended consequences, use parenthesis. It’s simpler and easier, and I might argue, more elegant.
March 22, 2018
Enabling Guest in a Database–#SQLNewBlogger
Another post for me that is simple and hopefully serves as an example for people trying to get blogging as #SQLNewBloggers.

The guest account exists in all your databases. This is installed by default, and guest is used to map a login that doesn’t otherwise have access to a database.

Sound scary?

It should. This would be bad if any login could connect to any database, potentially reading data using the guest account. Fortunately Microsoft has done two things. First, guest is disabled in all user databases. This is because it’s disabled in model, which is our template.

Second, guest is typically assigned no rights. It’s a member of the public role, which also has no rights by default.

Enabling Guest

If you want to allow anonymous access for logins through the guest account, it’s easy. Be wary and careful of doing this and be sure you understand what rights have been granted to public if you do this. In general, I’d expect auditors and any compliance/security officers to be against this, but you should check.

The user exists already, and just needs the CONNECT permission to get enabled. You can do this with this code:
```
GRANT CONNECT TO guest
```
If you want to remove permission, use
```
REVOKE CONNECT FROM guest
```
That’s it. Remember, by default this user can’t access any objects. I would recommend you not grant rights to guest, but use roles. Either one of the built in ones, or better yet, create your own role and choose limited permissions.

SQLNewBlogger

One of the ways you can showcase your knowledge, show you’re learning, and show you’re motivated to enhance your career is blogging. This post is an example of what you could write, in your own words, about something you’ve learned.

This one took my about 5 minutes after I’d spent a little time getting guest enabled for a test project.
February 15, 2018
Advent of Code 2017 Day 5–#SQLNewBlogger
Another post for me that is simple and hopefully serves as an example for people trying to get blogging as #SQLNewBloggers.

This is day 5 of the Advent of Code 2017. If you want to read about the puzzles, start with Day 1. This is going to be a crazy looping item, since it will move through the list, relative to the current spot, and incrementing items, I know this won’t be good in SQL.

Still. Worth solving.

Let’s load the data. I’ll use a table, but first, I’ll also add an identity. This will help me number instructions and figure out what the next one is.
```
CREATE TABLE Day5
( InstructionKey INT IDENTITY(1,1)
, Instruction INT)
GO
```
There are issues with identities, but this is a great trick:
```
CREATE VIEW Day5V
AS
SELECT d.Instruction FROM dbo.Day5 AS d
GO
-- reusable code
BULK INSERT Day5V FROM 'e:\Documents\GitHub\AdventofCode\2017\Day5\Input.txt' WITH (ROWTERMINATOR='\n')
GO
```
Now I can get to work. Here’s the logic I used.

I wanted to first set some starting points. I have a counter (0 based, increment first). This determines how many times I jump around. I also need to track the current instruction key and the next key. And, of course, I need the instruction value.

The identity is the array index, or the instruction key (which place am I in). In this case, I’ll try to follow this logic.

Get the end (out of bounds, which is the max + 1). I loop until I get an jump outside of the end range. The loop does these items:
- Get the current instruction jump
- Set the next location to be the current key + the current jump
- Update the current jump to increment by 1
- Set the current instruction key to the next key
- loop
This seems to be what I need. On the test set, this worked fine. When I first set this up, I used this code:
```
DECLARE @end INT ,
         @CurrentInstructionKey INT = 1 ,
         @Instruction INT ,
         @NextInstructionKey INT ,
         @counter INT = 0;
SELECT @end = MAX(InstructionKey) + 1
FROM dbo.Day5 AS d;

-- SELECT [end] = @end;

WHILE @CurrentInstructionKey < @end
BEGIN
     SET @counter = @counter + 1;
     SELECT @Instruction = Instruction
     FROM Day5
     WHERE InstructionKey = @CurrentInstructionKey;
     SELECT @NextInstructionKey = @CurrentInstructionKey + @Instruction;
     UPDATE dbo.Day5
     SET Instruction = Instruction + 1
     WHERE InstructionKey = @CurrentInstructionKey;
     SET @CurrentInstructionKey = @NextInstructionKey;
--PRINT @CurrentInstruction
END;

SELECT Counter = @counter ,
        [current] = @CurrentInstruction;
```
When I ran this, it chugged for some time. I bet in Python or C#, which would solve quickly with arrays. With updates, it’s slow. Like minutes slow for 1074 rows.

However, it worked.

Part II

In this part, this instructions are almost the same, but based on the current instruction value, we either increase or decrease the value. Not a big change. Our new update looks like:
```
UPDATE dbo.Day5
SET Instruction = Instruction + CASE
                                     WHEN @Instruction >= 3 THEN
                                         -1
                                     ELSE
                                         1
                                 END
WHERE InstructionKey = @CurrentInstructionKey;
```
This also works, albeit slowly. I left this around 5:30 and went to the gym.

One of the easier puzzles.
January 25, 2018