Thursday

What are the differences between a clustered and a non-clustered index

What are the differences between a clustered and a non-clustered index
06:42 by Anjan Kant

Introduction

Hope you liked my previous article on Understanding The Relationships in SQL Server. In this article, I am going to discuss clustered and non-clustered indexes in SQL. An index is a disk structure related with a table or view that accelerates the extraction of rows from the table or view. It contains keys created from one or more columns in the table or view. These keys are stored in a B-tree structure that allows SQL Server to rapidly and proficiently discover the rows associated with the key values.
differences between a clustered and a non-clustered index

SQL supports two types of indexes:

  • Clustered index
  • Non-clustered index

Clustered index

Clustered indexes sorts and stores data in table or view based on their key values. There is only one clustered index per table and this table with clustered index is known as clustered table.

Non-Clustered index

Non-clustered indexes have a separate structure from data rows. A non-clustered index comprises of non-clustered index key values. Each one of these key value entry has a pointer to the data rows that includes the key value. It is important for columns that have repeated values.

Difference between Clustered and Non-clustered index in SQL

The difference between clustered and non-clustered indexes is one of the most asked questions during interviews.
Given below are some of the differences between clustered and non-clustered indexes:
  1. Clustered index sorts data rows whereas non-clustered doesn't.
  2. One table can have one clustered index, but there is no such condition on non-clustered index.
  3. In many databases, a clustered index is directly made on primary key.
  4. Clustered Index is more faster to non clustered index because non clustered  physically stored in index order. 
  5. Non clustered index is more faster for insert and update rather than clustered index.
  6. Clustered index is stored logical so it don't require extra storage.

Conclusion

That’s all on difference between clustered and non-clustered indexes in SQL. Both indexes are extremely critical in SQL.

Relevant Reading

Wednesday

How to avoid duplicate records in SQL view using Entity Framework

How to avoid duplicate records in SQL view using Entity Framework
09:25 by Anjan Kant

Introduction

Sometimes while using SQL we experience that SQL query returns same or duplicate results. Since we expect and need a single result from the query, it is a problem. Here we will discuss what causes duplicate records and how to avoid them in SQL view using Entity Framework.

You can also know about how to Fix SQL error: 26 - A network-related error occurred in SQL Server by reading my previous article.

Duplicate rows in Entity Framework SQL View 

In Entity Framework primary key is known as entity key. Entity Framework auto set fields as entity key for those not null column and return the row that match those entity key that causes the problem.
The problem is on the application side Entity framework mapping, as object mapping rules in Entity framework are different with database. The Entity framework does the mapping on application memory with accordance to its designed logic when the query results returns from database. Entity Model has a unique way of handling rows with equal primary key values. To fix issue duplicate records in SQL view using Entity Framework, you need to write below code:
 How to avoid duplicate records in SQL view using Entity Framework

You can set AsNoTracking option directly on your view to resolve this issue.

context.myviewname.AsNoTracking().Where(x => x.ColumnName != null);

Conclusion

To avoid this problem the primary key columns should be chosen correctly. If primary key couldn't be chosen correctly due to null-values, we add a column to our view that always have a unique value for each row and make it primary key in entity model and set AsNoTracking. This is how explained  How to avoid duplicate records in SQL view using Entity Framework.

Relevant Reading

Saturday

HTML: Difference Between Id And Name In Html

HTML: Difference Between Id And Name In Html
04:02 by Anjan Kant

INTRODUCTION 

Web development is a very vast topic as it covers a lot of materials, and even sometimes professional web developers cannot remember all of it.
Here we will be discussing the difference between name attribute and ID attribute in HTML.
Difference Between Id And Name In Html

NAME ATTRIBUTE

Name attribute in HTML indicates the name of an <input> element. The Name attribute is used to reference form data after a form is submitted or to reference elements in JavaScript. The name attribute is used for posting or sending data in a form submission in DOM (document object model).

ID ATTRIBUTE

ID attribute indicates a unique ID for HTML element. The ID attribute is mostly used in CSS, and by JavaScript who controls the element with the unique ID. The ID of a form has nothing to do with the data and information contained in it.
Both name attribute and ID attribute appear to have same purpose that of giving an identifier. The distinction is a name attribute exchanges transfers program to server and can be different in relation to the ID. ID is relied upon to be unique within the page.

CONCLUSION

Name and ID vary because the server is expecting the similar name from various forms in the similar document. The ID must be unique, the name most certainly not.

Suggested Reading

Key Relationships: Understanding The Relationships in SQL Server

Key Relationships: Understanding The Relationships in SQL Server
01:29 by Anjan Kant

Introduction
A web application is a client-server software application in which the user interface runs in a web browser.Modern web applications today interact with databases , usually with a language called SQL. SQL (Structure Query Language) is a language designed for interacting with relational database management systems (RDBMS) like SQL Server, MySQL, Oracle, Sq Lite etc. In my earlier article, I explained about how to fix SQL network error 26.

What is THE RELATIONAL MODEL?

The Relational database management systems are based on relational model. The relational model for database management is an approach to managing data using a structure and language consistent with first-order predicate logic. It is a prescription for how to represent and manipulate data. The purpose of the relational model is to provide a declarative method for specifying data and queries: users directly state what information the database contains and what information they want from it, and allow database management software ensure that it describes data structures for storing the data and retrieval procedures for answering queries.
Most relational databases use the SQL data definition and query language; these systems implement what can be regarded as an engineering approximation to the relational model. A table in an SQL database scheme corresponds to a predicate variable; the contents of a table to a relation; key constraints, other constraints, and SQL queries correspond to predicates.
Database designs are closely associated with database relationships, the association among two columns in a single or more tables.In SQL server, these relationships are defined using Primary Key-Foreign Key constraints. A link is created between two tables where the primary key of one table is associated with the foreign key of another table using database relationships.Now you must be wondering what is the key? Well Key is a set of one or more columns whose combined values are unique among all occurrences in a given table. A key is the relational means of specifying uniqueness.

What are the different types of relationships in SQL?

  • One-to-One
  • One-to-Many
  • Many-to-Many

One-to-One Relationships

This is relationship between the primary and foreign keys relationships with primary key relating to foreign key only one record. A One-to-One relationship means that we have two tables that have a relationship, but that relationship only exists in such a way that any given row from Table A can have at most one matching row in Table B.
A real world example of this could be the relationship between a person and a drivers license. A person cannot have more than one driving licence and in reverse a licence cannot be owned by more than one person. This is a one-to-one relationship. Other examples could be: Person-to-mailing address , school-to-principal , person-to-passport,etc. It is called one-to-one because you can have only one record pointing to one other record.
one-to-one relationship
A generic example of a one-to-one relationship.

One-to-Many Relationships

This is the most commonly used type of relationship.This is also relationship between primary & foreign keys relationships but here primary key relating to multiple records.The One-to-Many relationship is defined as a relationship between two tables where a row from one table can have multiple matching rows in another table.
The common example of a one to many relationship is when we're talking about Users and Addresses. A User can have one or more addresses (mailing and billing address).The key here is that any one address can only belong to one user and only one user.
A one-to-many relationship
A one-to-many relationship from the perspective of TABLE B

Many-to-Many Relationships

In a Many-to-Many relationship, a record in Table B can potentially belong to several records in Table A. And vice versa, a record in Table A can potentially belong to several records in Table B. A simple many-to-many relationship includes two dimensions.
The many-to-many database relationship is used when the rows in the first table can map to multiple rows in the second table and those rows in the second table can also map to multiple different rows in the first table.

Common examples of Many-to-Many include:

  • Doctors have many patients; patients have many doctors.
  • Customers have many bank accounts; bank accounts might belong to more than one customer.
  • Students are enrolled in many courses; each course has many students.

many-to-many relationship
A typical example of a many-to-many relationship

Conclusion

There are, without a doubt, any number of ways in which you can relate the tables in these examples , depending on each table's role within a given database. The manner in which we use the examples here is not important; But the technique we use establish the relationship between these tables is. Once we learn these techniques, we can identify and establish relationships between the tables .

Relevant Reading