Register Login

SQL Server Interview Questions and Answer

Updated Mar 19, 2019

What is SQL Server?

SQL Server is a relational database management system developed by Microsoft. Apart from supporting ANSI SQL, it also has support for SQL and it contains the T-SQL, its personal implementation of the query language. It is dependent on the platform. It supports GUI and command line interfaces.

How to use SQL Server?

The SQL Server can be used for the following ways:

  • To make databases.
  • To perform database operations on the like create, update, remove, add, join etc.
  • The SQL Server Integration Services (SSIS) is used for performing ETL operations on the data.
  • The SQL Server Analysis Services (SSAS) provides analytics support for large and medium companies.
  • The SQL Server Reporting Services (SSRS) helps professional and business analysts to develop business reports from their data.
  • It is used for conducting data backups and optimizing database tasks.

What is Firebird SQL server?

Firebird SQL Server is an open source relational database management system. The Firebird API is used to connect to the Firebase database. It is very flexible and runs on Windows, LINUX, UNIX and Mac OS.

How to create the schema in SQL server?

The schema can be created in SQL Server Management Studio by these steps:

  • Click on the database folder in the Object Explorer.
  • Expand the database where the new schema is to be created.
  • In the Security folder, click on Schema inside New.
  • Inside the Schema New box, type in the name of the new schema.
  • Inside the Schema owner box, for owning the schema, enter the name of the role or database.
  • Hit on OK.

What is stored procedure in SQL server?

A stored procedure adds a security layer between the database and the user interface. It is also used to produce multiple output parameters after accepting input parameters. The stored procedures provide security through the data access controls so that the users do no perform write operations or modifications.

How to view stored procedure in SQL server?

Inside the SQL Server Management Studio, the stored procedure is viewed through the Object Explorer or using Transact SQL.

How to shrink the log file in SQL server?

The steps to shrink a log file are:

  • In the Microsoft SQL Server Management Studio, right click on the database and click on Properties.
  • Click on Options and make the recovery model Simple.
  • Hit OK.
  • Click Tasks, go to Shrink and then to Database.
  • Then after right-clicking on the database, select Properties and hit Options.
  • Alter the recovery model to Full and hit OK.

What option would you choose in order to store the database on a dedicated SQL server?

To store the database on a dedicated SQL Server, the Windows Internal Database is used.

How to connect to a local SQL server?

The local SQL Server can be connected through SSMS with SQL Server LocalDB.

What is collation in SQL Server?

The collation is a configuration setting that specifies how the database will interact with the character data at the database, column and server level. Different collations are used for supporting the language differences among the users of the databases. It specifies the strategy to compare and sort the character data. Another important function is to specify a character set that is a bit pattern for each character in the dataset.

How to resolve a deadlock in SQL server?

When a transaction is selected as a deadlock victim, the present batch is terminated, the transaction is rolled back and an error message is returned. An error handler is required to handle and resolve the deadlock. It handles the error 1205 and takes action by submitting the query that caused the deadlock. However, the application has to halt before resubmitting the query. This lets other transactions get time to execute and release their locks, and the chances of deadlocks are reduced in the future.

How to debug a stored procedure in SQL server?

The stored procedures in SQL can be debugged using the Transact SQL debugger. This makes the debugging process interactive by displaying the local variables, the SQL call stack, and the stored procedure parameters. The following are the steps to start debugging:

  • Inside the SQL Server, navigate to Debug and click on Start Debugging.
  • The stored procedure opens up in the window and the cursor can be seen blinking at the start of the window.
  • Perform the debugging operations in the window.

How to delete duplicate rows in SQL server?

Duplicate rows are deleted using the following methods:

  • Using the ROW_NUMBER() function.
  • Through a temporary table.
  • Using the MERGE statement.
  • Making a completely new table with different records.
  • Through the Common Table Expressions.
  • In SSIS, by using the Fuzzy Group Transformation

How to read an execution plan in SQL server?

An SQL query execution plan is read keeping in mind the following information:

  • The plan is in the shape of a tree that depends upon the statements in the query.
  • Every icon in the tree represents an operator.
  • Parent and children nodes are displayed.
  • The child nodes are connected to the parent nodes by arrows.
  • Child nodes belonging to the same parent reside in the same columns.
  • The cost of the query is represented as a percentage. Sum of the query costs must be 100 %.
  • The width of the connecting arrows depends on the number of rows.

How to rename a column in SQL server?

Columns are renamed in SQL by two methods:

1.Using Object Explorer in SQL Server Management Studio

  • Go to the Database Engine instance where the tables reside.
  • Right click on the table and select the Rename option.
  • Provide the new column name for altering the previous name.

2.Using T-SQL commands:

  • Go to the Database Engine instance where the tables reside.
  • Click on New Query in the Standard Bar.
  • Put the following code and press Execute

USE Database2019;

GO

EXEC sp_rename 'Table_name.OldColname', 'New_name', 'COLUMN';

GO

Where the Database2019 is the name of the database and Table_name is the table where the columns reside. The ‘Oldname’ is changed to ‘New_name’.

What is isolation level in SQL server?

A transaction level specifies the limit up to which the one transaction is isolated from another transaction’s data modifications. Some of their common functions are:

  • Controlling the type of locks requested and if they are taken when data is read.
  • Checking the amount of time read locks are on hold.
  • High transaction level reduces the concurrency effects.

How to find indexes on a table in SQL server?

  • Indexes are found using:
  • sp_helpindex
  • From the sys.indexes system catalogue
  • Using sys.index_columns

What is SQL server native client?

The SQL Native Client API is used to develop or make applications that use SQL Server 2005 instead of MDAC (Microsoft Data Access Components).

How to use SQL server profiler?

The SQL Server Profiler is used to detect and troubleshoot problems in the Server. It depicts the health of a SQL instance. Users make traces to store the data and analyze them for bugs. Then they use the profiler to fix these errors.

How to create two foreign keys in one table in SQL server?

Two foreign keys can be added to a table by creating them separately. For example,

FOREIGN KEY (added) REFERENCES TableStudent1_Addr(added),

FOREIGN KEY (id) REFERENCES Table1(id)


×