Tue Jun 09 09:22:45 GMT 2015 From /weblog/database

view

http://blog.jooq.org/2015/06/02/what-exactly-are-sql-views/

(google search) (amazon search)

Sat May 09 08:11:37 GMT 2015 From /weblog/database

column-db

Normally, we store object like

1,Smith,Joe,40000;2,Jones,Mary,50000;3,Johnson,Cathy,44000;

However, some say store as

1,2,3;Smith,Jones,Johnson;Joe,Mary,Cathy;40000,50000,44000;

is better for many cases...

http://www.infoq.com/news/2007/09/row-vs-column-dbs

ROW base DB are dead end? - http://slideshot.epfl.ch/play/suri_stonebraker

(google search) (amazon search)

Wed Jul 02 07:41:26 GMT 2014 From /weblog/database

migration

http://blog.jooq.org[..]unbeatable-sql-development-productivity/

Ruby tool to support Database versioning, look like this can work independence from Ruby on Rails - http://www.google.com[..]&lr=&q=Ruby+Rails+Migrations&btnG=Search
An example coding with this tool - http://rails.aizatto.com[..]7/activerecord-migrations-without-rails/ http://wiki.rubyonrails.org/rails/pages/UnderstandingMigrations

Links about Database versioning: http://www.cmcrossroads.com/cgi-bin/cmwiki/view/CM/CMandDatabases

Eclipse plugin - http://dbcopy.sourceforge.net/en/dbcopy/index.html but I cannot get it working, instead, http://dbcopyplugin.sourceforge.net/ work a lot better. However it is better to install using installation jars (and source) of SQuirreL, otherwise there may be version not map issue.

Another new tool, migrate4j - http://karussell.wordpress.com[..]12/23/simple-database-migration-in-java/

Comments of difference approach of DB deployment - https://www.simple-talk.com[..]stration/database-deployment-challenges/

(google search) (amazon search)

Wed Jun 18 03:28:50 GMT 2014 From /weblog/database

Graph

http://www.infoq.com/articles/data-modeling-graph-databases

http://thinkaurelius.com[..]nding-the-world-using-tables-and-graphs/

http://markorodriguez.com[..]tion-and-reasoning-with-graph-databases/

http://markorodriguez.com/2013/01/09/on-graph-computing/

(google search) (amazon search)

Fri Apr 25 01:51:18 GMT 2014 From /weblog/database

normalization

A discussion against(?) 100% normalization - http://www.codinghorror.com/blog/archives/001152.html

Short overview of normalization rules - http://www.datamodel.org/NormalizationRules.html

https://www.simple-talk.com[..]ogramming/database-normalization-basics/

(google search) (amazon search)

Thu Mar 06 07:02:35 GMT 2014 From /weblog/database

boolean

http://blog.jooq.org[..]/25/the-jdbc-boolean-compatibility-list/

(google search) (amazon search)

Mon Jan 13 04:24:11 GMT 2014 From /weblog/database/sql

Problem of SQL

A nice article to talk about problem of SQL. Although we probably need to live with SQL or abstraction of SQL (various OQL and ORM QL), it still nice to know about this

http://www.oreillynet.com/lpt/a/6060

How to simulate column reuse, which is missing from standard SQL - http://blog.jooq.org[..]/the-sql-languages-most-missing-feature/

(google search) (amazon search)

Wed Oct 23 08:49:49 GMT 2013 From /weblog/database

versioning

A discussion - http://tech.groups.yahoo.com/group/agileDatabases/message/2222
Various discussion - http://tsql.ca[..]atabaseDeployment/tabid/708/Default.aspx http://www.simple-talk.com[..]tration/deploying-database-developments/ http://www.andrew-eells.com/database-versioning-framework/

Tools - http://secretgeek.net/dbcontrol.asp

http://www.infoq.com/articles/db-versioning-scripts

(google search) (amazon search)

Mon Oct 07 05:21:31 GMT 2013 From /weblog/database/objective

ORM

A presentation about Politics issues of using ORM persistence - http://www.softwaresummit.com[..]ers/SmithDonaldPoliticsOfPersistence.pdf

A checklist of features of ORM library, which, I think, may be too detail to consider that much? http://madgeek.com/Articles/ORMapping/EN/mapping.htm

Some problem of ORM nowadays - http://blogs.ittoolbox.com[..]e-ubiquitous-bastardization-of-orm-17100

This post rise a good point, if the ORM tool doesn't provide a simpler solution, why bother to use it? Just because many people using it? http://en.newinstance.it/2006/10/23/trimming-jdbc/

We have been using hibernate for while, actually it is good. But LGPL block us from using it anymore...

Here is some other experience about this - http://shinetech.com/pages/viewpage.action?pageId=649 and the discussion of this article - http://www.theserverside.com[..]m/news/thread.tss?thread_id=41174#212825

Some interestig ORM packages without XML
http://www.jenkov.com/mrpersister/introduction.tmpl
http://www.simpleorm.org/sorm/whitepaper.html
http://www.h2database.com/html/jaqu.html

And there is one try to use javadoc doclet approach to make all those SQL documented at javadoc also, look like an interesting idea - http://voruta.sourceforge.net/

Advantage of using ORM, but are those really so important? http://ayende.com[..]oWriteYourOwnObjectRelationalMapper.aspx

Good source about various pattern and test on ORM - http://blog.xebia.com/category/jpa/implementation-patterns/

Analysis on ORM package - http://seldo.com/weblog/2011/06/15/orm_is_an_antipattern

(google search) (amazon search)

Sun Jan 06 11:22:03 GMT 2013 From /weblog/database/performance

embedded

Compare the performance of LevelDB, BDB and BangDB, it sound like BangDB is best... but this article from BangDB - http://highscalability.com[..]ldb-berkley-db-and-bangdb-for-rando.html

(google search) (amazon search)

Thu Jan 03 14:58:44 GMT 2013 From /weblog/database

blob

Discussion about using blob or not - http://www.systemswemake.com/papers/to-blob-or-not

(google search) (amazon search)

Sat Jul 28 06:10:31 GMT 2012 From /weblog/database

generate data

Generate insert statement from table data, mysql syntax - http://mahmoudoracle.blogspot.com.au[..]ate-insert-statement-for-table-data.html

Or with random number - http://moinne.com[..]ningful-test-data-using-a-mysql-function

(google search) (amazon search)

Mon Jan 16 14:25:38 GMT 2012 From /weblog/database

dbunit

tutorial - http://www-106.ibm.com/developerworks/java/library/j-dbunit.html http://zenoconsulting.wikidot.com/blog:8

select part of table - http://www.oreillynet.com[..]njava/blog/2005/10/dbunit_made_easy.html

Some tips of using DBunit, like workaround if no primary key table and format custom timestamp - http://www.systemmobile.com/?s=DBUnit

Scriptable DBUnit - http://musingsofaprogrammingaddict.blogspot.com[..]9/02/scriptable-data-set-for-dbunit.html

http://www.javacodegeeks.com[..]nit-testing-with-dbunit-json-hsqldb.html

(google search) (amazon search)

Thu Jun 30 16:34:29 GMT 2011 From /weblog/database/performance

parameter sniffing

When the query plan is compiled on first run of the procedure after a SQL Server restart, the query is optimised for those initial variables in that initial call.

There’s ways to write the procedure to have a more representative query plan chosen on initial compile each time:

http://blogs.msdn.com[..]ryoptteam/archive/2006/03/31/565991.aspx

This is what is known as parameter sniffing.

(google search) (amazon search)

Fri Apr 29 06:41:53 GMT 2011 From /weblog/database/sql

time

Function that convert string to timestamp for mssql, oracle and sybase - http://connectsql.blogspot.com[..]ql-server-basics-todate-function-in.html

(google search) (amazon search)

Fri Apr 29 01:34:07 GMT 2011 From /weblog/database/performance

count

Tip of having faster 'select count(*) from table' of sql server, probably can use this as reference to check similar feature on other DB, if that hurt something - http://beyondrelational.com[..]are-there-any-alternatives-to-count.aspx

(google search) (amazon search)

Thu Mar 03 16:43:10 GMT 2011 From /weblog/database

replication

Difference kind replication - http://www.infoq.com[..]les/Concurrency-Control-Data-Replication

(google search) (amazon search)

Tue Jan 11 00:46:00 GMT 2011 From /weblog/database/sql

xml

manipulate xml in sql server - http://www.15seconds.com/issue/050803.htm

(google search) (amazon search)

Tue Dec 21 15:03:41 GMT 2010 From /weblog/database

will_you_get_benefits_from_the_new_sql_server_certification

Will you get benefits from the brand new easy to get SQL server expert accreditation?



In my opinion the most notable DB platforms are Microsoft and Oracle. I've come to find that MySQL is popular in start-up companies and DB people who know this platform might make higher salaries, but I honestly rarely ever come across MySQL in job searches.



One reason I like MS SQL Server is because it also includes the SSRS BI platform that is powerful and popular. DB folks who know SSRS (SQL Server Reporting Services) have become very sought-after candidates in lots of companies. Generally, companies end up being forced to buy licenses for third-party BI (Business Intelligence) applications which often can cost a lot of extra money.



With SQL Server, BI and ETL (extract, transform, load) tools come with the first license. I know that maybe the interest in databases is especially to learn DB construction, but you never know when or if you opt to branch out and learn more.



An advantage of SQL is the language itself is fairly universal. I say fairly because each vendor has added their unique functions and syntax. Microsoft and Sybase both use T-SQL, Oracle uses PL/SQL and MySQL uses ANSI-SQL. Ultimately what you learn on one platform can be put on other platforms.



To become a SQL Server Microsoft Certified Master (MCM) is a small task now. Under 1 percent of certified professionals hold a Master certification, and for good reason: In addition to having difficult minimum competencies (5 years SQL Server experience and deep knowledge in most aspects of SQL Server), training for a SQL Server MCM has always come at a significant cost. In fact, that cost has been about $18,500, when you accumulate the required three week training and four exams.



The SQL Server MCM certification is the highest technical certification that Microsoft offers to SQL Server professionals. It's suitable for individuals with five or more years of hands-on SQL Server experience in critical environments. Competencies include designing and implementing high-performance, scalable enterprise environments and troubleshooting the most challenging SQL Server issues.



Well, at PASS (The Professional Association for SQL Server) this week, Microsoft announced all that is beginning to change. According to the company, the changes have been in reaction to requests from experienced SQL Server experts who need to earn MCM's but (go figure) don't possess $18,500.



Now, candidates can earn the certification by passing just two exams: the four-hour Knowledge Exam, and also a six-hour hands-on Lab Exam, which will be obtainable in early 2011.



Fortunately, Microsoft has made some significant changes to the program, including:



* The 3-week in-person training is not required. So, if you believe like you have the required steps, you are able to march right up and take the certification exams. You only pay the cost of the exams, that is about $2,500.

* Nowadays there are only two exams rather than four: 88-970 (Knowledge Exam) and 88-971 (Lab Exam).

* To aid SQL Server professionals plan the exams at a lower cost, Microsoft now offers some free MCM videos.



So you? Do you consider the brand new MS SQL Certification can help you in your job?



About the author: M. P. Rouse is writing for the sql certification path blog, her personal and non-commercial in nature hobby blog targeted at recommendations to supply free info for data base beginners/professionals to help them find a new profession.

(google search) (amazon search)

Fri Mar 27 15:02:05 GMT 2009 From /weblog/database/performance

sharding

An idea of database sharding, basically replicate a unified version for operation required global access - http://itsfrosty.wordpress.com[..]com/2009/03/20/database-sharding-basics/

(google search) (amazon search)

Fri Mar 27 03:49:44 GMT 2009 From /weblog/database/performance

MaterializedView

Oracle can help us to denormalize tables according to query via materialized view, should help performance a lot, probably there is similar feature in other DBMS - http://www.oracle.com[..]ology/products/oracle9i/daily/jul05.html

(google search) (amazon search)

Sat Mar 21 10:54:47 GMT 2009 From /weblog/database/sql

except

example of except keyword - http://sqlblog.com[..]rence-between-two-sets-of-like-data.aspx

(google search) (amazon search)

Fri Feb 13 15:05:40 GMT 2009 From /weblog/database

null

Difference database sort null in timestamp column in difference way:

MySQL: NULL values are presented first, or last if you specify DESC to sort in descending order. Exception: In MySQL 4.0.2 through 4.0.10, NULL values sort first regardless of sort order. - http://dev.mysql.com/doc/mysql/en/problems-with-null.html

SQL server 2000, Sybase 12.5 and hsql 1.8.0: NULL values are presented first, or last if you specify DESC to sort in descending order.

Oracle 8: NULL values are presented last, or first if you specify DESC to sort in descending order.

A more detail default sorting behaviour:
{null, -1, +1} // ASC sort for informix, microsoft, mysql and sybase
{-1, +1, null} // ASC sort for IBM, Ingres, InterBase, Oracle and PostgreSQL
{+1, -1, null} // ASC sort for informix, InterBase, PostgreSQL, microsoft, mysql and sybase
{null, +1, -1} // ASC sort for IBM, Ingres and Oracle

A discussion about using NULL or not - http://discuss.joelonsoftware.com/default.asp?design.4.403282 http://discuss.joelonsoftware.com/default.asp?design.4.494249

One solution - http://web.onetel.com[..]Manifesto/Missing-info-without-nulls.pdf

Null, mean not application or no value? - http://www.livejournal.com/users/djberg96/47074.html

Nothing neither equals, nor unequals, to NULL - http://fishbowl.pastiche.org/2009/01/30/why_null_is_special/

(google search) (amazon search)

Wed Jan 07 15:18:29 GMT 2009 From /weblog/database

Database Refactorings

List of Database Refactorings

Pramod has been working on defining the various refactorings that he's used in the approach defined in our paper on evolutionary database design Here we keep a list of the refactorings that he has defined so far.

http://martinfowler.com/dbrefact/

There is some tools support, but not sure if that really work... - http://www.simple-talk.com[..]-database-development-with-sql-refactor/

Video of tutorial presentation, very nice - http://www.infoq.com/presentations/ambler-database-refactoring

Article show how driven DB design with test - http://www.methodsandtools.com/archive/archive.php?id=78

(google search) (amazon search)

Mon Mar 17 17:25:23 GMT 2008 From /weblog/database/objective

Databases and Objects

A nice article which talk about relation DB and OO. But I have an difference opinions, I think that advantage of relational database is the problem that OO like to prevent.

The main advantage of relational database is the relatioal model make it easy to perform reporting. However, relation model is more difficult than hierarchical model (OO model) to enable encapsulation and polymorphism.

Turn out, if we stand on OO programmer point of view, we will complaint that most RDBMS is in fact just a giant global variable; If we stand as DBA point of view, we will complaint other thing.

Can we create something that optimize for both world? I don’t think so…

http://jooto.com/blog/index.php/2005/11/17/databases-and-objects/

Update: a message also discuss about this: http://groups.yahoo.com/group/pragprog/message/5682

Update #2: A further analysis of ORM: http://www.codinghorror.com/blog/archives/000621.html

(google search) (amazon search)