Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
Log in  ::  Register  ::  Not logged in

Serialising tabular and relational data.

By Phil Factor,

The way that we move or store tabular data in files isn't particularly clever. Forget, for a moment, the conceptual gulf between the Relational and Object-oriented view of data, for we can't even dump data to disk in an open-standard way that can subsequently be read reliably by a range of other applications. We can't transfer data from one relational database to another with any sort of elegance and efficiency. We strain to copy data to or from spreadsheet programs or statistical analysis tools. When we do so, we represent data in unreliable ways that would have been met with a grin of familiar recognition by the geeks of our parents' generation. We load data into SQL Server from third-party databases, table by blooming table, using methods that seem futuristic until we look under the covers and find the bulk load engine of BCP clicking along much as it has for the past twenty or so years.

There isn't a good existing standard format for tabular data. The CSV format goes back to the sixties, and is yet to receive an agreed international standard. It can't be reliably used without an accompanying metadata file. There are proposals for a standard for representing fielded text which has everything that is required except for widespread adoption. It is just about possible to use YAML to copy small databases between two different relational systems but it isn't easy and impossibly verbose.

What's required? It must be Unicode-text based rather than binary: There must be no chance of delimiter collision: The metadata must be part of the file: It should be reasonably compact and shouldn't rely on being zipped: The order of columns, as well as the column labels, must be described in the metadata along with a description of the datatype: It should be possible to supply some indication of keys and constraints: It should be able to support binary data and blobs efficiently: It should cope with column values that are XML or JSON: It should, if possible be a superset of an existing de-facto standard so that it can be used even when not explicitly supported. It should be easy to implement. Well, something like that would suit me, though I'd probably add the rather unkind condition that it mustn't use XML in any of its guises.

This isn't a good standard for a database vendor to initiate. The universities would distain such a practical task. Surely, the definition of such a standard is down to the community of users. I'd be fascinated to read other views on the subject.

Total article views: 172 | Views in the last 30 days: 1
Related Articles

Strict Database Standards and Conventions

In this article by Mattias Fagerlund, he shows you how he employs strict database naming standards a...


Backup Isn't Enough

When is backup not enough? Steve Jones talks about a few things that can cause you issues and a back...


A Standard Database Security Script Generator

A script generator for standard security compliance.


T-SQL Development Standards

Writing corporate development standards


Can't restore database / Log issues?!

Can't restore database from backup that had huge log file.

database weekly    

Join the most active online SQL Server Community

SQL knowledge, delivered daily, free:

Email address:  

You make SSC a better place

As a member of SQLServerCentral, you get free access to loads of fresh content: thousands of articles and SQL scripts, a library of free eBooks, a weekly database news roundup, a great Q & A platform… And it’s our huge, buzzing community of SQL Server Professionals that makes it such a success.

Join us!

Steve Jones

Already a member? Jump in:

Email address:   Password:   Remember me: Forgotten your password?
Steve Jones