SQL Building Indexes From Query of Table?

Question

I copied some tables from another database. I also created a table with the index information. I would like to run a query and dynamically create the indexes. I can write a program in .NET or Java but I would like to know how to do it in SQL. Here is an example of what I'm trying to do:

A query of the index table returns this information:
------------------------------------------------------------------------------------------------------------------
TABLENAME_1 | INDEXNAME_1 | COMMASEPARATEDLISTOFCOLUMNS_1
------------------------------------------------------------------------------------------------------------------
TABLENAME_2 | INDEXNAME_2 | COMMASEPARATEDLISTOFCOLUMNS_2
-------------------------------------------------------------------------------------------------------------------

I would like to be able to loop through the table of results and do this:

LOOP ITERATION 1

CREATE INDEX INDEXNAME_1 ON TABLENAME_1
(COMMASEPARATEDLISTOFCOLUMNS_1)

LOOP ITERATION 2

CREATE INDEX INDEXNAME_2 ON TABLENAME_2
(COMMASEPARATEDLISTOFCOLUMNS_2)

.
.
.
.
LOOP ITERATION N

CREATE INDEX INDEXNAME_N ON TABLENAME_N
(COMMASEPARATEDLISTOFCOLUMNS_N)

Answer 1

Ignoring the syntax about creating Index , the below SQL Script can be used for this task.



This has been written on Microsoft SQL Server, wherein Dynamic SQL has been used to build the CREATE INDEX command and a WHILE loop to pick each record of the table.





/********* Script STARTs***********/



CREATE TABLE IndexCatalog(TableName VARCHAR(50),IndexName VARCHAR(50), IndexedColumns VARCHAR(500))



DECLARE @IndexCatalogRecordCount INT, @MinTableName VARCHAR(50),@SqlCmdString VARCHAR(2000)

SELECT @MinTableName=MIN(TableName) FROM IndexCatalog



WHILE(@MinTableName IS NOT NULL)

BEGIN

SELECT @SqlCmdString= 'CREATE INDEX '+IndexName+' ON '+TableName+'

('+IndexedColumns+')' FROM IndexCatalog WHERE TableName=@MinTableName



/*PRINT @SqlCmdString*/

EXEC ( @SqlCmdString)

SELECT @MinTableName=MIN(TableName) FROM IndexCatalog WHERE TableName>@MinTableName

END



/********* Script ENDs***********/



The script essentially builds the command as a string and stores it in a variable and then that variable is passed to EXEC Function which executes it.



Also, if you ever wish to see how you command string is made at run-time.. comment EXEC and uncomment PRINT .

Embed the right CREATE INDEX command in this script.



Tell us if you have difficulties in deciphering the script or have trouble tweaking for your use... lets hope to fix it...





--In 'thoughts'...

Lonely Rogue.

https://twitter.com/LonelyRogue

Answer 2

in case you propose will IDX1 be faster than IDX2, then no. each index will be an same % (presented each little thing else is an same). if you're asking if a kind on an index impacts the speed of a question, the answer is certain. even if that's nevertheless very quickly, Oracle has to grant a quickly-experiment of the completed table -earlier- it starts returning ends up in case you type those consequences. in case you go away it unsorted, it would want to initiate returning consequences as quickly because it unearths them, which permits it to fetch the real of the recordset extra straight away.

Answer 3

You can't do that in strict SQL.



You might be able to do it in a procedural SQL such as Oracle's PL/SQL, depending on how much flexibility you need, such as determining the columns in the table at run-time, or knowing them in advance.



In either case, it will depend on which database you're using, as the syntax is ever so slightly different depending on which database (or vastly different in some cases).