Sitecore - Get items details using Database Query

Sitecore provides Item.GetDescendants() function to fetch all descendants of any item. Sitecore APIs always fetch items with their field values (whether we required them or not) either from database or from cache, so chances of more lengthy database query execution or heavy cache clearing or creation. That's the reason why Sitecore APIs like Item.GetDescendants() or database.GetItem() are slower.

It is recommended to use Sitecore APIs for development purposes, but still Database Queries can serve better than APIs to get what exactly we need (item's children/descendants and their field values). Database Query should be used only when we need to fetch lots of items in a single call or we need to read few field values for these items.

In such cases, we can make direct queries on database to get performance improvement and get speedy results.

Different SQL Queries

1. Select all children
2. Select all descendants
3. Get path of item
4. Get any field's value
5. Get item's full details with field types

Below queries are performed on Sitecore Training Sample Site.

Select all children

Below query gets children of item '/sitecore/content/Home'. It's GUID is {4E7AB8B1-6A39-4C8C-BF5B-816F8105BFFD}

SELECT ID, [Name], Created from  [dbo].[Items]
 WHERE [ParentID] = '{4E7AB8B1-6A39-4C8C-BF5B-816F8105BFFD}'

Select all descendants

Below query gets descendatns of item '/sitecore/content/Home'. It's GUID is {4E7AB8B1-6A39-4C8C-BF5B-816F8105BFFD}

WITH [Content_Items] AS 
 (
  SELECT [ID], [Name], Created
   FROM [dbo].[Items]
   WHERE ID='{4E7AB8B1-6A39-4C8C-BF5B-816F8105BFFD}'
  UNION ALL
  SELECT  i.[ID], i.[Name], i.Created
   FROM [dbo].[Items] i
   INNER JOIN [Content_Items] ci ON ci.ID = i.[ParentID]
 )
 SELECT ID, Created
 FROM [Content_Items]

 GO

Get path of item

We can also get full path of item. Suppose, we need to get full path of Home or we need to create SiteMap frequently, this query will be a great option to generate FullPath/URL of each item.

Below is the function we can create to get Full/Relative path of any item. Here, SC_GetPath has two parameters. First parameter is ID which is the item's ID. Second parameter is to get relative path, means from this ID, the path will be generated.

CREATE FUNCTION [dbo].[SC_GetPath] 
(
 @ItemID [uniqueidentifier],
 @RootPath [uniqueidentifier]
)
RETURNS varchar(MAX)
AS
BEGIN
 DECLARE @Result varchar(MAX);

 with scpath(Name, ParentID, id)
 as
 (
  select Cast(a.Name as varchar(MAX)), a.ParentID, a.ID
  from [Items] a
  where a.ID = @ItemID
  union all
  select Cast(b.Name + '/' + c.Name as varchar(MAX)), b.ParentID, b.ID
  from [Items] b
    inner join scpath c on b.ID = c.ParentID
  where c.ParentID is not null
 )

 select top 1 @Result = '/' + d.Name  from scpath d
  where d.ID = @RootPath /*'11111111-1111-1111-1111-111111111111'*/

 RETURN @Result

END

Get full item path

Suppose we need to get full path of item Jobs.
ID of Jobs item: {6C897D6C-CE0C-422D-8955-7113A0E1B8EF}
ID of sitecore item: {11111111-1111-1111-1111-111111111111}

Sitecore, we can make query as below to get path /sitecore/content/Home/Jobs: Here,

SELECT [Name], [dbo].[SC_GetPath] (ID,'{11111111-1111-1111-1111-111111111111}') AS ItemPath 
 FROM [dbo].[Items]
 WHERE [ID] = '{6C897D6C-CE0C-422D-8955-7113A0E1B8EF}'

Get relative item path

Now, to get relative path of item Jobs starting from /Home.
ID of Jobs item: {6C897D6C-CE0C-422D-8955-7113A0E1B8EF}
ID of Home item: {4E7AB8B1-6A39-4C8C-BF5B-816F8105BFFD}

Sitecore, we can make query as below to get path /Home/Jobs: Here,

SELECT [Name], [dbo].[SC_GetPath] (ID,'{4E7AB8B1-6A39-4C8C-BF5B-816F8105BFFD}') AS ItemPath 
 FROM [dbo].[Items]
 WHERE [ID] = '{6C897D6C-CE0C-422D-8955-7113A0E1B8EF}'

Get any field's value

Getting item's field value depends on its type of field means it is Versioned, Unversioned or SharedField. Each type of field and its values are stored in different table. So, before getting any field's value, it is good to know its field type and based on it we can get execute query.

See how below function helps to return a shared field's value of an item, similarly we can write function for Versioned and Unversioned fields too:

CREATE FUNCTION [dbo].[SC_GetSharedFieldValue] 
(
 @ItemID [uniqueidentifier],
 @FieldID  [uniqueidentifier]
)
RETURNS nvarchar(max)
AS
BEGIN
 DECLARE @Result  nvarchar(max)
 SELECT @Result = value 
  FROM [dbo].[sharedFields]
  where ItemId=@ItemID
  and fieldid=@FieldID
 RETURN @Result
END

Here, Size field's ID is:{6954B7C7-2487-423F-8600-436CB3B6DC0E}. So, for getting media item's size, we can use above function as below. Here, quert list down all child media media of /sitecore/media library/Images/Banners and its ID is: {1D02B586-0B55-4C2F-AED4-A3172B81B0DA}

SELECT ID, [Name], [dbo].[SC_GetSharedFieldValue] (ID, '{6954B7C7-2487-423F-8600-436CB3B6DC0E}') AS FileSize
 FROM [dbo].[Items]
 WHERE [ParentID] = '{1D02B586-0B55-4C2F-AED4-A3172B81B0DA}'

Get item's full details

Below query will help to get all fields' values and their field types like below:

• All Shared field values
• All Versioned field values
• All Unversioned field values
• All Field Types (Like Single-Line Text, Checkbox, Multilist, etc.)

For getting item's all fields values, we have to make a UNION query on all three types of tables of three different field types. See below query to get item's all details:

DECLARE @ItemId uniqueidentifier 
SET @ItemId = '{0DE95AE4-41AB-4D01-9EB0-67441B7C2450}'
DECLARE @FieldTypeId uniqueidentifier 
SET @FieldTypeId = '{AB162CC0-DC80-4ABF-8871-998EE5D7BA32}' -- Item Id of Field Type in each item

SELECT I.Name, [dbo].[SC_GetSharedFieldValue](I.Id, @FieldTypeId) AS [Field Type],
  S.Value, '' AS [Language], '' AS [Version] FROM SharedFields S, Items I 
 WHERE S.itemid=@ItemId
 AND S.FieldID = I.ID
UNION
SELECT I.Name, [dbo].[SC_GetSharedFieldValue](I.Id, @FieldTypeId) AS [Field Type],
  U.Value, U.Language, '' AS Version FROM UnversionedFields U, Items I 
 WHERE U.itemid=@ItemId
 AND U.FieldID = I.ID
UNION
SELECT I.Name, [dbo].[SC_GetSharedFieldValue](I.Id, @FieldTypeId) AS [Field Type], 
  V.Value, V.Language, V.Version FROM VersionedFields V, Items I 
 WHERE V.itemid=@ItemId
 AND V.FieldID = I.ID

This will return all fields' values as below.
- Shared Fields do not contain Language and Version fields, so we have returned blank.
- Unversioned Fields do not contain Version, so have returned blank as Version.

SQL Database queries faster than Sitecore APIs and better than Sitecore fast query, isn't it?