What is the concept of Index in database and how many types
An index can be created in a table to find data more quickly and efficiently. The users cannot see the indexes, they are just used to speed up searches/queries.
Disadvantage
Updating a table with indexes takes more time than updating a table without (because the indexes also need an update). So you should only create indexes on columns (and tables) that will be frequently searched against.
Index types
It just like a primary key, but it has no business impact. It’s used to uniquely identify records.
View: In SQL, a view is a virtual table based on the result-set of an SQL statement. A view contains rows and columns, just like a real table. The fields in a view are fields from one or more real tables in the database. View will not be updated if table is updated.
Notice that each SELECT statement within the UNION must have the same number of columns. The columns must also have similar data types. Also, the columns in each SELECT statement must be in the same order.
There are two types of unions, Union, Union All
SELECT column_name(s) FROM table_name1
UNION
SELECT column_name(s) FROM table_name2
SELECT column_name(s) FROM table_name1
UNION ALL
SELECT column_name(s) FROM table_name2
What is Candidate Key?
All unique keys which can be set as primary key are called candidate keys.
Difference between truncate, delete, drop
Delete: Deletes the data from table
Truncate: Deletes the data from table also re set the auto increment values.
Drop: Deletes the data and structure of table.
Difference between load testing, performance testing, stress testing
Performance testing is a general concept.
Performance Testing= Load Testing+ Stress Testing
Load Testing: Application response time for specific no of users.
Stress Testing: Application behavior for a specific time when the break point where application will be breaked.
In SQL Difference between “IN” and “BETWEEN”
Both are used in to fetch data for a specific range. For example if you want to fetch salary of an employee from salary 15000 to 25000. When you use IN then 15000, 25000 will not be fetched. When you use between all data between 15000 and 25000 including these two will be fetched.
Stack and heap
Stack: In line memory, lives Ram memory where value types saved
Heap: collection, values which are not convertible to binary are saved. Pointer to that values is in stack.
Normalization
Unnormalized – There are multivalued attributes or repeating groups
1 NF – No multivalued attributes or repeating groups.
2 NF – 1 NF plus no partial dependencies
3 NF – 2 NF plus no transitive dependencies
What is the difference between an ERD and an ERM?
ERM: In software engineering, an entity-relationship model (ERM) is an abstract and conceptual representation of data. Entity-relationship modeling is a database modeling method, used to produce a type of conceptual schema or semantic data model of a system, often a relational database, and its requirements in a top-down fashion. Diagrams created by this process are called entity-relationship diagrams, ER diagrams, or ERDs.
An index can be created in a table to find data more quickly and efficiently. The users cannot see the indexes, they are just used to speed up searches/queries.
Disadvantage
Updating a table with indexes takes more time than updating a table without (because the indexes also need an update). So you should only create indexes on columns (and tables) that will be frequently searched against.
Index types
| Index type | Description |
| Clustered | A clustered index sorts and stores the data rows of the table or view in order based on the clustered index key. The clustered index is implemented as a B-tree index structure that supports fast retrieval of the rows, based on their clustered index key values. Primary keys are clustered index. |
| Non clustered | A non-clustered index can be defined on a table or view with a clustered index or on a heap. Each index row in the non-clustered index contains the non-clustered key value and a row locator. This locator points to the data row in the clustered index or heap having the key value. The rows in the index are stored in the order of the index key values, but the data rows are not guaranteed to be in any particular order unless a clustered index is created on the table. |
| Unique | A unique index ensures that the index key contains no duplicate values and therefore every row in the table or view is in some way unique. Both clustered and non-clustered indexes can be unique. |
| Index with included columns | A non-clustered index that is extended to include non-key columns in addition to the key columns. |
| Indexed views | An index on a view materializes (executes), the view and the result set is permanently stored in a unique clustered index in the same way a table with a clustered index is stored. Nonclustered indexes on the view can be added after the clustered index is created. |
| Full-text | A special type of token-based functional index that is built and maintained by the Microsoft Full-Text Engine for SQL Server (MSFTESQL) service. It provides efficient support for sophisticated word searches in character string data. |
| XML | A shredded, and persisted, representation of the XML binary large objects (BLOBs) in the xml data type column. |
It just like a primary key, but it has no business impact. It’s used to uniquely identify records.
- In case of insert, update which query will be executed first
View: In SQL, a view is a virtual table based on the result-set of an SQL statement. A view contains rows and columns, just like a real table. The fields in a view are fields from one or more real tables in the database. View will not be updated if table is updated.
- Type of joins
- Differ between sp and function
- What the Union operator do
Notice that each SELECT statement within the UNION must have the same number of columns. The columns must also have similar data types. Also, the columns in each SELECT statement must be in the same order.
There are two types of unions, Union, Union All
- Union
SELECT column_name(s) FROM table_name1
UNION
SELECT column_name(s) FROM table_name2
- Union All
SELECT column_name(s) FROM table_name1
UNION ALL
SELECT column_name(s) FROM table_name2
What is Candidate Key?
All unique keys which can be set as primary key are called candidate keys.
Difference between truncate, delete, drop
Delete: Deletes the data from table
Truncate: Deletes the data from table also re set the auto increment values.
Drop: Deletes the data and structure of table.
- Different between web server and app server
- Difference between session and cookie
- Difference between web site and web application
- Difference between Web service & API
Difference between load testing, performance testing, stress testing
Performance testing is a general concept.
Performance Testing= Load Testing+ Stress Testing
Load Testing: Application response time for specific no of users.
Stress Testing: Application behavior for a specific time when the break point where application will be breaked.
In SQL Difference between “IN” and “BETWEEN”
Both are used in to fetch data for a specific range. For example if you want to fetch salary of an employee from salary 15000 to 25000. When you use IN then 15000, 25000 will not be fetched. When you use between all data between 15000 and 25000 including these two will be fetched.
Stack and heap
Stack: In line memory, lives Ram memory where value types saved
Heap: collection, values which are not convertible to binary are saved. Pointer to that values is in stack.
Normalization
Unnormalized – There are multivalued attributes or repeating groups
1 NF – No multivalued attributes or repeating groups.
2 NF – 1 NF plus no partial dependencies
3 NF – 2 NF plus no transitive dependencies
What is the difference between an ERD and an ERM?
ERM: In software engineering, an entity-relationship model (ERM) is an abstract and conceptual representation of data. Entity-relationship modeling is a database modeling method, used to produce a type of conceptual schema or semantic data model of a system, often a relational database, and its requirements in a top-down fashion. Diagrams created by this process are called entity-relationship diagrams, ER diagrams, or ERDs.
Post a Comment