The legacy wiki pages are here for reference purposes only.
For up-to-date information make sure to check the documentation section.

Umbraco 4.6 will feature support for SQL Server CE 4 which is an embedded version of SQL Server that works on shared hosting and in medium trust. There are certain differences in supported features between the "real" SQL Server and the CE version which can cause issues in both the core of Umbraco and in 3rd party packages. We hope to find all the issues in the core of Umbraco for the final release of 4.6 and we'll use this document to describe the things we've found and what we've done to fix the issues. This is meant as an inspiration for 3rd party package developers to update their packages if needed.

Issues in SQL Statements

IF statements are NOT supported
For instance when Umbraco Courier 1.2 installs it'll use the following SQL to update permissions which will fail:

"if exists(select id from umbracoUserType where id = 1 and not userTypeDefaultPermissions like '%^') BEGIN update umbracoUserType set userTypeDefaultPermissions = userTypeDefaultPermissions + '^' where id = 1 END"

(still working on a work around)

Nested queries are NOT supported

For instance this optimized call is used to fetch documents for the tree view and will fail because of nested select statements:

                 (select count(id) from umbracoNode where parentId = @id) as Children,
                 (select Count(published) as tmp from cmsDocument where published = 1 And nodeId = @id) as Published,


Workaround was to move the nested select into an left outer join and use CASE statements in the select:

CASE WHEN (>0) THEN ELSE 0 END as Children,
CASE WHEN (publishedTable.publishedTotal>0) THEN publishedTable.publishedTotal ELSE 0 END as Published,

When using TOP queries you'll need to use TOP as a method call

This will fail:

SELECT top 10 * from umbracoNode

Workaround is to wrap the number of rows in parentheses:

SELECT top(10) * from umbracoNode


Distinct in count is not supported

For instance:

SELECT count(distinct from ....


(work around is simply not to use distinct cals with the count method)

Issues in working with SQL CE4 data in .NET

When using a DataReader, the cursor can't re-read

A typical pattern when reading data which would fail because the cursor in SQL CE can only move forward could be something like this:

using(IRecordsReader dr = SqlHelper.ExecuteReader("Select alias,design,master from cmsTemplate where nodeId = " + this.Id))
            if (dr.HasRecords)

The HasRecords property will read the first row which means that in order to call the Read() method the cursor will need to move back which isn't supported in SQL CE 4. Workaround is a pattern where you assign the bool value of the Read() method and use that to check for data (it'll return false if no data has been read):

using(IRecordsReader dr = SqlHelper.ExecuteReader("Select alias,design,master from cmsTemplate where nodeId = " + this.Id))
            bool hasRows = dr.Read();
            if (hasRows)

This is also important when reading data using a while loop for instance is the community mothership for Umbraco, the open source cms. With a friendly forum for all your questions, a comprehensive documentation and a ton of packages from the community. This site is running Umbraco version 7.5.2