Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
global_erp_solution
Starting Member
28 Posts |
 Posted - 2012-09-21 : 00:02:41
|
| hi, I'm tasked with converting pl/sql stored procedure to t-sql. I'm wondering if SQL Server also has implicit and explicit cursor like pl/sql? how many types of cursor does SQL Server 2008 have? thanks |
|
|
jackv
Master Smack Fu Yak Hacker
2179 Posts |
Posted - 2012-09-21 : 01:51:25
|
| There are certain actions in SQL Server which trigger an implicit CURSOR. For example , a Query references remote tables in linked servers forward - onnly becomes a Keyset. The explicit cursors supported by SQL Server are: Static cursorsDynamic cursorsForward-only cursorsKeyset-driven cursorsJack Vamvas--------------------http://www.sqlserver-dba.com |
 |
|
|
Transact Charlie
Master Smack Fu Yak Hacker
3451 Posts |
Posted - 2012-09-21 : 06:19:12
|
take the opportunity to rewrite them without using cursors if possible.Post here (maybe on a separate thread for each object) if you want some help rewriting them.Transact CharlieMsg 3903.. The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION. http://nosqlsolution.blogspot.co.uk/ |
|
|
|
LoztInSpace
Aged Yak Warrior
940 Posts |
Posted - 2012-09-21 : 07:54:08
|
| Cursors in Oracle are 100% ok assuming you can't just do what you're doing in SQL. In SQL Server they 100% suck. Often though what you need is a SP returning a set of records from a select statement. In SQL Server that's not a cursor be be careful you don't confuse the two. |
|
|
|
global_erp_solution
Starting Member
28 Posts |
Posted - 2012-09-22 : 00:31:48
|
| AFAIK, the only substitute for cursor is subquery. are subqueries better than cursors in term of performance? |
|
|
|
jackv
Master Smack Fu Yak Hacker
2179 Posts |
Posted - 2012-09-22 : 02:29:10
|
| It depends on the how you've written the subquery. Is it optimised making use of indexes etc? Subqueries can be very efficient. Can you write the query using JOINS?If you post here ?Jack Vamvas--------------------http://www.sqlserver-dba.com |
|
|
|
global_erp_solution
Starting Member
28 Posts |
Posted - 2012-09-22 : 02:34:31
|
| okay, up until now, stored procedures I've encountered are quite rudimentary. just a bunch of really long joins, aggregates and DMLs. no cursor yet. I'm just collecting as many info as possible on cursors since I'm bound to encounter cursor one of these days and I want to be prepared. thanks |
|
|
|
jackv
Master Smack Fu Yak Hacker
2179 Posts |
Posted - 2012-09-22 : 08:06:36
|
| Alright. Using a set based approach in SQL Server - will yield - generally much better results. Cursors have a place - under certain conditions -. In an OLTP I'd advise to avoid themJack Vamvas--------------------http://www.sqlserver-dba.com |
|
|
|
|
|
|
oracle to t-sql cursors