如何在SELECT中创建非空的geography/geometry/image/XML类型虚拟字段？

Great question! You absolutely can create non-nullable columns of geography, geometry, image, and xml types in a SELECT statement or view—you just need to adjust your approach, since ISNULL doesn't play nicely with these specialized types the way it does with scalar types. The key is to ensure you're passing explicit, non-null instances of these types rather than relying on implicit conversions that confuse SQL Server's nullability inference.

Breakdown by Type #

1. geography & geometry #

The issue with your original ISNULL approach is that passing a string literal (like 'POLYGON EMPTY') triggers an implicit conversion, which SQL Server interprets as potentially nullable. Instead, use the type's static constructor methods to create an explicit non-null instance:

-- Non-null geography (with SRID 4326, the standard for WGS84)
SELECT geography::STGeomFromText('POLYGON EMPTY', 4326) AS NonNullGeography;

-- Non-null geometry (with SRID 0 for local planar data)
SELECT geometry::STGeomFromText('POLYGON EMPTY', 0) AS NonNullGeometry;

If you still want to use ISNULL for consistency with other columns, make sure the second argument is an explicit instance of the type (not a string):

SELECT ISNULL(CAST(NULL AS geography), geography::STGeomFromText('POLYGON((1 1, 3 3, 3 1, 1 1))', 4326)) AS NonNullGeography2;

2. image (Deprecated) #

First, note that image is a deprecated type—Microsoft recommends using varbinary(MAX) instead for new development. If you must work with image, you need to pass an explicit image-typed constant instead of a string/numeric literal:

-- Non-null image (using a minimal binary value)
SELECT CAST(0x00 AS image) AS NonNullImage;

-- ISNULL variant
SELECT ISNULL(CAST(NULL AS image), CAST(0x123567AB AS image)) AS NonNullImage2;

3. xml #

Similar to spatial types, passing a string literal to ISNULL causes implicit conversion confusion. Instead, explicitly cast your XML string to the xml type to create a non-null instance:

-- Non-null xml with a simple root element
SELECT CAST('<root><sample>Test</sample></root>' AS xml) AS NonNullXml;

-- ISNULL variant
SELECT ISNULL(CAST(NULL AS xml), CAST('<?xml version="1.0" encoding="utf-8"?><html></html>' AS xml)) AS NonNullXml2;

Full Test View #

Here's a complete view implementation that creates non-nullable columns for all four types, plus a query to verify nullability:

DROP VIEW IF EXISTS dbo.TestTypes;
GO
CREATE VIEW dbo.TestTypes AS
SELECT 
    -- Non-null geography
    geography::STGeomFromText('POLYGON EMPTY', 4326) AS NonNullGeography,
    ISNULL(CAST(NULL AS geography), geography::STGeomFromText('POLYGON((1 1, 3 3, 3 1, 1 1))', 4326)) AS NonNullGeography2,
    -- Non-null geometry
    geometry::STGeomFromText('POLYGON EMPTY', 0) AS NonNullGeometry,
    ISNULL(CAST(NULL AS geometry), geometry::STGeomFromText('POLYGON((1 1, 3 3, 3 1, 1 1))', 0)) AS NonNullGeometry2,
    -- Non-null image (deprecated)
    CAST(0x00 AS image) AS NonNullImage,
    ISNULL(CAST(NULL AS image), CAST(0x123567AB AS image)) AS NonNullImage2,
    -- Non-null xml
    CAST('<root><sample>Test</sample></root>' AS xml) AS NonNullXml,
    ISNULL(CAST(NULL AS xml), CAST('<?xml version="1.0" encoding="utf-8"?><html></html>' AS xml)) AS NonNullXml2
GO

-- Verify all columns are marked NOT NULL
SELECT 
    COLUMN_NAME, 
    IS_NULLABLE 
FROM INFORMATION_SCHEMA.COLUMNS 
WHERE TABLE_NAME = 'TestTypes';

Key Notes #

• For spatial types (geography/geometry), always specify a valid SRID (spatial reference ID) to avoid unexpected behavior.
• Replace image with varbinary(MAX) for new projects—it's fully supported, has better performance, and follows modern SQL Server best practices.
• All these methods avoid dependencies on other database objects, which aligns with your requirement to skip dependency handling.

内容的提问来源于stack exchange，提问作者Stefan Steiger