RSS feed [root] /database /weblog



title search:


Sat Sep 09 00:15:42 HKT 2017


(google search) (amazon search)
download zip of files only

Fri Apr 25 09:51:18 HKT 2014 From /weblog/database


A discussion against(?) 100% normalization -

Short overview of normalization rules -[..]ogramming/database-normalization-basics/

(google search) (amazon search)

Thu Mar 06 15:02:35 HKT 2014 From /weblog/database


(google search) (amazon search)

Mon Jan 13 12:24:11 HKT 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

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

(google search) (amazon search)

Mon Oct 07 13:21:31 HKT 2013 From /weblog/database/objective


A presentation about Politics issues of using ORM persistence -[..]ers/SmithDonaldPoliticsOfPersistence.pdf

A checklist of features of ORM library, which, I think, may be too detail to consider that much?

Some problem of ORM nowadays -[..]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?

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 - and the discussion of this article -[..]m/news/thread.tss?thread_id=41174#212825

Some interestig ORM packages without XML

And there is one try to use javadoc doclet approach to make all those SQL documented at javadoc also, look like an interesting idea -

Advantage of using ORM, but are those really so important?[..]oWriteYourOwnObjectRelationalMapper.aspx

Good source about various pattern and test on ORM -

Analysis on ORM package -

(google search) (amazon search)

Fri May 31 17:55:35 HKT 2013 From /weblog/database/sql


With IN and subquery / or "top" like keywords -[..]econd-highest-or-maximum-salary-sql.html

(google search) (amazon search)

Sun Jan 06 19:22:03 HKT 2013 From /weblog/database/performance


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

(google search) (amazon search)

Thu Jan 03 22:58:44 HKT 2013 From /weblog/database


Discussion about using blob or not -

(google search) (amazon search)

Sat Jul 28 14:10:31 HKT 2012 From /weblog/database

generate data

Generate insert statement from table data, mysql syntax -[..]ate-insert-statement-for-table-data.html

Or with random number -[..]ningful-test-data-using-a-mysql-function

(google search) (amazon search)

Mon Jan 16 22:25:38 HKT 2012 From /weblog/database


tutorial -

select part of table -[..]njava/blog/2005/10/dbunit_made_easy.html

Some tips of using DBunit, like workaround if no primary key table and format custom timestamp -

Scriptable DBUnit -[..]9/02/scriptable-data-set-for-dbunit.html[..]nit-testing-with-dbunit-json-hsqldb.html

(google search) (amazon search)

Fri Jul 01 00:34:29 HKT 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:[..]ryoptteam/archive/2006/03/31/565991.aspx

This is what is known as parameter sniffing.

(google search) (amazon search)

Fri Apr 29 14:41:53 HKT 2011 From /weblog/database/sql


Function that convert string to timestamp for mssql, oracle and sybase -[..]ql-server-basics-todate-function-in.html

(google search) (amazon search)

Fri Apr 29 09:34:07 HKT 2011 From /weblog/database/performance


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 -[..]are-there-any-alternatives-to-count.aspx

(google search) (amazon search)

Fri Mar 04 00:43:10 HKT 2011 From /weblog/database


Difference kind replication -[..]les/Concurrency-Control-Data-Replication

(google search) (amazon search)

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


manipulate xml in sql server -

(google search) (amazon search)

Tue Dec 21 23:03:41 HKT 2010 From /weblog/database


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 23:02:05 HKT 2009 From /weblog/database/performance


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

(google search) (amazon search)

Fri Mar 27 11:49:44 HKT 2009 From /weblog/database/performance


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 -[..]ology/products/oracle9i/daily/jul05.html

(google search) (amazon search)

Sat Mar 21 18:54:47 HKT 2009 From /weblog/database/sql


example of except keyword -[..]rence-between-two-sets-of-like-data.aspx

(google search) (amazon search)

Fri Feb 13 23:05:40 HKT 2009 From /weblog/database


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. -

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 -

One solution -[..]Manifesto/Missing-info-without-nulls.pdf

Null, mean not application or no value? -

Nothing neither equals, nor unequals, to NULL -

(google search) (amazon search)

Wed Jan 07 23:18:29 HKT 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.

There is some tools support, but not sure if that really work... -[..]-database-development-with-sql-refactor/

Video of tutorial presentation, very nice -

Article show how driven DB design with test -

(google search) (amazon search)

Mon Sep 08 23:56:11 HKT 2008 From /weblog/database

lock table for select

Look like using "select XXX for update" is widely supported

BTW, someone suggest using it probably ok for using (nolock) at all -

(google search) (amazon search)

Tue Mar 18 01:25:23 HKT 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…

Update: a message also discuss about this:

Update #2: A further analysis of ORM:

(google search) (amazon search)

Wed Jan 30 17:24:04 HKT 2008 From /weblog/database/vendor

oracle cookbook

After default install of oracle at win32, here is the step of creating user:

1) sqlplus /noloh (enter sqlplus shell without login)
2) connect oracle/oracle as sysdba (super user connection)
grant dba to CITY;

-- Grant/Revoke system privileges
grant create view to CITY;
grant unlimited tablespace to CITY;

imp CITY/city full=y file=c:\xxxxx.dmp

6) sqlplus city (try login)

About create table space

create tablespace TBL01 datafile 'H:\oracle\oradata\ora9\TBL01.dbf' size 50M extent management local segment space management auto;

Other useful views
1) sys.dba_tablespaces;
2) sys.dba_users
3) v$database
4) dba_data_files
5) v$datafile

Start and stop oracle batch
net start OracleCSService
net start OracleDBConsole[db name]
net start OracleOraDb10g_home1iSQL*Plus
net start OracleOraDb10g_home1TNSListener
net start OracleService[db name]

net stop OracleCSService
net stop OracleDBConsole[db name]
net stop OracleOraDb10g_home1iSQL*Plus
net stop OracleOraDb10g_home1TNSListener
net stop OracleService[db name]

An useful link:

How to install oracle in Redhat 9 -

Check store procedure issues: select * from user_errors

If there is join like a.column1 = b.column2, and column1 in DATE datatype while column2 is VARCHAR2 datatype, oracle will auto-cast it in JDeveloper, but always return false if I call that stored procedure using JDBC.

Show plsql function detail implementation: select text from user_source where name = MY_PROCEDURE order by line; -

It is possible to get NullPointerException from oracle driver with IBM JDK... it look like IBM issue -[..]s/forums/message.jspa?messageID=13980641

plsql result caching -[..]ed-up-database-code-with-result-caching/

Oracle date foramt -

(google search) (amazon search)

Tue Jan 08 21:30:32 HKT 2008 From /weblog/database

query table metadata

Several database using following query:

select column_name, numeric_precision as length, data_type from information_schema.columns where table_name='TEST'

H2, mysql and postgresql support above syntax

There difference of getColumnName() and getColumnLabel() , for SQL "select name as n from table" getColumnName() with return "n" and getColumnLabel() will return "name" -[..]you-using-resultsetmetadatagetcolum.html

(google search) (amazon search)