VistaDB 3 Engine Features

.VDB3 databases are single-file databases (or data stores) that contain all the data for a given database
VistaDB Data types are highly compatible with SQL Server 2005 (See: Data Types for more)
TSQL parser highly compatible with SQL Server 2005 syntax and functions
Temp tables within a single procedure, or that auto destroy when the connection is closed
VDB3 databases store rich data types while ensuring a high-degree of data integrity
Multiple .VDB3 database files may be created and simultaneously opened
.VDB3 database files can be opened in 5 different modes:
- Exclusive Read-Write
- Exclusive Read Only
- Non-Exclusive Read-Write
- Non-Exclusive Read Only
- Shared Read Only
.VDB3 database may be copied and backed up as you would any regular file
File copy command can be used to backup a VistaDB database file.
4TB of data per database
65,535 tables per database, 65,535 columns per table and 65,535 indexes per table
Blowfish encryption key can be 8 to 56 characters with the VistaDB engine implementing 16 iterations of encryption using the key
Database, table and column level encryption supported
Compression may be applied to character and binary type columns
Dynamically set default values when records are added or modified with static values or V-Script function results
Primary and Foreign keys supported
Indexes support case insensitive and case sensitivity, ascending and descending flags
Indexes are automatically compressed
Indexes support case insensitive and case sensitivity, ascending and descending flags
Indexes are automatically compressed
Default values
Configurable page size in the database file to optimize data access depending on type of data being stored
Automatic best-fit block size allocation for Binary and Text type data (this is a new feature in SQL Server 2008 called Sparse Tables)
Constraints
Relationships (via Primary and Foreign keys)
Columns may be marked as read-only and hidden
Columns may be assigned captions and descriptions
Tables and columns support descriptions
NewID() function support for Unique Identifier and character type columns
Native 64 bit support on Windows x64 platforms
No CPU or RAM usage limitations imposed by the engine
Full-Text Search indexes in VarChar, Character and Memo fields
CLR Triggers - C# or VB.Net custom code called during update, delete or insert operations
CLR Procs - C# or VB.Net Stored Procedures and User Defined Functions
TSQL Stored Procedures and User Defined Functions (UDFs)

Features that will be added in 3.x releases (see roadmap):

MS Sync Services Support
Client / Server Support
LINQ Entity Framework provider support

VistaDB 3.x Data Types compared to TSQL

SQL Server 2005	VistaDB 3.x	.NET System
BigInt	BigInt	Int64
Bit	Bit	Byte
Bit	Bit	Boolean
Char	Char	String
DateTime	DateTime	DateTime
DateTime	DateTime	DateTime
Decimal	Decimal	Decimal
Float	Float	Double
Identity Fields	Identity Fields	-
Image	Image	Byte[]
Image	Image	Byte[]
Int	Int	Int32
Money	Money	Decimal
NChar	NChar	String
NText	NText	String
NVarchar	NVarchar	String
Real	Real	String
SmallDateTime	SmallDateTime	Int16
SmallInt	SmallInt	Int16
SmallMoney	SmallMoney	Decimal
Text	Text	String
Timestamp	Timestamp	Int64
TinyInt	TinyInt	Byte
UniqueIdentifier	UniqueIdentifier	Guid
Binary	(use varbinary)
VarBinary	VarBinary	Byte[]
VarChar	VarChar	String
XML	-	-

VistaDB 3.x Database Specifications and Limits

Maximum	Details
# of Columns in a Table	1,024 This applies to all column types.
# of Columns in an Index Key	256
# of Indexes in a Table	Primary indexes per table: 1 Secondary indexes per table: Unlimited Increasing the number of secondary indexes may affect performance since the engine must update all indexes when row changes are applied.
# of Rows in a Table	4,294,967,293 VistaDB uses an unsigned 32-bit integer RowId to address physical records. When a record is deleted, the respective RowId doesn't participate in further table modifications, and the record apartment is put into re-cycling. RowId's get re-assigned to records when a database is packed.
# of Rows in a Transaction	Limited only by available memory constraints of the operating system or hardware. Transactions use multi-versioning record schema. New record versions are allocated for each transaction.
# of Tables in a Database	2,147,483,646 In reality, the limit is based on available memory constraints of the operating system or hardware.
Size of Database File	4TB per .VDB3 database file Theoretical limit is 16 Exabytes.
Size of Index Database Objects	Page size / 4 Objects are: Database Description, Table Name/Description, column Name/Description, Index Name, View Name/Description, Constraint Name/Description etc.
Size of Check Constraint Expression and Default Value Expression	Page Size / 4
Size of CLR Proc Signature	Page size / 4
Size of Assembly COFF-Image	Depends on Page Size. For example: 1Kb page - 134, 216, 632 bytes 16Kb page - 2, 147, 466, 112 bytes
Size of View Expression	~60,000,000 chars
Size of Rows (Records)	Maximum size approx. the same as the Maximum Size for Image column divided by 3. For example: 1Kb page ~ 44, 000, 000 bytes 16Kb page ~ 704, 000, 000 bytes
Size of Text/NText/Image Columns	Text/ NText and Image column data is not stored in the row if their data exceeds 25% of the database page size The maximum size depends on database page size and code page (for character columns). For example: 1Kb page - 134, 216, 632 bytes 16Kb page - 2, 147, 466, 112 bytes
Transaction Isolation Level	ReadCommitted Snapshot isolation
Database File Page Size	1,024 (by default) - 16,384 bytes Data is read and written by pages. The bigger page, the more time will be required for I/O operations. At the same time, the rows are grouped by several instances and aligned within the page boundary. If the pages size is insufficient to allocate ~3 records, data is expanded and stored to extra pages. The expansion process takes additional time for I/O and thus, the more actual data length a record has, the bigger the page size should be. This Page Size is defined when a database is created. It can be modified later but must be followed by a Pack operation.
Data Deployment Options	VDB3 files can be custom named, and deployed on a wide variety of media and platforms. Partial trust - medium trust (shared hosting) environment fully supported. XCopy deployable - Nothing to install on target machine. No services, no registry access. Can run as Guest on any Windows OS. Read only - Data may be written to read only media (CD / DVD ROM) and queried live. LAN - Through the use of mapped drive letters or UNC naming. USB Key - Data may be stored on any Windows drive (NTFS / FAT / FAT32). Isolated Storage - VistaDB fully supports creation and running of databases in Isolated Storage. Memory Backed / VM - Create temp databases that are only backed by Virtual Memory. When the database is closed it is destroyed.
Memory Tables	A database created in memory. The size is limited only by available memory constraints of the operating system or hardware. Memory Tables and Memory Databases follow the same restrictions and constraints as physical tables and databases.

Self Imposed Limits (** where noted)

VistaDB was designed using 32-bit and 64-bit memory addressing in most situations that required large upper limits. The effect of supporting large upper limits are ranges that exceed 4 billion or 4GB for 32-bit values and provides massive Exabytes limits for 64-bit values. Because of our focus on supporting the creation of small to midsize applications, supporting such enormous upper limits is not feasible for testing. Therefore we have implemented some self-imposed limits in some places.

Setting Default Values

VistaDB allows you to dynamically set default values when new records are added, or optionally when they are modified, using pre-defined functions. With this feature, you can generate a wide range of data settings based on the existing data types. For example, you can use the GUID() function to generate unique GUID values and "version control" table records, or use the NOW() function to stamp new records with the current Date and Time.

Nunit Tests and Coverage Reports Included

We include over 425 NUnit tests with our application to demonstrate our commitement to quality. We also include the reports of our code coverage tests to show our progress in testing the engine. Our goal is to reach 80% code coverage in each module. We are not there yet, but we are working on it.

VistaDB Software, Inc.