Alex tech

Stored Procedure Stored procedure is a database object that is a set of SQL statements stored on the server. Stored procedures are very similar to ordinary procedures in high-level languages, they can have input and output parameters and local variables, they can perform numeric calculations and operations on symbolic data, the results of which can be assigned to variables and parameters. Stored procedures can perform standard database operations (both DDL and DML). In addition, loops and branches are possible in stored procedures, that is, they can use instructions to control the execution process. Stored procedures improve performance, enhance programmability, and support data security features. In most DBMSs, the first time a stored procedure is run, it is compiled (parsed and a data access plan is generated) and further processing it faster. Trigger Trigger is a stored procedure of a special type, which the user does not call directly, but whose execution is conditioned by the ...

Transaction is an impact on a database that transfers it from one consistent state to another and is expressed in changing the data stored in the database. Main properties of the transaction Atomicity ensures that no transaction is partially committed in the system. Either all of its suboperations will be executed, or none of them will be executed. Consistency . A transaction that reaches its normal completion and thus commits its results maintains database consistency. Isolation . During the execution of a transaction, concurrent transactions should not affect its outcome. Durability . Regardless of problems at the lower levels (for example, a system outage or hardware failures), changes made by a successfully completed transaction should remain saved after the system is returned to work. Levels of transaction isolation In order to increase the isolation of transactions and, accordingly, the reliability of working with data: Read uncommitted (dirty read) - read uncommitted ...

Nonclustered indexes - data is physically arranged in random order, but logically ordered according to the index. This type of index is suitable for frequently changing datasets. With cluster indexing, the data is physically ordered, which significantly increases the speed of data retrieval (but only in the case of sequential data access). Only one clustered index can be created per dataset. Does it make sense to index data that has a small number of possible values? Approximate rule that can be followed when creating an index is that if the amount of information (in bytes) that does NOT satisfy the sampling condition is less than the size of the index (in bytes) for the given sampling condition, then, in general, optimization will slow down the sampling. When is full dataset scan superior to index access? Full scan is performed by multi-block reading. Index scan - single block. Also, when accessing by index, the index itself is scanned first, and then blocks are read from the dat...

By sort order ordered - the indices in which the elements are ordered increasing decreasing unordered - indices at which the items are unordered By data source view indexes expression indices By influence on the data source clustered index - When defined in a dataset, the physical location of the data is rebuilt according to the structure of the index. The logical structure of the dataset in this case is more of a dictionary than an index. The data in the dictionary is physically ordered, such as alphabetically. Clustered indexes can provide significant improvements in data search performance, even when compared to regular indexes. The performance increase is especially noticeable when working with sequential data. nonclustered index is the most common member of the index family. Unlike clustered, they do not rebuild the physical structure of the dataset, but only organize links to the corresponding records. To identify the required record in the data set, the noncluster...

Index - a database object created to improve the performance of data retrieval. Datasets can have a large number of records, which are stored in random order, and it can take a long time to search for them according to a given criterion by sequentially scanning the dataset, record by record. The index is formed from the values of one or several fields and pointers to the corresponding records of the data set, thus, a significant increase in the speed of retrieving from this data is achieved. Benefits acceleration of searching and sorting by a specific field or set of fields ensuring the uniqueness of data Disadvantages the requirement for additional space on disk and in RAM, and the larger/longer the key, the larger the index size Slower insert, update, and delete operations because the indexes themselves have to be updated. Indexes are preferred for : Counter fields to avoid duplicate values in this field; The fields by which the data is sorted; Fields that are frequ...

Normalization is the process of transforming database relationships to a normal form (a step-by-step, reversible process of replacing the original schema with another schema in which the datasets have a simpler and more logical structure). Normalization is intended to bring the database structure to a form that provides minimal logical redundancy, and is not intended to decrease or increase the performance or decrease or increase the physical volume of the database. The ultimate goal of normalization is to reduce the potential inconsistency of information stored in the database. Normal forms First Normal Form (1NF) - relationship is in 1NF if the values ​​of all its attributes are atomic (indivisible). Second normal form (2NF) - relationship is in 2NF if it is in 1NF, and all non-key attributes depend only on the whole key, and not on any part of it. Third normal form (3NF) - relationship is in 3NF if it is in 2NF and all non-key attributes are independent of each other. Fourt...

Database is a set of information organized and adapted for processing by a computer system. Database management system (DBMS) is a set of general or special purpose tools that provide creation, access to materials and database management. The main functions of the DBMS: data management data change logging data backup and recovery support for data definition and manipulation language Relational data model Relational data model is logical data model and applied theory of building relational databases. The relational data model includes the following components: Structural aspect - data is a set of relationships. Integrity aspect - relationships meet certain integrity conditions: domain (data type) level, relationship level, and database level. Aspect of processing (manipulation) - support for operators to manipulate relations (relational algebra, relational calculus). Normal form is a property of relation in relational data model that characterizes it in terms of redu...

Transaction isolation level is a conditional value that determines to what extent, as a result of logically parallel transactions in the DBMS, it is allowed to receive inconsistent data. The transaction isolation scale contains a range of values, ranked from lowest to highest; a higher level of isolation means better data consistency, but using it can reduce the number of physically concurrent transactions. Conversely, a lower level of isolation allows more concurrent transactions, but reduces the accuracy of the data. Thus, choosing the used level of isolation of transactions, the developer of the information system, to a certain extent, provides a choice between the speed of work and ensuring the guaranteed consistency of the data received from the system. Concurrent access issues using transactions With parallel execution of transactions, the following problems are possible: lost update - if one data block is simultaneously changed by different transactions, all changes are lost...