Create an Indexed Field in a Database Table

[DanC]

Question

How do I create an index on a field in my database table?

Answer

An index for a field can be created using the Post Create SQL Statement option (option #10) found in the Configuration window of the Input tool.

The following example applies to MS SQL Server only and creates an index for the column "City" in the table co_store_file_north.

Important: You must use the SQL code that is appropriate for your database. For instance, the example shown below will not work for Oracle.

Steps

1. Add an Input tool to a blank canvas - this is the only tool needed.
2. In the Input tool, add the appropriate SQL statement to the Post SQL Statement option in the Configuration window:
   
   

   CREATE INDEX index_city ON co_store_file_north (City)

   
   
   
   
3. Run the workflow.
4. Check your database to make sure the index was created.
   
   

Keep in mind that both the Pre and Post Create SQL Statement options are only available for specific connection types. Per the Help section, only the following connection types are supported:.mdb, .mdb*, .oci, .accdb, ODBC, OLEDB.

Thanks for reading!

[ccdyat]

Hi,

Is there any way to do Indexing using In-Database tools? My Data Source is Amazon Redshift and the nature of data is Sensor Data, count comes in Millions. 

Please advice.

Regards,

Sreekanth

[fpinchon]

I suppose you could use at the end of your workflow a Data Stream Out tool, followed by a Output Data tool, where you would use the Pre Create SQL Statement? Not super elegant, but that should work...