Tag: T-SQL

Another post for me that is simple and hopefully serves as an example for people trying to get blogging as #SQLNewBloggers.

I was reading Iris Classon’s blog recently and ran across a post on her day at work. I think it’s a fantastic post that does help younger people understand what a job is like. I’m looking forward to seeing more.

One quick thing struck me in the post, which was that she has clients that are missing key value settings in a table. I’ve dealt with this, and want to write more, but a quick post on just finding out that there are missing settings.

Finding the Data Inconsistencies

Each client should have a set of key value pairs in a settings table. However, because of application problems, not every client does. In the sample data (below), there are 8 clients, each of which should have 5 values in the GlobalSettings table. However, there are only 20 rows in this table when there should be 40.

This is the type of issue I’ve had occur in an application, especially one that evolves over time. We add a key-value item to the application, which new clients get, but older ones are never populated. This is the same type of issue I saw in a post by Iris Classon.

How can I find the items that don’t match? One simple way is to count the values, but include a HAVING clause to limit the results. If I want to see who has all the values, I can do this:

SELECT gs.ClientID, COUNT(*)

FROM dbo.GlobalSetting AS gs 

GROUP BY gs.ClientID
  HAVING COUNT(*) = 5

In my sample data, this returns one row, for Client 1. If I change the HAVING clause to < 5, I get the other seven rows.

There are other considerations here, and this isn’t the best way that you might ensure you have the values. I might have a table, or a derived table that ensures I’m checking the right 5 values.

I’ve written more about this in an article at SQLServerCentral.

The Setup

I built a couple quick tables and added data with this script. Note that there are 8 clients, and that each has a series of settings in a table. There are 5 possible settings (Position, Height, Weight, Number, College)

CREATE TABLE Client
(ClientKey INT IDENTITY (1,1) NOT NULL CONSTRAINT ClientPK PRIMARY KEY 
, ClientName VARCHAR(200)
, ClientStatus TINYINT)
go
CREATE TABLE GlobalSetting
( GlobalSettingKey INT IDENTITY (1,1) NOT NULL CONSTRAINT GlobalSettingPK PRIMARY KEY 
, ClientID INT NOT NULL CONSTRAINT GlobalSettingFK_Client_ClientID FOREIGN KEY REFERENCES Client
, GlobalSettingName VARCHAR(100)
, GlobalSettingValue VARCHAR(500)
)
GO
INSERT client VALUES ('Shaquil', 1), ('Von', 1), ('Bradley', 1), ('Shane', 2), ('Todd', 2), ('Jerrol', 3), ('Jeff', 3), ('Josey', 3)

 Position, College, Height, weight, number
INSERT dbo.GlobalSetting
(
    ClientID ,
    GlobalSettingName ,
    GlobalSettingValue
)
VALUES
  (1, 'Position', 'OLB')
, (1, 'Weight', '250'),
(1, 'College', 'CSU')
, (1, 'Height', '74')
, (1, 'Number', '48')
, (2, 'Weight', '250')
, (2, 'Number', '58')
, (2, 'College', 'Texas A&M')
, (3, 'Height', '76')
, (3, 'Weight', '269')
, (4, 'Position', 'OLB')
, (4, 'College', 'Missouri')
, (4, 'Height', '75')
, (5, 'Weight', '230')
, (5, 'Number', '51')
, (5, 'College', 'Sacramento St')
, (6, 'Weight', '235')
, (6, 'Position', 'LB')
, (7, 'Weight', '249')
, (8, 'Number', '47')

SQLNewBlogger

This only took about 10 minutes to write, though I had to build the tables and data. Of these, the data took the longest, because I had to look up the values .

This is a basic example of checking data in a business situation. I might write about this in my job, perhaps showing a daily integrity check or a custom metric that I use to ensure the system is working. You can do the same thing and ensure that your data is correct.

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