Indexing Tables

Question

Indexing Tables

Viewing 6 posts - 16 through 20 (of 20 total)

You must be logged in to reply to this topic. Login to reply

water490 SSC Eights! Points: 958 More actions · Answer 1

Jonathan AC Roberts wrote:

If the index doesn't already exist you need to remove DROP_EXISTING = ON from the index creation or set it to OFF.

If I do that then I get an error when the script is run again. The way I fixed it was to change that option from OFF to ON.

pietlinden SSC Guru Points: 63503 More actions · Answer 2

If the index is created, you should be able to find it in the sys.indexes table

sys.indexes (Transact-SQL) - SQL Server | Microsoft Learn

water490 SSC Eights! Points: 958 More actions · Answer 3

thank you for this.

I am a rookie so excuse the ignorance. I need to re-calculate the index after a certain calculation is done. I thought that using the script I created would do the job. How would using sys.indexes help me?

frederico_fonseca SSCoach Points: 16155 More actions · Answer 4

water490 wrote:

thank you for this.
I am a rookie so excuse the ignorance. I need to re-calculate the index after a certain calculation is done. I thought that using the script I created would do the job. How would using sys.indexes help me?

you don't recalculate an index - you rebuild it if/when required, or you drop and recreate (which is what you are doing).

but the main question here is WHY do you think you need to do it? does the proc where this is done do lots of deletes/updates on that table? if this is why you do it then a index reorg is needed ONLY to cleanup the deletes and recompress the deltastores. see https://www.red-gate.com/simple-talk/databases/sql-server/performance-sql-server/hands-on-with-columnstore-indexes-part-3-maintenance-and-additional-options/ for further info

and what is the number of rows on that table? if not on the hundreds of millions it likely should not be a columnstore in the first place

Jeff Moden SSC Guru Points: 1004356 More actions · Answer 5

frederico_fonseca wrote:

water490 wrote:

if this is why you do it then a index reorg is needed ONLY to cleanup the deletes and recompress the deltastores. see https://www.red-gate.com/simple-talk/databases/sql-server/performance-sql-server/hands-on-with-columnstore-indexes-part-3-maintenance-and-additional-options/ for further info

Just in case someone else reads this and misunderstands... this is for Column Store indexes and REORGANIZE is definitely the way to go here.

For Row Store indexes, I recommend avoiding REORGANIZE altogether. It simply doesn't work the way people think it does and usually perpetuates fragmentation where mid-index fragmentation occurs... like on Random Guids and other evenly distributed fragmentation.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Dennis Jensen SSChasing Mays Points: 604 More actions · Answer 6

Well you can be sure it gets created by doing the following

DROP INDEX [dbo].[VIXNearTermExpiration]

Then change your code to do it in two steps rather than one as follows:

DROP INDEX IF EXISTS [dbo].[VIXNearTermExpiration]

CREATE CLUSTERED COLUMNSTORE INDEX [VIXNearTermExpirationIndex]
    ON [dbo].[VIXNearTermExpiration]
  WITH ( COMPRESSION_DELAY = 0 )
    ON [PRIMARY]

If I am understanding what DROP EXISTING = ON is doing that is. Never used this, cannot see a reason to use it. If I do not know if it exists I can look at the database to see if its there. By the not naming it IX_NearTermExpiration will mean the data modeling aspect of SSMS will not work correctly.