Some tip for using SQL

Database Research center of Computer Institute of Harbin University of Technology zhaokai@hit.edu.cn How to display a query plan in SQL Server 2000. Display execution plans as text using SQL Query Analyzer Showplan_text and showplan_allthe two SET options showplan_text and Showplan_all let you see the ES timated query plan without actually executing the query. Both options also automatically enable the SET noexec option, so don ' t, and any results from your query-you and only th e way that SQL Server has determined be the best method for processing the query. Turning noexec on can is a good thing while tuning a query. For example, if you have a query this takes minutes to execute, and you might try to create the index that would allow it to Run faster. However, immediately after creating a new index, your might just want to know whether the query optimizer'll even choose To use that index. If you are were actually executing the query every time you looked in its, it would take to minutes for every Attempt. Setting noexec on along with the "show" option would allow you to the "plan" withOut actually executing all the statements. WARNING Since turning on showplan_text or showplan_all implies this noexec is also in, you must set the SHOWPLAN option to Off before your do anything else. For example, your must set SHOWPLAN_TEXT to off before setting Showplan_all to on. Graphically display the execution plan using SQL Query Analyzer how to use the DBCC PAGE command to view the data page format example: Using Testgoselect * from Stugo results: First look at the content of the sysindexes relationship. Each of the indexes and tables in the database occupies one row in the table. The table is stored in each database. The column name data type describes the Idint table ID (If indid = 0 or 255). Otherwise, the ID of the table to which the index belongs. Statusint internal System state information. Firstbinary (6) A pointer to the first page or root page. Indidsmallint Index id:0 = The table is a new table, the corresponding table is a heap 1 = clustered index >1 = nonclustered 255 = table entry with text or image data Rootbinary (6) If indid >= 1 and 1 repeat. If indid = 255,rows is set to 0. This column is provided for backward compatibility. Select ID, first,indid from sysindexes where ID =object_id (' stu ') and indid in (0,1) DBCC is: DBCC PAGE ({dbid | dbname},fi LENUM,PAGENUM[,PRINTOPT]) to obtain Filenum,filenum, execute the following statement: Select ID, first,indid from sysindexes where ID =object_id (' stu ' ) and indid in (0,1) ID, and then run DBCC DBCC TRACEON (3604) GodThe BCC page (' Test ', 1,25,1) GODBCC completed. If DBCC prints an error message, contact your system administrator. PAGE: (1:25)------------BUFFER:-------BUF @0x18ed5a00---------------bpage = 0x19620000 Bhash = 0x00000000 Bpageno = ( 1:25) Bdbid = 7 Breferences = 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 = 453576 654 M_indexid = 0 M_prevpage = (0:0) M_nextpage = (0:0) Pminlen = 8 m_slotcnt = 8m_freecnt = 7938 M_freedata = 1316 M_reser vedcnt = 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) = CHAN GEDML (1:7) = not min_logged DATA:-----Slot 0, Offset 0x60-------------------the record Type = Primary_record the record Attribute s = Null_bitmap variable_columns 19620060:00080030 0000000c 01000002 7a001300 0 ........ z19620070:6f6168 haoslot 1, Offset 0x73-------------------record Type = Primary_record record Attributes = Null_bitma P variable_columns 19620073:00080030 00000002 01000002 6b001200 0..............k19620083:6961 2, Offset aiSlot----- --------------record Type = Primary_record record Attributes = Null_bitmap variable_columns 19620085:00080030 0000002d 01 000002 77001300 0 ...-.......... w19620095:676e61 angslot 3, Offset 0x98-------------------record Type = Primary_record Re Cord Attributes = null_bitmap variable_columns 19620098:00080030 0000000f 01000002 66001300 0..............f196200a8:67 6e65 Engslot 4, Offset 0xab-------------------record Type = Primary_record record Attributes = Null_bitmap variable_column S 196200ab:00080030 0000004c 01000002 78001100 0...l..........x196200bb:78 x Slot 5, Offset 0xbc-------------------Recor D Type = Primary_record record Attributes = Null_bitmap variable_columns 196200bc:00080030 0000005a 01000002 79001100 0. . z..........y196200cc:79Y Slot 6, Offset 0xcd-------------------record Type = Primary_record record Attributes = Null_bitmap 196200cd:00080010 00 0000c8 020002 ...... Slot 7, Offset 0x50e--------------------record Type = Primary_record record Attributes = Null_bitmap Variable_columns 1962 050e:00080030 000004d2 01000002 7a001600 0..............z1962051e:6b6f6168 6961 TABLE:-------------haokaioffset Ffset 7 (0x7)-1294 (0x50e) 6 (0x6)-205 (0XCD) 5 (0x5)-188 (0XBC) 4 (0x4)-171 (0XAB) 3 (0x3)-152 (0x98) 2 (0x2)- 1 (0x1)-0x85 (0x73) 0 (0x0)-(0x60) DBCC execution completed. If DBCC prints an error message, contact your system administrator. Analysis: Format of data rows 19620060:00080030 0000000c 01000002 7a001300 0..............z19620070:6f6168 haoid = name = Zhao 30 00 0800 0c000000 0200 0100 1300 7a68616f300008000c000000020000010013007a68616f status bit not used find position of first column eighth byte start fixed length the value of the first column is c= 12 The total number of columns for the 2Null sign can be assumed to be a non-empty variable length column number 1 column the first variable length end of the position at 0x13=19 end the first variable-length column Zhao object_id returns the database object identification number. Syntax object_id (' object ') parameter ' object ' to use. The data type of object is char or nchar. If the data type of object is char, then the hiddenSex to convert it into nchar. return type int DBCC EXTENTINFO uses DBCC extentinfo to get a list of all the extents that belong to an object. The syntax is that DBCC extentinfo (DBNAME,TABLENAME,INDEXID) SQL connects the data pages of the clustered index to the linked list, but not to the heap. For example, there are rows and their data as follows (an optional example): 19918060:00080030 0a0a0001 01000004 4e002b00 0............+. n19918070:48207765 20657269 6f4a202d 6f6e2062 ew hire-job no19918080:70732074 66696365 646569 t specifiedjob_id = 1 Jo B_desc = New hire-job not specifiedmin_lvl = MAX_LVL = Ten 00080030 0a0a0001 01000004 4e002b00 48207765 20657269 02d 6f6e2062 70732074 66696365 6465693000080001000a column starting position small int first column data tiny int third column data 0a04000001002btiny int fourth column number of data columns is 4 the nullable variable length column number is 1 the position of the first variable-length column remaining is the string replicate the character expression repeated at the specified number of times. The syntax replicate (character_expression, integer_expression) parameter character_expression an alphanumeric expression consisting of character data. Character_expression can be either a constant or a variable, or it can be a character or binary data column. Integer_expression is a positive integer. If integer_expression is negative, an empty string is returned. The return type varcharcharacter_expression must be a data type that can be implicitly converted to varchar. Otherwise, the character_expression is explicitly converted using the CAST function. For Example:replicate (CAST (1 as varchar (1)), or replicate (' 1 ', 250) And they are the same. If cast (1 as Char), the 1 space occupied by type conversion is 32 bytes if cast (1 as varchar (1)), 1 takes up 1 bytes. Primary key unique in SQL Primary key and unique processing is the same. The system automatically establishes a clustered index on the primary key and the unique constrained columns. The query optimizer makes decisions based on the presence of the index rather than on a fact that is strongly declared as a primary code. For example, after the IT table is established, the system automatically builds a clustered index for it, while the diagram above shows the relationship between Sysobjects,syscolumns and sysindexes. After inserting two rows, the results are as follows: When we insert inserts into it values (3,null) analysis file format 1990408d:00080010 00000003 020002 ... id = 3 NAME = [NULL] 10 00 0800 03000000 0200 021000080003000000020002 The first column of data in the eighth byte of a column data for 3 total two columns NULL = 10 second column empty, end Indexing is not named as clustered or nonclustered, then the system defaults to nonclustered, that is, indid > 1 syslogins retains all logged in user information sp_dboption display or change database options. You cannot use sp_dboption on the master or the tempdb database. Backward compatibility support sp_dboption. Set database options using ALTER database. Syntax sp_dboption [[@dbname =] ' database ' [, [@optname =] ' option_name '] [, [@optvalue =] ' value '] parameter [@dbname =] ' The name of the database in which the specified option is set. The data type for the database is sysname and the default value is NULL. [@optname =] ' Option_name ' The name of the option to set. It is not necessary to enter the full option name. Microsoft? SQL Server? Any unique part of the name that can be recognized. If the option name contains spaces or keywords, enclose the option name in quotation marks. If this argument is omitted,sp_dboption will list the options that are in the open state. The option_name data type is varchar (35) and the default value is NULL. [@value =] New setting for ' value ' option_name. If this argument is omitted, sp_dboption returns the current setting. Value can be true, false, on, or off. The data type of value is varchar (10) and the default value is NULL. The return code value of 0 (success) or 1 (failed) can be sp_dboption to set the batch Insertselect into/bulkcopy the SELECT into statement and fast bulk copy are allowed when true. Cumulative sum problem-t-sql series Cumulative sum problem has one table: col1 value Running_tot 1 10 0 (10) 2 15 0 (25) 3 50 (0) to place the result of a value column cumulative sum into the Running_tot column, you can do this: D Eclare @i intset @i=0update tableset @i=running_tot=@i+value or declare @i intset @i=0update tableset @i=running=@i+ Valuesfrom table