You might be aware of the Halloween problem in which a an SQL cursor can be a nightmare for you if not properly used.
您可能知道万圣节问题,如果没有正确使用,SQL游标可能会成为您的噩梦。
What are the other subtle problems (difficult to debug and detect) that might be introduced if one does not carefully make use of SQL 2005 features?
如果不仔细使用SQL 2005功能,可能会引入哪些其他微妙问题(难以调试和检测)?
4 个解决方案
#1
First of all cursors should not be used unless there absolutely is no other way to do it. Almost everything can be done set based. A numbers table will help with that. Check XACT_STATE for doomed transactions. there are many more things to be aware of
首先,除非绝对没有其他方法,否则不应使用游标。几乎所有事情都可以完成。数字表将有助于此。检查XACT_STATE是否有注定的事务。还有很多事情需要注意
Read here about some of SQL Server Programming Pitfalls
在这里阅读一些SQL Server编程陷阱
#2
Trying to treat SQL like a programming environment is probably the biggest mistake. SQL is all about declaring what you want. If you ever are using cursors or looping constructs, ask yourself if there was a way you could transform it into an INSERT/UPDATE
尝试像编程环境一样对待SQL可能是最大的错误。 SQL就是声明你想要的东西。如果您正在使用游标或循环结构,请问自己是否有办法将其转换为INSERT / UPDATE
#3
- Doing NOT IN on a column which can have NULLS does not give result.
- Doing count(cloumn) on a column which can have nulls would only give you count of non null values.
在没有NULLS的列上执行NOT IN不会产生结果。
对可以具有空值的列执行count(cloumn)只会给出非空值的计数。
#4
There are a number of issues that come to mind:
我想到了许多问题:
- NULL handling - a lot of folks have trouble understanding that NULL is a very different beast than "0" (zero), and that you CANNOT usually compare IF myCol = NULL - you need to use "IS NULL" or "IS NOT NULL" and so forth.
NULL处理 - 很多人都很难理解NULL是一个非常不同于“0”(零)的野兽,并且你通常不能比较IF myCol = NULL - 你需要使用“IS NULL”或“IS NOT NULL”等等。
NULL has its place - when it really means the absence of a choice or value - but in many cases, you're probably better off defining a default value (on an INT column often "0") that will represent "nothing" or "no choice has been made" etc.
NULL有它的位置 - 当它真的意味着没有选择或值 - 但在许多情况下,你可能最好定义一个默认值(在INT列上经常为“0”)代表“无”或“没有选择“等
- Indexing - a lot of confusion around indexing and what the pros and cons, benefits and drawbacks are. There's no point in indexing every column - and even if you've identified a column to be used often in a SELECT ... WHERE clause, if the selectivity of the column is not sufficiently small (some experts say if a single value of a column doesn't select out 1% or less of your data, don't bother indexing), often it won't really help since a full-table scan (index scan) will be cheaper.
索引 - 关于索引的许多混淆以及利弊,利弊是什么。索引每一列都没有意义 - 即使你已经在SELECT ... WHERE子句中确定了一个经常使用的列,如果列的选择性不够小(一些专家说如果单个值为a)列不会选择1%或更少的数据,不要打扰索引),通常它不会真正有用,因为全表扫描(索引扫描)会更便宜。
And also, with indexing - if you use T-SQL functions in your WHERE clauses, like "UPPER(myCol) = (value)" or "LEFT(myCol, 10) = (value)", then you will not see the intended benefit from an index, either.
而且,使用索引 - 如果你在WHERE子句中使用T-SQL函数,如“UPPER(myCol)=(value)”或“LEFT(myCol,10)=(value)”,那么你将看不到它的意图从指数中受益。
Indexing can be a huge boost to your performance - but index wisely!
索引可以极大地提升您的性能 - 但明智的指数!
#1
First of all cursors should not be used unless there absolutely is no other way to do it. Almost everything can be done set based. A numbers table will help with that. Check XACT_STATE for doomed transactions. there are many more things to be aware of
首先,除非绝对没有其他方法,否则不应使用游标。几乎所有事情都可以完成。数字表将有助于此。检查XACT_STATE是否有注定的事务。还有很多事情需要注意
Read here about some of SQL Server Programming Pitfalls
在这里阅读一些SQL Server编程陷阱
#2
Trying to treat SQL like a programming environment is probably the biggest mistake. SQL is all about declaring what you want. If you ever are using cursors or looping constructs, ask yourself if there was a way you could transform it into an INSERT/UPDATE
尝试像编程环境一样对待SQL可能是最大的错误。 SQL就是声明你想要的东西。如果您正在使用游标或循环结构,请问自己是否有办法将其转换为INSERT / UPDATE
#3
- Doing NOT IN on a column which can have NULLS does not give result.
- Doing count(cloumn) on a column which can have nulls would only give you count of non null values.
在没有NULLS的列上执行NOT IN不会产生结果。
对可以具有空值的列执行count(cloumn)只会给出非空值的计数。
#4
There are a number of issues that come to mind:
我想到了许多问题:
- NULL handling - a lot of folks have trouble understanding that NULL is a very different beast than "0" (zero), and that you CANNOT usually compare IF myCol = NULL - you need to use "IS NULL" or "IS NOT NULL" and so forth.
NULL处理 - 很多人都很难理解NULL是一个非常不同于“0”(零)的野兽,并且你通常不能比较IF myCol = NULL - 你需要使用“IS NULL”或“IS NOT NULL”等等。
NULL has its place - when it really means the absence of a choice or value - but in many cases, you're probably better off defining a default value (on an INT column often "0") that will represent "nothing" or "no choice has been made" etc.
NULL有它的位置 - 当它真的意味着没有选择或值 - 但在许多情况下,你可能最好定义一个默认值(在INT列上经常为“0”)代表“无”或“没有选择“等
- Indexing - a lot of confusion around indexing and what the pros and cons, benefits and drawbacks are. There's no point in indexing every column - and even if you've identified a column to be used often in a SELECT ... WHERE clause, if the selectivity of the column is not sufficiently small (some experts say if a single value of a column doesn't select out 1% or less of your data, don't bother indexing), often it won't really help since a full-table scan (index scan) will be cheaper.
索引 - 关于索引的许多混淆以及利弊,利弊是什么。索引每一列都没有意义 - 即使你已经在SELECT ... WHERE子句中确定了一个经常使用的列,如果列的选择性不够小(一些专家说如果单个值为a)列不会选择1%或更少的数据,不要打扰索引),通常它不会真正有用,因为全表扫描(索引扫描)会更便宜。
And also, with indexing - if you use T-SQL functions in your WHERE clauses, like "UPPER(myCol) = (value)" or "LEFT(myCol, 10) = (value)", then you will not see the intended benefit from an index, either.
而且,使用索引 - 如果你在WHERE子句中使用T-SQL函数,如“UPPER(myCol)=(value)”或“LEFT(myCol,10)=(value)”,那么你将看不到它的意图从指数中受益。
Indexing can be a huge boost to your performance - but index wisely!
索引可以极大地提升您的性能 - 但明智的指数!