Page 243 - Open Soource Technologies 304.indd

Unit 10: Database Fundamentals and Connectivity Option

rsOrders.MoveNext
Loop
lblTotalOrders = grandTotal
In this case, a recordset is retrieved that contains all the orders (rsOrders). Based on some custom
business logic, the details for certain orders need to be retrieved. We can see that an additional
recordset known as rsDetails is opened to retrieve the details, using the same connection as
rsOrders, while rsOrders is still open and being used. In the past, this wouldn’t have been
possible. This allows we to create a single global connection object for application, and to use
this throughout application for all access to a given database.
10.5.5 Varchar (MAX)
In previous versions of SQL Server, we would use a VARCHAR column to store text. The
problem was that a VARCHAR column was limited to 8000 characters. If we needed to store
more than that, we had to use the TEXTtype, and TEXT was significantly harder to work with.
SQL Server 2005 (and SQL Server 2005 Express) supports a new data type known as VARCHAR
(MAX). This type can be treated exactly like a VARCHAR, and it can store 2 GB of data per row.
For binary data, SQL Server 2005 Express also includes a VARBINARY (MAX) type, which
replaces the IMAGE type.
10.5.6 Common Table Expressions (CTE)

It’s common, when working with data, to need to create intermediate results. These can show
up in the form of temporary tables, or views. Often, this intermediate information is needed only
while a given calculation is being done, and it can then be discarded.With SQL Server 2005
Express, a new mechanism for these kinds of scenarios is the common table expression (CTE).
Simply put, a CTE lets we treat the results of a select statement as a table. To look at a concrete
example, consider the CTE in Listing 3, which returns the region with the most territories.
Listing 3. Common table expression (CTE) with SQL Server 2005
WITH CountTerritories(RegionID, TerritoryCount) AS
SELECT
RegionID,
count(*) AS TerritoryCount
FROM Territories
GROUP BY RegionID
)
SELECT * FROM CountTerritories WHERE TerritoryCount =
(
SELECT max(TerritoryCount) FROM CountTerritories
)
Here, Count Territories is created as a sort of temporary view that’s used just for this single select
statement. Once created, it can be queried against in the subsequent SELECT statement, as though
it were a table.

LOVELY PROFESSIONAL UNIVERSITY 237