【网学网提醒】：网学会员为需要朋友们搜集整理了SQL技巧整理相关资料，希望对各位网友有所帮助!
1．TRUNCATETABLE和DELETETABLE的区别.............................................................2．2．表变量和临时表的作用及区别.................................................................................................2．5.如何查找一个字段的属性............................................................................................................4如何查找一个字段的属性6.排序规则说明..............................................................................................................................47对字符数据类型字段某列值进行累加.......................................................................................48.DBMS处理查询计划的过程......................................................................................................59.动态SQL基本语法......................................................................................................................5动态10、动态SQL介绍.........................................................................................................................6、11、修改数据库表字段表名，但不改变其内容...........................................................................6、修改数据库表字段表名，12、创建数据表...............................................................................................................................6、13、修改数据库表字段长度及类型...............................................................................................6、14、IN的使用................................................................................................................................6、15、数据类型优化...........................................................................................................................6、16、SQL的使用规范：..................................................................................................................6、的使用规范：17、索引的使用规范：...................................................................................................................7、索引的使用规范：18、TEMPDB的使用规范：.........................................................................................................7、的使用规范：19、用UNION-ALL替换UNION(如果有可能的话：..........................................................8、如果有可能的话)：
1．Truncatetable和deletetable的区别
1.TRUNCATETABLE在功能上与不带Where子句的Delete语句相同：二者均删除表中的全部行。但TRUNCATETABLE比Delete速度快，且使用的系统和事务日志资源少。2.Delete语句每次删除一行，并在事务日志中为所删除的每行记录一项。TRUNCATETABLE通过释放
存储表数据所用的数据页来删除数据，并且只在事务日志中记录页的释放。3.TRUNCATETABLE删除表中的所有行，但表结构及其列、约束、索引等保持不变。新行标识所用的计数值重置为该列的种子。如果想保留标识计数值，请改用Delete。4.对于由FOREIGNKEY约束引用的表，不能使用TRUNCATETABLE，而应使用不带Where子句的Delete语句。由于TRUNCATETABLE不记录在日志中，所以它不能激活触发器。5.TRUNCATETABLE不能用于参与了索引视图的表。
6、TRUNCATE是一个DDL语言，向其他所有的DDL语言一样，他将被隐式提交，不能对TRUNCATE使用ROLLBACK命令。7、TRUNCATE将重新设置高水平线和所有的索引。在对整个表和索引进行完全浏览时，经过TRUNCATE操作后的表比DELETE操作后的表要快得多。8、TRUNCATE不能触发任何DELETE触发器。9、不能授予任何人清空他人的表的权限。10、当表被清空后表和表的索引讲重新设置成初始大小，而delete则不能。11、不能清空父表。
2．表变量和临时表的作用及区别
在SQLServer的性能调优中，有一个不可比拟的问题：那就是如何在一段需要长时间的代码或被频繁调用的代码中处理临时数据集?表变量和临时表是两种选择。记得在给一家国内首屈一指的海运公司作SQLServer应用性能评估和调优的时候就看到过大量的临时数据集处理需求，而他们的开发人员就无法确定什么时候用临时表，什么时候用表变量，因此他们就简单的使用了临时表。实际上临时表和表变量都有特定的适用环境。先卖弄一些基础的知识：表变量变量都以@或@@为前缀，表变量是变量的一种，另外一种变量被称为标量(可以理解为标准变量，就是标准数据类型的变量，例如整型int或者日期型DateTime)。以@前缀的表变量是本地的，因此只有在当前用户会话中才可以访问，而@@前缀的表变量是全局的，通常都是系统变量，比如说@@error代表最近的一个T-SQL语句的报错号。当然因为表变量首先是个变量，因此它只能在一个Batch中生存，也就是我们所说的边界，超出了这个边界，表变量也就消亡了。表变量存放在内存中，正是因为这一点所有用户访问表变量的时候SQLServer是不需要生成日志。同时变量是不需要考虑其他会话访问的问题，因此也不需要锁机制，对于非常繁忙的系统来说，避免锁的使用可以减少一部分系统负载。表变量另外还有一个限制就是不能创建索引，当然也不存在统计数据的问题，因此在用户访问表变量的时候也就不存在执行计划选择的问题了(也就是以为着编译阶段后就没有优化阶段了)，这一特性有的时候是件好事，而有些
时候却会造成一些麻烦。临时表
临时对象都以#或##为前缀，临时表是临时对象的一种，还有例如临时存储过程、临时函数之类的临时对象，临时对象都存储在tempdb中。以#前缀的临时表为本地的，因此只有在当前用户会话中才可以访问，而##前缀的临时表是全局的，因此所有用户会话都可以访问。临时表以会话为边界，只要创建临时表的会话没有结束，临时表就会持续存在，当然用户在会话中可以通过DROPTABLE命令提前销毁临时表。我们前面说过临时表存储在tempdb中，因此临时表的访问是有可能造成物理IO的，当然在修改时也需要生成日志来确保一致性，同时锁机制也是不可缺少的。跟表变量另外一个显著去别就是临时表可以创建索引，也可以定义统计数据，因此SQLServer在处理访问临时表的语句时需要考虑执行计划优化的问题。表变量vs.临时表
表变量数据集的存储位置是否需要日志是否可以创建索引是否可以使用统计数据是否可以在多会话中访问是否需要锁机制内存(不考虑被换到页面文件这种情况)否否否否否
临时表磁盘(不考虑访问后被缓存到内存中)是是是是是
结论综上所述，大家会发现临时表和表变量在底层处理机制上是有很多差别的。简单地总结，我们对于较小的临时计算用数据集推荐使用表变量。如果数据集比较大，如果在代码中用于临时计算，同时这种临时使用永远都是简单的全数据集扫描而不需要考虑什么优化，比如说没有分组或分组很少的聚合(比如说COUNT、SUM、AVERAGE、MAX等)，也可以考虑使用表变量。使用表变量另外一个考虑因素是应用环境的内存压力，如果代码的运行实例很多，就要特别注意内存变量对内存的消耗。一般对于大的数据集我们推荐使用临时表，同时创建索引，或者通过SQLServer的统计数据(Statisitcs)自动创建和维护功能来提供访问SQL语句的优化。如果需要在多个用户会话间交换数据，当然临时表就是唯一的选择了。需要提及的是，由于临时表存放在tempdb中，因此要注意tempdb的调优。附:表变量和临时表的比较：
???
临时表是利用了硬盘(tempdb数据库)，表名变量是占用内存，因此小数据量当然是内存中的表变量更快。当大数据量时，就不能用表变量了，太耗内存了。大数据量时适合用临时表。表变量缺省放在内存，速度快，所以在触发器，存储过程里如果数据量不大，应该用表变量。临时表缺省使用硬盘，一般来说速度比较慢，那是不是就不用临时表呢？也不是，在数据量比较大的时候，如果使用表变量，会把内存耗尽，然后使用TEMPDB的空间，
这样主要还是使用硬盘空间，但同时把内存基本耗尽，增加了内存调入调出的机会，反而降低速度。这种情况建议先给TEMPDB一次分配合适的空间，然后使用临时表。
????????????
临时表相对而言表变量主要是多了I/O时间，但少了对内存资源的占用。数据量较大的时候，由于对内存资源的消耗较少，使用临时表比表变量有更好的性能。建议：触发器、自定义函数用表变量；存储过程看情况，大部分用表变量；特殊的应用，大数据量的场合用临时表。表变量有明确的作用域，在定义表变量的函数、存储过程或批处理结束时，会自动清除表变量。在存储过程中使用表变量与使用临时表相比，减少了存储过程的重新编译量。涉及表变量的事务只在表变量更新期间存在。这样就减少了表变量对锁定和记录资源的需求。表变量需要事先知道表结构，普通临时表，只在当前会话中可用与表变量相同into一下就可以了,方便；全局临时表：可在多个会话中使用存在于temp中需显示的drop。（不知道表结构情况下临时表方便一些）全局临时表的功能是表变量没法达到的。表变量不必删除，也就不会有命名冲突，临时表特别是全局临时表用的时候必须解决命名冲突。应避免频繁创建和删除临时表，减少系统表资源的消耗。在新建临时表时，如果一次性插入数据量很大，那么可以使用selectinto代替createtable，避免log，提高速度；如果数据量不大，为了缓和系统表的资源，建议先createtable，然后insert。如果临时表的数据量较大，需要建立索引，那么应该将创建临时表和建立索引的过程放在单独一个子存储过程中，这样才能保证系统能够很好的使用到该临时表的索引。如果使用到了临时表，在存储过程的最后务必将所有的临时表显式删除，truncatetable，先然后droptable，这样可以避免系统表的较长时间锁定。
慎用大的临时表与其他大表的连接查询和修改，减低系统表负担，因为这种操作会在一条语句中多次使用tempdb的系统表。
5.如何查找一个字段的属性
execsp_mshelpcolumnstablename
6.排序规则说明
usegoalter其中_CI(CS)是否区分大小写，CI不区分，CS区分_AI(AS)是否区分重音，AI不区分，AS区分databaseAdventureWorkscollateChinese_PRC_CI_ASmaster
7对字符数据类型字段某列值进行累加
CREATEfunctionintvarchar(8000)function_name(
@chvName)returnsasbegindeclareset@r
varchar(4000)数据集where基准字段=@chvName
@r=''
select@r=@r+数据集字段fromreturn(stuff(@r,1,1,''))end
8.DBMS处理查询计划的过程DBMS处理查询计划的过程是这样的：1、查询语句
的词法、语法检查2、将语句提交给DBMS的查询优化器3、优化器做代数优化和存取路径的优化4、由预编译模块生成查询规划5、然后在合适的时间提交给系统处理执行6、最后将执行结果返回给用户其次，看一下SQLSERVER的数据存放的结构：一个页面的大小为8K(8060)字节，8个页面为一个盘区，按照B树存放。9.动态SQL基本语法
1．什么时候用＋号？为了组成一个字符串类型的语句，要用＋号作连接符。一般情况下，字符串与字符串变量连接时才用加号。如DECLARE@SQLNVARCHAR(1000),@tablenamenvarchar(100),@SORTFIELDNVARCHAR(100)SELECT@TABLENAME=N'MYTABLE',@SORTFIELD=N'USERCODE'SET@SQL=N'SELECT*from'+@tablename+N'orderby'+@FIELDSORT'这样的组成的SQL语句是：SELECT*fromMYTABLEorderbyUSERCODE
2.什么时候用引号：常数字符串都要用引号，如上面例子的N'SELECT*FROM'
3.什么时候用空格：一般连接关键字的引号内侧要加一空格，如：N'SELECT*from'+@tablenameFROM后面要加空格，否则连接后会成为：SELECT*FROMMYTABLEFROM和MYTABLE连起来了，从而出错。
4.其它要注意的：生成后字串中有引号的语句。比如要生成这样一个语句SELECT*FROMMYTABLEWHEREUSERCODE=''
这样写是不对的：SET@SQL=N'SELECT*from'+@tablename+'WHEREUSERCODE='''
这样才是对的：SET@SQL=N'SELECT*from'+@tablename+'WHEREUSERCODE=N'''''
可以看出：在字串中间的两个引号变成了4个。
总结一下：语句中间需要显示出的引号，引号个数要加倍。
10、动态SQL介绍1）什么是动态SQL在Oracle数据库开发PL/SQL块中我们使用的SQL分为：静态SQL语句和动态SQL语句。所谓静态SQL指在PL/SQL块中使用的SQL语句在编译时是明确的，执行的是确定对象。而动态SQL是指在PL/SQL块编译时SQL语句是不确定的，如根据用户输入的参数的不同而执行不同的操作。编译程序对动态语句部分不进行处理，只是在程序运行时动态地创建语句、对语句进行语法分析并执行该语句。
11、修改数据库表字段表名，但不改变其内容
sp_rename'表名.旧字段名','新'
--注意:
更改对象名的任一部分都可能会破坏脚本和存储过程。
往表里插入一列字段
Altertable表名add
字段名称
字段类型默认值
12、创建数据表
CREATETABLE[dbo].[Table0020]([rkey][numeric](8,0)IDENTITY(1,1)NOTNULL,[code][char](10)NULL,[gongyi_desc][char](10)NULL,CONSTRAINT[PK_Table0020]PRIMARYKEYCLUSTERED([rkey]ASC)WITH(PAD_INDEXALLOW_ROW_LOCKS)ON[PRIMARY]GO=OFF,STATISTICS_NORECOMPUTE=ON,ALLOW_PAGE_LOCKS=OFF,IGNORE_DUP_KEY=OFF,=ON)ON[PRIMARY]---创建主键
13、修改数据库表字段长度及类型
altertable<表名>altercolumn<字段名>
新类型名(长度)例：原表：table0016.table0020_rkeychar(10)altertabletable0016altercolumntable0020_rkeynumeric(8,0)–更改字段类型altertabletable0016altercolumntable0020_rkeychar(20)–更改字段长度
14、in的使用在IN后面值的列表中，将出现最频繁的值放在最前面，出现得最少的放在最后面，减少判断的次数15、数据类型优化分析selectemp_nameformemployeewheresalary>3000在此语句中若salary是Float类型的，则优化器对其进行优化为Convert(float,3000)，因为3000是个整数，我们应在编程时使用3000.0而不要等运行时让DBMS进行转化。同样字符和整型数据的转换。16、SQL的使用规范：
i.尽量避免大事务操作，慎用holdlock子句，提高系统并发能力。
ii.
尽量避免反复访问同一张或几张表，尤其是数据量较大的表，可以考虑先根据条件提取数据到临
时表中，然后再做连接。iii.尽量避免使用游标，因为游标的效率较差，如果游标操作的数据超过1万行，那么就应该改写；
如果使用了游标，就要尽量避免在游标循环中再进行表连接的操作。iv.注意where字句写法，必须考虑语句顺序，应该根据索引顺序、范围大小来确定条件子句的前后
顺序，尽可能的让字段顺序与索引顺序相一致，范围从大到小。v.确使用索引。不要在where子句中的“=”左边进行函数、算术运算或其他表达式运算，否则系统将可能无法正
17、索引的使用规范：
i.ii.强制指定索引iii.iv.避免对大表查询时进行tablescan，必要时考虑新建索引。在使用索引字段作为条件时，如果该索引是联合索引，那么必须使用到该索引中的第一个字段作索引的创建要与应用结合考虑，建议大的OLTP表不要超过6个索引。尽可能的使用索引字段作为查询条件，尤其是聚簇索引，必要时可以通过indexindex_name来
为条件时才能保证系统使用该索引，否则该索引将不会被使用。v.vi.要注意索引的维护，周期性重建索引，重新编译存储过程。尽量使用exists代替selectcount(1)来判断是否存在记录，count函数只有在统计表中所有行
数时使用，而且count(1)比count(*)更有效率。vii.viii.ix.x.xi.尽量使用“>=”，不要使用“>”。注意一些or子句和union子句之间的替换注意表之间连接的数据类型，避免不同类型数据之间的连接。注意存储过程中参数和数据类型的关系。
注意insert、update操作的数据量，防止与其他应用冲突。如果数据量超过200个数据页面（400k），
那么系统将会进行锁升级，页级锁会升级成表级锁。
18、tempdb的使用规范：i.尽量避免使用distinct、orderby、groupby、having、join、cumpute，因为这些语
句会加重tempdb的负担。ii.iii.insert。iv.如果临时表的数据量较大，需要建立索引，那么应该将创建临时表和建立索引的过避免频繁创建和删除临时表，减少系统表资源的消耗。在新建临时表时，如果一次性插入数据量很大，那么可以使用selectinto代替create
table，避免log，提高速度；如果数据量不大，为了缓和系统表的资源，建议先createtable，然后
程放在单独一个子存储过程中，这样才能保证系统能够很好的使用到该临时表的索引。
v.
如果使用到了临时表，在存储过程的最后务必将所有的临时表显式删除，先
truncatetable，然后droptable，这样可以避免系统表的较长时间锁定。vi.慎用大的临时表与其他大表的连接查询和修改，减低系统表负担，因为这种操作会在一
条语句中多次使用tempdb的系统表。19、用UNION-ALL替换UNION(如果有可能的话)：
当SQL语句需要UNION两个查询结果集合时,这两个结果集合会以UNION-ALL的方式被合并,然后在输出最终结果前进行排序.如果用UNIONALL替代UNION,这样排序就不是必要了.效率就会因此得到提高.需要注意的是，UNIONALL将重复输出两个结果集合中相同记录.因此各位还是要从业务需求分析使用UNIONALL的可行性.UNION将对结果集合排序,这个操作会使用到SORT_AREA_SIZE这块内存.对于这块内存的优化也是相当重要的.下面的SQL可以用来查询排序的消耗量低效：SELECTFROMACCT_NUM,BALANCE_AMTDEBIT_TRANSACTIONS
WHERETRAN_DATE=’31-DEC-95’UNIONSELECTACCT_NUM,BALANCE_AMTFROMDEBIT_TRANSACTIONSWHERETRAN_DATE=’31-DEC-95’高效:SELECTACCT_NUM,BALANCE_AMTFROMDEBIT_TRANSACTIONSWHERETRAN_DATE=’31-DEC-95’UNIONALLSELECTACCT_NUM,BALANCE_AMTFROMDEBIT_TRANSACTIONSWHERETRAN_DATE=’31-DEC-95’
20、检查是否有重复行产生
select*--into#table1from(select*,row_number()over(partitionbyd95rkeyorderbydo_date,rkey)id_1from[PPC_转仓单表])awhereid_1>1wheredo_date>='2010-01-01'--droptable#table1
21SQL基本语句汇总
1、表里插入一列ALTERTABLEtable_nameADDcolumn_namedatatype22COALESCE()函数应用：当你要在n个字段中选取某一个非空值可以用它
T-SQL的COALESCE()函数能够让你指定多个表达式，而且COALESCE()会返回组里第一个非空的值。要使用下面的形式，其中expression可以是任何数据类型，并能够等于大多数合法的表达式：COALESCE(expression[,…n])--（与CASE函数等价）CASE
WHEN(expression1ISNOTNULL)THENexpression1...WHEN(expressionNISNOTNULL)THENexpressionNELSENULL如果所有自变量(expression1-N)均为NULL，则COALESCE返回NULL值。
23排序规则的统一Chinese_PRC_CI_ASChinese_PRC_CI_BIN
/***********
查询排序规则，字段属性**************/selectB.Name表,A.Name列,TYPE_NAME(A.XTYPE)类型,a.length长度,a.collation排序规则fromSYSCOLUMNSAJOINSYSOBJECTSBONA.ID=B.IDANDB.TYPE='U'WHERETYPE_NAME(A.XTYPE)IN('VARCHAR','CHAR','NVARCHAR','NCHAR','TEXT','NTEXT')anda.collation='Chinese_PRC_CI_AS'
/**********更改字段属性及排序规则**********/altertableTabNamealtercolumnColnametypeNamecollateChinese_PRC_BIN
24查询日志跟踪表
SELECT
[__$start_lsn]
,[__$end_lsn],[__$seqval],[__$operation],[__$update_mask],[RKEY],[DATA9109_PTR],[DATA9107_PTR]FROM[LIVE].[cdc].[dbo_Data9110_CT]__$operation值值为1时为删除值为2是插入值为3是更新前的旧值值为4是更新后的新
