Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

s there any difference in using cursor and table? Expand / Collapse
Author
Message
Posted Thursday, November 8, 2012 5:04 AM


SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Thursday, August 14, 2014 4:48 AM
Points: 76, Visits: 346
is there any difference in using cursor and table ?
any performance issues ?



sample are shown below

example 1:

declare rs_cursor CURSOR for select name from master.dbo.sysdatabases


example 2:

DECLARE @temp as table (RowID int not null primary key identity(1,1),
Filename varchar(500))

insert into @temp select name from master.dbo.sysdatabases
Post #1382423
Posted Thursday, November 8, 2012 5:08 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Tuesday, September 16, 2014 2:20 AM
Points: 5,231, Visits: 5,106
Yes there is typically a huge difference depending on what you want to do.

Cursors are typical RBAR meaning it processes a row at a time, where as with a table it can process a "set" of data at a time which is more effective.




Want an answer fast? Try here
How to post data/code for the best help - Jeff Moden
Need a string splitter, try this - Jeff Moden
How to post performance problems - Gail Shaw
CrossTabs-Part1 & Part2 - Jeff Moden
SQL Server Backup, Integrity Check, and Index and Statistics Maintenance - Ola Hallengren
Managing Transaction Logs - Gail Shaw
Troubleshooting SQL Server: A Guide for the Accidental DBA - Jonathan Kehayias and Ted Krueger

Post #1382425
Posted Thursday, November 8, 2012 1:45 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: 2 days ago @ 11:33 AM
Points: 5, Visits: 200
^ This, and they're usually not compared to each other, as they have totally different uses. What you mentioned is a table variable and is more aptly compared to a temp table, while cursors to while loops. You can use a cursor on a temp table, to do a per-row transaction/DML, but not vice versa. A good analogy would be like comparing a subject with an adjective.
Post #1382698
Posted Thursday, November 8, 2012 8:34 PM


SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Thursday, August 14, 2014 4:48 AM
Points: 76, Visits: 346
is it possible to fetch multiple columns (using cursor)
Post #1382802
Posted Thursday, November 8, 2012 10:21 PM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Today @ 7:13 AM
Points: 1,127, Visits: 1,594
sumith1andonly1 (11/8/2012)
is it possible to fetch multiple columns (using cursor)


Yes it is possible to fetch multiple columns using cursors. You need to do a little research on Cursors.....the following link would help you a lot:

MSSQL Cursor


Vinu Vijayan

For better and faster solutions please check..."How to post data/code on a forum to get the best help" - Jeff Moden
Post #1382816
Posted Thursday, November 8, 2012 11:54 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 8:29 AM
Points: 13,741, Visits: 10,716
sumith1andonly1 (11/8/2012)
is it possible to fetch multiple columns (using cursor)


Let's be clear here:

cursors are sloooooooooooow.

Only use them if you have a very good reason to use them.




How to post forum questions.
Need an answer? No, you need a question.
What’s the deal with Excel & SSIS?

Member of LinkedIn. My blog at LessThanDot.

MCSA SQL Server 2012 - MCSE Business Intelligence
Post #1382840
Posted Friday, November 9, 2012 2:25 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Today @ 7:13 AM
Points: 1,127, Visits: 1,594
Koen Verbeeck (11/8/2012)
sumith1andonly1 (11/8/2012)
is it possible to fetch multiple columns (using cursor)


Let's be clear here:

cursors are sloooooooooooow.

Only use them if you have a very good reason to use them.


Yes, I agree. Cursors are slow because they do row by row transactions instead of set based transactions which are much much much faster than row by row transactions.


Vinu Vijayan

For better and faster solutions please check..."How to post data/code on a forum to get the best help" - Jeff Moden
Post #1382895
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse