Skip to main content

Posts

Showing posts from March, 2012

Google Search

Custom Search

Live Search

Yahoo Search


Search the web Search this site

Dynamically Creating Computed Columns in SQL

If you are into programming you should have definitely worked with calculated fields in SQL. But for others I will briefly explain what they are.Calculated columns are columns which depend on other columns. It gets its value by calculating which can involve values of other columns. The calculation formula is the only thing stored in the column.So each time the column is referenced the calculation is done. But if you use the keyword “persisted” while creating the column then the values will be kept in the table. Whenever a referenced value is updated the computed column is also automatically updated to highlight the change. Also by using persisted you can index a computed column.You can create a table with persisted computed column as follows.CREATETABLE Customer( CustomerId INTIDENTITY(1, 1)NOTNULL, CustomerFirstName NVARCHAR(100)NOTNULL, CustomerLastName NVARCHAR(100)NOTNULL, CustomerFullName AS CustomerFirstName +' '+ CustomerLastName PERSISTED) One thing to remember is that …