September 23, 2018 at 7:40 pm
I have an SCD2 table like so:
TableName_PK Recnum BusKeyA BusKeyB BusKeyC Valid_From Valid_To
TableName_PK is an identity column.
Recnum is a surrogate key for the composite business/natural keys BusKeyA+BusKeyB+BusKeyC.
Recnum must remain consistent within the SCD2 history of the business keys.
Its default value is -1, but is replaced during trigger execution.
The below approach will have gaps in Recnum as new business keys arrive, but that's actually ok: when PK = Recnum, that's a surrogate for the first record for this set of business keys (not that I would use it as such, I'd use min(valid_from)). But in short the gap in Recnum is fine.
I've implemented this using a trigger:
ALTER TRIGGER [dbo].[trgUpdateRecnum]
ON [dbo].[TableName]
AFTER INSERT, UPDATE
AS
BEGIN
SET NOCOUNT ON;
UPDATE [dbo].[TableName]
SET recnum = t.[TableName_PK]
FROM [dbo].[TableName] t
INNER JOIN
INSERTED i
ON t.[TableName_PK] = i.[TableName_PK]
WHERE t.[recnum] = -1;
END
This is a very large table, we've just upgraded to SQL Server 2016, and I now want to use a CLUSTERED COLUMNSTORE INDEX on this table. Which doesn't support triggers.
I know I can use a NONCLUSTERED COLUMNSTORE INDEX instead, but I was wondering if there was a way to implement this functionality without needing the trigger? I could likely do this via the ETL, but would prefer to implement this at the database/table level if possible.
Could I use a sequence instead of a trigger? Some other approach?
Thanks...
September 24, 2018 at 12:32 am
Ok, I've got this approach...
In my ETL, for new rows, the recnum will be null, as it's not in the source table; for existing rows, the recnum will be populated by the target table. I'm doing a FULL OUTER JOIN to detect new/existing/deleted.
As long as my ETL code "does the right thing", this should work. I do think the trigger is more bulletproof though.
Does a nonclustered columnstore index have the same performance as a clustered columnstore index, it just takes up more space?
This approach requires two inserts, one for new rows (no ID), one for existing rows (includes ID).
Thoughts on this approach?
USE [tempdb]
GO
DROP TABLE IF EXISTS #t1
GO
DROP SEQUENCE IF EXISTS recnum
CREATE SEQUENCE recnum START WITH 1 INCREMENT BY 1
GO
CREATE TABLE #t1 (
ID BIGINT DEFAULT NEXT VALUE FOR recnum NOT NULL,
name VARCHAR(100)
)
GO
-- new rows
INSERT INTO [#t1] ([name])
SELECT name
FROM sys.databases
SELECT * FROM [#t1]
-- existing rows
INSERT INTO [#t1]
SELECT *
FROM [#t1]
ORDER BY [name]
SELECT * FROM [#t1] ORDER BY name
-- mixed
DROP TABLE IF EXISTS #t2
CREATE TABLE #t2 (ID BIGINT, name VARCHAR(100))
INSERT INTO [#t2]
VALUES (NULL,'FOO'),
(1,'master'),
(NULL,'BAR')
INSERT INTO #t1 (name)
SELECT name
FROM #t2
WHERE ID IS NULL
INSERT INTO #t1 (ID, name)
SELECT ID, name
FROM #t2
WHERE ID IS NOT NULL
SELECT * FROM [#t1] ORDER BY name
September 27, 2018 at 6:31 pm
If there is a better way I could have worded this question could you PM me?
If I need to improve my reputation in some way on SQL Server Central forums could you PM me?
"Was it me?" Or was it the way I worded the question?
Judging from the low view count, even the title was insufficient to generate interest. Or perhaps it's just a boring topic?
SQL Server Central forums is a hugely important resource for me, so I'd like to use it in the best way possible, and to contribute back to the community when I can.
For answers to this specific post I'd prefer PM.
December 1, 2018 at 10:56 pm
I think you may have just picked an off day. I don't think anyone intentionally ignored your question. If people don't like how you phrased a question, they are usually pretty quick to let you know...
Given the age on this, I'm assuming that this is a dead issue at this point... If not, post back and let us know.
December 2, 2018 at 9:47 am
I don't recall looking at this one. I can tell you that I don't have the time to look at every post.
If I had looked at it, keep in mind that a lot of us simply don't use Column Store. I don't even though I have several tables with a half billion rows in them. Looking at it now, if I did have such a need, I'm not sure I'd dedicate an SCD table to Column Store. I'd probably find a different way to increase performance.
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply