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