Sql server interview questions from microsoft

Having technical knowledge is not enough to get a job these days rather you should show your dedication, positive attitude, flexibility, and read to adopt new changes to your interviewers. Interviewers ask a lot of other non-technical questions to evaluate your qualities like I just mentioned above dedication, positive attitude, flexibility and read to adopt new changes.

Make sure to prepare their answers as well before going into the job market. I am giving the example of some of these questions. It works just like another mini operating system for SQL Server operations.

These records will be cleaned up by an internal process that is called the ghost cleanup process. This is a single-threaded background process that automatically runs on some intervals to check if any page is marked for ghost records or not and if it finds any, then it physically removes those ghosted records from pages.

No, renaming a database will not change its physical file names. You need to manually perform additional steps to change the database physical file names. Changing the physical database file name is multistep activity.

We must not perform such activities on regular basis and only do them on a need basis. Below are the high-level steps to change a physical file of a database.

We can use either the system dynamic management function sys. We need to pass database and file id details about that specific database file for which we are looking for IO statistics. Below is its example. 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.

What are the differences between local and global temporary tables? A CHECK constraint can be applied to a column in a table to limit the values that can be placed in a column. Check constraint is to enforce integrity. A sub-query is a query which can be nested inside a main query like Select, Update, Insert or Delete statements.

This can be used when expression is allowed. Properties of sub query can be defined as. What are the types of sub query? Scheduled tasks or jobs are used to automate processes that can be run on a scheduled time at a regular interval.

This scheduling of tasks helps to reduce human intervention during night time and feed can be done at a particular time. User can also order the tasks in which it has to be generated.

This function is used to return a non-null from more than one column in the arguments. FLOOR function is used to round up a non-integer value to the previous least integer. Example is given. Yes, we can check locks in the database. Triggers are used to execute a batch of SQL code when insert or update or delete commands are executed against a table. Triggers are automatically triggered or executed when the data is modified. It can be executed automatically on insert, delete and update operations.

What are the types of Triggers? The leaf node of a non-clustered index does not consist of the data pages. Instead, the leaf nodes contain index rows. Index id of non-clustered indexes is greater than 0.

The Recursive stored procedure is defined as a method of problem-solving wherein the solution arrives repetitively. SQL Server supports recursive stored procedure which calls by itself. It can nest up to 32 levels. It can be called by itself directly or indirectly. One-to-One relationship : It can be implemented as a single table and rarely as two tables with primary and foreign key relationships.

One to one relationship exists if an entity in one table has a link with only one entity on another table. A CHECK constraint is applied to a column in a table to limit the values that can be placed in a column.

It enforces integrity. After using the check constraint on the single column, we can only give some specific values for that particular column. Check constraint apply a condition for each column in the table.

It is one of the essential parts of the Microsoft's SQL server. Server agent's purpose is to implement the tasks easily with the scheduler engine which allows our jobs to run at scheduled date and time.

This function is used to return a non-null from more than one column in the arguments. Authentication mode is used for authentication of the user in SQL server, and it can be selected at the time of setup of the database engine. Window authentication mode : This authentication mode is used to connect through a Microsoft NT 4.

In Windows authentication server take computer's username and password for authentication purpose. SQL server authentication mode is disabled in this mode. In SQL server authentication mode a unique username and password are required for a particular database, as it will not depend on windows account. Microsoft SQL Server Profiler is a graphical user interface that allows system administrators to monitor events of the database engine. SQL server profiler trace monitor every event to a file.

SQL profiler can be used for real-time monitoring of data or also for future analysis of data. It is a background tool of Microsoft SQL Server, so it runs continuously in the background as a window service. SQL Server agent allows the database administrator to handles automated tasks and schedules jobs. It runs a window service so can start automatically when the system boots or you can start it manually. Scheduled tasks let you manage the tasks in an automated manner that runs on regular or predictable cycles.

You can schedule administrative tasks and also determine the order in which tasks will run. DBCC stands for database consistency checker. This command is used to check the consistency of the database. DBCC command help to review and monitoring the maintenance of tables, database, and for validation of operations done on the database, etc.

For example:. IF the user executes the above commands, a database snapshot is created through the database engine, and it continues in the consistent transactional state. After that, It runs the checks against stored database snapshot, and after the completion of the command, it dropped the snapshot. The session object is used to maintain the session of each user. If a user enters into an application, he gets a session id, and when he leaves application, then the session id is deleted.

If he enters again into the application, he gets a different session id, but for application object, once ad id is generated, it maintains the whole application. Value type and reference type may be similar regarding declaration syntax and usage, but their semantics are distinct. Value type and reference type differ with the memory area, where it stored. The value types are derived from System. Value Type while Reference type is derived from System Object.

Means value type stores a particular value but a reference type stores a reference or address to the object. Boxing : Implicit conversion of a value type integer, character, etc. In boxing process, a value type which generally stores on the stack is being allocated on the heap rather than the stack.

Boxing wraps a value inside the object which can be stored in the heap section. Unboxing : explicit conversion of that same reference type which is created by boxing process back to a value type is known as unboxing. In the unboxing process, a boxed value type is unboxed from the heap and allocated on the stack.

Both are used to send the data from client side to server side. Get method is not secured as it can be bookmarked but, post method is secured as it cannot be bookmarked.

Log shipping is the process of automating the backup of a database and transaction log file on a primary database server and then restoring them on a standby server. The primary purpose of log shipping is to increase database availability just like replication. On each of secondary database, log backups are applied particularly. Snapshot replication is the best method for replicating data that changes infrequently. Snapshot replication is the easiest way to maintain.

Transactional replication is generally used in the "server to server" environment. It is appropriate when you want incremental change propagated to the subscriber. It is generally used in the server to the client environment. Merge replication is appropriate when multiple subscribers might update the same data at the various time.

Hotfixes are small software patches that are applied to live systems. A hotfix is a single, cumulative package that includes one or more files used to address a problem in a software product. A patch is a program installed in the machine to rectify the problem occurred in the system and ensured the security of that system. The hotfix is a Kind of Patches provided by the Microsoft.

In Microsoft SQL Server, hotfixes are small patches designed to address specific issues, most commonly to freshly-discovered security holes. Hotfix response proactively against any bug. Trace flag in SQL server sets the specific characteristic of the server. The most common trace flags used with SQL Server are:.