Multithreaded program



Multithreaded program programming, what is multithreaded programming, real world example or live code for multithreaded program in C # sharp, multithreading in c java tutorial  ....
Multithreaded Program
Multithreaded Program
real world example or live code for multithreaded program in C # sharp
real world example or live code for multithreaded program in C # sharp

Web Service Standards


what is soa architecture, web services standards, application of web services, java web services, service oriented architecture, security and real world and live example of web services ....

web services standards
web services standards
example of web services
example of web services

  ...

Web Services

Use of web services, when web services should use or not use, real world example or live code of web services, Web Service messages and client, Web Services are individual units of software programming   ....
web services
web services

De-normalization



 Explained the De-normalization and its complete definition, real world scenarios or live code example on demoralization, what is demoralization in sql database

Explained the De-normalization and its complete definition! ...


De-normalization function is a technique to move from higher to lower normal forms of database modeling in order to speed up database access.
De-normalization method is an approach to speed up (optimizing) read performance (data retrieval) in which the administrator selectively adds back specific instances of redundant data after the data structure has been normalized.
De-normalization functionality is the process of introducing redundancy into a table by incorporating data from a related table. Tables are usually de-normalized to prevent expensive SQL join operations between them. One should always normalize to Third Normal Form (3NF) and only apply de-normalization selectively as a last resort if performance problems are experienced.

Keep in Mind : De-normalizations are not free and introduces the following problem into the design:
  • More disk space is used as the same data is duplicated in more than one table
  • DML operations are more expensive as the same data must be maintained in more than one table
  • Risk of  "out of sync" data increases 
 

Use Of Indexes in SQL Server


Use of indexes in database SQL Server, real world example or live sql code for indexes sql server, how index can create, update and deleted with rename

Use of indexes in database SQL Server

One of the physical storage structures provided by most SQL-based DBMS is an index, which is a structure that provides rapid access to the rows of a table based on the values of one or more columns.
Indexes speed up the querying process by providing swift access to rows in the data tables, similarly to the way a book’s index helps you find information quickly within that book.
Indexes are created on columns in tables or views.

Data types which are not supported to indexes

We can create indexes on most columns in a table or a view. The exceptions are primarily those columns configured with large object (LOB) data types, such as image, text, and varchar (max).

Explained the Index Structures

Some DBMS products support two or more different types of indexes, which are optimized for different types of database access:

What is B-tree index – It uses a tree structure of index entries and index blocks (groups of index entries) to organize the data values that it contains into ascending or descending order. This structure is hierarchical in nature, with the root node at the top of the hierarchy and the leaf nodes at the bottom. This type of index, which is the default type in nearly all DBMS products, provides efficient searching for a single value or for a range of values, such as the search required for an inequality comparison operator or a range test (BETWEEN) operation.

Hash index explained – It uses a randomizing technique to place all of the possible data values into a moderate number of buckets within the index. Since a given data value is always placed into the same bucket, the DBMS can search for that value simply by locating the appropriate bucket and searching within it. But the assignment of values to buckets does not preserve the order of data values, so a hash index cannot be used for inequality or range searches.

Definition of T-tree index – It is a variation of the B-tree index that is optimized for in-memory databases.

Bitmap index Theory– It is useful when there are a relatively small number of possible data values.

How on can create Index-organized table – It is relatively a new option that stores the entire table in the index. This is useful for tables that have few columns other than the primary key, such as code lookup tables that typically have only a code (such as a department code) and a description (such as a department name).

Explained all types of Index

i. First one is Implicit Index: They are created when a column is explicitly defined with PRIMARY KEY, UNIQUE KEY constraint.

ii. Second one is Explicit Index: They are created using the "CREATE INDEX …" syntax.

iii. Third one is Clustered Index: A clustered index stores the actual data rows at the leaf level of the index. It is like a telephone directory, where you find the entry you are looking for.
An important characteristic of the clustered index is that the indexed values are sorted in either ascending or descending order. As a result, there can be only one clustered index on a table or view because the set of rows can be maintained in only one order at a time.
In addition, data in a table is sorted only if a clustered index has been defined on a table.
Clustered Index is also called Physical Index.

Keep in Mind: A table that has a clustered index is referred to as a clustered table. A table that has no clustered index is referred to as a heap.

Explained the Non-Clustered index: The leaf nodes of a non-clustered index contain only the values from the indexed columns and row locators that point to the actual data rows. This means that the query engine must take an additional step in order to locate the actual data.
A non-clustered index is like the index in the back of a book. You can quickly search the index for the topic you want, and then you get a reference to a page that you must look up to find the rest of the information.
A row locator’s structure depends on whether it points to a clustered table or to a heap. If referencing a clustered table, the row locator points to the clustered index, using the value from the clustered index to navigate to the correct data row. If referencing a heap, the row locator points to the actual data row.
Non-Clustered indexes cannot be sorted like clustered indexes; however, you can create more than one non-clustered index per table or view.
Non-Clustered Index is also called Logical Index.

Keep in Mind: SQL Server 2005 supports up to 249 non-clustered indexes, and SQL Server 2008 support up to 999.

In addition to an index being clustered or non-clustered, it can be configured in other ways:

  • What is Composite index: An index that contains more than one column. In both SQL Server 2005 and 2008, you can include up to 16 columns in an index, as long as the index doesn’t exceed the 900-byte limit. Both clustered and non-clustered indexes can be composite indexes.
  • Unique Index definitions: Indexes on primary keys are a special type called a unique index, in which each value can appear only once. This is how the database system ensures that primary key values are never duplicated in the tables. If the index is composite, the uniqueness is enforced across the columns as a whole, not on the individual columns. A unique index is automatically created when you define a primary key or unique constraint:
      What is Primary Key: When you define a primary key constraint on one or more columns, SQL Server automatically creates a unique, clustered index if a clustered index does not already exist on the table or view. However, you can override the default behavior and define a unique, non-clustered index on the primary key.

The index on the primary key can be clustered or non-clustered. In SQL Server, it defaults to being a clustered index. The SQL Server syntax for manually creating a clustered index on the primary key fields is shown below:

CREATE CLUSTERED INDEX index_f_name ON table_t_name (column_c_name1, column_c_name2, …);

        Explain the Unique Key: When you define a unique constraint, SQL Server automatically creates a unique, non-clustered index. You can specify that a unique clustered index be created if a clustered index does not already exist on the table. 
     CREATE UNIQUE CLUSTERED INDEX index_name
            ON table_name (column_name1, column_name2, …);
Whate is Covering index: This is a type of index that includes all the columns that are needed to process a particular query. For example, your query might retrieve the FirstName and LastName columns from a table, based on a value in the ContactID column. You can create a covering index that includes all three columns.

Simple Syntax for CREATE INDEX:

CREATE INDEX index_n_name
ON table_t_name;

Following is the example for Single-Column Indexes:

CREATE
INDEX index_n_name
ON table_t_name (column_c_name1);

Example on Composite Indexes:

CREATE INDEX index_i_name;
ON table_t_name (column12, column21…);

Nice Example on Unique Indexes:

CREATE
UNIQUE INDEX index_i_name 
 
ON table_t_name (column_c_name1, column_c_name2...);

What is DROP INDEX Command:

An index can be dropped using SQL DROP command. Care should be taken when dropping an index because performance may be slowed or improved. Some indexes are not so easy to drop, namely any index supporting a unique or primary key constraint.

DROP INDEX index_n_name;

Explain the View Existing Indexes:

EXEC sp_helpindex table_t_name;

How to Rename an Index:

EXEC sp_t_rename 'table_t_name.index_New_name', 'index_New_name';

How and when indexes can be used / avoided? 


Although indexes are intended to enhance a database's performance, there are times when they should be avoided. The following guidelines indicate when the use of an index should be reconsidered:
·                                 Indexes should not be used on small tables or tables that have frequent, large batch update or insert operations.
·                                 Indexes should not be used on columns that contain a high number of NULL values. 
·                                 Columns that are frequently manipulated should not be indexed.
·                                 If you have a lot of indexes, then every time you add, delete, or amend a row in a table (DML operations like INSERT, UPDATE, DELETE), all the indexes must be updated which takes time. So while indexes can speed up retrieval, they may slow some maintenance operations. Indexes also take up room on your storage device.
·                                 Indexes should be used only on columns which are used to search the table frequently.
·                                 Try to insert or modify as many rows as possible in a single statement, rather than using multiple queries.
·                                 Create non-clustered indexes on columns used frequently in your statement’s predicates and join conditions.
·                                 Consider indexing columns used in exact-match queries  .

Normalization


 What is, definition of Normalization, Benefits of normalization, real world scenario or live project work on normalization, purpose of normalization is to minimize redundancy and dependency  .

Define Normalization?

Normalization is the process of designing database tables in a way that makes for efficient use of disk space and that allows the efficient manipulation and updation of data.
The main purpose of normalization is to minimize redundancy and dependency, which involves dividing large tables into smaller tables and defining relationships between them. 
Normalization isolates data, so that additions, deletions, and modifications of a field can be made in just one table and then propagated through the rest of the database via the defined relationships.

Multiple benefits of Normalization:
  • Elimination of data redundancy and dependency in database
  • Improved performance
  • Query optimization 
  • Efficient manipulation and updation of data (faster updates due to less number of columns in one table)
  • Index improvements 

Normal Forms



Explanation on Various Normal Forms, description on first, second, third, fifth and fourth normal forms, domain key normal form, real world example or live project scenarios on normal forms.

Various Normal Forms with Explained Description

(1NF) First Normal Form: Eliminate Repeating Groups- A relation R is said to be in First Normal Form (1NF) if and only if all the attributes of the relation R are atomic in nature. A table (relation) is in 1NF if
  • There are no duplicate rows in the table
  • Each cell is single-valued (i.e., there are no repeating groups or arrays)
  • Entries in a column (attribute, field) are of the same kind 
Keep in Mind: Make a separate table for each set of related attributes, and give each table a primary key. Each field contains at most one value from its attribute domain.

(2NF) Second Normal Form: Redundant Data Eliminated- A relation R is said to be in Second Normal Form (2NF) if and only if
  • It is in the First Normal Form (1NF), and 
  • No partial dependency exists between non-key attributes and key attributes
Keep in Mind: If an attribute depends on only a part of the multi-valued key, remove it to a separate table.

(3NF) Third Normal Form: Eliminated Columns are Not Dependant On Key- A relation R is said to be in Third Normal Form (3NF) if and only if
  • It is in Second Normal Form (2NF) 
  • No transitive dependency exists between non-key attributes and key attributes 
Keep in Mind: If attributes do not contribute to a description of the key, remove them to a separate table. All attributes must be directly dependent on the primary key.

(BCNF) Boyce-Codd Normal Form- A table is in BCNF if it is in 3NF and if every determinant is a candidate key. A 3NF relation is almost always in BCNF. However, the following conditions define some situations when a 3NF relation may not be in BCNF:
  • The candidate keys are composite 
  • There are more than one candidate keys in the relation 
  • There are some common attributes in the relation 
Keep in Mind: If there are non-trivial dependencies between candidate key attributes, separate them out into distinct tables. 

(4NF) Fourth Normal Form: Isolate Independent Multiple Relationships- A table is in 4NF if it is in BCNF and if it has no multi-valued dependencies. 

Note: No table may contain two or more 1:n or n:m relationships that are not directly related. 

(5NF) Fifth Normal Form: Isolate Semantically Related Multiple Relationships- A table is in 5NF, also called "Projection-Join Normal Form" (PJNF), if it is in 4NF and if every join dependency in the table is a consequence of the candidate keys of the table. 

Keep in Mind: There may be practical constrains on information that justify separating logically related many-to-many relationships. 

(DKNF) Domain-Key Normal Form: A table is in DKNF if every constraint on the table is a logical consequence of the definition of keys and domains. It is a model which is free from all modification anomalies.