Computed column

                Today when I was looking into the new features of SQL server 2005, I came across a concept of computed columns and thought of sharing with you all. Usually we use computed columns of dataset in application side with adds overhead to the application and potentially reducing the performance of the application. Computed columns in the dataset in the application side can be replaced with computed columns in the SQL server table.

A computed column is not a physical column in a table but computed from an expression that uses other columns in the same table. The data’s in the computed columns will not be saved physically in the table unless it is marked as PERSISTED. When a computed column is marked as persisted index can be created on it. For example computed columns could have the definition datediff(year,[DOB],getdate()).  The expression can be a non-computed column name, constant, function, variable and any combination of their connected by one or more operators. The expression cannot be a sub query or include an alias data types. A computed column cannot be used in INSERT and UPDATE statement. A computed column can be used as a foreign key  only if it is marked as PERSISTED. Enforcing check constraint, null or not null on the computed column also requires the column to be marked PERSISTED.

Example of using computed column:

CREATE TABLE [dbo].[TableFeatures](

      [ID] [int] NULL,

      [Name] [varchar](100) NULL,

      [DOB] [datetime] NULL,

    [AGE]  AS (datediff(year,[DOB],getdate()))

) ON [PRIMARY]

Example of insert statement:

INSERT INTO Tablefeatures VALUES(1,'sathya','1983-11-02')

INSERT INTO tablefeatures VALUES(2,'sathya','1981-11-02')

INSERT INTO tablefeatures VALUES(3,'sathya','1982-11-02')

Output of the select statement:

ID            Name    DOB                                                       Age

1              sathya   1983-11-02 00:00:00.000                25

2              sathya   1981-11-02 00:00:00.000                27

3              sathya   1982-11-02 00:00:00.000                26

Comments

Post a Comment

Popular posts from this blog

Displaying alert message from server side(ASP.NET1.1)

Public Sub ShowMessage(ByVal MessageText As String) MessageText = Replace(MessageText, """", "") Dim strScript As String = " " If (Not Page.IsStartupScriptRegistered("clientScript")) Then Page.RegisterStartupScript("clientScript", strScript) End If End Sub Technorati Tags: ASP.NET , ASP.NET1.1 , asp.net serverside message box
Read more

Row_Number()

  Yesterday I when I was looking into the enhancements in SQL server 2005 I discovered the below cool features and thought of sharing with your all. This new features will reduce the amount of coding and drastically improve the performance of complex SQL queries.  Conventionally we generate sequence numbers using complex logic or by using temp tables or table variable with identity columns, but with this new feature “ROW_NUMBER()” will allow developers to generate sequence numbers in a single line of code. Below I have attached an example. ROW_NUMBER()returns the sequence number of a row within a partition of a result set, starting at 1 for the first row in each partition. Syntax: ROW_NUMBER ( ) OVER ( [ <partition_by_clause> ] <order_by_clause> ) Example usage: CREATE TABLE RowNumTest (Name VARCHAR(50), DOB DATETIME, Age AS (DATEDIFF(YEAR,DOB,GETDATE()))) INSERT INTO RowNumTest (Name, DOB) VALUES('Sathya', '1983-11-02') INSERT INTO RowNumTes
Read more