[sql] - 没有nolock的查询时没有数据 [英] [Sql]-none data when query without nolock

问题描述

大家好，

- 当我使用查询时：

Hi everyone,
- When i use query:

SELECT * FROM TABLE WHERE col > '20160505'

它没有有任何数据

col类型：日期时间

我不知道为什么。

谁可以帮我解决这个问题？非常感谢大家



我的尝试：



- 但是当我使用查询时：

It don't has any data
Type of col: Datetime
I don't know why.
Who can help me with this problem? Thank you so much everyone

What I have tried:

- But when i use query :

SELECT * FROM TABLE with(nolock) WHERE col > '20160505'

它有数据

- 或者，当我查询除日期时间类型以外的其他条件时，它有数据

It has data
- OR when i query with another condition except datetime type, it has data

推荐答案

如果结果不同，首先要记住的是事务仍然是正在进行或者如果你照看交易但仍然没有数据，就会发生回滚。



NOLOCK是邪恶的。它将向您显示处于交易中间的数据，在我看来应该避免。有关详细信息，请查看应考虑什么当使用NOLOCK提示时 [ ^ ]



作为旁注，查询似乎与日期时间数据有关。如果列类型确实是datetime，而不是隐式varchar转换，请使用 CAST和CONVERT（Transact-SQL） [ ^ ]。例如：

If the results are different, the first thing that comes in mind is that the transaction is still ongoing or if you look after the transaction and still no data, a rollback has occurred.

NOLOCK is evil. It will show you data that is in the middle of a transaction and in my opinion it should be avoided. For further information, have a look at What should be considered when NOLOCK hint is used[^]

As a side note, the query seems peculiar concerning the datetime data. If the column type really is datetime, instead implicit varchar conversion, use CAST and CONVERT (Transact-SQL)[^]. For example:

...WHERE col > CONVERT(date, '05/05/2016', 101)

将使您的代码更强大，性能更好。



另一方面，如果列的类型是varchar并且您在其中存储日期，请考虑更改类型专栏。

This will make your code more robust and better performing.

On the other hand if the type of the column is varchar and you store dates in it, consider changing the type of the column.

这篇关于[sql] - 没有nolock的查询时没有数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！