JOINS

What are joins?Sometimes we have to select data from two or more tables to make our result complete. We have to perform a join.

How many types of Joins?Joins can be categorized as:

Inner joins (the typical join operation, which uses some comparison operator like = or <>). These include equi-joins and natural joins.
Inner joins use a comparison operator to match rows from two tables based on the values in common columns from each table. For example, retrieving all rows where the student identification number is the same in both the students and courses tables.

Outer joins. Outer joins can be a left, a right, or full outer join.
Outer joins are specified with one of the following sets of keywords when they are specified in the FROM clause:

DATA TYPES

What are the data types in SQL

bigint	Binary	bit	char	Cursor
datetime	Decimal	float	image	Int
money	Nchar	ntext	nvarchar	Real
smalldatetime	Smallint	smallmoney	text	Timestamp
tinyint	Varbinary	Varchar	uniqueidentifier

INDEX

INDEX

What is Index? It’s purpose?
Indexes in databases are similar to indexes in books. In a database, an index allows the database program to find data in a table without scanning the entire table. An index in a database is a list of values in a table with the storage locations of rows in the table that contain each value. Indexes can be created on either a single column or a combination of columns in a table and are implemented in the form of B-trees. An index contains an entry with one or more columns (the search key) from each row in a table. A B-tree is sorted on the search key, and can be searched efficiently on any leading subset of the search key. For example, an index on columns A, B, C can be searched efficiently on A, on A, B, and A, B, C.

Index Optimization tips

T-SQL Queries

2 tables

Employee	Phone
empid empname salary mgrid	empid phnumber

Select all employees who doesn't have phone?
SELECT empname
FROM Employee
WHERE (empid NOT IN
(SELECT DISTINCT empid
FROM phone))

Transact-SQL Optimization Tips

Use views and stored procedures instead of heavy-duty queries.
This can reduce network traffic, because your client will send to server only stored procedure or view name (perhaps with some parameters) instead of large heavy-duty queries text. This can be used to facilitate permission management also, because you can restrict user access to table columns they should not see.