VistaDB 4 SQL Support

VistaDB 4 features a robust SQL query processor that is highly compatible with Microsoft SQL Server 2005 Transact-SQL (T-SQL) syntax.  In most cases we support one of multiple syntaxes that full SQL Server supports.

VistaDB can be thought of as a subset of Microsoft SQL Server T-SQL.  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.

VistaDB command level used be used as the least common demoninator in situations where users are planning to run the same SQL code on both database engines.  For example, lets say there is a command that has 1 syntax supported in VistaDB, but SQL Server supports 4 ways to call that command.  If you use the common syntax you will be able to run the command in either location.

• SQL Commands
• Aggregate Functions
• Date and Time Functions
• Mathematical Functions
• String Functions
• System Functions

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 Trigger in the database.
DROP TRIGGER	Deletes the specified trigger from the database.
ALTER TRIGGER	Change an existing Trigger.
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.

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

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