s there any difference in using cursor and table?

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2008 » SQL Server 2008 - General » s there any difference in using cursor and...

s there any difference in using cursor and table?

Rate Topic

Display Mode

Topic Options

Author

Message

sumith1andonly1

Posted Thursday, November 08, 2012 5:04 AM

Valued Member

Group: General Forum Members
Last Login: Sunday, May 05, 2013 11:09 PM
Points: 66, Visits: 260

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

anthony.green

Posted Thursday, November 08, 2012 5:08 AM

SSCertifiable

Group: General Forum Members
Last Login: Friday, April 12, 2013 3:51 AM
Points: 5,075, Visits: 4,831

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

jad0083

Posted Thursday, November 08, 2012 1:45 PM

Forum Newbie

Group: General Forum Members
Last Login: Wednesday, April 17, 2013 5:49 PM
Points: 5, Visits: 81

^ 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

sumith1andonly1

Posted Thursday, November 08, 2012 8:34 PM

Valued Member

Group: General Forum Members
Last Login: Sunday, May 05, 2013 11:09 PM
Points: 66, Visits: 260

is it possible to fetch multiple columns (using cursor)

Post #1382802

vinu512

Posted Thursday, November 08, 2012 10:21 PM

Ten Centuries

Group: General Forum Members
Last Login: Monday, May 20, 2013 12:37 AM
Points: 1,049, Visits: 1,439

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 Wink

Post #1382816

Koen Verbeeck

Posted Thursday, November 08, 2012 11:54 PM

SSCrazy Eights

Group: General Forum Members
Last Login: Today @ 2:11 AM
Points: 9,378, Visits: 6,473

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

vinu512

Posted Friday, November 09, 2012 2:25 AM

Ten Centuries

Group: General Forum Members
Last Login: Monday, May 20, 2013 12:37 AM
Points: 1,049, Visits: 1,439

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 Wink

Post #1382895

« Prev Topic | Next Topic »

Permissions