行使sql的一些tip
发布时间：2018-08-18 11:53:06 所属栏目：电商 来源：站长网
导读：哈尔滨家产大学计较机学院数据库研究中心 zhaokai@hit.edu.cn如安在SQL Server 2000中表现查询打算。 行使 SQL 查询说明器以文本方法表现执行打算SHOWPLAN_TEXT and SHOWPLAN_ALLThe two SET options SHOWPLAN_TEXT and SHOWPLAN_ALL let you see the estim
|
哈尔滨家产大学计较机学院数据库研究中心 zhaokai@hit.edu.cn如安在SQL Server 2000中表现查询打算。 行使 SQL 查询说明器以文本方法表现执行打算SHOWPLAN_TEXT and SHOWPLAN_ALLThe 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 testgoselect * from stugo功效： 起首来看sysindexes相关的内容。数据库中的每个索引和表在表中各占一行。该表存储在每个数据库中。列名数据范例描写IdInt表 ID（假如 indid = 0 或 255）。不然为索引所属表的 ID。StatusInt内部体系状态信息。Firstbinary(6)指向第一页或根页的指针。IndidSmallint索引 ID： 0 = 该表为新表，对应的表是一个堆1 = 聚积索引>1 = 非聚积255 = 具有 text 或 image 数据的表条目rootbinary(6)假如 indid >= 1 和 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 indid4535766540x1900000001000然后运行 DBCC dbcc traceon (3604)godbcc page('test',1,25,1)goDBCC 执行完毕。假如 DBCC 输出了错误信息，请与体系打点员接洽。PAGE: (1:25)------------BUFFER:-------BUF @0x18ED5A00---------------bpage = 0x19620000 bhash = 0x00000000 bpageno = (1:25)bdbid = 7 breferences = 24 bstat = 0x9bspin = 0 bnext = 0x00000000 PAGE HEADER:------------Page @0x19620000----------------m_pageId = (1:25) m_headerVersion = 1 m_type = 1m_typeFlagBits = 0x0 m_level = 0 m_flagBits = 0x8008m_objId = 453576654 m_indexId = 0 m_prevPage = (0:0)m_nextPage = (0:0) pminlen = 8 m_slotCnt = 8m_freeCnt = 7938 m_freeData = 1316 m_reservedCnt = 0m_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 ALLOCATEDPFS (1:1) = 0x61 MIXED_EXT ALLOCATED 50_PCT_FULL DIFF (1:6) = CHANGEDML (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..............z19620070: 6f6168 haoSlot 1, Offset 0x73-------------------Record Type = PRIMARY_RECORD Record Attributes = NULL_BITMAP VARIABLE_COLUMNS 19620073: 00080030 00000002 01000002 6b001200 0..............k19620083: 6961 aiSlot 2, Offset 0x85-------------------Record Type = PRIMARY_RECORD Record Attributes = NULL_BITMAP VARIABLE_COLUMNS 19620085: 00080030 0000002d 01000002 77001300 0...-..........w19620095: 676e61 angSlot 3, Offset 0x98-------------------Record Type = PRIMARY_RECORD Record Attributes = NULL_BITMAP VARIABLE_COLUMNS 19620098: 00080030 0000000f 01000002 66001300 0..............f196200a8: 676e65 engSlot 4, Offset 0xab-------------------Record Type = PRIMARY_RECORD Record Attributes = NULL_BITMAP VARIABLE_COLUMNS 196200ab: 00080030 0000004c 01000002 78001100 0...L..........x196200bb: 78 x Slot 5, Offset 0xbc-------------------Record Type = PRIMARY_RECORD Record Attributes = NULL_BITMAP VARIABLE_COLUMNS 196200bc: 00080030 0000005a 01000002 79001100 0...Z..........y196200cc: 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..............z1962051e: 6b6f6168 6961 haokaiOFFSET 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..............z19620070: 6f6168 haoid = 12 name = zhao 30 00 0800 0c000000 0200 00 0100 1300 7a68616f300008000c000000020000010013007a68616f状态位未行使找到第一 列的位置第八个字节开始定长第一列的值为 c=12总列数为2Null符号均可觉得非空变长列数为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............+.N19918070: 48207765 20657269 6f4a202d 6f6e2062 ew Hire - Job no19918080: 70732074 66696365 646569 t specifiedjob_id = 1 job_desc = New Hire - Job not specifiedmin_lvl = 10 max_lvl = 10 00080030 0a0a0001 01000004 4e002b00 48207765 20657269 6f4a202d 6f6e2062 70732074 66696365 6465693000080001000a列数开始的位置Small int 第一列数据Tiny int第三列数据0a04000001002bTiny int第四列数据列数为4均可空变长列数为1第一个变长列竣事的位置剩下的为字符串 REPLICATE以指定的次数一再字符表达式。语法REPLICATE ( character_expression , integer_expression ) 参数character_expression由字符数据构成的字母数字表达式。character_expression 可所以常量或变量，也可所以字符列或二进制数据列。integer_expression是正整数。假如 integer_expression 为负，则返回空字符串。返回范例varcharcharacter_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 021000080003000000020002第一列数据在第八个字节处一个列数据为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 来配置批量insertselect 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 intset @i=0update tableset @i=running_tot=@i+value可能declare @i intset @i=0update tableset @i=running=@i+valuesfrom table （编辑：湖南网） 【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容! |
