Tek-Tips is the largest IT community on the Internet today!
Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!
-
Congratulations derfloh on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!
cool source of sql scripts
- Thread starter dky1e
- Start date
- Status
On initial viewing of the information on this site, I would suggest caution in taking to heart all of the information on this site. The site includes opinionated absolutes, such as any use of cursors is the result of poor design and poor implmentation and basically says that any developer that says otherwise is a moron.
- Thread starter
- #3
If you are designing a database system that will be utilized by applications then yes, stay away from cursors. However, once you begin to create applications that utilize your newly designed database, situtations arise that require cursors. It is much more efficient and convenient to develop a sql script that uses a cursor or cursors to perform a simple task, than it is to create an application that does the same thing.
>> Anything can be done with out having to resort to cursors
If that's the case why make them available at all? It is true that you should try and avoid them wherever possible but in some cases they can provide a useful mechanism.
I would have to agree with stravis on that site. There are a couple of useful tips but the author seems to think that there is only ever one correct way of doing anything (eg "all database access should be via stored procedures" - a bit of a sweeping generalisation I would say). A good DBA/developer is familiar with as many different techniques and methods as possible and, while I agree there are certain conventions and "standard practices", there are situations where a little lateral thinking can provide a better solution.
--James
If that's the case why make them available at all? It is true that you should try and avoid them wherever possible but in some cases they can provide a useful mechanism.
I would have to agree with stravis on that site. There are a couple of useful tips but the author seems to think that there is only ever one correct way of doing anything (eg "all database access should be via stored procedures" - a bit of a sweeping generalisation I would say). A good DBA/developer is familiar with as many different techniques and methods as possible and, while I agree there are certain conventions and "standard practices", there are situations where a little lateral thinking can provide a better solution.
--James
- Thread starter
- #6
"all database access should be via stored procedures"
They're compiled on the server and execution plan in properly cached, it makes them fastest (if written correctly, ofcourse). Another great thing is that it's very easy to maintain security or even implement row-level security if needed. I avoid views, just because you have to maintain indexes both on the view and the tables in order to make it efficient.
But, I agree... you should be able to know all possible options available. If I need to put something together QUICK, or even something that will be run infrequently such as a report, I'll use what ever makes it easiest. But if it's a back-end system for a website, for example, the rules should apply.
They're compiled on the server and execution plan in properly cached, it makes them fastest (if written correctly, ofcourse). Another great thing is that it's very easy to maintain security or even implement row-level security if needed. I avoid views, just because you have to maintain indexes both on the view and the tables in order to make it efficient.
But, I agree... you should be able to know all possible options available. If I need to put something together QUICK, or even something that will be run infrequently such as a report, I'll use what ever makes it easiest. But if it's a back-end system for a website, for example, the rules should apply.
topher0303
Programmer
I agree with not using cursors unless absolutely necessary. The solution is not always to move it to the app. You would be amazed how often a cursor can be replaced with a well developed sql statement.
I prefer the use of sprocs as well. However, there are situations that they do not seem feasible. One such example is when you create an app that gives the user the ability to search a table using one or more fields. there are some many combonations for a single lookup. How do you resolve this using sprocs?
I prefer the use of sprocs as well. However, there are situations that they do not seem feasible. One such example is when you create an app that gives the user the ability to search a table using one or more fields. there are some many combonations for a single lookup. How do you resolve this using sprocs?
nigelrivett
IS-IT--Management
>> basically says that any developer that says otherwise is a moron.
I don't believe I say that anywhere!
>> However, once you begin to create applications that utilize your newly designed database, situtations arise that require cursors. It is much more efficient and convenient to develop a sql script that uses a cursor or cursors to perform a simple task, than it is to create an application that does the same thing.
Nope - Occasionally a cursor can be more efficient than a correlated subquery but tieing yourself into acting on a single row is always a bad idea.
>> If that's the case why make them available at all?
Good question - it's to allow people who think in a procedural way to put there ideas into code. In the old days they were very resource intensive and always slower so there wasn'r really any argument.
>> I agree with not using cursors unless absolutely necessary.
They are never necesasry on sql server - I have never written one except to post on forums preceded by "don't do this".
The remark on my web site was meant to be a bit tongue in cheek but isn't a bad premise. I will allow staff to implement them just to force them to at least try set oriented solutions.
I don't believe I say that anywhere!
>> However, once you begin to create applications that utilize your newly designed database, situtations arise that require cursors. It is much more efficient and convenient to develop a sql script that uses a cursor or cursors to perform a simple task, than it is to create an application that does the same thing.
Nope - Occasionally a cursor can be more efficient than a correlated subquery but tieing yourself into acting on a single row is always a bad idea.
>> If that's the case why make them available at all?
Good question - it's to allow people who think in a procedural way to put there ideas into code. In the old days they were very resource intensive and always slower so there wasn'r really any argument.
>> I agree with not using cursors unless absolutely necessary.
They are never necesasry on sql server - I have never written one except to post on forums preceded by "don't do this".
The remark on my web site was meant to be a bit tongue in cheek but isn't a bad premise. I will allow staff to implement them just to force them to at least try set oriented solutions.
I also agree with not using cursors unless absolutely necessary.
nigelrivett,
as you sad: I have never written one except to post on forums preceded by "don't do this".,
there is lot of developers that can say this ( and they are happy ),
but I wonder, if I am the only one, that allow users to create it's own calculations on 'unlimited' count of groups of data
( and for each group of data he can define different calculation, for example: Column1 + 2 * ( Column3 - Column4 ) ... )
I have this in all my programs, that I am working on it
And there is no way to do this without cursors :-(
Zhavic
---------------------------------------------------------------
In the 1960s you needed the power of two Comodore64s to get a rocket to the moon. Now you need a machine which is a vast number of times more powerful just to run the most popular GUI.
nigelrivett,
as you sad: I have never written one except to post on forums preceded by "don't do this".,
there is lot of developers that can say this ( and they are happy ),
but I wonder, if I am the only one, that allow users to create it's own calculations on 'unlimited' count of groups of data
( and for each group of data he can define different calculation, for example: Column1 + 2 * ( Column3 - Column4 ) ... )
I have this in all my programs, that I am working on it
And there is no way to do this without cursors :-(
Zhavic
---------------------------------------------------------------
In the 1960s you needed the power of two Comodore64s to get a rocket to the moon. Now you need a machine which is a vast number of times more powerful just to run the most popular GUI.
nigelrivett
IS-IT--Management
Depends what you mean but I've maybe done similar things - parsing expressions entered and turning them into actions on data including arithmeitic and conditional expressions.
>> And there is no way to do this without cursors
Nope - not saying it will be faster but you never need to use a cursor in sql server. You can always substitute a temp table and iterate through that.
As I said - it may not be better but allows more options.
>> And there is no way to do this without cursors
Nope - not saying it will be faster but you never need to use a cursor in sql server. You can always substitute a temp table and iterate through that.
As I said - it may not be better but allows more options.
well here is my 20c worth.....
>>"but you never need to use a cursor in sql server".
I have to disagree with your statement and agree with James and stravis. You can't generalise like that. If you never need to use cursors then why did Microsoft include it in SQL Server? Thats like saying you can use cursors in Oracle and DB2 but you never have to use them in SQL Server. On the surface all 3 are RDMSs and all support SQL in one way or another.
Personally if any version of SQL would allow you to get away from cursors it would be PL/SQL and not T-SQL but that is my opinion.
Microsoft themselves use a cursor in a procedure called sp_help_revlogin which can be located on the support site to assist you in migrating logins between servers.
There are downsides to #temp tables especially inside procedures that make use of linked servers as I believe you can only use one #temp table in a procedure across linked servers.
I agree that you should try and avoid cursors but they are there for a reason and if they are used correctly and optimally cursors are very useful function.
Again as I stated this is my opinion.
John
>>"but you never need to use a cursor in sql server".
I have to disagree with your statement and agree with James and stravis. You can't generalise like that. If you never need to use cursors then why did Microsoft include it in SQL Server? Thats like saying you can use cursors in Oracle and DB2 but you never have to use them in SQL Server. On the surface all 3 are RDMSs and all support SQL in one way or another.
Personally if any version of SQL would allow you to get away from cursors it would be PL/SQL and not T-SQL but that is my opinion.
Microsoft themselves use a cursor in a procedure called sp_help_revlogin which can be located on the support site to assist you in migrating logins between servers.
There are downsides to #temp tables especially inside procedures that make use of linked servers as I believe you can only use one #temp table in a procedure across linked servers.
I agree that you should try and avoid cursors but they are there for a reason and if they are used correctly and optimally cursors are very useful function.
Again as I stated this is my opinion.
John
nigelrivett
IS-IT--Management
>> You can't generalise like that. If you never need to use cursors then why did Microsoft include it in SQL Server?
Whatever is coded with a cursor in t-sql can be coded without using a temp table. I'm not saying it will be better but is possible.
Don't know if this is true of Oracle but certainly I have rewritten a lot of code to remove cursors to improve performance. About DB2 I have no idea.
I've already covered why they are included - there is a lot of redundancy in the product.
>> Microsoft themselves use a cursor in a procedure called sp_help_revlogin
Cursors are used in a number of system SPs. Also a lot which don't use cursors are fairly poorly written - but they do their job (usually). Especially with v6.5 they had a good chance of blocking all activity on the server when trying to diagnose any problems - they are a lot better now but you still have to consider their impact.
I've been working full time developing with sql server for 10 years and have never come across an instance where I used a cursor. It's not possible to prove by example but it's easy to see that you can itterate through any result set without a cursor if you need to (except in one case where it becomes very difficult but I'm not going to expand on that here).
Whatever is coded with a cursor in t-sql can be coded without using a temp table. I'm not saying it will be better but is possible.
Don't know if this is true of Oracle but certainly I have rewritten a lot of code to remove cursors to improve performance. About DB2 I have no idea.
I've already covered why they are included - there is a lot of redundancy in the product.
>> Microsoft themselves use a cursor in a procedure called sp_help_revlogin
Cursors are used in a number of system SPs. Also a lot which don't use cursors are fairly poorly written - but they do their job (usually). Especially with v6.5 they had a good chance of blocking all activity on the server when trying to diagnose any problems - they are a lot better now but you still have to consider their impact.
I've been working full time developing with sql server for 10 years and have never come across an instance where I used a cursor. It's not possible to prove by example but it's easy to see that you can itterate through any result set without a cursor if you need to (except in one case where it becomes very difficult but I'm not going to expand on that here).
- Status
Similar threads
- Locked
- Question
- Locked
- Question
