April 12, 2018 at 11:58 am
So let's say I have a table called student (ID int, FirstName varchar(20), LastName varchar(20)) which is 100 GB in size and I create a clustered index on ID. Will I need additional storage or the size of the table still remains the same? The confusion for me which I am trying to understand is that why the storage grows when the clustered index is created on a table (Or it doesn't grow and I really shouldn't bug anyone) If the storage does grow, than my question is why is it growing if creating a clustered index only store the values in an ascending order or the storage is growing because now you have clustered key which is assigned to each ID. Can someone help me with this confusion since I am trying to learn.
April 12, 2018 at 12:04 pm
NewBornDBA2017 - Thursday, April 12, 2018 11:58 AMSo let's say I have a table called student (ID int, FirstName varchar(20), LastName varchar(20)) which is 100 GB in size and I create a clustered index on ID. Will I need additional storage or the size of the table still remains the same? The confusion for me which I am trying to understand is that why the storage grows when the clustered index is created on a table (Or it doesn't grow and I really shouldn't bug anyone) If the storage does grow, than my question is why is it growing if creating a clustered index only store the values in an ascending order or the storage is growing because now you have clustered key which is assigned to each ID. Can someone help me with this confusion since I am trying to learn.
The best way to learn is by doing.
Create yourself a sample table (as a heap), put some sample data in there, check the size, add a CI then check the size again.
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
April 12, 2018 at 12:21 pm
Phil Parkin - Thursday, April 12, 2018 12:04 PMNewBornDBA2017 - Thursday, April 12, 2018 11:58 AMSo let's say I have a table called student (ID int, FirstName varchar(20), LastName varchar(20)) which is 100 GB in size and I create a clustered index on ID. Will I need additional storage or the size of the table still remains the same? The confusion for me which I am trying to understand is that why the storage grows when the clustered index is created on a table (Or it doesn't grow and I really shouldn't bug anyone) If the storage does grow, than my question is why is it growing if creating a clustered index only store the values in an ascending order or the storage is growing because now you have clustered key which is assigned to each ID. Can someone help me with this confusion since I am trying to learn.The best way to learn is by doing.
Create yourself a sample table (as a heap), put some sample data in there, check the size, add a CI then check the size again.
And be sure to have enough data to get a measurable size. There are some tools that can help you create sample data if you don't know how to.
April 12, 2018 at 12:31 pm
In addition to the tests, think it through. Is it storing just the data in a clustered index or might there be something in addition?
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
April 12, 2018 at 3:02 pm
So far this is what I have. After I created a clustered index on a table which was about 14 GB in size. Before I created the index, total amount of space used by data was 14.728GB and total amount of space used by index was 1.084GB. After creating the index, total amount of space used by data was 14.558GB and total amount of space used by index was 1.209. Still learning.........
April 18, 2018 at 12:48 pm
Sorry to be PIB but just confirming. You create a clustered index on a column (EmpID int) and then records in this column are stored in an asc order (1,2,3....) and that's when B tree structure is created. Each record is assigned a key (not too sure about this info) and then you have underlying data (rest of the columns in emp table) stored somewhere on the disk which have pointers, pointing to the clustered key and that's why the data storage and index storage changed.
April 18, 2018 at 12:59 pm
NewBornDBA2017 - Wednesday, April 18, 2018 12:48 PMSorry to be PIB but just confirming. You create a clustered index on a column (EmpID int) and then records in this column are stored in an asc order (1,2,3....) and that's when B tree structure is created. Each record is assigned a key (not too sure about this info) and then you have underlying data (rest of the columns in emp table) stored somewhere on the disk which have pointers, pointing to the clustered key and that's why the data storage and index storage changed.
Nope, that's a fair description of a nonclustered index (except the 'stored in order' part)
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply