VistaDB 3 SQL-99 Support
|
-
Truly embedded ADO.NET 2 SQL database for .NET 2+, CF 2 and Mono
-
Less than 1 MB single assembly deployment
-
Cross-platform Windows, Linux and Mac OS X support using the Mono Project
-
Easy migration from SQL Server TSQL
Royalty-free distribution |
 |
|
VistaDB 3 features a robust SQL query processor that supports
SQL-99 and is highly compatible with Microsoft SQL Server's Transact-SQL
(T-SQL) syntax (meaning we follow the TSQL syntax rather than the Access syntax). We do not fully support all the T-SQL functions or TSQL stored procedures.
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 |
|
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. |
| CONTAINS |
Full text
searching. |
| ISDATE |
Determines
whether an input expression is a valid date. |
| ISNULL |
Replaces NULL
with the specified replacement value. |
| CONTAINS |
Full text
searching. |
| LASTIDENTITY |
Returns the
last-inserted identity value scoped by session and specific table. |
| NEWID |
Generate a new
ID |
| SP_RENAME |
Rename an
object |
|
Purchase |
Products |
Downloads |
Forums |
Blog |
Free Trial
Whitepaper