Database Reference
In-Depth Information
Build-In Functions
The following build-in functions are supported:
Math functions:
ACOS
,
ASIN
,
ATAN
,
ATN2
,
COS
,
COT
,
DEGREES
,
EXP
,
LOG
,
LOG10
,
PI
,
POWER
,
RAND
,
SIN
,
SQRT
,
SQUARE
, and
TAN
Date/time functions:
CURRENT_TIMESTAMP
,
DATEADD
,
DATEDIFF
,
DATEFROMPARTS
,
DATEPART
,
DATETIME2FROMPARTS
,
DATETIMEFROMPARTS
,
DAY
,
EOMONTH
,
GETDATE
,
GETUTCDATE
,
MONTH
,
SMALLDATETIMEFROMPARTS
,
SYSDATETIME
,
SYSUTCDATETIME
, and
YEAR
String functions:
LEN
,
LTRIM
,
RTRIM
, and
SUBSTRING
Error functions:
ERROR_LINE
,
ERROR_MESSAGE
,
ERROR_NUMBER
,
ERROR_PROCEDURE
,
ERROR_
SEVERITY
, and
ERROR_STATE
NEWID
and
NEWSEQUENTIALID
CAST
and
CONVERT
.
However, it is impossible to convert between a non-unicode and a
unicode string.
ISNULL
SCOPE_IDENTITY
You can use
@@ROWCOUNT
within a natively-compiled stored procedure; however, its value is
reset to 0 at the beginning and end of the procedure.
Query Surface Area
The following query surface area functions are supported:
SELECT
,
INSERT
,
UPDATE
,
and
DELETE
CROSS JOIN
and
INNER JOIN
are the only join types supported. Moreover, you can use joins
only with
SELECT
operators
.
Expressions in the
SELECT
list and
WHERE
and
HAVING
clauses are supported as long as they
use supported operators
.
IS NULL
and
IS NOT NULL
GROUP BY
is supported with the exception of grouping by string or binary data.
TOP
and
ORDER BY
. However, you cannot use these
WITH TIES
and
PERCENT
in the
TOP
clause.
Moreover, the
TOP
operator is limited to 8,192 rows when the
TOP <constant>
is used, or
even a lesser number of rows in the case of joins. You can address this last limitation by using
a
TOP <variable>
approach. However, it is less efficient in terms of performance
.
Execution Statistics
By default, SQL Server does not collect execution statistics for natively-compiled stored procedures due to the
performance impact it introduces. You can enable such a collection at the procedure level with the
exec sys.sp_xtp_
control_proc_exec_stats 1
command. Moreover, you can use the
exec sys.sp_xtp_control_query_exec_stats 1
command to enable a collection at the statement level. SQL Server does not persist those settings, and you will need
to re-enable statistics collection after each SQL Server restart.
