Sql Server Basics and Architechture

This Articles helps you to understand the basic concept & Architechture of Sql Server

1. What do you mean by database?
A database is a structured form of data storage where data can be retrieved and managed efficiently. It is the collection of tables where the data is stored in an organized way. Tables consist of rows and columns in which rows are also known as records or tuples, and columns are known as attributes.

2. What is a Relational Database?
A relational database is the collection of data that have an established relationship with each other. It consists of rows and columns. Each row in a database is a record, and columns have the attributes of data. Each row in a table can be identified with a unique ID known as a primary key. And rows in a table can be related to other tables using foreign keys.

3. What do you mean by RDBMS?
RDBMS stands for Relational DataBase Management System. It's the most common type of DBMS, used for working with data stored in multiple tables related to each other by means of shared keys. that allows storing, managing, querying, and retrieving data from a relational database. And RDBMS interacts with users and the database; it can also carry out administrative tasks such as – managing data storage, accessing data, and assessing database performance.
The SQL programming language is particularly designed to interact with RDBMS. Some examples of RDBMS are MySQL, PostgreSQL, Oracle, MariaDB, etc.

4. What is Data Integrity?
Data Integrity is the assurance of accuracy and consistency of data over its entire life-cycle and is a critical aspect of the design, implementation, and usage of any system which stores, processes, or retrieves data.
It also defines integrity constraints to enforce business rules on the data when it is entered into an application or a database.

5. What is SQL?
It stands for Structured Query Language. A programming language used for interaction with relational database management systems (RDBMS). This includes fetching, updating, inserting, and removing data from tables.

6. What is DBMS, and what types of DBMS do you know?
It stands for Database Management System, a software package used to perform various operations on the data stored in a database, such as accessing, updating, wrangling, inserting, and removing data. There are various types of DBMS, such as relational, hierarchical, network, graph, or object-oriented. These types are based on the way the data is organized, structured, and stored in the system.

7. What is SQL Server, and mention its core components?
SQL Server is an RDBMS developed by Microsoft. It has two core components – database engine and SQLOS.
The database engine has a relation engine that supports processing queries, and managing database files, pages, indexes, etc.
SQLOS is a SQL Operating system. SQLOS provides operating systems such as memory and I/O management. It also performs the operations like exception handling and synchronization services.

8. What are ACID properties, and what do they mean?
The ACID properties are nothing but Atomicity, Consistency, Isolation, and Durability. Generally, all transactions must follow ACID properties.
Atomicity: It ensures that a complete transaction must take place in a single execution. Suppose there is only a subset of operations during a transaction, then there could be a compromise in the aim of the transaction. But, atomicity eliminates this possibility.
Consistency: It ensures that a transaction takes place with absolute data consistency before and after the transaction. Simply put, data consistency shouldn’t be compromised during transactions.
Isolation: It ensures that each transaction takes place in complete isolation from other transactions. It means that each transaction must run as if it is the only transaction that is happening in the system.
Durability: It makes sure that every transaction must be recoverable when required. Once a transaction is committed, it means that all the data updates have been made in the database then, and they can be retrieved at any time by users.

9. What are the various editions of SQL Server 2019 that are available in the market?
SQL Server 2019 is available in 5 editions. These are as follows:
Enterprise: This delivers comprehensive high-end datacenter capabilities with blazing-fast performance, unlimited virtualization, and end-to-end business intelligence for mission-critical workloads and end-user access to data insights.
Standard: This delivers basic data management and business intelligence database for departments and small organizations to run their applications and supports common development tools for on-premises and cloud-enabling effective database management.
Web: This edition is a low total-cost-of-ownership option for Web hosters and Web VAPs to provide scalability, affordability, and manageability capabilities for small to large-scale Web properties.
Express: Express edition is the entry-level, free database and is ideal for learning and building desktop and small server data-driven applications.
Developer: This edition lets developers build any kind of application on top of SQL Server. It includes all the functionality of Enterprise edition, but is licensed for use as a development and test system, not as a production server.

10. What are trace flags and mention a few common trace flags used with SQL Server?
These flags are used to alter server behavior or set server characteristics. Few common trace flags used with SQL Server are as follows
1204, 1205, 1222 – These flags are used for dead-lock Information.
174 –  This trace flag increases the SQL Server Database Engine plan cache bucket count from 40,009 to 160,001 on 64-bit systems.
1118 – Forces uniform extent allocations instead of mixed page allocations – (SQL 2005 and 2008) To reduces Temp-DB contention.
652  – This trace flag disables page prefetching scans.
2566 – Used to run the DBCC CHECKDB command without data purity check, unless the DATA_PURITY option is specified.

11. In which TCP/IP port does SQL Server run? Can it be changed?
SQL Server runs on port 1433. Yes, it can be changed from the network utility TCP/IP properties.

12. What is the use of Database Engine in the SQL Server?
The database engine can store, process, and secure data. It creates database objects such as stored procedures, views, and triggers; and executes the objects. It processes queries, provides controlled access, and optimizes transactions in a database. A SQL server can support up to 50 database engine instances in a single computer.