Umbraco, supported T-SQL in SQL Server vs SQLite

By Markus Johansson
2022-07-16

Since Umbraco v10 ships with SQLite and no more SQLCE I figured it would be a good idea upgrade my old "compare post" about SQL Server vs SQLCE.

There is a big difference between the databases in that the data types are very different but I was surprised to see that SQLite supports many types of queries that SQL CE did not. The data types in SQLite is basically:

  • NULL
  • INTEGER
  • REAL
  • TEXT
  • BLOB

This will be a "work in progress" blog post about the differences between SQL Server and SQLite and I will keep updating it when I find new cases.

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

x

SELECT with Subquery in WHERE-statement

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

x

x

SELECT with Subquery (scalar1) 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

x5

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

 

SELECT TOP 

SELECT TOP 5 * FROM cmsContentType

SELECT * FROM cmsContentType LIMIT 5

x

x5

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

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 Id from umbracoContentVersion ORDER BY Id

X

X

SELECT COUNT with DISTINCT3

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

 

CAST4

SELECT CAST(id as text) textId FROM umbracoNode

X

X*

DATALENGTH6

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

 

 

....

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
4. Since the column data types are not the same (SQL Server has nvarchar, varchar while SQLite uses TEXT) this needs to be handled.
5. SELECT TOP is not supported in SQLite. But if the subquery was SELECT nodeId from umbracoContentVersion LIMIT 1 it would work.
6. SQLite calls this function length, used like so: length([columnName])

 

 






More blog posts



15 januari 2021

Umbraco and MiniProfiler