Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
Jim77
Constraint Violating Yak Guru
440 Posts |
 Posted - 2008-08-01 : 07:16:33
|
Hi there guys,I know it has been asked a million times before and I thought I had long since grasped the basic concepts of indexing tables from websites like SQLTeam, but I can't seem to get my head around our product system containing loads of tables like the one below with many non-clustered indexs but no clustered index: ie:sp_helpindex MasterProductIX_Product nonclustered located on PRIMARY EanTraderIX_Product_1 nonclustered located on PRIMARY OrderFormTypePK_Product nonclustered, unique located on PRIMARY ProductCodewhat is the point as non clustered are built around clustered, I have been told by our supplier that maintains our Product System that Clustered indexes are not needed in there tables at all which I find hard to believe. |
|
|
spirit1
Cybernetic Yak Master
11752 Posts |
Posted - 2008-08-01 : 08:14:20
|
| from the NC index standpoint it doesn't care if you have a CI or not. if you have CI than it points to the CI identifier, if not it points to the rowId identifier.as for the "CI's are not needed part" they'll have to give you a more valid argument than that._______________________________________________Causing trouble since 1980Blog: http://weblogs.sqlteam.com/mladenpSpeed up SSMS development: www.ssmstoolspack.com <- version 1.0 out! |
 |
|
|
Jim77
Constraint Violating Yak Guru
440 Posts |
Posted - 2008-08-01 : 10:38:18
|
| in a nutshell I like that explanation thanks ! |
|
|
|
sodeep
Master Smack Fu Yak Hacker
7174 Posts |
Posted - 2008-08-01 : 10:56:40
|
| Well if you are fetching lots of data and using Where , between ,join, then you need Clustered index becoz leaf level of it contains data. |
|
|
|
Jim77
Constraint Violating Yak Guru
440 Posts |
Posted - 2008-08-01 : 11:10:40
|
| yes and the reason for the NC creation was to get leaf level datafrom several tables using certain criteria expressed in the WHERE clause, I need to inform them CI is the way forward and then there NC's will work better. |
|
|
|
Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)
7020 Posts |
Posted - 2008-08-01 : 11:35:21
|
| The simple explanation is that the vendor does not know how to implement a SQL Server database.Unfortunately, this is normal for vendor supplied software. From my experience, less than one vendor out of twenty knows enough to create a physical design I would call minimally acceptable.CODO ERGO SUM |
|
|
|
rmiao
Master Smack Fu Yak Hacker
7266 Posts |
Posted - 2008-08-01 : 23:33:54
|
| Agree, just count how many varchar(1) columns in those vendor supplied dbs. |
|
|
|
LoztInSpace
Aged Yak Warrior
940 Posts |
Posted - 2008-08-03 : 09:58:40
|
| I'm confused. Are you asking why there is no clustered index? There is never an actual mandatory need for one - it's an implementation option but not mandatory. None of the indexes you describe look like they should be clustered.Am I missing something? |
|
|
|
rmiao
Master Smack Fu Yak Hacker
7266 Posts |
Posted - 2008-08-03 : 17:53:48
|
| >> None of the indexes you describe look like they should be clustered.Hard to say without knowing data and app, may need one on other column? |
|
|
|
LoztInSpace
Aged Yak Warrior
940 Posts |
Posted - 2008-08-04 : 10:21:53
|
| @rmiao - That's what I said - none of the indexes described look like they should be clustered. Maybe on another column but we have not been shown any other columns.Looks like a case of 'I have heard clustered indexes are good so I need one at any cost'.@Jimn77 - Why do you think you need one? |
|
|
|
Jim77
Constraint Violating Yak Guru
440 Posts |
Posted - 2008-08-05 : 07:01:25
|
| sorry for the late reply guys Loztinspace you are correct 'I have heard clustered indexes are good so I need one at any cost' school of thoughtthis table I am referring to is a dimension table in a AS2000 cube, it is also used for ad hoc querying from QA where filters are mostly attained to the productcode field ie select * from product where productcode between 5678 and 8907 but predominintly a SCD1 dimension table with the following schema, but since NC's are built around a rowId if no CI is in place that spirit1 so helpfully pointed out i amay not need them.CREATE TABLE [Product] ( [ProductCode] [char] (7) COLLATE Latin1_General_CI_AS NOT NULL , [Description] [char] (45) COLLATE Latin1_General_CI_AS NULL , [Warehouse] [char] (1) COLLATE Latin1_General_CI_AS NULL , [AltSection] [char] (3) COLLATE Latin1_General_CI_AS NULL , [Supplier] [char] (6) COLLATE Latin1_General_CI_AS NULL , [StorePack] [int] NULL , [ItemType] [char] (1) COLLATE Latin1_General_CI_AS NULL , [Size] [char] (15) COLLATE Latin1_General_CI_AS NULL , [FreeProdInd] [char] (1) COLLATE Latin1_General_CI_AS NULL , [RandomWgt] [char] (1) COLLATE Latin1_General_CI_AS NULL , [Status] [char] (1) COLLATE Latin1_General_CI_AS NULL , [VatCode] [char] (1) COLLATE Latin1_General_CI_AS NULL , [EanConsumer] [char] (13) COLLATE Latin1_General_CI_AS NULL , [EanTrader] [char] (14) COLLATE Latin1_General_CI_AS NULL , [NSLCode] [char] (7) COLLATE Latin1_General_CI_AS NULL , [Section] [char] (3) COLLATE Latin1_General_CI_AS NULL , [PriceChallenge] [char] (1) COLLATE Latin1_General_CI_AS NULL , [OrderFormType] [char] (3) COLLATE Latin1_General_CI_AS NULL , [LabelCode] [char] (1) COLLATE Latin1_General_CI_AS NULL , [FlashPack] [char] (1) COLLATE Latin1_General_CI_AS NULL , [CrtgType] [char] (3) COLLATE Latin1_General_CI_AS NULL , [Dept] [char] (3) COLLATE Latin1_General_CI_AS NULL , [AutoSubProd] [char] (7) COLLATE Latin1_General_CI_AS NULL , [SpecOffProd] [char] (7) COLLATE Latin1_General_CI_AS NULL , [UnitPrSeqNo] [int] NULL , [UnitPrQty] [decimal](18, 4) NULL , [Notation] [char] (20) COLLATE Latin1_General_CI_AS NULL , [ReceiptDesc] [char] (30) COLLATE Latin1_General_CI_AS NULL , [NoWksSold] [int] NULL , [CreDate] [datetime] NULL , [WhseCostPr] [money] NULL , [BranACostPr] [money] NULL , [BranBCostPr] [money] NULL , [WhseEffDte] [int] NULL , [BranAEffDte] [int] NULL , [BranBEffDte] [int] NULL , [RetPr1] [money] NULL , [RetPr2] [money] NULL , [RetPr3] [money] NULL , [RetPr4] [money] NULL , [RetPr5] [money] NULL , [RetPr6] [money] NULL , [RetPr7] [money] NULL , [RetPr8] [money] NULL , [RetPr9] [money] NULL , [RetPr10] [money] NULL , [RP1EffDte] [int] NULL , [RP2EffDte] [int] NULL , [RP3EffDte] [int] NULL , [RP4EffDte] [int] NULL , [RP5EffDte] [int] NULL , [RP6EffDte] [int] NULL , [RP7EffDte] [int] NULL , [RP8EffDte] [int] NULL , [RP9EffDte] [int] NULL , [RP10EffDte] [int] NULL , [StockTakeType] [char] (1) COLLATE Latin1_General_CI_AS NOT NULL , [CrtgType2] [char] (3) COLLATE Latin1_General_CI_AS NULL , [NextTranNo] [int] NOT NULL , [Notes] [varchar] (200) COLLATE Latin1_General_CI_AS NULL , [Pallet] [int] NOT NULL , [Layer] [int] NOT NULL , [ShopStatus] [char] (1) COLLATE Latin1_General_CI_AS NOT NULL , [AgeRestricted] [char] (2) COLLATE Latin1_General_CI_AS NOT NULL , [UnderAgeOp] [char] (1) COLLATE Latin1_General_CI_AS NOT NULL , [HostInd] [char] (1) COLLATE Latin1_General_CI_AS NOT NULL , [ProtectRetail] [char] (1) COLLATE Latin1_General_CI_AS NOT NULL , [SubSection] [varchar] (4) COLLATE Latin1_General_CI_AS NOT NULL , [StopSale] [char] (1) COLLATE Latin1_General_CI_AS NOT NULL , [PosMessCode] [char] (4) COLLATE Latin1_General_CI_AS NULL , [LoyalBonPoints] [int] NOT NULL , [SELDesc] [varchar] (45) COLLATE Latin1_General_CI_AS NULL , [LastChangeDate] [datetime] NOT NULL , [TillType] [int] NOT NULL , [DateShopDelete] [datetime] NOT NULL , [SuppProdCode] [varchar] (20) COLLATE Latin1_General_CI_AS NULL , [NewsType] [char] (1) COLLATE Latin1_General_CI_AS NOT NULL , [NewsFrequency] [varchar] (20) COLLATE Latin1_General_CI_AS NULL , [WgtFactor] [money] NOT NULL , [StoresApplicable] [char] (26) COLLATE Latin1_General_CI_AS NULL , [SellingUnit] [char] (4) COLLATE Latin1_General_CI_AS NOT NULL , [WareHouseStatus] [char] (1) COLLATE Latin1_General_CI_AS NOT NULL , [AltpackProd] [char] (7) COLLATE Latin1_General_CI_AS NULL , [DutyNisaJsy] [money] NOT NULL , [DutyNisaGsy] [money] NOT NULL , [NisaStorePack] [int] NOT NULL , [OrderingFactor] [decimal](18, 2) NOT NULL , [JsySfwySuppCode] [varchar] (12) COLLATE Latin1_General_CI_AS NULL , [JsySfwySuppProdCode] [varchar] (13) COLLATE Latin1_General_CI_AS NULL , [GsySfwySuppCode] [varchar] (12) COLLATE Latin1_General_CI_AS NULL , [GsySfwySuppProdCode] [varchar] (13) COLLATE Latin1_General_CI_AS NULL , [TargetMargin] [decimal](18, 2) NOT NULL , [CreatedBy] [varchar] (50) COLLATE Latin1_General_CI_AS NULL , [AmendedBy] [varchar] (50) COLLATE Latin1_General_CI_AS NULL , [ThreeMonthVat] [char] (1) COLLATE Latin1_General_CI_AS NOT NULL , [ReductPercent] [decimal](18, 2) NOT NULL , [MinPrice] [money] NOT NULL , [MaxPrice] [money] NOT NULL , [ExceptionFlag] [char] (1) COLLATE Latin1_General_CI_AS NOT NULL , [SWYEDLP] [char] (1) COLLATE Latin1_General_CI_AS NOT NULL , [SWYREM] [char] (1) COLLATE Latin1_General_CI_AS NOT NULL , [BasePrice] [money] NOT NULL , [KVI] [char] (1) COLLATE Latin1_General_CI_AS NOT NULL ) ON [PRIMARY]GO |
|
|
|
|
|
|
|
|
Why no clustered index ?