SQLDatabase.NET

Limitations of SQLDatabase.Net

SQLDatabase.Net has it's advantages but there are also limitations, most of the limitations can be worked around, using development strategy.

These limitations are based on real world testing, some limitations are only present in theory and some are present due to other restrictions such as hardware and dot net limitations. Internally library uses Int64 (long) and in theory supports 2 petabytes for each schema file but practically it is limited to to 400 TB total with 8 TB for each schema file and total 52,224 (1024 * 50 + 1024 for primary) user tables with 1024 tables in each schema file. Limitations are for optimal use across systems and some can be removed using System Commands (SYSCMD). There are two different .dll files one for desktop other for mobile operating systems (PCL). This limitation is due to nature of portable class library such as inability to read write files. However NetStandard2_0 works everywhere, it is supported such as server, desktop and mobile, any new development should use the .Net Standard library.

  • The maximum Database file size should be less than 1 terabyte for each physical file.
  • Limited ANSI SQL standards are implemented.
  • Maximum 1024 tables are supported per schema, attach multiple file to increase this limit.
  • Maximum length of SQL statement is one million bytes.
  • Maximum number of joins in a single statement is 50
  • Maximum number of user Attached databases or files is 50, one primary and one temp are created automatically and are not counted as user attached.
  • Maximum 1024 columns are supported in each table or view.
  • One billion maximum row size is supported.
  • Expression depth is set to 1000 e.g. number of subqueries etc.
  • Maximum compound select statement is 1000 / 4 restricting it to 250 maximum.
  • Only LEFT OUTER JOIN is supported, RIGHT or FULL OUTER JOIN is not supported.
  • DROP COLUMN, ALTER COLUMN, ADD CONSTRAINT are not supported, however RENAME TABLE, ADD COLUMN or ALTER COLUMN are fully supported.
  • Views are read-only.
  • Grant and Revoke are not supported, should be managed by operating system or application.
  • Stored Procedures, User Defined SQL Functions are not supported.
  • Object names can not start with temp_sys or sys, there is also a list of reserved keywords.

Note: SQLDatabase.Net.dll and SQLDatabase.Net.PCL.dll are written in Microsoft .Net making them dependent on .Net to be installed and on Linux and Mac, mono-complete or mono-runtime to be installed. The NetStandard 2.0 library can be used by .Net core 2.0