A
Yes, it is a valid physical design modification to accommodate more flexible locking behavior.
If you join the two tables and expose it to your ORM with a view, I don't think the ORM will know the difference (unless it attempts to look at the view metadata like looking for primary key or something during code generation).
Your view should be updatable, and even if it isn't due to some peculiarity of updatable view limitations can always be made updatable using INSTEAD OF triggers.
And yes, I would certainly consider it. Whether I would consider it before actually doing load testing on a single table design, I'm not sure.
Here's an example in SQL Server:
CREATE TABLE main
( ID INT IDENTITY NOT NULL
,DATA VARCHAR(255)
,CONSTRAINT PK_MAIN PRIMARY KEY CLUSTERED (ID ASC)
);
CREATE TABLE aux
( ID INT NOT NULL
,MOREDATA VARCHAR(255)
,CONSTRAINT PK_AUX PRIMARY KEY CLUSTERED (ID ASC)
);
GO
CREATE VIEW unified
AS
SELECT main.*, aux.MOREDATA
FROM main
INNER JOIN aux
ON aux.ID = main.ID;
GO
INSERT INTO main (DATA) VALUES ('somedata');
INSERT INTO aux (ID, MOREDATA) VALUES (SCOPE_IDENTITY(), 'some more data');
Then:
BEGIN TRANSACTION;
SELECT dm_tran_locks.request_session_id,
dm_tran_locks.resource_database_id,
DB_NAME(dm_tran_locks.resource_database_id) AS dbname,
CASE
WHEN resource_type = 'object'
THEN OBJECT_NAME(dm_tran_locks.resource_associated_entity_id)
ELSE OBJECT_NAME(partitions.OBJECT_ID)
END AS ObjectName,
partitions.index_id,
indexes.name AS index_name,
dm_tran_locks.resource_type,
dm_tran_locks.resource_description,
dm_tran_locks.resource_associated_entity_id,
dm_tran_locks.request_mode,
dm_tran_locks.request_status
FROM sys.dm_tran_locks
LEFT JOIN sys.partitions ON partitions.hobt_id = dm_tran_locks.resource_associated_entity_id
LEFT JOIN sys.indexes ON indexes.OBJECT_ID = partitions.OBJECT_ID AND indexes.index_id = partitions.index_id
WHERE resource_associated_entity_id > 0
AND resource_database_id = DB_ID()
ORDER BY request_session_id, resource_associated_entity_id;
SELECT *
FROM unified;
UPDATE unified
SET MOREDATA = 'changed data'
WHERE ID = 1;
SELECT *
FROM unified;
SELECT dm_tran_locks.request_session_id,
dm_tran_locks.resource_database_id,
DB_NAME(dm_tran_locks.resource_database_id) AS dbname,
CASE
WHEN resource_type = 'object'
THEN OBJECT_NAME(dm_tran_locks.resource_associated_entity_id)
ELSE OBJECT_NAME(partitions.OBJECT_ID)
END AS ObjectName,
partitions.index_id,
indexes.name AS index_name,
dm_tran_locks.resource_type,
dm_tran_locks.resource_description,
dm_tran_locks.resource_associated_entity_id,
dm_tran_locks.request_mode,
dm_tran_locks.request_status
FROM sys.dm_tran_locks
LEFT JOIN sys.partitions ON partitions.hobt_id = dm_tran_locks.resource_associated_entity_id
LEFT JOIN sys.indexes ON indexes.OBJECT_ID = partitions.OBJECT_ID AND indexes.index_id = partitions.index_id
WHERE resource_associated_entity_id > 0
AND resource_database_id = DB_ID()
ORDER BY request_session_id, resource_associated_entity_id;
COMMIT TRANSACTION;
