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.
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..
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)
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
can anyone help me about this????
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
hi ammen
can u plz try this ?
select leavetype,(abs(substring(loofhours,4,5))*100/60) as totalnoofhours from tablename order by totalnoofhours asc;
I think Cursor is a System DataType not Databse Object.
We create Cursor type Varaibles
I too accept the view of Vivek
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)
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
I just wanted to commend you for your excellent articles. They have been very helpful and informative.
i think cursor is a database object
what type cursor in sqlserver?
@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.
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.
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.
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..
You can use set-based approach.
Give us more informations on what you want
can we define Like Cursor is a pointer, maintaiin Reference of one by one row Data Objects.
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
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.
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
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…….
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.
“WHILE @@FETCH_STATUS == 0 ” correct syntax