VistaDB 6
VistaDB / Developer's Guide / SQL Reference / Differences from SQL Server
In This Topic
    Differences from SQL Server
    In This Topic

    VistaDB Supports a Subset of TSQL

    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.

    Unsupported Commands and Features

    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)

    Dynamic SQL Within a SQL Script

    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.

    Command Differences

    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.

    Something you need?

    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. 

    Behavior differences

    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.

    C++ and .Net runtimes

    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.

    Pagesize Limits

    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 Procedures and Functions

    SQL CLR Functions in SQL Server can be used as computed columns.  VistaDB doesn't support computed columns.

    Clustered Indexes

    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.

    See Also