Welcome to another blog post about SQL Server! Today, we are going to explore the concept of computed columns and how they behave in different scenarios.

First, let’s create a sample database and table to work with:

CREATE DATABASE DB2012;
USE DB2012;
CREATE TABLE TableDT (
  DT1 VARCHAR(100),
  DT2 DATETIME2,
  DT1C AS DT1,
  DT2C AS DT2
);
INSERT INTO TableDT (DT1, DT2)
SELECT GETDATE(), GETDATE();

In this table, we have four columns. The first column, DT1, is a regular VARCHAR column, and the second column, DT2, is a DATETIME2 column. Both columns are populated with the same data using the GETDATE() function.

Now, let’s run a SELECT statement to retrieve the data from both columns:

SELECT DT1, DT2 FROM TableDT;

Before running the query, take a moment to guess the result and write it down. This will be our Question 1.

Next, let’s run another SELECT statement, but this time we will retrieve only the computed columns:

SELECT DT1C, DT2C FROM TableDT;

Again, take a moment to guess the result and write it down. This will be our Question 2.

Now, here comes the interesting part. Let’s use the CAST function on the computed columns:

SELECT CAST(DT1C AS DATETIME2) CDT1C, CAST(DT2C AS DATETIME2) CDT1C FROM TableDT;

Take a moment to guess the result and write it down. This will be our Question 3.

Now, let’s inspect all the answers together and see how many of you got them correct:

Answer 1: [Your answer for Question 1]

Answer 2: [Your answer for Question 2]

Answer 3: [Your answer for Question 3]

If you haven’t tried running the script yet, you can execute all three queries together and see the results.

Now, you might be wondering why we get the same result from both expressions in Question 3, whereas in Question 2, the expressions have different answers. The answer to this question will be revealed in a future blog post, so stay tuned!

That’s it for today’s blog post. I hope you enjoyed this small experiment with computed columns in SQL Server. Stay curious and keep exploring!