Transcript Html Overview

SQL Server 2008
Overview
Presented by Tarek Ghazali
IT Technical Specialist
Microsoft SQL Server MVP, MCTS
Microsoft Web Development MCP
ITIL V3 Foundation Certified
© 2009 Tarek Ghazali. All rights reserved.
What is new in SQL
Server 2008

More powerful management
– Policy Management feature

Improved performance and scalability
– Storing high volume of data
– Support for diverse types of data

Better security and availability
– Security increased by data encryption
– Availability increased by database mirroring

Changes for developers
LINQ (Language
Integrated Query)

LINQ enables database developers to
issue queries against a database using
a .NET-based programming language
instead of the normal T-SQL
statements.
LINQ (Language
Integrated Query) (Cont.)


SQL Server 2008 enhances LINQ by
providing a new LINQ to SQL provider
that allows developers to issue LINQ
commands directly against SQL Server
tables and columns
This will reduce the amount of time it
takes to create new data queries
Enhancement of T-SQL
Statements




MERGE statement: It allows the
developer to check for the existence of
data before trying to insert the data.
Allows you to merge two tables
together based on a set of criteria
Useful for synchronizing data
Great for consolidating in a Data
Warehouse
Enhancement of T-SQL
Statements (Cont.)

No longer it is necessary to create
complex joins in order to update data
that exists and to insert data that does
not already exist, all during a single
statement.
Table Valued Parameters
(TVP)


TVP's make possible to use a "table" as a
parameter for a procedure
Limitations:
1. TVP's can only be READONLY in the procedure that
define them as a parameter
2. Can only be used as an input parameter.
3. Apart from this the same rules apply to TVP's as to table
variables for example no DDL can be executed against a
TVP and no statistics are kept for TVP's.
Data Compression
Works on tables and indexes
 Works on clustered and nonclustered indexes
 Compression types: Page and Row
 T-SQL extensions to Table and
Index DDL

Data Compression (Cont.)

CREATE TABLE... [WITH
(<table_option>
[,...n])]<table_option>:={
DATA_COMPRESSION =
{NONE|ROW|PAGE}[ON PARTITIONS
({<partition_number_expression>|<ran
ge>} [,...n])]

New SP to estimate cost savings

sp_estimate_data_compression_savings
New Datatypes
Date and Time Datatypes



DATE – a date only type (precision 1
day)
TIME – a time only type (precision 100
nano seconds)
DATETIMEOFFSET –a time zone aware
datetime type (precision 100 nano
seconds)
Date and Time Datatypes
(Cont.)

DATETIME2 – a datetime type with
larger fractional seconds and year
range than the existing DATETIME
type (precision 100 nano seconds)
HierarchyId: New
datatype



Enables database applications to
model tree structures in a more
efficient way than currently possible
Stores values that represent nodes in
a hierarchy tree
Implemented as a CLR UDT that
exposes several efficient and useful
built-in methods for creating and
operating on hierarchy nodes
Spatial Datatypes
 Geography



datatype
Round Earth geospatial model
Define points, lines, and areas with
longitude and latitude
Account for planetary curvature and
obtain accurate “great circle” distances
Spatial Datatypes (Cont.)
 Geometry
datatype
– Planar (“Flat Earth”) geospatial model
– Define points, lines, and areas with
coordinates
– Use for localized areas or non-projected
surfaces
 Both
types provide static and
instance methods
– Calculate distances, find intersections, etc.
FILESTREAM datatype



Stores data in the File System but,
allows it to be queried via normal TSQL
Allows easy transition between
relational and non-relational data, so
application can access documents as
data
Under the covers uses the Win32 APIs
to speed streaming of data.
FILESTREAM datatype



Stores data in the File System but,
allows it to be queried via normal TSQL
Allows easy transition between
relational and non-relational data, so
application can access documents as
data
Under the covers uses the Win32 APIs
to speed streaming of data.
FILESTREAM datatype
(Cont.)


Allows developers Use T-SQL
SELECT/INSERT/UPDATE/DELETE
statements to query or modify
FILESTREAM data
Data stored outside of the database
on more cost-effective storage without
comprising features for accessing such
data.
New Security Features


SQL Server 2005 introduced data
security in the form of data encryption
With SQL Server 2008, encryption is
greatly enhanced with the introduction
of two features: Extensible Key
Management and Transparent
Data Encryption
New Security Features
(Cont.)

Extensible Key Management allows for
an enhanced structure to safely store
the keys used in the encryption
infrastructure—not only in the
database itself but also outside the
database in third-party software
modules or with a Hardware Security
Module
New Security Features
(Cont.)

Transparent Data Encryption offers
improved flexibility for encrypting data
by allowing encryption to be a
property of the database and not just
the result of functions in a line of code
Demo
Resources & Questions

Microsoft Resources:
– http://msdn.microsoft.com/sqlserver/
– http://www.microsoft.com/sql/community

Contact me:
– [email protected]

Download Presentation:
– http://www.sqlmvp.com