T-SQL compare SQL Server vs SQL CE (Compact Edition)

By Markus Johansson
2019-10-22

This blog post came out of my work with packages for Umbraco CMS, but it's totally applicable even if you're not working with Umbraco. When building packages for Umbraco that works with custom tables you have to keep in mind that CMS supports different databases. We need to make sure that our queries works on both the standard SQL Server and with SQLCE.

I've decided to try to create a list of queries and map out what does and what doesn't work depending on the database type.

 

Description

Query-example

SQL Serv.

SQLCE

Regular SELECT

SELECT * FROM umbracoNode

x

x

SELECT with Subquery in select-statement

SELECT id
(SELECT COUNT(pk) from cmsDataType) as Test
FROM umbracoNode

x

 

SELECT with Subquery in WHERE-statement

SELECT id, [text]
FROM umbracoNode
WHERE Id IN (SELECT nodeId from cmsDataType)

x

x

SELECT with Subquery (scalar1) in WHERE

SELECT id, [text]
FROM umbracoNode
WHERE Id = (SELECT TOP 1 nodeId from cmsDataType)

x

 

SELECT with Inner Join

SELECT n.id, n.[text]
FROM umbracoNode n
INNER JOIN cmsDataType d ON d.nodeId = n.id

x

x

SELECT with STUFF-function

SELECT id,[text], STUFF([text],1,1, '')
FROM umbracoNode

x

x

SELECT TOP 

SELECT TOP 5 * FROM cmsContentType

x

x

SELECT with “FOR XML PATH”.
Will format as a scalar XML-payload

SELECT TOP 10 id,[text]
FROM umbracoNode
ORDER By [text]
FOR XML Path('node')

x

 

Row_Number() / Paging with
SELECT with ROW_NUMBER()

SELECT id,[text],
ROW_NUMBER() OVER (ORDER BY [text]) AS RowNumber
FROM umbracoNode
ORDER By [text]

x

 

Paging with
SELECT WITH OFFSET AND FETCH 2

SELECT id,[text]
FROM umbracoNode
ORDER BY id
OFFSET 0 ROWS
FETCH NEXT 10 ROWS ONLY

x

x

SELECT DISTINCT-feature

SELECT DISTINCT ContentId from cmsContentVersion ORDER BY ContentId

x

x

SELECT COUNT with DISTINCT3

SELECT COUNT(DISTINCT thumbnail) FROM cmsContentType

x

 

SELECT with LIKE in Where

SELECT *
FROM umbracoNode WHERE [text] LIKE '%st%'

x

x

SELECT with Subquery in FROM-statement.

SELECT count(contentId)
FROM (SELECT DISTINCT contentId FROM cmsContentVersion) cmsContentVersion

x

x

SELECT with LEFT OUTER JOIN

SELECT n.id, n.[text], d.dbType, d.pk FROM umbracoNode as n
LEFT OUTER JOIN cmsDataType as d ON n.id = d.nodeId

x

x

Parameter Alias declared as string

SELECT id as 'foo' FROM bar

x

 

Parameter Alias declared inline

SELECT id as foo FROM bar

X

X

SELECT with INNER JOIN Subquery in FROM

SELECT * FROM cmsTemplate as t
INNER JOIN (SELECT templateId, count(id) as total
FROM umbracoDocumentVersion WHERE published = 1
GROUP by templateId) as dv ON dv.templateId = t.nodeId

X

X

CROSS APPLY

SELECT id,userName,ugr.Roles FROM umbracoUser u
CROSS APPLY (SELECT COUNT(ug.userId) as Roles FROM umbracoUser2UserGroup AS ug WHERE ug.userId = u.id) as ugr
ORDER BY ugr.Roles DESC

X

X

 

1. A scalar query is a query that returns one row consisting of one column.
2. Works on SQL Server 2012+, older versions need the ROW_NUMBER()
3. Another version of the query with a join in the FROM-caluse works on SQL CE: SELECT COUNT(thumbs.total) as Total FROM (SELECT DISTINCT thumbnail as total FROM cmsContentType) as thumbs

I hope that this little table is useful and please, feel free to drop a comment if you have any feedback or suggestions on things I've missed.

 

 






More blog posts



15 januari 2021

Umbraco and MiniProfiler