一切福田,不離方寸,從心而覓,感無不通。

Category Archives: SQLServer

SQL Server 跨库同步数据

最近有个需求是要跨库进行数据同步,两个数据库分布在两台物理计算机上,自动定期同步可以通过SQL Server代理作业来实现,但是前提是需要编写一个存储过程来实现同步逻辑处理。这里的存储过程用的不是opendatasource,而是用的链接服务器来实现的。存储过程创建在IP1:192.168.0.3服务器上,需要将视图v_custom的客户信息同步到IP2:192.168.0.10服务器上的t_custom表中。逻辑是如果不存在则插入,存在则更新字段。

如果没有正确配置,经常会出现 消息 7391,级别 16,状态 2,过程 xxxxx,第 XX 行 。无法执行该操作,因为链接服务器 "xxxxx" 的 OLE DB 访问接口 "SQLNCLI" 无法启动分布式事务。   可以参照如下的配置: 具体可以参看:http://www.cnblogs.com/chnking/archive/2007/04/04/699891.html   from:https://www.cnblogs.com/isaboy/p/sql_server_job.html

龙生   07 Sep 2018
View Details

PowerDesigner中NAME和COMMENT的互相转换,需要执行语句

使用说明: 在【Tools】-【Execute Commands】-【Edit/Run Script】 下。输入下面你要选择的语句即可,也可以保存起来,以便下次使用,后缀为.vbs。 需要注意的问题是:运行语句时必须在Module模式下,如果是导出报表时执行会出现错误提示。 1.Name转到Comment注释字段。一般情况下只填写NAME,COMMENT可以运行语句自动生成。 将该语句保存为name2comment.vbs

2.将Comment内容保存到NAME中,comment2name.vbs 实习互换。语句为:

from:https://www.cnblogs.com/netsql/archive/2010/05/24/1742734.html

龙生   27 Jun 2018
View Details

PowerDesigner从Sqlserver中反转为带注释的字典及快捷键操作

PowerDesigner的操作经常忘记,所以把常用的功能记录下来备忘。 1、修改反转过来的字段 PowerDesigner从数据库反转的时候,默认不带注释,需要先进行修改。 输入如下脚本: 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 {OWNER, TABLE, S, COLUMN, DTTPCODE, LENGTH, SIZE, PREC, COMPUTE, NOTNULL, IDENTITY, DOMAIN, DEFAULT, COMMENT, ExtIdentityIncrement, ExtIdentitySeed} select u.name, o.name, c.colid, c.name, case when (s.usertype < 100) then s.name else x.name end, c.prec, c.length, c.scale, z.text , case (c.status & 8) when 8 then 'NULL' else 'NOTNULL' end, case (c.status & 128) when 128 then 'identity' else " end, case when (s.usertype < 100) then " else s.name end, v.text, CONVERT(varchar, ISNULL(p.[value], ")) AS text, case (c.status & 128) when 128 then ident_incr(u.name + '.' + o.name) else null end, case (c.status & 128) when 128 then ident_seed(u.name + '.' + o.name) else null end from dbo.sysusers u join dbo.sysobjects o on (o.uid = u.uid and o.type in ('U', 'S', 'V')) […]

龙生   27 Jun 2018
View Details

sqlserver的四种分页方式

第一种:ROW_NUMBER() OVER()方式 select * from ( select *, ROW_NUMBER() OVER(Order by ArtistId ) AS RowId from ArtistModels ) as b where RowId between 10 and 20 —where RowId BETWEEN 当前页数-1*条数 and 页数*条数— 执行结果是: 第二种方式:offset fetch next方式(SQL2012以上的版本才支持:推荐使用 ) select * from ArtistModels  order by ArtistId offset 4 rows fetch next 5 rows only --order by ArtistId offset 页数*条数 rows fetch next 条数 rows only —- 执行结果是: 第三种方式:--top not in方式 (适应于数据库2012以下的版本) select top 3 * from ArtistModels where ArtistId not in (select top 15 ArtistId from ArtistModels) ——where Id not in (select top 条数*页数  ArtistId  from ArtistModels) […]

龙生   04 Jun 2018
View Details

SQL Server 数值四舍五入,小数点后保留2位

1.round() 函数是四舍五入用,第一个参数是我们要被操作的数据,第二个参数是设置我们四舍五入之后小数点后显示几位。 2.numeric 函数的2个参数,第一个表示数据长度,第二个参数表示小数点后位数。 例如: select   cast(round(12.5,2)   as   numeric(5,2))  结果:12.50 select   cast(round(12.555,2)   as   numeric(5,2))  结果:12.56 select   cast(round(122.5255,2)   as   numeric(5,2)) 结果:122.53 select   cast(round(1222.5255,2)   as   numeric(5,2)) 结果:报错了! 原因是:1222.5255,整数位是4,小数位是2,加起来4+2=6,超出了numeric设置的5位,所以为了保险,可以增减numeric的参数,例如numeric(20,2)。 此文系转载后作一调整,转载自:http://blog.csdn.net/caoyuanlang_11/article/details/5410833

龙生   26 Apr 2018
View Details

解决SQL Server 2008无法连接127.0.0.1的问题

电脑操作系统是Win10中文版,新装的英文版SQL Server 2008,纯默认安装,没有做任何改动。 装完SQL Server 2008之后,发现只能用默认的机器名来登录: 如果用127.0.0.1登录就会报出如下的错误: 在网上查询一番之后,发现让开启SQL Server Configuration Manager中的Named Pipes就可以解决问题,然而试了并没有什么卵用。。。 于是开始自己动手尝试解决方案,经过一番尝试,功夫不负有心人啊!终于算是找到了,下面把步骤贴出来: 第一步,开启TCP/IP。因为装完SQL之后这项协议默认是不开通的: 第二步,设置TCP/IP中属性的"IP ALL"的端口为1433。众所周知,SQL Server的默认端口为1433,只是不知道为何,默认安装却没有这一项。   然后,重启SQL服务就可以用127.0.0.1登录上了:   from:https://www.cnblogs.com/goldenbiu/p/4726463.html

龙生   20 Apr 2018
View Details

SQL语句优化(转载)

一、操作符优化 1、IN 操作符 用IN写出来的SQL的优点是比较容易写及清晰易懂,这比较适合现代软件开发的风格。但是用IN的SQL性能总是比较低的,从Oracle执行的步骤来分析用IN的SQL与不用IN的SQL有以下区别: ORACLE试图将其转换成多个表的连接,如果转换不成功则先执行IN里面的子查询,再查询外层的表记录,如果转换成功则直接采用多个表的连接方式查询。由此可见用IN的SQL至少多了一个转换的过程。一般的SQL都可以转换成功,但对于含有分组统计等方面的SQL就不能转换了。 推荐方案:在业务密集的SQL当中尽量不采用IN操作符,用EXISTS 方案代替。 2、NOT IN操作符 此操作是强列不推荐使用的,因为它不能应用表的索引。 推荐方案:用NOT EXISTS 方案代替 3、IS NULL 或IS NOT NULL操作(判断字段是否为空) 判断字段是否为空一般是不会应用索引的,因为索引是不索引空值的。 推荐方案:用其它相同功能的操作运算代替,如:a is not null 改为 a>0 或a>’’等。不允许字段为空,而用一个缺省值代替空值,如申请中状态字段不允许为空,缺省为申请。 4、> 及 < 操作符(大于或小于操作符) 大于或小于操作符一般情况下是不用调整的,因为它有索引就会采用索引查找,但有的情况下可以对它进行优化,如一个表有100万记录,一个数值型字段A,30万记录的A=0,30万记录的A=1,39万记录的A=2,1万记录的A=3。那么执行A>2与A>=3的效果就有很大的区别了,因为A>2时ORACLE会先找出为2的记录索引再进行比较,而A>=3时ORACLE则直接找到=3的记录索引。 5、LIKE操作符 LIKE操作符可以应用通配符查询,里面的通配符组合可能达到几乎是任意的查询,但是如果用得不好则会产生性能上的问题,如LIKE ‘%5400%’ 这种查询不会引用索引,而LIKE ‘X5400%’则会引用范围索引。 一个实际例子:用YW_YHJBQK表中营业编号后面的户标识号可来查询营业编号 YY_BH LIKE ‘%5400%’ 这个条件会产生全表扫描,如果改成YY_BH LIKE ’X5400%’ OR YY_BH LIKE ’B5400%’ 则会利用YY_BH的索引进行两个范围的查询,性能肯定大大提高。 6、UNION操作符 UNION在进行表链接后会筛选掉重复的记录,所以在表链接后会对所产生的结果集进行排序运算,删除重复的记录再返回结果。实际大部分应用中是不会产生重复的记录,最常见的是过程表与历史表UNION。如: select * from gc_dfys union select * from ls_jg_dfys 这个SQL在运行时先取出两个表的结果,再用排序空间进行排序删除重复的记录,最后返回结果集,如果表数据量大的话可能会导致用磁盘进行排序。 推荐方案:采用UNION ALL操作符替代UNION,因为UNION ALL操作只是简单的将两个结果合并后就返回。 select * from gc_dfys union all select * from ls_jg_dfys 二、SQL书写的影响 1、同一功能同一性能不同写法SQL的影响。 如一个SQL在A程序员写的为  Select * from zl_yhjbqk B程序员写的为 Select * from dlyx.zl_yhjbqk(带表所有者的前缀) C程序员写的为 Select * from DLYX.ZLYHJBQK(大写表名) D程序员写的为 Select *  […]

龙生   16 Sep 2017
View Details

SQLSERVER如何查看索引缺失

当大家发现数据库查询性能很慢的时候,大家都会想到加索引来优化数据库查询性能, 但是面对一个复杂的SQL语句,找到一个优化的索引组合对人脑来讲,真的不是一件很简单的事。 好在SQLSERVER提供了两种“自动”功能,给你建议,该怎么调整索引 第一种是使用DMV 第二种是使用DTA (database engine tuning advisor) 数据库引擎优化顾问 这篇文章主要讲第一种 从SQL2005以后,在SQLSERVER对任何一句语句做编译的时候,都会去评估一下, 这句话是不是缺少什么索引的支持,如果他认为是,他还会预估,如果有这麽一个索引 他的性能能提高多少   SQLSERVER有几个动态管理视图 sys.dm_db_missing_index_details sys.dm_db_missing_index_groups sys.dm_db_missing_index_group_stats sys.dm_db_missing_index_columns(index_handle)     sys.dm_db_missing_index_details 这个DMV记录了当前数据库下所有的missing index的信息,他针对的是SQLSERVER从启动以来所有运行的语句, 而不是针对某一个查询。DBA可以看看,哪些表格SQLSERVER对他是最有“意见”的 以下是这个DMV的各个字段的解释: 1、index_handle:标识特定的缺失索引。该标识符在服务器中是唯一的。index_handle 是此表的密钥 2、database_id :标识带有缺失索引的表所驻留的数据库 3、object_id :标识索引缺失的表 4、equality_columns:构成相等谓词的列的逗号分隔列表 即哪个字段缺失了索引会在这里列出来(简单来讲就是where 后面的筛选字段), 谓词的形式如下:table.column =constant_value 5、inequality_columns :构成不等谓词的列的逗号分隔列表,例如以下形式的谓词:table.column > constant_value “=”之外的任何比较运算符都表示不相等。 6、included_columns:用于查询的涵盖列的逗号分隔列表(简单来讲就是 select 后面的字段)。 7、statement:索引缺失的表的名称 比如下面这个查询结果 那么应该创建这样的索引

在ProductID上创建索引,SalesOrderID作为包含性列的索引   注意事项: 由 sys.dm_db_missing_index_details 返回的信息会在查询优化器优化查询时更新,因而不是持久化的。 缺失索引信息只保留到重新启动 SQL Server 前。如果数据库管理员要在服务器回收后保留缺失索引信息, 则应定期制作缺失索引信息的备份副本     sys.dm_db_missing_index_columns(index_handle) 返回与缺少索引(不包括空间索引)的数据库表列有关的信息,sys.dm_db_missing_index_columns 是一个动态管理函数 字段解释 index_handle:唯一地标识缺失索引的整数。     sys.dm_db_missing_index_groups   返回有关特定缺失索引组中包含的缺失索引(不包括空间索引)的信息   sys.dm_db_missing_index_group_stats 返回缺失索引组的摘要信息,不包括空间索引 这个视图说白了就是预估有这麽一个索引,他的性能能提高多少 有一个字段比较重要: avg_user_impact: 实现此缺失索引组后,用户查询可能获得的平均百分比收益。该值表示如果实现此缺失索引组,则查询成本将按此百分比平均下降。 就是说,增加了这个缺失索引,性能可以提高的百分比   下面是MSDN给出的示例,缺失索引组句柄为 2

  示例代码:

[…]

龙生   03 Aug 2016
View Details
1 2 12