This article is written under ‘Quick and Short’ edition. In this article I will explain about database indexes, type of indexes, best practices to use index, how to use indexes to improve performance of T-SQL (database query).
Read more Quick and Short articles.
What is index
Indexes in databases are very similar to indexes in libraries. Indexes allow locating information within a database fast, much like they do in libraries. If all books in a library are indexed alphabetically then you don’t need to browse the whole library to find particular book. Instead you’ll simply get the first letter from the book title and you’ll find this letter’s section in the library starting your search from there, which will narrow down your search significantly.
An Index can be created on a single column or a combination of columns in a database table. A table index is a database structure that arranges the values of one or more columns in a database table in specific order. The table index has pointers to the values stored in specified column or combination of columns of the table. These pointers are ordered depending on the sort order specified in the index. Read more »
VN:F [1.7.2_963]
Rating: 5.0/5 (1 vote cast)
This article is written under ‘Quick and Short’ edition. In this article I will explain about Joins used within T-SQL statement.
Read more Quick and Short articles.
The JOIN keyword is used in an SQL statement to query data from two or more tables, based on a relationship between certain columns in these tables.
Tables in a database are often related to each other with keys.
A primary key is a column (or a combination of columns) with a unique value for each row. Each primary key value must be unique within the table. The purpose is to bind data together, across tables, without repeating all of the data in every table.
Different SQL Joins:
- Inner Join
- LEFT OUTER JOIN or LEFT JOIN
- RIGHT OUTER JOIN or RIGHT JOIN
- FULL OUTER JOIN or FULL JOIN
- Cross Join
- UNION
- UNION ALL
Read more »
VN:F [1.7.2_963]
Rating: 4.0/5 (1 vote cast)
This is my very first article under ‘Quick and Short’ edition. In this article, I will introduce the concept of normalization and take a brief look at the most common normal forms.
Read more Quick and Short articles.
What is Normalization?
Normalization is the process of efficiently organizing data in a database. There are two goals of the normalization process: 1) eliminating redundant data (for example, storing the same data in more than one) and 2) ensuring data dependencies make sense (only storing related data in a table). Both of these are worthy goals as they reduce the amount of space a database consumes and ensure that data is logically stored. Read more »
VN:F [1.7.2_963]
Rating: 5.0/5 (1 vote cast)
The power of a relational database isn’t in single tables — it’s in combining tables to obtain new data sets. The real power of the relational design is only realized when you bring multiple tables together to show the data they contain as a group.
You define “key” fields that uniquely identify a particular row in a table, and those fields are the ones that you’ll use most often to “join” the tables back together. A relational database’s design is created with that join in mind from the start. In fact, the keyword used to bring tables together is JOIN — but I’m getting ahead of myself. Read more »
VN:F [1.7.2_963]
Rating: 4.0/5 (1 vote cast)
In this article, I’ll explore data types. I won’t cover them all, because you won’t use all of them (at first, anyway) and because they change based on which version and even sometimes which edition you’re using. But I will cover the major types that you’ll see used most often.
Data types are the kinds of data you can store in a column of a SQL Server table. When you create a table with the CREATE TABLE command, you specify the columns and the type of data they can contain. Often, the data type will require more information in addition to what it is, such as how long or what precision the data will store. Here’s a sample statement: Read more »
VN:F [1.7.2_963]
Rating: 4.0/5 (1 vote cast)
It’s fairly simple to describe a database constraint – it’s just like the English use of the word, to prevent something. In the case of a SQL Server database, constraints are rules you create to specify how data is to be stored in your database.
Whether you design databases or program against them, database integrity is absolutely essential. It’s not just about making sure a certain table has data in it; it’s about ensuring that the data throughout the system doesn’t cause a program to crash or a result to be ambiguous.
Constraints enable SQL Server to implement this integrity. There are several classes of constraints, which we’ll see in a moment. By layering them intelligently, you maintain the integrity of the database as a whole.
Most constraints are column-based, meaning that they are declared and enforced on a single column at a time. One, in particular, is declared and enforced on a table – we’ll see that one in a moment. Read more »
VN:F [1.7.2_963]
Rating: 4.0/5 (1 vote cast)
This section provides you with information and useful links to avail various Citizen Services being provided by the Central & State/UT Governments in India . The list, however, is not exhaustive, as we are committed to adding more and more information about other services for which citizens and other stakeholders need to interact with the Government. Keep visiting this section for new updates !! Read more »
VN:F [1.7.2_963]
Rating: 3.0/5 (1 vote cast)
Represent an operation to be performed on the elements of an object structure. Visitor lets you define a new operation without changing the classes of the elements on which it operates.
Frequency of use: low
This structural code demonstrates the Visitor pattern in which an object traverses an object structure and performs the same operation on each node in this structure. Different visitor objects define different operations. Read more »
VN:F [1.7.2_963]
Rating: 2.0/5 (1 vote cast)
Define the skeleton of an algorithm in an operation, deferring some steps to subclasses. Template Method lets subclasses redefine certain steps of an algorithm without changing the algorithm’s structure.
Frequency of use: medium
This structural code demonstrates the Template method which provides a skeleton calling sequence of methods. One or more steps can be deferred to subclasses which implement these steps without changing the overall calling sequence. Read more »
VN:F [1.7.2_963]
Rating: 2.0/5 (1 vote cast)
Define a family of algorithms, encapsulate each one, and make them interchangeable. Strategy lets the algorithm vary independently from clients that use it.
Frequency of use: medium high
This structural code demonstrates the Strategy pattern which encapsulates functionality in the form of an object. This allows clients to dynamically change algorithmic strategies. Read more »
VN:F [1.7.2_963]
Rating: 0.0/5 (0 votes cast)
Allow an object to alter its behavior when its internal state changes. The object will appear to change its class.
Frequency of use: medium
This structural code demonstrates the State pattern which allows an object to behave differently depending on its internal state. The difference in behavior is delegated to objects that represent this state. Read more »
VN:F [1.7.2_963]
Rating: 0.0/5 (0 votes cast)
Define a one-to-many dependency between objects so that when one object changes state, all its dependents are notified and updated automatically.
Frequency of use: high
This structural code demonstrates the Observer pattern in which registered objects are notified of and updated with a state change. Read more »
VN:F [1.7.2_963]
Rating: 0.0/5 (0 votes cast)
Without violating encapsulation, capture and externalize an object’s internal state so that the object can be restored to this state later.
Frequency of use: low
This structural code demonstrates the Memento pattern which temporary saves and restores another object’s internal state. Read more »
VN:F [1.7.2_963]
Rating: 0.0/5 (0 votes cast)
Define an object that encapsulates how a set of objects interact. Mediator promotes loose coupling by keeping objects from referring to each other explicitly, and it lets you vary their interaction independently.
Frequency of use: medium low
This structural code demonstrates the Mediator pattern facilitating loosely coupled communication between different objects and object types. The mediator is a central hub through which all interaction must take place. Read more »
VN:F [1.7.2_963]
Rating: 0.0/5 (0 votes cast)
Provide a way to access the elements of an aggregate object sequentially without exposing its underlying representation.
Frequency of use: high
This structural code demonstrates the Iterator pattern which provides for a way to traverse (iterate) over a collection of items without detailing the underlying structure of the collection. Read more »
VN:F [1.7.2_963]
Rating: 0.0/5 (0 votes cast)