Simple EAV structure will be way more effective than dynamic schema or XML.
With appropriate indexing searching for a value in EAV structure will be much faster than in XML.
And more flexible than XML, as it would allow storing non-varchar values in its native form, without converting to a string format.
CREATE TABLE dbo.CustomField (
ID SMALLINT PRIMARY KEY,
FieldName NVARCHAR(100) COLLATE DATABASE_DEFAULT NOT NULL
)
CREATE INDEX IX_Fieldname ON dbo.CustomField (Fieldname)
-- Populating lookup-data
INSERT INTO dbo.CustomField ( ID, FieldName )
SELECT 1, N'Location'
UNION
SELECT 2, N'Address'
CREATE TABLE dbo.YourEntity_CustomField (
EntityID INT NOT NULL,
CustomFieldID SMALLINT NOT null,
FieldValue SQL_VARIANT NOT NULL,
PRIMARY KEY (EntityID, CustomFieldID, FieldValue),
--FOREIGN KEY (EntityID) REFERENCES dbo.YourEntity(ID)
)
CREATE INDEX IX_FieldValue ON dbo.YourEntity_CustomField (FieldValue)
-- Adding a "before update" record
INSERT INTO dbo.YourEntity_CustomField
( EntityID, CustomFieldID, FieldValue )
SELECT 100, -- ID from YourEntity table
CF.ID,
'New Dehli'
FROM dbo.CustomField cf
WHERE cf.FieldName = 'Location'
Now, session 1 is updating data:
BEGIN TRANSACTION
UPDATE ECF
SET FieldValue = 'Mumbai'
FROM dbo.YourEntity_CustomField ECF
INNER JOIN dbo.CustomField cf ON cf.ID = ECF.CustomFieldID
WHERE ecf.EntityID = 100
AND cf.FieldName = N'Location'
session 2 is adding a column to the TABLE
DECLARE @NewAddress NVARCHAR(500)
SET @NewAddress = N'Some New Address'
INSERT INTO dbo.YourEntity_CustomField
( EntityID, CustomFieldID, FieldValue )
SELECT 100, CF.ID, @NewAddress
FROM dbo.CustomField cf
WHERE cf.FieldName = 'Address'
AND NOT EXISTS (SELECT * FROM dbo.YourEntity_CustomField ECF
WHERE ECF.EntityID = 100
AND ECF.CustomFieldID = CF.ID
AND ECF.FieldValue = @NewAddress)
GO
No problem. No blocking.
Completed instantly.
You may wish to add another field in the 2nd session while the 1st session is waiting for the transaction to complete:
DECLARE @DateFrom DATETIME
SET @DateFrom = GETDATE()
INSERT INTO dbo.CustomField ( ID, FieldName )
SELECT 3, N'DateFrom'
INSERT INTO dbo.YourEntity_CustomField
( EntityID, CustomFieldID, FieldValue )
SELECT 100, CF.ID, @DateFrom
FROM dbo.CustomField cf
WHERE cf.FieldName = 'DateFrom'
AND NOT EXISTS (SELECT * FROM dbo.YourEntity_CustomField ECF
WHERE ECF.EntityID = 100
AND ECF.CustomFieldID = CF.ID
AND ECF.FieldValue = @DateFrom)
With a simple PIVOT you may choose which columns to select in each particular query:
SELECT EntityID, [Location]
FROM (
SELECT EntityID, cf.FieldName, ECF.FieldValue
FROM dbo.YourEntity_CustomField ECF
INNER JOIN dbo.CustomField cf ON ECF.CustomFieldID = CF.ID
WHERE ECF.EntityID = 100
) ST
PIVOT (
MAX(FieldValue)
FOR FieldName IN ([Location])
) AS PT
SELECT EntityID, [Location], [Address], [DateFrom]
FROM (
SELECT EntityID, cf.FieldName, ECF.FieldValue
FROM dbo.YourEntity_CustomField ECF
INNER JOIN dbo.CustomField cf ON ECF.CustomFieldID = CF.ID
WHERE ECF.EntityID = 100
) ST
PIVOT (
MAX(FieldValue)
FOR FieldName IN ([Location], [Address], [DateFrom])
) AS PT
But you need to
COMMIT TRANSACTION
in the 1st session to let the SELECTs go.
_____________
Code for TallyGenerator