分类:数据库技术

经验一、where 条件左边最好不要使用函数,比如:

           select …     where     datediff(day,date_column,getdate())>0
           这样即使在date_column列上建立了索引,也可能不会使用索引,而使用全表扫描。
            这样的语句要重新规划设计,保证不使用函数也能够实现。通过修改,一个系统过程的运行效率提高大约几十倍甚至上百倍!此外不要使用诸如like ‘%ab’,不能充分利用索引,而要在%前加字符。
  经验二、两个比较字段最好使用相同数据类型,而不是兼容数据类型。比如 int 与 numeric(感觉一般不是太明显)。
  经验三、复合索引的非前导列做条件时,基本没有起到索引的作用。
            比如 create index idx_tablename_ab on tablename(a,b)
                update tablename set c = XX where b>= XXX and …
                在这个语句中,只用了索引列b而没用索引列a做查询条件,基本上索引没有发挥作用。 导致表扫描引起blocking 甚至运行十几分钟后报告失败。
                一定要认真检查 改正措施: 在接口中附加条件
                    update tablename set c = XX  where  a = XXX  and b>= XXX
                 或者建立索引类似于
                    create index idx_tablename_ba on tablename(b,a)
  经验四、 多个大表的关联查询,如果性能不好,并且其中一个大表中取的数据比较少,可以考虑将查询分两步执行。
             先将一个大表中的少部分数据 select * into #tmp from large_table where …
             然后再用 #tmp 去做关联,效果可能会好不少。(前提:生成 #tmp表时,访问large_table 用到了比较好的索引,速度比较快)
   经验五、 tempdb 的使用。
              最好多用 select  into ,这样不记日志 ,尤其是有大量数据的报表时。虽然写起来麻烦,但值得。
                create table #tmp (……)这样写性能不好。尤其是大量使用时,容易发生tempdb 争用。
   经验六、 系统级别的参数设置
               一定要估计一下,不要配置太多,占用资源 ,配置太少,发生性能问题。
                 连接数,索引打开个数、锁个数 等、 当然 ,内存配置不要有明显的问题,比如,procedure cache
                不够 (一般缺省20%,如果觉得太多,可以减少一些)。如果做报表经常使用大数据量读,可以考虑使用 16K data cache
   经验七、索引的建立,很重要。
               clustered index     /nonclustered index 的差异,自己要搞清楚。各适用场合,另外如果
               clustered index 不允许 重复数,也一定要说明。
               索引设计是以为数据访问快速为原则的,不能 完全参照数据逻辑设计的,逻辑设计时的一些东西,可能对物理访问不起作用
    经验八、定期统计量数据的更新,更新频率看数据变化速度,一般数据发生10-15%左右变化就要进行统计量的更新,否则索引容易失效,update statistics
    经验九、定期
    经验九、强制索引使用
             如果怀疑有表访问时不是使用索引,而且这些条件字段上建立了合适的索引,可以强制使用
               select * from tableA (index idx_name) where …
              这个对一些报表程序可能比较有用。
    经验十、找一个好的监视工具
             工欲善其事,必先利其器,一点都不错呀。
              我用 DBArtisan ,监视哪些表被锁定时间长, blocking 等
              还有 sp_object_status 20:00:00 , sp_sysmon 20:00:00 等
    经验十一: 综合以上对IN/EXISTS的讨论,我们可以得出一个基本通用的结论:IN适合于外表大而内表小的情况;EXISTS适合于外表小而内表大的情况。
   另外,系统中确认不使用的中间数据,可以进行转移。这些要看系统的情况哦
          最后祝你好运气。
以上为个人经验,欢迎批评指正!
     呵呵 写完后忘记一个     一定要注意热点表 ,这是影响并发问题的一个潜在因素!

解决方法: 行锁模式 如果表的行比较小,可以故意增加一些不用的字段

     比如     char(200)     让一页中存放的行不要太多。
文章转自:http://www.2cto.com/database/201204/129094.html

数据库技术

Specifying I/O Size in a Query

If your SQL Server is configured for large I/Os in the default data cache or in named data caches, the optimizer can decide to use large I/O for:

Queries that scan entire tables

Range queries using clustered indexes, such as queries using >, <, > x and < ybetween, and like “charstring%”

Queries that use covering nonclustered indexes

In these cases, disk I/O can access up to eight pages simultaneously, if the cache used by the table or index is configured for it.

Each named data cache can have several pools, each with a different I/O size. Specifying the I/O size in a query causes the I/O for that query to take place in the pool that is configured for that size. See Chapter 9, “Configuring Data Caches” in the System Administration Guide for information on configuring named data caches.

To specify a particular I/O size, add the prefetch specification to the index clause of a selectdelete, or update statement. The syntax is:

select select_list from table_name     (index index_name prefetch size)
    [, table_name ...]
where ...
delete from table_name (index index_name prefetch size) ...
update table_name set col_name = value     from table_name (index index_name                     prefetch size) ...

Valid values for size are 2, 4, 8, and 16. If no pool of the specified size exists in the data cache used by the object, the optimizer chooses the best available size.

If there is a clustered index on au_lname, this query performs 16K I/O while it scans the data pages:

select * 
from authors (index au_names prefetch 16)
    where au_lname like "Sm%"

If a query normally performs prefetch, and you want to check its I/O statistics with 2K I/O, you can specify a size of 2K:

select type, avg(price)
    from titles (index type_price prefetch 2)
    group by type

Note: If you are experimenting with prefetch sizes and checking statistics i/o for physical reads, you may need to clear pages from the cache so that SQL Server will perform physical I/O on the second execution of a query. If the table or index, or its database, is bound to a named data cache, you can unbind and rebind the object. If the query uses the default cache, or if other tables or indexes are bound to the object’s cache, you can run queries on other tables that perform enough I/O to push the pages out of the memory pools.

Index Type and Prefetching

To perform prefetching on the data pages, specify either the clustered index name, or the table name. To perform prefetching on the leaf level pages of a nonclustered index (for covered queries, for example), specify the nonclustered index name.

Table 9-1: Index name and prefetching

Index Name Parameter Prefetching Performed On
Table name Data pages
Clustered index name Data pages
Nonclustered index name Leaf pages of nonclustered index

When prefetch Specification Is Not Followed

Normally, when you specify an I/O size in a query, the optimizer incorporates the I/O size into the query’s plan. However, the specification cannot be followed:

If the cache is not configured for I/O of the specified size, the optimizer substitutes the “best” size available.

If any of the pages included in that I/O request are in cache. If the I/O size specified is eight data pages, but one of the pages is already in the 2K pool, SQL Server performs 2K I/O on the rest of the pages for that I/O request.

If the page is on the first extent in an allocation unit. This extent holds the allocation page for the allocation unit, and only 7 data pages.

If there are no buffers available in the pool for that I/O size, SQL Server uses the next lowest available size.

If prefetching has been turned off for the table or index with sp_cachestrategy.

The system procedure sp_sysmon reports on prefetches requested and denied for each cache. See “Data Cache Management”.

set prefetch on

By default, SQL Server checks whether prefetching is useful for all queries. To disable prefetching during a session, use the command:

set prefetch off

To re-enable prefetching, use the command:

set prefetch on

If prefetching is turned off for an object with sp_cachestrategy, this command does not override that setting.

If prefetching is turned off for a session with set prefetch off, you cannot override it by specifying a prefetch size in a selectdelete, or insert command.

The set prefetch command takes effect in the same batch in which it is run, so it can be included in stored procedures to affect the execution of the queries in the procedure.

数据库技术

数据库存取缓冲区的LRUMRU算法

 

1.Cache Hit and Cache Miss

当使用者第一次向数据库发出查询数据的请求的时候,数据库会先在缓冲区中查找该数据,如果要访问的数据恰好已经在缓冲区中(我们称之为Cache Hit)那么就直接用缓冲区中读取该数据.

反之如果缓冲区中没有使用者要查询的数据那么这种情况称之为Cache Miss,在这种情况下数据库就会先从磁盘上读取使用者要的数据放入缓冲区,使用者再从缓冲区读取该数据.

很显然从感觉上来说Cache Hit会比Cache Miss时存取速度快.

 

2.LRU(最近最少使用算法) and MRU(最近最常使用算法)

所谓的LRU(Least recently used)算法的基本概念是当内存的剩余的可用空间不够时,缓冲区尽可能的先保留使用者最常使用的数据,换句话说就是优先清除”较不常使用的数据”,并释放其空间.之所以”较不常使用的数据”要用引号是因为这里判断所谓的较不常使用的标准是人为的、不严格的.所谓的MRU(Most recently used)算法的意义正好和LRU算法相反.

下面我们通过Oracle 9i Cache中对LRU和MRU的使用来看一下两者在缓冲区工作机制中的作用和区别

     在Oracle 9i中有LRU List的概念我们可以把LRU List想象成是一连串的缓冲区集合,两端分别是LRUMRU, 当数据库从磁盘上读取数据放入缓冲区时,系统必须先确定缓冲区中有free buffers,这个时候Oracle 9i会扫描LRU List,扫描的基本原则是

1.     LRUMRU;

2.     当扫描到free buffer或已扫描的缓冲区数目超过临界值时,就会停止扫描动作;

      如果在扫描过程顺利的在LRU List中找到了free buffer,那么Oracle 9i就把从磁盘读出的数据写到free buffer中然后把free buffer加到LRU List的MRU.

      那如果扫描过程没有在LRU List中找到free buffer怎么办?当然是从LRU List的LRU开始清除缓冲区,如此一来就可以腾出新的空间了.

      下图就是一个例子

          使用者查询数据A,初始的时候LRU List中没有数据A,于是Oracle 9i到磁盘读取A,然后放到LRU List的MRU,使用者再从LRU List中读取数据A,同理对于B,C…当LRU List满了以后,如果使用者查询N,此时N不在LRU List中而且LRU List中已经没有free buffer了,此时Oracle 9i就开始从LRU淘汰A以腾出空间存放N.

 

                      图 1

 

我们再来看另外一种情况

    在State 3之后,恰好使用者持续的查询A—这将会导致A一直被放置在靠近MRU的缓冲区,结果将如图State m’所示,你会发现图2的State m’与图1的State m缓冲区存放的数据完全一样但是存放位置不一样.此时LRU List满了,如果再放N的时候LRU List`淘汰的是B,因为A的查询率高于B,所以LRU List让A在缓冲区中呆上较长的时间而先淘汰掉”较不常用的”的B.

                             图 2

   

本文转载自:http://news.dayoo.com/tech/201005/21/10000617_102079584.htm

数据库技术

如果只是查看sybase中的索引和主键,可以用sp_helpindex系统存储过程。

如果想删除特定名称的主键和索引,可以直接写drop语句和alter table语句。

如果想动态的删除sybase数据库中的索引和主键,则需要用到一下几个表

sysobjects每个数据库中都有

每个表、视图、存储过程、扩展存储过程、日志、规则、缺省值、触发器、检查约束、参照约束、计算列、基于函数的索引键和(仅在  tempdb中)临时对象以及其它形式的编译对象在  sysobjects  中都有相应的一行。对象  type   为 N 时,每个分区条件 ID  在 sysobjects  中也都有相应的一行。

主要字段有:

name varchar(255) not null 对象名
id int 对象 ID
uid int 对象所有者的用户 ID
type char(2) 可以为以下对象类型之一:
C – 计算列
D –  缺省值
F – SQLJ 函数
L –  日志
N –  分区条件
P – Transact-SQL 或 SQLJ  过程
PR – 准备对象(由 Dynamic SQL  创建)
R – 规则
RI –  参照约束
S – 系统表
TR – 触发器
U –  用户表
V –  视图
XP – 扩展存储过程

 

sysindexes

每个聚簇索引、每个非聚簇索引、每个没有聚簇索引的表和每个包含 text   或  image  列的表在  sysindexes   中都有相应的一行。该表对于每个基于函数的索引或在计算列上创建的索引也都有相应的一行。

主要字段有:

name varchar(255)
null
索引或表名
id int 索引的 ID  或索引所属的表的 ID
indid smallint
• 0  — 如果是表
• 1  — 如果是所有页锁定表上的聚簇索引
• >1  —  如果是 DOL  锁定表上的非聚簇索引或聚簇索引
• 255  —  如果是  text 、image 、文本链或 Java  行外结构(大对象,即
LOB 结构)

status smallint 内部系统的状态信息

其中status列的相关说明如下:

1、官方文档

十进制
十六进
制状态
10×1如果试图插入重复键,则中止当前命令或触发器
20×2唯一索引
40×4如果试图插入重复行,则中止当前命令或触发器;对于 DOL  锁定表,则始终为 0
16 0x10 聚簇索引
64 0x40 如果是所有页锁定表,索引允许重复行;如果是 DOL  锁定表,则始终为 0
128 0x80 已排序的对象;对于没有聚簇索引的表或文本对象,则不设置
512 0x200
create index  语句中使用的  sorted data 选项
2048 0x800 对主键的索引
32768 0x8000 可疑索引;使用另一种排序顺序创建索引

2、谷歌结果

case when i.status = 0 then ‘普通索引’
when i.status = 2 then ‘唯一索引’
when i.status = 16 then ‘聚簇索引’
when i.status = 2048 then ‘主键索引’
when i.status = 2050 then ‘主键唯一索引’
when i.status = 2066 then ‘‘
when i.status = 16402 then ‘聚簇唯一索引’
when i.status = 2097152 then ‘有字段允许为空的普通索引’
when i.status = 2097154 then ‘有字段允许为空的唯一索引’

好像主键的条件是status&2048=2048,而这个规则在多个DBMS中都适用,比如MS SQL SERVER和mysql

 

附上完整SQL
declare @TABLE_NAME varchar(50)
set @TABLE_NAME=’ht_authlog_t1′

while 1=1
begin

declare @PK_NAME varchar(50)
set @PK_NAME=null
declare @sql varchar(1000)
set @sql=null

select @PK_NAME=name from sysindexes where indid>0 and indid<255 and status&2048=2048 and id in
(select id from sysobjects where [email protected]_NAME)

if @PK_NAME is null
begin
print ‘pk name is null’
break
end
else
begin

print ‘pk name is not null’
print @PK_NAME
select @sql=’alter table ‘[email protected]_NAME+’ drop constraint ‘[email protected]_NAME
exec(@sql)

end
end

go
—————————————————————————-

declare @TABLE_NAME varchar(50)
set @TABLE_NAME=’ht_authlog_t1’

while 1=1
begin

declare @INDEX_NAME varchar(50)
set @INDEX_NAME=null
declare @sql varchar(1000)
set @sql=null

select @INDEX_NAME=name from sysindexes where indid>0 and indid<255 and status&2048!=2048 and id in
(select id from sysobjects where [email protected]_NAME)

if @INDEX_NAME is null
begin
print ‘index name is null’
break
end
else
begin

print ‘index name is not null’
print @INDEX_NAME
select @sql=’drop index ‘[email protected]_NAME+’.’[email protected]_NAME
exec(@sql)

end
end

 

 

数据库技术

最近在做大数据量清除的任务,从1亿条数据中删除3000万条,怎么弄,直接delete,时间上不仅难以忍受,更容易造成日志空间溢出和死锁。另外,如果发生回滚,这将是灾难性的。

一种比较好的做法是利用rowcount分批进行删除。每次删除50W条左右即可,每次利用日志空间1G,然后就会做日志清理。至于为什么每次利用1G后做清理,跟两个东西有关。一个是trunc log on chkpt,另一个就是checkpoint。

trunc log on chkpt的意思就是系统在做checkpoint的时候进行日志截断清除。那么系统到底什么时候做checkpoint呢,只能从官网找了:

各位看官老爷请看下文吧,懒得翻译了:

 

Automatic checkpoint procedure

Approximately once a minute, the checkpoint task checks each database on the server to see how many records have been added to the transaction log since the last checkpoint. If the server estimates that the time required to recover these transactions is greater than the database’s recovery interval, Adaptive Server issues a checkpoint.

The modified pages are written from cache onto the database devices, and the checkpoint event is recorded in the transaction log. Then, the checkpoint task “sleeps” for another minute.

To see the checkpoint task, execute sp_who. The checkpoint task usually appears as “CHECKPOINT SLEEP” in the “cmd” column:

fid  spid  status      loginame  origname  hostname         blk_spid  dbname
        tempdbname    cmd                block_xloid   threadpool
---  ----  ----------  --------  --------  ----------------  --------  ------
        ----------    -----------------  -----------   -------------------
. . .
0       2    sleeping      NULL      NULL                NULL       0  master
            tempdb     DEADLOCK TUNE              0    syb_default_pool
0       3    sleeping      NULL      NULL                NULL       0  master
            tempdb     ASTC HANDLER               0    syb_default_pool
0       4    sleeping      NULL      NULL                NULL       0  master
            tempdb     CHECKPOINT SLEEP           0    syb_default_pool

数据库技术

SYBASE数据库方面BCP说明

BCP是SYBASE公司提供专门用于数据库表一级数据备份的工具。

一般存放在所安装的ASE或者Open Client 的BIN目录中。

12版本以前的ASE,bcp存放目录为 $SYBASE/bin

12版本(含12版本)以后存放目录为 $SYBASE/OCS-12_x/bin

其中$SYBASE为SYBASE安装目录,12_x代表12.0、12.5版本,显示为12_0或者12_5

可执行文件名称为bcp.EXE

参数列表如下

(可用 bcp 得到)

usage: bcp [[database_name.]owner.]table_name[:slice_number] {in | out} datafile

[-m maxerrors] [-f formatfile] [-e errfile]

[-F firstrow] [-L lastrow] [-b batchsize]

[-n] [-c] [-t field_terminator] [-r row_terminator]

[-U username] [-P password] [-I interfaces_file] [-S server]

[-a display_charset] [-q datafile_charset] [-z language] [-v]

[-A packet size] [-J client character set]

[-T text or image size] [-E] [-g id_start_value] [-N] [-X]

[-M LabelName LabelValue] [-labeled]

[-K keytab_file] [-R remote_server_principal]

[-V [security_options]] [-Z security_mechanism] [-Q]

常用数据备份格式为:

bcp dbname..tablename out c:\temp\filename -Usa -Ppassword -Sservername -c

即可。

其中 -U后为SYBASE登录名称,-P后为SYBASE登录口令,-S后为SYBASE服务名称,-c代表使用可见文本方式导出数据

如果为数据恢复只需要将out 替换为 in 即可。

可用如下方法生成一个可以一次导出一个数据库中所有表的数据的执行脚本

编辑一个如下文本文件 文件名称例为 bcpscript:

use dbname 选中将要导出数据的数据库

go

select ‘bcp dbname..’ + name + ‘ out c:\temp\’ + name + ‘ -Usa -P -Ssybcdsrv -c’ from sysobjects where type = ‘U’ 在sysobjects系统表中type为U的表为用户表,系统表为S。

go

用如下格式执行

isql -Usa -Ppassword -Sservername -i bcpscript -o bcpout.bat

i参数后为输入文件,o参数后文件为输入文件执行后得到的输出文件。

执行后可以得到一个后缀名为 BAT 的批处理文件(在unix下则生成一个shell文件并更改相应的执行权限),可直接执行。即在指定的目录下导出了相应的数据文件。一个表的数据为一个文件。如在UNIX下则可不用BAT后缀。

备份得到的数据文件如果需要重新往数据库中恢复,只需要将上面操作步骤中BCP命令中的out 参数换为in参数即可。

注意:在对正式数据做操作前最好先作一些测试。

另外,如果牵涉到使用bcp进行字符集的更改,可以采用 -J charset 参数进行

关于BCP等工具的具体使用指南,请参见SYBASE相关文档。

针对系统移植所需做的工作,应该有如下几个步骤:

1. 安装新环境的硬件环境,包括网络,硬盘状况;

2. 安装新环境的操作系统,包括Service Pack;

3. 安装相同版本的SYBASE数据库产品,包括补丁;

4. 添加数据库用户,设备等相关信息,应与老系统中一致

5. 创建新系统数据库;

6. 利用你所拥有的表脚本或者通过SYBASE CENTRAL中的生成DDL功能,将老系统中的建表脚本导出,生成数据库中的表.最好将建表脚本与建立表上约束(主键,外键等)的脚本分开,先在表上不建约束,在数据导入后,再加上.;

7. BCP OUT 老系统中数据,根据上面提到的方法;

8. 运行建立其他对象的脚本,包括索引,主键,外键及存储过程,触发器,缺省等;

9. 在新系统中对需要更改的表结构做更改,或者添加新表;

10. 测试应用系统是否工作正常.

 

 

 

 

bcp    说明 以用户指定的格式将数据库表复制到操作系统文件或从操作系统文件中复制出来。bcp 位于$SYBASE/$SYBASE_OCS/bin 中。
Windows NT 实用程序是 bcp.exe,它位于 %SYBASE%\%SYBASE_OCS%\bin 中。

语法 bcp [[database_name.]owner.]table_name [:slice_number] {in | out} datafile
[-m maxerrors]
[-f formatfile]
[-e errfile]
[-F firstrow]
[-L lastrow]
[-b batchsize]
[-n]
[-c]
[-t field_terminator]
[-r row_terminator]
-U username
[-P password]
[-I interfaces_file]
[-S server]
[-a display_charset]
[-z language]
[-A packet_size]
[-J client_charset]
[-T text_or_image_size]
[-E]
[-g id_start_value]
[-N]
[-X]
[-K keytab_file]
[-R remote_server_principal]
[-V [security_options]]
[-Z security_mechanism]
[-Q]
[-Y]

bcp -v
参数     database_name
如果正被复制的表位于缺省数据库或 master 中,则此参数是可选项。否则,必须指定数据库名。

所有者
如果您或数据库所有者拥有正被复制的表,则此参数是可选项。如果未指定所有者,bcp 首先查找您所拥有的表是否有此名称,然后查找
数据库所有者所拥有的表是否有此名称。如果其他用户拥有此表,则必须指定所有者名,否则命令将失败。
view_name
是正在拷出的视图名。
table_name
是要复制的数据库表的名称。表名不能是 Transact-SQL 保留字。分区号 partition_number 并不存在于表 table_name 中。
slice_number
是要复制的数据库表的数据段的编号。
partition_id
是要复制到的分区的标识符。
in | out
是复制方向。in 表示从文件向数据库表中复制;out 表示从数据库表或视图向文件复制。
datafile
是操作系统文件的全路径名。路径名的长度可以是 1 到 255 个字符。
-m maxerrors
是在 bcp 中止复制之前,允许的最大非致命错误数。bcp 放弃不能插入的每一行 (由于转换错误,或尝试向不允许有空值的列中插入空值〕,同时将每个被放弃的行计为一个错误。如果不包括此参数,
bcp 将使用缺省值 10。
– f formatfile
是文件的完整路径名,该文件存储着上次在同一个表上使用 bcp 时的应答。回答 bcp 的格式提问后,它提示您将回答保存到一个格式文件中。格式文件的创建是可选的。缺省文件名是 bcp.fmt。bcp 程序可以在复制数据时引用格式文件,这样用户就不必以交互式方式重复以前的格式应答。只有在用户以前创建了希望现在用于拷入或拷出的格式文件的情况下,才使用 -f 参数。如果不指定此参数,bcp 将以交互方式向用户询问有关格式的信息。
– e errfile
是错误文件的全路径名,bcp 在其中存储无法从文件传送给数据库的所有行。来自 bcp 的错误消息显示在终端上。bcp 只在指定此参数后才创建错误文件。
– F firstrow
是要从输入文件中复制的第一行的行号 (缺省为第一行) 。在执行多进程复制这一繁重任务时应避免使用 -F 选项,因为它通常导致 bcp 占用更多资源来运行,且不能加快进程。而应将 -F 用于单个进程,进行即席复制。
– L lastrow
是要从输入文件复制的最后一行的行号 (缺省为最后一行)。
– b batchsize
是每批数据中被复制的行数 (缺省为复制一批数据的所有行)。批量复制只适用于批量拷入;对批量拷出不起作用。bcp 接受的 batchsize 的最小数值为 1。
– n
使用本机(操作系统)格式执行复制操作。指定 -n 参数意味着 bcp 将不对每个字段进行提示。使用本机数据格式的文件为人工不可读格式。
– c
使用 char 数据类型作为数据文件中所有列的缺省数据类型来进行复制操作。如果要在平台间共享数据,则使用这种格式。此参数不对每个字段进行提示;它使用 char 作为缺省的存储类型,没有前缀,使用 \t(制表符)作为缺省的字段终结符,并且使用 \n (换行符)作为缺省的行终结符。
– t field_terminator
指定缺省的字段终结符。
– r row_terminator
指定行终结符。
-U username
指定 Adaptive Server 的登录名。
– P password
指定 Adaptive Server 口令。如果没有指定 -Ppassword,bcp 将提示输入口令。如果口令是 NULL,可以省略 -P 标志。
– I interfaces_file
指定连接到 Adaptive Server 时要搜索的接口文件的名称和位置。如果没有指定 -I,bcp 将在 SYBASE 环境变量(Windows NT 中的 ini 目录)指定的目录中查找接口文件 (Windows NT 中的 sql.ini)。
– S server
指定要连接到的 Adaptive Server 名。如果指定不带参数的 -S,bcp 将使用 DSQUERY 环境变量指定的服务器。
– a display_charset
允许用户从终端上运行 bcp,而此终端上的字符集与正在运行 bcp 的计算机上的字符集不同。将 -a 和 -J 联用以指定转换所需的字符集转换文件 (.xlt 文件) 。只有在客户端字符集与缺省字符集相同时,才单独使用 -a,而不使用 -J。

如果使用 -a 参数命名的字符转换文件遗漏或输错名称,将出现如下错误消息:
Error in attempting to determine the size of a pair of translation tables.:’stat’ utility failed.
– z language
是服务器用来显示 bcp 提示和消息的替代语言的正式名称。没有 -z 标志时,bcp 使用服务器的缺省语言。可以在安装期间或安装之后,使用 langinstall 实用程序 (或 Windows NT 中的 langinst)或 sp_addlanguage 存储过程向 Adaptive Server 添加语言。
如果使用 -z 参数指定了不正确或无法识别的语言,会显示如下错误消息:
Unrecognized localization object. Using default value ‘us_english’.Starting copy…=> warning.
– v
显示 bcp 的版本号和版权消息,并返回操作系统。
– A packet_size
指定用于此 bcp 会话的网络包大小。例如:bcp pubs2..titles out table_out -A 2048为此 bcp 会话将包大小设置为 2048 字节。 packet_size 必须介于 default network packet size 和 maximum network packet size 配置变量的值之间,且必须是 512 的倍数。使用大于缺省值的网络包大小可提高大批量复制操作的性能。
– J client_charset
指定在客户端上使用的字符集。bcp 使用过滤器在 client_charset 和Adaptive Server 字符集之间转换输入。
-J client_charset 请求将 Adaptive Server 字符集与客户端上使用的字符集 client_charset 进行相互转换。
不带参数的 -J 将字符集转换设置为 NULL。这样不会发生任何转换。若客户端与服务器使用相同的字符集,则使用该设置。
如果省略 -J,则字符集将设置为平台的缺省字符集,且不必是客户端正使用的字符集。
如果使用 -J 参数指定了不正确或无法识别的字符集,将显示如下错误消息:
Unrecognized localization object. Using default value ‘iso_1’.Starting copy…=> warning.
– T text_or_image_size
允许以字节为单位指定 Adaptive Server 发送的 text 或 image 数据的最大长度。缺省值为 32K。如果 text 或 image 字段大于 -T 的值或缺省值,bcp 不会发送此溢出值。
– E
显式指定表的 IDENTITY 列的值。缺省情况下,在向包含 IDENTITY 列的表中批量复制数据时,bcp 将为每行指派一个临时的 IDENTITY 列值 0。这仅在向表中复制数据时有效。bcp 从数据文件中读取 ID 列的值,但是不将值发送给服务器。相反,在 bcp 向表中插入每一行时,服务器为每行指定一个唯一的、连续的 IDENTITY 列值,起始值为 1。如果向表中复制数据时指定了 – E 标志,bcp 将从数据文件中读取列值,并将该值发送给向表中插入该值的服务器。如果插入的行数超过了 IDENTITY 列可能的最大值,Adaptive Server 将返回一个错误。将数据批量复制出来时,-E 参数不起作用。Adaptive Server 会将 ID 列复制到数据文件,除非您使用 -N 参数。
不能同时使用 -E 和 -g 标志。
– g id_start_value
指定将 IDENTITY 列的值作为拷入数据的起点。
不能同时使用 -g 和 -E 标志。
– N
跳过 IDENTITY 列。在以下情况下使用该参数:拷入数据时主机数据文件不包含 IDENTITY 列值的占位符;或者拷出数据时不希望主机文件中包含 IDENTITY 列信息。拷入数据时,-N 和 -E 参数不能同时使用。
– X
指定在与服务器的这次连接中,应用程序启动带客户端口令加密的登录。bcp (客户端)通知服务器希望进行口令加密。服务器返回一个密钥,bcp 使用此密钥加密口令,然后服务器在口令到达时使用此密钥对它进行鉴定。如果 bcp 崩溃,系统将创建一个包含用户口令的核心文件。如果未使用加密选项,则口令在文件中以纯文本格式显示。如果使用加密选项,则口令不可读。
– K keytab_file
指定用于 DCE 中鉴定的 keytab 文件的路径。
– R remote_server_principal
按照安全性机制的定义为服务器指定主管名。缺省情况下,服务器的主管名与服务器的网络名 (由 -S 参数或 DSQUERY 环境变量指定)相匹配。当服务器的主管名和网络名不一致时,使用 -R 参数。
– V security_options
指定基于网络的用户鉴定。使用此选项时,用户必须在运行实用程序之前登录到网络的安全系统。在这种情况下,用户必须用 -U 选项提供其网络用户名;任何用 -P 选项提供的口令都将被忽略。
– V 后面可接一组 security_options 关键字母选项来启用其它安全服务。
这些关键字母包括:
· c - 启用数据保密性服务
· i - 启用数据完整性服务
· m - 启用连接建立过程中的相互鉴定
· o - 启用数据源加戳服务
· r - 启用数据重放检测
· q - 启用顺序混乱检测
– Z security_mechanism
指定用于连接的安全性机制名称。
在 $SYBASE/install/libtcl.cfg 配置文件中定义安全性机制名称。如果不提供 security_mechanism 名称,则使用缺省机制。
– Q
提供对 bcp 版本 10.0.4 的向后兼容性,以用于可空列的复制操作。
– Y
指定字符集转换在服务器中被禁用,而是在使用 bcp IN 时由 bcp 在客户端上执行。

 

 

 

 

 

 

 

 

 

BCP in
bcp “db_ams..tblPatchPolTrxnMthly1101” in “D:\PatchData_1101.txt” -b 3000 -c -t! -S server_name -U username  -P password

BCP out
bcp “db_ams..tblBusnSrc_Alt_Pol_Txn_Mthly03” out “D:\PatchDataAlt_1103.txt” -c -t! -S “server_name” -U “username” -P “password”

 

http://blog.csdn.net/wwp1026/article/details/6900569

数据库技术

在数据库实现中,通过锁定机制控制数据库的并发访问,保证数据库访问的正确性。根据定义:

锁定是一种并发控制机制:它可以确保数据在同一事务中和不同事务之间保持一致。在多用户环境中,由于几个用户可能会在同一时间使用同一数据,因此需要锁定功能。

  sybase锁分类

按照锁性质可以分为共享锁,排他锁。当在数据库事务中,读取信息时,会对数据库添加共享锁。当修改信息时,会添加排他锁。

按照锁的粒度,可以分为行锁,页锁,表锁等。

sybase隔离级别

sybase分为0,1,2,3四个隔离级别。

0 读取未提交的,允许事务读取未提交的数据更改,排他锁在对数据库进行写操作后立即释放,不会持有到事务提交或回滚。

1 读取已提交的,仅允许事务读取已提交的数据更改,排他锁持有到事务提交或回滚,但共享锁在加载数据到内存后立即释放。

2 可重复读取事务可重复同一查询,事务读取过的任何行都不会被更新或删除,排它锁和共享锁都会持有到事务结束,查询结果集不可以删除和修改,但是可以插入。

3 可串行化读取事务可重复同一查询,且得到完全相同的结果。不能插入任何将出现在结果集中的行,排它锁和共享锁都会持有到事务结束,查询结果集不可以增删改。

可以使用select @@isolation语句查看数据库的隔离级别。

sybase数据库通常隔离界别设置为1,值得注意的是使用WAS通过jdbc连接数据库上,经常会将隔离级别提升为2。在使用E-SQL编译时,通常将隔离级别提升为3。

  sybase死锁

sybase数据库出现死锁,即处于死锁中的各事务,都持有锁,但又都在等待其他锁,从而组成一个环,造成死锁。

最简单的死锁情况,事务T1,T2,执行顺序相反,会造成死锁,情形如下:

 

执行顺序

T1 T2
1 排他锁A
2 排他锁B
3 排他锁B
4 排他锁A

 

这时候,会出现事务T1持有排他锁A,同时等待排他锁B,事务T2持有排他锁B,等待排他锁A。这是就造成了T1等待T2释放排他锁B,T2等待T1释放排他锁A,形成一个死锁环。

多个事务出现死锁时,情形与两个事务死锁相比,只是环更大了一些,环上的节点多了一些。其本质仍然是形成一个等待环。

  隔离级别对死锁的影响

隔离级别同样会对锁定有很大的影响,例如,

情形一、

 

执行顺序

T1 T2
1 排他锁A
2 排他锁B
3 共享锁B
4 共享锁A

 

当隔离级别为0时,不会出现死锁。当隔离界别为1,2,3时,则会发生死锁。

情形二、

 

执行顺序

T1 T2
1 共享锁A
2 共享锁B
3 排他锁B
4 排他锁A

 

当隔离级别为0,1时,不会出现死锁。当隔离界别为2,3时,会发生死锁。

情形三、

该情况是最近在系统中发现的一个死锁问题。程序从文件导入数据到数据库中,每次导入一条记录时,首先尝试以update的方式导入一条记录,当找到记录为空时,则将该条记录更改为以insert的方式导入到数据库中。

同时,导入过程是由多个进程共同完成的,每个进程导入一个文件,多个进程同时工作,然而当程序运行时,多个进程同时导入出现死锁。

通过查看日志,发现死锁都是发生在insert时,出现next-key lock。

通过检查数据库的隔离级别,为1,没有发现异常,百思不得其解。

后在程序中添加查询数据库隔离级别语句,以检查在程序运行中到底隔离级别是多少?

经检查,隔离级别为3,也就是说在事务中,不能插入任何将出现在结果集中的行,下面分析一下出现死锁的原因。

当两个进程同时插入记录到同一个间隙中时,每个事务可能由两个操作组成

1.update

2.insert,当update结果集为空时,则转为insert。

其执行过程中,两个进程可能出现以下运行情况

 

执行顺序

T1 T2
1 共享锁A
2 共享锁B
3 排他锁B
4 排他锁A

 

例如,目前数据库只有一条记录,主键为5,此时T1,T2分别插入主键为3,4的数据,由于两个事务都在运行之中,因此T1,T2都会尝试在5之前插入数据,首先其在update时,会产生共享锁,由于隔离级别为3,此时两个事务尝试插入时都会失败,要解决这种死锁,可以在程序中显式设置隔离级别为1。

  sybase锁升级

sybase同时提供锁升级的功能,例如将行锁升级为页锁,将页锁升级为行锁。具体参数可以进行设置。

例如当某一页中90%的行都被锁定,那么此时sybase可能将这些多个行锁升级为一个页锁,锁定整个页。这也是造成死锁一个重要的原因。

有时,根据判断,不会产生死锁。

 

执行顺序

T1 T2
1 行级排他锁A
2 行级排他锁B
3 行级排他锁C
4 行级排他锁D

 

在上述情况中,如果没有锁升级机制,是无论如何也不会产生死锁的。但是当有了锁升级机制之后,可能T1在将行级锁A升级为页锁Pa,T2将行锁B升级为页锁Pb,而T1需要访问的行C在页Pb中,T2需要访问的D在也Pa中,这时就会构成一个锁定环,构成死锁。

总结

在软件系统实现中,经常会采用数据库。使用数据库时死锁问题是大家通常都会遇到的,遇到死锁首先需要分析原因,定位问题,这也是最关键的一步。

出现死锁的原因很多,但本质一定是多个进程出现了相互等待,而每个进程又都持有锁,从而形成了一个依赖关系环,因此问题最关键的就是找到这个依赖关系,以及是哪几个事务,哪几个锁导致了死锁,只要确定了这几点,死锁问题将迎刃而解。

数据库技术

所支持的加锁机制

全页加锁

全页加锁既是一个新术语,它又是由ASE(Adaptive Server Enterprise)在过去所支持的一种加锁类型这种类型有下列特性:对所有可被访问的页面在页面级加锁;

当各种类型的页面以任何方式发生改变时,对这些排它性的页面进行加锁;而且这种加锁机制一直保持到该事务终止;

当下一个所需的页面已经成功地获得,对那些已经释放的的当前访问页进行共享页面加锁(如果采用了第三层ANSI隔离,则把这种加锁机制保持到该事务终止为止) 采用页级时间印记(timestamp)以确定是否发生改变,详细信息记录在事务日志中,以便在系统恢复时以向前或向后方式使用

这种加锁方式常常提供性能最高的解决方案,特别是当应用设计时已经考虑了这些特性时更是如此但是,有一些应用系统,当发生某些活动时,这种对整个页面进行加锁的方式就可能会对系统性能产生有重大意义的影响对于那些面对诸如文件系统或其它已经支持更细小尺度加锁机制的数据库厂家产品的一般环境而设计的应用系统而言,这种情况尤其如此
此外,还存在一系列问题,它们要围绕着更加困难的条件进行工作它们通常要采用更加具有Sybase特性的解决方案。对于商用的应用软件制造厂商而言,对他们是一个挑战,因为这将要求他们必须跨越他们所支持的数据库平台,去完成维护其原代码的工作,而这个工作有相当工作量在这个领域的基本问题如下:
对已经按照升序值创建的非群聚性索引的最末端叶型页面存在着争议;对非群聚性索引的表进行插入和查询时可能发生死锁;  在按照群聚性的索引值进行更新和对非群聚性索引的表进行查询访问之间可能发生死锁; 在没有作索引的表的最后一行可能发生冲突(尽管对最后的特定地址可以使用分区) ;有可能使行数很少的表之间发生潜在的冲突(尽管对特定的地址可以使用填充因子[ fillfactors]和每页最大行数[ max_rows_per_page]这两个参数)  ;对每个页面两边进行加锁的需要常常被分割开来; 如果一个表特别小,以致在一个单一页面中进行驻留,那么对单一行的访问实际上将破坏对整个表的加锁机制
仅对数据加锁

仅对数据加锁机制试图去解决本文前一节所关注的主要问题(其他的议题将在其它功能领域中加以解决)这种加锁方式支持两类不同的工作方式: 数据行加锁和数据页加锁。在这两种情况中,对于它们所支持的加锁方式,都与以前的加锁机制有所不同仅对数据加锁具有下列特性:
在索引页面中不会破坏事务加锁。相反,而是采用了一种称之为锁存的机制锁存是一种类似于旋转锁(spinlocks)的同步方法,它们与事务无关并且只保留很短的周期(一般而言,当一个任务在数据库中物理上改变一小片数据时,这个周期相当于在共享存贮区中在一个2K的页面改变某些字节数据的时间)一旦完成之后,这个任务将直接打开这个锁存当这种情况还可能临时同其它组块时 ,因为这种锁存不能对服务器任务进行有上下文的切换,也不能涉及死锁,并且只能保持主要的一小段时间,所以它们不能产生有显著意义的争用

采用一个RID对单一行进行数据行加锁(行标识[RID—-Row ID]是逻辑页号与所在页面上该行号的组合);支持固定的行标识 RIDs, 它可以是向前的,允许不进行其RID的改变,就完成数据行的移动当一行变大超过了它的可用空间时,采用上述结果对非群聚索引不需要进行任何改变。

不需要进行任何争用就可以在表的尾部进行插入操作,这一功能已经增加进来。.
支持采用范围加锁、下一个关键字加锁和无限大加锁等方式对逻辑范围值进行加锁
支持由最顶层操作所导致的页面分割这些情况直接加以提交,”系统”事务可以导致在更短一点的时间周期里保持分裂的页面处于锁定状态。

为了支持这些变化,需要对采用的存贮表结构进行一系列改善这些改进的主要效果如下:
群聚索引现在被存贮为象许多人所熟悉的IBM DB2产品所采用的“放置索引”(”placement indexes.”)方式这种结构类似于非群聚性的索引,需要类似的空间总量。这种修正的结构导致了在数据初始存贮时可以按照顺序跨数据页进行存储,但是当发生插入时,它们就要尽可能紧密存放以便在正确的逻辑页面中不存在页面分割此外,在数据页中的数据顺序在新行增加时是不进行维护整理的。这种索引的应用使每个群聚化的索引周游增加了一次I/O操作

行位移表已经增加到索引页和数据页中。这种增加和新的行索引行存贮格式具有使每个索引页面所存贮的索引条目个数减少的潜在能力

固定行标识(RIDS)。当一行移动时,对于分配新行位置的向前地址被放在用于驻留该行的位置上当这种移动需要改变非群聚性索引时,对该行的访问需要增加一次I/O操作以得到‘向前’的位置。

一般而言,索引将更小和更短,这是因为如下原因:从每个叶级页面中采用双重键限制机制来限制双重键(Duplicate key)例如,如果值“绿”(”GREEN”)在下列行标识(RIDs)值等于123-1,234-2,和345-3的行中, 就分别存贮值“绿”(”GREEN”),123-1,234-2,345-3,而不是存贮值“绿”(”GREEN,”)三次在每个索引页中每个值只存贮一次。

在非群聚性索引树的非叶型结点中将后缀实行压缩(例如,如果键值是”GREEN”和”HAMILTON”,而在这两个值之间发生分裂,那么就在非页级索引页面中存储”G”和”H”)
数据页和数据行加锁
只对数据加锁机制支持两种方式:数据页加锁和数据行加锁这些与它们的工作方式和所提供的功能相类似这两种方式仅在对数据访问产生阻碍作用时,在加锁的尺度上有所区别。在数据页加锁方式下再采用数据行加锁方式具有两种作用(一种起正向作用,另一种起反向作用)首先,较小尺度加锁机制的使用可能导致减少争用与冲突,然而当大量数据发生变化时,就有可能对加锁产生大量阻碍的情况发生

特定使用的加锁类型
除非对配置参数加以特定,对所有的表都予置了隐含的全页面加锁机制
sp_configure ‘lock scheme’, [allpages | datapages | datarows]
当数据库从原先版本的服务器中转储出来重新加载时,所有的表都被定义为全页面加锁的表当建立一个新表时,可以不使用这个缺省值,可采用如下的句法格式:
create table ;… lock [allpages | datapages | datarows]
为了在使用的一个表中改变加锁类型,可以采用如下的句法格式:
alter table ; lock [allpages | datapages | datarows]
在一个现存的表中改变加锁方式,将引起下列三种行动后果发生:
首先,如果一张表从全页加锁转变为仅对数据加锁,或者从仅对数据加锁转变为全页加锁,在这两种类型之间就要对表进行选择以允许进行存贮格式改变如果这是一个分区表,就要同时假定必要的并行级别和工作线程已经配置好的情况下,才能执行。

其次, 对表中的群聚性索引必须重新创建因为我们能保证数据,所以如果从全页加锁方式转换为只对数据加锁时,这种重新创建可以通过”with sorted_data”来完成然而,当从仅对数据加锁机制转换为全页加锁方式时,就要进行并行的索引创建操作(请注意:如果这是一个分区表时,那么并行等级和工作线程的数目必须加以配置才能允许进行这种改变,否则这种迁移将会失败)

最后,非群聚性的索引将被重建,如果服务器已经为并行处理所加以配置,当进行本步骤时将加以采用

由于这些活动同潜在的工作量有关,从全页加锁机制改变为仅对数据加锁或从仅对数据加锁改变为全页加锁机制都可能是耗费时间的活动为了标注这一点,有以下一些选择:
如果可能的话,应该配置使用并行方式。这至少对执行非群聚性索引的哈斯(杂凑,即hashed)创建方法是必须的,但是如果可能的话,采用分区表和分区扫描将使系统得到更大的改进

在选择进入和创建群聚性的索引之后,该任务将被设置检查点(checkpointed )所以,如果有充分的硬件资源,通过允许在任何一个时间点上,检查点任务可以具有多于10个(系统缺省值)的异步I/O请求,利用dbcc进行调谐将能够带来有益的效果(‘maxwritedes’,number)

进一步作为降低使用检查点成本的一种方法,在相关的高速缓冲池(cache pool)大数据量的I/O操作中,采用对高淘汰程度进行标记的方法,并允许清洁程序(好象家庭主妇一样)保持特别活跃的状态,将为那些检查点需要从高速缓冲池中刷新较“脏”的页面的而增加的I/O操作次数,并因此花费了在检查点上的时间,都能够大大减少作出贡献

如果预先进行了配置,则可以对并行的选择进入可以使用预先分配的盘区。所以,通过将sp_configure number of pre-allocated extents设置为16也将对系统性能有明显的积极的效果

备注:在仅对数据加锁类型之间进行改变不需要对数据进行备份, 而且执行起来只需很短的一段时间

 

SYBASE  ;表锁,页锁,行锁。

(2008-09-30 12:44:15)

 ALTER TABLE XSJL LOCK DATAPAGES
 ALTER TABLE XSJL LOCK DATAROWS
 ALTER TABLE XSJL LOCK ALLPAGES
1全页锁(allpages lock) 对查询的表及索引页加锁,也就是table lock
2页锁  (data lock)     对所查询的结果所在页加锁,对索引不加锁
3、行锁  (row lock)      对某行数据加锁
好像一个lock占用的内存为120byte!
锁只是一种保护机制,并不影响数据存储!
对于并行性较高的应用要充分考虑使用行级锁,这样对于提高并发性能至关重要!当然,事务都存在利弊两方面,使用行级锁,也会带来一些相应的弊端,比如使用的锁越多,占用的内存也越多,在使用行级锁的表上频繁的进行数据删除插入操作久而久之会造成数据库碎片的大量生成,数据库性能会下降.这就需要定时进行reorg操作,但该操作比较耗时,且影响业务!
1. reorg reclaim_space 回收因删除和行缩短更新操作产生的页上的未用空间。
reorg reclaim_space tablename回收表上的未用空间
  reorg rebuild 撤消行转移及回收空间,重写所有行以便与表的聚簇索引一致,向数据页写入行以便与通过sp_chgattribute对空间管理设置所做的改变保持一致,删除并重建表的所有索引
2.reorg rebuild tablename 回收表空间,重建所有索引
 注:reorg同dbcc一样 需要开销一定的磁盘等资源,请勿在服务器繁忙时执行

数据库技术

虽没有真正明白,记录一遍以后查看。

在工作过程中遇到一个很奇怪的问题。

两个表左连接查询,左表9w数据量,右表3w数据量,连接条件是右表的两个主键。

select count(*) from

AAA left join BBB

on AAA.f_prov = BBB.f_prov and AAA.f_sqbrno = BBB.f_brno

where …..

GROUP BY AAA.f_prov,f_aucnno,f_sqbrno,f_name

其他类似的查询基本都是2s左右,但是这两个表查询实际会用24s的时间来进行。

然后用执行计划进行查看,发现其他表连接用的是Nested Loop Join,而这个用的是Merge Join。然后我发现第二个表进行了排序并且没有命中索引和主键。

解决方法是删除之前BBB表的两个索引,重新建立BBB表的唯一索引,把f_prov和f_brno加入索引中,虽然还是Merge Join,但是基本上都在2s一下。

有机会要看一下怎么设定数据库使用哪种JOIN算法。

 

通往性能优化的天堂-地狱 JOIN方法说明

http://www.cnblogs.com/RicCC/archive/2007/06/26/SQL-Server-Performance-Tuning-Nested-Loop-Merge-Hash-Join.html

SQL: Join 2 ——Nested Loop Join

http://blog.sina.com.cn/s/blog_6d5a77ce0100qggr.html

 

http://wenku.baidu.com/link?url=YqFcov9mcIpW6FsRqXN89-tqRl3vWJqt3w-PNor42FrXpXS2fY_VmFn3yGQWIswRuyqf8Qw8HBVZEWJQU1PFDnyOQQOqNIkOV5eSzlcR0Qy

HASH JOIN ,MERGE JOIN ,NESTED LOOP 比较

http://trophy.iteye.com/blog/1416410

数据库JOIN算法

http://blog.csdn.net/huangpeng8612/article/details/3645174

数据库技术

最近在做这个问题的时候,发现网上很多资料不是很全,而且有些是有错的,现在我把问题解决了,特把相应的方法贴出来。

在执行插入语句前,需要做下面几步操作:
1、从开始菜单进入到 配置工具 > sql server 2005 外围应用配置器 > 服务和连接的外围配置器 > Datebase Engine > 服务,然后重启sqlserver服务。(在数据库非正常关闭之后,可能会出现,“没法初始化数据源的错误”,此时重启一下sqlserver服务就可以了)

2、然后运行 regedit 进入注册表编辑器 HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL.1\Providers\Microsoft.Jet.OLEDB.4.0 新建DWORD (32位值)名为DisallowAdhocAccess 值为0.

3、注意:
3.1、把需要导入的Execl文件关闭;
3.2、配置工具 > SQL Server 外围配置管理器 > 功能的外围应用配置器 > Database Engine > 即席远程查询 > 启用OPENROWSET 和            OPENDATASOURCE 支持 (勾选上)

3、在执行插入语句前,必须先打开这个组件服务:
exec sp_configure ‘show advanced options’,1
  reconfigure
  exec sp_configure ‘Ad Hoc Distributed Queries’,1
  reconfigure

注意:在每次用完之后,记得把它关闭。
exec sp_configure ‘Ad Hoc Distributed Queries’,0
  reconfigure
  exec sp_configure ‘show advanced options’,0
  reconfigure

4、插入语句:

/*新建表y,同时插入数据*/
select * into y from OpenDataSource(‘Microsoft.Jet.OLEDB.4.0’,’Data Source=”G:\1.xls”;Extended properties= “Excel 8.0;HDR=YES;IMEX=1″‘)…[Sheet1$]
/*新建表y,同时插入数据*/

/*插入到已有的表x中*/
insert into x select * from OPENROWSET(‘MICROSOFT.JET.OLEDB.4.0′,’Excel 8.0;HDR=YES;DATABASE=G:\1.xls’,Sheet1$);
/*插入到已有的表x中*/
参数HDR的值
HDR=Yes,这代表第一行是标题,不做为数据使用 ,如果用HDR=NO,则表示第一行不是标题,做为数据来使用。系统默认的是YES

参数Excel 8.0
对于Excel 97以上版本都用Excel 8.0
IMEX ( IMport EXport mode )设置
IMEX 有三种模式,各自引起的读写行为也不同,容後再述:
0 is Export mode
1 is Import mode
2 is Linked mode (full update capabilities)
我这里特别要说明的就是 IMEX 参数了,因为不同的模式代表著不同的读写行为:
当 IMEX=0 时为“汇出模式”,这个模式开启的 Excel 档案只能用来做“写入”用途。
当 IMEX=1 时为“汇入模式”,这个模式开启的 Excel 档案只能用来做“读取”用途。
当 IMEX=2 时为“连結模式”,这个模式开启的 Excel 档案可同时支援“读取”与“写入”用途。
意义如下:
0 —输出模式;
1—输入模式;
2—-链接模式(完全更新能力)

原文地址:http://www.cnblogs.com/guolebin7/archive/2010/08/29/1811902.html

版权归原作者所有,转载请注明出处
Liudroid博客转载
http://www.liuzhaocn.com 

数据库技术