INF: New SQL Server features in SQL Server 2008 R2 – Part 1

SQL Server 2008 R2 expands on the value delivered in SQL Server 2008 by providing a wealth of new features and capabilities that can benefit your entire organization. Many of these features are already documented in SQL Server Books Online at https://msdn.microsoft.com/en-us/library/bb500435(SQL.105).aspx so we’ve tried not to cover these. Also many new SQL Server 2008 R2 features are in the areas of Business Intelligence (SQL Server Reporting Service SSRS, SQL Server Analysis Services SSAS) - we’ve not mentioned these in below list and these are available on MSDN https://msdn.microsoft.com/en-us/library/bb500435(SQL.105).aspx . Additional unmentioned features include Microsoft StreamInsight which is referenced in https://www.microsoft.com/sqlserver/2008/en/us/R2-complex-event.aspx and massively parallel processing (MPP) mentioned in https://www.microsoft.com/sqlserver/2008/en/us/parallel-data-warehouse.aspx .

This blog contains additional information apart from what is mentioned in SQL Server Books Online and also contains MSDN links.

=====

Notes =====

1. SQL Server 2008 R2: This is a product (like SQL Server 2000/2005/2008).

2. R2 version: Its fairly straightforward to know if SQL database engine version is 2008 R2. SQL Server Management Studio shows R2 version starting with 10.50 after connecting to the SQL Server instance – for example the Release to Market (RTM) build will show 10.50.1600 as shown below :

image

============

Enhancements ============

1. Allows for database files to be created on UNC shares: SQL Server 2008 R2 allows databases to be created on network (UNC) shares. Note that this was possible in earlier versions too but required a special trace flag to implement this behavior as mentioned in https://support.microsoft.com/default.aspx?scid=kb;EN-US;304261 . This trace flag is now not required so below works.

{

My machine name: vijayrod3
Another machine name: vijayrod-vm
UNC share: \\vijayrod-vm\Test_DB_Create\
Assumption: Above share has appropriate permissions (Read/Write)

--below TSQL specified the UNC share in FILENAME

USE master;
GO
CREATE DATABASE Foo
ON
( NAME = Foo_dat,
FILENAME = '\\vijayrod-vm\Test_DB_Create\Foodat.mdf',
SIZE = 10,
MAXSIZE = 50,
FILEGROWTH = 5 )
LOG ON
( NAME = Foo_log,
FILENAME = '\\vijayrod-vm\Test_DB_Create\Foolog.ldf',
SIZE = 5MB,
MAXSIZE = 25MB,
FILEGROWTH = 5MB ) ;
GO

--GoTo database properties using SQL Server Management Studio to verify path as shown below:

 

}

2. Enhanced Unicode compression: As mentioned in https://blogs.msdn.com/sqlserverstorageengine/archive/2009/08/17/unicode-compression-in-sql-server-2008r2.aspx, “SQL Server uses UCS-2 encoding scheme that takes two bytes of storage regardless of the locale. For example, in ASCII character set when stored as NCHAR, each character only needs 1 byte of storage but it is stored using 2 bytes with the significant byte being 0. In fact most European languages need only 1 byte of storage. When an application is either converted or written to user Unicode based data types, it can, depending on the size of strings, increase the storage requirements significantly”. The Unicode compression is enabled or disabled as part of ROW or PAGE compression.

{

USE master
GO
CREATE DATABASE db_Unicode
GO
USE db_Unicode
GO
CREATE TABLE student (name NVARCHAR(50))
GO
INSERT INTO student VALUES ('Suhas')
INSERT INTO student VALUES ('Varun')
INSERT INTO student VALUES ('Balmukund')
INSERT INTO student VALUES ('Saket')
GO

--note the average record size
select avg_record_size_in_bytes
from sys.dm_db_index_physical_stats (db_id('db_Unicode'),
OBJECT_ID('dbo.student'), null, null, 'SAMPLED')

/*
Here are the values returned:
-Average Row length in SQL2008 (before row compression) = 23
-Average Row length in SQL2008R2 (before row compression) = 23

SQL2008 version: 10.0.2531, Enterprise edition
SQL2008R2 version: 10.50.1450.3 (beta), Enterprise edition
*/

--enable row compression for table
alter table dbo.student rebuild
with (data_compression = ROW)

--check the average record size again
select avg_record_size_in_bytes
from sys.dm_db_index_physical_stats (db_id('db_Unicode'),
OBJECT_ID('dbo.student'), null, null, 'SAMPLED')

/*
Here are the values returned:
-Average Row length in SQL2008 (before row compression) = 20 (around 13% compression)
-Average Row length in SQL2008R2 (before row compression) = 11 (around 52% compression). This has an extra savings of 9 bytes.
*/

As you can see that in this example,the Unicode compression gave us extra % compression with ROW compression.

Please refer https://blogs.msdn.com/sqlserverstorageengine/archive/2009/08/17/a-unicode-compression-example.aspx for more details on our in-house testing for observing compression savings.

Part 2 is posted at : https://blogs.msdn.com/b/sqlserverfaq/archive/2010/05/13/inf-new-sql-server-features-in-sql-server-2008-r2-part-2.aspx

Vijay Rodrigues
SE, Microsoft SQL Server

Reviewed by
Sudarshan Narasimhan & Nickson dickson TL , Microsoft SQL Server