Key Value Pair Basics for SQL Server Data

By: Tim Smith   |   Comments   |   Related: > Application Development

Problem

In this tip we will look at key value pairs using C# and PowerShell and where these may be useful. While we can store these data in a table structure on the back-end, there may be situations where we want to extract these values in these key-value formats for application or configuration purposes. In later versions of SQL Server, T-SQL works with JSON, so this is an additional option if you're using a later edition of SQL Server.

Solution

Key value pairs provided a useful tool to map data to each other, such as mapping a public key to a private key, or mapping items to an object - like city names within a country. In this tip, we look at some of the basics of key value pairs using examples to assist in thinking about how to structure data on the back-end, or how to load data from the back-end with ETL applications, loaded configuration from a database, or other uses involving data mapped to each other.

We will begin this tip introducing key-value pairs by using simple C# for demonstrative purposes - the following code is not meant to be run inside a class or method for an object in production - this is only to show a key-value dictionary in C#. Open Visual Studio, select new project and title it Demo, then copy and paste the below code and run the program:

using System;
using System.Collections.Generic;

namespace Demo
{
    class Program
    {
        static void Main(string[] args)
        {
            Dictionary <string, string > testDictionary = new Dictionary <string, string >();
            testDictionary.Add("keyOne", "Value1");
            testDictionary.Add("keyTwo", "Value2");
            testDictionary.Add("keyThree", "Value3");

            foreach (var dictItem in testDictionary)
            {
                Console.WriteLine("The key is " + dictItem.Key + " and the value is " + dictItem.Value);
            }

            Console.WriteLine(System.Environment.NewLine);
            Console.WriteLine(System.Environment.NewLine);

            Dictionary <string, string[] > testDictionaryMultiple = new Dictionary <string, string[] >();
            testDictionaryMultiple.Add("keyOne", new string[] { "Value1Position1", "Value1Position2" });
            testDictionaryMultiple.Add("keyTwo", new string[] { "Value2Position1", "Value2Position2" });

            foreach (var dictMultiple in testDictionaryMultiple)
            {
                Console.WriteLine("The key is " + dictMultiple.Key + " and the values in this key are: ");

                foreach (string value in dictMultiple.Value)
                {
                    Console.WriteLine("\t " + value);
                }
            }

            Console.ReadLine();
        }
    }
}

You should see:

As we can see in the first part of the code, we have a dictionary object which has two strings - the key and the value. In this example, keyOne is associated with Value1, keyTwo is associated with Value2, and keyThree is associated with Value3. If you want to imagine an application of this, you can think of a connection string in an XML file where the key is "ourConnection" and the value is the ADO.NET connection string. For key-value pairs like this, configurations are one application of these.

In the second example from the above code, we see two string keys, but within each key, we have two string arrays; in this example, the key keyOne has two string values Value1Position1 and Value1Position2. An example of this in practice would be a video game where a soldier has an array of weapons as the values with the key being weapons. An example of this using ETL would be loading data from a SQL Server table into a dictionary where the key is the name of a currency and the values are the high, average, low, and last price of that currency.

Using the above example, we'll switch to PowerShell (launch PowerShell ISE) and execute similar code to produce a similar output:

$json = '{ "keyOne": "Value1", "keyTwo": "Value2", "keyThree": "Value3"}'
$json = $json | ConvertFrom-Json

$json.keyOne
$json.keyTwo
$json.keyThree

[Environment]::NewLine

$multjson = '{ "keyOne": [ "Value1Position1", "Value1Position2" ], "keyTwo": [ "Value2Position1", "Value2Position2" ] }'
$multjson = $multjson | ConvertFrom-Json

$multjson.keyOne

You should see:

We can even nest further with key-value pairs. For example, in the below PowerShell, we have one key of dayOne, but within this key, we have two currency keys - DXY and BTC and these keys have the high, median and low value for the day measured:

$layerjson = '{ "dayOne": [ { "dxy": ["101.50","101.34","101.21"] }, { "btc": ["840.40","802.61","798.21"] } ] }'
$layerjson = $layerjson | ConvertFrom-Json

Write-Host "DXY:"
$layerjson.dayOne.dxy
[Environment]::NewLine
Write-Host "BTC:"
$layerjson.dayOne.btc

In order to retrieve the nested currency keys, we only have to add the key name after the previous key and a period. This example shows how we can nest data further into a key value pair, where we have a key with a value that is actually another key-value pair. Think of it in pseudo code like:

Simple:
{Key}-{Value}

Nested:
{Key}-{{Key}-{Value}}

Visually imagining how data should look, whether configuration data, reporting data, or live data may shape what solutions we offer to the end user who needs the data. It prevents miscommunication that may lead to queries which load unnecessary data, the wrong data, or are designed in a manner that creates additional problems.

In addition, knowing how data are being used can help us decide where we want to optimize our architecture and data. A simple example would be a configuration table where we have connection string names, connection strings, date of creation and modification, ID fields, and notes about each, and our application may only need the names and strings - in this case, applying an index for this process to names may help, if filters are required for some strings. In a more complicated example, I may have a currency table with name and ID that is linked to a table with the actual data for each currency throughout the day and knowing which values need to be used from both tables will shape how I structure the data along with objects for optimization, such as indexes and statistics. As a general rule, when converting data from SQL Server into a specific format - like JSON - in some cases, it is much faster to do this in directly in .NET. Finally, in later editions of SQL Server, we may find use cases for in memory tables for some reports or live data feeds that involve key-value pairs; I've found many useful cases for these tables with data that needs to be loaded quickly for key-value reports, or for reports that can be structured in this manner.

As you become familiar with key-value pair data in practice, you may be able to offer these as a suggestion in some cases when working with object oriented developers, as this is generally seen as an easy form of data to use. I've reorganized some reports using this set up that lead to major performance gains for the client. While not appropriate in all cases, knowing what and how to structure these data may be useful in the future.

Next Steps

• In thinking of the examples from the above tip, what is a key-value pair that you can think of with data that you work with?
• Knowing how data are used helps architect the structure and optimization for those data. One question to ask developers you work with is how are these data reported on the front end, or application layer.
• Many libraries, especially in JavaScript, use and work with key-value pair data, which increases utility.

About the author

Tim Smith works as a DBA and developer and also teaches Automating ETL on Udemy.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips