Summary of Useful Features in SQL Server 2008

一时失言乱红尘 2021-11-29 15:34 242阅读 0赞

### Summary of Useful Features in SQL Server 2008 ###

SQL Server 2008 will be coming out sometime this summer (in theory). At last week's [TechFuse][] event in Minneapolis, and in blogs I sometimes read, I've started to pick up on a number of useful features and improvements that should make one strongly consider upgrading when the Release to Market (RTM) is ready (certainly this is not all-inclusive):

#### Encryption at rest ####

Whole databases can be encrypted with a master key (which can be stored in an HSM), rather than encrypting by each column. No code changes are required, hence this feature is called Transparent Data Encryption. It gives true encryption of data at rest, included backup files.

*  Blog: [SQL Server 2008 Transparent Data Encryption][]
 *  Books Online: [Understanding Transparent Data Encryption (TDE)][Understanding Transparent Data Encryption _TDE]

#### Filtered indexes ####

An index can be created with a filter (i.e. index all rows where MyDateField is not null), thus making much more meaningful indexes &rarrow; improved performance.

*  Blog: [SQL Server 2008 filtered indexes in 5 minutes][]
 *  Books Online: [Filtered Index Design Guidelines][]

#### Sparse columns support ####

There are times when you want to create say 20 columns, only a portion of which will be used at any given time (perhaps there are multiple applications using the table, and they each have a few unique needs). If you create all these columns, your table becomes a little unwieldy in many ways. You can use an XML column to store just the subset required, or in 2008 you can use the new sparse columns feature. This will improve indexing and manageability.

*  Blog: [SQL Server 2008 Sparse Columns][]
 *  Books Online: [SQL Server 2008 Sparse Columns][SQL Server 2008 Sparse Columns 1]

#### Performance Data Collector ####

The new Performance Data Collector stores query information in a data warehouse, making it easy to answer questions like "whose query was hogging the resources this morning, causing my job to get a timeout?"

*  Blog: [Creating a custom data collection in SQL Server 2008][]
 *  Books Online: [Introducing the Data Collector][]

#### Resource Governor ####

Allows the administrator to allocate resource slices to different users or classes of users; thus for instance you could specify that the core processing user would always get (if needed) a minimum of 40% of the CPU & RAM, and you could specify that the marketing users (once they have beefy report models to work with) never get more than 10% of the resources. For example.

*  Blog: [SQL 2008 Resource Governor][]
 *  Books Online: [Resource Governor Monitoring][]

#### Report Builder ####

The Reporting Services Report Builder has been entirely redesigned — now looks like an Office 2007 application. The table and matrix controls have been replaced with a far more flexible [tablix][] control that allows you to do all kinds of cool things. There is still a [Visual Studio-integrated][] Report Designer, though the things I've heard had left me confused on that point (BOL may be out of date, or the presentation at TechFuse was wrong?). The blog link below clears up the confusion.

*  Blog: [Transmissions from the Satellite Heart (What's up with Report Builder?) ][Transmissions from the Satellite Heart _What_s up with Report Builder_]
 *  [Books Online: ][Transmissions from the Satellite Heart _What_s up with Report Builder_]

#### Integration Services ####

These upgrades are basically in the area of performance improvements, particularly with regard to the Lookup data flow task. Other improvements / new functionality: ADO.Net as default data source (rather than OLEDB); data profiling task; C\# as new / default language for scripting tasks.

*  Blog: [SSIS: Big improvements to Lookup in SQL Server 2008][SSIS_ Big improvements to Lookup in SQL Server 2008]
 *  Blog: [C\# comes to the script task/component][C_ comes to the script task_component]
 *  Blog: [What's new in SQL Server 2008 for SSIS - Part one][What_s new in SQL Server 2008 for SSIS - Part one]
 *  Blog: [What's new in SQL Server 2008 for SSIS - Part two][What_s new in SQL Server 2008 for SSIS - Part two]

#### Transact SQL / Database Engine ####

Finally, we get to the heart of SQL Server: the structured query language. Supposedly this thing is much faster. There is some new syntax available: `GROUPING SETS` for enhanced BI-type queries; `MERGE` to perform inserts, updates, and deletes in one statement; the`+=` operator, and more. There's also the new Change Data Capture for enhanced auditability and better incremental bulk loads.

*  Blog: [GROUPING SETS in SQL Server 2008][]
 *  Blog: [SQL Server 2008 'Katmai' Change Data Capture (CDC) in SSIS][SQL Server 2008 _Katmai_ Change Data Capture _CDC_ in SSIS]
 *  Books Online: [How Change Data Capture Works][]
 *  Books Online: [Using GROUP BY with ROLLUP, CUBE, and GROUPING SETS][Using GROUP BY with ROLLUP_ CUBE_ and GROUPING SETS]
 *  Books Online: [MERGE][]

转载于:https://www.cnblogs.com/fanweixiao/archive/2008/03/19/1113492.html

[TechFuse]: http://www.nhmn.com/techfuse/
[SQL Server 2008 Transparent Data Encryption]: http://edge.technet.com/Media/580/
[Understanding Transparent Data Encryption _TDE]: http://msdn2.microsoft.com/en-us/library/bb934049%28SQL.100%29.aspx
[SQL Server 2008 filtered indexes in 5 minutes]: http://www.statisticsio.com/Home/tabid/36/articleType/ArticleView/articleId/68/SQL-Server-2008-filtered-indexes-in-5-minutes.aspx
[Filtered Index Design Guidelines]: http://msdn2.microsoft.com/en-us/library/cc280372%28SQL.100%29.aspx
[SQL Server 2008 Sparse Columns]: http://blogs.technet.com/andrew/archive/2008/02/28/sql-server-2008-sparse-columns.aspx
[SQL Server 2008 Sparse Columns 1]: http://www.safnet.com/SQL%20Server%202008%20Sparse%20Columns
[Creating a custom data collection in SQL Server 2008]: http://www.statisticsio.com/Home/tabid/36/articleType/ArticleView/articleId/57/Default.aspx
[Introducing the Data Collector]: http://msdn2.microsoft.com/en-us/library/bb677248%28SQL.100%29.aspx
[SQL 2008 Resource Governor]: http://portal.sqltrainer.com/2007/12/sql-2008-resource-governor.html
[Resource Governor Monitoring]: http://msdn2.microsoft.com/en-us/library/bb933941%28SQL.100%29.aspx
[tablix]: http://msdn2.microsoft.com/en-us/library/bb934258%28SQL.100%29.aspx
[Visual Studio-integrated]: http://msdn2.microsoft.com/en-us/library/ms159253%28SQL.100%29.aspx
[Transmissions from the Satellite Heart _What_s up with Report Builder_]: http://blogs.msdn.com/bwelcker/archive/2007/12/11/transmissions-from-the-satellite-heart-what-s-up-with-report-builder.aspx
[SSIS_ Big improvements to Lookup in SQL Server 2008]: http://blogs.conchango.com/jamiethomson/archive/2007/08/21/SSIS_3A00_-Big-improvements-to-Lookup-in-SQL-Server-2008.aspx
[C_ comes to the script task_component]: http://blogs.conchango.com/jamiethomson/archive/2007/06/09/Katmai_5C00_SSIS_3A00_-C_2300_-comes-to-the-script-task_2F00_component.aspx
[What_s new in SQL Server 2008 for SSIS - Part one]: http://blogs.msdn.com/mattm/archive/2008/01/10/what-s-new-in-sql-server-2008-for-ssis-part-one.aspx
[What_s new in SQL Server 2008 for SSIS - Part two]: http://blogs.msdn.com/mattm/archive/2008/01/22/what-s-new-in-sql-server-2008-for-ssis-part-two.aspx
[GROUPING SETS in SQL Server 2008]: http://blogs.msdn.com/craigfr/archive/2007/10/11/grouping-sets-in-sql-server-2008.aspx
[SQL Server 2008 _Katmai_ Change Data Capture _CDC_ in SSIS]: http://weblogs.sqlteam.com/derekc/archive/2008/01/28/60469.aspx
[How Change Data Capture Works]: http://msdn2.microsoft.com/en-us/library/bb522657%28SQL.100%29.aspx
[Using GROUP BY with ROLLUP_ CUBE_ and GROUPING SETS]: http://msdn2.microsoft.com/en-us/library/bb522495%28SQL.100%29.aspx
[MERGE]: http://msdn2.microsoft.com/en-us/library/bb510625%28SQL.100%29.aspx