Symantec Most Frequently Asked Latest SQL Server Interview Questions Answers
What is a FULL Backup?
A full backup is the most common type of backup in SQL Server. This is the complete backup of the database. It also contains part of transaction log so it can be recovered.
What is OLTP?
OLTP means Online transaction processing which follows rules of data normalization to ensure data integrity. Using these rules complex information is broken down into a most simple structure.
What is RDBMS?
RDBMS or Relational Data Base Management Systems are database management systems that maintain data in the form of tables. We can create relationships between the tables. An RDBMS has the capability to recombine the data items from different files, providing powerful tools for data usage.
What are the properties of the Relational tables?
Relational tables have six properties:
Values are atomic.
Column values are of the same kind.
Each row is unique.
The sequence of columns is insignificant.
The sequence of rows is insignificant.
Each column must have a unique name.
What’s the difference between a primary key and a unique key?
The differences between primary key and a unique key are:
The primary key is a column whose values uniquely identify every row in a table. Primary key values can never be reused. They create a clustered index on the column and cannot be null.
A Unique key is a column whose values also uniquely identify every row in a table but they create a non-clustered index by default and it allows one NULL only.
When is UPDATE_STATISTICS command used?
As the name implies UPDATE_STATISTICS command updated the statistics used by the index to make the search easier.
What is the difference between a HAVING CLAUSE and a WHERE CLAUSE?
The differences between HAVING CLAUSE and WHERE CLAUSE are:
Both specify a search condition but Having clause is used only with the SELECT statement and typically used with GROUP BY clause.
If GROUP BY clause is not used then Having behaved like WHERE clause only.
Which TCP/IP port does SQL Server run on?
By default SQL Server runs on port 1433.
What is the difference between clustered and a non-clustered index?
A clustered index is an index that rearranges the table in the order of index itself. Its leaf nodes contain data pages. A table can have only one clustered index.
A non-clustered index is an index that does not re-arranges the table in the order of index itself. Its leaf nodes contain index rows instead of data pages. A table can have many non-clustered indexes.
List the different index configurations possible for a table?
A table can have one of the following index configurations:
No indexes
A clustered index
A clustered index and many non-clustered indexes
A non-clustered index
Many non-clustered indexes
What is the recovery model? List the types of recovery model available in SQL Server?
Recovery model basically tells SQL Server what data should be kept in the transaction log file and for how long. A database can have only one recovery model.
It also tells SQL server that which backup is possible in a particular recovery model selected. There are three types of recovery model:
Full
Simple
Bulk-Logged
Mirroring is a high availability solution. It is designed to maintain a hot standby server which is consistent with the primary server in terms of a transaction. Transaction Log records are sent directly from principal server to secondary server which keeps a secondary server up to date with the principal server.
What are the advantages of the Mirroring?
Advantages of Mirroring are:
It is more robust and efficient that Log shipping.
It has an automatic failover mechanism.
The secondary server is synced with the primary in near real time.
What is a Log Shipping?
Log shipping is nothing but the automation of backup and restores of a database from one server to another standalone standby server. This is one of the disaster recovery solutions. If one server fails for some reason we will have the same data available on the standby server.
What are the advantages of Log shipping?
Advantages of Log shipping:
Easy to set up.
Secondary database can be used as a read-only purpose.
Multiple secondary standby servers are possible
Low maintenance.
Can we take the full database backup in Log shipping?
Yes, we can take the full database backup. It won’t affect the log shipping.
What is an execution plan?
An execution plan is a graphical or textual way of showing how SQL server breaks down a query to get the required result. It helps a user to determine why queries are taking more time to execute and based on the investigation user can update their queries for the maximum result.
In Query Analyzer is an option called “Show Execution Plan” (located on the Query drop-down menu). If this option is turned on it will display a query execution plan in a separate window when a query is run again.
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.
This improves the performance of the retrieval of the data from the database.
What is a FULL Backup?
A full backup is the most common type of backup in SQL Server. This is the complete backup of the database. It also contains part of transaction log so it can be recovered.
What is OLTP?
OLTP means Online transaction processing which follows rules of data normalization to ensure data integrity. Using these rules complex information is broken down into a most simple structure.
What is RDBMS?
RDBMS or Relational Data Base Management Systems are database management systems that maintain data in the form of tables. We can create relationships between the tables. An RDBMS has the capability to recombine the data items from different files, providing powerful tools for data usage.
What are the properties of the Relational tables?
Relational tables have six properties:
Values are atomic.
Column values are of the same kind.
Each row is unique.
The sequence of columns is insignificant.
The sequence of rows is insignificant.
Each column must have a unique name.
What’s the difference between a primary key and a unique key?
The differences between primary key and a unique key are:
The primary key is a column whose values uniquely identify every row in a table. Primary key values can never be reused. They create a clustered index on the column and cannot be null.
A Unique key is a column whose values also uniquely identify every row in a table but they create a non-clustered index by default and it allows one NULL only.
When is UPDATE_STATISTICS command used?
As the name implies UPDATE_STATISTICS command updated the statistics used by the index to make the search easier.
 |
| Symantec Most Frequently Asked Latest SQL Server Interview Questions Answers |
What is the difference between a HAVING CLAUSE and a WHERE CLAUSE?
The differences between HAVING CLAUSE and WHERE CLAUSE are:
Both specify a search condition but Having clause is used only with the SELECT statement and typically used with GROUP BY clause.
If GROUP BY clause is not used then Having behaved like WHERE clause only.
Which TCP/IP port does SQL Server run on?
By default SQL Server runs on port 1433.
What is the difference between clustered and a non-clustered index?
A clustered index is an index that rearranges the table in the order of index itself. Its leaf nodes contain data pages. A table can have only one clustered index.
A non-clustered index is an index that does not re-arranges the table in the order of index itself. Its leaf nodes contain index rows instead of data pages. A table can have many non-clustered indexes.
List the different index configurations possible for a table?
A table can have one of the following index configurations:
No indexes
A clustered index
A clustered index and many non-clustered indexes
A non-clustered index
Many non-clustered indexes
What is the recovery model? List the types of recovery model available in SQL Server?
Recovery model basically tells SQL Server what data should be kept in the transaction log file and for how long. A database can have only one recovery model.
It also tells SQL server that which backup is possible in a particular recovery model selected. There are three types of recovery model:
Full
Simple
Bulk-Logged
What is Mirroring?
Mirroring is a high availability solution. It is designed to maintain a hot standby server which is consistent with the primary server in terms of a transaction. Transaction Log records are sent directly from principal server to secondary server which keeps a secondary server up to date with the principal server.
What are the advantages of the Mirroring?
Advantages of Mirroring are:
It is more robust and efficient that Log shipping.
It has an automatic failover mechanism.
The secondary server is synced with the primary in near real time.
What is a Log Shipping?
Log shipping is nothing but the automation of backup and restores of a database from one server to another standalone standby server. This is one of the disaster recovery solutions. If one server fails for some reason we will have the same data available on the standby server.
What are the advantages of Log shipping?
Advantages of Log shipping:
Easy to set up.
Secondary database can be used as a read-only purpose.
Multiple secondary standby servers are possible
Low maintenance.
Can we take the full database backup in Log shipping?
Yes, we can take the full database backup. It won’t affect the log shipping.
What is an execution plan?
An execution plan is a graphical or textual way of showing how SQL server breaks down a query to get the required result. It helps a user to determine why queries are taking more time to execute and based on the investigation user can update their queries for the maximum result.
In Query Analyzer is an option called “Show Execution Plan” (located on the Query drop-down menu). If this option is turned on it will display a query execution plan in a separate window when a query is run again.
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.
This improves the performance of the retrieval of the data from the database.
Post a Comment