Specify decimal precision of column in a SQL Server indexed view

1.4k Views Asked by At

I am wondering if it's possible to override the default column precision of columns within an indexed view. The view seems to always create the columns with the largest possible precision.

A complete run-able example is below:

-- drop and recreate example table and view if they exist
IF EXISTS(SELECT * FROM sys.objects WHERE name = 'ExampleIndexedView')
    DROP VIEW [dbo].[ExampleIndexedView]

IF EXISTS(SELECT * FROM sys.objects WHERE name = 'Example')
    DROP TABLE [dbo].[Example]

-- create example table
CREATE TABLE [dbo].[Example](
    [UserID] [int],
    [Amount] [decimal](9, 2)
) ON [PRIMARY]

-- insert sample rows
INSERT INTO [dbo].[Example] ([UserID], [Amount]) VALUES (1, 10)
INSERT INTO [dbo].[Example] ([UserID], [Amount]) VALUES (2, 20)
INSERT INTO [dbo].[Example] ([UserID], [Amount]) VALUES (3, 30)
INSERT INTO [dbo].[Example] ([UserID], [Amount]) VALUES (1, 15)
INSERT INTO [dbo].[Example] ([UserID], [Amount]) VALUES (1, 2.5)
GO

-- create indexed view
CREATE VIEW [dbo].[ExampleIndexedView]
WITH SCHEMABINDING
AS

SELECT
    e.UserID as UserID
    ,SUM(ISNULL(e.[Amount], 0)) as [Amount]
    ,COUNT_BIG(*) as [Count]        --Required for indexed views
FROM [dbo].[Example] e
GROUP BY 
    e.UserID
GO

CREATE UNIQUE CLUSTERED INDEX [CI_ExampleIndexedView]
    ON [dbo].[ExampleIndexedView]
        ([UserID])

-- show stats for view
exec sp_help [ExampleIndexedView]

This results in a view with the columns:

UserID(int, null)  
Amount(decimal(38,2), null)  

I understand why the view would automatically use the largest possible storage type for a SUM column, however let's say I know that the summing of that Amount column will never exceed the limits of a decimal(19, 2) - is there a way I can force the view to create the column as decimal(19, 2) instead of decimal(38, 2)?

decimal(38, 2) takes 17 bytes to store, decimal(19,2) only takes 9 bytes. As a test I duplicated my indexed view into a regular table where I used decimal(19,2) and the overall storage space saving was around 40%, so it seems like it would be a worthwhile thing to do for a view that contains a large number of decimal aggregations.

Edit: Posted a complete run-able example that creates an example table, populates it with a few rows, then creates an indexed view on that table. The result is that the Amount column in the indexed view is decimal(38,2), i would like to find a way to force it to decimal(19,2) for space saving reasons.

1

There are 1 best solutions below

8
On

CAST() or CONVERT() it to your required precision

Select 
    UserID, 
    CONVERT(DECIMAL(9,2), SUM(Amount)) as Amount, 
FROM 
    Example
GROUP BY 
    UserID