# Database custom queries

**A Custom Query** allows a table to be defined as an SQL statement. It is usually preferable to create a View in the database and select the View from the Views tab. Designer is able to preview data from a database view but cannot preview data from a custom query.

A custom query should be a single SQL statement to retrieve a row set. If you want to perform more complicated processing such as a multi-line SQL script before running your row set query then this should be done as a pre-processing step.

## \<`top`\> placeholder

The special string \<`top`\> is used by FastStats Designer to modify the custom query to improve performance when retrieving preview data.

The tag \<`top`\> will be substituted with the SQL command `TOP 1` to improve performance when Designer is detecting the fields returned by the custom SQL query.

> [!NOTE]
>
> **Example**
>
> If the custom query `SELECT PersonURN, Surname, Forename FROM People` then the database would execute the query before returning the results. By adding the tag \<`top`\>: `SELECT <top> PersonURN, Surname, Forename FROM People` then the query executed will be `SELECT PersonURN, Surname, Forename FROM People` when extracting the data or `SELECT TOP 1 PersonURN, Surname, Forename FROM People` when determining the fields used in the data source. For MySQL FastStats Designer will substitute a LIMIT clause and so \<`top`\> should appear at the end of the query.

The following values are substituted:

| Database | Substituted Value |
| --- | --- |
| Oracle | AND ROWNUM \\\<= 100 |
| MySql | LIMIT 100 |
| Any other (SQL Server, Access, etc) | TOP 100 |

### Further examples

| Database | Example Custom Query |
| --- | --- |
| SQL Server | SELECT \<`top`\> PersonURN, Surname, Forename FROM People |
| MySql | SELECT PersonURN, Surname, Forename FROM People \<`top`\> |
| Oracle | SELECT PersonURN, Surname, Forename FROM People WHERE 1=1 \<`top`\> |

During extract the \<`top`\> placeholder is removed entirely to return the whole results set.