Tips on Optimizing SQL Server Clustered Indexes
http://www.sql-server-performance.com/tips/clustered_indexes_p1.aspx
As a rule of thumb, every table should have a clustered index. Generally, but not always, the clustered index should be on a column that monotonically increases--such as an identity column, or some other column where the value is increasing--and is unique. In many cases, the primary key is the ideal column for a clustered index.
If you have any experience with performance tuning SQL Server 6.5, you may have heard that is not a good idea to add a clustered index to a column that monotonically increases because it can cause a "hotspot" on the disk that can cause performance problems. That advice is true in SQL Server 6.5.
Normally, "hotspots" aren't generally a problem. You would have to have over 1,000 transactions a second before a "hotspot" were to negatively affect performance. In fact, a "hotspot" can be beneficial under these circumstances because it eliminates page splits.
Here's why. If you are inserting new rows into a table that has a clustered index as its primary key, and the key monotonically increases, this means that each INSERT will physically occur one after another on the disk. Because of this, page splits won't occur during INSERTs, which in itself saves overhead. This is because SQL Server has the ability to determine if data being inserted into a table has a monotonically increasing sequence, and won't perform page splits when this happens.
If you are inserting a lot of rows into a heap (a table without a clustered index), data is not inserted in any particular order onto data pages, whether the data is monotonically or not monotonically increasing. This results in SQL Server having to work harder (more reads) to access the data when requested from disk. On the other hand, if a clustered index is added to a table, data is inserted sequentially on data pages, and generally less disk I/O is required to retrieve the data when requested f
相关文档:
1、定义基本表
SQL语言使用动词CREATE定义基本表,其具体语法格式如下:
CREATE TABLE <表名>
(<列名><数据类型>[列级完整性约束条件]...[,<列名><数据类型>[列级完整性约束条件]][,<表级完整性约束条件>]);
例如:建立一个学生表Student,它由学号Sno,姓名Sname,性别Ssex,年 ......
MS SQL Server查询优化方法
作者:xmllover 2007-11-29
查询速度慢的原因很多,常见如下几种
1、没有索引或者没有用到索引(这是查询慢最常见的问题,是程序设计的缺陷)
2、I/O吞吐量小,形成了瓶颈效应。
3、没有创建计算列导致查询不优化。
4、内存 ......
Rebuild indexes online with SQL Server 2005
http://blogs.techrepublic.com.com/datacenter/?p=249
Online index rebuild
SQL Server 2005 introduces the ability to rebuild your indexes in an
online fashion so that other processes are able to access the table
while the rebuild is occurring. Because y ......
[Sql]EXCEPT 和 INTERSECT关键字
http://www.cnblogs.com/treeyh/archive/2008/07/01/1232845.html
EXCEPT
从 EXCEPT 操作数左边的查询中返回右边的查询未返回的所有非重复值。
INTERSECT
返回 INTERSECT 操作数左右两边的两个查询均返回的所有非重复值。
A. 使用 EXCEPT
在示例中使用 TableA 和 TableB 中的数据。
......