February 23th Links:SQL SERVER

.NET Framework

      1. What is .NET Framework and CLR,CTS and CLS?

  1) .NET Framework:

.net framework = CLR + manage libraries + tools;

2) CLR(common language runtime)

  CLR takes care of code management upon program execution and provides various services such as memory management,thread management,security management and other system services.

    3) CTS(common Type system)

CTS describes how types are declared,used and managed.

4) CLS(common language specification)公共语言规范

The CLS is specification that defines the rules to support 

Language integration.That is done in such a way that

program written by any language(.net compliant兼容)

can interoperate with one another

SQL SERVER

1. A Visual Explanation of SQL SERVER - Coding Horror

1) inner join : produces the set of records that match in both Table A and Table B

2) Full outer join: produces the set of reacords that match all both Table A and Table B,if

    there's no match,the missing side will contain null.

3) Left outer join: produces a complete set of records from Table,with the matching records(where available) in Table B.If there's no match,the right side will contain null.

4) Left outer join + where B.Id is null:

     To produce the set of records only in Table A,but not in Table B.

5) Full outer join + where B.Id is null and A.Id is null.

    To produce the set of records unique to Table A and Table B.

6) Cross join:  a x b:

    To produce the set of records that per record from Table B has all of Table A                        records. 

2.  Clustered Index,Non-clustered Index,Column Index(include Sql2005)

1)  Clustered Index:

only 1 allowed per table.

physically rearranges the data in the table to conform to the index constraints

For use on columns that are frequently searched for ranges of data

For use on columns with low selectivity

2) Non-Clustered Index

up to 249 allowed per table

create a sprated list of key values with pointers to the location of the datta in the data pages

For use on columns with high selectivity

3) Included Column Index(New in Sql Server2005)

 Why does SQL2005 create new index?

There are limitations to creating indexes that make covering queries defficult:

a. A 900-byte size limit on indexes

b. A maximun of 16 columns can be named in the index

c. Columns wite data types like narchar(max),text,and ntext can't by used in    indexes

So a new type of index was developed in SQL2005 that assists in covering queries:indexes with included columns.

Related Post:

Improve Performance with SQL 2005 Covering Index Enhancements

Enabling Clustered Indexes and NonClustered Indexes

Index with Included Columns - MSDN

How to Create Included Columns - MSDN

3. How to Query to verify Constered Indexes using T-SQL

    The sql statement:

 

  USE AdventureWorks - Replace AdventureWorks with your DBName

GO

SELECT DISTINCT[TABLE] = OBJECT_NAME(OBJECT_ID)

FROM SYS.INEXES

WHERE INDEX_ID = 0

AND OBJECTPROPERTY(OBJECT_ID,'IsUserTable') = 1

 

4. SQL SERVER recovery Models

    SQL SERVER provides three different recovery models that allow u to specify the way SQL Sever manages log files and perpare your enterprise for a disaster。Each of these models represents a different approach to balancing the tradeofff between conserving disk space and providing for granular disaster recovery options.

    1) simple recovery model

    2) full recovery model

    3) bulk-logged recovery model

5. Enable and Disable Index Non Clustered Index Using T-SQL

 Englis Words

1. venn diagrams:(维恩图，用图表示集与集之间的关系)

2. populate:填充，构建

 3. IRC : internet Relay chat

 4. specification:规范

5. compliant：兼容，顺从的

 6. disaster:灾难

 7. bulk:大批，体积