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 TRANSACTIONin the 1st session to let the SELECTs go.