1. The View has to be created with SCHEMABINDING option. In this case, the DB engine will not allow you to change the underlying table schema.
2. The View can't contain any nondeterministic function, DISTINCT clause and subquery.
3. The underlying tables in the View must have clustered index (Primary keys)

1. Create the function (if it does not exists already) and make sure that this function is deterministic. Add SCHEMABINDING option in the function definition. Example:
   

   CREATE FUNCTION [dbo.ufnGetLineTotal]
   
   (
   
   -- Add the parameters for the function here
   
   @UnitPrice [money],
   
   @UnitPriceDiscount [money],
   
   @OrderQty [smallint]
   
   )
   
   RETURNS money
   
   WITH SCHEMABINDING
   
   AS
   
   
   
   BEGIN
   
       return (((@UnitPrice*((1.0)-@UnitPriceDiscount))*@OrderQty))
   
   END

2. Add a computed column in your desired table and specify the function with parameters as the value of the computed column.
3. Create an index on the computed column.

CREATE PRIMARY XML INDEX index_name ON <object> ( xml_column )

CREATE XML INDEX

index_name

ON <object> ( xml_column )

USING XML INDEX primary_xml_index_name

FOR { VALUE | PATH | PROPERTY }

1. Path Secondary XML indexes: useful when using the .exist() methods to determine whether a specific path exists.
2. Value Secondary XML indexes: used when performing value-based queries where the full path is unknown or includes wildcards.
3. Property Secondary XML indexes: used to retrieve property values when the path to the value is known.

1. Creating the scheduled operation
   Here are the steps to create a scheduled operation in SQL Server that periodically populates a history table on a specified schedule.
   
   • Make sure that SQL Server Agent is running. To do this, launch the SQL Server Configuration Manager, click on the SQL Server 2005 Services and start the SQL Server Agent by right clicking on it.
     
     
   • Expand SQL Server Agent node in the object explorer and click on the Job node to create a new job. In the General tab, provide job name and descriptions.
     
     
   • On the Steps tab, click on the New button to create a new job step. Provide a name for the step and also provide TSQL, that would load the history table with the daily sales data, along with providing Type as Transact-SQL script(T-SQL). Press OK to save the Step.
     
     
   • Go to the Schedule tab and click on New button to specify a job schedule.
     
     
   • Click the OK button to save the schedule and also to apply the schedule on the specified job.
2. Perform expensive calculations in advance in data INSERT/UPDATE, simplify SELECT query
   In most of the cases in your application you will see that data insert/update operations occur one by one, for each record. But, data retrieval/read operations involve multiple records at a time. So, if you have a slowly running read operation (select query) that has to do complex calculations to determine a resultant value for each row in the big result set, you can consider doing the following:
   
   • Create an additional column in a table that will contain the calculated value.
   • Create a trigger for Insert/Update events on this table and calculate the value there using the same calculation logic that was in the select query earlier. After calculation, update the newly added column value with the calculated value.
   • Replace the existing calculation logic from your select query with the newly created field.
   
   After doing this, the Insert/Update operation for each record in the table will be a bit slower because the trigger will now be executed to calculate a resultant value, but the data retrieval operation should run faster than previous. Because while the SELECT query executes, the database engine does not have to process the expensive calculation logic any more for each row.