SQL Reference
Table Of Contents
- Chapter 1 Introduction
- Chapter 2 Supported standards
- Support for Unicode characters
- SQL statements
- SELECT statement
- SQL clauses
- FROM clause
- WHERE clause
- GROUP BY clause
- HAVING clause
- UNION operator
- ORDER BY clause
- OFFSET and FETCH FIRST clauses
- FOR UPDATE clause
- DELETE statement
- INSERT statement
- UPDATE statement
- CREATE TABLE statement
- TRUNCATE TABLE statement
- ALTER TABLE statement
- CREATE INDEX statement
- DROP INDEX statement
- SQL expressions
- SQL functions
- FileMaker system objects
- Reserved SQL keywords
- Index
Chapter 2 | Supported standards 23
FileMaker automatically creates indexes as needed. Using CREATE INDEX causes the index to
be built immediately rather than on demand.
Example
CREATE INDEX ON Salespeople.Salesperson_ID
DROP INDEX statement
Use the DROP INDEX statement to remove an index from a database file. The format of the DROP
INDEX statement is:
DROP INDEX ON table_name.column_name
DROP INDEX ON table_name (column_name)
Remove an index when your database file is too large, or you don’t often use a field in queries.
If your queries are experiencing poor performance, and you’re working with an extremely large
FileMaker database file with many indexed text fields, consider dropping the indexes from some
fields. Also consider dropping the indexes from fields that you rarely use in SELECT statements.
Dropping an index for any column automatically selects the Storage Option of None and clears
Automatically create indexes as needed in Indexing for the corresponding field in the
FileMaker database file.
The PREVENT INDEX CREATION attribute is not supported.
Example
DROP INDEX ON Salespeople.Salesperson_ID
SQL expressions
Use expressions in WHERE, HAVING, and ORDER BY clauses of SELECT statements to form
detailed and sophisticated database queries. Valid expression elements are:
1 Field names
1 Constants
1 Exponential/scientific notation
1 Numeric operators
1 Character operators
1 Date operators
1 Relational operators
1 Logical operators
1 Functions
Field names
The most common expression is a simple field name, such as calc or
Sales_Data.Invoice_ID.