SQL Server – Basics

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.

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.

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.

What do you mean by Relationships between Tables and mention their Types?
Ans: Relationships between tables describe how a row in a table is related to a row or rows of another table in a database.
There are 3 types of relationships, as follows:

• One to one: When a row in a table has only one related row in another table
• One to many: When a row in a table has multiple related rows in another table
• Many to many: When a row has multiple related rows in another table and vice-versa.

What is an Entity in a Database?
Ans: Entities are master data services models or objects that can be distinguishable from other objects.
Rows in an entity represent members of the master data, and columns represent attributes of the master data. Generally, entities group the same kind of data.
For example, a list of employees of a company.

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

What is Data Integrity?
Ans: 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.

What is SQL?
Ans: 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.

What is DBMS, and what types of DBMS do you know?
Ans: 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.

 What is SQL Server, and mention its core components?
Ans: 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.

What are ACID properties, and what do they mean?
Ans: 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.

What is the use of Database Engine in the SQL Server?
Ans: 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.

What is a SQL comment?
Ans: A human-readable clarification on what a particular piece of code does. SQL code comments can be single-line (preceded by a double dash --) or span over multiple lines (as follows: /*comment_text*/). When the SQL engine runs, it ignores code comments. The purpose of adding SQL code comments is to make the code more comprehensive for those people who will read it in the future.

What is the TCP/IP port on which SQL Server runs?
The TCP/IP port on which SQL Server runs is 1433.


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 deadlock 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.