[2.z] Alex's SQL Server - 随笔分类 - Alex Tian

sql server中index的REBUILD和REORGANIZE

摘要：参考文献：http://technet.microsoft.com/en-us/library/ms188388.aspx正文本文主要讲解如何使用alter index来rebuild和reorganize索引来清除碎片，rebuild能够完全清除碎片，但是reorganize却不能。Rebuild index--1.准备实验数据select * into Employee from AdventureWorks2008R2.HumanResources.Employee;--2.查看使用空间：Employee 290 72 KB 56 KB 8 KB ... 阅读全文

posted @ 2013-12-05 14:17 Alex Tian 阅读(2688) 评论(0) 推荐(0)

How to find user who ran DROP or DELETE statements on your SQL Server Objects

摘要：http://www.mssqltips.com/sqlservertip/3090/how-to-find-user-who-ran-drop-or-delete-statements-on-your-sql-server-objects/?utm_source=dailynewsletter&utm_medium=email&utm_content=headline&utm_campaign=20131202 阅读全文

posted @ 2013-12-03 20:40 Alex Tian 阅读(141) 评论(0) 推荐(0)

Inside TSQL Querying - Chapter 3. Query Tuning

摘要：Tuning MethodologyWhen dealing with performance problems, database professionals tend to focus on the technical aspects of the system, such as resource queues, resource utilization, and so on. However, users perceive performance problems simply as waitsthey make a request and have to wait to get the 阅读全文

posted @ 2013-11-27 17:29 Alex Tian 阅读(324) 评论(0) 推荐(0)

Inside TSQL Querying - Chapter 2. Physical Query Processing

摘要：Summary DescriptionThe SQL language is spoken by most database experts, and all relational database products include some dialect of the SQL standard. Nevertheless, each product has its own particular query-processing mechanism. Understanding the way a database engine processes queries helps softwar 阅读全文

posted @ 2013-11-26 15:36 Alex Tian 阅读(189) 评论(0) 推荐(0)

Inside TSQL Querying - Chapter 1. Logical Query Processing

摘要：Logical Query Processing Phases Summary(8) SELECT (9) DISTINCT (11) (1) FROM (3) JOIN (2) ON (4) WHERE (5) GROUP BY (6) WITH {CUBE | ROLLUP}(7) HAVING (10) ORDER BY Brief Description of Logical Query Processing Phases Don't worry too much if the description of the steps doesn't seem ... 阅读全文

posted @ 2013-11-25 17:45 Alex Tian 阅读(310) 评论(0) 推荐(0)

Report launcher to run SSRS report subscriptions on demand

摘要：http://www.mssqltips.com/sqlservertip/3078/report-launcher-to-run-ssrs-report-subscriptions-on-demand/?utm_source=dailynewsletter&utm_medium=email&utm_content=headline&utm_campaign=20131024ProblemSSRS report subscriptions are a great feature for automatic report delivery on regular sched 阅读全文

posted @ 2013-10-25 10:04 Alex Tian 阅读(397) 评论(0) 推荐(0)

如何清除特定语句的执行计划缓存

摘要：SQL server运行到一定的时候, 执行计划的缓存可能会相当大,有些能到几个GB的大小。这个时候假设某个语句比较复杂而且SQL server 生成的执行计划不够优化，你希望把该执行计划的缓存清除使得SQL server能够重新编译该语句。该如何做呢？如果是存储过程则很好办，直接使用sp_recompile就可以了，如下所示。如果参数是表，那么所有用到该表的存储过程或trigger都会重新编译，从而把原来的plan 替换掉：USE AdventureWorks;GOEXECsp_recompileN'Sales.Customer';GO如果是一般的语句呢？比如下面的语句:u 阅读全文

posted @ 2013-06-17 13:29 Alex Tian 阅读(3056) 评论(2) 推荐(0)

你需要明白的SQL SERVER书签查找(Bookmark Lookup)

摘要：你也许会想，假如非聚集索引可以快速的找到所求的数据，但遗憾的是，非聚集索引却不包含所有所求列时该怎么办？这时SQL Server会面临两个选择，直接访问基本表去获取数据或是在非聚集索引中找到数据后，再去基本表获得非聚集索引没有覆盖到的所求列。这个选择取决于所估计的行数等统计信息。查询分析器会选择消耗比较少的那个。一个简单的书签查找如图5所示。图5.一个简单的书签查找从图5可以看出，首先通过非聚集索引找到所求的行，但这个索引并不包含所有的列，因此还要额外去基本表中找到这些列，因此要进行键查找，如果基本表是以堆进行组织的，那么这个键查找(Key Lookup)就会变成RID查找(RID L. 阅读全文

posted @ 2013-03-12 16:08 Alex Tian 阅读(1167) 评论(0) 推荐(0)

你需要明白的SQL SERVER 隔离级别

摘要：隔离等级由低到高分别为 Read Uncommited(最高的性能，但可能出现脏读，不可重复读，幻读) Read commited(可能出现不可重复读，幻读) Repeatable Read(可能出现幻读) Serializable(最低的性能，Range锁会导致并发下降) SNOPSHOT(这个是通过在tempDB中创建一个额外的副本来避免脏读，不可重复读，会给tempDB造成额外负担，因为不是标准ANSI SQL标准，不详细讨论) 阅读全文

posted @ 2013-03-11 13:47 Alex Tian 阅读(334) 评论(0) 推荐(0)

你需要明白的脏读，不可重复读，幻读

摘要：脏读（Dirty Read) 脏读意味着一个事务读取了另一个事务未提交的数据,而这个数据是有可能回滚不可重复读(Unrepeatable Read) 不可重复读意味着，在数据库访问中，一个事务范围内两个相同的查询却返回了不同数据。这是由于查询时系统中其他事务修改的提交而引起的。例如：事务B中对某个查询执行两次，当第一次执行完时，事务A对其数据进行了修改。事务B中再次查询时，数据发生了改变幻读(phantom read)幻读,是指当事务不是独立执行时发生的一种现象，例如第一个事务对一个表中的数据进行了修改，这种修改涉及到表中的全部数据行。同时，第二个事务也修改这个表中的数据，这种修改是向表中插阅读全文

posted @ 2013-03-11 13:43 Alex Tian 阅读(16541) 评论(0) 推荐(1)

你需要明白的索引和约束的前缀（AK,PK,IX,CK,FK,DF,UQ）

摘要：PK - Primary KeyIX - Non-Unique IndexAK - Unique Index (AX should have been AK (Alternate Key))CK - Check ConstraintDF - Default ConstraintFK - Foreign KeyUQ - Unique Constraint 阅读全文

posted @ 2013-02-28 11:54 Alex Tian 阅读(7170) 评论(0) 推荐(2)

你需要明白的数据集之间的运算(UNION,INTERSECT,EXCEPT)

摘要：1. UNION A∪B的逻辑,当遇到数据集中重复的行时，紧保留一个2. UNION ALL A∪B的逻辑，但与UNION不同的是，当遇到两个数据集中重复的行时，全部保留3. INTERSECT A∩B的关系,不支持INTERSECT ALL4.EXCEPT 实现A-B的关系,不支持 EXCEPT ALL测试代码：SELECT 1 AS Value1, 1 AS Value2 INTO #T1UNION ALLSELECT 1 AS Value1, 1 AS Value2UNION ALLSELECT 2 AS Value1, 2 AS Value2UNION ALLSELECT 2 ... 阅读全文

posted @ 2013-02-26 10:48 Alex Tian 阅读(219) 评论(0) 推荐(0)

Rebuilding a heap

摘要：In 2005, rebuilding a table that was a heap (no clustered index) wasn't easy. You could copy it to a different table, or you could add a clustered index and then drop it.In 2008, this is a far easier thing to do. They have added to the ALTER TABLE command a method to rebuild the table, which is 阅读全文

posted @ 2013-02-25 17:27 Alex Tian 阅读(163) 评论(0) 推荐(0)

Comparison Operators Modified by ANY, SOME, or ALL

摘要：Comparison operators that introduce a subquery can be modified by the keywords ALL or ANY. SOME is an ISO standard equivalent for ANY.Subqueries introduced with a modified comparison operator return a list of zero or more values and can include a GROUP BY or HAVING clause. These subqueries can be re 阅读全文

posted @ 2013-02-25 11:21 Alex Tian 阅读(198) 评论(0) 推荐(0)

你需要明白的聚合函数{[[ALL | DISTINCT] expression |*}

摘要：1. 在聚合函数中，可以用ALL 或者DISTINCT这样的关键字2. 当Count()作用于某一特定列(Column)，和以“*”作为参数时的区别是当Count(列名)碰到“Null”值时不会将其计算在内阅读全文

posted @ 2013-02-23 13:54 Alex Tian 阅读(404) 评论(0) 推荐(0)

flysun0311

随笔分类 - [2.z] Alex's SQL Server

公告