Updating indexes for block model space

Putting [SOW_Number] first in the PK might help but doing that to table with 83,423,460 rows is not something I would jump into.

In computer science, a B-tree is a self-balancing tree data structure that maintains sorted data and allows searches, sequential access, insertions, and deletions in logarithmic time.

I setup a test-bed on my laptop so I could test the performance of adding the suggested index vs having no index on USE master; IF EXISTS (SELECT 1 FROM sys.databases d WHERE = 'Test DB') DROP DATABASE Test DB; GO CREATE DATABASE Test DB ON (NAME = 'Test DB_Primary', FILENAME = '/var/opt/mssql/data/Test DB_Primary.mdf', SIZE = 5GB, MAXSIZE = 10GB, FILEGROWTH = 1GB) LOG ON (NAME = 'Test DB_Log', FILENAME = '/var/opt/mssql/log/Test DB_Log.ldf', SIZE = 5GB, MAXSIZE = 10GB, FILEGROWTH = 1GB); GO USE Test DB; CREATE TABLE dbo.

Dropping and re-creating the clustered index will result in all 83 million rows being written to the log while data is moved in and out of the clustered index. Keeping in mind the columns in the clustered index key are used (duplicated) in every non-clustered index, the space savings from right-sizing the data might be substantial. FPP_Invoice_Revenue ADD Till_Prev_Inv_Amt_Comp AS Till_Prev_Inv_Amt_In_USD / 0.0285714286 ALTER TABLE dbo.

FPP_Invoice_Revenue ADD Cur_Inv_Amt_Comp AS Cur_Inv_Amt_In_USD / 0.0285714286 ALTER TABLE dbo.

Note that if you are not creating new indices in 6.x but just reading 5.x indices, multiple types are supported.

If you are creating new indices, then you need to change your application I'm afraid.

Leave a Reply