An Idea can change your life.....

Saturday, October 31, 2009

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.


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.


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.

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.


Friday, October 30, 2009


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


  • 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


/*Show me the count of the records before truncate*/



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



/*Rollback now*/


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



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

  • Can be rollbacked


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


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

Table referenced by a foreign key constraint


  • 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 statement without a where clause works.

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

DELETE is DML Command


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.

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

Thursday, October 29, 2009

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

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

  • primary key creates a clustered index on the column
  • unique creates a nonclustered index by default
  •  primary key doesn't allow NULLs
  •  but unique key allows one NULL only.
  • 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.

Rule2.gif (8304 bytes)

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.

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.

(PrimaryKey int PRIMARY KEY,
CharCol varchar(10) COLLATE French_CI_AS NOT NULL
varchar(10)COLLATE Latin1_General_CI_AS NOT NULL

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


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.

Wednesday, October 28, 2009


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


· 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


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 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

INDEX index_name

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


[ON filegroup ]


CREATE TABLE dummytable1 (

  empid   INT,

  empname VARCHAR(700))

INSERT INTO dummytable1

VALUES     (4,



INSERT INTO dummytable1

VALUES     (6,



INSERT INTO dummytable1

VALUES     (1,



INSERT INTO dummytable1

VALUES     (3,



SELECT empid

FROM   dummytable1


CREATE CLUSTERED INDEX ix_dummytable ON dbo.dummytable1 (


SELECT empid

FROM   dummytable1


CREATE NONCLUSTERED INDEX ix_dummytable1 ON dbo.dummytable1 (




FROM   dummytable1

WHERE  empname LIKE '%a%'

       AND empid = 1

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.

Friday, October 02, 2009

WordAhead Vocabulary Builder


Trace Indian Mobile Location & Operator


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

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