Understanding Views

The CREATE VIEW SQL statement creates a view. The simple format of the CREATE VIEW statement is as follows :

CREATE VIEW

viewname AS selectstatement

where viewname is the name of the view and selectstatement is the SELECT statement used to select data from one or more table.

The following CREATE VIEW statement creates myView with records as a result of SELECT myName FROM myTable :

CREATE VIEW myView AS SELECT myName FROM myTable

The following two CREATE VIEW statements create the View1 view and the View2 view from the Orders table based on different criteria:

CREATE VIEW "View1" AS SELECT OrderID, OrderDate, ShippedDate FROM Orders WHERE (Freight < 10) CREATE VIEW "View2" AS SELECT OrderID, OrderDate, ShippedDate FROM Orders WHERE (Freight > 1000)

You can also create views by selecting data from multiple tables. For example, the following view selects data from the Products and Categories tables. By using views this way, you can essentially "save" frequently used queries:

CREATE VIEW "Products by Category" AS SELECT Categories.CategoryName, Products.ProductName, Products.QuantityPerUnit, Products.UnitsInStock, Products.Discontinued FROM Categories INNER JOIN Products ON Categories.CategoryID = Products.CategoryID WHERE Products.Discontinued <> 1 ORDER BY Categories.CategoryName, Products.ProductName

Note

You may have heard the term materialized view and wondered what it was. Basically, a materialized view is a view that's been stored (in many ways defeating the original goal of views as being virtual tables, but that's another issue). SQL Server calls its materialized views indexed views , and although you can create indexed views with any version of SQL Server, only the enterprise, developer, and evaluation editions use them. A SQL Server indexed view is essentially a view that has had a set of unique values "materialized" into the form of a clustered index, thereby providing a quick lookup in terms of pulling the information behind a view together.

Using SQL Server's SELECT...FOR XML Clause

Most of today's databases support Extensible Markup Language (XML). In SQL Server, for example, you can execute SQL queries to return results as XML rather than standard rowsets. You can execute these queries directly or from within stored procedures. To retrieve results directly, you use the FOR XML clause of the SELECT statement. Within the FOR XML clause, you specify one of three XML modes: RAW , AUTO , or EXPLICIT . (You can also work with XML data in Access, Oracle, MySQL, and other databases, but the techniques vary.)

For example, this SQL Server SELECT statement retrieves information from the Customers and Orders tables in the Northwind database:

SELECT Customers.CustomerID, ContactName, CompanyName, Orders.CustomerID, OrderDate FROM Customers, Orders WHERE Customers.CustomerID = Orders.CustomerID AND (Customers.CustomerID = "NALFKI" OR Customers.CustomerID = "NXYZAA") ORDER BY Customers.CustomerID FOR XML AUTO

Understanding SQL Injection

SQL injection is the process of using SQL queries and failing because of invalid user input. SQL injection is usually caused by the code written by developers. For example, let's say a form has a TextBox1 control and developers use the text of TextBox1 as one of the SQL query variables :

string str = "SELECT * FROM Table WHERE

Name

='" + TextBox1.Text + "'";

Now, what happens when Amie O'Donell enters her name in TextBox1? The execution of the SQL query will fail because of invalid SQL syntax. You have to be careful when building and executing direct SQL queries. You can simply avoid this by checking if the user input has an invalid character in it.

Another common scenario is when the database server is expecting a number value and the user enters a string value or vice versa. This could lead to a failure if you don't have a check in the code. For example, you could use the ToString method to make sure the input is a valid query (using the Convert class to convert from one data type to another).