SQL SERVER – Index Levels and Delete Operations – Page Level Observation

USE tempdb
GO

-- Create Table FragTable
CREATE TABLE FragTable (ID CHAR(800),
FirstName CHAR(2000),
LastName CHAR(3000),
City CHAR(2200))
GO
-- Create Clustered Index
CREATE CLUSTERED INDEX [IX_FragTable_ID] ON FragTable
(
[ID] ASC
ON [PRIMARY]
GO

-- Insert one Million Records
INSERT INTO FragTable (ID,FirstName,LastName,City)
SELECT TOP 100 ROW_NUMBER() OVER (ORDER BY a.nameRowID,
'Bob',
CASE WHEN ROW_NUMBER() OVER (ORDER BY a.name)%THEN 'Smith'
ELSE 'Brown' END,
CASE WHEN ROW_NUMBER() OVER (ORDER BY a.name)%10 THEN 'New York'
WHEN ROW_NUMBER() OVER (ORDER BY a.name)%10 THEN 'San Marino'
WHEN ROW_NUMBER() OVER (ORDER BY a.name)%10 THEN 'Los Angeles'
ELSE 'Houston' END
FROM 
sys.all_objects a
CROSS JOIN sys.all_objects b
GO

-- Check the spaces
sp_spaceused 'FragTable'
GO
-- Check the percentages
SELECT avg_page_space_used_in_percent
,avg_fragmentation_in_percent
,index_level
,record_count
,page_count
,fragment_count
,avg_record_size_in_bytes
FROMsys.dm_db_index_physical_stats(DB_ID('TempDb'),OBJECT_ID('FragTable'),NULL,NULL,'DETAILED')
GO
-- Delete all from table
DELETE
FROM 
FragTable
GO

-- Check the spaces
sp_spaceused 'FragTable'
GO
-- Check the percentages
SELECT avg_page_space_used_in_percent
,avg_fragmentation_in_percent
,index_level
,record_count
,page_count
,fragment_count
,avg_record_size_in_bytes
FROMsys.dm_db_index_physical_stats(DB_ID('TempDb'),OBJECT_ID('FragTable'),NULL,NULL,'DETAILED')
GO
-- Clean up
DROP TABLE FragTable
GO


Crie um site com

  • Totalmente GRÁTIS
  • Design profissional
  • Criação super fácil

Este site foi criado com Webnode. Crie o seu de graça agora!