Creating nonclustered index after clustered index

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Creating nonclustered index after clustered index

Author

Topic

Sanchit297
Starting Member

16 Posts

Posted - 2010-06-15 : 10:41:37

Hi,

I have a table "TestTable" which contains about 3-4 million data and already has one clustered index created during DTS execution task.
I want to add a new NonClustered Index to the DTS Execute task in which a Clustered index is created, just after Clustered Index creation bit.
Please have a look at the syntax ive used for Non clustered index creation below, do I need to add anything else to enhance performance ( I mean to add something more as given for the Clustered index creation bit)??


BEGIN TRANSACTION
SET QUOTED_IDENTIFIER ON
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
SET ARITHABORT ON
SET NUMERIC_ROUNDABORT OFF
SET CONCAT_NULL_YIELDS_NULL ON
SET ANSI_NULLS ON
SET ANSI_PADDING ON
SET ANSI_WARNINGS ON
COMMIT
BEGIN TRANSACTION
CREATE CLUSTERED INDEX IX_TestTable ON dbo.TestTable
    (
    Item,
    DateTime1
    ) ON [PRIMARY]
GO
COMMIT

CREATE NONCLUSTERED INDEX  IX_TestTable2 ON dbo.TestTable

    (
    Id,
    Location,UniqueNum, DateTime2
    ) ON [PRIMARY]
GO

Thanks,..

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2010-06-15 : 11:01:58

What are you going to use this index for?

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

Sanchit297
Starting Member

16 Posts

Posted - 2010-06-15 : 12:09:44

From the SQL server 2000, some reports are sent to the client, for which it was required to add a clustered index to this table to improve performance..

Now a new report would be generated using this table's data and for that the columns used in Clustered index are not in use. So i have created a non clustered index which contains the cols required in the new report.

Hope it helps..

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-06-15 : 12:17:10

Sounds ok to me.

No, you're never too old to Yak'n'Roll if you're too young to die.

Sanchit297
Starting Member

16 Posts

Posted - 2010-06-15 : 12:35:19

This is my first assignment in T-SQL. Can you tell me the importance of writing the below below creation of Clustered index please and if the same can be added while creating a Non Clustered index....

BEGIN TRANSACTION
SET QUOTED_IDENTIFIER ON
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
SET ARITHABORT ON
SET NUMERIC_ROUNDABORT OFF
SET CONCAT_NULL_YIELDS_NULL ON
SET ANSI_NULLS ON
SET ANSI_PADDING ON
SET ANSI_WARNINGS ON
COMMIT

Thanks....

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources