[轉載] Index Scan vs Seek in SQL Server

FAQ : Index Scan Vs Seek in SQL Server

There are five logical/physical operators in SQL Server related to Index scan ,Index Seek and Table scan. 

(a) Table Scan

(b) Clustered Index Scan

(c) Clustered Index Seek

(d) Index Scan (Non- Clustered index Seek)

(e) Index Seek (Non- Clustered index Seek)

Table Scan 

Table Scan retrieves all rows from the table (if you have no WHERE Conditions). Basically, before returning the rows, it traverse through all data pages related to the table. If you have where condition, though it travel through all the pages only those rows are returned which satisfy the conditions. When you do not have Clustered index on the table it does a table scan. In other words, both Clustered Index Scan (clustered index is nothing but the data itself) and Table Scan are same because in both method system traverse through all the data pages. Generally, you should avoid table scan.

Clustered Index Scan

Clustered Index Scan is nothing but horizontally traversing though the clustered index data pages. Clustered Index Scan return all the rows from the clustered index (Clustered index is nothing but Data). If you have where condition , only the satisfying rows are returned, but system traverse through all the data pages of the clustered index. Both Table scan and Clustered Index Scan are generally considered to be bad. But at times like if the table is small contains only few rows table or clustered index scan may be good also

Clustered Index Seek

Clustered Index Seek traverse vertically right down to the Data page where the requested data is stored. Basically any seek is vertically traversing though the B-Tree structure (as we all know the index is stored in B-tree structure in sql server). System does a Seek when it find a useful index and generally its done for highly selective query.

Index Scan or Non-Clustered Index Scan

As already said, Scan is horizontal traversing of B-Tree data pages. But in this case it horizontally traverse though the Non-Clustered index available. Its not same as Clustered index scan or Table Scan. In SQL Server , while reading execution plan you can find only Index Scan not Non-Clustered Index Scan. But you must read Index Scan as Non-Clustered Index Scan.

Index Seek or Non-Clustered Index Seek

As already mentioned Seek is Vertical traversing of B-Tree to the data page. But in Index seek it vertical traversing of Non-Clustered Index. Generally, its considered as the best option for high selective query.

Crystal report with Dynamic Column

The objective of this article is to provide a process which can be followed to develop a crystal report with dynamic columns using parameterized column fields and formula fields. Before going for this the developer has to decide the maximum number of columns he/she has to display and as per the no. of columns the size of the paper. The developer can define the size of the paper by selecting Printer Setup in File menu.

There are two ways to display the columns in Crystal Report dynamically. The default is using the infamous Cross Tab component and the other is using parameterized columns. But there are certain limitations to this . Where the cross tab does not provide complete control to each individual cells value, for using dynamic columns, the developer has to decide the maximum no columns in design time and all the field values should be of same data type.

Lets take an example of an yearly report where the developer has to create  such a report , when executed will display only those month values as columns , which are selected with "Total" immediately next . So the view may look like :

If the sales of products selected from Mar 2006 to May 2006.

Product Name	Mar	Apr	May	Total
Pro 1	12	89	90	191
Pro 2	20	99	70	199
Grand Total	32	188	160	390

Same way if the end results for different scenario :

1. If date is between Jan 2006 - Aug 2006
   Product Name  Jan  Feb  Mar  Apr  May  Jun  Jul  Aug  Total
2. If date is between Jun 2006 - Nov 2006
   Product Name  Jun  Jul  Aug  Sep  Oct  Nov  Total

Note : These instructions only work if all the attribute fields are of the same data type as they will be dynamically interchanged using a logic statement. 

It seems most of the attribute fields will be of a String data type. We can create formulas in our report that will convert numbers to text using the ToText function. Then, simply use the formula instead of the database field. Or, if our organization uses Views or Queries as data source, convert the data type on that end.

Steps to create a report with dynamic columns...

1) Determine the maximum number of columns the report page can handle. Or determine the reasonable number of attributes an end user would want to see. (Consider a report with 50 columns of attributes. It would be unreadable.)

2) Create a string parameter for every attribute column on the report. Do not set the default values at this time (see next step).

3) Open Note Pad (on your desktop under accessories). Type all possible attributes starting in top right corner with one below the other (see below).

None
Jan
Feb
Mar
Apr
May
Jun
Jul
Aug
Sep
Oct
Nov
Dec
Total

Keep in mind, these will be listed in the pick list and displayed on the report as column headings. The value "None" is important. It will be used in a logic statement, later in these instructions. Save the file (as a text file, .txt) in a shared location all users of the report can access (or on the CE server if using Enterprise).

Name the file similar to the .rpt file name combined with the word "attributes" or "parameter" so it is easy to locate.

4) Edit each parameter created in step 2 and do the following:

Click on "Set default values".

Click on "Import pick list".

Browse to locate the text file you just created in step 3.

Click OK. The list is automatically populated with the values in the text file.

5) Create empty formulas for the detail fields. The Design will look something like this...

Row Num Product name {?Month1} {?Month2} {?Month3}{?Month4}........................{?Total}
{rownum} {db.ProdName} {@mon.1-Detail} {@Mon.2-Detail} {@Mon.3-Detail}.............{@Total-Detail}

Just use the Insert Fields box and create the formulas up front. Leave them empty. Place them on the Design Tab. Then use the Insert Fields box to edit them later.

6) Edit the Mon.1-Detail field. Enter the following logic...

(assuming the Stored Proc or View returning column names as ProdName Jan Feb Mar Apr May Jun Jul Aug Sep Oct Nov Dec)

if {?Month1}= "JAN" then {DB.JAN}
else if {?Month1}= "FEB" then {DB.FEB}
else if {?Month1}= "MAR" then {DB.MAR}
else if {?Month1}= "APR" then {DB.APR}
else if {?Month1}= "MAY" then {DB.MAY}
else if {?Month1}= "JUN" then {DB.JUN}
else if {?Month1}= "JUL" then {DB.JUL}
else if {?Month1}= "AUG" then {DB.AUG}
else if {?Month1}= "SEP" then {DB.SEP}
else if {?Month1}= "OCT" then {DB.OCT}
else if {?Month1}= "NOV" then {DB.NOV}
else if {?Month1}= "DEC" then {DB.DEC}
------ add the total field value if applicable
else 0;

Save the formula changes, but don't exit the formula editor. Copy the entire logic statement, then exit.

7) Edit {@Mon.2-Detail}. Paste the logic statement from step 6 into the Formula editor. Use Find/Replace in the formula Editor to find "?Month1" and replace with "?Month2".

Then save the change. Repeat this process for the remaining column detail formulas.

Note: Incase of Total field it will start from the second field onwards. So to display the Total value add the line below (incase if second column will be counted as the total column)

else if {?Month2}= "Total" then {@Month_Det1}

Same way for all other columns also add .....

else if {?Month3}= "Total" then {@Month_Det1} +  {@Month_Det2} If 3rd will be the Total column

else if {?Month4}= "Total" then {@Month_Det1} +  {@Month_Det2} + {@Month_Det3} if 4th col will be the Total and so on.. 

8) You may also want to add conditional formatting to supress the columns that are not used. To do this you need to edit each field in the column (header and detail).

Start with Column 1's heading...

Right click on the parameter field, {?Column 1}.
Select Format field.
On the Common tab find Suppress at the top.

Go to the right and click on the "X+2" button. Enter the following logic in the Format formula editor:

if {?Month1}="None" then true else false;

Save, but don't close. Copy the statement. Close the Format formula editor.

Repeat on the {@Month.1-Detail} field using the pasted statement.

Repeat on the remaining columns, but remember to change the column number in the logic.

9) To add a running total for each column select a new running total field as display below

Select the parameterized field to sum up and and in the type of summary field select Sum from the dropdown.

Repeat the same process for all the columns as displayed above and also suppress them as per the condition (Refer Sec 8 to make a conditional suppress).

How it works :

1) The column heading will display the parameter value selected by the user. It will display just as you typed it into the default value list when creating the parameter.

Example :

In the example below I am displaying the records of references  during the month of Aug and Sep 2006 including the total and grand total value.

Select Aug for the month1 parameter field

Repeat it to select Sep for month2 parameter field and Total for month3 field . And select From Date and To date as 1st Aug 2006  - 30th Sep 2006.

2) The detail section will display the corresponding attributes for each item record based on the parameter values provided at run time.

On execution of this report it will display like the below

Contribution by: Kishore Gandra , Sarika Pahurkar , Edmond Joseph

http://www.c-sharpcorner.com/UploadFile/uditsingh/CR1111022006055359AM/CR11.aspx

[iPhone] How to Enable Emoji on iPhone iOS 4

Emoji icons disappeared for a while with the release of iOS 4. But they’re back! This simple trick, compliments of Simon, will show you how to enable Emoji icons on your iPhone iOS 4.

Step 1: Download this free app from the App Store.

Step 2: Launch the app and tap “ok, let’s do this”.

Step 3: Go to Settings > General > Keyboard > International Keyboard > Add New Keyboard > Emoji.

Step 4: Reboot your iPhone.

You can now get emoji icons on your iPhone. To bring up the emoji keyboard, launch your Notes.app and tap the little “globe” icon next to the space key.

If you send an email containing emoji icons, the person you send the email to must have emoji enabled too. For some reason, I wasn’t able to use emoji icons in the Email.app. It worked great for notes though.

How is it working for you?

Quoted from: http://www.iphonedownloadblog.com/2010/06/30/how-to-enable-emoji-on-iphone-ios-4/

ABAP Read_Text Function Module Tutorial To Read Long Text

In today’s tutorial, I want to show you how to use the READ_TEXT function module to read SAP long text. Every text in SAP has its own ID and NAME, by passing these parameters into the READ_TEXT function, we can get all the text in the SAP long text object.

Here’s how to do it, for example we’ll be using long text from Sales Order.

1. Execute tcode VA02 and enter the Sales Order number.



2. Press enter to display the Sales Order data. Choose GoTo -> Header -> Texts



3. Double click the “Header Note” text and enter the long text in the text editor.



4. Click CTRL+S to save the Sales Order data.

5. Now that we need to get the “ID”, “NAME” and “OBJECT” of the text object to be used as the input parameters of READ_TEXT function module, to get this information, you can execute VA02 or VA03 and enter the Sales Order number again.
Choose GoTo -> Header -> Texts


6. Now double click inside the long text editor.



7. Choose Goto -> Header.



8. On the next window screen, you will see all the parameters required for the READ_TEXT function. (Name, Language, ID, Object)



9. Now let’s get the text using ABAP, open your ABAP editor (SE38), copy this code below.

"A sample code to use READ_TEXT FM 

"www.freesaptutorial.com

  data:
        BEGIN OF header OCCURS 0,
          ld_txt1(163),
          ld_txt2(163),
          ld_txt3(163),
        END OF header.

  DATA: li_lines LIKE STANDARD TABLE OF tline WITH HEADER LINE,
        ID like THEAD-TDID,
        TNAME LIKE THEAD-TDNAME,
        TDOBJECT like THEAD-TDOBJECT.

        ID = '0001'.
        TNAME = '1820000009'.
        TDOBJECT = 'VBBK'.

  CALL FUNCTION 'READ_TEXT'
    EXPORTING
      id       = id
      language = sy-langu
      name     =  TNAME
      object   = TDOBJECT
    TABLES
      lines    = li_lines.

  READ TABLE li_lines INDEX 1.
  IF sy-subrc = 0.
    header-ld_txt1 = li_lines-tdline.
  ENDIF.

  READ TABLE li_lines INDEX 2.
  IF sy-subrc = 0.
    header-ld_txt2 = li_lines-tdline.
  ENDIF.

  READ TABLE li_lines INDEX 3.
  IF sy-subrc = 0.
    header-ld_txt3 = li_lines-tdline.
  ENDIF.

  WRITE:/ header-ld_txt1.
  WRITE:/ header-ld_txt2.
  WRITE:/ header-ld_txt3.

10. Now execute the program (F8), you will see the long text from the header note text object.

Quoted from http://www.freesaptutorial.com/abap-read_text-function-module-tutorial-to-read-long-text/

[ABAP] Display ListBox in Parameter of Selection Screen

Display ListBox in Parameter of Selection Screen

REPORT zpwtest.


TYPE-POOLS: vrm.

DATA: name TYPE vrm_id,

      list TYPE vrm_values,

      value LIKE LINE OF list.

 

PARAMETERS: ps_parm(10) AS LISTBOX VISIBLE LENGTH 10.

 

AT SELECTION-SCREEN OUTPUT.

 

  name = 'PS_PARM'.

  value-key = '1'.

  value-text = 'LINE 1'.

  APPEND value TO list.

  value-key = '2'.

  value-text = 'LINE 2'.

  APPEND value TO list.

  CALL FUNCTION 'VRM_SET_VALUES'

       EXPORTING

            id     = name

            values = list.

 

START-OF-SELECTION.

  WRITE: / 'PARAMETER:', ps_parm.

ABAP程式設計師要懂的103個問題

1. What is the typical structure of an ABAP program? 
2. What are field symbols and field groups.? Have you used "component idx of structure" clause with field groups? 
3. What should be the approach for writing a BDC program? 
4. What is a batch input session? 
5. What is the alternative to batch input session? 
6. A situation: An ABAP program creates a batch input session. We need to submit the program and the batch session in background. How to do it? 
7. What is the difference between a pool table and a transparent table and how they are stored at the database level? 
8. What are the problems in processing batch input sessions? How is batch input process different from processing on line? 
9. What do you define in the domain and data element? 
10. What are the different types of data dictionary objects? 
11. How many types of tables exists and what are they in data dictionary? 
12. What is the step by step process to create a table in data dictionary? 
13. Can a transparent table exist in data dictionary but not in the data base physically? 
14. What are the domains and data elements? 
15. Can you create a table with fields not referring to data elements? 
16. What is the advantage of structures? How do you use them in the ABAP programs?
17. What does an extract statement do in the ABAP program? 
18. What is a collect statement? How is it different from append? 
19. What is open sql vs native sql? 
20. What does an EXEC SQL stmt do in ABAP? What is the disadvantage of using it? 
21. What is the meaning of ABAP editor integrated with ABAP data dictionary? 
22. What are the events in ABAP language? 
23. What is an interactive report? What is the obvious diff of such report compared with classical type reports? 
24. What is a drill down report? 
25. How do you write a function module in SAP? describe. 
26. What are the exceptions in function module? 
27. What is a function group? 
28. How are the date abd time field values stored in SAP? 
29. What are the fields in a BDC_Tab Table. 
30. Name a few data dictionary objects? 
31. What happens when a table is activated in DD? 
32. What is a check table and what is a value table? 
33. What are match codes? describe? 
34. What transactions do you use for data analysis? 
35. What is table maintenance generator? 
36. What are ranges? What are number ranges? 
37. What are select options and what is the diff from parameters? 
38. How do you validate the selection criteria of a report? And how do you display initial values in a selection screen? 
39. What are selection texts? 
40. What is CTS and what do you know about it? 
41. When a program is created and need to be transported to prodn does selection texts always go with it? if not how do you make sure? Can you change the CTS entries? How do you do it? 
42. What is the client concept in SAP? What is the meaning of client independent? 
43. Are programs client dependent? 
44. Name a few system global variables you can use in ABAP programs? 
45. What are internal tables? How do you get the number of lines in an internal table? How to use a specific number occurs statement?
46. How do you take care of performance issues in your ABAP programs? 
47. What are datasets? 
48. How to find the return code of a stmt in ABAP programs? 
49. What are interface/conversion programs in SAP? 
50. Have you used SAP supplied programs to load master data? 
51. What are the techniques involved in using SAP supplied programs? Do you prefer to write your own programs to load master data? Why?
52. What are logical databases? What are the advantages/disadvantages of logical databases? 
53. What specific statements do you using when writing a drill down report? 
54. What are different tools to report data in SAP? What all have you used? 
55. What are the advantages and disadvantages of ABAP query tool? 
56. What are the functional areas? User groups? and how does ABAP query work in relation to these? 
57. Is a logical database a requirement/must to write an ABAP query? 
58. What is the structure of a BDC sessions. 
59. What are Change header/detail tables? Have you used them? 
60. What do you do when the system crashes in the middle of a BDC batch session? 
61. What do you do with errors in BDC batch sessions? 
62. How do you set up background jobs in SAP? What are the steps? What are the event driven batch jobs? 
63. Is it possible to run host command from SAP environment? How do you run? 
64. What kind of financial periods exist in SAP? What is the relavent table for that? 
65. Does SAP handle multiple currencies? Multiple languages? 
66. What is a currency factoring technique? 
67. How do you document ABAP programs? Do you use program documentation menu option? 
68. What is SAPscript and layout set? 
69. What are the ABAP commands that link to a layout set? 
70. What is output determination? 
71. What are IDOCs? 
72. What are screen painter? menu painter? Gui status? ..etc. 
73. What is screen flow logic? What are the sections in it? Explain PAI and PBO. 
74. Overall how do you write transaction programs in SAP? 
75. Does SAP has a GUI screen painter or not? If yes what operating systems is it available on? What is the other type of screen painter called? 
76. What are step loops? How do you program pagedown pageup in step loops? 
77. Is ABAP a GUI language? 
78. Normally how many and what files get created when a transaction program is written? What is the XXXXXTOP program? 
79. What are the include programs? 
80. Can you call a subroutine of one program from another program? 
81. What are user exits? What is involved in writing them? What precations are needed? 
82. What are RFCs? How do you write RFCs on SAP side? 
83. What are the general naming conventions of ABAP programs? 
84. How do you find if a logical database exists for your program requrements? 
85. How do you find the tables to report from when the user just tell you the transaction he uses? And all the underlying data is from SAP structures? 
86. How do you find the menu path for a given transaction in SAP? 
87. What are the different modules of SAP? 
88. What is IMG in SAP? 
89. How do you get help in ABAP? 
90. What are different ABAP editors? What are the differences? 
91. What are the different elements in layout sets? 
92. Can you use if then else, perform ..etc statements in sap script? 
93. What type of variables normally used in sap script to output data? 
94. How do you number pages in sapscript layout outputs? 
95. What takes most time in SAP script programming? 
96. How do you use tab sets in layout sets? 
97. How do you backup sapscript layout sets? Can you download and upload? How? 
98. What are presentation and application servers in SAP? 
99. In an ABAP program how do you access data that exists on a presentation server vs on an application server? 
100. What are different data types in ABAP? 
101. What is difference between BDC and Call Transaction? 
102. Setting up a BDC program where you find information from? 
103. What has to be done to the packed fields before submitting to a BDC session. 


Quoted from: http://aspromise.pixnet.net/blog/post/19896714