1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
|
% Generated by roxygen2: do not edit by hand
% Please edit documentation in R/backend-mssql.R
\name{backend-mssql}
\alias{simulate_mssql}
\title{Backend: SQL server}
\usage{
simulate_mssql(version = "15.0")
}
\arguments{
\item{version}{Version of MS SQL to simulate. Currently only, difference is
that 15.0 and above will use \code{TRY_CAST()} instead of \code{CAST()}.}
}
\description{
See \code{vignette("translation-function")} and \code{vignette("translation-verb")} for
details of overall translation technology. Key differences for this backend
are:
\itemize{
\item \code{SELECT} uses \code{TOP} not \code{LIMIT}
\item Automatically prefixes \verb{#} to create temporary tables. Add the prefix
yourself to avoid the message.
\item String basics: \code{paste()}, \code{substr()}, \code{nchar()}
\item Custom types for \verb{as.*} functions
\item Lubridate extraction functions, \code{year()}, \code{month()}, \code{day()} etc
\item Semi-automated bit <-> boolean translation (see below)
}
Use \code{simulate_mssql()} with \code{lazy_frame()} to see simulated SQL without
converting to live access database.
}
\section{Bit vs boolean}{
SQL server uses two incompatible types to represent \code{TRUE} and \code{FALSE}
values:
\itemize{
\item The \code{BOOLEAN} type is the result of logical comparisons (e.g. \code{x > y})
and can be used \code{WHERE} but not to create new columns in \code{SELECT}.
\url{https://docs.microsoft.com/en-us/sql/t-sql/language-elements/comparison-operators-transact-sql}
\item The \code{BIT} type is a special type of numeric column used to store
\code{TRUE} and \code{FALSE} values, but can't be used in \code{WHERE} clauses.
\url{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.
\itemize{
\item To convert from bit to boolean use \code{x == 1}
\item To convert from boolean to bit use \verb{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"))
}
|
