/*

Based on a table Product.Products and Product.BillOfMaterials.

*/

/*

SELECT * FROM Product.Products WHERE ID = 738

SELECT * FROM Product.vBillOfMaterials WHERE ProductAssemblyID = 738 ORDER BY BOMLevel

SELECT * FROM Product.vBillOfMaterialsName WHERE ProductAssemblyID = 738 ORDER BY BOMLevel

*/

CREATE VIEW [Product].[vBillOfMaterials]

AS

WITH BOM_cte 

AS

(

SELECT b.ProductID AS ProductAssemblyID, ID, ProductID, ComponentID, ItemNo, Quantity, LastUpdate, LastUpdateBy, AlternativeAccy, 0 AS BOMLevel 

FROM Product.BillOfMaterials b

UNION ALL

SELECT cte.ProductAssemblyID, b.ID, b.ProductID, b.ComponentID, b.ItemNo, b.Quantity, b.LastUpdate, b.LastUpdateBy, b.AlternativeAccy, BOMLevel + 1 AS BOMLevel

        FROM [BOM_cte] cte

            INNER JOIN [Product].[BillOfMaterials] b 

            ON b.ProductID = cte.[ComponentID]

)

SELECT BOM.* FROM BOM_cte BOM

The following example shows how to activate AWE and configure a limit of 1 GB for min server memory and 6 GB for max server memory.
First, configure AWE:
Copy Code

sp_configure 'show advanced options', 1
RECONFIGURE
GO

sp_configure 'awe enabled', 1
RECONFIGURE
GO

After SQL Server restarts, the following message is written to the SQL Server error log: "Address Windowing Extensions enabled."
Next, configure memory:
Copy Code

sp_configure 'min server memory', 1024
RECONFIGURE
GO

sp_configure 'max server memory', 6144
RECONFIGURE
GO