What’s new in SQL 2008

I talk about SQL server every chance I get because of some the great features available (SSRS, SSIS, SSAS).  So I looked into SQL 2008 the first chance I got.  More than once I have been asked about the new features of SQL server 2008.  So here is a list I complied (not 100% of the features, just the ones I thought they were easy to sell)

Top new features:

  1. T-SQL Intellisense – Intellisense is now available for T-SQL. This allows for easier and quicker development.
  2. Date/Time data types – There is a new data type for just the date and there is a new data type for just the time. There is no longer the need to program around a datetime data type.
  3. Encrypted Backups – Backups can be encrypted to prevent data disclosure or tampering.
  4. Transparent Data Encryption – The entire database can be encrypted by the SQL engine. This encrypts all database data and log files for a database log. All indexes and tables are encrypted. No changes to applications are required.
  5. Memory Management improvements in Reporting Services – Running large reports will no longer consume all available memory.

 

List of some of the new features

Security/Auditing
– Transparent Data Encryption (encryption while data is ‘still’ on disk, transparent to applications)
– External Key Management (Consolidation of key management, integration with external products)
– Data Auditing (1st-class ‘AUDIT’ objects; DDL support; audit objects, principals, data, etc.; support for multiple logging targets)

Availability/Reliability
– Pluggable CPU support
– Enhanced Database Mirroring (compression of mirror streams, enhanced performance, automatic page-level repair for principal/mirror)

Performance
– Data compression (easy to enable/disable online, more efficient data storage (this is NOT traditional data compression))
– Backup stream compression (server level control or backup statement control, all backup types)
– Performance data collection (single, common framework for data collection, reporting, and storage/warehousing)
– Improved Plan Guide support (plan freezing, pull plans directly from plan cache, SSMS integration, etc.)
– Resource Governor (create pools and groups to govern, define classifications based on built-in functions, segment resource utilization amoung groups)

Management
– Policy-based management framework (manage via policies vs. scripts, enterprise-wide support, automated monitoring/enforcement, etc.)
– Integrate with Microsoft System Center
– Extended Events (high perf lightweight tracing infrastructure, NOT sql trace, integrated with ETW, unprecidented insight into goings-on)

Development Enhancements
– Improved datetime datatypes (100th nanosecond precision (7 digits past second), time-zone datetime offset, date only, time only)
– HierarchyID datatype (hierarchical-aware data type, ORDPath values, built-in functions, methods, etc.)
– Entity Data Model support (develop ‘business entities’ vs. tables, model complex relationships, retrieve entities vs. rows/columns)
– LINQ
– Sql Server Change Tracking (Change Data Capture, get ‘diff’ data changes WITHOUT a comparible value (i.e. datetime, timestamp, etc.))
– Table Valued Parameters
– MERGE statement (‘upsert’ data, also includes deletion functionality)
– Large UDT’s (no more 8000 byte limit on CLR-based UDTs, no more 8000 byte limit for UDA’s)
– Spatial data (GEOMETRY and GEOGRAPHY data types, built-in spatial function support, spatial indexes)
– XML enhancements (support for lax validation, office 12 support, xs:dateTime support, lists/union types, LET FLOWR support, etc.)
– Inline initialization and compound assignment

  Service Broker
– New UI and Tools for working with (add/drop/edit functionality within SSMS, Diag tools, )
– Conversation Priority (set message ordering, send/receive impact, 1-10 levels)

Data Storage
– Data compression (see above)
– FILESTREAM attribute (get the ‘best of both’ functionality from BLOBs in the DB vs. BLOBs on filesystem, no more “to blob or not to blob”)
– Integrated Full Text Search (FTS fully integrated into DB engine, no external storage, no external service, more efficient and reliable costing)
– Sparse columns (more efficient storage for ‘wide’ tables with many columns that repeat and don’t contain data)
– New index types (spatial indexes, hierarchical indexes, FILTERED indexes (indexes on filtered values within columns), etc.)

Data Warehousing/ETL
– Partitioned Table Parallelism (no more thread limit per partition)
– Star Join support (no special syntax, optimizer based, full backward syntax support)
– Data compression (see above)
– Resource Governor (see above)
– Persistent Lookups in SSIS (no more re-querying for lookup operators, cache lookups in multiple ways, persist lookups to disk)
– Improved thread scheduling in SSIS (shared thread pool, pipeline parallelism)
– Change Data Capture (see above)
– MERGE statement (see above, great uses with slowly changing dimensions)
– Scale-out analysis services (read-only storage supports multiple AS servers)
– Subspace computations
– New Tools for Cube design
– Best Practice Design Alerting
– Backup cubes with better scalability
– Data-mining add-ins for Excell

Reporting
– IIS Agnostic Reporting Services Deployment (no IIS required to run RS any longer)
– Rich-text support
– Enhanced visualiztion (graphing)
– New Word rendering (render reports to Microsoft Word)

Advertisements

Got something to add?

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s