Enjoy Shortcuts on Windows then check it Out...!

Run Commands:
compmgmt.msc - Computer management
devmgmt.msc - Device manager
diskmgmt.msc - Disk management
dfrg.msc - Disk defrag
eventvwr.msc - Event viewer
fsmgmt.msc - Shared folders
gpedit.msc - Group policies
lusrmgr.msc - Local users and groups
perfmon.msc - Performance monitor
rsop.msc - Resultant set of policies
secpol.msc - Local security settings

services.msc - Various Services
msconfig - System Configuration Utility
regedit - Registry Editor
msinfo32 - System Information
sysedit - System Edit
win.ini - windows loading information(also system.ini)
winver - Shows current version of windows
mailto: - Opens default email client
command - Opens command prompt
Run Commands to access the control panel:
Add/Remove Programs control appwiz.cpl
Date/Time Properties control timedate.cpl
Display Properties control desk.cpl
FindFast control findfast.cpl
Internet Properties control inetcpl.cpl
Keyboard Properties control main.cpl keyboard
Mouse Properties control main.cpl
Multimedia Properties control mmsys.cpl
Network Properties control netcpl.cpl
Password Properties control password.cpl
Printers Folder control printers
Sound Properties control mmsys.cpl sounds
System Properties control sysdm.cpl
Command Prompt:
ANSI.SYS Defines functions that change display graphics, control cursor movement, and reassign keys.
APPEND Causes MS-DOS to look in other directories when editing a file or running a command.
ARP Displays, adds, and removes arp information from network devices.
ASSIGN Assign a drive letter to an alternate letter.
ASSOC View the file associations.
AT Schedule a time to execute commands or programs.
ATMADM Lists connections and addresses seen by Windows ATM call manager.
ATTRIB Display and change file attributes.
BATCH Recovery console command that executes a series of commands in a file.
BOOTCFG Recovery console command that allows a user to view, modify, and rebuild the boot.ini
BREAK Enable / disable CTRL + C feature.
CACLS View and modify file ACL’s.
CALL Calls a batch file from another batch file.
CD Changes directories.
CHCP Supplement the International keyboard and character set information.
CHDIR Changes directories.
CHKDSK Check the hard disk drive running FAT for errors.
CHKNTFS Check the hard disk drive running NTFS for errors.
CHOICE Specify a listing of multiple options within a batch file.
CLS Clears the screen.
CMD Opens the command interpreter.
COLOR Easily change the foreground and background color of the MS-DOS window.
COMP Compares files.
COMPACT Compresses and uncompress files.
CONTROL Open control panel icons from the MS-DOS prompt.
CONVERT Convert FAT to NTFS.
COPY Copy one or more files to an alternate location.
CTTY Change the computers input/output devices.
DATE View or change the systems date.
DEBUG Debug utility to create assembly programs to modify hardware settings.
DEFRAG Re-arrange the hard disk drive to help with loading programs.
DEL Deletes one or more files.
DELETE Recovery console command that deletes a file.
DELTREE Deletes one or more files and/or directories.
DIR List the contents of one or more directory.
DISABLE Recovery console command that disables Windows system services or drivers.
DISKCOMP Compare a disk with another disk.
DISKCOPY Copy the contents of one disk and place them on another disk.
DOSKEY Command to view and execute commands that have been run in the past.
DOSSHELL A GUI to help with early MS-DOS users.
DRIVPARM Enables overwrite of original device drivers.
ECHO Displays messages and enables and disables echo.
EDIT View and edit files.
EDLIN View and edit files.
EMM386 Load extended Memory Manager.
ENABLE Recovery console command to enable a disable service or driver.
ENDLOCAL Stops the localization of the environment changes enabled by the setlocal command.
ERASE Erase files from computer.
EXIT Exit from the command interpreter.
EXPAND Expand a Microsoft Windows file back to it’s original format.
EXTRACT Extract files from the Microsoft Windows cabinets.
FASTHELP Displays a listing of MS-DOS commands and information about them.
FC Compare files.
FDISK Utility used to create partitions on the hard disk drive.
FIND Search for text within a file.
FINDSTR Searches for a string of text within a file.
FIXBOOT Writes a new boot sector.
FIXMBR Writes a new boot record to a disk drive.
FOR Boolean used in batch files.
FORMAT Command to erase and prepare a disk drive.
FTP Command to connect and operate on a FTP server.
FTYPE Displays or modifies file types used in file extension associations.
GOTO Moves a batch file to a specific label or location.
GRAFTABL Show extended characters in graphics mode.
HELP Display a listing of commands and brief explanation.
IF Allows for batch files to perform conditional processing.
IFSHLP.SYS 32-bit file manager.
IPCONFIG Network command to view network adapter settings and assigned values.
KEYB Change layout of keyboard.
LABEL Change the label of a disk drive.
LH Load a device driver in to high memory.
LISTSVC Recovery console command that displays the services and drivers.
LOADFIX Load a program above the first 64k.
LOADHIGH Load a device driver in to high memory.
LOCK Lock the hard disk drive.
LOGON Recovery console command to list installations and enable administrator login.
MAP Displays the device name of a drive.
MD Command to create a new directory.
MEM Display memory on system.
MKDIR Command to create a new directory.
MODE Modify the port or display settings.
MORE Display one page at a time.
MOVE Move one or more files from one directory to another directory.
MSAV Early M*cros*ft Virus scanner.
MSD Diagnostics utility.
MSCDEX Utility used to load and provide access to the CD-ROM.
NBTSTAT Displays protocol statistics and current TCP/IP connections using NBT
NET Update, fix, or view the network or network settings
NETSH Configure dynamic and static network information from MS-DOS.
NETSTAT Display the TCP/IP network protocol statistics and information.
NLSFUNC Load country specific information.
NSLOOKUP Look up an IP address of a domain or host on a network.
PATH View and modify the computers path location.
PATHPING View and locate locations of network latency.
PAUSE Command used in batch files to stop the processing of a command.
PING Test / send information to another network computer or network device.
POPD Changes to the directory or network path stored by the pushd command.
POWER Conserve power with computer portables.
PRINT Prints data to a printer port.
PROMPT View and change the MS-DOS prompt.
PUSHD Stores a directory or network path in memory so it can be returned to at any time.
QBASIC Open the QBasic.
RD Removes an empty directory.
REN Renames a file or directory.
RENAME Renames a file or directory.
RMDIR Removes an empty directory.
ROUTE View and configure windows network route tables.
RUNAS Enables a user to execute a program on another computer.
SCANDISK Run the scandisk utility.
SCANREG Scan registry and recover registry from errors.
SET Change one variable or string to another.
SETLOCAL Enables local environments to be changed without affecting anything else.
SETVER Change MS-DOS version to trick older MS-DOS programs.
SHARE Installs support for file sharing and locking capabilities.
SHIFT Changes the position of replaceable parameters in a batch program.
SHUTDOWN Shutdown the computer from the MS-DOS prompt.
SMARTDRV Create a disk cache in conventional memory or extended memory.
SORT Sorts the input and displays the output to the screen.
START Start a separate window in Windows from the MS-DOS prompt.
SUBST Substitute a folder on your computer for another drive letter.
SWITCHES Remove add functions from MS-DOS.
SYS Transfer system files to disk drive.
TELNET Telnet to another computer / device from the prompt.
TIME View or modify the system time.
TITLE Change the title of their MS-DOS window.
TRACERT Visually view a network packets route across a network.
TREE View a visual tree of the hard disk drive.
TYPE Display the contents of a file.
UNDELETE Undelete a file that has been deleted.
UNFORMAT Unformat a hard disk drive.
UNLOCK Unlock a disk drive.
VER Display the version information.
VERIFY Enables or disables the feature to determine if files have been written properly.
VOL Displays the volume information about the designated drive.
XCOPY Copy multiple files, directories, and/or drives from one location to another.
TRUENAME When placed before a file, will display the whole directory in which it exists
TASKKILL It allows you to kill those unneeded or locked up applications
Windows XP Shortcuts:
ALT+- (ALT+hyphen) Displays the Multiple Document Interface (MDI) child window’s System menu
ALT+ENTER View properties for the selected item
ALT+ESC Cycle through items in the order they were opened
ALT+F4 Close the active item, or quit the active program
ALT+SPACEBAR Display the System menu for the active window
ALT+TAB Switch between open items
ALT+Underlined letter Display the corresponding menu
BACKSPACE View the folder one level up in My Computer or Windows Explorer
CTRL+A Select all
CTRL+B Bold
CTRL+C Copy
CTRL+I Italics
CTRL+O Open an item
CTRL+U Underline
CTRL+V Paste
CTRL+X Cut
CTRL+Z Undo
CTRL+F4 Close the active document
CTRL while dragging Copy selected item
CTRL+SHIFT while dragging Create shortcut to selected iteM
CTRL+RIGHT ARROW Move the insertion point to the beginning of the next word
CTRL+LEFT ARROW Move the insertion point to the beginning of the previous word
CTRL+DOWN ARROW Move the insertion point to the beginning of the next paragraph
CTRL+UP ARROW Move the insertion point to the beginning of the previous paragraph
SHIFT+DELETE Delete selected item permanently without placing the item in the Recycle Bin
ESC Cancel the current task
F1 Displays Help
F2 Rename selected item
F3 Search for a file or folder
F4 Display the Address bar list in My Computer or Windows Explorer
F5 Refresh the active window
F6 Cycle through screen elements in a window or on the desktop
F10 Activate the menu bar in the active program
SHIFT+F10 Display the shortcut menu for the selected item
CTRL+ESC Display the Start menu
SHIFT+CTRL+ESC Launches Task Manager
SHIFT when you insert a CD Prevent the CD from automatically playing
WIN Display or hide the Start menu
WIN+BREAK Display the System Properties dialog box
WIN+D Minimizes all Windows and shows the Desktop
WIN+E Open Windows Explorer
WIN+F Search for a file or folder
WIN+F+CTRL Search for computers
WIN+L Locks the desktop
WIN+M Minimize or restore all windows
WIN+R Open the Run dialog box
WIN+TAB Switch between open items
Windows Explorer Shortcuts:
ALT+SPACEBAR - Display the current window’s system menu
SHIFT+F10 - Display the item’s context menu
CTRL+ESC - Display the Start menu
ALT+TAB - Switch to the window you last used
ALT+F4 - Close the current window or quit
CTRL+A - Select all items
CTRL+X - Cut selected item(s)
CTRL+C - Copy selected item(s)
CTRL+V - Paste item(s)
CTRL+Z - Undo last action
CTRL+(+) - Automatically resize the columns in the right hand pane
TAB - Move forward through options
ALT+RIGHT ARROW - Move forward to a previous view
ALT+LEFT ARROW - Move backward to a previous view
SHIFT+DELETE - Delete an item immediately
BACKSPACE - View the folder one level up
ALT+ENTER - View an item’s properties
F10 - Activate the menu bar in programs
F6 - Switch between left and right panes
F5 - Refresh window contents
F3 - Display Find application
F2 - Rename selected item
Internet Explorer Shortcuts:
CTRL+A - Select all items on the current page
CTRL+D - Add the current page to your Favorites
CTRL+E - Open the Search bar
CTRL+F - Find on this page
CTRL+H - Open the History bar
CTRL+I - Open the Favorites bar
CTRL+N - Open a new window
CTRL+O - Go to a new location
CTRL+P - Print the current page or active frame
CTRL+S - Save the current page
CTRL+W - Close current browser window
CTRL+ENTER - Adds the http://www. (url) .com
SHIFT+CLICK - Open link in new window
BACKSPACE - Go to the previous page
ALT+HOME - Go to your Home page
HOME - Move to the beginning of a document
TAB - Move forward through items on a page
END - Move to the end of a document
ESC - Stop downloading a page
F11 - Toggle full-screen view
F5 - Refresh the current page
F4 - Display list of typed addresses
F6 - Change Address bar and page focus
ALT+RIGHT ARROW - Go to the next page
SHIFT+CTRL+TAB - Move back between frames
SHIFT+F10 - Display a shortcut menu for a link
SHIFT+TAB - Move back through the items on a page
CTRL+TAB - Move forward between frames
CTRL+C - Copy selected items to the clipboard
CTRL+V - Insert contents of the clipboard
ENTER - Activate a selected link
HOME - Move to the beginning of a document
END - Move to the end of a document
F1 - Display Internet Explorer Help

Copying Data From one table of One Server to another table of second table

Steps

Go To sql server management studio>>select ur Local server >>Expand It>>Then Expand Server Objects section>>Linked Server

>>Right Click On it>>New Linked Server>>

A new Window Will Open

Then in The Right Pane in front of Linked server Label write ur server Name >> Select Server Type(Sql Server As i think for ur case)

Then From the left Pane select Security

On the right side u will see a window which will having following fields Local Login,Impersonate,Remote User,Remote Password,

Click Add and fill following

Local Login:-login name for ur local server

Remote User -:login id of other server

Remote Password :-password for that server.

Then Press Ok.

By Now U Have Created a linked server.

Now How To Access it this statement will help u.

select * from [SERVERNAME].[DATABASENAME].[SCHEMA].[TABLENAME]

SELECT INTO [SERVERNAME].[DATABASENAME].[SCHEMA].[TABLENAME]

Code for Dynamically set the database(Connection String) for the project

Code for Dynamically set the database for the project
a)In button click event you can set the databse using database properties.
b)In another button click you can access from configuration settings for SQLdataadapter

sample code in c#.net shown below:

private void button1_Click(object sender, EventArgs e)
{
Microsoft.Data.ConnectionUI.DataConnectionDialog dcd=new Microsoft.Data.ConnectionUI.DataConnectionDialog();
Microsoft.Data.ConnectionUI.DataSource.AddStandardDataSources(dcd);
if (Microsoft.Data.ConnectionUI.DataConnectionDialog.Show(dcd) == DialogResult.OK)
{
textBox1.Text = dcd.ConnectionString;
ConnectionStringsSection Section=new ConnectionStringsSection();
ConnectionStringSettings Setting;
Configuration config = ConfigurationManager.OpenExeConfiguration(ConfigurationUserLevel.None);

Section = (ConnectionStringsSection)(config.GetSection("connectionStrings"));
if (Section.ConnectionStrings["ConString"]==null)
{

Section.ConnectionStrings.Add(new ConnectionStringSettings("ConString", dcd.ConnectionString));

}
Setting = Section.ConnectionStrings["ConString"];
Setting.ConnectionString = dcd.ConnectionString;
ConfigurationManager.RefreshSection("connectionStrings");

config.Save(ConfigurationSaveMode.Modified, true);
ConfigurationManager.RefreshSection("connectionStrings");


}
else
{
textBox1.Text = "";
}
}

private void button2_Click(object sender, EventArgs e)
{

System.Data.SqlClient.SqlDataAdapter da = new System.Data.SqlClient.SqlDataAdapter("select *from Testing",System.Configuration.ConfigurationManager.ConnectionStrings["ConString"].ConnectionString );
da.Fill(ds);
dataGridView1.DataSource = ds.Tables[0];

}

Code for How to Add Combobox Columns to datagridview in VB.Net

'Get a reference to the default DataView of the table
Dim dv As DataView = dt.DefaultView


'Create 2 combo box columns: Set DisplayMember, ValueMember and DataSource
Dim colQty1 As New DataGridViewComboBoxColumn

()
colQty1.DisplayMember = "Quantity1"
colQty1.ValueMember = "Quantity1"
colQty1.HeaderText = "Quantity 1"
colQty1.DataSource = dv

Dim colQty2 As New DataGridViewComboBoxColumn()
colQty2.DisplayMember = "Quantity2"
colQty2.ValueMember = "Quantity2"
colQty2.HeaderText = "Quantity 2"
colQty2.DataSource = dv

'Add both columns to it
dgv.Columns.AddRange(New DataGridViewColumn() {colQty1, colQty2})

SQL Server DO's and DON'Ts

SQL Server DO's and DON'Ts

So, you are now the leader of a SQL Server based project and this is your first one, perhaps migrating from Access. Or maybe you have performance problems with your SQL Server and don't know what to do next. Or maybe you simply want to know of some design guidelines for solutions using SQL Server and designing Database Access Layers (DAL): this article is for you.

Even if you are not using SQL Server, most of these design guidelines apply to other DBMS, too: Sybase is a very similar environment for the programmer, and Oracle designs may benefit from this too. I won't show here how to use specific T-SQL tricks, nor won't give you miracle solutions for your SQL Server problem. This is by no means a complete, closed issue. What I intend to do is give you some advices for a sound design, with lessons learned through the last years of my life, seeing the same design errors being done again and again.

DO know your tools.

Please, don't underestimate this tip. This is the best of all of those you'll see in this article. You'd be surprised of how many SQL Server programmers don't even know all T-SQL commands and all of those effective tools SQL Server has.

"What? I need to spend a month learning all those SQL commands I'll never use???" you might say. No, you don't need to. But spend a weekend at MSDN and browse through all T-SQL commands: the mission here is to learn a lot of what can and what can't be done. And, in the future, when designing a query, you'll remember "Hey, there's this command that does exactly what I need", and then you'll refer again to MSDN to see its exact syntax.

In this article I'll assume that you already know the T-SQL syntax or can find about it on MSDN.

DON'T use cursors

Let me say it again: DON'T use cursors. They should be your preferred way of killing the performance of an entire system. Most beginners use cursors and don't realize the performance hit they have. They use memory; they lock tables in weird ways, and they are slow. Worst of all, they defeat most of the performance optimization your DBA can do. Did you know that every FETCH being executed has about the same performance of executing a SELECT? This means that if your cursor has 10,000 records, it will execute about 10,000 SELECTs! If you can do this in a couple of SELECT, UPDATE or DELETE, it will be much faster.

Beginner SQL programmers find in cursors a comfortable and familiar way of coding. Well, unfortunately this lead to bad performance. The whole purpose of SQL is specifying what you want, not how it should be done.

I've once rewritten a cursor-based stored procedure and substituted some code for a pair of traditional SQL queries. The table had only 100,000 records and the stored procedure used to take 40 minutes to process. You should see the face of the poor programmer when the new stored procedure took 10 seconds to run!

Sometimes it's even faster to create a small application that gets all the data, proccess it and update the server. T-SQL was not done with loop performance in mind.

If you are reading this article, I need to mention: there is no good use for cursors; I have never seen cursors being well used, except for DBA work. And good DBAs, most of the time, know what they are doing. But, if you are reading this, you are not a DBA, right?

DO normalize your tables

There are two common excuses for not normalizing databases: performance and pure laziness. You'll pay for the second one sooner or later; and, about performance, don't optimize what's not slow. Often I see programmers de-normalizing databases because "this will be slow". And, more frequent than the inverse, the resulting design is slower. DBMSs were designed to be used with normalized databases, so design with normalization in mind.

DON'T SELECT *

This is hard to get used, I know. And I confess: often I use it; but try to specify only the columns you'll need. This will:

1. Reduce memory consumption and network bandwidth
2. Ease security design
3. Gives the query optimizer a chance to read all the needed columns from the indexes

DO know how your data will be/is being acessed

A robust index design is one of the good things you can do for your database. And doing this is almost an art form. Everytime you add an index to a table, things get faster on SELECT, but INSERT and DELETE will be much slower. There's a lot of work in building and mantaining indexes. If you add several indexes to a table to speed your SELECT, you'll soon notice locks being held for a long time while updating indexes. So, the question is: what is being done with this table? Reading or Updating data? This question is tricky, specially with the DELETE and UPDATE, because they often involve a SELECT for the WHERE part and after this they update the table.

DON'T create an index on the "Sex" column

This is useless. First, let's understand how indexes speed up table access. You can see indexes as a way of quickly partitioning a table based on a criteria. If you create an index with a column like "Sex", you'll have only two partitions: Male and Female. What optimization will you have on a table with 1,000,000 rows? Remember, mantaining an index is slow. Always design your indexes with the most sparse columns first and the least sparse columns last, e.g, Name + Province + Sex.

DO use transactions

Specially on long-running queries. This will save you when things get wrong. Working with data for some time you'll soon discover some unexpected situation which will make your stored procured crash.

DO beware of deadlocks

Always access your tables on the same order. When working with stored procedures and transactions, you may find this soon. If you lock the table A then table B, always lock them in this very same order in all stored procedures. If you, by accident, lock the table B and then table A in another procedure some day you'll have a deadlock. Deadlocks can be tricky to find if the lock sequence is not carefully designed.

DON'T open large recordsets

A common request on programming forums is: "How can I quickly fill this combo with 100,00 items?". Well, this is an error. You can't and you shouldn't. First, your user will hate browsing through 100,000 records to find the right one. A better UI is needed here, because you should ideally show no more that 100 or 200 records to your users.

DON'T use server side cursors

Unless you know what your are doing. Client side cursors often (not always) put less overhead on the network and on the server, and reduce locking time.

DO use parametrized queries

Sometimes I see in programming forums, questions like: "My queries are failing with some chars, e.g. quotes. How can I avoid it?". And a common answer is: "Replace it by double quotes". Wrong. This is only a workaround and will still fail with other chars, and will introduce serious security bugs. Besides this, it will trash the SQL Server caching system, which will cache several similar queries, instead of caching only one. Learn how to use parameterized queries (in ADO, through the use of the Command Object, or in ADO.NET the SqlCommand) and never have these problems again.

DO always test with large databases

It's a common pattern programmers developing with a small test database, and the end user using large databases. This is an error: disk is cheap, and performance problems will only be noticed when it's too late.

DON'T import bulk data with INSERT

Unless strictly necessary. Use DTS or the BCP utility and you'll have both a flexible and fast solution.

DO beware of timeouts

When querying a database, the default timeout is often low, like 15 seconds or 30 seconds. Remember that report queries may run longer than this, specially when your database grows.

DON'T ignore simultaneous editing

Sometimes two users will edit the same record at the same time. When writing, the last writer wins and some of the updates will be lost. It's easy to detect this situation: create a timestamp column and check it before you write. If possible, merge changes. If there is a conflict, prompt the user for some action.

DON'T do SELECT max(ID) from Master when inserting in a Detail table.

This is another common mistake, and will fail when two users are inserting data at the same time. Use one of SCOPE_IDENTITY, IDENT_CURRENT, and @@IDENTITY. Avoid @@IDENTITY if possible because it can introduce some nasty bugs with triggers.

DO Avoid NULLable columns

When possible. They consume an extra byte on each NULLable column in each row and have more overhead associated when querying data. The DAL will be harder to code, too, because everytime you access this column you'll need to check

I'm not saying that NULLs are the evil incarnation, like some people say. I believe they can have good uses and simplify coding when "missing data" is part of your business rules. But sometimes NULLable columns are used in situations like this:

CustomerName1
CustomerAddress1
CustomerEmail1
CustomerName2
CustomerAddress2
CustomerEmail3
CustomerName1
CustomerAddress2
CustomerEmail3

This is horrible. Please, don't do this, normalize your table. It will be more flexible and faster, and will reduce the NULLable columns.

DON'T use the TEXT datatype

Unless you are using it for really large data. The TEXT datatype is not flexible to query, is slow and wastes a lot of space if used incorrectly. Sometimes a VARCHAR will handle your data better.

DON'T use temporary tables

Unless strictly necessary. Often a subquery can substitute a temporary table. They induce overhead and will give you a big headache when programming under COM+ because it uses a database connection pool and temporary tables will last forever. In SQL Server 2000, there are alternatives like the TABLE data type which can provide in-memory solutions for small tables inside stored procedures too.

DO learn how to read a query execution plan

The SQL Server query analyzer is your friend, and you'll learn a lot of how it works and how the query and index design can affect performance through it.

DO use referential integrity

This can be a great time saver. Define all your keys, unique constraints and foreign keys. Every validation you create on the server will save you time in the future.

Tips for writing efficient SQL Queries

	If your query will return more than 20 percent of the rows in the table, use a full-table scan rather than an index scan (Rules 20-80-20)
2	Keep statistics up to date
3	Use the SHARED_CURSOR parameter
4	Use different tablespaces for tables and indexes
5	Use table partitioning (and local indexes) when appropriate (partitioning is an extra cost feature)
6	Use SQL standards and conventions to reduce parsing
7	Avoid joining too many tables
8	Use hints as appropriate
9	Avoid full table scans on large tables
10	Monitor index browning (due to deletions; rebuild as necessary)

1. Network traffic is reduced. This can have a significant impact on performance if the table has a large number of columns, or the table has a long or long raw column (both of which can be upto 2 GB in length). These types of columns will take a long time to transfer over the network and so they should not be fetched from the database unless they are specifically required.

1. The code is easier to understand.

1. It could save the need for changes in the future. If any columns is added to or removed from the base table/view, then “select * “statement can produce wrong results set and statement may fail.

• Use table alias: Always use table alias and prefix all column names with the aliases when you
• are using more than one table.

• Never compare NULL to anything else: All expressions return NULL if one of the operands is NULL. This is applicable for all operators except Concatenation operator (||).

• Use Bind Variables: It is also better to use bind variables in queries. That way the query becomes generic and therefore re-usable. For example, instead of writing query like –

o SELECT ename, sal FROM emp WHERE deptno = 20;

o Change it to –

o SELECT ename, sal FROM emp WHERE deptno = :deptno;

o The first query can be re-used for deptno number 20 only, whereas the second query can be reused for any other deptno also.

• SQL Writing Convention: It is a good practice to use a standard syntax for wiring SQL queries. We will recommend following standards to use while writing SQL queries. Write all standard SQL TEXT in upper case:

For example:

SELECT ename, sal FROM emp WHERE deptno = 20;

Write all non standard SQL TEXT (Table name, Column name etc) in lower case:

For example:

SELECT ename, sal FROM emp WHERE deptno = 20;

Note:-

It is important to write similar SQL statement in same case.

For example: Oracle will reparse following queries as they are not written in the same case

Select * from EMP;

Select * from emp;

• Use EXISTS instead of DISTINCT: Use EXISTS in place of DISTINCT if you want the result set to contain distinct values while joining tables.

For example:

SELECT DISTINCT d.deptno, d.dname FROM dept d, emp e

WHERE d.deptno = e.deptno;

The following SQL statement is a better alternative.

SELECT d.deptno, d.dname FROM dept d WHERE EXISTS

(SELECT e.deptno FROM emp e WHERE d.deptno = e.deptno);

• Use of expressions and indexes: The optimizer fully evaluates expressions whenever possible and translates certain syntactic constructs into equivalent constructs. This is done either because Oracle can more quickly evaluate the resulting expression than the original

The original expression is merely a syntactic equivalent of the resulting expression. Any computation of constants is performed only once when the statement is optimized rather than each time the statement is executed. Consider these conditions that test for salaries greater than $2000.

sal > 24000/12

sal > 2000

sal*12 > 24000

If a SQL statement contains the first condition, the optimizer simplifies it into the second condition. Please note that optimizer does not simplify expressions across comparison operators. The optimizer does not simplify the third expression into the second. For this reason, we should write conditions that compare columns with constants whenever possible, rather than conditions with expressions involving columns.

The Optimizer does not use index for the following statement:

SELECT * FROM emp WHERE sal*12 > 24000 ;

Instead of this use the following statement:

SELECT * FROM emp WHERE sal > 24000/12 ;

• Use of NOT operator on indexed columns: Never use NOT operator on an indexed column.Whenever Oracle encounters a NOT on an index column, it will perform full-table scan.

For Example:

SELECT * FROM emp WHERE NOT deptno = 0;

Instead use the following:

SELECT * FROM emp WHERE deptno > 0;

• Function or Calculation on indexed columns: Never use a function or calculation on an indexed column. If there is any function is used on an index column, optimizer will not use index.

For Example: Do not use until need exactly match string:

SELECT * FROM emp WHERE SUBSTR (ename, 1, 3) = 'MIL';

Use following instead:

SELECT * FROM emp WHERE ename LIKE 'MIL%';

Do not use the following as || is the concatenate function. Like other functions and it disables index.

SELECT * FROM emp WHERE ename || job = 'MILLERCLERK'