All Dotnet Interview Questions 250-300

Question 251 – What are the Advantages of User Defined Functions?

·         UDF can be used in a Select, Where, or Case statement.

·         UDF can be used in join T-SQL Statements

·         It can act like a table, so the ability to break out complex logic into shorter and shorter code blocks
Additional benefit of making the code less complex and easier to write and maintain.

·         We can pass parameters to customize and gets the return based on our requirement

·         Simpler to invoke in sql statements than a stored procedure.

Question 252 – What are the DisAdvantages of User Defined Functions?

·         UDF that return non-deterministic values are not allowed to be called from inside UDF

·         GETDATE is an example of a non-deterministic function. Every time the function is called, a different value is returned.

·         Not every SQL statement or operation is valid within a function.

·         The following lists enumerate the valid and invalid function operations:

§  Valid:
Assignment statements

§  Control-flow statements

§  Variable declarations

§  SELECT statements that modify local variables

§  Cursor operations that fetch into local variables

§  INSERT, UPDATE, DELETE statement that act upon local table variables

§ 
Invalid:
Built-in, nondeterministic functions such as GetDate()

§  Statements that update, insert, or delete tables or views

§  Cursor fetch operations that return data to the client

Question 253 – What is the difference between a Stored Procedure and Functions?

Stored Procedure	Functions
Called independently, using the EXEC command	Called from within another SQL statement
May return a scalar value, a table value or Nothing	Always return a value (either a scalar value or a table)
Allow you to enhance application security by GRANT and Deny access	Here we can’t use this

Question 254 – What is a Trigger?

·         A trigger is a SQL procedure that initiates an action when an event (INSERT, DELETE,UPDATE) occurs.

·         Triggers can restrict access to specific data, perform logging, or audit data modifications.

·         A trigger cannot be called or executed.

Question 255 – What is a DML Trigger?

·         Fired when a Data Manipulation Language (DML) event takes place

·         AFTER - Executed after the action of the INSERT, UPDATE, or DELETE statement is performed.

·         INSTEAD OF - Executed in place of the usual triggering action. INSTEAD OF triggers can also be defined on views with one or more base tables, where they can extend the types of updates a view can support.

Question 256 – What is a DDL Trigger?

·         DDL triggers are new to SQL Server 2005.

·         This type of triggers, like regular triggers, fire stored procedures in response to an event.

·         They fire in response to a variety of Data Definition Language (DDL) events.

·         These events are specified by the T-SQL statements that are start with the keywords CREATE, ALTER, and DROP.

·         Certain stored procedures that perform DDL-like operations can also fire this.

·         These are used for administrative tasks like auditing and regulating database operations.

Question 257 – What is a CLR Trigger?

·         CLR triggers can be a DDL or DML one or can also be an AFTER or INSTEAD OF trigger.

·         Here we need to execute one or more methods written in managed codes that are members of an assembly created in the .Net framework.

·         Again, that assembly must be deployed in SQL Server 2005 using CREATE assembly statement.

·         The Microsoft.SqlServer.Server Namespace contains the required classes and enumerations for this objective.

·         Question 258 – What are the Advantages of Triggers?

·         Audit a table for security

·         Automatic updating of one or more tables whenever a DML/DDL statement is executed on that table.

·         Triggers can be used to enforce constraints. For e.g. : Any insert/update/ Delete statements should not be allowed on a table after office hours. For enforcing this constraint Triggers should be used.

·         Used to publish information about database events to subscribers. Events like Database startup or shutdown or a user even like User login in or user logoff.

Question 259 – What are the DisAdvantages of Triggers?

·         It is easy to view constraints, indexes, Sp's in database but triggers are difficult to view.

·         Execute invisible to application. They are not visible or can be traced in debugging code.

·         Hard to follow their logic as it they can be fired before or after the database insert/update happens.

·         Easy to forget about triggers and if there is no documentation it will be difficult to figure out.

·         Run every time when the db fields are updated; it is overhead on system, it makes system run slower.

Question 260 – What are Views in SQL Server?

·         A view is an "Virtual Table".

·         It does not contain any data directly, it is a set of query that are applied to one or more tables as object.

·         It can be thought of as a subset of a table.

·         It can be used for retrieving data, as well as updating or deleting rows.

·         The results of using a view are not permanently stored in the database.

·         A view serves as a security mechanism. This ensures that users are able to retrieve and modify only the data seen by them.

·         A view also serves as a mechanism to simplify query execution. Complex queries can be stored in the form as a view, and data from the view can be extracted using simple queries.

Question 261 – What are the Types of VIEW?

·         Standard Views - Combining data from one or more tables through a standard view. Focus on specific data and simplifying data manipulation.

·         Indexed Views - It has been computed and stored. You index a view by creating a unique clustered index on it. Indexed views dramatically improve the performance of some types of queries. Indexed views work best for queries that aggregate many rows. They are not well-suited for underlying data sets that are frequently updated.

·         Partitioned Views - Joins horizontally partitioned data from a set of member tables across one or more servers. This makes the data appear as if from one table. A view that joins member tables on the same instance of SQL Server is a local partitioned view.

Question 262 – How Views ensure security of data?

·         Specific rows of the tables.

·         Specific columns of the tables.

·         Specific rows and columns of the tables.

·         Rows fetched by using joins.

·         Statistical summary of data in a given tables.

·         Subsets of another view or a subset of views and tables.

Question 263 – List some of the common examples of views?

·         A subset of rows or columns of a base table.

·         A union of two or more tables.

·         A join of two or more tables.

·         A statistical summary of base tables.

·         A subset of another view, or some combination of views and base table.

Question 264 – What are the Limitations of views?

·         A view can be created only in the current database.

·         The name of a view must not be the same as that of the base table.

·         A view can be created only if there is a SELECT permission on its base table.

·         A SELECT INTO statement cannot be used in view declaration statement.

·         A trigger or an index cannot be defined on a view.

·         The CREATE VIEW statement cannot be combined with other SQL statements in a single batch.

Question 265 – What are Linked Servers in SQL Server?

·         A linked server configuration enables SQL Server to execute commands against OLE DB data sources on remote servers.

·         It is a concept in SQL by which we can add other SQL Server to a Group and query both the Server.

·         Stored Procedure sp_addlinkedserver, sp_addlinkedsrvlogin will be used add new Linked Server.

·         With a linked server, you can create very clean, easy to follow, SQL statements that allow remote data to be retrieved, joined and combined with local data.

Question 266 – What are the Advantages of Linked Servers?

·         Remote server access.

·         The ability to issue distributed queries, updates, commands, and transactions across the enterprise.

·         The ability to address diverse data sources similarly.

Question 267 – What is a Cursor in SQL Server?

It is a database object used by applications to manipulate data in a set on a row-by-row basis, instead of the typical SQL commands that operate on all the rows in the set at one time.

Question 268 – What are the steps to Create Cursor in SQL Server?

Below are the steps by step approach to create a Cursor.

·         Declare cursor

·         Open cursor

·         Fetch row from the cursor

·         Process fetched row

·         Close cursor

·         De-allocate cursor

Question 269 – What are the Types of Cursors in SQL Server?

There are 4 types of cursors in SQL Server and are as below.

·         Static

·         Dynamic

·         Forward only

·         Keyset Driven

Question 270 – What are the Cursor Optimization Tips available?

·         Avoid using SQL Server cursors, whenever possible.

·         Do not forget to close SQL Server cursor when its result set is not needed.

·         Do not forget to de-allocate SQL Server cursor when the cursors are not needed.

·         Reduce the number of records to process in the cursor.

·         Reduce the number of columns to process in the cursor.

·         Use READ ONLY cursors, whenever possible, instead of updatable cursors.

·         Try avoid using insensitive, static and keyset cursors, whenever possible.

·         Use FAST_FORWARD cursors, whenever possible.

·         Use FORWARD_ONLY cursors, if you need updatable cursor and the FETCH NEXT will be the only used fetch option.

 Question 271 – What are the Cursor Alternatives?

·         Set based logic

·         SQL Server Integration Services (SSIS) or Data Transformation Services (DTS)

·         WHILE loop

·         COALSCE

·         sp_MSforeachdb - Run The Same SQL Command Against All SQL Server Databases

·         sp_MSforeachtable

·         CASE expression - Using the CASE expression instead of dynamic SQL in SQL Server

Question 272 – What are the Limitations of Cursors?

·         Cursor requires a network roundtrip each time it fetches a record, thus consume network resources.

·         While data processing, it issues locks on part of the table, or on the whole table.

Question 273 – What is a SubQuery?

·         Subquery or Inner query or Nested query is a query in a query.

·         A Subquery is usually added in the WHERE Clause of the sql statement.

·         Used when we know how to search a value using SELECT statement, but don’t know the exact value.

·         Subqueries are an alternate way of returning data from multiple tables.

·         Subqueries can be used with the following sql statements along with the comparison operators like =, <, >, >=, <= etc. SELECT, INSERT, UPDATE, DELETE

Question 274 – What are the Properties of Subquery?

·         It must be enclosed in the parenthesis.

·         It must be put in the right hand of the comparison operator.

·         It cannot contain an ORDER-BY clause.

·         A query can contain more than one sub-query.

Question 275 – What are the Types of Subquery?

·         Single-row sub query, where the sub query returns only one row.

·         Multiple-row sub query, where the Subquery returns multiple rows.

·         Multiple column sub query, where the sub query returns multiple columns.

Question 276 – What is a Correlated Subquery?

·         A query is called correlated sub query when both the inner query and the outer query are interdependent.

·         For every row processed by the inner query, the outer query is processed as well.

·         The inner query depends on the outer query before it can be processed.

·         Correlated SubQuery can be said to be dependent on the outer query.

·         SELECT p.product_name FROM product p WHERE p.product_id = (SELECT o.product_id FROM order_items o WHERE o.product_id = p.product_id);

Question 277 – What are the Properties of Correlated Subquery?

·         Can nest many queries you want but it is recommended not to nest more than 16 Subqueries in oracle.

·         If a Subquery is not dependent on the outer query it is called a non-correlated Subquery.

Question 278 – What are the different Authentication options and Authentication Modes in SQL Server?

·         Authentication Types - Windows and SQL Server Authentication are the two types available.

·         Authentication Mode Types - Windows Authentication mode and Mixed Mode are two modes available

Question 279 – What is a Windows Authentication Mode?

·         It allows a user to connect through a Microsoft Windows NT® 4.0 or Windows® 2000 user account.

·         When using 'Windows authentication mode' you can only use Windows authentication to connect to SQL Server.

Question 280 – What is a Mixed Authentication Mode(Windows Authentication and SQL Server Authentication)?

·         Mixed Mode allows users to connect to an instance of SQL Server using either Windows Authentication or SQL Server Authentication.

·         When using 'Mixed mode' you can use either 'Windows authentication' or 'SQL Server authentication' to connect to SQL Server 20

Question 281 – How to Select an Authentication Mode in SQL Server?

·         Microsoft’s best practice recommendation is that you use Windows authentication mode whenever possible.

·         The main benefit is that the use of this mode allows you to centralize account administration for your entire enterprise in a single place: Active Directory.

·         This dramatically reduces the chances of error or oversight.

Question 282 – What is an Identity?

·         Identity (or AutoNumber) is a column that automatically generates numeric values.

·         A start and increment value can be set, but most DBA leave these at 1.

·         A GUID column also generates numbers; the value of this cannot be controlled.

·         Identity/GUID columns do not need to be indexed.

·         SELECT @@IDENTITY - returns the last IDENTITY value produced on a connection

·         SELECT IDENT_CURRENT('tablename') - returns the last IDENTITY value produced in a table

·         SELECT SCOPE_IDENTITY() - returns the last IDENTITY value produced on a connection

Question 283 – What are INNER JOINs in SQL?

·         This join returns rows when there is at least one match in both the tables.

·         Select * From Table1 Inner Join Table2 ON table1.ColumnName = Table2.ColumnName

Question 284 – What is a LEFT OUTER JOIN in SQL?

·         This join returns all the rows from the left table in conjunction with the matching rows from the right table.

·         If there are no columns matching in the right table, it returns NULL values.

·         Example - Select * From Table1 LEFT Join Table2 ON table1.ColumnName = Table2.ColumnName

Question 285 – What is a RIGHT OUTER JOIN in SQL?

·         This join returns all the rows from the right table in conjunction with the matching rows from the left table.

·         If there are no columns matching in the left table, it returns NULL values.

·         Example - Select * From Table1 RIGHT Join Table2 ON table1.ColumnName = Table2.ColumnName

Question 286 – What is a FULL OUTER JOIN in SQL?

·         This join combines left outer join and right after join. It returns row from either table when the conditions are met and returns null value when there is no match.

·         Example - Select * From Table1 FULL Join Table2 ON table1.ColumnName = Table2.ColumnName

Question 287 – What is an EQUI JOIN in SQL?

It is a specific type of comparator-based join, or the join that uses only equality(only =) comparisons in the join-predicate.

Question 288 – What is a CROSS JOIN in SQL?

·         It produces the Cartesian product of the tables involved in the join.

·         A cross join that does not have a WHERE clause

·         The size of a result set is the no of rows in the 1st table multiplied by the no of rows in the 2nd table.

·         Example: is when company wants to combine each product with a pricing table to analyze each product at each price.

Question 289 – What is a SELF JOIN in SQL?

Same table is specified twice with two different aliases in order to match the data within the same table.

Question 290 – What is a NATURAL JOIN in SQL?

·         It offers a further specialization of equi-joins.

·         The join predicate arises implicitly by comparing all columns in both tables that have the same column-name in the joined tables.

·         The resulting joined table contains only one column for each pair of equally-named columns.

·         Example - SELECT * FROM Vendor NATURAL JOIN advance

Question 291 – What is Log shipping?

·         It 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 1 server fails, the other 1 will have the same db and can be used 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.

·         This in effect keeps the two SQL Servers in "synch". Should the production server fail, all you have to do is point the users to the new server, and you are all set.

Question 292 – What is an Index?

·         An index is a physical structure containing pointers to the data.

·         Indices are created in an existing table to locate rows more quickly and efficiently.

·         It’s possible to create index on one or more columns of a table, and each index is given a name.

·         The users cannot see the indexes; they are just used to speed up queries.

·         Effective indexes are one of the best ways to improve performance in a database application.

·         A table scan happens when there is no index available to help a query.

·         Table scans are sometimes unavoidable, but on large tables, it has a terrific impact on performance.

·         A table can have one of the below indexes combinations

§  No Indexes

§  A clustered index

§  A clustered index and many non-clustered indexes

§  A non-clustered index

§  Many non-clustered indexes

Question 293 – How to Create an Effective Index?

·         Use the following guidelines to help create efficient indexes

·         Create indexes based on use.

·         Keep clustered index keys as small as possible.

·         Consider range data for clustered indexes.

·         Create an index on all foreign keys.

·         Create highly selective indexes.

·         Consider a covering index for often-used, high-impact queries.

·         Use multiple narrow indexes rather than a few wide indexes.

·         Create composite indexes with the most restrictive column first.

·         Consider indexes on columns used in WHERE, ORDER BY, GROUP BY, and DISTINCT clauses.

·         Remove unused indexes.

·         Use the Index Tuning Wizard.

Question 294 – What are the Types of Indexes available?

·         Clustered Index

·         Non Clustered index

·         Primary Key index

·         Unique index

·         Bitmap index

·         Hash index

·         Function Based index

·         B-Tree index

·         Virtual index

·         Composite index

·         Covering index

Question 295 – What is the difference between Primary Key and a Unique Key?

Primary Key	Unique Key
Primary key creates clustered index	Unique key creates non clustered index
Can have only 1 Primary key in a table	Can have many Unique key in a table
It cannot contain NULL values	Can have NULL values, even more than 1 null values

Question 296 – What is the difference between Delete and a Truncate?

Delete	Truncate
Does not reset the identity of the table	Resets identity of the table
Its DML Command	Its DDL Command
It can be rolled back	It cannot be rolled back
Its Slower (Removes row one by one)	Its Faster (Uses Fewer systems)
WHERE Condition can be used	WHERE Condition can’t be used
Records entry in transaction log for each deleted row	Removes the data by de-allocating the data pages
Trigger can be activated	Trigger can’t be activated

Question 297 – What is a Clustered Index?

A clustered index is a special type of index that reorders the way records in the table are physically stored. The leaf nodes of a clustered index contain the data pages. Clustered index is unique for any given table

Question 298 – What is a Non Clustered Index?

A nonclustered index is a special type of index in which the logical order of the index does not match the physical stored order of the rows on disk. The leaf node of a nonclustered index does not consist of the data pages. Instead, the leaf nodes contain index rows.

Question 299 – What is the difference between Clustered and Non Clustered Index?

Clustered Index	Non Clustered Index
can have only one clustered index on a table	we can have as many non-clustered indexes(255)
The leaf level of a clustered index is theactual data	non-clustered index the leaf level is actually apointer to the data in rows
The RowLocator in Clustered Index is the clusteredIndex key	The row locator in Non Clustered Index is a pointer to the row. ROW ID (RowLocator)= file identifier + page number + row number on the page
It doesn’t allow null values	It allow one null values
Assigned for primary key	Assigned for unique key
Cluster index exists on the physical level	They are not created on the physical level but at the logical level
It sorts the data at physical level	It does not sort the data at physical level
A clustered index requires no separate storage than the table storage	requires separate storage than the table storage to store the index information

Question 300 – What is BCP used in SQL?

·         BCP (Bulk Copy Program) is a command line utility by which you can import and export large amounts of data in and out of SQL SERVER database.

·         To import or export a set of columns WHERE clause can be used with bcp commands and all the conditions can be mentioned in the query to generate the set of rows you want to copy.