Umbraco, supported T-SQL in SQL Server vs SQLite

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 cmsDictionary) as Test
FROM umbracoNode

x

SELECT with Subquery in WHERE-statement

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

x

x

SELECT with Subquery (scalar¹) in WHERE

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

SQLite: WHERE Id = (SELECT nodeId from umbracoContentVersion LIMIT 1)

x

SELECT with Inner Join

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

x

SELECT with STUFF-function

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

x

SELECT TOP 

SELECT TOP 5 * FROM cmsContentType

SELECT * FROM cmsContentType LIMIT 5

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 ²

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

X

X

SELECT DISTINCT-feature

SELECT DISTINCT Id from umbracoContentVersion ORDER BY Id

X

X

SELECT COUNT with DISTINCT³

SELECT COUNT(DISTINCT thumbnail) FROM cmsContentType

X

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.text, d.preventCleanup FROM umbracoNode as n
LEFT OUTER JOIN umbracoContentVersion as d ON n.id = d.nodeId

X

X

Parameter Alias declared as string

SELECT id as 'foo' FROM umbracoNode

X

X

Parameter Alias declared inline

SELECT id as foo FROM umbracoNode

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

DATEPART

SELECT DATEPART(YEAR, createDate) AS [Year]
FROM umbracoNode

X

CAST⁴

SELECT CAST(id as text) textId FROM umbracoNode

X

X*

DATALENGTH⁶

SELECT DATALENGTH([text]) textId FROM umbracoNode

X

X*

UNION ALL

SELECT id,userLogin as [email] FROM umbracoUser
UNION ALL
SELECT nodeId as id,email FROM cmsMember

X

X

CONVERT

SELECT CONVERT(nvarchar(50), uniqueId) as data3 FROM umbracoNode

X