SQL SERVER – Is Cursor Database Object or Datatype

It is commonly believed that cursor are Database Objects. I have always given the definition of cursor as SQL Server cursors 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 reader of blog asked me question if cursor is database object or datatype? My answer to this question is Cursor 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 same way as any other datatypes.

I would like to see all of your opinion about what do you think about cursor? Database Object or Database Datatype.

Reference : Pinal Dave (http://blog.SQLAuthority.com)

About these ads

34 thoughts on “SQL SERVER – Is Cursor Database Object or Datatype

  1. 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

    Like

    • 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

      Like

  2. hi ammen
    can u plz try this ?

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

    Like

  3. Pingback: SQL SERVER - 2008 - Interview Questions and Answers - Part 2 Journey to SQL Authority with Pinal Dave

  4. 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)

    Like

  5. 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

    Like

  6. @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.

    Like

    • 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.

      Like

  7. 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.

    Like

  8. 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..

    Like

  9. 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

    Like

  10. 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.

    Like

  11. 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

    Like

  12. 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…….

    Like

  13. Pingback: SQL SERVER – Interview Questions and Answers – Frequently Asked Questions – Day 3 of 31 Journey to SQLAuthority

  14. 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.

    Like

  15. we can say cursor as database code not a database object.database objects are tables,indexes,views.database code like stored procs,cursors

    Like

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s