EXL Service Most Frequently Asked Latest SQL Server Interview Questions Answers
What is a performance monitor?
Windows performance monitor is a tool to capture metrics for the entire server. We can use this tool for capturing events of SQL server also.
Some useful counters are – Disks, Memory, Processors, Network etc.
What are 3 ways to get a count of the number of records in a table?
SELECT * FROM table_Name
SELECT COUNT(*) FROM table_Name
SELECT rows FROM indexes WHERE id = OBJECT_ID(tableName) AND indid< 2
Can we rename a column in the output of SQL query?
Yes by using the following syntax we can do this.
SELECT column_name AS new_name FROM table_name;
What is the difference between a Local and a Global temporary table?
If defined in inside a compound statement a local temporary table exists only for the duration of that statement but a global temporary table exists permanently in the database but its rows disappear when the connection is closed.
Why are Views required in the SQL Server or in any other database?
Views are very beneficial because of the following reasons:
Views are required to hide the complexity that is involved in the database schema and also to customize the data for a particular set of users.
Views provide a mechanism to control the access to particular rows and columns.
These help in aggregating the data to improve the performance of the database.
What command is used to delete a table from the database in the SQL Server and how?
Delete Command is used to delete any table from the database in the SQL Server. Following is the way to use this command:
Delete Name of the table
Ex: If the name of a table is “employee” then delete command to delete this table can be written as Delete employee.
Why is replication required on the SQL Server?
Replication is the mechanism which is used to synchronize the data among the multiple servers with the help of a replica set.
This is mainly used to increase the capacity of the reading and to provide an option to its users to select among various different servers to perform the read/write operations.
What command is used to create a database in the SQL Server and how?
CREATEDATABASE Command is used to create any database in the SQL Server. Following is the way to use this command:
CREATEDATABASE Name of the Database
Ex: If the name of a database is “employee” then create command to create this database can be written as CREATEDATABASE employee.
What function does a database engine serve in the SQL Server?
Database Engine is a type of a service in the SQL Server which starts as soon as the Operating System starts. This may run by default depending upon the settings in the O/S.
What are the advantages of having an index on the SQL Server?
The index has the following advantages:
Index supports the mechanism of having faster data retrieval from the database.
This forms a data structure in a way which helps in minimizing the data comparisons.
SQL Profiler provides a graphical representation of events in an instance of SQL Server for the monitoring and investment purpose. We can capture and save the data for further analysis. We can put filters as well to captures the specific data we want.
What do you mean by authentication modes in SQL Server?
There are two authentication modes in SQL Server.
Windows mode
Mixed Mode – SQL and Windows.
How can we check the SQL Server version?
By running the following command:
SELECT @@Version
Is it possible to call a stored procedure within a stored procedure?
Yes, we call a stored procedure within a stored procedure It is called recursion property of SQL server and these type of stored procedures are called nested stored procedures.
What are the common performance issues in SQL Server?
Following are the common performance issues:
Deadlocks
Blocking
Missing and unused indexes.
I/O bottlenecks
Poor Query plans
Fragmentation
List the various tools available for performance tuning?
There are various tools available for performance tuning:
Dynamic Management Views
SQL Server Profiler
Server Side Traces
Windows Performance monitor.
Query Plans
Tuning advisor
What is a performance monitor?
Windows performance monitor is a tool to capture metrics for the entire server. We can use this tool for capturing events of SQL server also.
Some useful counters are – Disks, Memory, Processors, Network etc.
What are 3 ways to get a count of the number of records in a table?
SELECT * FROM table_Name
SELECT COUNT(*) FROM table_Name
SELECT rows FROM indexes WHERE id = OBJECT_ID(tableName) AND indid< 2
Can we rename a column in the output of SQL query?
Yes by using the following syntax we can do this.
SELECT column_name AS new_name FROM table_name;
What is the difference between a Local and a Global temporary table?
If defined in inside a compound statement a local temporary table exists only for the duration of that statement but a global temporary table exists permanently in the database but its rows disappear when the connection is closed.
Why are Views required in the SQL Server or in any other database?
Views are very beneficial because of the following reasons:
Views are required to hide the complexity that is involved in the database schema and also to customize the data for a particular set of users.
Views provide a mechanism to control the access to particular rows and columns.
These help in aggregating the data to improve the performance of the database.
What command is used to delete a table from the database in the SQL Server and how?
Delete Command is used to delete any table from the database in the SQL Server. Following is the way to use this command:
Delete Name of the table
Ex: If the name of a table is “employee” then delete command to delete this table can be written as Delete employee.
Why is replication required on the SQL Server?
Replication is the mechanism which is used to synchronize the data among the multiple servers with the help of a replica set.
This is mainly used to increase the capacity of the reading and to provide an option to its users to select among various different servers to perform the read/write operations.
What command is used to create a database in the SQL Server and how?
CREATEDATABASE Command is used to create any database in the SQL Server. Following is the way to use this command:
CREATEDATABASE Name of the Database
Ex: If the name of a database is “employee” then create command to create this database can be written as CREATEDATABASE employee.
 |
| EXL Service Most Frequently Asked Latest SQL Server Interview Questions Answers |
What function does a database engine serve in the SQL Server?
Database Engine is a type of a service in the SQL Server which starts as soon as the Operating System starts. This may run by default depending upon the settings in the O/S.
What are the advantages of having an index on the SQL Server?
The index has the following advantages:
Index supports the mechanism of having faster data retrieval from the database.
This forms a data structure in a way which helps in minimizing the data comparisons.
This improves the performance of the retrieval of the data from the database.
What is the SQL Profiler?SQL Profiler provides a graphical representation of events in an instance of SQL Server for the monitoring and investment purpose. We can capture and save the data for further analysis. We can put filters as well to captures the specific data we want.
What do you mean by authentication modes in SQL Server?
There are two authentication modes in SQL Server.
Windows mode
Mixed Mode – SQL and Windows.
How can we check the SQL Server version?
By running the following command:
SELECT @@Version
Is it possible to call a stored procedure within a stored procedure?
Yes, we call a stored procedure within a stored procedure It is called recursion property of SQL server and these type of stored procedures are called nested stored procedures.
What are the common performance issues in SQL Server?
Following are the common performance issues:
Deadlocks
Blocking
Missing and unused indexes.
I/O bottlenecks
Poor Query plans
Fragmentation
List the various tools available for performance tuning?
There are various tools available for performance tuning:
Dynamic Management Views
SQL Server Profiler
Server Side Traces
Windows Performance monitor.
Query Plans
Tuning advisor