What is Cursor in SQL Server with Example

No comments


o use cursor in sql server that syntax will be like as shown below

Syntax to Create Cursor


DECLARE cursorname CURSOR
FOR selectstatement -- like SELECT OrderID,CustomerID FROM Orders
OPEN cursor
FETCH tablerow FROM cursor
Process Fetched Tablerow
CLOSE cursor
DEALLOCATE cursor
If you observe above syntax we have different steps in cursor

Step 1: Declare Cursor

First we need to declare cursor name that will be used in cursor execution

Step 2: Select Statement

Select statement is used to get data from table to process with cursor

Step 3: Open Cursor

This statement is used to open the cursor to process select statement result set

Step 4: Fetch Rows

Once cursor is opened, rows can be fetched from the cursor one by one

Step 5: Close Cursor

Once our process finished we need to close the cursor

Step 6: Deallocate Cursor

We need to deallocate cursor to delete cursor definition and release resources associated with the cursor

We will check this with example for that first create one table UserDetails in your database and insert some 100 or 200 records or install northwind database in your server and use orders table it contains more than 500 records

Example

Write cursor script like as shown below and run it.


DECLARE ex_cursor CURSOR
FOR SELECT OrderID,CustomerID FROM Orders
DECLARE @oid INT
DECLARE @cname NVARCHAR(50)
OPEN ex_cursor
FETCH NEXT FROM ex_cursor INTO @oid,@cname
WHILE @@FETCH_STATUS = 0
BEGIN
PRINT  (CAST(@oid AS VARCHAR(5)) + '-' + @cname)
FETCH NEXT FROM ex_cursor INTO @oid,@cname
END
CLOSE ex_cursor
DEALLOCATE ex_cursor
Output:

When we run above query our results like as shown below

Demo

No comments :

Post a Comment

SQL Server Difference between @@IDENTITY, SCOPE_IDENTITY () and IDENT_CURRENT

No comments
@@IDENTITY

It will return last or newly inserted record id of any table in current session but it’s not limited to current scope. In current session if any trigger or functions inserted record in any table that it will return that latest inserted record id regardless of table. We need to use this property whenever we don’t have any other functions or triggers that run automatically.

Syntax


SELECT @@IDENTITY

SCOPE_IDENTITY()

This property will return last or newly inserted record id of table in current session or connection and it’s limited to current scope that means it will return id of newly inserted record in current session / connection stored procedure or query executed by you in current scope even we have any other functions or triggers that run automatically. Its better we can go with property whenever we need to get last or newly inserted record id in table.

Syntax


SELECT SCOPE_IDENTITY()

IDENT_CURRENT

This property will return last or newly inserted record id of specified table. It’s not limited to any session or scope it’s limited to mentioned table so it will return last inserted record id of specified table.

Syntax


SELECT IDENT_CURRENT(table_name)

Finally we can say SCOPE_IDENTITY properties is best to get newly inserted record id from executed stored procedure or query when compared with other properties

Example


CREATE TABLE SAMPLE1 (Id INT IDENTITY)
CREATE TABLE SAMPLE2 (Id INT IDENTITY(100,1))
-- Trigger to execute while inserting data into SAMPLE1 table
GO
CREATE TRIGGER TRGINSERT ON SAMPLE1 FOR INSERT
AS
BEGIN
INSERT SAMPLE2 DEFAULT VALUES
END
GO

SELECT * FROM SAMPLE1  -- It will return empty value
SELECT * FROM SAMPLE2  -- It will return empty value

When we execute above statements we will get output like as shown below



Now we will insert default values in “SAMPLE1” table by executing following query and check values of @@identity, scope_identity() and ident_current(‘tablenae’)


INSERT SAMPLE1 DEFAULT VALUES

SELECT @@IDENTITY  -- It returns value 100 this was inserted by trigger

SELECT SCOPE_IDENTITY()  -- It returns value 1 this was inserted by insert query in SAMPLE1

SELECT IDENT_CURRENT('SAMPLE2') -- It returns value inserted in SAMPLE2 table

Our output will be like as shown below


SQL Server Difference between @@IDENTITY, SCOPE_IDENTITY() and IDENT_CURRENT

No comments :

Post a Comment

Differences between IQueryable and IEnumerable in C# with Example

No comments
Introduction:

Here I will explain difference between IQueryable and IEnumerable list in c# with example or IEnumerable vs IQueryable list in c# with example or what is difference between IQueryable and IEnumerable list in c# with example. In c# we use IQueryable and IEnumerable lists to perform data manipulation.

Description:

In previous posts I explained multiple inheritance in c#, vb.netdifference between throw and throw ex in c#, vb.netconstructor in c#, vb.net with exampledelegates in c#, vb.net with examplesealed class in c#, vb.net with exampleusing statement in c#, vb.net and many articles related to interview questionsasp.netc#.net. Now I will explain difference between IQueryable and IEnumerable list in c# with example.

Generally, we use IEnumerable and IQueryable to hold collection of data and perform data manipulation operations like filtering, etc. based on our requirements. We will see difference between IEnumerable and IQueryable with examples.

IEnumerable

1. If we want to use IEnumerable in our application, we can get it by adding System.Collections namespace.


2. IEnumerable best suitable for in-memory operations because first it will execute “select query” on server and it will load all the data into client memory then only it will apply all the filter conditions.

Suppose if we have table called EmployeeDetails in our database from that we need to get only top 3 records where users gender equals to “Male” for this if we use IEnumerable first it will execute “select query” on database server, it loads all the records into memory and then it filters the data based on our requirement.



3. For remote operations IEnumerable is not suggestable and its better use IEnumerable to perform query operations on in-memory collections like List, Array, etc.

In case if our tables contain more than 1 or 2 lac records then IEnumerable will fetch all the records into our application memory then it will perform filter conditions due to this our application becomes very slow.

4. IEnumerable is beneficial when you want to load all the data from query into memory and apply further filtering. Its best suitable for LINQ to Objects and LINQ to XML operations.

IEnumerable Example

Following is the simple example for IEnumerable collection.


DataClasses1DataContext dbcon = new DataClasses1DataContext();
IEnumerable<EmployeeDetail> emplist = dbcon.EmployeeDetails.Where(e => e.Gender.Equals("Male"));
emplist = emplist.Take<EmployeeDetail>(3);

When we execute above code we will get sql query like as shown below


SELECT [t0].[EmpId], [t0].[EmpName], [t0].[Location], [t0].[Gender]
FROM [dbo].[EmployeeDetails] AS [t0]
WHERE [t0].[Gender] = @p0


Here if you observe above query “Top 3” filtering condition is missing because IEnumerable will apply filtering conditions once it loads all the data in client-side memory.

IQueryable

1. If we want to use IQueryable in our application, we can get it by adding System.Linq namespace.



2. IQueryable is best suitable for out-memory (remote database) operations because it will execute select query with all filter conditions on server.

Suppose if we have table called EmployeeDetails in our database from that we need to get only top 3 records where users gender equals to “Male” for this if we use IQueryable it will execute “select query along with filter conditions” on database server and it loads only required records based on our conditions.



3. IQueryable is best suitable for LINQ to SQL operations.

IQueryable Example

Following is the simple example for IQueryable collection.


DataClasses1DataContext dbcon = new DataClasses1DataContext();
IQueryable<EmployeeDetail> emplist = dbcon.EmployeeDetails.Where(e => e.Gender.Equals("Male"));
emplist = emplist.Take<EmployeeDetail>(3);

When we execute above code we will get sql query like as shown below


SELECT TOP (3) [t0].[EmpId], [t0].[EmpName], [t0].[Location], [t0].[Gender]
FROM [dbo].[EmployeeDetails] AS [t0]
WHERE [t0].[Gender] = @p0

Here if you observe above query “Top 3” filtering condition also included it means IQueryable will apply all the filtering conditions on SQL Server itself to get only matching records instead of loading all the data into memory.

I hope it helps you to understand difference between IEnumerable and IQueryable.

No comments :

Post a Comment