计算列帮助-TSQL [英] Computed Column Help - TSQL
问题描述
CREATE TABLE [dbo].[tblLocations](
[latitude] [float] NOT NULL,
[longitude] [float] NOT NULL,
[location] [varchar](500) NOT NULL,
[timestamp] [datetime] NOT NULL,
[point] [geography] AS geography::Point(latitude, longitude, 4326) NOT NULL
)
在 AS
。
这不是您声明计算列的方式吗?
Isn't this how you declare a computed column?
推荐答案
您自己无需声明数据类型
或可为空
You don't declare the datatype
or nullability yourself
CREATE TABLE [dbo].[tblLocations](
[latitude] [float] NOT NULL,
[longitude] [float] NOT NULL,
[location] [varchar](500) NOT NULL,
[timestamp] [datetime] NOT NULL,
[point] AS geography::Point(latitude, longitude, 4326)
)
通常,SQL Server将假定该列为可空除非您在公式周围添加 ISNULL()
。
In general SQL Server will assume the column is nullable unless you add an ISNULL()
around the formula.
但是我刚刚尝试了以下列定义
However I just tried the following column definition
[point2] AS ISNULL(geography::Point(latitude, longitude, 4326),
geography::STGeomFromText('LINESTRING(-122.360 47.656, -122.343 47.656)', 4326))
,并且在 sys.computed_columns
中仍然显示为 is_nullable
看起来适用于CLR数据类型(可能是因为SQL Server不相信它们具有确定性)。
and that still shows up as is_nullable
in sys.computed_columns
so it doesn't look like that applies to CLR datatypes (probably as SQL Server doesn't trust these to be deterministic).
编辑:但是它 NOT NULL ,只要计算列被标记为 PERSISTED
即
However it is syntactically valid to specify NOT NULL
as long as the computed column is marked as PERSISTED
i.e.
[point] AS geography::Point(latitude, longitude, 4326) PERSISTED NOT NULL
在这种特殊情况下,尝试创建具有此类定义的表给出运行时错误。
In this particular case however attempting to create a table with such a definition gives a runtime error.
表
'foo'中的计算列'point'不能持久,因为
列类型地理是一种
非字节排序的CLR类型。
Computed column 'point' in table 'foo' cannot be persisted because the column type, 'geography', is a non-byte-ordered CLR type.
这篇关于计算列帮助-TSQL的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!