Skip to main content

Computed Columns into Table -Sql Server

        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 2016 by using SQL Server Management Studio or Transact-SQL

CREATE TABLE dbo.Products 
(
    ProductID int IDENTITY (1,1) NOT NULL
  , QtyAvailable smallint
  , UnitPrice money
  , InventoryValue AS QtyAvailable * UnitPrice
);

-- Insert values into the table.

INSERT INTO dbo.Products (QtyAvailable, UnitPrice)  VALUES (25, 2.00), (10, 1.5);

-- Display the rows in the table.

SELECT * FROM dbo.Products; 


To add a new computed column to an  existing table
ALTER TABLE dbo.Products ADD RetailValue AS (QtyAvailable * UnitPrice * 1.35);

To Change an existing column to a computed column

ALTER TABLE dbo.Products DROP COLUMN RetailValue;
GO
ALTER TABLE dbo.Products ADD RetailValue AS (QtyAvailable * UnitPrice * 1.5);

Comments

Popular posts from this blog

What is RDBMS?

In our crucial programming world, RDBMS is stand for Relational Database Management Systems is based on the Relational Model that maintain data records and index in tables. Generally, the term "Relations" are maintained in RDMS.  In a relational database,store the data into collection of tables, which might be related by common fields (database table columns). RDBMS also provide relational operators to manipulate the data stored into the database tables. Most RDBMS use SQL as database query language. There are several objects are included in RDBMS like ,Indexes,Synonyms,Tables,Views Clusters,Constraints,Database links,Database triggers. Most popular RDBMS  are Oracle, MS-SqlServer, MySql,DB2. etc.

What are the difference between DDL, DML , DCL and TCL commands?

Database Command Types DDL Data Definition Language  (DDL) statements are used to define the database structure or schema.  -           CREATE - to create objects in the database -           ALTER - alters the structure of the database -           DROP - delete objects from the database -           TRUNCATE - remove all records from a table, including all spaces allocated for the records are removed -           COMMENT - add comments to the data dictionary -           RENAME - rename an object DML Data Manipulation Language  (DML) statements are used for managing data in database. DML commands are not auto-committed. It means changes made by DML command are not permanent to d...

Identify and Delete duplicate Records in SQL Server

      S cenario: I have accumulated data into SQL Server database from three different servers (oracle, O2 and oracle) for a financial reporting. After data accumulated , we found more duplicate rows; business requirement was to remove all duplicate rows because of final data will be import to another system. For this purpose, I want to write script to find out duplicate rows from my table. Finally, I want to delete only duplicate rows not original row. Original row means if i have total 2 rows those same or duplicate, so I want to delete only one row that is duplicate. Let’s go............................ Step 1: Create a student Table below Script: CREATE TABLE [Students] (     [ID] [int] NULL,     [Name] [varchar] ( 50 ) NULL,     [address] [varchar] ( 50 ) NULL,     [contact] [varchar] ( 50 ) NULL,     [email] [varchar] ( 50 ) NULL ...