Happy Coding ...

Create Database CREATE DATABASE  -----> syntax *****you can use the CREATE statement only to create objects onthe local server CREATE DATABASE [ON [PRIMARY] ([NAME = <’logical file name’>,] FILENAME = <’file name’> [, SIZE = ] [, MAXSIZE = size in kilobytes, megabytes, gigabytes, or terabytes>] [, FILEGROWTH = ])] [LOG ON ([NAME = <’logical file name’>,] FILENAME = <’file name’> [, SIZE = ] [, MAXSIZE = size in kilobytes, megabytes, gigabytes, or terabytes>] [, FILEGROWTH = ])] [ COLLATE ] [ FOR ATTACH [WITH ]| FOR ATTACH_REBUILD_LOG| WITH DB_CHAINING ON|OFF | TRUSTWORTHY ON|OFF] [AS SNAPSHOT OF ] [;] CREATE DATABASE Accounting ON (NAME = ‘Accounting’, FILENAME = ‘c:\Program Files\Microsoft SQL Server\ MSSQL.1\mssql\data\AccountingData.mdf’, SIZE = 10, MAXSIZE = 50, FILEGROWTH = 5) LOG ON (NAME = ‘AccountingLog’, FILENAME = ‘c:\Program Files\Microsoft SQL Server\ MSSQL.1\mssql\data\AccountingLog.ldf’, SIZE = 5...

Converting from bytes to terabytes is very easy considering that terabyte = 1024*Gigabyte              = 1024 * 1024 * Megabyte              = 1024 * 1024 * 1024 KiloByte              = 1024 * 1024 * 1024 * 1024 * Byte Query below gives number of documents in a media and the total size of the documents. See how you have used Alias in the query SELECT        [MEDIA] = X.media_id,         [TOTAL] = COUNT(*),       [Size_KB] = SUM (Size_MB) FROM       (       SELECT              d.edoc_id,              d.media_id ,             SIZE_MB =  (                         CAST(ISNULL(d.Size1,0) AS DECIMAL) +      ...

In SQL we have four different ways to "store" data in a table: local temporary tables (#table_name), global temporary tables (##table_name), permanent tables (table_name), table variables (@table_name).   Local Temporary Tables CREATE TABLE #people ( id INT, name VARCHAR(25) )   A temporary table is created and populated on disk, in the system database tempdb — with a session-specific identifier packed onto the name, to differentiate between similarly-named #temp tables created from other sessions. The data in this #temp table (in fact, the table itself) is visible only to the current scope (usually a stored procedure, or a set of nested stored procedures). The table gets cleared up automatically when the current procedure goes out of scope, but you should manually clean up the data when you're done with it:   DROP TABLE #people   This will be better on resources ("release early") than if you let the system clean up *after* the current session finishes the r...

ASP.NET: Server side technology ( code is interpreted by the web server ) for developing web applications based on Microsoft .Net Framework IDE : Integrated Development Environment Web Client: A program that is used to interact with the web application eg: Internet Explorer, Firefox

OOPs or Object Oriented Programming is an approach that puts object at the center of programming model. An object is a self contained entity that has a state and behaviour. State is described by fields and properties, while behavior is defined by methods and events. Object is an instance of a class.  A class acts like a blueprint for the object . It defines behavior of object of that type.So all objects of type Vehicle will have the same behavior. Vehicle class will have the properties - 'color' , 'weight', 'model', 'make' . Properties are characteristic shared by all objects of a particular class.  Methods are actions of an object - 'start', 'stop' Constructors are special kinds of methods, used to initialize objects. It executes automatically when a class is instantiated Access modifies helps in controlling the visibilty of a class. * Public allows the property or methods to be called outside the class * Private fields and ...

ASP.NET is a server side technology for developing web based applications based on Microsoft .Net  Framework. ASP.NET is run on the web server. When a user click on a page/website link on a web browser, a request is sent to the web server. The web server reads the code and generates a web page . The web page is then sent to the browser where the client reads this page. Since the processing is on the server, it is called 'Server Side technology'. Features of ASP.NET: It lets you write server side code using over 40 languages. Most popular being C# and VB ASP.NET pages are compiled, not interpreted It has full access to the functionality  of .Net Framework... XML, web services, database interaction, emails, regular expression You can separate server side code of your page from HTML layout Easy to reuse common User Interface elements An ASP.NET page generally consists of the following: 1. Directive: Controls how page is compiles , how page is cached by web browser, err...

FxCop (Framework Police) is a free code analysis tool provided by Microsoft basically intended for class library developers. It is a must have tool for new developers who want help in getting familiarized with  Microsoft Design Guidelines. FxCop helps to write robust code and easily maintain code by telling you about the possible design, localization, performance and security improvements. It can prevent flaws in the code and helps the programmer to follow best practices. FxCop is fully integrated in the Visual Studio and is distributed as both a fully featured application that has a graphical user interface (FxCop.exe) for interactive work, and a command-line tool (FxCopCmd.exe) suited for use as part of automated build processes or integrated with Microsoft Visual Studio® .NET as an external tool. It analyses Binary Common Intermediate Language (CIL) instead of the source code, it is not language specific tool and can be used for analysing a dll written in any programming langu...

-- Create table t1 and insert values. CREATE TABLE t1 (a INT NULL) INSERT INTO t1 values (NULL) INSERT INTO t1 values (0) INSERT INTO t1 values (1) GO When SET ANSI_NULLS is ON, all comparisons against a null value evaluate to UNKNOWN.  -- SET ANSI_NULLS to ON and test. SET ANSI_NULLS ON GO DECLARE @varname int =  NULL SELECT * FROM t1  WHERE a = @varname  -- No output SELECT * FROM t1  WHERE a <> @varname -- No output SELECT * FROM t1  WHERE a IS NULL     -- NULL  GO When SET ANSI_NULLS is OFF, comparisons of all data against a null value evaluate to TRUE if the data value is NULL -- SET ANSI_NULLS to OFF and test. SET ANSI_NULLS OFF  GO DECLARE @varname int =  NULL SELECT * FROM t1  WHERE a = @varname  -- NULL SELECT * FROM t1  WHERE a <> @varname -- 0 , 1 SELECT * FROM t1  WHERE a IS NULL     -- NULL  GO -- Drop table t1. DROP TABLE t1

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...

Its extremely easy to create a workbook and place it on a network location where several people can edit the contents simultaneously. For example, if the people in your work group each handle several projects and need to know the status of each other's projects, the group can use a shared workbook to track the status of the projects. All persons involved can then enter the information for their projects in the same workbook. As the owner of the shared workbook, you can manage it by removing users from the shared workbook and resolving conflicting changes. When all changes have been incorporated, you can stop sharing the workbook. Share A Workbook Not all features are supported in a shared workbook. If you want to include any of the following features, you should add them before you save the workbook as a shared workbook: merged conditional formats data validation, charts, pictures, objects including drawing objects, hyperlinks scenarios, outlines subtotals, data tables PivotTa...

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...