Q.#) What Is SQL Profiler?
SQL Profiler is a tool which allows system administrator to monitor events in the SQL server. This is mainly used to capture and save data about each event of a file or a table for analysis.
Q.#) What is recursive stored procedure?
SQL Server supports recursive stored procedure which calls by itself. Recursive stored procedure can be defined as a method of problem solving wherein the solution is arrived repetitively. It can nest up to 32 levels.
Q.#) TCP/IP port does SQL Server run on?
By default SQL Server runs on port 1433.
Q.#) 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.
Q.#) List the different index configurations possible for a table?
A table can have one of the following index configurations:
1. No indexes
2. A clustered index
3. A clustered index and many non-clustered indexes
4. A non-clustered index
5. Many non-clustered indexes
Q.#) 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:
1. Full
2. Simple
3. Bulk-Logged
Q.#) Different backups available in SQL Server?
Different possible backups are:
1. Full backup
2. Differential Backup
3. Transactional Log Backup
4. Copy Only Backup
5. File and Filegroup backup
Q.#) 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.
Q.#) 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.
Q.#) What are the properties of the Relational tables?
Relational tables have six properties:
Values are atomic.
1. Column values are of the same kind.
2. Each row is unique.
3. The sequence of columns is insignificant.
4. The sequence of rows is insignificant.
5. Each column must have a unique name.
Q.#) Difference between a primary key and a unique key?
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.
Q.#) 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.
Q.#) What is the difference between a HAVING CLAUSE and a WHERE CLAUSE?
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.
Q.#) 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 a secondary server which keeps a secondary server up to date with the principal server.
Q.#) What is 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.
Q.#) What are the advantages of Log shipping?
Advantages of Log shipping:
1. Secondary database can be used as a read-only purpose.
2. Multiple secondary standby servers are possible
3. Low maintenance.
Q.#) What is an execution plan?
Ans. An execution plan is a graphical or textual way of showing how the 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.
Q.#) What is the Stored Procedure?
A stored procedure is a set of SQL queries which can take input and send back output. And when the procedure is modified, all clients automatically get the new version. Stored procedures reduce network traffic and improve performance. Stored procedures can be used to help ensure the integrity of the database.
Q.#) List the advantages of using Stored Procedures?
Advantages of using Stored procedures are:
1. Stored procedure boosts application performance.
2. Stored procedure execution plans can be reused as they cached in SQL Server’s memory which reduces server overhead.
3. Stored procedures can be reused.
4. Stored procedures can encapsulate logic. You can change the stored procedure code without affecting clients.
5. Stored procedures provide better security for your data.
Q.#) What is identity in SQL?
An identity column in the SQL automatically generates numeric values. We can be defined as a start and increment value of the identity column. Identity columns do not need to be indexed.
Q.#) What are the common performance issues in SQL Server?
Following are the common performance issues:
1. Deadlocks
2. Blocking
3. Missing and unused indexes.
4. I/O bottlenecks
5. Poor Query plans
6. Fragmentation
Q.#) List the various tools available for performance tuning?
There are various tools available for performance tuning:
1. Dynamic Management Views
2. SQL Server Profiler
3. Server Side Traces
4. Windows Performance monitor.
5. Query Plans
6. Tuning advisor
Q.#) 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.
Q.#) How to count the number of records in a table?
1. SELECT * FROM table_Name
2. SELECT COUNT(*) FROM table_Name
3. SELECT rows FROM indexes WHERE id = OBJECT_ID(tableName) AND indid< 2
Q.#) 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.
Q.#) 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.
Q.#) How can we check the SQL Server version?
By running the following command: SELECT @@Version
Q.#) What is SQL Server Agent?
SQL Server agent allows us to schedule the jobs and scripts. It helps is implementing the day to day DBA tasks by automatically executing them on a scheduled basis.
Q.#) What is the PRIMARY KEY?
The primary key is a column whose values uniquely identify every row in a table. Primary key values can never be reused.
Q.#) What is a UNIQUE KEY constraint?
A UNIQUE constraint enforces the uniqueness of the values in a set of columns, so no duplicate values are entered. The unique key constraints are used to enforce entity integrity as the primary key constraints.
Q.#) What is FOREIGN KEY
When a one table’s primary key field is added to related tables in order to create the common field which relates the two tables, it called a foreign key in other tables. Foreign Key constraints enforce referential integrity.
Q.#) What is a CHECK Constraint?
A CHECK constraint is used to limit the values or type of data that can be stored in a column. They are used to enforce domain integrity.
Q.#) What are a Scheduled Jobs?
The scheduled job allows a user to run the scripts or SQL commands automatically on the scheduled basis. The user can determine the order in which commands need to execute and the best time to run the job to avoid the load on the system.
Q.#) What is a heap?
A heap is a table that does not contain any clustered index or non-clustered index.
Q.#) What is BCP?
BCP or Bulk Copy is a tool by which we can copy a large amount of data to tables and views. BCP does not copy the structures same as source to destination. BULK INSERT command helps to import a data file into a database table or view in a user-specified format.
Q.#) TCP/IP port does SQL Server run on?
By default SQL Server runs on port 1433.
Q.#) 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.
Q.#) List the different index configurations possible for a table?
A table can have one of the following index configurations:
1. No indexes
2. A clustered index
3. A clustered index and many non-clustered indexes
4. A non-clustered index
5. Many non-clustered indexes
Q.#) 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:
1. Full
2. Simple
3. Bulk-Logged
Q.#) Different backups available in SQL Server?
Different possible backups are:
1. Full backup
2. Differential Backup
3. Transactional Log Backup
4. Copy Only Backup
5. File and Filegroup backup
Q.#) 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.
Q.#) 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.
Q.#) What are the properties of the Relational tables?
Relational tables have six properties:
Values are atomic.
1. Column values are of the same kind.
2. Each row is unique.
3. The sequence of columns is insignificant.
4. The sequence of rows is insignificant.
5. Each column must have a unique name.
Q.#) Difference between a primary key and a unique key?
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.
Q.#) 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.
Q.#) What is the difference between a HAVING CLAUSE and a WHERE CLAUSE?
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.
Q.#) 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 a secondary server which keeps a secondary server up to date with the principal server.
Q.#) What is 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.
Q.#) What are the advantages of Log shipping?
Advantages of Log shipping:
1. Secondary database can be used as a read-only purpose.
2. Multiple secondary standby servers are possible
3. Low maintenance.
Q.#) What is an execution plan?
Ans. An execution plan is a graphical or textual way of showing how the 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.
Q.#) What is the Stored Procedure?
A stored procedure is a set of SQL queries which can take input and send back output. And when the procedure is modified, all clients automatically get the new version. Stored procedures reduce network traffic and improve performance. Stored procedures can be used to help ensure the integrity of the database.
Q.#) List the advantages of using Stored Procedures?
Advantages of using Stored procedures are:
1. Stored procedure boosts application performance.
2. Stored procedure execution plans can be reused as they cached in SQL Server’s memory which reduces server overhead.
3. Stored procedures can be reused.
4. Stored procedures can encapsulate logic. You can change the stored procedure code without affecting clients.
5. Stored procedures provide better security for your data.
Q.#) What is identity in SQL?
An identity column in the SQL automatically generates numeric values. We can be defined as a start and increment value of the identity column. Identity columns do not need to be indexed.
Q.#) What are the common performance issues in SQL Server?
Following are the common performance issues:
1. Deadlocks
2. Blocking
3. Missing and unused indexes.
4. I/O bottlenecks
5. Poor Query plans
6. Fragmentation
Q.#) List the various tools available for performance tuning?
There are various tools available for performance tuning:
1. Dynamic Management Views
2. SQL Server Profiler
3. Server Side Traces
4. Windows Performance monitor.
5. Query Plans
6. Tuning advisor
Q.#) 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.
Q.#) How to count the number of records in a table?
1. SELECT * FROM table_Name
2. SELECT COUNT(*) FROM table_Name
3. SELECT rows FROM indexes WHERE id = OBJECT_ID(tableName) AND indid< 2
Q.#) 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.
Q.#) 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.
Q.#) How can we check the SQL Server version?
By running the following command: SELECT @@Version
Q.#) What is SQL Server Agent?
SQL Server agent allows us to schedule the jobs and scripts. It helps is implementing the day to day DBA tasks by automatically executing them on a scheduled basis.
Q.#) What is the PRIMARY KEY?
The primary key is a column whose values uniquely identify every row in a table. Primary key values can never be reused.
Q.#) What is a UNIQUE KEY constraint?
A UNIQUE constraint enforces the uniqueness of the values in a set of columns, so no duplicate values are entered. The unique key constraints are used to enforce entity integrity as the primary key constraints.
Q.#) What is FOREIGN KEY
When a one table’s primary key field is added to related tables in order to create the common field which relates the two tables, it called a foreign key in other tables. Foreign Key constraints enforce referential integrity.
Q.#) What is a CHECK Constraint?
A CHECK constraint is used to limit the values or type of data that can be stored in a column. They are used to enforce domain integrity.
Q.#) What are a Scheduled Jobs?
The scheduled job allows a user to run the scripts or SQL commands automatically on the scheduled basis. The user can determine the order in which commands need to execute and the best time to run the job to avoid the load on the system.
Q.#) What is a heap?
A heap is a table that does not contain any clustered index or non-clustered index.
Q.#) What is BCP?
BCP or Bulk Copy is a tool by which we can copy a large amount of data to tables and views. BCP does not copy the structures same as source to destination. BULK INSERT command helps to import a data file into a database table or view in a user-specified format.
No comments:
Post a Comment