Index size question (DDL)

This should be the DDL:
CREATE TABLE [dbo].[Transactions] (
[ID] [int] IDENTITY (1000, 1) NOT NULL ,
[SSN_TIN] [char] (9) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
,
[ACCT_NUMBER] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS
NOT NULL ,
[Settle_Date] [smalldatetime] NOT NULL ,
[SEQ_NUM] [int] NOT NULL ,
[FUND_ID] [varchar] (22) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
NULL ,
[TRANS_CODE] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS
NOT NULL ,
[INT_TYPE] [smallint] NOT NULL ,
[Trade_Date] [smalldatetime] NULL ,
[QUANTITY] [decimal](16, 6) NOT NULL ,
[PRICE] [decimal](21, 8) NOT NULL ,
[PROCEEDS] [money] NULL ,
[FIN_INST_ID] [smallint] NOT NULL ,
[FUND_CODE] [varchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
[TradeMonth] [int] NULL ,
[Acct_Pre] [varchar] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[Transactions-Old] (
[SSN_TIN] [char] (9) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
,
[ACCT_NUMBER] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS
NOT NULL ,
[Settle_Date] [smalldatetime] NOT NULL ,
[SEQ_NUM] [int] NOT NULL ,
[FUND_ID] [varchar] (22) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
NULL ,
[TRANS_CODE] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS
NOT NULL ,
[INT_TYPE] [smallint] NOT NULL ,
[Trade_Date] [smalldatetime] NULL ,
[QUANTITY] [decimal](16, 6) NOT NULL ,
[PRICE] [decimal](21, 8) NOT NULL ,
[PROCEEDS] [money] NULL ,
[FIN_INST_ID] [smallint] NOT NULL ,
[FUND_CODE] [varchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
[TradeMonth] AS (datepart(year,[Trade_Date]) * 100 + datepart
(month,[Trade_Date])) ,
[Acct_Pre] AS (left([Acct_Number],3))
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[Transactions-Old] WITH NOCHECK ADD
CONSTRAINT [PK_Transactions] PRIMARY KEY CLUSTERED
(
[SSN_TIN],
[ACCT_NUMBER],
[Settle_Date],
[SEQ_NUM]
) WITH FILLFACTOR = 90 ON [PRIMARY]
GO
CREATE CLUSTERED INDEX [IX_CL_Transactions_TradeDate] ON [dbo].
[Transactions]([ID]) WITH FILLFACTOR = 80 ON [PRIMARY]
GO
ALTER TABLE [dbo].[Transactions] ADD
CONSTRAINT [PK_Transactions_ID] PRIMARY KEY NONCLUSTERED
(
[ID]
) ON [PRIMARY]
GO
DW <None> wrote in news:OupT2CjAEHA.3348@.TK2MSFTNGP11.phx.gbl:

> I have a SQL 2000 table with 16 million rows. I made a copy of it.
> The two tables have the same number of rows, 16,152,139 to be exact.
> The old table had a clustered, composite primary key across the first
> four columns -- ssn (char(9)), Acct Number (varchar 20), sequence
> number (varchar(20)), and transaction date (smalldatetime). The
> database size was 1,708,032 KB and the index was 8,520 KB.
> To the new table, I added an ID field of type Int, and made it the
> primary key nonclustered, also an identity field. The only other
> index is a different date field in the table that's a clustered index
> (smalldatetime). I also set the index fill factor to 80% from 90% in
> the old one.
> The new table takes 2,406,592 KB; it's bigger because of the extra
> field. BUT the index (as shown in the Task Pad summary) is 163,656
> KB. *How could two single-column indexes take 19 times the storage
> space as one 4-column composite index?* I have run dbcc dbreindex on
> the table.
> I also ran dbcc updateusage on the new table and got trivial
> differences.
> Here is what SHOWCONTIG gives, if that helps. Anything else I can
> look at?
> DBCC SHOWCONTIG scanning 'Transactions-Old' table...
> Table: 'Transactions-Old' (87671360); index ID: 1, database ID: 7
> TABLE level scan performed.
> - Pages Scanned........................: 212443
> - Extents Scanned.......................: 26688
> - Extent Switches.......................: 26687
> - Avg. Pages per Extent..................: 8.0
> - Scan Density [Best Count:Actual Count]......: 99.51% [26556:266
88]
> - Logical Scan Fragmentation ..............: 0.00%
> - Extent Scan Fragmentation ...............: 0.50%
> - Avg. Bytes Free per Page................: 766.4
> - Avg. Page Density (full)................: 90.53%
> DBCC SHOWCONTIG scanning 'Transactions' table...
> Table: 'Transactions' (711673583); index ID: 1, database ID: 7
> TABLE level scan performed.
> - Pages Scanned........................: 280366
> - Extents Scanned.......................: 35103
> - Extent Switches.......................: 35102
> - Avg. Pages per Extent..................: 8.0
> - Scan Density [Best Count:Actual Count]......: 99.84% [35046:351
03]
> - Logical Scan Fragmentation ..............: 0.00%
> - Extent Scan Fragmentation ...............: 0.11%
> - Avg. Bytes Free per Page................: 1562.7
> - Avg. Page Density (full)................: 80.69%
> DBCC SHOWCONTIG scanning 'Transactions' table...
> Table: 'Transactions' (711673583); index ID: 2, database ID: 7
> LEAF level scan performed.
> - Pages Scanned........................: 19966
> - Extents Scanned.......................: 2500
> - Extent Switches.......................: 2499
> - Avg. Pages per Extent..................: 8.0
> - Scan Density [Best Count:Actual Count]......: 99.84% [2496:2500
]
> - Logical Scan Fragmentation ..............: 0.01%
> - Extent Scan Fragmentation ...............: 0.60%
> - Avg. Bytes Free per Page................: 6.2
> - Avg. Page Density (full)................: 99.92%
> I would post the DDL but I can't find the link to get the format...
> Thanks.
> David Walker
>Oops -- the new Transactions table actually has the ID (int) field
indexed twice, both clustered and non-clustered, with 2 different
indexes. Now how did that happen? :-)
I'll fix the indexes and check again.
David Walker
DW <None> wrote in news:ebCGMOjAEHA.3828@.TK2MSFTNGP10.phx.gbl:

> This should be the DDL:
> CREATE TABLE [dbo].[Transactions] (
> [ID] [int] IDENTITY (1000, 1) NOT NULL ,
> [SSN_TIN] [char] (9) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
> NULL
> ,
> [ACCT_NUMBER] [varchar] (20) COLLATE SQL_Latin1_General_CP1_C
I_AS
> NOT NULL ,
> [Settle_Date] [smalldatetime] NOT NULL ,
> [SEQ_NUM] [int] NOT NULL ,
> [FUND_ID] [varchar] (22) COLLATE SQL_Latin1_General_CP1_CI_AS
NOT
> NULL ,
> [TRANS_CODE] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI
_AS
> NOT NULL ,
> [INT_TYPE] [smallint] NOT NULL ,
> [Trade_Date] [smalldatetime] NULL ,
> [QUANTITY] [decimal](16, 6) NOT NULL ,
> [PRICE] [decimal](21, 8) NOT NULL ,
> [PROCEEDS] [money] NULL ,
> [FIN_INST_ID] [smallint] NOT NULL ,
> [FUND_CODE] [varchar] (15) COLLATE SQL_Latin1_General_CP1_CI_
AS
> NULL ,
> [TradeMonth] [int] NULL ,
> [Acct_Pre] [varchar] (3) COLLATE SQL_Latin1_General_CP1_CI_AS
> NULL
> ) ON [PRIMARY]
> GO
> CREATE TABLE [dbo].[Transactions-Old] (
> [SSN_TIN] [char] (9) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
> NULL
> ,
> [ACCT_NUMBER] [varchar] (20) COLLATE SQL_Latin1_General_CP1_C
I_AS
> NOT NULL ,
> [Settle_Date] [smalldatetime] NOT NULL ,
> [SEQ_NUM] [int] NOT NULL ,
> [FUND_ID] [varchar] (22) COLLATE SQL_Latin1_General_CP1_CI_AS
NOT
> NULL ,
> [TRANS_CODE] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI
_AS
> NOT NULL ,
> [INT_TYPE] [smallint] NOT NULL ,
> [Trade_Date] [smalldatetime] NULL ,
> [QUANTITY] [decimal](16, 6) NOT NULL ,
> [PRICE] [decimal](21, 8) NOT NULL ,
> [PROCEEDS] [money] NULL ,
> [FIN_INST_ID] [smallint] NOT NULL ,
> [FUND_CODE] [varchar] (15) COLLATE SQL_Latin1_General_CP1_CI_
AS
> NULL ,
> [TradeMonth] AS (datepart(year,[Trade_Date]) * 100 + datepart
> (month,[Trade_Date])) ,
> [Acct_Pre] AS (left([Acct_Number],3))
> ) ON [PRIMARY]
> GO
> ALTER TABLE [dbo].[Transactions-Old] WITH NOCHECK ADD
> CONSTRAINT [PK_Transactions] PRIMARY KEY CLUSTERED
> (
> [SSN_TIN],
> [ACCT_NUMBER],
> [Settle_Date],
> [SEQ_NUM]
> ) WITH FILLFACTOR = 90 ON [PRIMARY]
> GO
> CREATE CLUSTERED INDEX [IX_CL_Transactions_TradeDate] ON [dbo].
> [Transactions]([ID]) WITH FILLFACTOR = 80 ON [PRIMARY]
> GO
> ALTER TABLE [dbo].[Transactions] ADD
> CONSTRAINT [PK_Transactions_ID] PRIMARY KEY NONCLUSTERED
> (
> [ID]
> ) ON [PRIMARY]
> GO
>
> DW <None> wrote in news:OupT2CjAEHA.3348@.TK2MSFTNGP11.phx.gbl:
>
>|||Um, I fixed the incorrect index to be a clustered index on the
Trade_Date column like it should have been, and the results are
essentially the same. I'm still confused.
Thanks for any insights.
David Walker
DW <None> wrote in news:ebCGMOjAEHA.3828@.TK2MSFTNGP10.phx.gbl:

> This should be the DDL:
> CREATE TABLE [dbo].[Transactions] (
> [ID] [int] IDENTITY (1000, 1) NOT NULL ,
> [SSN_TIN] [char] (9) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
> NULL
> ,
> [ACCT_NUMBER] [varchar] (20) COLLATE SQL_Latin1_General_CP1_C
I_AS
> NOT NULL ,
> [Settle_Date] [smalldatetime] NOT NULL ,
> [SEQ_NUM] [int] NOT NULL ,
> [FUND_ID] [varchar] (22) COLLATE SQL_Latin1_General_CP1_CI_AS
NOT
> NULL ,
> [TRANS_CODE] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI
_AS
> NOT NULL ,
> [INT_TYPE] [smallint] NOT NULL ,
> [Trade_Date] [smalldatetime] NULL ,
> [QUANTITY] [decimal](16, 6) NOT NULL ,
> [PRICE] [decimal](21, 8) NOT NULL ,
> [PROCEEDS] [money] NULL ,
> [FIN_INST_ID] [smallint] NOT NULL ,
> [FUND_CODE] [varchar] (15) COLLATE SQL_Latin1_General_CP1_CI_
AS
> NULL ,
> [TradeMonth] [int] NULL ,
> [Acct_Pre] [varchar] (3) COLLATE SQL_Latin1_General_CP1_CI_AS
> NULL
> ) ON [PRIMARY]
> GO
> CREATE TABLE [dbo].[Transactions-Old] (
> [SSN_TIN] [char] (9) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
> NULL
> ,
> [ACCT_NUMBER] [varchar] (20) COLLATE SQL_Latin1_General_CP1_C
I_AS
> NOT NULL ,
> [Settle_Date] [smalldatetime] NOT NULL ,
> [SEQ_NUM] [int] NOT NULL ,
> [FUND_ID] [varchar] (22) COLLATE SQL_Latin1_General_CP1_CI_AS
NOT
> NULL ,
> [TRANS_CODE] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI
_AS
> NOT NULL ,
> [INT_TYPE] [smallint] NOT NULL ,
> [Trade_Date] [smalldatetime] NULL ,
> [QUANTITY] [decimal](16, 6) NOT NULL ,
> [PRICE] [decimal](21, 8) NOT NULL ,
> [PROCEEDS] [money] NULL ,
> [FIN_INST_ID] [smallint] NOT NULL ,
> [FUND_CODE] [varchar] (15) COLLATE SQL_Latin1_General_CP1_CI_
AS
> NULL ,
> [TradeMonth] AS (datepart(year,[Trade_Date]) * 100 + datepart
> (month,[Trade_Date])) ,
> [Acct_Pre] AS (left([Acct_Number],3))
> ) ON [PRIMARY]
> GO
> ALTER TABLE [dbo].[Transactions-Old] WITH NOCHECK ADD
> CONSTRAINT [PK_Transactions] PRIMARY KEY CLUSTERED
> (
> [SSN_TIN],
> [ACCT_NUMBER],
> [Settle_Date],
> [SEQ_NUM]
> ) WITH FILLFACTOR = 90 ON [PRIMARY]
> GO
> CREATE CLUSTERED INDEX [IX_CL_Transactions_TradeDate] ON [dbo].
> [Transactions]([ID]) WITH FILLFACTOR = 80 ON [PRIMARY]
> GO
> ALTER TABLE [dbo].[Transactions] ADD
> CONSTRAINT [PK_Transactions_ID] PRIMARY KEY NONCLUSTERED
> (
> [ID]
> ) ON [PRIMARY]
> GO
>
> DW <None> wrote in news:OupT2CjAEHA.3348@.TK2MSFTNGP11.phx.gbl:
>
>|||Hi David,
Thank you for using the newsgroup and it is my pleasure to help you with
you issue.
From you informaiton provided, you generate the SQL script from one table.
You noticed that the ID column in the following part:
CREATE CLUSTERED INDEX [IX_CL_Transactions_TradeDate] ON [dbo].
[Transactions]([ID]) WITH FILLFACTOR = 80 ON [PRIMARY]
GO
ALTER TABLE [dbo].[Transactions] ADD
CONSTRAINT [PK_Transactions_ID] PRIMARY KEY NONCLUSTERED
(
[ID]
) ON [PRIMARY]
GO
There is an clustered index and nonclustered index bulid on the table. You
wonder how it come, right?
To get the information on the table, you could run this statement in you
Query Analyzer:
exec sp_help transactions
OR
sp_helpindex transactions
From my experience, in the Enterprise Manger you have first create a
clustered index 'IX_CL_Transactions_TradeDate' on the column ID, then you
create a PRIMARY KEY constraint on this same column. So, finaly, you will
found that the 'sp_help transactions' or 'sp_helpindex transactions' will
show that the index on the column ID is non-clustered.
For maintenance purpose, you could run the DBCC INDEXDEFRAG
Hope this helps and if you still have questions, please feel free to post
your message here and I am glad to help.
Thanks.
Sincerely Yours
Baisong Wei
Microsoft Online Support
----
Get Secure! - www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only. Thanks.|||Hello David,
Thanks to Gert for pointing you in the right direction. For
additional information, I would recommend you to read
"Estimating the Size of a Table with a Clustered Index" topic in the
Books On line which give some formula of calculating the
size of Clustered/Non-Clustered indexs in the Table. As per this
info for a Non-Clustered index
Total leaf index row size (Index_Row_Size) = CIndex_Row_Size +
Fixed_Key_Size + Variable_Key_Size + Index_Null_Bitmap + 1
The final value of 1 represents the index row header.
"CIndex_Row_Size is the total index row size for the clustered index
key".
That means NonClustered Index size would contain Clustered index row
size which is why you see larger size for non-clustered index.
Does that help answer your question ?
Thanks for using MSDN Newsgroup.
Vikrant Dalwale
Microsoft SQL Server Support Professional
Microsoft highly recommends to all of our customers that they visit
the http://www.microsoft.com/protect site and perform the three
straightforward steps listed to improve your computers security.
This posting is provided "AS IS" with no warranties, and confers no
rights.
--
>Subject: Re: Index size question (DDL)
>From: DW <None>
>References: <OupT2CjAEHA.3348@.TK2MSFTNGP11.phx.gbl>
<ebCGMOjAEHA.3828@.TK2MSFTNGP10.phx.gbl>
>User-Agent: Xnews/06.08.25
>Message-ID: <O6osh$jAEHA.1212@.TK2MSFTNGP12.phx.gbl>
>Newsgroups: microsoft.public.sqlserver.server
>Date: Thu, 04 Mar 2004 15:24:44 -0800
>NNTP-Posting-Host: nensdsllascruces195.hyperspeeddsl.com
209.136.33.195
>Lines: 1
>Path:
cpmsftngxa06.phx.gbl!TK2MSFTNGXA06.phx.gbl!TK2MSFTNGXA05.phx.gbl!TK2MS
FTNGP08.phx.gbl!TK2MSFTNGP12.phx.gbl
>Xref: cpmsftngxa06.phx.gbl microsoft.public.sqlserver.server:332420
>X-Tomcat-NG: microsoft.public.sqlserver.server
>Um, I fixed the incorrect index to be a clustered index on the
>Trade_Date column like it should have been, and the results are
>essentially the same. I'm still confused.
>Thanks for any insights.
>David Walker
>
>DW <None> wrote in news:ebCGMOjAEHA.3828@.TK2MSFTNGP10.phx.gbl:
>
SQL_Latin1_General_CP1_CI_AS[color=darkr
ed]
NOT
SQL_Latin1_General_CP1_CI_AS[color=darkr
ed]
SQL_Latin1_General_CP1_CI_AS[color=darkr
ed]
SQL_Latin1_General_CP1_CI_AS[color=darkr
ed]
NOT
SQL_Latin1_General_CP1_CI_AS[color=darkr
ed]
SQL_Latin1_General_CP1_CI_AS[color=darkr
ed]
it.
exact.
first
index
90% in
163,656
storage
dbreindex on
[26556:26688]
[35046:35103]
[2496:2500]
format...
>|||> info for a Non-Clustered index
> Total leaf index row size (Index_Row_Size) = CIndex_Row_Size +
> Fixed_Key_Size + Variable_Key_Size + Index_Null_Bitmap + 1

> That means NonClustered Index size would contain Clustered index row
> size which is why you see larger size for non-clustered index.
What exactly is "clustered index row size" and "index row size"? I
can't quite parse that... The clustered index is on a column, and all
rows have the same size ;-)
And larger, yes, but this much larger' hmmmmm...
Both tables, where one is a copy of the other, have clustered indexes.
The size of the first table's clustered index (on the first 4 columns)
is 8 meg. The size of the second tables two indexes, one clustered on a
smalldatetime field and the other non-clustered on Account Number and
SSN, is 137 meg. So the non-clustered index takes 129 meg.
Any more help would be great. Mr Wei, I didn't "wonder how it come", I
wondered why the one that should be smaller was so much bigger than the
other. I ran dbreindex, but I'll run indexdefrag as you suggested, and
see what that does.
Thanks.
David Walker
vikrantd@.online.microsoft.com (Vikrant V Dalwale [MSFT]) wrote in news:
#q#3CcTBEHA.4044@.cpmsftngxa06.phx.gbl:

>
> Hello David,
> Thanks to Gert for pointing you in the right direction. For
> additional information, I would recommend you to read
> "Estimating the Size of a Table with a Clustered Index" topic in the
> Books On line which give some formula of calculating the
> size of Clustered/Non-Clustered indexs in the Table. As per this
> info for a Non-Clustered index
> Total leaf index row size (Index_Row_Size) = CIndex_Row_Size +
> Fixed_Key_Size + Variable_Key_Size + Index_Null_Bitmap + 1
> The final value of 1 represents the index row header.
> "CIndex_Row_Size is the total index row size for the clustered index
> key".
> That means NonClustered Index size would contain Clustered index row
> size which is why you see larger size for non-clustered index.
> Does that help answer your question ?
> Thanks for using MSDN Newsgroup.
> Vikrant Dalwale
> Microsoft SQL Server Support Professional
>
> Microsoft highly recommends to all of our customers that they visit
> the http://www.microsoft.com/protect site and perform the three
> straightforward steps listed to improve your computers security.
> This posting is provided "AS IS" with no warranties, and confers no
> rights.
>
> --
> <ebCGMOjAEHA.3828@.TK2MSFTNGP10.phx.gbl>
> 209.136.33.195
> cpmsftngxa06.phx.gbl!TK2MSFTNGXA06.phx.gbl!TK2MSFTNGXA05.phx.gbl!TK2MS
> FTNGP08.phx.gbl!TK2MSFTNGP12.phx.gbl
> SQL_Latin1_General_CP1_CI_AS
> NOT
> SQL_Latin1_General_CP1_CI_AS
> SQL_Latin1_General_CP1_CI_AS
> SQL_Latin1_General_CP1_CI_AS
> NOT
> SQL_Latin1_General_CP1_CI_AS
> SQL_Latin1_General_CP1_CI_AS
> it.
> exact.
> first
> index
> 90% in
> 163,656
> storage
> dbreindex on
> [26556:26688]
> [35046:35103]
> [2496:2500]
> format...
>|||I posted before reading that BOL topic; sorry -- I'll check it out.
In addition to the formulas, what is the logical reason, that I can wrap my
head around, why a nonclustered index would be 19 times as large as a
clustered index?
Thanks.
David Walker|||You may want to read my reply again, and check out BOL on indexes.
Bottom line is, that your clustered index is not 8 MB, and so the NC
index is not 19 times larger. Only the branches (the pointers to the
lowest level index pages) are 8 MB in total. As mentioned before, no
system can possibly index a table with just 0.5 bytes per row (on
average). The index will need at least the same amount of space as the
(average) size of the indexed column.
Actually, your clustered index used 1716 MB (table data which includes
leafs of clustered index 1708MB + branches of clustered index 8MB). So
with 129 MB I would say your NC index is considerably smaller...
Gert-Jan
DW wrote:
> I posted before reading that BOL topic; sorry -- I'll check it out.
> In addition to the formulas, what is the logical reason, that I can wrap m
y
> head around, why a nonclustered index would be 19 times as large as a
> clustered index?
> Thanks.
> David Walker
(Please reply only to the newsgroup)|||OK, thanks for the info. I'm reading Inside SQL Server 2000, and when I'm
done, I should understand!
David
Gert-Jan Strik <sorry@.toomuchspamalready.nl> wrote in
news:4050DBB4.42F156BA@.toomuchspamalready.nl:

> You may want to read my reply again, and check out BOL on indexes.
> Bottom line is, that your clustered index is not 8 MB, and so the NC
> index is not 19 times larger. Only the branches (the pointers to the
> lowest level index pages) are 8 MB in total. As mentioned before, no
> system can possibly index a table with just 0.5 bytes per row (on
> average). The index will need at least the same amount of space as the
> (average) size of the indexed column.
> Actually, your clustered index used 1716 MB (table data which includes
> leafs of clustered index 1708MB + branches of clustered index 8MB). So
> with 129 MB I would say your NC index is considerably smaller...
> Gert-Jan
>
> DW wrote:
>

Index on UDT

Hi,
I wanted to create an index on a property of my UDT and used this DDL:
create table t2(
c1 int identity,
c2 point,
c3 as c2.X persisted,
c4 as c2.Y persisted)
go
But I get this error:
Msg 4936, Level 16, State 1, Line 1
Computed column 'c3' in table 't2' cannot be persisted because the column is
non-deterministic.
X and Y are properties of Point UDT, but I cannot use
SqlFunction(IsDeterministic:=True) for that.
Any help would be greatly appreciated.
LeilaLook up SqlMethodAttribute in MSDN.
Adam Machanic
Pro SQL Server 2005, available now
http://www.apress.com/book/bookDisplay.html?bID=457
--
"Leila" <Leilas@.hotpop.com> wrote in message
news:%23fifF85LGHA.2320@.TK2MSFTNGP11.phx.gbl...
> Hi,
> I wanted to create an index on a property of my UDT and used this DDL:
> create table t2(
> c1 int identity,
> c2 point,
> c3 as c2.X persisted,
> c4 as c2.Y persisted)
> go
> But I get this error:
> Msg 4936, Level 16, State 1, Line 1
> Computed column 'c3' in table 't2' cannot be persisted because the column
> is non-deterministic.
> X and Y are properties of Point UDT, but I cannot use
> SqlFunction(IsDeterministic:=True) for that.
> Any help would be greatly appreciated.
> Leila
>|||I used this attribute for X property but generated error: This attribute is
not valid on this declaration type.
"Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message
news:%23MLKkb9LGHA.500@.TK2MSFTNGP15.phx.gbl...
> Look up SqlMethodAttribute in MSDN.
>
> --
> Adam Machanic
> Pro SQL Server 2005, available now
> http://www.apress.com/book/bookDisplay.html?bID=457
> --
>
> "Leila" <Leilas@.hotpop.com> wrote in message
> news:%23fifF85LGHA.2320@.TK2MSFTNGP11.phx.gbl...
>|||You need to set it on the get or set method of the property independently.
e.g.:
public int X
{
[SqlMethodAttribute(...)]
get
{
//...
}
set
{
//...
}
}
Adam Machanic
Pro SQL Server 2005, available now
http://www.apress.com/book/bookDisplay.html?bID=457
--
"Leila" <Leilas@.hotpop.com> wrote in message
news:%23AgVJVBMGHA.964@.tk2msftngp13.phx.gbl...
>I used this attribute for X property but generated error: This attribute is
>not valid on this declaration type.
> "Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message
> news:%23MLKkb9LGHA.500@.TK2MSFTNGP15.phx.gbl...
>|||Thanks indeed :-)
"Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message
news:OBV22nCMGHA.2628@.TK2MSFTNGP15.phx.gbl...
> You need to set it on the get or set method of the property independently.
> e.g.:
> public int X
> {
> [SqlMethodAttribute(...)]
> get
> {
> //...
> }
> set
> {
> //...
> }
> }
> --
> Adam Machanic
> Pro SQL Server 2005, available now
> http://www.apress.com/book/bookDisplay.html?bID=457
> --
>
> "Leila" <Leilas@.hotpop.com> wrote in message
> news:%23AgVJVBMGHA.964@.tk2msftngp13.phx.gbl...
>

Index Discussion

Hi Folks,

Got a topic open for debate.

We currently have an archive table - DDL

CREATE TABLE [dbo].[Audit] (
[id] [int] identity (1,1) NOT NULL ,
[col1] [char] (10) NOT NULL ,
[col2] [char] (15) NOT NULL ,
[col3] [int] NOT NULL ,
[col4] [varchar] (50) NOT NULL ,
[col5] [datetime] NOT NULL ,
[col6] [varchar] (4000) NULL ,
[col7] [char] (3) NULL
)
GO

This table grows to about 40 million rows during the course of the month. The table has a clustered index on the id field and a non clustered index on the col2 and col3. The id column is not used in queries. At the moment we run weekly dbcc reindexes on all the indexes. We are running into a space issue on the reindex of the clustered index (copying the whole table out , ordering etc) and are considering dropping the index or changing to a non clustered index. (The DBCC utility that we have built will only rebuilt all the indexes or none at all.)

I feel this is not a good idea and know my reasons. I would like some input as to why this might prove a bad idea.

Will it increase page splitting? Will the table performance be impacted even if the queries are not specifically using the clustered index?

What are the reasons for and against?

Thanks FolksWhy do yuo have an [id] column if you don't use it?

I am IDENTITY, there for I am....

Drop the Index

The when people start screaming, the create a non unique index...|||The when people start screaming, the create a non unique index...:D :D ... they don't scream... simply blame SQL server with no cause.:mad:|||I never designed the schema. Bag of pish if you ask me.

Nevertheless before I drop the index :

Will the table start to split pages if we lose the clustered ?

The table is inserted into the by date order. There is no index on the datatime field but the id field clustered index maintains the date order of the table. Would go if the clustered index was dropped?

Do the other non-clustered indexes not use the clustered as a backbone? Will the non-clustered grow if the clustered was dropped?

Am basing my concerns over dropping this index mainly from the advice on the link below.

http://www.sql-server-performance.com/clustered_indexes.asp

More conjecture please.|||OK, first, the order of data in a database has no meaning...

Second, (and I should have said this earlier), do NO alteration in a prod environment until you tested ANY approach in DEV

Me telling you to (and off the cuff) to just drop the index was so bad, I had to drink many margaritas to forget it...

Well, ok, I'm always looking for an excuse...

And no, indexes are not dependant on each other...

And keeping an IDENTITY to make sure the dates are in the right order (did I read that right) doesn't make sense to me...

The big question is...

CREATE TABLE with clustered index

Data is meant to be stored in that order...however data will be put on pages where it finds room...how much free space?

When the table is REORG'ed it will order the data by it...

Now the question...you drop a cluster, and then reorg...what happens?

Don't know, I'll have to test it...

However, I think you have bigger issues...|||You know what? I wanted to do the following, and then reorg the data pages...(it's a db2 term I guess), and realized I don't know how, except to unload and load (OK, another db2 term, bcp out and bcp in)...

Anyone?

I've DBCC REINDEX, but it doesn't mention anything about the pages...

got to be a way...

USE Northwind
GO

CREATE TABLE myTable99 (
Col1 int NOT NULL
, Col2 char(1) NOT NULL
)
GO

CREATE UNIQUE CLUSTERED INDEX myTable99_IX1 ON myTable99(Col1)
CREATE INDEX myTable99_IX2 ON myTable99(Col2)
GO

sp_help myTable99
GO

INSERT INTO myTable99(Col1, Col2)
SELECT 1, 'A' UNION ALL
SELECT 2, 'B' UNION ALL
SELECT 3, 'C' UNION ALL
SELECT 4, 'D'
GO

SELECT * FROM myTable99
GO

DROP INDEX myTable99.myTable99_IX1
GO

sp_help myTable99
GO

INSERT INTO myTable99(Col1, Col2)
SELECT 5, 'E' UNION ALL
SELECT 6, 'F' UNION ALL
SELECT 7, 'G' UNION ALL
SELECT 8, 'H'
GO

SELECT * FROM myTable99
GO|||Thanks for the feedback Brett,

Don't worry - Would never drop an index on a table in prod without fully testing and understanding the implications before doing so

Hence this thread...

Going to some testing and get back to you.

Is there a way in T-SQL you can check the size of a specific index?

Don't trust EM ....|||Originally posted by aldo_2003
Don't trust EM ....

Good...

Of a specific index?

Anyone...

sp_spaceused myTable99
GO

Will tell you the size of all...

I'll keep looking...

EDIT: IF this was DB2 I'd have an answer...|||sysindexes tells you how many pages where used

can we simply multiply this by 8kb to get the answer?|||ANytime to get accurate sizes better to DBCC UPDATEUSAGE or use @.UPDATEUSAGE='TRUE' in SP_SPACEUSED statements.|||Brett, Satya ,

Have done a bit of testing

Inserted 6 million rows into this table with all the indexes
i.e 1 clustered and 2 non clustered

Did sp_spaceused with DBCC UPDATEUSAGE
index size = 233278kb

Then i dropped the clustered index

Did sp_spaceused with DBCC UPDATEUSAGE

index size = 284104kb

Why has the total index size gone up when I have dropped the an index?

What is going on ??

"And no, indexes are not dependant on each other..."?

Anybody ?|||Have you performed DBCC DBREINDEX before and after CLustered Index drop?

I've bit doubt in this regard after this it should return correct sizes.|||Have done this and tested

Still get the same result -

i.e increse in overall index size when I drop the clustered index

has anyone else noticed this behaviour or am I the only one.

shame SQL Server has no T-SQL to check out the size of specific indexes|||Thats for sure there is no direct deal to get the result.
http://www.sql-server-performance.com/q&a13.asp - review for information.

HTH|||Thanks Satya,

Still don't know what is happening with my indexes though|||Index internals. I still wish I had a chance to get to that lecture when it was around last, but here is what I do know.

A clustered index is an index that has the data pages as its leaf pages. In otherwords, this is the order in which the data is supposed to be stored on disk. With extent switches, and pages from other indexes peppered in, the data may not be contiguous, but the theory is there. The beauty of a clustered index is that if you are expecting ranges of data to be scanned, the data is all in a nice row on the disk to be scooped up. The bad side is that if you are planting data in the table in a random order, you end up with all sorts of page splits. This is why clustered indexes on Identity columns became all the rage.

You may remember that Microsoft suggests that you make the clustered key as small as possible, as well. With just the above reasons, there is no justification for this, so there is a second reason. Any non-clustered index will use the clustered index key in place of the rowid, if a clustered index exists. This means that a generic record for a non-clustered index looks like this:

indexed column1, indexed column2..., indexed columnn:clustered index column1, clustered index column2,...clustered index columnn

When you have an integer as the sole clustered index key, the second part of the non-clustered index row is quite small (4 bytes), but if you substitute a rowid (I think a rowid is fileid:pageid:slot number), then you have increased the size of the individual records in the index.

Clear as mud?

Now, the question becomes, are you running queries on the archive table?|||Excellent Answer MCrowley

So non clustered indexes do use clustered indexes to assist there own structure.

We do run queries against the archive table but performance is not a key factor. Non OLTP type enviroment.

What is an issue is the space and especially the space when a rebuild of the clustered index occurs.

I'm going to take some of the queries run by our users into our dev enviroment and make sure that the server does not freak out when I run the same queries after dropping the clustered index.

Thanks to all whom have helped me out on this.
Learned quite a bit about indexes this week - time for a bevy ...!|||One last thing I forgot to mention. I believe that when you run dbcc dbreindex against a clustered index, the whole table (i.e. the leaf nodes of the clustered index) is copied to a new location. So effectively you need to have as much free space in the database as the table takes up, in order to be successful. DBCC DBREINDEX against a clustered index (or just run against the table name) also has the unfortunate side effect of rebuilding all of the non-clustered indexes, too, so you have to add that space on, too. DBCC INDEXDEFRAG is not as effective as DBREINDEX, but it is nicer to the system.|||Thanks again

Lets hope future versions of SQL make easier for DBA's to size all objects (i.e specific indexes) in the databases with the ability to attribute the overall size of the database to the sum of the objects within it.

All the best.|||Testing at my end proves to be working in terms of sizes what you're looking for.

For instance with clustered index presence database free space was 2.4gigs and after removal it was 2.8gigs.

I will explain more about this on Monday.:cool:|||From BOL:

Nonclustered indexes can be defined on a table with a clustered index, a heap, or an indexed view. In Microsoft SQL Server 2000, the row locators in nonclustered index rows have two forms:

If the table is a heap (does not have a clustered index), the row locator is a pointer to the row. The pointer is built from the file identifier (ID), page number, and number of the row on the page. The entire pointer is known as a Row ID.

If the table does have a clustered index, or the index is on an indexed view, the row locator is the clustered index key for the row. If the clustered index is not a unique index, SQL Server 2000 makes duplicate keys unique by adding an internally generated value. This value is not visible to users; it is used to make the key unique for use in nonclustered indexes. SQL Server retrieves the data row by searching the clustered index using the clustered index key stored in the leaf row of the nonclustered index.
Because nonclustered indexes store clustered index keys as their row locators, it is important to keep clustered index keys as small as possible. Do not choose large columns as the keys to clustered indexes if a table also has nonclustered indexes.|||I would also experiment with having col5, col2, and col3 as clustered index, and id as unique constraint against existing queries.|||The whole point is to save some space on server, so not in terms of performance perspective. By dropping the existing clustered index it can save 500megs atleast and by adding this composite clustered index it will addup more space.|||Originally posted by Satya
The whole point is to save some space on server, so not in terms of performance perspective. By dropping the existing clustered index it can save 500megs atleast and by adding this composite clustered index it will addup more space.

First, I said "experiment", second, - I was trying to combine the need for performance to be retained while trying to eliminate the need for reindexing on a weekly basis by structuring the clustered index in such a way that reindexing will not be needed. And I think this approach will work better than dropping the index while still starving for space maybe in a couple of weeks due to increase in data (my 2 cents)|||Originally posted by rdjabarov
First, I said "experiment", second, - I was trying to combine the need for performance to be retained while trying to eliminate the need for reindexing on a weekly basis by structuring the clustered index in such a way that reindexing will not be needed. And I think this approach will work better than dropping the index while still starving for space maybe in a couple of weeks due to increase in data (my 2 cents)
No worries mate, just hurl thru.