DataTamer: SQL Server Services

Property Bag

SET NOCOUNT ON
GO


CREATE TABLE [dbo].[tester]
	(
	[testerID] INT IDENTITY(1,1) NOT NULL CONSTRAINT [tester_primary] PRIMARY KEY CLUSTERED
	,[CreateDate] DATETIME NOT NULL CONSTRAINT [tester_CreateDate] DEFAULT (GETUTCDATE())
	,[properties] XML NULL
	)
GO


DECLARE
	@row_count INT = 1

TRUNCATE TABLE [dbo].[tester]

WHILE @row_count <= 1000
	BEGIN
	INSERT [dbo].[tester] ([properties])
	SELECT
		(
		SELECT
			[Name] AS [@Name]
			,[Units] AS [@Units]
			,[Value] AS [@Value]
		FROM
			(
			SELECT
				'Inventory #' AS [Name]
				,'ABC' AS [Value]
				,'N/A' AS [Units]
			UNION ALL
			SELECT
				'Cost' AS [Name]
				,'15.00' AS [Value]
				,'$' AS [Units]
			UNION ALL
			SELECT
				'DeleteMe' AS [Name]
				,'999' AS [Value]
				,'CAD' AS [Units]
			) AS A
		FOR XML PATH('Property')
		)
	
	SELECT @row_count = @row_count + 1
	END
GO


SELECT TOP 100 * FROM [dbo].[tester]
GO


-- select multiple records, must pivot
SELECT TOP 100
	[testerID]
	,[CreateDate]
	,[properties].value('(/CustomProperties/Property[@Name="Inventory
#"]/@Value)[1]','nvarchar(200)') AS [Inventory #]
	,[properties].value('(/CustomProperties/Property[@Name="Inventory
#"]/@Units)[1]','nvarchar(200)') AS [Inventory # Units]
	
,[properties].value('(/CustomProperties/Property[@Name="Cost"]/@Value)[1]','
nvarchar(200)') AS [Cost]
	
,[properties].value('(/CustomProperties/Property[@Name="Cost"]/@Units)[1]','
nvarchar(200)') AS [Cost Units]
	
,[properties].value('(/CustomProperties/Property[@Name="Blank"]/@Value)[1]',
'nvarchar(200)') AS [Blank]
	
,[properties].value('(/CustomProperties/Property[@Name="Blank"]/@Units)[1]',
'nvarchar(200)') AS [Blank Units]
FROM
	[dbo].[tester]
GO


-- search multiple records, must pivot
SELECT TOP 100
	[testerID]
	,[CreateDate]
	
,[properties].value('(/CustomProperties/Property[@Value="15.00"]/@Name)[1]',
'nvarchar(200)') AS [PropertyName]
	
,[properties].value('(/CustomProperties/Property[@Value="15.00"]/@Value)[1]'
,'nvarchar(200)') AS [PropertyValue]
	
,[properties].value('(/CustomProperties/Property[@Value="15.00"]/@Units)[1]'
,'nvarchar(200)') AS [PropertyUnits]
FROM
	[dbo].[tester]
WHERE
	[properties].exist('/CustomProperties/Property[@Value="15.00"]') = 1 GO


-- select one record, must shred
SELECT
	[testerID]
	,[CreateDate]
	,x.y.value('@Name','varchar(500)') AS [PropertyName]
	,x.y.value('@Value','varchar(500)') AS [PropertyValue]
	,x.y.value('@Units','varchar(500)') AS [PropertyUnits] FROM
	[dbo].[tester]
	CROSS APPLY [properties].nodes(N'/CustomProperties/Property') AS
x(y) -- select all Property nodes below CustomProperties WHERE
	[testerID] = 1
GO


CREATE PRIMARY XML INDEX [tester_xml]
	ON [dbo].[tester] ([properties])
GO

set statistics io on
-- select distinct list of all custom properties, must shred SELECT DISTINCT
	x.y.value('@Name','varchar(500)') AS [PropertyName] FROM
	[dbo].[tester]
	CROSS APPLY [properties].nodes(N'/Property') AS x(y) -- select all Property nodes
-- NO INDEX: Table 'tester'. Scan count 9, logical reads 88, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
-- WITH INDEX: Table 'tester'. Scan count 1, logical reads 31, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
set statistics io off
GO


UPDATE
	[dbo].[tester]
SET
	[properties].modify('delete /Property[@Name="DeleteMe"]') GO SELECT TOP 100 * FROM [dbo].[tester] GO


DROP TABLE [dbo].[tester]
GO
DataTamer: SQL Server Services

Property Bag

Articles
