T-SQL Output Query | The Computed Column Puzzle

Recently I was implementing something in my project with computed column. From there this question comes to my mind. So here is the question. Let’s say you are doing some calculation on current year basis. Now what will happen next year means when the system dates change. Check out the example below-

--

--Consider the table definition
CREATE TABLE testCalculateColumn
(
	 ID SMALLINT
	,NumberOfYears SMALLINT
	,StartingYear AS YEAR(GETDATE()) - NumberOfYears
)
GO

--

Here Starting year is the computed column which is dependent of NumberOfYears and the current year. Now NumberOfYear will be static once we inserted any row, or it will only change if we update any value, But YEAR(GETDATE())can change every year based on your system date. Now let’s insert values into the table.

--

--Insert some values
INSERT INTO testCalculateColumn
VALUES (1,2),(1,8),(1,12),(1,1),(1,5)
GO

--

Now let’s run the select command to check the values of Starting Year

--

SELECT 
	ID,NumberOfYears, StartingYear
FROM
	testCalculateColumn;
GO


--

Output when Year = 2015

ID	NumberOfYears	StartingYear
1	2	2013
1	8	2007
1	12	2003
1	1	2014
1	5	2010

Today the year is 2015, Now what will happen next year when you system year = 2016. Are values of Starting year will change or will remain the same?

You have fill the below table when system year changes from 2015 to 2016-

ID	NumberOfYears	StartingYear
1	2	?
1	8	?
1	12	?
1	1	?
1	5	?

Please leave a comment if you need solution to the above puzzle ! Cheers

Keep Learning

Pawan Kumar Khowal

MSBISKills.com