Integration Services Videos (SQL Server 2008)

Use Integration Services videos to accomplish the following goals:

* Learn about the product and component technologies.

* See how to do various tasks.

* Discover related help topics that help you expand your knowledge of Integration Services.

http://technet.microsoft.com/en-us/library/dd299421.aspx

SQL Server How Do I Videos How-To Demos

Videos Link

http://technet.microsoft.com/hi-in/sqlserver/dd353197%28en-us%29.aspx

How to create a computed column using SQL Server Management Studio?

How to create a computed column using SQL Server Management Studio?

Take the following steps to create a computed column using SQL Server Management Studio:

1. Run SQL Server Management Studio from Start > Programs > Microsoft SQL Server 2005 > SQL Server Management Studio.
   
   
   
2. In the Connect to Server window, click the Connect button.
   
   
   
3. In the Microsoft SQL Server Management Studio window, double-click the Databases folder.
   
   
   
4. In the Microsoft SQL Server Management Studio window, double-click the required database. Here, the Axle database is being used.
   
   
   
5. In the Microsoft SQL Server Management Studio window, double-click the Tables folder.
   
   
   
6. In the Microsoft SQL Server Management Studio window, right-click the required table and click Modify.
   
   
   
7. Type the name of the new column. Here, Amount is the new column.
   
   
   
8. In Column Properties, expand Computed Column Specification. In the Formula box, type the formula. Here, the Price column is multiplied by the Quantity column.
   
   
   
9. Go to the File menu and click Save Products to save the modifications in the Products table.
   
   
   

What are the conditions under which a function can be schema bound?

What are the conditions under which a function can be schema bound?

A function can be schema bound only if the following conditions are true:

• The function is a Transact-SQL function.
• The user-defined functions and the views it references are also schema-bound.
• The objects that the function references are referenced using a two-part name.
• The function and the objects it references belong to the same database.
• The user who executed the CREATE FUNCTION statement has REFERENCES permission on the database objects that the function references.
• SCHEMABINDING cannot be specified for functions that reference alias data types or CLR functions.

Waiting for HIKE

Attitude

An old man lived alone in a village.

He wanted to spade his potato garden, but it was very hard work.

His only son, who would have helped him, was in prison.

The old man wrote a letter to his son and mentioned his situation:

Dear Son,

I am feeling pretty bad because it looks like I won't be able to plant my potato garden this year.

I hate to miss doing the garden, because your mother always loved planting time.

I'm just getting too old to be digging up a garden plot.

If you were here, all my troubles would be over.

I know you would dig the plot for me, if you weren't in prison.

Love,

Dad

Shortly the old man received this telegram:

"For Heaven's sake, Dad, don't dig up the garden!! That's where I buried the GUNS!!"

At 4 a.m the next morning, a dozen FBI agents and local police officers showed up and dug up the entire garden without finding any guns.

Confused, the old man wrote another note to his son telling him what happened, and asked him what to do next.

His son's reply was:

"Go ahead and plant your potatoes, Dad.. It's the best I could do for you from here."

Moral:

"NO MATTER WHERE YOU ARE IN THE WORLD,

IF YOU HAVE DECIDED TO DO SOMETHING DEEP FROM YOUR HEART YOU CAN DO IT. IT IS THE THOUGHT THAT MATTERS.. NOT WHERE YOU ARE OR WHERE THE PERSON IS"

TwoOppositeSuicides

Install multiple versions of IE on your PC

Install multiple versions of IE on your PC

Ever wanted to test your website in various versions of Internet Explorer?

Every time you install a newer version of Internet Explorer, Windows has a tendency to overwrite previous versions. And generally, that's a good thing. You're missing out on an awful lot if you're using Internet Explorer 3 to browse the web these days. AJAX, Flash, and other newfangled features just aren't going to work as well, or at all.

But if you're a developer and you want to see how your website looks to someone running an ancient browser, you might want to install an old copy of Internet Explorer alongside your IE7. And it turns out that this is completely doable if a little complicated. You need to alter some settings, redirect some DLLs, and rename a few things here and there.

If you have a PC running Windows XP (it doesn't work for Vista) and you want to test your application in multiple versions of Internet Explorer, you need to install this software.

Install

Source

sql server Xquery Samples

./author

All elements within the current context. Note that this is equivalent to the expression in the next row.

author

All elements within the current context.

first.name

All elements within the current context.

etc....

check below

XPath Examples expressions

XML structure

Key terminology


The material in this section is based on the XML Specification. This is not an exhaustive list of all the constructs which appear in XML; it provides an introduction to the key constructs most often encountered in day-to-day use.

 (Unicode) Character

By definition, an XML document is a string of characters. Almost every legal Unicode character may appear in an XML document.

Processor and Application

Software which processes an XML document. It is expected that a processor works in the service of an application. There are certain very specific requirements about what an XML processor must do and not do, but none as to the behavior of the application. The processor (as the specification calls it) is often referred to colloquially as an XML parser.

Markup and Content

The characters which make up an XML document are divided into markup and content. Markup and content may be distinguished by the application of simple syntactic rules. All strings which constitute markup either begin with the character "<" and end with a ">", or begin with the character "&" and end with a ";". Strings of characters which are not markup are content.

 Tag

A markup construct that begins with "<" and ends with ">". Tags come in three flavors: start-tags, for example

, end-tags, for example

, and empty-element tags, for example .

Element

A logical component of a document which either begins with a start-tag and ends with a matching end-tag, or consists only of an empty-element tag. The characters between the start- and end-tags, if any, are the element's content, and may contain markup, including other elements, which are called child elements. An example of an element is Hello, world.. Another is .

Attribute

A markup construct consisting of a name/value pair that exists within a start-tag or empty-element tag. In this example, the name of the attribute is "number" and the value is "3": Connect A to B. This element has two attributes, src and alt: An element must not have two attributes with the same name.

XML Declaration

XML documents may begin by declaring some information about themselves, as in the following example.



Sql Server Sample


Source

Save Web Page as pdf

Use PDF Download to do whatever you like with PDF files on the Web. Regain control and eliminate browser problems, view PDFs directly in Firefox as HTML, and use the all-new Web-to-PDF toolbar to save and share Web pages as high-quality PDF files.

Install Here

Execise Video DataBase

Execise Video DataBase

Learn Correct  exercise technique

Exercise videos with tips and instructions

To get the maximum results out of any exercise you should use the correct technique. The best way to learn good technique is one on one, but since we can't get to every gym around the world we've got the next best thing, exercise instruction videos and pictures!




Each video is a demonstration of how to do the exercise using correct technique. There is also additional information, tips, pictures and links on the video pages to help you get the best results using the exercise. If you need help with any of these exercises just ask in the forum.
 
Source

xquery xpath expressions

XQuery uses XPath  expressions to locate nodes in a document and to navigate from one location to another within a single document or across documents. Navigation paths defined using XPath consist of a sequence of steps separated by /. A single step comprises an axis, a node test, and zero or more step qualifiers.

The axis specifies the direction of movement, relative to the context node. Supported axes in SQL Server 2005 are child, descendant, parent, attribute, self and descendant-or-self.

child : the children of the context node

descendant : all descendants (children, childrens children, ...)

parent : the parent (empty if at the root)

ancestor : all ancestors from the parent to the root

following-sibling : siblings to the right

preceding-sibling : siblings to the left

following : all following nodes in the document, excluding descendants

preceding : all preceding nodes in the document, excluding ancestors

attribute : the attributes of the context node

namespace : namespace declarations in the context node

self : the context node itself

descendant-or-self :  the union of descendant and self

ancestor-or-self : the union of ancestor and self

Create,View,Edit PDF files Online

Edit PDF files with PDFescape - an online, free PDF reader, free PDF editor & free PDF form filler. View PDF documents on the web

Click Here To Use PDFescape Now Free!

(Psst, it's the only free PDF editor & form filler online!)

Sub Query

The SQL subquery feature lets you use the results of one query as part of another query.

A subquery is a query within a query.



A sub-query is a SQL Server statement embedded inside of another SQL Server statement.

Sub-query execution is dependent upon the nesting level of the query. The execution tree goes from inner-most queries to outer-most queries. The higher nested queries can access the results returned by the lower nested queries.


Paranthesis

• A sub‐query must be enclosed in the parenthesis

comparison operator

• A sub‐query must be put in the right hand of the comparison operator

Order By

• A sub‐query cannot contain an ORDER‐BY clause

Number Of SubQuery's

• A query can contain more than one sub‐query

Types

• Sub Query
• Correlated Sub Query

Results

• Single Row
• Multipe Row
• Multiple Columns

Correlated Sub Query Sample
A correlated sub-query is dependent upon the outer query. The outer query and the sub-query are related typically through a WHERE statement located in the sub-query. The way a correlated sub-query works is when a reference to the outer query is found in the sub-query, the outer query will be executed and the results returned to the sub-query. The sub-query is executed for every row that is selected by the outer query.
____________________________________________________
CREATE TABLE SalesHistory

(
SaleID INT IDENTITY(1,1),
Product VARCHAR(30),
SaleDate SMALLDATETIME,
SalePrice MONEY
)

SELECT SaleDate, Product, SalePrice,
ISNULL(
(
SELECT
SUM(SalePrice)
FROM
SalesHistory sh1
WHERE
Sh1.Product = sh.Product AND
Sh1.SaleID < sh.SaleID
),0) AS RunningTotal
FROM SalesHistory sh
ORDER BY Product, SaleID

____________________________________________________

Index Seek VS Index scan

SQL Server can use different data access strategies when retrieving rows from the table. The strategy that will be used depends on the columns of the table, the available indexes, the query, the data in the table, and the statistics. There are 7 basic data access strategies.

SQL Server: Scans and seeks

The most primitive operation in SQL Server is retrieving from a table a set of rows that satisfies a given search predicate. This can be achieved using two basic strategies: scans and seeks.

Scan

Scans can be performed on any structure (index or heap). Scanning a table means that SQL Server reads all the rows in the table. Only the rows satisfying the search predicate are returned. Scans imply sequential reads that are usually faster than reading each page individually, but the cost of this operation is proportional to the size of the table.

In indexes, SQL Server uses the pointers to the next page (or previous) for scanning. In a heap, it uses the IAM pages.
The number of logical reads during this operation is the number of pages read during the scan, plus one logical read at each non-leaf level, in case of an index, in order to locate the first row of the table. This operation counts as a scan in the IO statistics.

Seek

Seeks can only be performed on indexes (clustered or nonclustered). A seek is performed when SQL Server can leverage the ordering of the rows in the index to identify a range of rows in the index that contains all the rows satisfying the search predicate.

The search predicate is divided in two parts:

• The seek predicate, such as all the rows within this range satisfy the seek predicate, and all the rows outside of that range don't. Seek predicates are usually predicates on the leftmost columns of the key of an index.
• The residual predicate, such as the search predicate is the conjunction of the seek predicate and the residual predicate. The residual predicate is optional.
  

source

Index Scan retrieves all the rows from the table. Index Seek retrieves selective rows from the table.

SEEK is better then SCAN

But there are few scenarios where SCAN operator perform better then SEEK specially when large number of rows are expected to return by operation.

sample

UPDATE STATISTICS

You can add statistics on columns that don't have statistics in order to boost query performance.

UPDATE STATISTICS updates the indexes on these tables accordingly.

difference between DELETE & TRUNCATE commands

Where Condition

• Delete Removes the rows from a table based on the condition that we provide with a WHERE clause.

• Truncate Actually remove all the rows from a table(i.e there is no where condition on this)

Transaction log

Truncate:

• 
  
  Is faster and uses fewer system and transaction log resources than DELETE.
  
  
  
• 
  
  Deallocates the data pages used to store the table’s data, and only the page deallocations are recorded in the transaction log.
  
  
  
• 
  
  directly delete records without entry in transaction log.
  
  
  
• 
  
  It Can't be rollbacked
  

TRUNCATE Can’t be Rolled Back Using Log Files After Transaction Session Is Closed.
But can be rollbacked the following way

____________________________________________________________

 CREATE TABLE dbo.TEST_TRUNCATE (COL1 INT IDENTITY, COL2 NVARCHAR(100));

SET NOCOUNT ON
GO
INSERT INTO dbo.TEST_TRUNCATE (COL2) VALUES ('TEST TRUNCATE');
GO
 BEGIN TRAN
/*Show me the count of the records before truncate*/

SELECT COUNT(*) FROM dbo.TEST_TRUNCATE

TRUNCATE TABLE dbo.TEST_TRUNCATE

/*Show me the count of the records now*/

PRINT 'INSIDE THE TRANSACTION'

SELECT COUNT(*) FROM dbo.TEST_TRUNCATE

/*Rollback now*/

ROLLBACK TRAN

/*Show me the count of the records now*/


PRINT 'OUTSIDE THE TRANSACTION'

SELECT COUNT(*) FROM dbo.TEST_TRUNCATE
______________________________________________________________________________
 
Delete:

• 
  
  DELETE removes rows one at a time and records an entry in the transaction log for each deleted row.
  
  
  
• 
  
  Can be rollbacked
  
  
  

Identity

Truncate

• 
  
   truncate table the counter used by an identity column for new rows is reset to the seed for the column
  
  
  

Delete

• 
  
   If you want to retain the identity counter, use delete statement instead.
  
  
  

Table referenced by a foreign key constraint

Truncate

• 
  
  You cannot use truncate table on a table referenced by a foreign key constraint
  
  
  
• 
  
  Because truncate table is not logged, it cannot activate a trigger.
  
  
  

 Delete

• 
  
  Delete statement without a where clause works.
  
  
  

Truncate table may not be used on tables participating in an indexed view.

TRUNCATE is DDL Command

DELETE is DML Command

Performance

TRUNCATE TABLE is faster and uses fewer system and transaction log resources. 

Delete Table is slower

The DELETE statement is executed using a row lock, each row in the table is locked for deletion.

Locking

TRUNCATE TABLE always locks the table and page

The DELETE statement is executed using a row lock, each row in the table is locked for deletion

What's the difference between a primary key and a unique(Candidate) key

uniqueness

Both primary key and unique key enforces uniqueness of the column on which they are defined.

Indexes

• primary key creates a clustered index on the column
• unique creates a nonclustered index by default

Nulls

•  primary key doesn't allow NULLs
•  but unique key allows one NULL only.

Multiple

• you can have multiple unique keys in a table
• can have only one Primary Key

Both of them can be refered as Foreign Key

A foreign key is a column (the child collumn) in a table which has a corresponding relationship and a dependency on another collumn (the parent collumn) that is usually in a different table.

Rules of Data Normalization - 2NF

2NF was originally defined by E.F. Codd[1] in 1971. A table that is in first normal form (1NF) must meet additional criteria if it is to qualify for second normal form. Specifically: a 1NF table is in 2NF if and only if, given any candidate key and any attribute that is not a constituent of a candidate key, the non-key attribute depends upon the whole of the candidate key rather than just a part of it.

Wikipedia Source

Definition: In order to be in Second Normal Form, a relation must first fulfill the requirements to be in First Normal Form. Additionally, each nonkey attribute in the relation must be functionally dependent upon the primary key. : In order to be in Second Normal Form, a relation must first fulfill the requirements to be in First Normal Form. Additionally, each nonkey attribute in the relation must be functionally dependent upon the primary key.

Example

database design A series of steps followed to obtain a database design that allows for efficient access and storage of data in a relational database. These steps reduce data redundancy and the chances of data becoming inconsistent.

A table in a relational database is said to be in normal form if it satisfies certain constraints. Codd's original work defined three such forms but there are now five generally accepted steps of normalisation.
 
The most obvious indication that a database is not 2NF is if multiple records in a table might have the exact same value for a column.
 
To make a database 2NF compliant:

• Identify any fields that do not relate directly to the primary key.
• Create new tables accordingly
• Assign or create new primary keys
• Create the requisite foreign keys indicating the relationships

Note that when a 1NF table has no composite candidate keys (candidate keys consisting of more than one attribute), the table is automatically in 2NF.

Rules of Data Normalization - 1NF

Make a separate table for each set of related attributes, and give each table a primary key


Codd states that the "values in the domains on which each relation is defined are required to be atomic with respect to the DBMS.

atomic value are one that "cannot be decomposed into smaller pieces by the DBMS (excluding certain special functions)

• There are no duplicate rows.
• Every row-and-column intersection contains exactly one value from the applicable domain (and nothing else).

First Normal Form (1NF) sets the very basic rules for an organized database:


* Eliminate duplicative columns from the same table.

* Create separate tables for each group of related data and identify each row with a unique column (the primary key).

A table is in the first normal form if it contains no repeating columns.

single value restriction. The first involves assigning several values to the same attribute:

This should be redefined as a relation with attributes Emp ID, Child, and Gender — but with only one value per attribute. The resulting table should have four rows.

Similarly, each attribute should be separated from the other attributes. Thus, the following is not allowed (or, at least, not encouraged):

The Parents attribute should be broken up into two attributes, Father and Mother.

Another common (but incorrect) way of representing the employee and child information above is as follows:

The above series of attributes ChX and GenderX is known as a repeating group.

Fix this problem by simply creating a relation with Emp ID, Child, and Gender (and four rows).

Derived data are values that can be computed from values in other attributes.

A relation should not contain any derived data. For example, an attribute should not hold the multiplicative product of two other attributes or the grand total of some other attribute.

A table is in 1NF if it adheres to the single value restriction and disallows repeating groups and derived data.

Rules to get to 1NF


1 Break up all attributes so that they are single-valued.

2 Remove all derived data attributes.

3 Define a primary identifier for the relation.

4 Remove all repeating groups. Put them in another relation.

 
wikipedia source on 1nf
 
Problems on 1NF

SQL Server Collation

What is collation?

Collation refers to a set of rules that determine how data is sorted and compared.

Character data is sorted using rules that define the correct character sequence,

with options for specifying

1) case-sensitivity,

2) accent marks,

3) kana character types and

4)character width.

 A collation specifies the bit patterns that represent each character and the rules by which characters are sorted and compared.

Case sensitivity

If A and a, B and b, etc. are treated in the same way then it is case-insensitive. A computer treats A and a differently because it uses ASCII code to differentiate the input. The ASCII value of A is 65, while a is 97. The ASCII value of B is 66 and b is 98.

Accent sensitivity

Sensitive or insensitive to accented characters, also known as extended characters. Accented characters are those characters that have a diacritical mark, such as the German umlaut (ë) or the Spanish tilde (~). For example, accent sensitivity defines whether 'a' is equal or not equal to 'ä'.

If a and á, o and ó are treated in the same way, then it is accent-insensitive. A computer treats a and á differently because it uses ASCII code for differentiating the input. The ASCII value of a is 97 and áis 225. The ASCII value of o is 111 and ó is 243.

Kana Sensitivity

When Japanese kana characters Hiragana and Katakana are treated differently, it is called Kana sensitive.

Width sensitivity

When a single-byte character (half-width) and the same character when represented as a double-byte character (full-width) are treated differently then it is width sensitive.

SQL Server supports two categories of character data types:

* The Unicode data types nchar, nvarchar, and ntext. These data types use the Unicode character representation. Code pages do not apply to these data types.

* The non-Unicode character data types char, varchar, and text. These data types use the character representation scheme defined in a single or double-byte code page.

The easiest way to manage character data in international databases is to always use the Unicode nchar, nvarchar, and ntext data types in place of their non-Unicode equivalents (char, varchar, and text).

Sort Order

A sort order specifies the rules used by SQL Server to interpret, collate, compare, and present character data. For example, a sort order defines whether 'a' is less than, equal to, or greater than 'b'

When you install an instance of SQL Server, you can specify the default collation for that instance during setup. The default collation for the instance also becomes the default collation of the system databases: master, model, tempdb, msdb, and Distribution.

CREATE TABLE MyTable
(PrimaryKey int PRIMARY KEY,
CharCol varchar(10) COLLATE French_CI_AS NOT NULL
)
GO
ALTER TABLE MyTable ALTER COLUMN CharCol
varchar(10)COLLATE Latin1_General_CI_AS NOT NULL
GO

After a collation has been assigned to any object other than a column or database, you cannot change the collation except by dropping and re-creating the object

sample

CDC - Change Data Capture SQL Server 2008

CDC or Change data capture is a new feature in SQL Server 2008, which is an ability to record changes to table data into another table without writing triggers or some other mechanism, Change data capture records the changes like insert, update, and delete to a table in SQL server thus making the details of the changes available in relational format.

Video

Serialization

Manual Serialization 200% + Faster than BinaryFormatter

· Serialization Facts

o Binary serialization can handle graphs with multiple references to the same object;

o XML serialization will turn each reference into a reference to a unique object.

o Binary serialization the entire object state is saved

o while in XML serialization only some of the object data is saved

o


· Serialization Clone:

o Only for serializable objects


o Should be by serialization as it get serialized in web service

Advantages of Binary Serialization

All members, no matter if they are read-only will be serialized.
Greater performance*

Advantages of XML Serialization

Greater flexibility of object sharing and usage (interoperability)
No strict binary dependence
Human readable

T-SQL Query Processing Order

According to ANSI standard the query should be written in the following way:



SELECT [...]

FROM [table1]

JOIN [table2]

ON [condition]

WHERE [...]

GROUP BY [...]

HAVING [...]

ORDER BY [...]

What is the difference between varchar and nvarchar

VARCHAR is an abbreviation for variable-length character string. It's a string of text characters that can be as large as the page size for the database table holding the column in question. The size for a table page is 8,196 bytes, and no one row in a table can be more than 8,060 characters. This in turn limits the maximum size of a VARCHAR to 8,000 bytes.

The "N" in NVARCHAR means uNicode. Essentially, NVARCHAR is nothing more than a VARCHAR that supports two-byte characters. The most common use for this sort of thing is to store character data that is a mixture of English and non-English symbols — in my case, English and Japanese.

 

VARCHAR is stored as regular 8-bit data. But NVARCHAR strings are stored in the database as UTF-16 — 16 bits or two bytes per character

 

The difference is that nvarchar is used to store unicode data, which is used to store multilingual data in your database tables.

 

varchar: Variable-length, non-Unicode character data. The database collation determines which code page the data is stored using.

nvarchar: Variable-length Unicode character data. Dependent on the database collation for comparisons.

   

* nchar and nvarchar can store Unicode characters.

* char and varchar cannot store Unicode characters.

* char and nchar are fixed-length which will reserve storage space for number of characters you specify even if you don't use up all that space.

* varchar and nvarchar are variable-length which will only use up spaces for the characters you store. It will not reserve storage like char or nchar.

nchar and nvarchar will take up twice as much storage space, so it may be wise to use them only if you need Unicode support.

 

nchar(10) is a fixed-length Unicode string of length 10. nvarchar(10) is a variable-length Unicode string with a maximum length of 10. Typically, you would use the former if all data values are 10 characters and the latter if the lengths vary.

The main reason for preferring nvarchar over varchar would be internationalization (i.e. storing strings in other languages).

speed up the visual studio disable toolbox controls load

1) Tools Menu
2) Click the Windows Forms Designer Node
3) Disable AutoToolboxPopulate (Make it false)

Close Visual Studio
Restart it

If you include a custom built control in your project, it will not show up automatically on the toolbox

What are the difference between clustered and a non‐clustered index?

SQL Server data structures

Pages

In SQL Server, data is organized in pages. A page has a fixed size (8 KB). Each page contains records. The number of records that can be stored in a page depends on the size of the records. The operation of reading data from a page is called a logical IO. The smaller the size of a record is, the more records can be read with the same number of logical IOs.

A page can be identified by a 6 bytes page pointer: 2 bytes for the file ID and 4 bytes for the page number. A row in a page can be identified by an 8 bytes row ID: 2 bytes for the file ID, 4 bytes for the page number and 2 bytes for the row number.

There are two types of non system pages: data pages, composed of data rows, and index pages, composed of index rows.

Structure of a table: heaps and clustered tables

There are only two types of tables in SQL Server: heaps and clustered tables.

Heaps

Heaps are tables that have no clustered index. They are entirely composed of data pages. The data rows are not stored in any particular order, and there is no particular order to the sequence of the data pages. The data pages are not linked in a linked list.

A heap.

Clustered tables

A clustered table is a table whose rows are kept in sorted order. The clustered index is the structure that stores and maintains the rows of a clustered table in sorted order. The rows are ordered using the clustering key, which is defined by one or more columns of the table. Like in a heap, the data rows are stored in data pages, and the clustered index additionally uses index pages to navigate in the data.

SQL Server organizes indexes as trees, with one page at the root level, multiple pages at the leaf level, and zero or more levels in between.

• 
  
  Leaf level:
  The leaf level is the data of the table. It is composed of data pages. They store all the columns of the table for every row of the table.
  
  
  
• 
  
  Non-leaf levels:
  The non-leaf levels are composed of index pages.
  At every non-leaf level, each index row corresponds to a page in the next level. Each index row in every index page contains two things: the index key value, which is the first key value of the corresponding page in the next level of the index, and a 6 bytes page pointer to that corresponding page.
  
  
  

At each level (including leaf level), the pages have a pointer to the next and the previous page in the same level, so that each level forms a doubly-linked list. At each level, the pages and the rows within a page are ordered using the clustering key.

A clustered index.
Blue pages are index pages and green pages are data pages.

Nonclustered indexes

Nonclustered indexes are always associated with a table (it can be either a heap of a clustered table), but they have a structure completely separate from the data rows. Nonclustered indexes only contain a partial copy of the data of the associated table, sorted in a different order than the actual data. The rows are ordered using the nonclustered key, which is defined by one or more columns of the associated table. There can be multiple nonclustered indexes per table.

Nonclustered indexes have the same B-tree structure as clustered indexes, however the leaf level is different.

• 
  
  Leaf level:
  In a nonclustered index, the leaf level is composed of index pages instead of data pages. Each index row in the leaf level of a nonclustered index contains a nonclustered key value along with a row locator that points to the corresponding data row in the actual table structure (heap or clustered table).
  If the associated table is a heap, the row locator is an 8 bytes row ID. If the table is a clustered table, the row locator is the clustering key identifying the row.
  
  
  
• 
  
  Non-leaf levels:
  The non-leaf levels of a nonclustered index are used for navigation identically as in a clustered index.
  
  
  

At each level of the nonclustered index, the pages and the rows within a page are ordered using the nonclustered key.

A nonclustered index for the clustered table above.

A nonclustered index for the heap above.

Covered columns

The columns stored at the leaf of an index are called the columns covered by this index.

A clustered index always covers all the columns.

A nonclustered index covers all the columns of its non clustering key. If it is a nonclustered index on a clustered table, it also covers the columns of the clustering key of the table, since they constitute the row locators.

Clustered indexes are created by default to match the primary key, which is defined on tables in SQL Server. However, you can create a clustered index on any column and then define a primary key on a separate column or columns. At this point, the primary key would be created as a unique non-clustered index. Typically, a clustered index will match the primary key, but not necessarily, so be careful. Given the variety of situations that can arise, I'll be discussing the clustered indexes themselves, and for now ignore whether you choose to make them primary keys.

 different index configurations a table can have?

• No indexes
• A clustered index 
• A clustered index and many nonclustered indexes
• A nonclustered index
• Many nonclustered indexes

CREATE [UNIQUE] [ CLUSTERED | NONCLUSTERED ]
INDEX index_name

ON table (column [ASC
DESC] [ ,...n ] )

[WITH {IGNORE_DUP_KEY
DROP_EXISTING
SORT_IN_TEMPDB}]

[ON filegroup ]

 

CREATE TABLE dummytable1 (

  empid   INT,

  empname VARCHAR(700))


INSERT INTO dummytable1

VALUES     (4,

            Replicate('d',700))


GO


INSERT INTO dummytable1

VALUES     (6,

            Replicate('f',700))


GO


INSERT INTO dummytable1

VALUES     (1,

            Replicate('a',700))


GO


INSERT INTO dummytable1

VALUES     (3,

            Replicate('c',700))


GO


SELECT empid

FROM   dummytable1


GO


CREATE CLUSTERED INDEX ix_dummytable ON dbo.dummytable1 (

      empid)


SELECT empid

FROM   dummytable1


GO


CREATE NONCLUSTERED INDEX ix_dummytable1 ON dbo.dummytable1 (

      empname)


GO


SELECT *

FROM   dummytable1

WHERE  empname LIKE '%a%'

       AND empid = 1


GO
The Clustered/Non Clustered index can be enabled disabled

primary key creates a clustered index on the column, where as unique key creates a nonclustered index by default.

Fast Maths Trick of Calculation

WordAhead Vocabulary Builder

Trace Indian Mobile Location & Operator

IndianMobile

Searches across Indian mobile number details...
Updated Regularly

This is a good websit. you can know some phone no. Location & Operator

Self-Massage for Athletes

selfmassageforathletes
Massage away muscle pain and soreness
Turn your hands into powerful pain relievers. Are you aching in the places where you used to play? Join the revolution and feel better fast

Watch Vocabular Videos Studying vocabulary or just want to improve your vocabulary? This ‘video dictionary’ is indispensable!

WordAhead Study Room provides a distraction free environment with over 787 Vocabulary Videos and Flash Cards. More vocab videos continuously added. Join us, its FREE!Studying vocabulary or just want to improve your vocabulary? This ‘video dictionary’ is indispensable!
http://www.wordahead.com/WordList/tabid/71/Default.aspx

OpenDns Stay Secured

Make your network safer, faster, smarter, and more reliable.

start
OpenDNS Server Ip Address

208.67.222.222 and 208.67.220.220.

Effective Communication

Dealing with any clients in services is an art.

In the case of IT, we primarily deal with American and UK clients. It is useful to know how English language works with them.

Some of us may hesitate to speak to the client, because we are not confident. ‘Practice makes you confident’

“Practice these tips…….”

PowerPoint

Money Kumar The Monetary Policy

Money Kumar

The Monetary Policy

PDF

Managing Anger PPT

Managing Anger PPT


Thought it might help each of us know how to manage anger.

PowerPoint

Bell the Cat.... Click on the link

Try this out...very funny..............

http://www.members.shaw.ca/gf3/circle-the-cat.html

Incredible!!!New initiative of Govt of India ............ Very Informative - read, save & pass on

Government of India has an online Grievance forum at http://www.pgportal.gov.in

Can you imagine this is happening in INDIA ?

The govt. wants people to use this tool to highlight the problems they faced while dealing with Government officials or departments like (all hyperlinks given below links to respective dept/office site)

1) Railways

2) Posts

3) Telecom (incl. Bharat Sanchar Nigam Limited (BSNL) & Mahanagar Telephone Nigam Limited (MTNL)

4) Urban Development (Delhi Development Authority (DDA), Land & Development Office (L&DO), Central Public Works Department (CPWD), etc)

5) Petroleum & Natural Gas

6) Civil Aviation (Air India, Airports Authority of India, etc)

7) Shipping, Road Transport & Highways

8) Tourism

9) Public Sector Banks Allahabad Bank Andhra Bank Bank of Baroda Bank of India Bank of Maharashtra Canara Bank Central Bank of India Corporation Bank Dena Bank Indian Bank Indian Overseas Bank Industrial Development Bank of India Ltd National Bank for Agriculture and Rural Development Oriental Bank of Commerce Punjab & Sind Bank Punjab National Bank Small Industries Development Bank of India State Bank of Bikaner & Jaipur State Bank of Hyderabad State Bank of India State Bank of Indore State Bank of Mysore State Bank of Patiala State Bank of Travancore Syndicate Bank UCO Bank Union Bank of India United Bank of India Vijaya Bank

10) Public Sector Insurance Companies GIC of India Life Insurance Corporation of India National Insurance Company Ltd. The New India Assurance Company Ltd. The Oriental Insurance Company Ltd. United India Insurance Company Ltd.

11) National Saving Scheme of Ministry of Finance

12) Employees' Provident Fund Organization

13) Regional Passport Authorities Regional Passport Office, Ahemadabad Regional Passport Office, Amritsar Regional Passport Office, Bangalore Regional Passport Office, Bareilly Regional Passport Office, Bhopal Regional Passport Office, Bhubaneswar Regional Passport Office, Chandigarh Regional Passport Office, Chennai Regional Passport Office, Cochin Regional Passport Office, Coimbatore Regional Passport Office, Dehradun Regional Passport Office, Delhi Regional Passport Office, Ghaziabad Regional Passport Office, Goa Regional Passport Office, Guwahati Regional Passport Office, Hyderabad Regional Passport Office, Jaipur Regional Passport Office, Jalandhar Regional Passport Office, Jammu Regional Passport Office, Kolkata Regional Passport Office, Kozhikode Regional Passport Office, Lucknow Regional Passport Office, Madurai Regional Passport Office, Malappuram Regional Passport Office, Mumbai Regional Passport Office, Nagpur Regional Passport Office, Patna Regional Passport Office, Pune Regional Passport Office, Raipur Regional Passport Office, Ranchi Regional Passport Office, Shimla Regional Passport Office, Srinagar Regional Passport Office, Surat Regional Passport Office, Thane Regional Passport Office, Trichy Regional Passport Office, Trivandrum Regional Passport Office, Visakhapatnam

14) Central Government Health Scheme

15) Central Board of Secondary Education

16) Kendriya Vidyalaya Sangathan

17) National Institute of Open Schooling

18) Navodaya Vidyalaya Samiti

19) Central Universities

20) ESI Hospitals and Dispensaries directly controlled by ESI Corporation under Ministry of Labour

Many of us say that these things don't work in India .
Couple of months back, the Faridabad Municipal Corporation laid new roads in the area and the residents were very happy about it. But two weeks later, BSNL dug up the newly laid roads to install new cables which annoyed all the residents. A resident used the above listed grievance forum to highlight his concern. To his surprise, BSNL and Municipal Corporation of Faridabad were served a show cause notice and the guy received a copy of the notice in one week. Government has asked the FMC and BSNL about the goof up as it's clear that both the government departments were not in sync at all.

So use this grievance forum and educate others who don't know about this facility.

This way we can at least raise our concerns instead of just talking about the 'System' in India .

Invite your friends to contribute for many such happenings.

PLEASE SPREAD THIS MESSAGE IF U WANT OUR INDIA TO HAVE A BETTER TOMORROW