Databases Reference
In-Depth Information
Next, create a unique clustered index on the view:
CREATE UNIQUE CLUSTERED INDEX IDX_VIEW_TestUsers
ON vTestUsersType
(UserTypeKey, AgeGroup, Name, UserType)
Et voilà. When you run the statement again, you see a beautiful execution plan like the one in Figure
11-14
.
Because the view contains all the necessary columns, and the clustered index contains all the columns of the view,
you obtain the fastest possible data-retrieval technique, next to caching.
Figure 11-14.
Indexed view example
Stored Procedures
You've seen various ways to tune your statements and improve execution plans. However, keep in mind that you also
have stored procedures at your disposal.
Stored procedures can give you an edge if you need to execute logic that requires a large volume of data. Because
you know that returning lots of data turns into a performance problem in SQL Database, you can place the business
logic that needs the data in a stored procedure, and have the procedure return a status code. Because you aren't
charged for CPU time, this becomes an affordable option.
Stored procedures can also be a security tool, allowing you to proxy the calls to underlying tables through a
procedure and never allowing direct access to the tables.
Imagine that you need to calculate the cost of an item; however, in order to calculate the cost, you must loop to
obtain certain values and perform advanced operations. You can make a call from your application and calculate the
cost in the application code as follows:
float cost = 0.0; // the total cost
int id = 15; // the product category
string sql = "SELECT * FROM category WHERE catergoryId = " + id.ToString();
SqlConnection conn = new SqlConnection(connString);
SqlCommand cmd = new SqlCommand(sql, conn);
cmd.CommandType = CommandType.Text;
conn.Open();
SqlDataReader dr = cmd.ExecuteReader();
try
{
while (dr.Read())
