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

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 concrete list of queries and map out what does and what doesn't depending on the database type.

 

Description

Query

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

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()

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.

 

 


PS. If you like the blog post - please share it =D








Tillbaka