SQL SERVER – Is Cursor Database Object or Datatype?

Whenever we want to loop something we always look for logic like WHILE LOOP or FOR LOOP. Trust me on my word that both of them are cursor when it is about SQL Server.

SQL SERVER - Is Cursor Database Object or Datatype? loop It is commonly believed that the cursor is Database Objects. I have always given the definition of it as they are database objects used to manipulate data in a set on a row-by-row basis.

Just a few days ago – Imran one of the active readers of the blog asked me question if the cursor is database object or datatype? My answer to this question is it is Database Object. However, this question is very, very interesting. We define cursor same was as datatypes using DECLARE statement and it can be used the same way as any other data types.

I would like to see all of your opinions about what do you think about the this subject? Database Object or Database Datatype.

Here are few of the blog posts which are very much related to the subject. I would like all of you to check that out..

Simple Example – Part 1

Simple Example – Part 2

Please leave a comment with your thought. I will be happy to discuss with you more on this topic.

Reference: Pinal Dave (https://blog.sqlauthority.com)

,
Previous Post
SQL SERVER – 2005 – Restore Database Using Corrupt Datafiles (.mdf and .ldf)
Next Post
SQL Authority News – SQL Server Interview Questions – SQL Related Jobs – DBA Job Description

Related Posts

34 Comments. Leave new

  • hi ,sir

    i have a table named leave having field like leavetype, from, to,
    noofhours
    how can i retrive the row by row wise inorder to calculate the noofhours
    ie
    these are the data of noofhours field
    10.50 (it equals 10 hours n 30 min)
    20.50
    12.25

    i want to get the each row n split the 10.50 value to two
    ie. 10 n 50 n make a calculation like 50*100/60
    ie i want to make the 50 into clock time
    after each calculations i want to add all the data
    n return the result
    how can i do this …………
    can u pls help me

    Reply
  • can anyone help me about this????

    Reply
    • use int(noofhours) to get hours part and then and for hours part you can use
      ((noofhours- int(noofhours))*100)*60/100 gives your exact hours. so sql will like
      SELECT CAST( int(noofhours) AS INT) as Hours,
      CAST(((noofhours- int(noofhours))*100)*60/100) AS Minutes

      Reply
  • Darshanc Shah
    May 19, 2008 6:56 pm

    hi ammen
    can u plz try this ?

    select leavetype,(abs(substring(loofhours,4,5))*100/60) as totalnoofhours from tablename order by totalnoofhours asc;

    Reply
  • I think Cursor is a System DataType not Databse Object.
    We create Cursor type Varaibles

    Reply
  • Anant Anand Gupta
    January 7, 2009 7:57 pm

    I too accept the view of Vivek

    Reply
  • I think it’s a database object. Although we define it like a variable, it can contain all the data of all columns retrieved per row and move it into other variables. This can be any datatype!!

    So in my mind, it’s a database object and not a datatype (contains more information, and from all different datatypes)

    Reply
  • hello sir,
    first of all i want to give u my answer of question “Is Cursor Database Object or Datatype ”
    i think cursor is a datatype not object because the definition and declaration type is same as other datatype.

    now i have a question , i m writing a stored procedure using cursor.

    set @test_temp_query_one = ‘ SET

    @test_temp_count_one = CURSOR FORWARD_ONLY

    STATIC FOR SELECT MAX(FLID) FROM ( SELECT TOP ‘ +

    cast(@VAR_DIVIDE2 as varchar(50)) + ‘ FLID FROM ‘+

    cast(@var_table as varchar(50)) +’ WHERE (FLFILENO

    !=’ + cast(@VAR_SLIP_NO as varchar(50)) +’) ORDER BY

    FLID ) FLID ; OPEN @test_temp_count_one ‘

    EXEC sp_executesql @test_temp_query_one , N’

    @test_temp_count_one CURSOR OUTPUT’,

    @test_temp_count_one OUTPUT
    FETCH NEXT FROM @test_temp_count_one INTO @VAR_FILE_RANGE2

    this is a part of the proceduer because it’s so long…

    when i am executing this procedure the i got an error

    “Operand type clash: nvarchar is incompatible with cursor”

    this pro executing with c#.net pro .

    now my question is……

    tell me the list of datatype , those incompatible with cursor in sql server 7.0

    tahnks in advance

    Reply
  • I just wanted to commend you for your excellent articles. They have been very helpful and informative.

    Reply
  • i think cursor is a database object

    Reply
  • mukesh jamukiya
    December 11, 2009 5:22 pm

    what type cursor in sqlserver?

    Reply
  • @Mukesh,

    There is some thing called as books online. Please refer to that and you will get all information regarding cursors. Other wise make use of Google Search Engine.

    Let us know, if you have any (serious) questions.

    ~ IM.

    Reply
    • cusor are memory spaceon the main memory that is allocated for sql execution and processing. these are used for the mexhanism for manipulation of data row wise to fetch the desired result.

      Reply
  • Hi,

    Cursor is not a data type, it is an object but it has to be declared as cursor. Rows fetched out of a query is stored into this cursor in the RAM. If the query fetches large amount of data, then it occupies large amount of RAM capacity. Obviously other objects cannot be loaded into the memory as the cursor occupies more memory. Naturaly the performance of the SQL server is also pulled down. Thats why it is advised not to use cursors if you can implement some other logic to manipulate row by row.

    Reply
  • Questin:- Everyone we know that cursor and procedure are different object in Sql server. When we create any cursor we know that cursor would take time to complete there execution on the rows. So if we want to ignore or wanna other way to Use Cursor. What will be that. Please reply me its a logical but strong interview question as well..

    Reply
  • SrikanthReddy
    March 13, 2010 9:34 pm

    can we define Like Cursor is a pointer, maintaiin Reference of one by one row Data Objects.

    Reply
  • We declare a cursor and it allocates space in the RAM to do its operations till it completes it cycle so it acts as a variable .I would say it is a temporary database object working on row by row basis

    Reply
  • Cursor is a data type. Its just like @ table. The cursors stores the data in the RAM memory. If its a DB object then it will occupy the physical memory of the DB.

    Reply
  • Umesh Bhavsar
    May 26, 2010 8:08 am

    Hi There!

    With the release of SQL Server 2008, Microsoft has introduced new datatype Cursor. Previously, we were using Cursor as Database Object.

    So, in my opinion, Cursor is Data Type and Database Object also.

    The only difference being the way it is used.

    Hope it helps.

    Warm Regards,

    Umesh Bhavsar

    Reply
  • Hi sir,

    My question is very simple, i want to know if i can perform the operation of right outer join by using left outer join just by swapping the table.
    Then what is the use of right outer join.
    If there any difference between them please let me know.

    Thanks in advance…….

    Reply
  • vishal patwardhan (@vishalpatwardha)
    August 10, 2011 1:02 pm

    hi Pinal ,
    See an example
    I’ve two tables
    1)servertable(ServerID,ServerName)
    2)NewServerTable(ServerID,ServerName)

    if I use SqlStatement to insert records of servertable into NewServerTable
    like this :
    Insert Into NewServerTable
    Select From Server Table.

    and If I use Cursor for the same thing like this

    DECLARE db_cursor CURSOR FOR
    SELECT SERVERID,SERVERNAME
    FROM SERVERTABLE
    DECLARE @SERVERID INT
    DECLARE @SERVERNAME VARCHAR(20)
    OPEN db_cursor
    FETCH NEXT FROM db_cursor INTO @SERVERID,@SERVERNAME

    WHILE @@FETCH_STATUS = 0
    BEGIN
    FETCH NEXT FROM db_cursor INTO @SERVERID,@SERVERNAME
    INSERT INTO NEWSERVERTABLE(SERVERID,SERVERNAME) VALUES (@SERVERID,@SERVERNAME)
    END

    CLOSE db_cursor
    DEALLOCATE db_cursor

    I known to this that sqlqueries are much better in performance as compare to Cursor But How? Please give me explanation.

    Reply
  • “WHILE @@FETCH_STATUS == 0 ” correct syntax

    Reply

Leave a Reply