To define a table that spans two or more tables, Joins are used to link fields between tables. These may be INNER JOIN, LEFT JOIN or RIGHT JOIN.
Both Access and SQL Server use a syntax of Nested Joins when spanning a number of tables; the same syntax is accepted by both environments. MySQL is known to be similar.
HighStone uses Nested Joins in all SQL Syntax. In theory an alternative syntax could be implemented - but this would be a very significant task affecting both the HighStone coding, and all supporting Data and Report definitions.
Access - Allows an UPDATE script to be executed against a dataset that spans more than one source Table. This can be a useful and powerful tool.
SQL Server - Only allows the UPDATE script to be executed against datasets drawn from a single data table.
HighStone - Only defines UPDATE SQL scripts that are targeted at a single data table (SQL Server compliance).
SELECT SQL Scripts with Constants
Access - Allows the inclusion of Constants (Numeric or Text) in a SELECT script, these values will then be returned as values in a specific (unbound) data column.
SQL Server - Does not support the return of Constants with SELECT scripts.
HighStone - Avoids the inclusion of Constants in SQL SELECT scripts (SQL Server compliance). If it is known that records from different Source tables are to be drawn together (e.g. compiling comprehensive Cost Reports from different sources using a UNION clause), then any identification constants are built into the source tables.
Access - When using a Derived Value calculated from other fields on each returned data row, which is then Named appropriately, allows that Named parameter to be used in further expressions within the overall SQL Script.
SQL Server - Does not allow Derived Values to be used later within the same SQL SELECT Script.
HighStone - Avoids the re-use of Derived Values within the same SQL SELECT script (SQL Server compliance). When needed, all Derived Values must be calculated from the original Field Values in all instances.
ORDER BY Clause with Boolean Values
Access - Allows Sorting with Boolean data types [True comes before False].
SQL Server - Does not allow Sorting with Boolean data types.
HighStone - Avoids the use of Sorting with Boolean data type in all SQL scripts (SQL Server compliance).
GROUP BY Clause - With FIRST and LAST
Access - Supports the prefixes FIRST and LAST under GROUP BY clause (though never sure what was actually returned!).
SQL Server - Does not support the prefixes FIRST and LAST under GROUP BY clause
HighStone - Avoids the use of the prefixes FIRST and LAST under GROUP BY clause (SQL Server compliance).
WHERE Clause BETWEEN <FIRSTVALUE> AND <LASTVALUE> Sub-Clause
Access - If a BETWEEN Clause is used specified with the two values the wrong way round (LASTVALUE is less than FIRSTVALUE) Access will swap the values around and continue with the selection.
SQL Server - The selection is applied rigorously, and no data rows will be returned (but no error).
HighStone - Should always ensure the two values are consistent with LASTVALUE greater than FIRSTVALUE. But it has been known to catch us out on more than one occasion.
Access - The required syntax is 'ALTER TABLE <TableName> ADD COLUMN <ColumnName>…
SQL Server - The required syntax is 'ALTER TABLE <TableName> ADD <ColumnName>…
HighStone - Always creates a SQL Script in accordance with the required syntax of the current database environment.
Table Indexes and Relationships
The SQL Syntax for the management of Table Indexes and Relationships between Table Fields varies between the different database environments.
HighStone always creates a SQL Script in accordance with the required syntax of the current database environment.
Insert Into Tables Record Identifiers
Access - If an INSERT script specifies a value for a COUNTER field type, that value will be used provided it does not clash with any existing values. Otherwise the next available value is set automatically.
SQL Server - Does not usually accept values for INT IDENTITY field types. The database sets the next available value to the field automatically. This action can be overridden by using the SET_IDENTITY command. However this must be controlled as it can apply to only one table at a time, and has to be turned on/off as necessary. Setting this on a table without an IDTITY field defined will give an error.
Note that virtually all Import actions taking data into SQL Server will recast the value of all IDENTITY fields - from whatever source. This will have a very detrimental effect on any HighStone data being uploaded into a database and should be avoided.
HighStone - In general does not set Numeric Identity fields before inserting any records - it uses the value allocated by the database environment. Should this type of functionality be needed, HighStone will use GUID data types - always defining the GUID before submitting it to the database environment.
One instance when HighStone does use specific IDENTITY values is when importing data from XML source data files. In such cases the option to Preserve Identity Values is given, and if accepted the necessary control within the database environment is applied.