Rails每周闲碎(六): Database

1. 数据库的存储过程

 

    数据库的存储过程是为了提升sql语句的复用性和执行效率而出现的一种技术。

 

    它的优点有：

 

    1. 无需像sql语句一样，每次执行都要编译。这带来的是执行效率的提升。

 

    2. 重复使用。写好之后，可以重复调用。

 

    3. 提升应用程序的可移植性。无需改变程序代码，而只需改变存储过程，这为应用程序提高了可移植性。

 

    格式：

 

 

Create procedure procedue_name

   [@parameter data_type][output]

   [with]{recompile|encryption}

   as

        sql_statement

 

 

2. MS SQL Server snapshot

 

    A SQL server snapshot is essentially what it sounds like, it is a snapshot of the system that is stored and used as a primary system. When a snapshot occurs, the information is stored in the main drive and then further information created after the snapshot occurs is stored in a new data is stored in what is called a sparse file. A snapshot creates a backup of the system at that time, and uses 90% less drive space than backing up the system does. This information is stored for as long as the user requires it, and can be dropped when no longer needed.

 

    The purposes of a SQL server snapshot vary depending on the needs of the user. In some cases, it is used simply to back up the system without having to use so much drive space during the working day. In other situations, it is created when data is perceived as "ideal" and any new data needs to be compared to the data that was previously acquired. This information can be stored indefinitely and new snapshots can be created at any point to continue tracking specific data. When saving the user should simply take the name of the data and add a time stamp to it. This will ensure that there is no confusion when accessing the information as to what its purpose is.

 

    We used it to re-store our database at a time point, and it's quite faster than doing a restore from a backup.

 

3. _， %等字符对于mssql server有特殊含义

 

    rails没办法知道该不该escape这些字符，应该手动处理。

 

4. MS SQL Server的cluster index

 

    其它index都是逻辑index，而cluster index跟物理存储相关。一张表只能有一个cluster index，通常都是primary key。想通过改变cluster index来提升性能，是一种可接受的方案么？

 

   http://www.databasejournal.com/features/mssql/article.php/1443581/Index-Optimization-Tips.htm

 

5. index和性能

 

    要使数据库的组合index发挥作用，需要注意rails find方法中的select column顺序要和组合index的顺序保持一致。为了提升性能，总要忍受一些不那么美的事情。

 

6. sql server index: include clause

 

   Specifies the nonkey columns to be added to the leaf level of the nonclustered index. The nonclustered index can be unique or nonuniqe.

 

    http://jdixon.dotnetdevelopersjournal.com/using_the_sql_2005_create_index_include_clause.htm

 

7. ruby database gem

 

how to call store procedure from ruby?

 

http://sequel.rubyforge.org/documentation.html