把城镇涂成红色和非聚簇索引

我记得从我Sybase, SQL Server表可以支持多达255个索引。一个聚集索引和254个非聚簇索引。在Microsoft SQL Server改为249非聚簇索引。这些额外的5个潜在索引去了哪里?作为一个粗略的经验法则,我们总是试着为10或更少的索引/表OLTP环境有什么大不了的呢?现在,SQL Server 2008最多支持999非聚簇索引表。为什么?通常情况下,我们必须注意非聚簇索引。他们加快查找的时候我们只需要数据的一个子集。但他们可以放慢更新如果有太多。 The analogy we use is that a non-clustered index is like the index at the back of a reference book. We can quickly look up information without reading the whole book. The clustered index is more like the table of contents. It’s the logical order the data is stored in. Because the actual data is also stored in the clustered index, the correct analogy for this type of index is the book’s table of contents plus the pages themselves i.e. the whole book. So the clustered index is really the table itself. As we update data in the table, SQL Server has to keep each index up to date with the new data. So while many indexes may speed up overall read time, they may slow down updates. The exception to the rule is the Data Warehouse which is typically a large historical database that is read-only during the day. Since no updates are being made, we can go to town on the index front. Create as many as we want. SQL Server does not have to update them since no updates are allowed. They do take up more space which may be less of a worry in these days of cheap storage . If we have to trade storage for performance usually we go for the faster option. With the Data Warehouse, the classic architecture is the daily load cycle. Every night, we load the day’s activity from the operational databases into the warehouse. Before we do this, we usually drop all the indexes anyway then load the new data and rebuild the indexes. This usually is the fastest option. Data Partitioning can also speed up this process if designed correctly. In SQL Server 2008, sparse columns and wide tables are supported. A sparse column is where a majority of values in a column are going to be NULL and a table that supports these sparse columns is called a wide table. You are actually allowed up to 30,000 columns now in a wide table. You can also create filtered indexes on sparse columns so that only the non-NULL values are saved in the index. An example of this is where a Product table supports many products but only a small number of products are TV sets. You might have a column for TV Size but the majority of the values of this column will be NULL. Only the TV set products would have a non-NULL entry. So defining it as a sparse column and creating a filtered index will enable SQL Server to optimize access to this column and also save space. Now this actually is a violation of the normalization rules so Dr. Codd would not be amused but the feature is there if you need it. This is the real reason Microsoft increased the supported number of indexes. So some customers must be really going to town for Microsoft to expand support from 249 to 999 indexes per table. In fact, they must be painting the town red and leaving everything in Las Vegas! Maximum Capacity Specifications for SQL Server 2008: http://msdn.microsoft.com/en-us/library/ms143432.aspx Cheers Brian