A computed column is a virtual column that is not physically stored in the table, unless the column is marked PERSISTED.A computed column expression can use data from other columns to calculate a value for the column to which it belongs. You can specify an expression for a computed column in in SQL Server by using SQL Server Management Studio or Transact-SQL.
Limitations and Restrictions
A computed column cannot be used as a DEFAULT or FOREIGN KEY constraint definition or with a NOT NULL constraint definition.
A computed column value is defined by a deterministic expression and the data type of the result is allowed in index columns, a computed column can be used as a key column in an index or as part of any PRIMARY KEY or UNIQUE constraint. For example, if the table has integer columns a and b, the computed column a + b may be indexed, but computed column a + DATEPART(dd, GETDATE()) cannot be indexed, because the value might change in subsequent invocations.
A computed column cannot be the target of an INSERT or UPDATE statement.
Using Transact-SQL
USE Test_DBGO
-- Create Table
CREATE TABLE Table_A (ID INT,
FirstName VARCHAR(100),
LastName CHAR(8000))
GO
CREATE TABLE Table_B (ID INT,
FirstName VARCHAR(100),
LastName CHAR(8000))
GO
FirstName VARCHAR(100),
LastName CHAR(8000))
GO
CREATE TABLE Table_B (ID INT,
FirstName VARCHAR(100),
LastName CHAR(8000))
GO
-- Insert Ten Records
INSERT INTO Table_A (ID,FirstName,LastName)
SELECT TOP 10 ROW_NUMBER() OVER (ORDER BY a.name) RowID,
'Bob',
CASE WHEN ROW_NUMBER() OVER (ORDER BY a.name)%2 = 1 THEN 'Smith'
ELSE 'Brown' END
FROM sys.all_objects a
CROSS JOIN sys.all_objects b
GO
INSERT INTO Table_B (ID,FirstName,LastName)
SELECT TOP 10 ROW_NUMBER() OVER (ORDER BY a.name) RowID,
'Bob',
CASE WHEN ROW_NUMBER() OVER (ORDER BY a.name)%2 = 1 THEN 'Smith'
ELSE 'Brown' END
FROM sys.all_objects a
CROSS JOIN sys.all_objects b
GO
SELECT TOP 10 ROW_NUMBER() OVER (ORDER BY a.name) RowID,
'Bob',
CASE WHEN ROW_NUMBER() OVER (ORDER BY a.name)%2 = 1 THEN 'Smith'
ELSE 'Brown' END
FROM sys.all_objects a
CROSS JOIN sys.all_objects b
GO
INSERT INTO Table_B (ID,FirstName,LastName)
SELECT TOP 10 ROW_NUMBER() OVER (ORDER BY a.name) RowID,
'Bob',
CASE WHEN ROW_NUMBER() OVER (ORDER BY a.name)%2 = 1 THEN 'Smith'
ELSE 'Brown' END
FROM sys.all_objects a
CROSS JOIN sys.all_objects b
GO
-- Add Computed Column
ALTER TABLE dbo.Table_A ADD
SerialNo AS POWER(LEN(LEFT((LastName+CAST(ID AS VARCHAR(100))),3)), 12)+ID
GO
ALTER TABLE dbo.Table_B ADD
FullName AS (FirstName +' '+ LastName)
GO
SerialNo AS POWER(LEN(LEFT((LastName+CAST(ID AS VARCHAR(100))),3)), 12)+ID
GO
ALTER TABLE dbo.Table_B ADD
FullName AS (FirstName +' '+ LastName)
GO
-- See the result
SELECT *
FROM dbo.Table_A
WHERE SerialNo = 531450
GO
SELECT *
FROM dbo.Table_B
WHERE FullName = 'Bob Brown'
GO
FROM dbo.Table_A
WHERE SerialNo = 531450
GO
SELECT *
FROM dbo.Table_B
WHERE FullName = 'Bob Brown'
GO
-- Clean up Database
DROP TABLE Table_A
DROP TABLE Table_B
GO
DROP TABLE Table_B
GO
Post a Comment