Database

Databases

Friday, Feb 18th, 2010 8:27 AM

In the beginning there was dbase and the clipper complier. Many peoples assume that the Microsoft SQL SErver was written by Microsoft, however the origins of SQL Server are in an old system Sybase by Ashton-Tate who partened with Microsoft in 1987 and co-developed the software. Remnants of the old Sybase system remained in SQL Server until a total rewrite in the version 6 of SQL Server by Microsoft. Things have changed a lot and the latest generation of Microsoft SQL Server has really come of age.

Without a doubt one of the hidden treasures in the new version of SQL Server from 2005 onwards is the inclusion of xslt and xml. On the Surface it may appear just another option of language, however is it is considered in the light of the Entity framework and Visual Studio other possibilities arise. An example of the power of this is as follows. Most Rest based web sites publish the xsd of their system which declares the data representation and types used throughout. If the site is database driven, by translation of the xsd file, use of the Entity framework tools in Visual Studio, xml and a modicum of common sense, it becomes childs play to reconstruct 90% of the logical database structure of the sites data source, often resulting in a better more logical representation of the data structure than the original. This experiment was tried with the Facebook xsd and several others with great success enabling a far greater understanding of the API that is on offer and why certain functions work the way they do.

Another possibility which has arisen in the new version of Microsoft SQL Server is the possibility of extending the server using .Net. Originally this was possible in earlier versions using com which was largely ineffective. Although this feature is interesting it mus be used with caution because of the nature of these non-native extensions speed penalties may result.

Database Projects

Most of the projects in the past have involved a database in one form or another. A lot of caution has been taken to avoid mistakes such as business logic in the database, considered to be folly.

Any programmer these days must be at least familiar with SQL including stored procedures and triggers and must also be acutely aware of the correct methods of calling of stored procedures with parameters. The easily avoided subject of SQL injection arises far too commonly these days due entirly to inexperience.

Unfortunately use of Databases as a substituted for program logic is also common, primarily because many programmers take the path of least resistance and opt for calling the database under the false assumption that "database calls are fast". Again this is a practice fairly common in inexperienced programmers. This is a practice that should be avoided at all costs as it is inevitable that system scalability will suffer.