All Dotnet Interview Questions 300-350

Question 301 - What is the use of Having and Where Clause in SQL?

·         HAVING is just an additional filter to 'Where' clause.

·         First SQL server filters the rows using WHERE conditions and then performs group by on remaining rows and then filters the rows again with HAVING.

·         If a condition refers to an aggregate function, put that condition in the HAVING clause. Otherwise, use the WHERE clause.

·         You can't use HAVING unless you also use GROUP BY.

·         HAVING is typically used in a GROUP BY clause. When GROUP BY is not used, HAVING behaves like a WHERE clause.

Question 302 - What is the difference between Having and Where Clause in SQL?

Having	Where
It applies to summarized rows (summarized 'with GROUP BY)	It applies to rows
Having can used only with the select statement,typically used with group by clause	WHERE clause is used to impose condition onSELECT statement as well as single row function
Used after GROUP BY clause, when it is usedwithout group by it work like where clause	Used before GROUP BY clause

Question 303 - What is the Local Temporary Table?

·         They are created using same syntax as CREATE TABLE except table name is preceded by ‘#’ sign.

·         When table is preceded by single ‘#’ sign, it is defined as local temporary table and its scope is limited to session in which it is created.

Question 304 - What is the Global Temporary Table?

·         Syntax difference between global and local temporary table is of an extra ‘#’ sign. Global temporary tables are preceded with two ‘#’ (##) sign. Following is the definition.

·         In contrast of local temporary tables, global temporary tables are visible across entire instance.

·         Global temporary tables are dropped when the session that created it ends, and all other sessions have stopped referencing it.

Question 305 - What is the difference between @@Error and @@Rowcount ?

To get @@error and @@rowcount at the same time do both in same statement and store them in local variable. SELECT @RC = @@ROWCOUNT, @ER = @@ERROR

Question 306 - What is a Service Broker in SQL?

·         It is a unique feature in Microsoft SQL Server 2005 which is used to by internal or external processes to send and receive guaranteed, asynchronous messages by using extensions to Transact-SQL Data Manipulation Language (DML).

·         Service Broker not only brings asynchronous, queued messaging to database applications but significantly expands the state of the art for reliable messaging.

·         The key components of SQL Server Service broker are

·         Queue stores the messages for a particular service.

·         Dialog is a conversation between two services.

·         Conversation group is a group of related conversations.

·         Every conversation belongs to exactly one conversation group.

·         Activation specifies a sp that will handle messages destined for a particular service.

Question 307 - What is Database Mirroring in SQL?

·         Database mirroring involves two copies of a single database that typically reside on different computers.

·         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.

·         Database mirroring Increases data protection.

·         Database mirroring Increases availability of a database.

·         Database mirroring improves the availability of the production database during upgrades.

·         Database mirroring cannot be used on system databases.

Question 308 - What is Extended Stored Procedure in SQL?

·         An extended stored procedure is a function within a DLL that can be called from T-SQL, just the way we call normal stored procedures using the EXEC statement.

·         Question 309 – How to increase the Performance of a Query in SQL?

·         Know the performance and scalability characteristics of queries.

·         Write correctly formed queries.

·         Return only the rows and columns needed.

·         Avoid expensive operators such as NOT LIKE.

·         Avoid explicit or implicit functions in WHERE clauses.

·         Use locking and isolation level hints to minimize locking.

·         Use stored procedures or parameterized queries.

·         Minimize cursor use.

·         Avoid long actions in triggers.

·         Use temporary tables and table variables appropriately.

·         Limit query and index hints use.

·         Fully qualify database objects.

Question 310 - What is an Execution Plans in SQL?

·         It helps to check how the query runs background to fetch the data’s

·         Guidelines considered for execution plan

·         Evaluate the query execution plan.

·         Avoid table and index scans.

·         Evaluate hash joins.

·         Evaluate bookmarks.

·         Evaluate sorts and filters.

·         Compare actual versus estimated rows and executions.

Question 311 – What is Dead Lock?

·         A deadlock is a situation where in two transactions wait for each other to give up their respective locks.

·         When this happens, the SQL Server ends the deadlock by automatically choosing one and aborting the process, allowing the other process to continue.

·         The aborted transaction is rolled back and an error message is sent to the user of the aborted process.

·         SQL Server detects deadlocks and terminates one user's process.

·         Deadlocking can occur with locks, parallelism, threads, and application events.

·         Most frequent source of deadlocking is resource locking where the resources are table or index objects.

Question 312 – What is an example of Deadlock?

Transaction A attempts to update table 1 and subsequently read/update data from table 2, whereas transaction B attempts to update table 2 and subsequently read/update data from table 1. In such situations, transaction A holds locks that transaction B needs to complete its task and vice versa; neither transaction can complete until the other transaction releases locks.

Question 313 – How to Resolve Deadlock?

·         In SQL Server 2000, the Lock Monitor thread detects the deadlock.

·         It uses a periodic detection system, inspecting processes about every 5 seconds to determine if there are any deadlock cycles.

·         When it finds one, it automatically chooses one thread as the deadlock victim.

·         It then rolls back the victim thread's transaction, cancels its query, and returns error 1205 to its client.

·         The Lock Monitor generally chooses the least expensive transaction to roll back.

·         You can override this somewhat using SET DEADLOCK_PRIORITY to LOW for a session.

·         Whenever both threads have the same DEADLOCK_PRIORITY setting, the Lock Monitor will have to choose one of them as the victim.

Question 314 – What is Live Lock?

·         A livelock is one, where a request for an exclusive lock is repeatedly denied because a series of overlapping shared locks keeps interfering.

·         SQL Server detects the situation after four denials and refuses further shared locks.

·         A livelock also occurs when read transactions monopolize a table or page, forcing a write transaction to wait indefinitely.

·         This is different than deadlock as in deadlock both the processes wait on each other.

Question 315 – What is an example of Live Lock?

A human example of live lock would be two people who meet face-to-face in a corridor and each moves aside to let the other pass, but they end up moving from side to side without making any progress because they always move the same way at the same time and never cross each other. This is good example of live lock.

Question 316 – What are the options available to Move Database between servers?

There are lots of options available; you have to choose your option depending upon your requirements. Some of the options you have are:

·         BACKUP/RESTORE,

·         Detaching and attaching databases,

·         Replication,

·         DTS,

·         BCP,

·         Log shipping,

·         INSERT...SELECT,

·         SELECT...INTO,

·         Creating INSERT scripts to generate data.

Question 317 – What is Replication?

·         SQL replication allows not only for copying data between databases, but also copying any database objects as well. Essentially replication performs synchronization between databases. By utilizing SQL replication, you can distribute data to as many remote network locations you need, and you can do that over different types of networks including LAN, WAN, and Internet to name a few.

·         Database replication can be done in at least three different ways:

·         Snapshot replication: Data on one server is simply copied to another server, or to another database on the same server.

·         Merging replication: Data from two or more databases is combined into a single database.

·         Transactional replication: Users receive full initial copies of the database and then receive periodic updates as data changes.

Question 318 – What is Union?

·         UNION only selects distinct values.

·         When using the UNION command all selected columns need to be of the same data type

·         A UNION statement effectively does a SELECT DISTINCT on the results set.

Question 319 – What is Union ALL?

·         UNION ALL selects all values.

·         Union all will not eliminate duplicate rows, instead it just pulls all rows from all tables fitting your query specifics and combines them into a table.

·         If you know that all the records returned are unique, use UNION ALL instead, it gives faster results.

Question 320 – List some of the DBCC (Database Console Commands for SQL Server) Commands?

·         DBCC CACHESTATS displays information about the objects currently in the buffer cache, such as hit rates, compiled objects and plans, etc.
DBCC DROPCLEANBUFFERS: Use this command to remove all the data from SQL Server's data cache (buffer) between performance tests to ensure fair testing. Keep in mind that this command only removes clean buffers, not dirty buffers.

·         DBCC ERRORLOG: If you rarely restart the mssqlserver service, you may find that your server log gets very large and takes a long ti

Question 321 - What is a Full Database Backup type in SQL?

With this backup you are backing up the entire database and will be able to restore the entire database from just this backup.

Question 322 - What is a Differential Database Backup type in SQL?

·         A differential backup will record all of the data that has changed since the last database backup.

·         You must have a database backup in place to use a starting point for your differential backup.

·         Differential backups have several limitations including the following:

§  They do not provide point-in-time restore capabilities

§  They may only be restored after a complete database backup is restored

§  They may not be performed on the master database

Question 323 - What is a Transactional Log Database Backup type in SQL?

Transaction log backups record all transactions that have been recorded against the database since the last transaction log backup.

Question 324 - What is a Log Database Backup type in SQL?

·         File backups involve backing up individual files within a database.

·         This can be the quickest way to restore, but it also has a lot of overhead associated with it.

·         You must keep track of your file backups as well as use these file backups in conjunction with transaction log backups.

Question 325 - What are the advantages of NoLock in SQL?

Applying nolock in select statement will increase concurrency and performance in fetching.

Question 326 - What are the disadvantages of NoLock in SQL?

It doesn’t consider the transaction scenario, even if the transaction is under process not yet committed or rollback it will fetch the current record.

Question 327 - What is GRANT command in SQL?

·         It is a command used to provide access or privileges on the database objects to the users.

·         Syntax - GRANT privilege_name ON object_name TO {user_name |PUBLIC |role_name}

·         Example - GRANT SELECT ON Table1 TO User1

Question 328 - What is REVOKE command in SQL?

·         It removes user access rights or privileges to the database objects.

·         Syntax - REVOKE privilege_name ON object_name FROM {user_name |PUBLIC |role_name}

·         Example - REVOKE SELECT ON Table1 TO User1

Question 329 - What is Privileges command in SQL?

·         It defines the access rights provided to a user on a database object. There are of two types.

·         System privileges - This allows the user to CREATE, ALTER, or DROP database objects.

·         Object privileges - This allows the user to EXECUTE, SELECT, INSERT, UPDATE, or DELETE data.

Question 330 - What is Cascade command in SQL?

·         While dropping a parent table , it is required to drop the corresponding child table also.

·         Parent table cannot be deleted if there is any child for it. So the cascaded drop or delete helps in deleting the child and then the parent.

·         When you give a "drop table" command, it will not drop the table that has dependencies unless you include the "cascade" command in the statement.

Question 331 – What is the use of RESTRICT Keyword in SQL?

·         It specifies that table should not be dropped if any dependencies (i.e. triggers, stored procedure, primary key, foreign key etc) exist.

·         Therefore, if there are dependencies then error is generated and the object is not dropped.

Question 332 – What is the use of DML in SQL?

It is abbreviation of Data Manipulation Language. It is used to retrieve, store, modify, delete, insert and update in database. Examples: SELECT, INSERT, UPDATE, DELETE, MERGE, CALL, EXPLAIN PLAN, LOCK TABLE

Question 333 – What is the use of DDL in SQL?

It is abbreviation of Data Definition Language. It is used to create and modify the structure of database objects in database. Examples: CREATE, ALTER, DROP, TRUNCATE, COMMENT, RENAME

Question 334 – What is the use of DCL in SQL?

It is abbreviation of Data Control Language. It is used to create roles, permissions, and referential integrity as well it is used to control access to database by securing it. Examples: GRANT, REVOKE statements

Question 335 – What is the use of TCL in SQL?

It is abbreviation of Transactional Control Language. It is used to manage different transactions occurring within a database. Examples: COMMIT, ROLLBACK, SAVEPOINT,  SET TRANSACTION statements.

Question 336 – What is the use of Wildcards in SQL?

·         It can substitute for one or more characters when searching for data in a database.

·         SQL wildcards must be used with the SQL LIKE operator.

·         With SQL, the following wildcards can be used %, -, [charlist] etc.

Question 337 – What is the use of Aggregate functions?

·         It perform a calculation on a set of values and return a single value.

·         Except for COUNT, aggregate functions ignore null values.

·         Aggregate functions are frequently used with the GROUP BY clause of the SELECT statement.

·         Aggregate functions can be used as expressions only in the following:

·         The select list of a SELECT statement (either a SubQuery or an outer query).

·         A COMPUTE or COMPUTE BY clause.

·         A HAVING clause.

Question 338 – What is the use of ROLLUP in SQL?

·         ROLLUP clause is used to do aggregate operation on multiple levels in hierarchy.

·         It work with the "Group By " clause its main functioning comes into existence when we use Group by.
We can get sub-total of row by using the Rollup function.

·         When result is return by Group By class first row display grand total or we can say that the main total.

·         Syntax:- select firstcolumn, secondcolumn, sum(thirdcolumn) from tablename group by firstcolumn, secondcolumn with rollup order by firstcolumn.

Question 339 – What is the use of CUBE in SQL?

·         The CUBE operator generates a result set that is a multidimensional cube.

·         It is a result set that contains a cross tabulation of all the possible combinations of the dimensions.

·         The CUBE operator is specified in the GROUP BY clause of a SELECT statement.

·         The select list contains the dimension columns and aggregate function expressions.

·         The GROUP BY specifies the dimension columns and the keywords WITH CUBE.

Question 340 – What are the Differences between CUBE and ROLLUP?

·         CUBE generates a result set that shows aggregates for all combinations of values in the selected columns.

·         ROLLUP generates a result set that shows aggregates for a hierarchy of values in the selected columns.

Question 341 – What is the use of CUBE Operator in SQL?

The CUBE Operator is useful in generating reports that contain subtotals and totals. There are extensions of the GROUP BY clause.

Question 342 – What is the Difference between ROLLUP and COMPUTE?

·         ROLLUP returns a single result set while COMPUTE BY returns multiple result sets that increase the complexity of application code.

·         ROLLUP can be used in a server cursor while COMPUTE BY cannot.

·         The query optimizer can sometimes generate more efficient execution plans for ROLLUP than it can for COMPUTE BY.

Question 343 – What is the use of COMPUTE in SQL?

·         The optional BY keyword. This calculates the specified row aggregate on a per column basis.

·         A row aggregate function name. This includes SUM, AVG, MIN, MAX, or COUNT.

·         A column upon which to perform the row aggregate function.

Question 344 – What is the use of COMPUTE BY in SQL?

·         It allows you to see both detail and summary rows with one SELECT statement.

·         You can calculate summary values for subgroups, or a summary value for the whole result set.

Question 345 – What is the use of With TIES in SQL?

·         The SELECT TOP N query always return exactly N records, and arbitrarily drops any record that have the same value as the last record in the group.

·         The SELECT TOP N WITH TIES query always return N records with any record having the same value as the last record.

·         The TOP N PERCENT clause also do the same WITH TIES and Without TIES

Question 346 – What is the use of ALL & ANY operator in SQL?

·         Comparison operators that introduce a SubQuery can be modified by the keywords ALL or ANY
Using the > comparison operator as an example, >ALL means greater than every value, In other words, it means greater than the maximum value.

·         For example, >ALL (1, 2, 3) means greater than 3. >ANY means greater than at least one value, that is, greater than the minimum. So >ANY (1, 2, 3) means greater than 1.

·         Similarly, >ANY means that for a row to satisfy the condition specified in the outer query, the value in the column that introduces the SubQuery must be greater than at least one of the values in the list of values returned by the SubQuery

Question 347 – What is the use of Master Database in SQL?

This database holds information for all databases located on the SQL Server instance. SQL Server cannot start without a functioning master database.
Question 348 – What is the use of MSDB Database in SQL?This database stores information regarding database backups, SQL Agent information, DTS packages, SQL Server jobs, and some replication information such as for log shipping.

Question 349 – What is the use of TEMPDB Database in SQL?This holds temporary objects such as global and local temporary tables and stored procedures.

Question 350 – What is the use of MODEL Database in SQL?This is essentially a template database used in the creation of any new user database created in the instance.