Sql Server - Cursor Alternative
SQL Server - Cursor Alternative’s
/* The purpose of this series of workshops is to try to encourage you to take a practical approach to SQL skills. I always find I learn things much quicker by trying things out and experimenting. Please don't just run the samples, but make changes, alter the data, look for my mistakes, try to see if there are different ways of doing things. Please feel free to criticize or disagree with what I say, if you can back this up. This workbench on cursors is not intended to tell you the entire story, as a tutorial might, but the details on BOL should make much more sense after you've tried things out for yourself!
Contents
What are cursors for? Where would you use a cursor? Global cursors Are Cursors Slow? Cursor Variables Cursor Optimization Acknowledgements
What are cursors for? Cursors were created to bridge the 'impedance mismatch' between the 'record- based' culture of conventional programming and the set-based world of the relational database.
They had a useful purpose in allowing existing applications to change from ISAM or KSAM databases, such as DBaseII, to SQL Server with the minimum of upheaval. DBLIB and ODBC make extensive use of them to 'spoof' simple file-based data sources.
Relational database programmers won't need them but, if you have an application that understands only the process of iterating through result sets, like flicking through a card index, then you'll probably need a cursor.
Where would you use a Cursor? An simple example of an application for which cursors can provide a good solution is one that requires running totals. A cumulative graph of monthly sales to date is a good example, as is a cashbook with a running balance. We'll try four different approaches to getting a running total... */ /*so lets build a very simple cashbook */ CREATE TABLE #cb ( cb_ID INT IDENTITY(1,1),--sequence of entries 1..n Et VARCHAR(10),
/* The purpose of this series of workshops is to try to encourage you to take a practical approach to SQL skills. I always find I learn things much quicker by trying things out and experimenting. Please don't just run the samples, but make changes, alter the data, look for my mistakes, try to see if there are different ways of doing things. Please feel free to criticize or disagree with what I say, if you can back this up. This workbench on cursors is not intended to tell you the entire story, as a tutorial might, but the details on BOL should make much more sense after you've tried things out for yourself!
Contents
What are cursors for? Where would you use a cursor? Global cursors Are Cursors Slow? Cursor Variables Cursor Optimization Acknowledgements
What are cursors for? Cursors were created to bridge the 'impedance mismatch' between the 'record- based' culture of conventional programming and the set-based world of the relational database.
They had a useful purpose in allowing existing applications to change from ISAM or KSAM databases, such as DBaseII, to SQL Server with the minimum of upheaval. DBLIB and ODBC make extensive use of them to 'spoof' simple file-based data sources.
Relational database programmers won't need them but, if you have an application that understands only the process of iterating through result sets, like flicking through a card index, then you'll probably need a cursor.
Where would you use a Cursor? An simple example of an application for which cursors can provide a good solution is one that requires running totals. A cumulative graph of monthly sales to date is a good example, as is a cashbook with a running balance. We'll try four different approaches to getting a running total... */ /*so lets build a very simple cashbook */ CREATE TABLE #cb ( cb_ID INT IDENTITY(1,1),--sequence of entries 1..n Et VARCHAR(10),