Happy Coding ...

I heard my PM saying today "Their are two types of Idiots. One who will take multiple trips to the database to compare value. Others who will take the values in memory and compare then their". We have a situation. We need to take values from one table and check if it exists in the second table. Depending on weather it exists in the second table or not, we need to display with / without highlight it in the datagrid. There is one catch. The query is written by the user in one of the application screen and the user has no idea that the 2nd condition table needs to be applied. I know you would debate that we need to create a screen, so that the user will select what is required instead of writing the query. I know... but we need this asap. Screen will be in the next release. Let me elaborate: Consider that we have table A and table B. Both these tables have ID column. The user writes (on the application screen) SELECT * FROM A   I can check that this ID does not exist in ...

I was reviewing this application created by my colleague and was wondering, do we need a stored procedure for single insert statements. Could this be not written directly in the code? First, let me tell you what is a Stored Procedure is. A Stored Procedure is a group of Transact-SQL statements (DDL & DML) compiled into a single execution plan. It contain programming statements that perform operations in the database, including calling other procedures. A proc can accept input parameters and return multiple values in the form of output parameters to the calling procedure or batch. There are various advantages of a stored procedure (sometimes called a proc, sproc, StoPro, StoredProc, or SP) over inline code. The most important being 'Security' and 'Speed'. Compilation and storing of the query execution plan - Increases Speed & Reduces Network Traffic When SQL Server runs a query it calculates the most efficient method of processing the query and stores it in s...

Data deduplication, often called "intelligent compression" or "single-instance storage", is a process that uses matching logic to eliminate file records that are duplicates (dupes). It is a method of reducing storage needs by eliminating redundant data and replacing it with a pointer to the unique data copy Data deduplication offers other benefits. Lower storage space requirements will save money on disk expenditures. The more efficient use of disk space also allows for longer disk retention periods, which provides better recovery time objectives (RTO) for a longer time and reduces the need for tape backups. Data deduplication also reduces the data that must be sent across a WAN for remote backups, replication, and disaster recovery. Deduping is a 3 step process Step 1: Move the non duplicates (unique tuples) into a temporary table SELECT * into new_table FROM old_table WHERE 1 GROUP BY [column to remove duplicates by]; Step 2: Delete the old table. We no ...

Sharing Made Easy continued... When you are sharing something conflicts are bound to happen. A conflict happens when two users are both editing the same shared workbook and try to save changes that affect the same cell. Excel can keep only one of the changes in that cell. When the second user saves the workbook, Excel displays the RESOLVE CONFLICTS dialog box. 1. In the RESOLVE CONFLICTS dialog box, read the information about each change and the conflicting changes made by the other user. 2. To keep your change or the other person's change and to advance to the next conflicting change, click ACCEPT MINE or ACCEPT OTHER. To keep all of your remaining changes or all of the other user's changes, click ACCEPT ALL MINE or ACCEPT ALL OTHERS. To override resolve conflicts dialog To have your changes override all other changes without displaying the RESOLVE CONFLICTS dialog box again,: click SHARE WORKBOOK. On the ADVANCED tab, under CONFLICTING CHANGES BETWEEN USERS, cli...

Conditional Looping means executing the same code again and again, as long as the specified condition is true. 'WHILE' statement helps in executing a statement or a block of statement repeatedly, till the specified condition(s) is true. The number of times depends on the condition statement writen after the 'WHILE' keyword. The execution of statements in the WHILE loop can be controlled from inside the loop with the BREAK and CONTINUE keywords. Break causes an exit from the innermost WHILE loop. Any statements that appear after the END keyword, marking the end of the loop, are executed.  CONTINUE  causes the WHILE loop to restart, ignoring any statements after the CONTINUE keyword. Syntax for WHILE WHILE ( Boolean_expression )    BEGIN    'Sql Statement Block'    END Simple WHILE Example    WHILE (x>y)        BEGIN        SELECT 'If x is greater than y, this statement ...

Want to make changes to the database table in Microsoft SQL Management Studio, but getting the following message "Saving changes is not permitted" (figure 1)   Step 1: Go to Tools, and click on Options. The following screen gets displayed. Step 2: Click on Designers => Table and Database Designers (highlighted above). Step 3: Unchecke "Prevent saving changes that require table re-creation" and click on 'OK' Note: You may also unchecked 'Warn about tables affected'. If this is checked you will see the following message. Click on OK. All the requested changes are saved. Done!!

You must have been in a situation wherein you have to select between this or that, right or left depending on your present situation. If you were to stimulate this kind of situation in SQL, you need to use the IF-ELSE statement. IF - ELSE is the most commonly used Transact SQL command, that executes a statement depending on the condition. If the condition for IF statement is True, the statements inside the 'Begin-End' loop of 'IF' statement will be executed. If the condition for IF statement is False, the statements inside the 'Begin-End' loop of 'ELSE' statement will be executed. Thus, depending on which condition is true, statement or block of statements are executed The basic Syntax for IF..ELSE is: IF ( Boolean_expression )    BEGIN    'Sql Statement Block'    END ELSE    BEGIN    'Sql Statement Block'    END Simple If..Else Statement example IF (x>y)    BEGIN    SELECT 'If x is gre...

If you wish to perform a function on a group of cells, you must first select those cells by highlighting them. The exercises that follow teach you how to select. To select cells A1 to E1: Go to cell A1. Press the F8 key. This anchors the cursor. Note that "Extend Selection" appears on the Status bar in the lower-left corner of the window. You are in the Extend mode. Click in cell E7. Excel highlights cells A1 to E7. Press Esc and click anywhere on the worksheet to clear the highlighting. Alternative Method:     Select Cells by Dragging You can also select an area by holding down the left mouse button and dragging the mouse over the area. In addition, you can select noncontiguous areas of the worksheet by doing the following: Go to cell A1. Hold down the Ctrl key. You won't release it until step 9. Holding down the Ctrl key enables you to select noncontiguous areas of the worksheet. Press the left mouse button. While holding down the left mouse button, ...

Select the current column [Ctrl] + [Space] Select the current row [Shift] + [Space] Edit the active cell [F2] Move to the beginning of the worksheet [Ctrl] + [Home] Move to the last cell on the worksheet [Ctrl] + [End] Paste a name into a formula [F3] Paste a function into a formula [Shift] + [F3] Alternate value/formula view [Ctrl] + [`] (on key [1]) Calculate all sheets in all open workbooks [F9] To go to a cell quickly [F5] or [Ctrl] + [G] Display the Find dialog box [Shift] + [F5] Display the Format Cells dialog box [Ctrl] + [1] Create a chart [F11] Insert a new sheet [Alt] + [Shift] + [F1] Repeat the last action [F4] Repeat Find [Shift] + [F4] Open [Ctrl] + [F12] Exit [Ctrl] + [F4] Check spelling of current cell [F7] Activate the menu bar [F10] Display the Macro dialog box [Alt] + [F8] Apply outline to active cell [Ctrl] + [Shift] + [&] Convert to a percentage [Ctrl] + [Shift] + [%] Select all filled cells around active cell [Ctrl]...

LOOKUP As the name suggests, Excel gives us the option to lookup for a number or text in a specific area which needs to be stated. If the value is found the corresponding value or text is returned The syntax for LOOKUP is as follows; =LOOKUP( lookup_value , lookup_vector , result_vector )       In the diagram, column D contains varying salaries, against which there is a company car in column E which corresponds to each salary. For example, a £20030 salary gets a Golf, a £35000 salary gets a Scorpio. A LOOKUP formula can be used to return whatever car is appropriate to a salary figure that is entered. In this case, the lookup_value is the cell where the salary is entered (B13), the lookup_vector is the salary column (D3:D11), and the result_vector is the car column (E3:E11). Hence the formula; =LOOKUP(B13,D3:D11,E3:E11) Typing 40000 in cell B13 will set the lookup_value. LOOKUP will search through the lookup_vector to find the matching salary, and return th...

Excel has a number of logical functions which allows you to set various "conditions" and have data respond to them. For example, you may only want a certain calculation performed or piece of text displayed if certain conditions are met. To start using Functions in Excel you need to Open Excel => Click in a cell => Click on Insert Menu button =>  Function To read about If-Else condition click on Excel Conditional Logic - Part 1 . Here we will be talking about AND, OR and NOT Condition AND CONDITION This function is a logical test to see if all conditions are true. If this is the case, the value "TRUE" is returned. If any of the arguments in the AND statement are found to be false, the whole statement produces the value "FALSE". This function is particularly useful as a check to make sure that all conditions you set are met. Arguments are entered in the AND statement in parentheses, separated by commas, and there is a maximum of 30 a...

  Excel has a number of logical functions which allows you to set various "conditions" and have data respond to them. For example, you may only want a certain calculation performed or piece of text displayed if certain conditions are met. To start using Functions in Excel you need to Open Excel => Click in a cell => Click on Insert Menu button =>  Function    Lets consider a simple example. When we are in a restaurant and have X amount of money, we compare that the dish we select costs less than or equal to the amount we have (in this case X amount). Your selection is based on the condition that the price of the dish should be less than X amount. We can do similar calculations in Excel.   IF STATEMENT The IF function is used to analyze data, test whether or not it meets certain conditions and then act upon its decision IF statement is accompanied by three arguments enclosed in one set of parentheses; the condition to be met (logical_tes...