- Table of Contents
- About the Authors
- Tell Us What You Think!
- Part 1: Week 1 At a Glance
- Day 1. Introduction to SQL Server 7.0 and Relational Databases
- Day 2. Installing SQL Server 7.0
- Day 3. Using the SQL Server 7.0 Tools and Utilities
- Day 4. Creating and Implementing Databases, Files, and Filegroups
- Day 5. Using SQL Server Login and User Security
- Day 6. Working with Permissions
- Day 7. Implementing Backups in SQL Server 7.0
- Part 2: Week 2 At a Glance
- Day 8. Restoring SQL Server Databases
- Day 9. Creating Tables
- Day 10. Using SQL Server 7.0 Data Transformation Services
- Day 11. Retrieving Data with Queries
- Day 12. Data Modification with Queries
- Day 13. Enhancing Performance with Indexing
- Day 14. Ensuring Data Integrity
- Part 3: Week 3 At a Glance
- Day 15. Working with Views, Stored Procedures, and Triggers
- Day 16. Programming SQL Server 7.0
- Day 17. Understanding Replication Design and Methodologies
- Day 18. Implementing Replication Methodologies
- Day 19. Using the SQL Server Agent
- Day 20. Configuring and Monitoring SQL Server 7.0
- Day 21. Integrating SQL Server and the World Wide Web
- Appendix A. Answers
A computed column is an exciting new feature in SQL Server 7.0. A computed column does not store computed data—rather, it stores the expression used to compute the data. For example, I might create a computed column on my table called Total with an expression of Total AS price * quantity.
The expressions you store can be created from a noncomputed column in the same table, constants, functions, variables, and even names. Your computed column will automatically compute the data when it is called in a SELECT, WHERE, and ORDER BY clauses of a query (which you examine on Day 11, "Retrieving Data with Queries"). They can also be used with regular expressions.
There are a few rules to follow when you are dealing with computed columns:
- Columns referenced in the computed-column expression must be in the same table.
- The computed-column expression cannot contain a subquery.
- Computed columns cannot be used as any part of keys or indexes; this includes Fkeys, Pkeys, and unique indexes.
- A computed column cannot have a DEFAULT constraint attached to it.
- Computed columns cannot receive INSERT or UPDATE statements.
Here are a couple of examples using computed columns:
CREATE TABLE tblOrder ( OrdID int NOT NULL, Price money NOT NULL, Qty smallint NOT NULL, Total AS Price * Qty )
This first table has a computed column called Total, which is made up of the price * qty fields.
CREATE TABLE tblPrintInvoice ( InvoiceID int NOT NULL, InvDate datetime NOT NULL, PrintDate AS DateAdd(day,30, InvDate) )
In this example, you have a computed column called PrintDate that uses the DateAdd function to add 30 days to the InvDate column.