VistaDB SQL Support

VistaDB features a robust SQL query processor that is highly compatible with the most commonly used subset of Microsoft SQL Server Transact-SQL (T-SQL) syntax. All of our syntax is supported in SQL Server, but not the other way around. We do not fully support all the T-SQL functions or abilities. In some cases Microsoft has more than a dozen syntax variations for the same command. We usually only support the most common cases, and the ones that are closest to matching ANSI SQL when possible.

SQL Date and Time Functions

SQL Clauses Notes
DATEADD Returns a new datetime value based on adding an interval to the specified date.
DATEDIFF Returns the number of date and time boundaries crossed between two specified dates.
DATENAME Returns a character string representing the specified datepart of the specified date.
DATEPART Returns an integer that represents the specified datepart of the specified date.
year, quarter, month, dayofyear, day, week, weekday, hour, minute, second, millisecond,
DAY Returns an integer representing the day datepart of the specified date.
GETDATE Returns the current system date and time.
GETUTCDATE Returns the datetime value representing the current UTC time.
MONTH Returns an integer that represents the month part of a specified date.
YEAR Returns an integer that represents the year part of a specified date.

SQL String Functions

SQL Clauses Notes
ASCII Returns the ASCII code value of the leftmost character of a character expression.
CHAR Converts an int ASCII code to a character.
CHARINDEX Returns the starting position of the specified expression in a character string. Sometimes refered to as POS in other SQL syntax
LEFT Returns the left part of a character string with the specified number of characters.
LEN Returns the number of characters, instead of the number of bytes, of the specified string expression, excluding trailing blanks.
LOWER Returns a character expression after converting uppercase character data to lowercase.
LTRIM Returns a character expression after it removes leading blanks.
NCHAR Returns the Unicode character with the specified integer code, as defined by the Unicode standard.
PATINDEX Returns the starting position of the first occurrence of a pattern in a specified expression, or zeros if the pattern is not found, on all valid text and character data types.
REPLACE Replaces all occurrences of the second specified string expression in the first string expression with a third expression.
REPLICATE Repeats a character expression for a specified number of times.
REVERSE Returns the reverse of a character expression.
RIGHT Returns the right part of a character string with the specified number of characters.
RTRIM Returns a character string after truncating all trailing blanks.
SPACE Returns a string of repeated spaces.
STR Returns character data converted from numeric data.
STUFF Returns character data stuffed with another expression
SUBSTRING Extracts a substring from a table column or character literal
UNICODE Returns the integer value, as defined by the Unicode standard, for the first character of the input expression.
UPPER Returns a character expression with lowercase character data converted to uppercase.

SQL Aggregate Functions

SQL Clauses Notes
AVG AVG Returns the average of the values in a specified column or an expression.
COUNT Counts the number of rows retrieved by a SELECT statement.
COUNT_BIG Counts the number of rows retrieved by a SELECT statement.
MAX Calculates the largest value for a numeric column.
MIN Calculate the smallest value for a numeric column.
STDEV Computes the standard deviation.
SUM Returns the sum of all values in a column.

SQL Mathematical Functions

SQL Clauses Notes
ABS Returns the absolute value of the argument.
ACOS Returns the angle, in radians, whose cosine is the specified float expression; also called arccosine.
ASIN Returns the angle, in radians, whose sine is the specified float expression. This is also called arcsine.
ATAN Returns the angle in radians whose tangent is a specified float expression. This is also called arctangent.
ATAN2 Returns the angle, in radians, whose tangent is the quotient of two specified float expressions. This is also called arctangent.
CEILING Returns the smallest integer greater than, or equal to, the specified numeric expression.
COS Returns the trigonometric cosine of the specified angle, in radians, in the specified expression.
COT Returns the trigonometric cotangent of the specified angle, in radians, in the specified float expression.
DEGREES Returns the corresponding angle in degrees for an angle specified in radians.
EXP Returns the exponential value of the specified float expression.
FLOOR Returns the largest integer less than or equal to the specified numeric expression.
FRAC Returns the fractional part of the argument X.
INT Returns the Integer position of a Float value.
LOG Returns the natural logarithm of the specified float expression.
LOT10 Returns the base-10 logarithm of the specified float expression.
MAXOF Returns the highest value of the n figures.
MINOF Returns the lowest value of the n figures.
PI Returns the constant value of PI. (3.1415926535897932385)
POWER Returns the value of the specified expression to the specified power.
RADIANS Returns radians when a numeric expression, in degrees, is entered.
RAND Returns a random float value from 0 through 1.
ROUND Rounds a float-type value to an integer-type value.
SIGN Returns the positive (+1), zero (0), or negative (-1) sign of the specified expression.
SIN Returns the trigonometric sine of the specified angle, in radians, and in an approximate numeric, float, expression.
SQUARE Returns the square of the specified expression.
SQRT Returns the square root of the specified expression.
TAN Returns the tangent of the input expression.

SQL Commands

SQL Command Extra Commands and Clauses
SELECT Extracts data from one or more fields from one or more tables 

Aliases (AS and IN)
DISTINCT
FROM
GROUP BY [HAVING]
IS [NOT] NULL
JOIN (INNER, LEFT OUTER, RIGHT OUTER)
LASTIDENTITY
@@IDENTITY
@@VERSION
LIKE
CONTAINS
ORDER BY
WHERE
UNION [ALL]
ASC | DESC
INSERT The INSERT statement is used to add or append records to tables. 

INSERT INTO Table (SELECT * FROM AnotherTable)
UPDATE The UPDATE statement is used to update, modify or change data in tables. 

FROM 

Sub-Queries are supported:
UPDATE Table1 SET Column1 = (SELECT Column FROM Table2 WHERE Column1 = Value)
DELETE The DELETE statement is used to remove rows in tables.
CREATE DATABASE This command is used to create new VistaDB database.
CREATE table Creates a new table inside a VistaDB database.

[NOT] NULL
[NOT] READ ONLY
[NOT] ENCRYPTED
[NOT] PACKED
DEFAULT
IDENTITY (seed, increment)
CAPTION
DESCRIPTION
CODE PAGE

Constraints:
CHECK
PRIMARY KEY
FOREIGN KEY
REFERENCES
ON UPDATE [CASCADE]
ON DELETE [CASCADE]
UNIQUE
CLUSTERED | NONCLUSTERED 
ASC | DESC
ALTER table Changes or alters a data table's structure:
ALTER COLUMN
ADD COLUMN
DROP COLUMN
DROP CONSTRAINT
ADD CONSTRAINT
DROP table Deletes the specified table from the database.
CREATE INDEX Create a new index for a given table. 

UNIQUE
CLUSTERED | NONCLUSTERED
ASC | DESC
CASE SENSITIVE
VSCRIPT
ALTER INDEX Alters an index
name | ALL
REBUILD
DROP INDEX Deletes the specified index from the database.
CREATE TRIGGER Creates a new CLR trigger in the database. TSQL triggers are not supported.
DROP TRIGGER Deletes the specified CLR trigger from the database. TSQL triggers are not supported.
ALTER TRIGGER Change an existing CLR trigger. TSQL triggers are not supported.
BEGIN TRANSACTION Begin a transaction. VistaDB supports snapshot transactions. Each BEGIN TRANSACTION must end with a COMMIT or ROLLBACK.
COMMIT TRANSACTION Commit the current transaction
ROLLBACK TRANSACTION Rollback the current transaction.
CREATE VIEW Create a view
DESCRIPTION 
AS
ALTER VIEW Alters a specified view
DROP VIEW Deletes a specified view
CREATE ASSEMBLY DESCRIPTION
FROM
ALTER ASSEMBLY DESCRIPTION
FROM
DROP ASSEMBLY Delete an CLR Proc assembly
EXECUTE Execute a procedure
CREATE PROCEDURE DESCRIPTION
AS EXTERNAL NAME
DROP PROCEDURE Delete a CLR Proc
SET OPTIMIZATION ON | OFF
SET CHECK VIEW ON | OFF
SET @ variable_name = expression
DECLARE @ variable_name AS data_type
BEGIN ... END  
IF ELSE  
TRY...CATCH Exception handling withing TSQL
WHILE BREAK CONTINUE While loops are very powerful and allow looping over data within TSQL
RAISERROR Throws an exception from your SQL code to the calling application.
PRINT Returns text to the calling assembly for informational purposes.

VistaDB SQL System Functions

SQL Clauses Notes
@@IDENTITY Returns the last inserted identity value (scoped by session).
CASE Evaluates a list of conditions and returns one of multiple possible result expressions.
CAST Explicitly converts an expression of one data type to another.
COALESCE
CONVERT Explicitly converts an expression of one data type to another.
ISDATE Determines whether an input expression is a valid date.
ISNULL Replaces NULL with the specified replacement value.
CONTAINS Full text search looking for a single matching word within an FTS index.
LASTIDENTITY Returns the last-inserted identity value scoped by session and specific table.
NEWID Generate a new GUID
SP_RENAME Rename an object