Transcript BB16  Michael Rys Principal Lead Program Manager Microsoft Corporation [email protected]  Pain Points   Goals  Reduce the cost of managing all types of data  Simplify the development of applications which.

BB16
 Michael Rys
Principal Lead
Program Manager
Microsoft Corporation
[email protected]

Pain
Points


Goals

Reduce the cost of managing all types of data

Simplify the development of applications which use
relational and non-relational data

Extend services currently available for relational data
to non-relational data

Provide non-relational services to relational data


SQL Server 2005
Documents &
Multimedia
• Full Text Indexing
• XML Data Type and
Functions
• User Defined Types
Relational
BR Support
• Remote BLOB Store API
• Filestream
• Integrated FTS
• Fully supported
Geometry and
Geography data types
and Functions
Spatial
XML
SQL Server 2008
• XML Upgrades
• Large UDTs
• Sparse Columns
• Wide Tables
• Filtered Indices
• HierarchyID
Documents &
Multimedia
Use File Servers
Dedicated BLOB
Store
Store BLOBs in
Database
Application
Application
Application
BLOBs
BLOBs
DB
Advantages
Challenges
Example
DB
BLOBs
DB
• Low cost per GB
• Streaming Performance
• Lower cost/GB at scale
• Scalability & Expandability
• Integrated management
• Data-level consistency
• Complex application
development and
deployment
• Integration with structured
data
• Complex application
development and
deployment
• Separate management
• Enterprise-scales only
• Poor data streaming support
• File size limitations
• Highest cost per GB
• Windows File Servers
• NetApp NetFiler
• EMC Centera
• Fujitsu Nearline
• SQL Server
VARBINARY(MAX)
Documents &
Multimedia
Use File Servers
Application
BLOB
Store BLOBs in
Database
DB
Application
Dedicated BLOB
Store
Store BLOBs in
DB + File System
Application
BLOB
BLOB
Application
BLOB
DB
DB
DB
Remote BLOB Storage
SQL BLOB
FILESTREAM Storage
Documents &
Multimedia

Store BLOBs in
DB + File System


Application

BLOB


DB






Documents &
Multimedia
Applications
Remote BLOB Store API Client
Provider
Library
Provider
Library
Provider
Library
Provider API
SQL Server


BLOB
Store 1
BLOB Store
2
BLOB Store
3
Services
• Create
• Enumerate
• Fetch
• GC
• Delete
Documents &
Multimedia



Documents &
Multimedia





SELECT * FROM candidates
WHERE CONTAINS(resume,’”SQL Server”’)
AND ZipCode = ‘98052’





Documents &
Multimedia
SQL SERVER process
WB client
Bind
Doc_table
id
FTS Algeb.
SQL/FTS integrated
doc_type
query tree document
QE
Shared Memory
Parse
SQL Algeb.
QUERY
Results
FTS Algeb.
Algebrizer
T-SQL
Parser
FDHOST process
Execution
Plan
QO
Language
Module
STOPLIST
Ranking Func.
Integration
THESAURUS
FTLogicalOperator
FTLogicalOperator
FTExecutionOperator
FTExecutionOperator
Stemmer
iFilters
CREATE FULLTEXT INDEX ft_idx
ON Doc_table(document
Cardinality
TYPE COLUMN doc_type)
FTLogicalOperator
KEY INDEX doc_id_idx;
FTExecutionOperator
WB
Full-Text Index
Documents &
Multimedia
2 min
1 min
Populating an index of 20million rows of 1k data on identical hardware
(time in minutes)
Spatial








See BB24 – SQL Server 2008: Deep Dive into Spatial Data
XML










Scenarios










Relational
BR Support
Characteristics




Relational
BR Support
Requirements





Relational
BR Support
SQL Server 2008 features





Relational
BR Support
Scenarios










Relational
BR Support
Relational
BR Support
Key properties



/

/1/
/2/
/3/



/1/1/
/1/1/1/
/1/1/2/



/1/2/
/3/1/
/3/2/
Relational
BR Support





// Sparse as a storage attibute in Create/Alter table statements
Create Table Products(Id int, Type nvarchar(16)…,
Resolution int SPARSE, ZoomLength int SPARSE);
// No Change in Query/DML Behavior
Select Id, Type, Resolution, ZoomLength from Products;
Update Products set Resolution=3, ZoomLength = 105 where Id = 101;
Relational
BR Support







// Create a sparse column set
Create Table Products(Id int, Type nvarchar(16)…,
Resolution int SPARSE, ZoomLength int SPARSE,
Properties XML COLUMN_SET FOR ALL_SPARSE_COLUMNS);
// Generic relational access to sparse column set.
// Returns an aggregated XML fragment for all non-null sparse columns
Select Id, Type, Properties from Products;
<Resolution>4</Resolution>
<ZoomLength>70</ZoomLength>
// Generic updates through sparse column set. Similar to an XML update
Update Products set Type = ‘Camera”, Properties = @xml
Relational
BR Support




Sparse storage
Non-sparse storage
PK
c1
sc1
1
A
1
2
B
3
C
4
D
5
E
6
F
7
G
8
H
9
I
sc2
sc3
2
sc4
sc5
sc6
sc7
sc8
sc9
PK
C1
sc
1
9
1
A
(sc1,sc9)(1,9)
2
B
(sc2,sc4)(2,4)
3
C
(sc6,sc7)(6.7)
4
D
(sc1,sc5)(1,5)
5
E
(sc4,sc8)(4,8)
6
F
(sc3,sc9)(3,9)
7
G
(sc5,sc7)(5,7)
8
H
(sc2,sc8)(2,8)
9
I
(sc3,sc6)(3,6)
4
6
1
7
5
4
8
3
9
5
7
2
8
3
6
sc2
sc3
sc4
sc5
sc6
sc7
sc8
sc9
Relational
BR Support


































http://sqlblog.com/blogs/michael_rys/




http://msdn.microsoft.com/en-us/library/cc949109.aspx
http://www.microsoft.com/sqlserver/2008/en/us/wp-sql-2008-manage-unstructured.aspx




http://www.microsoft.com/sqlserver/2008/en/us/wp-sql-2008-whats-new-xml.aspx
http://msdn.microsoft.com/en-us/library/cc721269.aspx
http://connect.microsoft.com/sqlserver/feedback
33
© 2008 Microsoft Corporation. All rights reserved. Microsoft, Windows, Windows Vista and other product names are or may be registered trademarks and/or trademarks in the U.S. and/or other countries.
The information herein is for informational purposes only and represents the current view of Microsoft Corporation as of the date of this presentation. Because Microsoft must respond to changing market
conditions, it should not be interpreted to be a commitment on the part of Microsoft, and Microsoft cannot guarantee the accuracy of any information provided after the date of this presentation.
MICROSOFT MAKES NO WARRANTIES, EXPRESS, IMPLIED OR STATUTORY, AS TO THE INFORMATION IN THIS PRESENTATION.