What is use of NOLOCK ?

Use of NOLOCK

• NOLOCK is also known and dirty reading or blind reading in SQL Server.
• User can read uncommitted data without any wait by using NOLOCK.
• NOLOCK ignore the any LOCK given to transaction by any SQL user.

Example : 

Code :

BEGIN TRANSACTION 

UPDATE C 

SET C.ContactName='' 

FROM Customers C 

WHERE C.CustomerId = 2

-------------------------------------------------

select * from Customers WHERE CustomerId = 2

-----------------------------------------------

select * from Customers WITH (NOLOCK) WHERE CustomerId = 2

Cursor

Cursor

• When user executes any query, block of memory will be provided in database.
• This block of memory is provided for its internal processing and its private to database engine.
• It is called cursor.
• Microsoft SQL Server statements produce a complete result set, but there are times when the results are best processed one row at a time. Opening a cursor on a result set allows processing the result set one row at a time. You can assign a cursor to a variable or parameter with a cursor data type
• When user defines a block of memory to execute a query through programming it is said to be explicit cursor.
• To define explicit cursor, user have to follow below steps.
1. Declare cursor
2. Open cursor
3. Fatch cursor
4. Close Cursor
5. Deallocate Cursor

Example

DECLARE @fname varchar(50)
DECLARE cur_emp CURSOR
static FOR
SELECT fname from emp
OPEN cur_emp
IF @@CURSOR_ROWS > 0
BEGIN
FETCH NEXT FROM cur_emp INTO @fname
WHILE @@Fetch_status = 0
BEGIN
PRINT ' Name : '+@fname
FETCH NEXT FROM cur_emp INTO @fname
END
END
CLOSE cur_emp
DEALLOCATE cur_emp

Types of Cursors

• Static Cursors - A static cursor populates the result set at the time of cursor creation and query result is cached for the lifetime of the cursor.
• Dynamic Cursors - A dynamic cursor allows you to see the data updation, deletion and insertion in the data source while the cursor is open. Hence a dynamic cursor is sensitive to any changes to the data source and supports update, delete operations.

sql join

Joins

To get output from multiple tables we can use joins.
Using join, we can manipulate on two tables using single query.
Join can be applied if both tables contains same data type and width for column.

There are three types of joins

1. Inner
2. Outer (left, right)
3. Cross

Inner join

It is also called equi join.
It compares two columns from two tables using = operator.

Syntax:
select <columnName1>,<columnName2> from
<tableName1>Inner join<tableName2> on
<tableName1>.<columnName1> = <tableName2.>.<columnName2>;

Example:
select member_master.Name, member_master.Age, employee_master.salary
from member_master
inner join employee_master on
member_master.ID = employee_master.ID;

Outer Join

Outer join can be better illustrated using its types.

1. Left outer join

left outer join will take all the rows from left side table.
If there will be more rows in right side table, it will be truncated and if there will be less rows, null will be added as data.

syntax:
select <columnName1>,<columnName2>
from <tableName1> left join <tableName2> on
<tableName1>.<columnName> = <tableName2>.<columnName>;

2. Right Outer Join

right outer join is like left join but will take all rows from right side table.

Syntax:
select <columnName1>,<columnName2>
from <tableName1>right join <tableName2> on
<tableName1>.<columnName1> = <tableName2>.<columnName2>;

Cross Join

When we apply cross join, it will take first row from left side table and put it with each right side rows.
For example, table1 having 5 rows and table2 having 3rows
then first row from table1 will be placed with 3 rows of table2
and then 2nd row will be placed with 3 rows again and so on.

SQL Server Views with Example

SQL Server Views

Once table is created and data is populated we may need to hide some data from some users.
This leads to data security concept.
For this, we can create views.
View is sub set of database table, it means it is type of table but containing required part of data only.
It is virtual table.

Creating view:

Syntax:
create view <viewName> as select <columnName1>,<columnName2> from <tableName>;
Example:
create view Members as select Name,Age,Gender,Address from Employee_Master;

To select data from view, one can write as,
select <columnName1>,<columnName2> from <viewName>;

To destroy a view, one can write as,
drop view <viewName>;