Rebuild vs Reorganize

Rebuild:

An index ‘rebuild’ creates a fresh, sparkling new structure for the index. If the index is disabled, rebuilding brings it back to life. You can apply a new fillfactor when you rebuild an index. If you cancel a rebuild operation midway, it must roll back (and if it’s being done offline, that can take a while).

Reorganize:

This option is more lightweight. It runs through the leaf level of the index, and as it goes it fixes physical ordering of pages and also compacts pages to apply any previously set fillfactor settings. This operation is always online, and if you cancel it then it’s able to just stop where it is (it doesn’t have a giant operation to rollback).

SQL CAST

The CAST () function in the SQL language is a cast function that converts data from one type to another. For example it is possible to transform a date in datetime format DATE, or vice versa.

Syntax:

The syntax of the CAST function is:

SELECT CAST (Expression AS Datatype);

In this syntax, "expression" is the value to be transformed, while "Datatype" contains the type of data that have to be obtained. This type of data can be one of the following (depending on the database management system):

Data Type may be different Like:
--------------------------------
BINARY
CHAR
DATE
DATETIME
TIME
INT etc.
--------------------------------

Example:

DECLARE @First varchar(2)
DECLARE @Second varchar(2)
DECLARE @Third varchar(2)
set @First=25
set @Second=15
set @Third=33

Select CAST(@First as int) + CAST(@Second as int) +CAST (@Third as int) as Result

Output:73

Candidate Key Vs Primary Key Vs Alternate key Vs Composite Key Vs Unique Key VsForeign Key

Candidate Key

A Candidate Key is a set of one or more fields/columns that can identify a record uniquely in a table. There can be multiple Candidate Keys in one table. Each Candidate Key can work as Primary Key.
Example: In Student Table RollNo and EnrollNo are Candidate Keys since  these fields can be work as Primary Key.

Primary Key

Primary key is a set of one or more fields/columns of a table that uniquely identify a record in database table. It can not accept null, duplicate values. Only one Candidate Key can be Primary Key.

Alternate key

A Alternate key is a key that can be work as a primary key. Basically it is a candidate key that currently is not primary key.Example: In  RollNo and EnrollNo, RollNo becomes Alternate Keys when we define EnrollNo as Primary Key.

Composite/Compound Key

Composite Key is a combination of more than one fields/columns of a table. It can be a Candidate key, Primary key.

Unique Key

Uniquekey is a set of one or more fields/columns of a table that uniquely identify a record in database table. It is like Primary key but it can accept only one null value and it can not have duplicate values.

Foreign Key

Foreign Key is a field in table that is Primary key in another table. It can accept multiple null, duplicate values. Example : We can have a Emp_Id column in the Employee table which is pointing to Emp_Id column in a department table where it a foreign key.

nth highest salary in sql server

SELECT TOP 1 salary
FROM (
SELECT DISTINCT TOP n salary
FROM employee
ORDER BY salary DESC) a
ORDER BY salary

Difference between Correlated subquery and Nested subquery

Correlated subquery

Correlated subquery runs once for each row selected by the outer query. It contains a reference to a value
 from the row selected by the outer query.

Correlated subquery follows down to top approach i.e., main query is executed first(even though parenthesis      
 are present) and then child query. 

We can also say:In a subquery.

Example:

 select e1.empname, e1.basicsal, e1.deptno from emp e1

 where e1.basicsal = (select max(basicsal) from emp e2 where e2.deptno = e1.deptno)

Nested subquery

Nested subquery runs only once for the entire nesting (outer) query. It does not contain any reference to the
 outer query row.

We can also say: in a Correlated subquery,Inner query condition is used in the outer query.

Nested subquery follows top-down approach i.e., child query is executed first and then parent .

Outer query condition is used in the the inner query.

Example:

 select empname, basicsal, deptno from emp

 where (deptno, basicsal) in (select deptno, max(basicsal) from emp group by deptno)

Difference between Where and Having clauses

The WHERE clause selects rows before grouping. The HAVING clause selects rows after grouping.
The WHERE clause cannot contain aggregate functions. The HAVING clause can contain aggregate functions.

A HAVING clause is like a WHERE clause, but applies only to groups as a whole, whereas the WHERE clause applies to individual rows. A query can contain both a WHERE clause and a HAVING clause. The WHERE clause is applied first to the individual rows in the tables . Only the rows that meet the conditions in the WHERE clause are grouped. The HAVING clause is then applied to the rows in the result set.