Basic SQL Server

-When we combine two queries using EXCEPT clause, it will returns distinct rows from the first SELECT statement that are not returned by the second one.

-EXCEPT clause works the same way as the UNION operator of SQL and MINUS clause in Oracle.

-The syntax of EXCEPT clause is as follow

SELECT column1 [, column2 ]

FROM table1 [, table2 ]

[WHERE condition]

EXCEPT

SELECT column1 [, column2 ]

FROM table1 [, table2 ]

[WHERE condition]

-The difference between EXCEPT and NOT IN clause is EXCEPT operator returns all distinct rows from the rows returned by first select statement which does not exist in the rows returned by second select statement. On the other hand “NOT IN” will return all rows from returned by first select statement which does not exist in the rows returned by second select statement.

RANK is one of the Ranking functions which are used to give rank to each row in the result set of a SELECT statement.

For using this function first specify the function name, followed by the empty parentheses.

Then specify the OVER function. For this function, you have to pass an ORDER BY clause as an argument. The clause specifies the column(s) that you are going to rank.

For Example

SELECT ROW_NUMBER() OVER(ORDER BY Salary DESC) AS [RowNumber], EmpName, Salary, [Month], [Year] FROM EmpSalary

In the result you will see that the highest salary got the first rand and the lowest salary got the last rank. Here the rows with equal salaries will not get same ranks.

-In SQL the Trigger is the procedural code that executed when you INSERT, DELETE or UPDATE data in the table.

-Triggers are useful when you want to perform any automatic actions such as cascading changes through related tables, enforcing column restrictions, comparing the results of data modifications and maintaining the referential integrity of data across a database.

-For example, to prevent the user to delete the any Employee from EmpDetails table, following trigger can be created.

create trigger del_emp

on EmpDetails

for delete

as

begin

rollback transaction

print "You cannot delete any Employee!"

end

-When someone will delete a row from the EmpDetails table, the del_emp trigger cancels the deletion, rolls back the transaction, and prints a message "You cannot delete any Employee!"

-If we want to execute any procedural code automatically on specific time either once or repeatedly then we can create a Scheduled job for that code.

-Following are the steps to create a Scheduled Job.

1. Connect to your database of SQL server in SQL Server Management Studio.

2. On the SQL Server Agent. There you will find a Jobs folder. Right click on jobs and choose Add New.

3. A New Job window will appear. Give a related name for the job.

4. Click next on the "Steps" in the left menu. A SQL job can have multiple steps either in the form of SQL statement or a stored procedure call.

5. Click on the "Schedules" in the left menu. A SQL job can contain one or more schedules. A schedule is basically the time at which sql job will run itself. You can specify recurring schedules also.

-Using scheduled job you can also create alert and notifications.

-OPENXML provides an easy way to use an XML document as a data-source for your procedures.

-OPENXML data can be manipulated the same way we deal with database tables by treating xml tags in the form of columns and the value in the form of rows.

-By using OPENXML Data can be inserted or updated very quickly without multiple trips to the database.

-Example:

DECLARE @count int

DECLARE @xml varchar(5000)

SET @xml ='<Employees>

<Employee id="1">

<Name>DEF</Name>

<Employee >1234</ Employee >

</Employee >

<Employee id="2">

<Name>ABC</Name>

<PhoneNo>2211</PhoneNo>

</Employee >

</Employees>'

EXEC sp_xml_preparedocument @count OUTPUT, @xml

SELECT *

FROM OPENXML (@count, Employees/Employee')

WITH (id Varchar(10), Name varchar(100) 'Name' , PhoneNo Varchar(50) 'PhoneNo')

EXEC sp_xml_removedocument @index

It will give following result.

1 DEF 1234

2 ABC 2211

-RANK is one of the Ranking functions which are used to give rank to each row in the result set of a SELECT statement.

-For using this function first specify the function name, followed by the empty parentheses.

-Then specify the OVER function. For this function, you have to pass an ORDER BY clause as an argument. The clause specifies the column(s) that you are going to rank.

For Example

SELECT RANK() OVER(ORDER BY Salary DESC) AS [RowNumber], EmpName, Salary, [Month], [Year] FROM EmpSalary

-In the result you will see that the highest salary got the first rand and the lowest salary got the last rank.

Here the rows with equal salaries will get same ranks.

-Remember that the rank depends on the row's position in the result set, not on the sequential number of the row.

-When we execute any SQL operations, SQL Server opens a work area in memory which is called Cursor.

-When it is required to perform the row by row operations which are not possible with the set-based operations then Cursor is used.

-There are two of cursors

1. Implicate Cursor

SQL Server automatically manages cursors for all data manipulation statements. These cursors are called implicit cursors.

2. Explicit Cursor

When the programmer wants to perform the row by row operations for the result set containing more than one row, then he explicitly declare a cursor with a name.

They are managed by OPEN, FETCH and CLOSE.

%FOUND, %NOFOUND, %ROWCOUNT and %ISOPEN attributes are used in both types of cursors.

-SQL has introduced Log Shipping feature to synchronize the Distributed Database Server. Synchronize the database by copying Transaction logs, Backing up, Restoring data. SQL Server Job Agents is used to make these processes automatic.

-In the case of failure the Log Shipping will not transfer the server. That means it will not redirect your application to other server. This has to be done manually.

-Log shipping synchronizes the database only. The main functions of Log Shipping are as follows:

1. To Back up the transaction log of the primary database

2. To Copy the transaction log backup to every secondary server

3. To Restore the transaction log backup on the secondary database