Change database compatibility level – SQL Server

For all installations of SQL Server 2008, the default compatibility level is 100. Databases created in SQL Server 2008 are set to this level unless the model database has a lower compatibility level. When a database is upgraded to SQL Server 2008 from any earlier version of SQL Server, the database retains its existing compatibility level if it is at least 80. Upgrading a database with a compatibility level below 80 sets the database to compatibility level 80. This applies to both system and user databases. Use ALTER DATABASE to change the compatibility level of the database. To view the current compatibility level of a database, query the compatibility_level column in the sys.databases catalog view.

ALTER DATABASE database_name SET COMPATIBILITY_LEVEL = { 80 | 90 | 100 }

• 80 = SQL Server 2000
• 90 = SQL Server 2005
• 100 = SQL Server 2008

or Use sp_dbcmptlevel system proc to check/set different compatiblity levels to a database.

sp_dbcmptlevel ‘<db name>’ – to check the current compatibility
sp_dbcmptlevel ‘<db name>’, 100 – to set the compatibility to SQL Server 2008 

Changing the compatibility level while users are connected to the database can produce incorrect result sets for active queries. So set the database to single-user access mode and change the database compatibility to avoid any unexpected results.

 Read detailed info and importance of database compatibility @ http://msdn.microsoft.com/en-us/library/bb510680.aspx

I got to know this as some of our dynamic transfer build scripts were failed in the stage environment and the environments are having migrated databases from sql server 2005 to sql server 2008. As part of the migration database compatibility has not been changed to sql server 2008 i.e. 100. After setting 100 as compatibility level everything started working as expected.

Test Driven DB Development

http://blog.coryfoy.com/2007/07/test-driving-stored-procedures-in-sql-server-in-vs2008/

Graph Database

Most applications today handle data that is deeply associative, i.e. structured as graphs (networks). The most obvious example of this is social networking sites, but even tagging systems, content management systems and wikis deal with inherently hierarchical or graph-shaped data.

This turns out to be a problem because it’s difficult to deal with recursive data structures in traditional relational databases. In essence, each traversal along a link in a graph is a join, and joins are known to be very expensive. Furthermore, with user-driven content, it is difficult to pre-conceive the exact schema of the data that will be handled. Unfortunately, the relational model requires upfront schemas and makes it difficult to fit this more dynamic and ad-hoc data.

A graph database uses nodes, relationships between nodes and key-value properties instead of tables to represent information. This model is typically substantially faster for associative data sets and uses a schema-less, bottoms-up model that is ideal for capturing ad-hoc and rapidly changing data.

Neo4J ecosystem is Graph Database. Read more from here …

http://www.oscon.com/oscon2009/public/schedule/detail/8364

MongoDB is another Graph Database which is a scalable, high-performance, open source, document-oriented database. Read more from …

http://www.mongodb.org/

http://en.wikipedia.org/wiki/MongoDB