The design goal of VistaDB was to support a strict subset of the T-SQL language implemented in Microsoft SQL Server. The subset is designed to cover all of the common features so that rich applications can be made that support both VistaDB and SQL Server with the same codebase.
When starting out, it's recommended that queries be designed for VistaDB first and then ported up to SQL Server since anything that is supported and performs well in VistaDB is very likely to do well in SQL Server.
CURSOR
FETCH NEXT
LABELS / GOTO
Microsoft specific sp_ commands (except sp_rename)
xp_ commands
sp help
DEALLOCATE
RECOMPILE
Linked databases (including sp_addlinkedserver)
AS USER (there are no security constraints in VistaDB for code execution)
@@NESTLEVEL
REVERT
OPENDATASOURCE
SUSER_NAME
sys.database principals
sys.server principals
GO (not really supported by SQL Server in their engine either, only in tools)
SOUNDEX
FILESTREAM
Loading of COM based stored procs (deprecated in SQL 2008 as well)
XSP - C++ extended stored procs are not supported
Computed Columns - You cannot specify computed columns in VistaDB, or use functions as a column.
row_number
row_version
sequential guids are not supported
SELECT OVER - the OVER syntax is not supported
FULL OUTER JOIN
Named Schemas (VistaDB only supports dbo)
VistaDB doesn't support executing dynamic SQL from within a SQL execution block, such as:
EXEC ( 'SELECT * FROM table' );
Unfortunately the complexity of parsing and processing this correctly is beyond the engine. If you need to build up SQL code dynamically you can do it in CLR code or in your application.
RAISERROR only works if you put the calls within a try catch block on SQL Server. Otherwise you get different behavior. See the RaisError topic for more information.
If you need some of these features supported please reach out to the development team at Gibraltar Software through support. New SQL language features nearly always come from user requests with a specific application scenario in mind.
VistaDB doesn't emulate every behavior of SQL Server. Some of them are infeasible to emulate due to the difference between managed code and the C/C++ used to create Microsoft SQL Server. It may be possible implement some of those behaviors, but the performance hit would be large and there are thousands of "SQL Server-isms" where the coders wrote special case logic to handle some magic case. An example is that DATEADD can accept 0's as dates and SQL Server knows you meant the lowest value for a date possible (which is not 1/1/0001, but sometime in the 1700's!). The SQL ROUND function will return math exceptions in some cases that should work ( select ROUND(748.58, -3) works with VistaDB, but not SQL Server ).
Natural order of table selection is another example of behavior that VistasDB doesn't attempt to match. There is no natural order in SQL Server and the natural order can be altered by free space, fragmented indexes, etc.
VistaDB focuses on implementing each feature the way the intent of the function or feature was designed for, and to solve those needs. Wpecial case errors or side effects it doesn't attempt to match.
VistaDB is written entirely in .NET MSIL. Many of the cast and conversion behaviors in SQL Server are due to the fact that it is a C++ engine. For example just about anything in C++ can be cast from 0 to the object and it will end up with the minimum value for the type. This is not true in C#. Cast and Convert are two runtime behaviors that will be different than SQL Server.
The .NET decimal type is another example. It cannot handle the scale or precision of SQL Server decimals. VistaDB doesn't attempt to make .Net see larger scale and precision (or store them when it doesn't). The only answer would be to implement a non-native Decimal type. This has not been done because it would be quite slow for compare and math operations compared to native Decimal types. There is rarely a need to use the higher precision and scale, but if you do need it you will have to implement three BigInt columns manually and convert the data (you could use three 64 bit numbers to store the scale, precision, and mantissa of a custom precision object). See Stack Overflow and other programming resources for discussions about decimal and their use in .Net.
SQL Server has a fixed pagesize of 8Kb. VistaDB allows for variable page sizes. The pagesize does change the limits imposed on the database engine.
SQL CLR Functions in SQL Server can be used as computed columns. VistaDB doesn't support computed columns.
Because of the internal details of VistaDB it hasn't been practical or effective to use clustered indexes in the same way as SQL Server. If you create an index using the CLUSTERED option it will be accepted but ignored.