Peristiwa
31 Mac, 11 PTG - 2 Apr, 11 PTG
Acara pembelajaran SQL, Fabric dan Power BI terbesar. 31 Mac - 2 April. Gunakan kod FABINSIDER untuk menjimatkan $400.
Daftar hari iniPelayar ini tidak lagi disokong.
Naik taraf kepada Microsoft Edge untuk memanfaatkan ciri, kemas kini keselamatan dan sokongan teknikal yang terkini.
Applies to:
SQL Server
Azure SQL Database
Azure SQL Managed Instance
SQL database in Microsoft Fabric
A computed column is a virtual column that isn't 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 SQL Server by using SQL Server Management Studio (SSMS) or Transact-SQL (T-SQL).
A computed column can't be used as a DEFAULT
or FOREIGN KEY
constraint definition or with a NOT NULL
constraint definition. However, if the 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
, a computed column defined as a + b
might be indexed, but computed column defined as a + DATEPART(dd, GETDATE())
can't be indexed, because the value might change in subsequent invocations.
A computed column can't be the target of an INSERT or UPDATE statement.
SET QUOTED_IDENTIFIER
must be ON
when you're creating or changing indexes on computed columns or indexed views. For more information, see SET QUOTED_IDENTIFIER (Transact-SQL).
Requires ALTER permission on the table.
In Object Explorer, expand the table for which you want to add the new computed column. Right-click Columns and select New Column.
Enter the column name and accept the default data type (nchar(10)). The Database Engine determines the data type of the computed column by applying the rules of data type precedence to the expressions specified in the formula. For example, if the formula references a column of type money and a column of type int, the computed column will be of type money because that data type has the higher precedence. For more information, see Data Type Precedence (Transact-SQL).
In the Column Properties tab, expand the Computed Column Specification property.
In the (Formula) child property, enter the expression for this column in the grid cell to the right. For example, in a SalesTotal
column, the formula you enter might be SubTotal+TaxAmt+Freight
, which adds the value in these columns for each row in the table.
Penting
When a formula combines two expressions of different data types, the rules for data type precedence specify that the data type with the lower precedence is converted to the data type with the higher precedence. If the conversion is not a supported implicit conversion, the error Error validating the formula for column column_name.
is returned. Use the CAST
or CONVERT
function to resolve the data type conflict. For example, if a column of type nvarchar is combined with a column of type int, the integer type must be converted to nvarchar as shown in this formula ('Prod'+CONVERT(nvarchar(23),ProductID))
. For more information, see CAST and CONVERT (Transact-SQL).
Indicate whether the data is persisted by choosing Yes or No from the dropdown for the Is Persisted child property.
On the File menu, select Save table name.
The following example creates a table with a computed column that multiplies the value in the QtyAvailable
column times the value in the UnitPrice
column.
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 ProductID, QtyAvailable, UnitPrice, InventoryValue
FROM dbo.Products;
-- Update values in the table.
UPDATE dbo.Products
SET UnitPrice = 2.5
WHERE ProductID = 1;
-- Display the rows in the table, and the new values for UnitPrice and InventoryValue.
SELECT ProductID, QtyAvailable, UnitPrice, InventoryValue
FROM dbo.Products;
The following example adds a new column to the table created in the previous example.
ALTER TABLE dbo.Products ADD RetailValue AS (QtyAvailable * UnitPrice * 1.5);
Optionally, add the PERSISTED argument to physically store the computed values in the table:
ALTER TABLE dbo.Products ADD RetailValue AS (QtyAvailable * UnitPrice * 1.5) PERSISTED;
The following example modifies the column added in the previous example.
ALTER TABLE dbo.Products DROP COLUMN RetailValue;
GO
ALTER TABLE dbo.Products ADD RetailValue AS (QtyAvailable * UnitPrice * 1.5);
GO
Peristiwa
31 Mac, 11 PTG - 2 Apr, 11 PTG
Acara pembelajaran SQL, Fabric dan Power BI terbesar. 31 Mac - 2 April. Gunakan kod FABINSIDER untuk menjimatkan $400.
Daftar hari iniLatihan
Modul
Add calculated tables and columns to Power BI Desktop models - Training
By the end of this module, you'll be able to add calculated tables and calculated columns to your semantic model. You'll also be able to describe row context, which is used to evaluated calculated column formulas. Because it's possible to add columns to a table by using Power Query, you'll also learn when it's best to create calculated columns instead of Power Query custom columns.