SQL Server Interview Day - 1

October 17, 2017 0 Comments A+ a-

1) What is SQL ?

  • SQL stands for Structured Query Language.
  • SQL is used for access the database and manipulate the data/records.
  • SQL is an ANSI (American National Standards Institute) standard. As per ANSI, SQL is the standard language for relational database management systems.

2) What are the two authentication modes in SQL Server?

SQL Server is providing two type authentication modes for access the database.
  • Windows Mode
  • Mixed Mode

3) What is Windows Mode in sql server ?

It is one type of authentication mode on Sql Server. It allow to access database using your windows credential (window's user name and password)

4) What is Normalization?

Normalization is the process of reorganizing data in a database so that it meets two basic requirements:
  • No redundancy of Data
  • Data dependencies
Normalization allows databases to take up as low disk space as possible, resulting in increased performance.

5) What is Denormalization ?

Denormalization is the process of attempting to optimize the read performance of a database by adding redundant data or by grouping data.In some cases, denormalization is a means of addressing performance or scalability in relational database software.

6) What are the differences between Local and Global Temporary Tables?

Local Temporary Tables:-

These are visible only to the connection (means single user) that creates it, and It will deleted when the connection is closed.

Syntax:-

CREATE TABLE #tl_Emp

Global Temporary Tables :-

These tables are visible to everyone, and It will deleted when all connections (means all the user). that have referenced them have closed.

Syntax:-

CREATE TABLE ##tg_Emp

7) How is ACID property related to Database?

ACID stands for Atomicity, Consistency, Isolation and Durability. Let's discuss on this topic one by one.

Atomicity:-

In a one transaction execute time two or more queries will process, either all of the queries are committed or none of one.

Consistency:-

A transaction either creates a new and valid state of data or if any failure occurs, returns all data to its state before the transaction was started.

Isolation:-

A transaction in process and not yet committed must remain isolated from any other transaction.

Durability:-

Committed data is saved by the system such that, even in the event of a failure and system restart, the data is available in its correct state.

8) What is SQL server agent?

SQL agent is an integral part of Microsoft’s SQL Server. The SQL Server agent plays a big role in day to day tasks of SQL server administrator(DBA). It runs as a windows service only and allows for the handling of a wide variety of tasks such as backup automation, database replication setup, job scheduling, user permissions and database monitoring.

9) What Is SQL Profiler?

It’s a tool for troubleshooting and tracing problems in MS SQL Server, Microsoft’s Relational Database Management System (RDBMS). The profiler lets developers and Database Administrators (DBAs) create and handle traces and replay and analyze trace results. Developer also easily identify the execute query's information.

10) What is Log Shipping?

It is the process of automating take backup the transaction log file on a production SQL server and restoring same records onto secondary server. The process of transferring the transaction log files and restoring is automated across the SQL Servers. As this process result both server have similar copies.