backend-mssql {dbplyr} | R Documentation |
Backend: SQL server
Description
See vignette("translation-function")
and vignette("translation-verb")
for
details of overall translation technology. Key differences for this backend
are:
-
SELECT
usesTOP
notLIMIT
Automatically prefixes
#
to create temporary tables. Add the prefix yourself to avoid the message.String basics:
paste()
,substr()
,nchar()
Custom types for
as.*
functionsLubridate extraction functions,
year()
,month()
,day()
etcSemi-automated bit <-> boolean translation (see below)
Use simulate_mssql()
with lazy_frame()
to see simulated SQL without
converting to live access database.
Usage
simulate_mssql(version = "15.0")
Arguments
version |
Version of MS SQL to simulate. Currently only, difference is
that 15.0 and above will use |
Bit vs boolean
SQL server uses two incompatible types to represent TRUE
and FALSE
values:
The
BOOLEAN
type is the result of logical comparisons (e.g.x > y
) and can be usedWHERE
but not to create new columns inSELECT
. https://docs.microsoft.com/en-us/sql/t-sql/language-elements/comparison-operators-transact-sqlThe
BIT
type is a special type of numeric column used to storeTRUE
andFALSE
values, but can't be used inWHERE
clauses. https://learn.microsoft.com/en-us/sql/t-sql/data-types/bit-transact-sql?view=sql-server-ver15
dbplyr does its best to automatically create the correct type when needed, but can't do it 100% correctly because it does not have a full type inference system. This means that you many need to manually do conversions from time to time.
To convert from bit to boolean use
x == 1
To convert from boolean to bit use
as.logical(if(x, 0, 1))
Examples
library(dplyr, warn.conflicts = FALSE)
lf <- lazy_frame(a = TRUE, b = 1, c = 2, d = "z", con = simulate_mssql())
lf %>% head()
lf %>% transmute(x = paste(b, c, d))
# Can use boolean as is:
lf %>% filter(c > d)
# Need to convert from boolean to bit:
lf %>% transmute(x = c > d)
# Can use boolean as is:
lf %>% transmute(x = ifelse(c > d, "c", "d"))