使用sql的一些tip

哈尔滨工业大学计算机学院 数据库研究中心 zhaokai@hit.edu.cn 如何在SQL Server 2000中显示查询计划。 使用 SQL 查询分析器以文本方式显示执行计划 SHOWPLAN_TEXT and SHOWPLAN_ALL The two SET options SHOWPLAN_TEXT and SHOWPLAN_ALL let you see the estimated query plan without actually executing the query. Both options also automatically enable the SET NOEXEC option, so you don't see any results from your query—you see only the way that SQL Server has determined is the best method for processing the query. Turning NOEXEC ON can be a good thing while tuning a query. For example, if you have a query that takes 20 minutes to execute, you might try to create an index that will allow it to run faster. However, immediately after creating a new index, you might just want to know whether the query optimizer will even choose to use that index. If you were actually executing the query every time you looked at its plan, it would take you 20 minutes for every tuning attempt. Setting NOEXEC ON along with the show plan option will allow you to see the plan without actually executing all the statements. WARNING Since turning on SHOWPLAN_TEXT or SHOWPLAN_ALL implies that NOEXEC is also on, you must set the SHOWPLAN option to OFF before you do anything else. For example, you must set SHOWPLAN_TEXT to OFF before setting SHOWPLAN_ALL to ON. 使用 SQL 查询分析器以图形方式显示执行计划 如何使用DBCC PAGE命令来查看数据页格式 例子： use test go select * from stu go 结果： 首先来看sysindexes关系的内容。 数据库中的每个索引和表在表中各占一行。该表存储在每个数据库中。 列名 数据类型 描述 Id Int 表 ID（如果 indid = 0 或 255）。否则为索引所属表的 ID。 Status Int 内部系统状态信息。 First binary(6) 指向第一页或根页的指针。 Indid Smallint 索引 ID： 0 = 该表为新表，对应的表是一个堆 1 = 聚集索引 >1 = 非聚集 255 = 具有 text 或 image 数据的表条目 root binary(6) 如果 indid >= 1 和 < 255，root 是指向根页的指针。如果 indid = 0 或 indid = 255，root 是指向最后一页的指针。 minlen Smallint 最小行大小。 keycnt Smallint 键的数目。 groupid Smallint 在其上创建对象的文件组 ID。 dpages Int 如果 indid = 0 或 indid = 1，dpages 是已用数据页的计数。如果 indid = 255，其设置为 0。否则是已用索引页的计数。 reserved Int 如果 indid = 0 或 indid = 1，reserved 是分配给所有索引和表数据的页计数。如果 indid = 255，reserved 是分配给 text 或 image 数据的页计数。否则是分配给索引的页计数。 used Int 如果 indid = 0 或 indid = 1，used 是用于所有索引和表数据的总页数。如果 indid = 255，used 是用于 text 或 image 数据的页计数。否则是用于索引的页计数。 rowcnt Bigint 基于 indid = 0 和 indid = 1 的数据级行计数。如果 indid = 255，rowcnt 设置为 0。 rowmodctr Int 对自上次更新表的统计后插入、删除或更新行的总数进行计数。 xmaxlen Smallint 最大行大小。 maxirow Smallint 最大非叶索引行大小。 OrigFillFactor Tinyint 创建索引时使用的起始填充因子值。不保留该值；然而，如果需要重新创建索引但记不住当初使用的填充因子，则该值可能很有帮助。 reserved1 Tinyint 保留。 reserved2 Int 保留。 FirstIAM binary(6) 保留。 impid Smallint 保留。索引实现标志。 lockflags Smallint 用于约束经过考虑的索引锁粒度。例如，对于本质上是只读的查找表，可以将其设置为仅进行表级锁定以使锁定成本减到最小。 pgmodctr Int 保留。 keys varbinary(816) 组成索引键的列 ID 列表。 name Sysname 表名（如果 indid = 0 或 255）。否则为索引的名称。 statblob Image 统计 BLOB。 maxlen Int 保留。 rows Int 基于 indid = 0 和 indid = 1的数据级行数，该值对于 indid >1 重复。如果 indid = 255，rows 设置为 0。提供该列是为了向后兼容。 select id ,first,indid from sysindexes where id =object_id('stu') and indid in (0,1) DBCC的格式为: DBCC PAGE ({dbid | dbname},filenum,pagenum[,printopt]) 为了获得filenum，filenum，执行下面的语句： select id ,first,indid from sysindexes where id =object_id('stu') and indid in (0,1) id first indid 453576654 0x190000000100 0 然后运行 DBCC dbcc traceon (3604) go dbcc page('test',1,25,1) go DBCC 执行完毕。如果 DBCC 输出了错误信息，请与系统管理员联系。 PAGE: (1:25) ------------ BUFFER: ------- BUF @0x18ED5A00 --------------- bpage = 0x19620000 bhash = 0x00000000 bpageno = (1:25) bdbid = 7 breferences = 24 bstat = 0x9 bspin = 0 bnext = 0x00000000 PAGE HEADER: ------------ Page @0x19620000 ---------------- m_pageId = (1:25) m_headerVersion = 1 m_type = 1 m_typeFlagBits = 0x0 m_level = 0 m_flagBits = 0x8008 m_objId = 453576654 m_indexId = 0 m_prevPage = (0:0) m_nextPage = (0:0) pminlen = 8 m_slotCnt = 8 m_freeCnt = 7938 m_freeData = 1316 m_reservedCnt = 0 m_lsn = (6:166:2) m_xactReserved = 0 m_xdesId = (0:0) m_ghostRecCnt = 0 m_tornBits = 2 Allocation Status ----------------- GAM (1:2) = ALLOCATED SGAM (1:3) = NOT ALLOCATED PFS (1:1) = 0x61 MIXED_EXT ALLOCATED 50_PCT_FULL DIFF (1:6) = CHANGED ML (1:7) = NOT MIN_LOGGED DATA: ----- Slot 0, Offset 0x60 ------------------- Record Type = PRIMARY_RECORD Record Attributes = NULL_BITMAP VARIABLE_COLUMNS 19620060: 00080030 0000000c 01000002 7a001300 0..............z 19620070: 6f6168 hao Slot 1, Offset 0x73 ------------------- Record Type = PRIMARY_RECORD Record Attributes = NULL_BITMAP VARIABLE_COLUMNS 19620073: 00080030 00000002 01000002 6b001200 0..............k 19620083: 6961 ai Slot 2, Offset 0x85 ------------------- Record Type = PRIMARY_RECORD Record Attributes = NULL_BITMAP VARIABLE_COLUMNS 19620085: 00080030 0000002d 01000002 77001300 0...-..........w 19620095: 676e61 ang Slot 3, Offset 0x98 ------------------- Record Type = PRIMARY_RECORD Record Attributes = NULL_BITMAP VARIABLE_COLUMNS 19620098: 00080030 0000000f 01000002 66001300 0..............f 196200a8: 676e65 eng Slot 4, Offset 0xab ------------------- Record Type = PRIMARY_RECORD Record Attributes = NULL_BITMAP VARIABLE_COLUMNS 196200ab: 00080030 0000004c 01000002 78001100 0...L..........x 196200bb: 78 x Slot 5, Offset 0xbc ------------------- Record Type = PRIMARY_RECORD Record Attributes = NULL_BITMAP VARIABLE_COLUMNS 196200bc: 00080030 0000005a 01000002 79001100 0...Z..........y 196200cc: 79 y Slot 6, Offset 0xcd ------------------- Record Type = PRIMARY_RECORD Record Attributes = NULL_BITMAP 196200cd: 00080010 000000c8 020002 ........... Slot 7, Offset 0x50e -------------------- Record Type = PRIMARY_RECORD Record Attributes = NULL_BITMAP VARIABLE_COLUMNS 1962050e: 00080030 000004d2 01000002 7a001600 0..............z 1962051e: 6b6f6168 6961 haokai OFFSET TABLE: ------------- Row - Offset 7 (0x7) - 1294 (0x50e) 6 (0x6) - 205 (0xcd) 5 (0x5) - 188 (0xbc) 4 (0x4) - 171 (0xab) 3 (0x3) - 152 (0x98) 2 (0x2) - 133 (0x85) 1 (0x1) - 115 (0x73) 0 (0x0) - 96 (0x60) DBCC 执行完毕。如果 DBCC 输出了错误信息，请与系统管理员联系。 分析：数据行的格式 19620060: 00080030 0000000c 01000002 7a001300 0..............z 19620070: 6f6168 hao id = 12 name = zhao 30 00 0800 0c000000 0200 00 0100 1300 7a68616f 30 00 0800 0c000000 0200 00 0100 1300 7a68616f 状态位 未使用 找到第一 列的位置 第八个字节开始 定长第一列的值为 c=12 总列数为2 Null标志 均可以为非空 变长列数 为1列 第一个变长结束的位置 在0x13=19结束 第一个变长列 zhao OBJECT_ID 返回数据库对象标识号。 语法 OBJECT_ID ( 'object' ) 参数 'object' 要使用的对象。object 的数据类型为 char 或 nchar。如果 object 的数据类型是 char，那么隐性将其转换成 nchar。 返回类型 int DBCC EXTENTINFO 利用DBCC EXTENTINFO得到属于一个对象的所有盘区的列表。 其语法为： DBCC EXTENTINFO(dbname,tablename,indexid) sql 会把聚集索引的数据页用链表连接起来，但对于堆不需要。 例如有 以及 其数据行如下（选例）： 19918060: 00080030 0a0a0001 01000004 4e002b00 0............+.N 19918070: 48207765 20657269 6f4a202d 6f6e2062 ew Hire - Job no 19918080: 70732074 66696365 646569 t specified job_id = 1 job_desc = New Hire - Job not specified min_lvl = 10 max_lvl = 10 00080030 0a0a0001 01000004 4e002b00 48207765 20657269 6f4a202d 6f6e2062 70732074 66696365 646569 30 00 0800 0100 0a 列数开始的位置 Small int 第一列数据 Tiny int 第三列数据 0a 0400 00 01 002b Tiny int 第四列数据 列数为4 均可空 变长列数为1 第一个变长列结束的位置 剩下的为字符串 REPLICATE 以指定的次数重复字符表达式。 语法 REPLICATE ( character_expression , integer_expression ) 参数 character_expression 由字符数据组成的字母数字表达式。character_expression 可以是常量或变量，也可以是字符列或二进制数据列。 integer_expression 是正整数。如果 integer_expression 为负，则返回空字符串。 返回类型 varchar character_expression 必须为可隐性转换为 varchar 的数据类型。否则，使用 CAST 函数显式转换 character_expression。 For example: replicate(cast(1 as varchar(1)),250) or replicate(‘1’,250) and they are the same. 如果是cast(1 as char)则，类型转化后的1所占的空间为32个字节 如果是cast(1 as varchar(1)),则1所占的空间为1个字节。 Primary key unique 在SQL中primary key 和unique的处理是一样的。系统会自动在primary key 和unique所约束的列上建立聚集索引。查询优化器基于位于索引的存在而不是基于一个烈被声明为主码的事实来作出决策。例如： 在建立it表后，系统自动为it建立聚集索引，同时上图表明了sysobjects,syscolumns和sysindexes的关系。 待插入两行后，结果如下： 当我们插入 Insert into it values(3,null) 分析文件格式 1990408d: 00080010 00000003 020002 ........... id = 3 name = [NULL] 10 00 0800 03000000 0200 02 10 00 0800 03000000 0200 02 第一列数据在第八个字节处 一个列数据为3 共有两列 Null = 10 第二列为空，结束 建立索引是如不指名是clustered或nonclustered,则系统默认为非簇集的，即indid > 1 syslogins中保留了所有登陆用户的信息 sp_dboption 显示或更改数据库选项。不能在 master 或 tempdb 数据库上使用 sp_dboption。向后兼容性支持 sp_dboption。使用 ALTER DATABASE 设置数据库选项。 语法 sp_dboption [ [ @dbname = ] 'database' ] [ , [ @optname = ] 'option_name' ] [ , [ @optvalue = ] 'value' ] 参数 [@dbname =] 'database' 在其中设置指定选项的数据库的名称。database 的数据类型为 sysname，默认值为 NULL。 [@optname =] 'option_name' 要设置的选项的名称。没有必要输入完整的选项名称。Microsoft® SQL Server™ 可识别名称中任何独有的部分。如果选项名称包含空格或者关键字，请将选项名称用引号引起来。如果省略此参数，sp_dboption 将列出处于打开状态的选项。option_name 的数据类型为 varchar(35)，默认值为 NULL。 [@value =] 'value' option_name 的新设置。如果省略此参数，sp_dboption 将返回当前设置。value 可以是 true、false、on 或 off。value 的数据类型为 varchar(10)，默认值为 NULL。 返回代码值 0（成功）或 1（失败） 可以用sp_dboption 来设置批量insert select into/bulkcopy 当为 true 时，允许使用 SELECT INTO 语句和快速大容量复制。 累积求和问题－T－SQL系列 累积求和问题 有一表： col1 value running_tot 1 10 0 （10） 2 15 0 （25） 3 50 0 （75） 若要将value列累积求和的结果放到running_tot 列，可以这样： declare @i int set @i=0 update table set @i=running_tot=@i+value 或者 declare @i int set @i=0 update table set @i=running=@i+values from table