Q65. Difference between SQL database table and Azure storage table?
There is a fundamental difference between a SQL table and Azure table. While azure tables are capable of storing one type of data in a row and another type of data in an another row, SQL database tables are designed to store the same type of data in each and every row. This is because, relational databases enforce fixed schema and do not allow such flexibility. In Table storage, we refer to a row as an entity.
This is not possible in a direct way as in relational databases.
However, the same results are achieved by storing and processing data in the same partition in a table. For example we can store a customer details and his pending order details stored in the same partition. We can perform a query for a particular customer by giving the partition key. The query is performed on a particular partition (based on the partition key) and customer details are retrieved.
Remember, Azure tables do not enforce a definite number of columns.
So, while entities do not have fixed number of columns in a table, a particular entity contains a certain number of columns while the next entity may contain a different number of columns.
The process of creating a Table and saving data in Windows Azure begins with designing a class, instantiating the objects and saving these objects as entities. This is not so in case of a relational database where the process starts with creating a database, creating tables and setting relations between them, and writing stored procedures for CRUD operations.
In an Azure Table, a unique row is identified by the combination pair of Partition key and Row key. In a relational database, a primary key is used to identify a unique row.
In a relational database, we can create number of secondary indexes whereas in Azure tables we can create only one index based on the PartitionKey and RowKey properties.
The question arises how do we implement relationships and constraints and also run stored procedures in Azure tables?
This is not possible in a direct way as in relational databases.
However, the same results are achieved by storing and processing data in the same partition in a table. For example we can store a customer details and his pending order details stored in the same partition. We can perform a query for a particular customer by giving the partition key. The query is performed on a particular partition (based on the partition key) and customer details are retrieved.
Remember, Azure tables do not enforce a definite number of columns.
So, while entities do not have fixed number of columns in a table, a particular entity contains a certain number of columns while the next entity may contain a different number of columns.
The process of creating a Table and saving data in Windows Azure begins with designing a class, instantiating the objects and saving these objects as entities. This is not so in case of a relational database where the process starts with creating a database, creating tables and setting relations between them, and writing stored procedures for CRUD operations.
In an Azure Table, a unique row is identified by the combination pair of Partition key and Row key. In a relational database, a primary key is used to identify a unique row.
In a relational database, we can create number of secondary indexes whereas in Azure tables we can create only one index based on the PartitionKey and RowKey properties.
