Using Derived Columns

SQL Server supports the creation of derived columns. Derived columns are those that get their values from other columns in a table. For example, assume that you have a table called "Orders". Let's assume that the order identification number that is to be used for all external communications must be prefixed with an "ORD" and a 4 digit number, which is the "Orders" table primary key. This is quite a common requirement and most applications in the past used to "manage" this column independently and write application code that will generate this column. With SQL Server 2000 derived columns, this is very simple to implement. Here is how we can achieve this:

CREATE TABLE Orders
(
	InternalOrderID		INT IDENTITY(1,1) NOT NULL,
	ExternalOrderID		AS 'ORD' + RIGHT('0000' + CAST(InternalOrderID AS VARCHAR(10)), 4),
	OrderValue			DECIMAL (10,2)
)

In the above CREATE TABLE snippet, we can see that the "ExternalOrderID" is a derived column, based on the rules as we said above. Now when you insert data into the table, the columns is auto generated. For example, if we issue the following INSERT statement:


INSERT INTO orders VALUES (100.0)
SELECT * FROM orders

We can see that the "ExternalOrderID" has the value ORD0001. That's a pretty good functionality, right!! :-)

As another example, consider that you have a date column and you want to store the date and time seperately. Here is how you can do it in SQL Server.


CREATE TABLE Orders
(
	InternalOrderID		INT IDENTITY(1,1) NOT NULL,
	ExternalOrderID		AS 'ORD' + RIGHT('0000' + CAST(InternalOrderID AS VARCHAR(10)), 4),
	OrderValue			DECIMAL (10,2),
	OrderDateTime		DATETIME,
	OrderDate			AS CONVERT(VARCHAR(10), OrderDateTime, 101),
	OrderTime			AS CONVERT(VARCHAR(10), OrderDateTime, 108)
)

The two derived columns in the end of the above snippet split the date field into the date and time components using the CONVERT function. Now, you can insert data into the table as follows:


INSERT INTO orders VALUES (100.0, GETDATE())
SELECT * FROM orders

Check out the output and it has the information that we want...One immediate question that comes to our mind is "can I index derived columns??". Yes, you can, but subject to the following conditions.

  • The computed column must be deterministic. Expressions are deterministic if they always return the same result for a given set of inputs.
  • The ANSI_NULL connection-level option must be set to ON when the CREATE TABLE statement is executed.
  • The expression defined for the computed column cannot evaluate to the text, ntext, or image data types.
  • The connection on which the index is created, and all connections attempting INSERT, UPDATE, or DELETE statements that will change values in the index, must have six SET options set to ON and one option set to OFF. The optimizer ignores an index on a computed column for any SELECT statement executed by a connection that does not have these same option settings. The following options must be set to ON: ANSI_NULLS, ANSI_PADDING, ANSI_WARNINGS, ARITHABORT, CONCAT_NULL_YIELDS_NULL, QUOTED_IDENTIFIER and the NUMERIC_ROUNDABORT must be set to OFF.

Home