Modifying existing SQL tables is a common use case as the data models for your application change over time. The most basic modification you are likely to do is adding a new column to a SQL table to capture more data.

Simple changes can be done using SQL Server management software that provides a convenient user interface to interact with your database and tables. For more complex operations, using scripted commands in the Transact-SQL [(T-SQL)](https://docs.microsoft.com/en-us/sql/t-sql/language-reference?view=sql-server-ver16) language is usually preferable. Using T-SQL provides the greatest flexibility to fine-tune the table adjustments, and it makes it very easy to keep a record of the changes made to the table.

To add a column to an existing SQL Server table we are going to use the **ALTER** keyword. **ALTER** does more than just allowing us to add columns to a table, it is also used to delete or modify existing columns. It can further be used to add, modify or drop foreign keys on the table columns, as well as modifying constraints like *UNIQUE* or *NOT NULL* that can be placed on columns to protect data integrity.

The quickest way to add a new column is without any additional configuration:

```sql
ALTER TABLE table_name
ADD column_name datatype;
```

Where the *datatype* is replaced with any valid data type as listed [here by Microsoft](https://docs.microsoft.com/en-us/sql/t-sql/data-types/data-types-transact-sql?view=sql-server-ver16).

We can append additional configuration to this basic command to define specific requirements for the new column.

Here we are placing a *UNIQUE* constraint to enforce unique values in the new column:

```sql
ALTER TABLE table_name
ADD column_name datatype;
ALTER TABLE table_name 
ADD UNIQUE (column_name);
```

Adding a column that may not contain NULL values is a bit more complicated. We have to satisfy the *NOT NULL* constraint right away, meaning we can't create a column that can't be null without providing values for it. The solution is to provide a DEFAULT value:

```sql
ALTER TABLE table_name
ADD column_name datatype
NOT NULL
DEFAULT default_value
```

The chosen *default_value* depends on the datatype of the column being added and the requirements of your application, e.g. '0' for a column of strings.

Finally, let's add a column of auto-incrementing integers. For this example we use the **IDENTITY** keyword, which requires a seed value for the first row and an an increment to number the following rows. Both the seed and the increment are set to 1 here, the result is a new column containing values starting 1 to the number of rows in the table.

```sql
ALTER TABLE table_name
ADD column_name INT IDENTITY(1,1)
```

The **IDENTITY** keyword does the magic to make the column auto-increment, but be aware that there can only be one identity column per table. Due to its convenient set up and inherent uniqueness, the identity column is commonly used as the *PRIMARY KEY* column of the table, although this is not mandatory.

Two more constraints you may want to apply while adding a new SQL Server column are *FOREIGN KEY* and *CHECK*. *FOREIGN KEY* is used when a column needs to connect a relationship to another column in a different table, for example to only allow orders against a known product id for an online shop. The *CHECK* constraint is used to evaluate column entries against a predefined rule - e.g. value must be larger than 0 for a quantity or price column.

Modifying existing SQL tables is a common use case as the data models for your application change over time. The most basic of these modifications is adding a new column to a SQL table to capture more data.

How to Add a Column to a Microsoft SQL Server Table

Configuring the auto-increment property to a column is a simple SQL command. The complexities come in when trying to deal with either a) tables that have already been created, b) tables that already have data rows in them, and c) when you need to add auto-increment to an existing Primary Key column.

The complexities arise from the fact that adding the auto-increment requires a full table rebuild. If the table has already been created and/or has data rows then adding the auto-increment is more complex.

## Adding to a New Table 

```sql
CREATE TABLE Users (
  Id INT NOT NULL IDENTITY(1, 1) PRIMARY KEY,
  Email VARCHAR(100) NOT NULL,
  Firstname VARCHAR(100),
  Surname VARCHAR(100),
);
```

In this example, we are creating a new table called Users. 

In this table, there are four columns. The Id column is the important one. The keyword IDENTITY is what sets the column to auto-increment by one for every new row added to the table. Additionally, this SQL command sets the Id column as the Primary Key. This means that the column must also be a completely unique index on the table.

The IDENTITY constraint accepts two optional values (both defined as 1 in the example above). The first is the seed value which is essentially the starting number. This can be any valid INT to start from if 1 is not the desired starting point. The second value is the amount to increment each new row by. Setting it to 1 means the number will increase numerically as though it were counting. Setting it to 2 would skip a number each time -> 1,3,5,7,9 etc. Both these values default to 1 and so do not have to be included if you do not need to change them.

## Adding to an Existing Table

There are two options when adding an auto-increment column to an existing table. The first is to create a new table with the IDENTITY column defined, copy all the data rows from the existing table into it, delete the existing table and rename the new table the same as the original:

```sql
-- Create a new temp table with the identity column defined
CREATE TABLE dbo.Tmp_Names
    (
      Id int NOT NULL IDENTITY(1, 1) PRIMARY KEY,
      Name varchar(50) NULL
    )
ON  [PRIMARY]
go

SET IDENTITY_INSERT dbo.Tmp_Names ON
go

-- Update the temp table with the rows from the existing table
IF EXISTS ( SELECT  *
            FROM    dbo.Names ) 
    INSERT  INTO dbo.Tmp_Names ( Id, Name )
            SELECT  Id,Name
            FROM    dbo.Names TABLOCKX
go

SET IDENTITY_INSERT dbo.Tmp_Names OFF
go
-- Delete the existing table
DROP TABLE dbo.Names
go
-- Rename the temp table to match the original table to maintain schema integrity
Exec sp_rename 'Tmp_Names', 'Names'
```

The second option is to add the auto-increment column as a new column to the table, copy the column values across, delete the original column and then rename the new column:

```sql
-- Add a new column to the table
Alter Table Users
Add Id_new Int Identity(1, 1)
Go
-- Set the new column to the original
UPDATE Users
SET Id_new = Id
-- Delete the original column
Alter Table Users Drop Column Id
Go
-- Rename the new column to the original
Exec sp_rename 'Users.Id_new', 'Id', 'Column'
```

One thing to be aware of in this method is that an extra command is required to drop the existing column if it has been defined as the primary key. The name of the PK constraint will depend on what it was called when it was originally created:

```sql
-- Return the name of primary key.  
SELECT name  
FROM sys.key_constraints  
WHERE type = 'PK' AND OBJECT_NAME(parent_object_id) = N'Users';  
GO 
-- Delete the primary key constraint.  
ALTER TABLE Users  
DROP CONSTRAINT PK_Users_Id;   
GO  
```

The first command will return the name of the constraint which must go in place of 'PK_Users_Id' in the second command above.

Knowing how to add auto-incrementing to a numeric column in SQL Server is a skill all database admins should have. It is an exceptionally common use case, whether it be on the Primary Key column of your table, or you need a column to identify insert order or similar.

Add Auto-Increment to a Column in SQL Server

Adding indexes to your SQL Server tables is something that you will do plenty of times throughout your career, either as a full-stack developer or as a specialist database admin. Fortunately, adding indexes to tables in your database is a pretty simple task. 

Following on from [What are SQL Indexes](https://www.mickpatterson.com.au/blog/what-is-a-sql-index), we'll now cover how to actually implement one in your database.

The actual SQL command to add an index is incredibly simple:

```sql
CREATE UNIQUE INDEX index1 
ON schema1.table1 (column1 DESC, column2 ASC, column3 DESC);
```

This command is instructing the database to create a unique index named 'index1' on the table in 'schema1' named 'table1' and to take 'column1', 'column2', and 'column3' in descending, ascending and descending orders respectively.

SQL Server indexes do not need to be applied to multiple columns - they can be just a single column. The point of the above example is to show you that the index can span multiple columns and in multiple sort orders. 

The above example would be best applied to a table that has a very large amount of data rows that your application is querying frequently and uses the three columns in the WHERE clause of the query. Creating the index on the table and those columns creates a fast lookup table for your database to check first before it pulls out the rows that match your query filter values.

Adding indexes to your SQL Server tables is something that you will do plenty of times throughout your career, either as a full-stack developer or as a specialist database admin. Fortunately, adding indexes to tables in your database is a pretty simple task.

How to Create a SQL Server Index

## W﻿hat is a Foreign Key?

I﻿n a SQL database, a foreign key is a column, or combination of columns, in a single table that references another column in a different table. To 'reference' another column means to have the same value as the column it references.

T﻿his reference creates a relationship between the two tables, which is known as a parent-child relationship. In this relationship the parent table is the table that contains the reference column. The child table is the table that contains the column or columns that reference the parent value.

## Why Use a Foreign Key?

T﻿he main purpose of using foreign keys is to maintain referential integrity in the database. The value entered into the foreign key column of the child table **must** exist in the parent table. If the row in the parent table that contains this reference is removed, then the foreign key cannot be entered into the child table.

T﻿his leads to a secondary use of foreign keys. They help enforce business rules in the database. A value cannot be deleted from a parent table if a child table reference is without either cascading queries or more complex business logic.

F﻿oreign keys also make querying easier. The foreign key is traditionally also the column that tables are joined by when querying. Having a clear database structure and documentation about the foreign keys makes it much simpler to query the data.

Foreign Keys are a necessity in any relational database. They help connect distinct data tables together by creating a tightly-bound reference between the tables.

What is a Foreign Key?

## What is Memoization?

Memoization is a technique used in web development to speed up the execution of functions by caching the results of expensive function calls and returning the cached result when the same inputs occur again. The concept is very similar to caching on the server but done at a function level.

## Why Use Memoization?

Memoization is used to improve the performance of your code by reducing the number of times a function needs to execute. By caching the result of a function call, memoization can eliminate the need to perform complex calculations or expensive database queries every time a function is called with the same inputs.

T﻿he key here is to understand that the inputs to the function must match exactly for the function to use the memoized results instead of executing the function logic in its entirety again.

## How to Implement Memoization

To implement memoization, you need to create a cache that will store the results of function calls. How this is done depends on your technology stack and whether you have access to services like Redis for external caching of data.

At a base level, you can use an object or a Map to store the cached results. When a function is called, you check the cache to see if the result for the given inputs has already been computed. If it has, you return the cached result. If it hasn't, you compute the result and store it in the object or Map before returning it.

Here's an example of how to implement memoization in TypeScript:

```
const memoize = <T extends (...args: any[]) => any>(fn: T): T => {
  // Set up the cache map
  const cache = new Map();
  return ((...args: Parameters<T>): ReturnType<T> => {
    // Check if the key has already been cached and return the result if found
    const key = JSON.stringify(args);
    if (cache.has(key)) {
      return cache.get(key);
    }
    // If not found, execute the memoized function cache and return the result
    const result = fn(...args);
    cache.set(key, result);
    return result;
  }) as T;
};
```

I﻿n this example, the memoize function receives another function as its only argument. This function is the one that is complex or time-consuming that you want to cache the results from.

T﻿he memoize function then checks the cache map object to see if the complex function has been called before with the same set of arguments. If it has, the result of the function execution with those arguments is returned immediately.

I﻿f the key is not found, the memoize function then executes the complex function, caches the result and returns the result.

## When to Use Memoization?

1. C﻿aching Database Queries\
   I﻿f you have a function in your API logic that executes frequently with the same filter parameters, it may be worth memoizing the results so that the API does not need to make unnecessary requests to the database for the same data sets.
2. R﻿educing CPU Consumption\
   I﻿f the function is very CPU intensive to complete or long-running that affects other parts of your application, it would be sensible to memoize it.
3. I﻿mproving Network Performance\
   If the application makes many requests to a 3rd-party API, but with the same parameters, memoizing the results from the API will help reduce the number of API requests as well as speed up the application.

Memoization is a term you will come across at some stage when reading about frontend frameworks, libraries and in various packages you may need to use. This post will explain what memoization is, why use memoization and how to implement memoization.