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.
CAST() or CONVERT() it to your required precision