What is a Cursor ?



A cursor is a pointer to this context area. PL/SQL controls the context area through a cursor. A cursor holds the rows (one or more) returned by a SQL statement. The set of rows the cursor holds is referred to as the active set . 

                                                             Or

 Cursor is a database objects to retrieve data from a result set one row at a time, instead of the T-SQL commands that operate on all the rows in the result set at one time. We use cursor when we need to update records in a database table in singleton fashion means row by row 

We have basically two type of cursor in SQL
·        Implicit cursors
These are created by default when DML statements like, INSERT, UPDATE, and DELETE statements are executed. They are also created when a SELECT statement that returns just one row is executed.
·        Explicit cursors
They must be created when you are executing a SELECT statement that returns more than one row. Even though the cursor stores multiple records, only one record can be processed at a time, which is called as current row. When you fetch a row the current row position moves to next row.
Both implicit and explicit cursors have the same functionality, but they differ in the way they are accessed.

Life Cycle/Syntax of Cursor

Life Cycle/Syntax of Cursor

1.            Declare Cursor

A cursor is declared by defining the SQL statement that returns a result set.
  DECLARE cursor_name CURSOR
 [LOCAL | GLOBAL] --define cursor scope
 [FORWARD_ONLY | SCROLL] --define cursor movements (forward/backward)
 [STATIC | KEYSET | DYNAMIC | FAST_FORWARD] --basic type of cursor
 [READ_ONLY | SCROLL_LOCKS | OPTIMISTIC] --define locks
 FOR select_statement --define SQL Select statement
 FOR UPDATE [col1,col2,...coln] --define columns that need to be updated

2.            Open

A Cursor is opened and populated by executing the SQL statement defined by the cursor.
OPEN [GLOBAL] cursor_name --by default it is local

3.            Fetch

When cursor is opened, rows can be fetched from the cursor one by one or in a block to do data manipulation.
FETCH [NEXT|PRIOR|FIRST|LAST|ABSOLUTE n|RELATIVE n]
FROM [GLOBAL] cursor_name
INTO @Variable_name[a,b,c.....n]

4.            Close

After data manipulation, we should close the cursor explicitly.
CLOSE cursor_name
We car reopen a cursor after closing it.

5.            Deallocate

Finally, we need to delete the cursor definition and released all the system resources associated with the cursor.
DEALLOCATE cursor_name

Example:

SET NOCOUNT ON
DECLARE @Id int
DECLARE @name varchar(50)
DECLARE @salary int
DECLARE cur_emp CURSOR
STATIC FOR
     SELECT EmpID,EmpName,Salary from Employee
OPEN cur_emp
IF @@CURSOR_ROWS > 0
     BEGIN
           FETCH NEXT FROM cur_emp INTO @Id,@name,@salary
           WHILE @@Fetch_status = 0
           BEGIN
PRINT 'ID : '+ convert(varchar(20),@Id)+', Name : '+@name+ ', Salary : '+convert(varchar(20),@salary)
                FETCH NEXT FROM cur_emp INTO @Id,@name,@salary
           END
END
CLOSE cur_emp
DEALLOCATE cur_emp
SET NOCOUNT OFF

Statements operations supported by Cursor are
  •           CLOSE      
  •          CREATE PROCEDURE      
  •          DEALLOCATE      
  •          DECLARE CURSOR      
  •          DECLARE @local_variable      
  •          DELETE      
  •          FETCH      
  •          OPEN      
  •          UPDATE      
  •          SET      


System functions and system stored procedures also support cursors
  •          @@CURSOR_ROWS      
  •          CURSOR_STATUS      
  •          @@FETCH_STATUS      
  •          sp_cursor_list      
  •          sp_describe_cursor      
  •          sp_describe_cursor_columns      
  •       sp_describe_cursor_tables


at

Common Table Expression (CTE)


The common table expression (CTE) is a temporary named result set that you can reference within a SELECT, INSERT, UPDATE, or DELETE statement. It can be assumed as a temporary result set that is defined within the execution scope of a single SELECT, INSERT, UPDATE, DELETE, or CREATE VIEW statement. This is similar to resulting a table in that is not stored as an object and lasts only for the interval of the query. Again it offers the advantages of higher readability and ease in maintenance of complex queries. The query can be divided into separate, simple, logical building blocks.


Use OF CTE
·         Create a recursive query.

·         It is a Substitute of a view when the general use of a view is not required. Or we can say. You do not have to store the definition in metadata.

·         Enable grouping by a column.

·         Reference the resulting table multiple times in the same statement.

Example:

With EMPCTE(Emp_Id,Address, Name, Salery) --Col. names for Temporary table
AS
(
SELECT A.Address, E.Name, E.Salery from Emp_Master A
INNER JOIN EMP_Details E ON E.Emp_Id = A.Emp_Id
)
SELECT * FROM EMPCTE  --SELECT or USE CTE temporary Table
WHERE EMPCTE.Salery > 5000
ORDER BY EMPCTE.NAME

Arguments
·         expression_name
·         column_name
·         CTE_query_definition

Guidelines for Creating and Using Common Table Expressions

  • It must be followed by a single SELECT, INSERT, UPDATE, or DELETE statement that references some or all the CTE columns.
  • Multiple CTE query definitions can be defined in a nonrecursive CTE. The definitions must be combined by one of these set operators: UNION ALL, UNION, INTERSECT, or EXCEPT.
  • A CTE can reference itself and previously defined CTEs in the same WITH clause. Forward referencing is not allowed.
  • Specifying more than one WITH clause in a CTE is not allowed. For example, if a CTE_query_definition contains a subquery, that subquery cannot contain a nested WITH clause that defines another CTE.
  • We cannot use  ORDER BY ,INTO,OPTION ,FOR BROWSE clauses in the CTE_query_definition
  • When a CTE is used in a statement that is part of a batch, the statement before it must be followed by a semicolon.
  • Tables on remote servers can be referenced in the CTE.
  • When executing a CTE, any hints that reference a CTE may conflict with other hints that are discovered when the CTE accesses its underlying tables, in the same manner as hints that reference views in queries. When this occurs, the query returns an error.
at

SQL index

In SQL index are used to speed up the performance of queries. It reduce the number of database data pages scanned. In SQL Server, a clustered index determines the physical order of data in a table. Remember There can be only one clustered index per table. An index is nothing but a copy of select columns from a table that can be searched very efficiently that also includes a low-level disk block address

Please Note that updating a table’s data with indexes takes more time than updating a table without because the indexes also need an update. So you should only create indexes on columns and tables that will be frequently searched.

We have two type of index in SQL

 1.  Clustered Index

The primary key created for the Emp_id column will create a clustered index for the Emp_id column. A table can have only one clustered index on it.

When creating the clustered index, SQL server 2005 reads the Emp_id column and forms a Binary tree on it. This binary tree information is then stored separately in the disc. Expand the table Emp_Details and then expand the Indexes. You will see the following index created for you when the primary key is created:



SELECT * FROM Emp_Details WHERE Emp_id=662
Execution of the query will be

11. It will check the root node as 662 is less than 2000: move to left node
  2.   On 1st level it will check as 662 will in between 501-1000: move to right

It will get the records and send back the results  

2. Non Clustered Index

A non-clustered index is useful for columns that have some repeated values. Say for example, Emp_Id column of a bank database may have 15 million rows. But, the distinct values of account type may be 10-15. A clustered index is automatically created when we create the primary key for the table. We need to take care of the creation of the non-clustered index.

at

Sql Constraint


Sql Constraint
Constraints are the rules apply on data columns on table. These are used to ensure the accuracy and reliability of the data in the database.
There are two type of Constraints
·         Column level : Column level constraints are applied only to one column
·         Table level: table level constraints are applied to the whole table.
Following are commonly used constraints available in SQL.
·        NOT NULL: Ensures that a column cannot have NULL value.
CREATE TABLE Emp_Details
(
Emp_Id int NOT NULL,
Emp_Name varchar(200) NOT NULL,
Address varchar(200),
City varchar(200)
)
·        DEFAULT: Provides a default value for a column when none is specified.
CREATE TABLE Emp_Details
(
Emp_Id int NOT NULL,
Emp_Name varchar(200) NOT NULL,
Address varchar(200),
City varchar(200),
JoiningDate Datetime DEFAULT GETDATE()
)
·        UNIQUE: Ensures that all values in a column are different.
CREATE TABLE Emp_Details
(
Emp_Id int NOT NULL ,
Emp_Name varchar(200) NOT NULL,
Address varchar(200),
City varchar(200),
CONSTRAINT uc_EmpID UNIQUE (Emp_Id)
)
·        PRIMARY: Uniquely identified each rows/records in a database table.
CREATE TABLE Emp_Details
(
Emp_Id int NOT NULL PRIMARY KEY,
Emp_Name varchar(200) NOT NULL,
Address varchar(200),
City varchar(200),
)
·        FOREIGN Key: Uniquely identified a rows/records in any another database table.
CREATE TABLE Emp_Details
(
Emp_Id int NOT NULL PRIMARY KEY,
Emp_Name varchar(200) NOT NULL,
Address varchar(200),
City varchar(200),
PRIMARY KEY (Emp_Id) FOREIGN KEY REFERENCES Emp_Master(Emp_Id)
)

·        CHECK: The CHECK constraint ensures that all values in a column satisfy certain conditions.
CREATE TABLE Emp_Details
(
Emp_Id int NOT NULL CHECK (P_Id>0),
Emp_Name varchar(200) NOT NULL,
Address varchar(200),
City varchar(200),
PRIMARY KEY (Emp_Id)
)
·        INDEX: Use to create and retrieve data from the database very quickly.
User can apply the Constraints either at the time of creation of the table with the CREATE TABLE statement or by using ALTER TABLE statement to create constraints on existing table.
Dropping Constraints:
Constraint can be dropped using the ALTER TABLE command with the DROP CONSTRAINT option.
e.g., to drop the primary key constraint in the EMP_Details table, you can use the following command:
ALTER TABLE EMP_Details DROP CONSTRAINT EMP_Id;
Integrity Constraints:

Integrity constraints are used to ensure accuracy and consistency of data in a relational database. Data integrity is handled in a relational database through the concept of referential integrity.
at

Delete duplicate records from a SQL table without a primary key

If we need to Delete duplicate records from a SQL table without a primary key then we can do with many ways. please try below statements 

 1st:
DELETE SUB FROM
(SELECT ROW_NUMBER() OVER (PARTITION BY EmpId, EmpName, EmpSSN ORDER BY EmpId) cnt
 FROM Employee) SUB

WHERE SUB.cnt > 1

2nd:

With duplicates
As
(Select *, ROW_NUMBER() Over (PARTITION by EmpID,EmpSSN Order by EmpID,EmpSSN) as Duplicate From Employee)
delete From duplicates
Where Duplicate > 1 ;



These Statements will update Table and remove all duplicates from the Table!
at
Subscribe to: Posts (Atom)

Why we can't execute a stored procedure from a User Defined function(UDF)

Functions cannot "touch" any database but read them only. Stored procedures can do anything and everything with databases. You ...