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.
| Author |
Topic |
|
Haja Mohideen
Starting Member
12 Posts |
 Posted - 2015-01-02 : 00:54:21
|
| Dear All,I have created a covering Index like below,----------------------------------------------------------------CREATE NONCLUSTERED INDEX [INX_NONCLUS_COVERING_INVBAK] ON [dbo].[Invoices_Bak] ( [SellerID] ASC, -- Key column [BuyerID] ASC -- Key column )INCLUDE ( [ReceivedDate], --Non key column[BillNumber], --Non key column[TakenDate], --Non key column[Taken]) --Non keyWITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]GO------------------------------------------------------------Above index has created on production environment, Now i would like alter above index that is from non key column to key columnyou can find a non key column called [ReceivedDate] that is available in include part, i need to make this column to Key column.How to do this proper way without affect production environment that is using unwanted disk size and more.. Pls guide me. Nothing is impossible |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2015-01-02 : 13:53:34
|
| I would just create a new index with the columns the way you want and then drop your current index. If you don't have the disk space for that, then you'll need to DROP INDEX/CREATE INDEX. Depending upon the size of the table, this could cause performance issues for any queries that are currently using that index.Tara KizerSQL Server MVP since 2007http://weblogs.sqlteam.com/tarad/ |
 |
|
|
Haja Mohideen
Starting Member
12 Posts |
Posted - 2015-01-03 : 01:12:13
|
| Dear Tara Kizer,Thank you so much for your keen reply. I will follow it.Nothing is impossible |
|
|
|
|
|
|
|
|
Making covering index non-key to Key column