Sql Server Interview Questions and Answers (1758) - Page 14

Latest and authentic Interview questions. You can also post an interview question and win monthly prizes as well as gain community credit points.

1758 records found.

Post |

Interview Experiences |

Interview FAQs |

Online Interviews |

Exclusive Questions

Get 650+ Questpond's Interview videos on discount

What is the use of @@LANGID?

It returns the local language identifier (ID) of the language that is currently being used.

What is the use of @@VERSION statement?

@@VERSION statement returns version, processor architecture, build date, and operating system for the current installation of SQL Server.

What is the purpose of @@SERVERNAME statement in T-SQL?

To know the name of the local server that is running SQL Server.

What is the purpose of @@FETCH_STATUS statement in T-SQL?

To know the status of the last cursor FETCH statement issued against any cursor currently opened by the connection

What is Modulo EQUALS statement in SQL server?

Modulo divides one number by another and return the result of the operation.
%= is the operator used for this.

What is the use of @@ROWCOUNT?

Returns the number of rows affected by the last statement.

What is the maximum number rows can be returned in SqlServer by the @@ROWCOUNT?

2 billion rows.

If the rows are more than 2 billion, how can I set the rowcount?

Using ROWCOUNT_BIG statement.

How do we handle errors in SQL Server?

Using @@ERROR statement.

How do we raise the error message?

Using RAISERROR method.

Can we have Try catch in Sqlserver?

Yes we can have Try catch statements in Sqlserver from SQLServer 2005

What is the syntax of try catch?

BEGIN TRY
END TRY
BEGIN CATCH

END CATCH;

How can we cancel a transaction?

Using ROLLBACK TRANSACTION;

What is the purpose of @@LANGUAGE statement?

To know the current language used.

What is the use of @@IDLE in Transact-SQL?

@@IDLE informs the time that SQL Server has been idle since it was last started

What is Replication and Database Mirroring?

Database mirroring can be used with replication to provide availability for the publication database. Database mirroring involves two copies of a single database that typically reside on different computers. At any given time, only one copy of the database is currently available to clients which are known as the principal database. Updates made by clients to the principal database are applied on the other copy of the database, known as the mirror database. Mirroring involves applying the transaction log from every insertion, update, or deletion made on the principal database onto the mirror database.

What is OLTP (Online Transaction Processing)?

In OLTP - online transaction processing systems relational database design use the discipline of data modeling and generally follow the Codd rules of data normalization in order to ensure absolute data integrity. Using these rules complex information is broken down into its most simple structures (a table) where all of the individual atomic level elements relate to each other and satisfy the normalization rules.

When is the use of UPDATE_STATISTICS command?

This command is basically used when a large processing of data has occurred. If a large amount of deletions any modification or Bulk Copy into the tables has occurred, it has to update the indexes to take these changes into account. UPDATE_STATISTICS updates the indexes on these tables accordingly.

What is SQL Profiler?

SQL Profiler is a graphical tool that allows system administrators to monitor events in an instance of Microsoft SQL Server. You can capture and save data about each event to a file or SQL Server table to analyze later. For example, you can monitor a production environment to see which stored procedures are hampering performances by executing too slowly.
Use SQL Profiler to monitor only the events in which you are interested. If traces are becoming too large, you can filter them based on the information you want, so that only a subset of the event data is collected. Monitoring too many events adds overhead to the server and the monitoring process and can cause the trace file or trace table to grow very large, especially when the monitoring process takes place over a long period of time.

What is Log Shipping?

Log shipping is the process of automating the backup of database and transaction log files on a production SQL server, and then restoring them onto a standby server. Enterprise Editions only supports log shipping. In log shipping the transactional log file from one server is automatically updated into the backup database on the other server. If one server fails, the other server will have the same db and can be used this as the Disaster Recovery plan. The key feature of log shipping is that it will automatically backup transaction logs throughout the day and automatically restore them on the standby server at defined interval.