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

Category Archives: SQLServer

SQL server的一道入门面试题背后的思考

  最近看到一个SQL Server的小例子,发现完全可以作为SQL server的一道入门面试题。题目如下: 例:有一合同表Contract Id Name Total buget 1 合同名称 100  102,22 2 合同名称2 300 ,102,22, 3 合同名称3 200  103,23, 要求:用SQL语句更新表的buget字段,如果前后没有”,”要加上”,”(即一个英文逗号)。(10分) 创建表数据: View Code use Testdb2 go IF NOT OBJECT_ID(‘[Contract]‘) ISNULL DROPTABLE [Contract] GO Createtable [Contract] (ID intprimarykey identity(1,1) ,[Name] nvarchar(50) null ,Total floatnull ,buget Nvarchar(500) null ) go insertinto [Contract] select‘合同名称’,  100,’102,22′ unionall select‘合同名称2′, 300,‘,102,22,’ unionall select‘合同名称3′, 300,’101,23,’  分析:这道题乍看很简单,由于肯定用到Replace,所以很自然的结合left,right,从而得到以下语句 方法一: update [Contract] set buget=‘,’+buget whereleft(buget,1)=‘,’ update [Contract] set buget=buget+‘,’whereright(buget,1)=‘,’ 如果能写成一个 SQL语句,可以加1分。 update [Contract] set buget=(casewhen (left(buget,1)!=‘,’andright   (buget,1)!=‘,’) then‘,’+buget+‘,’ whenleft(buget,1)!=‘,’then‘,’+buget whenright(buget,1)!=‘,’then  buget+‘,’ else buget end) 如果能从字符串的开关和结尾这个思路出发,结合Reverse,可以提到如下方法: 方法二: update [Contract] set buget=‘,’+buget where charindex(‘,’,buget)<>1 update [Contract] set buget=buget+‘,’where charindex(‘,’,reverse(buget))<>1 该方法,主要涉及charindex函数和reverse函数。 说实话,我当时就这两种思路,这也是SQL中常见的基本用法。但出人意料的第三种方法出现了。 方法三: UPDATE [contract] SET Buget = ‘,’+Buget+‘,’ UPDATE [contract] SET Buget = REPLACE(Buget,‘,,’,‘,’) 解析:该方法最主要的亮点不在于语法的精妙,而在于其思路的异于常规。先给两边补上逗号,再替换双逗号为单逗号。这在实际编程中确实难能可贵。换句话说,如果没有事先思考过的话,这反映了解题者反应敏捷,思路开放。因此,至少可以再加3分。 当然,此语句其实还是有bug,比如如果原bug字段中间有两个逗号,那么在Replace时就会更新掉不应该更新的内容。不过,稍加修正,限定replace的范围即可, 受此思路启发,可以引申得到以下类似方法: 方法四: UPDATE [contract] SET Buget = substring(BuGet,2,len(BuGet)-1) wherecharindex(‘,’,buget)=1 UPDATE [contract] SET Buget = substring(BuGet,1,len(BuGet)-1) wherecharindex(‘,’,reverse(buget))=1 UPDATE [contract] SET BuGet = ‘,’+BuGet+‘,’ 该方法是先去掉两边的逗号,再给每条记录加上逗号,比起方法三来,稍显繁琐,这也反衬了方法三的巧妙。 当然,也可以结合前面的思路稍作修正,这里就不再赘述,请读者自己思考。 感悟:释迦牟尼说过“人生需要经过六项修炼:布施、持戒、忍辱、精进、禅定、智慧。”,SQL编程,或C#、Java,甚至Javascrip的某个领域也是如此。技术是死的,思路是鲜活的,有时候,思路能轻易地突破技术很难实现的死角。到了一定程度时,会发现潜意识里已经被惯性思维塞满,而无法接受新鲜思维方式或思路,如果一段时间内持续如此,那么,我们应该警醒,把自己的头脑放空,把自己置于一个初学者的地位,重新开始“精进”的修炼! 原文链接:http://www.cnblogs.com/downmoon/archive/2011/03/02/1968615.html  

龙生   19 Feb 2013
View Details

sql server 2008 不允许保存更改,您所做的更改要求删除并重新创建以下表 的解决办法

 启动SQL Server 2008 Management Studio 工具菜单—-选项—-Designers(设计器)—-阻止保存要求重新创建表的更改  取消勾选即可。 转自:http://www.cnblogs.com/EasyLive2006/archive/2009/01/13/1375182.html

龙生   20 Aug 2012
View Details

SQLServer CPU占用100%

  解决CPU100%的情况,首先需排除病毒的情况1、收缩数据库( 日志文件)2、重建索引3、数据库硬盘所在区域  db服务器性价比比较好的方式是搭建raid5 追求性能的话是raid0 使用perfmon观察disk queue,看是否一直高于1,如果长时间高于1说明磁盘性能有问题,意味着磁盘操作需要排队完成。考虑升级存储设备加入“Page Life Expectancy”如果这个值始终小于300秒,意味着你需要更大的内存(在Sql Server: Buffer Manager里)加入“Buffer Cache hit ratio” 如果这个值小于90%,意味着你需要更大的内存.(在Sql Server: Buffer Manager里) 4、数据库锁检查use master go declare @spid int,@bl int DECLARE s_cur CURSOR FOR select  0 ,blocked from (select * from sysprocesses where  blocked>0 ) a where not exists(select * from (select * from sysprocesses where  blocked>0 ) b where a.blocked=spid) union select spid,blocked from sysprocesses where  blocked>0 OPEN s_cur FETCH NEXT FROM s_cur INTO @spid,@bl WHILE @@FETCH_STATUS = 0 begin if @spid =0             select ' 引起数据库死锁的是 : '+ CAST(@bl AS […]

龙生   25 Apr 2012
View Details

解决sqlserver cpu占用100%的问题

重建索引的方法:ALTER INDEX ALL ON daji_zhaozu REORGANIZE sp_who active  --看看哪个引起的阻塞, blksp_lock  --看看锁住了那个资源id, objid , select object_name(objid) 得到dbcc inputbuffer(@blk) — 看看是那个语句 —————————————————————————- 优化sqlserver的配置.sql %%******************************************************************************************************/go exec sp_configure "awe enabled","1"--内存可以支持64gexec sp_configure "lightweight pooling","0"--不使用nt纤程exec sp_configure "priority boost","1"--增加sqlserver优先级exec sp_configure "network packet size (b)","8192"--增加sqlserver网络包的大小 reconfigure with override go --优化数据库设置declare @currentdatabase sysnameselect @currentdatabase = db_name((select dbid from master.dbo.sysprocesses where spid = @@spid))exec sp_dboption @currentdatabase, 'select into/bulkcopy', 'true' --对大容量数据操作不记录日志exec sp_dboption @currentdatabase, 'trunc. log on chkpt.', 'true' --自动截断日志exec sp_dboption @currentdatabase, 'auto create statistics', 'true'--自动创建统计exec sp_dboption @currentdatabase, 'auto update statistics', 'true'--自动更新统   查看SQL版本号: 看SP补丁打全了没。   1、收缩数据库( 日志文件)2、重建索引3、数据库硬盘所在区域  db服务器性价比比较好的方式是搭建raid5 追求性能的话是raid0 […]

龙生   25 Apr 2012
View Details

sqlserver占用CPU达到100%原因

CPU占用率高的原因 CPU占用率高是对物理硬盘的查询次数多;内存使用率高是物理磁盘—虚拟内存—内存三种之间数据交换次数多。 防杀毒软件造成故障或病毒、木马造成,特别是蠕虫病毒在系统内部或网络内部迅速复制,造成CPU占用资源率居高不下; 驱动没有经过认证或某些软件与系统不兼容,造成CPU资源占用100%; 服务器硬件问题:磁盘、内存/虚拟内存等等; 网络问题:网络带宽被大量占用,造成可用带宽较少,从而影响速度; 数据库设计的问题:触发器造成死锁、作业多且频繁、中间表的大量使用、游标的大量使用、索引的设计不合理、事务操作频繁; SQL语句设计不合理,造成查询效率低下、影响服务器性能的发挥; 二 CPU占用率高解决方法 针对上述原因及可能,有以下处理: 杀毒软件升级,对服务器系统和所在的局域网进行全面、严格的杀毒; 对服务器上已经安装的软件进行考证、整理,不装没有认证的驱动、尽量装兼容性强的必需软件、去掉不必需的软件;对服务器系统、端口进行监控,定时清理系统垃圾文件、关闭不使用和高危险端口; 定期周期性检查服务器硬件问题、整理系统磁盘,使服务器性能得到最大程度发挥;制定《电脑使用规范》,规范中明确使用范围和禁止范围,并依据规范定期查询各个部门的电脑使用情况;对网络结构、交换机定期检查、维护和调整;升级硬件; 使用sql server自带的性能分析追踪工具sql profiler分析数据库设计所产生问题的来源,进行有针对性处理; 使用sql server自带的查询性能分析工具sql query analyzer对可能影响性能且使用频繁的查询语句进行优化; 或升级sql server;重装sql server或服务器操作系统;使用cpu降温软件等辅助软件。         如果这些还解决不了问题的话,那就比较麻烦,需要专业人士对网站进行整体优化,更改错误不合理的程序,优化后cpu占用能降至百分之十左右   from url:http://www.e-digitalwave.com/faqview.asp?id=140

龙生   25 Apr 2012
View Details

win2003 sqlserver cpu 100%

郁闷,过年放假回来第二天,公司服务器scsi硬盘放屁啦,幸好数据库和图片昨天都备过份,但恢复的东东也让我头疼啊iis,sqlserver,生成100w的静态页,静态页我没备,55555555555   现上北京买个块硬盘来,原来系统是windows2000,手到也没有2000啦,从网上当了个win2003就装上啦,本以为会好用点,结果出了 很多小问题,我改,最头痛的问题让我遇上啦 cpu占用率100%,网站根本就打不开啦,恢复的前几天一直正常,过了10来天突然出现这个问题,把IIS站建了几个缓冲池,设置过期时间,不管用,重 建索引不用,sqlserver分给他1g多内存一开机就吃没啦,重起N便系统不管用,在网上搜了半天,也没什么实质性的解决方法,最后终于让我搜着一个 解决方法目前还管用   @echo off echo 正在清除服务器垃圾文件,请稍等…… del /f /s /q %systemdrive%/*.tmp del /f /s /q %systemdrive%/*._mp del /f /s /q %systemdrive%/*.log del /f /s /q %systemdrive%/*.gid del /f /s /q %systemdrive%/*.chk del /f /s /q %systemdrive%/*.old del /f /s /q %systemdrive%/recycled/*.* del /f /s /q %windir%/*.bak del /f /s /q %windir%/prefetch/*.* rd /s /q %windir%/temp & md %windir%/temp del /f /q %userprofile%/cookies/*.* del /f /q %userprofile%/recent/*.* del /f /s /q "%userprofile%/Local Settings/Temporary Internet Files/*.*" del /f /s /q "%userprofile%/Local Settings/Temp/*.*" del /f […]

龙生   25 Apr 2012
View Details

不同的 SQL JOIN

JOIN: 如果表中有至少一个匹配,则返回行 LEFT JOIN: 即使右表中没有匹配,也从左表返回所有的行 RIGHT JOIN: 即使左表中没有匹配,也从右表返回所有的行 FULL JOIN: 只要其中一个表中存在匹配,就返回行  

龙生   15 Mar 2012
View Details

Sqlserver – 去重

 海量数据(百万以上),其中有些全部字段都相同,有些部分字段相同,怎样高效去除重复?      如果要删除手机(mobilePhone),电话(officePhone),邮件(email)同时都相同的数据,以前一直使用这条语句进行去重:   delete from 表 where id not in ( select max(id) from 表 group by mobilePhone,officePhone,email )   or   delete from 表 where id not in ( select min(id) from 表 group by mobilePhone,officePhone,email )   复制代码          其中下面这条会稍快些。上面这条数据对于100万以内的数据效率还可以,重复数1/5的情况下几分钟到几十分钟不等,但是如果数据量达到300万以上,效率骤降,如果重复数据再多点的话,常常会几十小时跑不完,有时候会锁表跑一夜都跑不完。无奈只得重新寻找新的可行方法,今天终于有所收获:   //查询出唯一数据的ID,并把他们导入临时表tmp中 select min(id) as mid into tmp from 表 group by mobilePhone,officePhone,email   //查询出去重后的数据并插入finally表中 insert into finally select (除ID以外的字段) from customers_1 where id in (select mid from tmp) 复制代码         效率对比:用delete方法对500万数据去重(1/2重复)约4小时             […]

龙生   14 Mar 2012
View Details

SQL SERVER的数据类型

 1.SQL SERVER的数据类型  数据类弄是数据的一种属性,表示数据所表示信息的类型。任何一种计算机语言都定义了自己的数据类型。当然,不同的程序语言都具有不同的特点,所定义的数据类型的各类和名称都或多或少有些不同。SQLServer 提供了 25 种数据类型:  ·Binary [(n)]  ·Varbinary [(n)]  ·Char [(n)]  ·Varchar[(n)]  ·Nchar[(n)]  ·Nvarchar[(n)]  ·Datetime  ·Smalldatetime  ·Decimal[(p[,s])]  ·Numeric[(p[,s])]  ·Float[(n)]  ·Real  ·Int  ·Smallint  ·Tinyint  ·Money  ·Smallmoney  ·Bit  ·Cursor  ·Sysname  ·Timestamp  ·Uniqueidentifier  ·Text  ·Image  ·Ntext (1)二进制数据类型   二进制数据包括 Binary、Varbinary 和 Image  Binary 数据类型既可以是固定长度的(Binary),也可以是变长度的。  Binary[(n)] 是 n 位固定的二进制数据。其中,n 的取值范围是从 1 到 8000。其存储窨的大小是 n + 4 个字节。  Varbinary[(n)] 是 n 位变长度的二进制数据。其中,n 的取值范围是从 1 到 8000。其存储窨的大小是 n + 4个字节,不是n 个字节。  在 Image 数据类型中存储的数据是以位字符串存储的,不是由 SQL Server 解释的,必须由应用程序来解释。例如,应用程序可以使用BMP、TIEF、GIF 和 JPEG 格式把数据存储在 Image 数据类型中。 (2)字符数据类型   字符数据的类型包括 Char,Varchar 和 Text  字符数据是由任何字母、符号和数字任意组合而成的数据。  Varchar 是变长字符数据,其长度不超过 8KB。Char 是定长字符数据,其长度最多为 8KB。超过 8KB 的ASCII 数据可以使用Text数据类型存储。例如,因为 Html 文档全部都是 ASCII 字符,并且在一般情况下长度超过 8KB,所以这些文档可以 Text 数据类型存储在SQL Server 中。 (3)Unicode 数据类型   Unicode 数据类型包括 Nchar,Nvarchar 和Ntext  在 Microsoft SQL Server 中,传统的非 Unicode 数据类型允许使用由特定字符集定义的字符。在 SQL Server安装过程中,允许选择一种字符集。使用 Unicode 数据类型,列中可以存储任何由Unicode 标准定义的字符。在 Unicode 标准中,包括了以各种字符集定义的全部字符。使用Unicode数据类型,所战胜的窨是使用非 Unicode 数据类型所占用的窨大小的两倍。   在 SQL Server 中,Unicode […]

龙生   07 Mar 2012
View Details
1 6 7 8 12