Tip 7: Other New SQL Server 2008 Enhancements
While the first six tips in this article focused on specific language and data type enhancements in SQL Server 2008, there are other significant enhancements in SQL Server 2008.
- XML enhancements: Suppose you need to validate a single XML document against different schemas. SQL Server 2008 provides a new keyword (processContents="lax") to relax validations on undefined node elements that would otherwise invalidate the XML document. You can also use the new date and time data types in SQL Server 2008 XML documents.
- Filtered indexes: This works exactly the way it sounds. You can define an index that only contains references to the rows that meet your filter condition. For large databases, this can have a dramatic impact on performance.
- Sparse columns: Once again, another enhancement that is almost self-explanatory. You can define columns in a table as sparse, and the columns will take up almost no space if the value for the row is NULL. However, a sparse column will take up four more bytes if the value contains data. SQL Server 2008 Books Online contains more information on guidelines for when sparse columns are beneficial.
- Spatial Computing: SQL Server 2008 contains new GEOMETRY ("Flat Earth Model") and GEOGRAPHY ("Ellipsoidal model") data types. These are actually abstract data types you can use to work with more specific spatial data types (e.g., point, linestring, polygon, multipoint, etc.). Typically you would use the GEOMETRY abstract type for two-dimensional problems, and you would use the GEOGRAPHY abstract type for spherical data with latitude/longitude coordinates.
One final note on spatial computing: ESRI is using the new spatial features in SQL Server 2008 as part of the next scheduled release of their ArcGIS Geography Information System. For more information, read this press release
Tip 8: SQL Server Reporting Services 2008—A Lean and Mean Architecture, sans IIS
SQL Server Reporting Services 2005 was a big leap forward in terms of functionality from SSRS 2000. However, many developers/users experienced two major challenges:
- SSRS 2005 required Internet Information Services (IIS) to run.
- SSRS 2005 is extremely resource intensive, so much so that some IT shops would deploy SSRS 2005 on a different box than the SQL Server database.
Microsoft has rewritten SSRS 2008 from the ground up with goals of a simpler and more scalable architecture. As a result, you will find these major changes:
- First, SSRS 2008 no longer requires IIS. SSRS 2008 uses the http.sys driver and listens for report requests through http.sys. Not only does this reduce deployment headaches, it also reduces server overhead.
- All SSRS 2008 components work under one application pool and one Windows service.
- SSRS 2008 utilizes memory more efficiently, especially when working with reports that contain large sets of data. Additionally, SSRS 2008 will often load the first page of a report faster than SSRS 2005.
Tip 9: The New SSRS 2008 Designer Environment
|Figure 2. New Report Options: New SSRS 2008 options dialog box (e.g., Text Box Properties).|
Microsoft has implemented a number of significant changes and improvements to the overall design-time experience in SSRS 2008:
- In SSRS 2005, non-programmers had to use the Visual Studio 2005 shell (Business Intelligence Development Studio, "BIDS") to create SSRS reports. Microsoft has built a stand-alone report designer for SSRS 2008, one that has an Office 2007-style look and feel. For those who prefer to use Visual Studio, you'll see the same UI enhancements in the SSRS 2008 Project Template.
- The tabbed dialog boxes for various report options have been replaced with new dialogs, where options are more logically organized. Figure 2 shows an example.
- The report design area has been enhanced for clarity. Figure 3 shows the new design area, which includes new row/column group panes—this makes it much easier to visualize report groupings. Also note in Figure 3 that available report fields and "special fields" are available in the report elements task list on the left. Figure 4 shows that basic detail lines can now start on the same row as the nearest group.
Tip 10: Tablix Means Never Having to Say "The Control Won't Let Me Do This"
|Figure 3. New SSRS 2008 Report Designer: You can see report data on the left and row/column groups below.||
|Figure 4. Enhanced Details: Basic detail lines can start on the same row as the nearest group.||
SSRS 2005 contained two report controls called "Table" and "Matrix," that most reports will use to generate listings of data. The Table control allowed you to specify as many columns as you wanted, so long as the columns belonged to one horizontal group. The Matrix control allowed you to create a crosstab/pivot effect of a variable number of entities across the top (years, quarters, etc.)—as long as the entities belonged to the same group.
The strength of each control was a deficiency in the other. Often, developers wanted the functionality of the matrix control, but still wanted to place specific columns "free-form" either before, in between, or after horizontal column groups. Fortunately, Microsoft has implemented functionality that takes the best features of the table and matrix, and has called it the Tablix control.
If you've already experimented with any of the CTPs for SQL Server 2008, you may already know that the SSRS 2008 toolbox does not contain an actual control called Tablix. Microsoft has essentially expanded the functionality of both the Table and Matrix controls, so that you receive the same enhanced benefits, regardless of which control you initially selected.
shows one of the primary new benefits of the Tablix functionality—the ability to insert new columns either inside or outside of an existing column group, and either to the left or right. So now, if you have an existing measure you wish to add to a Matrix report (e.g., grand total freight in the same horizontal band as a column group for sales by quarter or year), you can do so! Figure 6
shows a report sample using the Tablix functionality.
Tip 11: Baker's Dozen Spotlight: New Charting Features in SSRS 2008 (Courtesy of Dundas)
|Figure 5. Tablix Control: This control provides the ability to add columns or rows either inside or outside a group.||
|Figure 6. Output from a Tablix Control: The figure shows a "free-form" column (Freight) outside the Year group.||
Charting in SSRS 2005 was, in my opinion, a decent but not spectacular experience. However, you'll want a large supply of pens to "write home" about charting in SSRS 2008. Microsoft purchased charting functionality from Dundas Data Visualization Inc.—as a result, SSRS 2008 contains the following:
- New chart types (see Figure 7 and Figure 8) show new standard chart options and new gauge report types).
|Figure 7. Better Charts: The figure shows the new chart dialog box and new chart types.||
|Figure 8. Dundas Gauge Reports: Here's an example of the new Gauge report types from Dundas.||
- Redesigned chart options dialogs (see Figure 9).
|Figure 9. Chart Series Properties: You set Chart Series options from this dialog.||
|Figure 10. Dual Y-axis Chart: Here's a bar chart with a dual Y-axis, so the chart can plot both dollar sales and a percentage.||
- Enhanced charting capabilities, such as the ability to build a chart with a dual-Y axis. Figure 10 shows a chart that plots dollar sales as one Y-axis (vertical bars) and also plots sales ratio percentage as a second Y-axis (lines). Note the option back in Figure 9 for a secondary axis.
- Support for a calculated series (e.g., if you want to take plotted data and chart a moving average).
- Support for custom CSS themes for the SSRS report preview/toolbar—an example URL would be:
The Dundas Chart product continues as a separate product. You can get more information here
Tip 12: New Exporting Capabilities in SSRS 2008
|Author's Note: As of the current SQL Server 2008 CTP, SSRS 2008 does not support any custom code you may have previously written for Dundas Chart.
SSRS 2008 now supports Microsoft Word's Rich Text rendering, along with all of the prior export formats (Excel, PDF, etc.) Microsoft has also improved report delivery to MOSS (Microsoft Office SharePoint Services).
Tip 13: Better Integration between SSRS 2008 and SharePoint
SSRS 2008 contains an add-in for Microsoft SharePoint Integrated mode. This process will include/respect data-driven subscriptions. You'll be able to cleanly display SSRS 2008 reports on a SharePoint site, right beside other Web Parts showing KPIs (Key Performance Indicators), Excel pivot tables, and other Business Intelligence Web Parts (e.g., PerformancePoint Server dashboards).
You can find the entire source code
on my Web site. Check my blog
for any follow-up tips, additional resources, and notes on Baker's Dozen articles.