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
)

Giving me bad syntax at the word AS.

Isn't this how you declare a computed column?

回答1:

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) 
)

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))

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).

Edit: 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.

Computed column 'point' in table 'foo' cannot be persisted because the column type, 'geography', is a non-byte-ordered CLR type.