Computed Columns In SQL Server

Today, i would like to talk about Computed Column in SQL Server.

what is computed column ? 

A computed column is computed from an expression that can use other columns in the same table. The expression can be a non computed column name, constant, function, and any combination of these connected by one or more operators. The expression cannot be a sub query. ( As Per MSDN)

Meaning :  The column which has to be populated while inserting the data to the already existing columns

Example : 

-- creating sample table with Computed Column

CREATE TABLE ComputedColumn (FirstName VARCHAR(50), LastName VARCHAR(50) , FullName AS FirstName + LastName )

with the above script we can create table and the next step is insert the data into table, if you run the following script , you will get an error

INSERT INTO ComputedColumn VALUES ('Narendra' ,'Kotha','NarendraKotha')

because the column "FullName" is not normal column which we are used to insert the data but it is computed column and the data is going to be populate in this column was already computed with the help of already existing columns.

the below script works fine 

INSERT INTO ComputedColumn VALUES ('Narendra' ,'Kotha')

Limitations  :- 

1). we cannot insert the data explicitly into computed column

2). we cannot update the data in computed column

3). we cannot change the datatype of computed column with ALTER

4). Only Unique and Primary Key constraints are allowed create on computed column 

Note : There are so many limitations but the above mentioned are best to my knowledge and i have faced  while working with Computed Columns.

Getting Comma Seperated Values Into Rows

Recently one of my friend got the following requirement

Actual Table :



OutPut Table



Here all the comma Separated values in each row have to be distributed into individual rows corresponding ID value.

of course this task can be done in multiple ways , i have come up with follwoing script

Source Code :

/* Creating SampleTable with 5 Rows */

IF EXISTS(SELECT 1 FROM sys.tables WHERE TYPE = 'U' AND name = 'SampleTable')
DROP TABLE SampleTable
GO
CREATE TABLE SampleTable (ID INT, Value VARCHAR(50))

INSERT INTO SampleTable VALUES(1,'1001'),
                              (2,'1002,1003'),
                              (3,'1004,1005,1006'),
                              (4,'1007,1008,1009,1010'),
                              (5,'1011,1012,1013,1014,1015')

 /*  Creating Copy of SampleTable For Our Query Processing */

IF EXISTS(SELECT 1 FROM sys.tables WHERE TYPE = 'U' AND name = 'SampleTable_Dup')
DROP TABLE SampleTable_Dup
GO

SELECT * INTO SampleTable_Dup FROM SampleTable

SELECT * FROM SampleTable_Dup


/* Creating ResultTable which is Temporary to Store the Result */

DROP TABLE #ResultTable

CREATE TABLE #ResultTable (ID INT,Value VARCHAR(50))


/* Actual Logic Starts */

DECLARE @Start INT,
        @Count INT,
        @ID INT,
        @String VARCHAR(50),
        @StringStart INT,
        @StringLength INT,
        @OutputString VARCHAR(50),
        @To INT
      
SET @Start = 1
SET @Count = (SELECT COUNT(1) FROM SampleTable_Dup)


WHILE (@Start < = @Count)
BEGIN
   
    SET @OutputString = ''
    SET @ID = (SELECT TOP 1 ID FROM    SampleTable_Dup)
    SET @String = (SELECT TOP 1 Value FROM SampleTable_Dup)
   
   
   
    IF (SELECT CHARINDEX(',',@String)) > 0
        BEGIN
      
            SET @StringStart = 1
            SET @StringLength = (SELECT LEN(@String))
          
            SET @To = 1
          
            WHILE (@StringStart <= @StringLength)
            BEGIN
              
                IF (SELECT SUBSTRING(@String,@StringStart,@To)) <> ','
                BEGIN
                    SET    @OutputString = @OutputString + (SELECT SUBSTRING(@String,@StringStart,@To))
                END
                ELSE
                BEGIN
                    INSERT INTO #ResultTable(ID,Value)
                    SELECT @ID,
                           @OutputString
                    SET @OutputString = ' '
                END  
              
            SET @StringStart = @StringStart + 1
                  
            END
            END
            IF (@OutputString <> ' ')
            INSERT INTO #ResultTable(ID,Value)
                SELECT @ID,
                       @OutputString
            ELSE
            BEGIN
                INSERT INTO #ResultTable(ID,Value)
                SELECT @ID,
                       @String
            END
          
    SET @Start = @Start + 1  
      
    DELETE FROM SampleTable_Dup WHERE ID = @ID AND Value = @String   
END
   

SELECT * FROM #ResultTable

Friends, if any body have faced the same problem , Please share with your solution .

Thanks,
Narendra