Beyond Relational

, 2007-06-05

I missed this yesterday, but I saw it on the schedule again, and I knew

I had to make this one. It's titled "Beyond Relational", which I expect

to mean it's focused on enhancements not necessarily related to rows

and columns. But I didn't see the abstract, so I'm not 100% sure what

it will be. I'm hoping it's not too much time on GIS stuff.

Jnue CTP is out and is the first public release. They are definitely

looking for feedback on the features that are already baked in.

4 pillars of SQL Server

- enterprise platform - all the "-abilities"

- Beyond relational

- Dynamic development - moving towards model based development.

- Pervuasive Insight - BI everywhere, all data can be used in BI.

Beyond Relational - The explosion of data which means more and more types of data will need to be hosted.

Survey of the audience: how many have more than 1TB at home? 75-80% did.

Applications work with different types of data to be rich.

- relational (rows/colummns), structured data


- documents and multimedia data.

- spatial data

An example is an insurance claims application. In the past the

application was forms mapped to tables in rows. Today we have pictures

and videos, relating to other documents like witness reports, moving

data in XML, and even mapping those claims to geographical locations.

Idea on new types of data is to ensure that the functionality available

for traditional types of data is also available for new data types.


- Reduce cost of managing all types of data

- simply development of apps using both relational and non-relational data

- Extend services for relational data to non-relational data.

Additions in Katmai

- XML upgrades

- Remote BLOB Store API, FILESTREAM, Integrated FTS

- support geometry and geography types and functions

- Large UDTs, flexible columns, wide tables, filtered indexes, hierarchyID

WinFS? - most of team was folded into SQL Server team. A lot of

the work went into SQL Server. The FILESTREAM type and large UDTs came

out of this project.

XML Upgrades

- Full support for storing and validating Office 12 document formats.

- Support for lax validation

- Full xs:dateTime support

 - support for no timezone values and timezone preservation

- support for lists and union types

 - There is XQUERY support for the let-clause.

 - Added support for insert sql:variable("@xml") into /a/b

Doucments and Multimedia

Why store in the db?

 - integrated management and data level consistency

 - problems with poor data streaming, size limitations, and high cost/GB

 - right now you can use VARBINARY(MAX)


 - File server storage or dedicated BLOB store server (RMC Centera, Fujitsu Nearline).

None of these is the right way. Each works in different situations.

SS2K8 should deliver a few different platforms.

- FILESTREAM feature. Addresses limitations of SQL BLOBs ni making them work better.

- REMOTE BLOB storage - allows blobs to live in file server or dedicated store with reference in db.

- SQL BLOBs - more support for internal storage.


storage attribute on VARVINARY(MAX)

- unstrcutured data is stored in the file systems (NTFS)

- dual programming model.

  - TSQL (same as blob)

  - WIN32 streaming APIs with T-SQL transactional semantics.

- Gives data consistency and integrated manageability (backup/restore, administraton)

- Size limit is the file system volume size

- uses SQL Server security stack

- demo - Kevin Farlee

  - create db with filegroups, one of which has an attribute as

containing filestreams. the location is a directory that will be

created. Different tables can have filestreams in different locations.

Just like tables.

  - table is a "varbinary(max) filestream" type.

  - insert a row and we see

  - to work with the data, need to get a handle and logical path to get to the data in the filesystem

    "select get_filestream_transaction_context(), field.pathname from MyTable"

  - Use a Win32 call to read and write this data from the file system (fascreat??)

  - Showed transaction rollback as well as trigger fire from update to the file system data.

  - backup, drop db, restore db, and data is there, including filestream data.

- First release in 2008 should have these limitations

 - remote storage of filestream not supported

 - DB snapshot and mirroring are not supported (log shipping will work)

 - encryption and table value parameters are not supported.

My question is what does this do to the log?


- applications can link via a provider. So each application provider needs to deliver their own provider for their own system.

- simple api for fetch, create, enumerate, Garbage collection, delete

- returns reference that is stored in the db. By going through the db,

there is some maintenance of the references. This should ensure some

link consistency and allows the application to be loosely linked to the

BLOB store.

Full-Text Indexing


 - indexes outside SS have manageability problems

 - mixed query performance suffers from pull over complete full-text set

 - scaling issues on big boxes


 - FTI fully integrated into the engine.

 - Mixed queries should scale better. (query on "contains() and xx = i)

Spatial Data

- New types (geometry, geography)

- New methods (intersects, buffer, more)

- new indexes for similar performance to other types.

- Full set of OGC/SQL MM, ISO 19125 components

- Integration with Virtual Earth

- Demo - michael rys

  - intersection of map with zip code query.

  - added spatial index, performance is improved.

  - showed integration with Virtual Earth as well

Relational Data

- HierarchyID - store arbitrary hierarchies of data and efficiently query them. New UDT to implement hierarchies.

- Large UDTs - no more 8k limit

- Sparse columns - optimized storage for sparsely populated columns.

- Wide tables - support for hundreds of thousands of sparse columns.

- Filtered indexes - define indexes over subsets of data.  Index on rows in a table with a particular value.





Related content

Database Mirroring FAQ: Can a 2008 SQL instance be used as the witness for a 2005 database mirroring setup?

Question: Can a 2008 SQL instance be used as the witness for a 2005 database mirroring setup? This question was sent to me via email. My reply follows. Can a 2008 SQL instance be used as the witness for a 2005 database mirroring setup? Databases to be mirrored are currently running on 2005 SQL instances but will be upgraded to 2008 SQL in the near future.


1,567 reads

Networking - Part 4

You may want to read Part 1 , Part 2 , and Part 3 before continuing. This time around I'd like to talk about social networking. We'll start with social networking. Facebook, MySpace, and Twitter are all good examples of using technology to let...


1,530 reads

Speaking at Community Events - More Thoughts

Last week I posted Speaking at Community Events - Time to Raise the Bar?, a first cut at talking about to what degree we should require experience for speakers at events like SQLSaturday as well as when it might be appropriate to add additional focus/limitations on the presentations that are accepted. I've got a few more thoughts on the topic this week, and I look forward to your comments.


360 reads