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

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 (scalar¹) 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 ²

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 DISTINCT³

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