What are the properties of a transaction?
The properties of a transaction are referred to as ACID properties. They are –
- Atomicity
- Consistency
- Isolation
- Durability
Describe ACID properties in databases.
Atomicity: This characteristic of a transaction means that a transaction is performed completely not performed at all. I.e. all the tasks in a transaction are completed or none are completed.
Consistency: This characteristic means that the database should be consistent before and after the transaction. For a successful transaction, the database can move from one state to another. Both states should abide by the same rules. For an unsuccessful transaction, if the transaction fails to abide by the rule and leads to an inconsistent state, the transaction should be rolled back.
Isolation: A transaction should be isolated. This means that no other operation should be allowed to access or see the intermediate state data.
Durability: When a transaction is finished, it must continue. It shouldn’t be undone and should be able to resist system outages. In other words, After a transaction successfully completes, changes to data persist and are not undone, even in the event of a system failure.
What is a View in SQL Server?
The data from one or more tables are combined into a virtual table called a view. Views limit table data access by choosing only necessary values and simplify complex queries.
A view is a virtual table whose contents are defined by a query. A view is made up of a number of named columns and rows of data, similar to a table. A view does not exist until it is indexed.
What is the benefit of the VIew?
Multiple tables can be joined together into a single virtual table using views. When the database engine aggregates data using methods like sum, average, etc. and presents the calculated results as part of the data, views can function as aggregated tables.
Views can hide the complexity of data.
Define UNION, UNION ALL, MINUS, INTERSECT?
UNION returns all distinct rows.
UNION ALL returns all rows, including duplicates.
Any separate rows that were chosen by the first query but not by the second are returned by the MINUS operator.
INTERSECT – returns every unique row that was chosen by both queries.
What is the meaning of NOT NULL in SQL Server?
Constraints are rules that specify the kind of data that can be entered into database tables. One of the constraints, NOT NULL, is used to make sure that a specific field in a table never receives a null value. Any insert or update action that tries to enter a null value in a column after a NOT NULL constraint has been established for that column will fail.
What does SQL Server’s term “built-in function” mean?
A built-in function in SQL is a predefined piece of code that accepts 0 or more arguments and returns a value.
Functions are collections of statements that take inputs, process them to carry out certain tasks, and then return results.
When you can use Database Triggers?
- When a database has to have an audit trail of activity
- obtaining extra info from a database that isn’t already there
- When enforcing referential integrity
- to put a business rule into effect
What is dynamic SQL?
The term “dynamic SQL” refers to a set of statements that are dynamically created at runtime, not stored in a database, and only executed at that moment.
What is ER Diagram?
The entity-relationship diagram (ER diagram) provides a visual representation of the logical links between tables in databases. The ER Diagram shows the relationships between the tables as well as the table structures’ column names and associated data types.
What distinctions may be made between dynamic SQL and stored procedures? OR
What is the difference between Stored Procedures and Dynamic SQL?
A stored procedure is a collection of statements that are STORED in compiled form in the databases as a database object.
Dynamic SQL statements are created dynamically during runtime, are not saved in a database, and are only executed at that moment.
Big Data and SQL 