Interview Questions.

Question 1. What Is Cursor?

Answer :

Cursor is a database item utilized by applications to control data in a hard and fast on a row-by way of-row foundation, instead of the everyday SQL commands that function on all of the rows inside the set at one time.

In order to paintings with a cursor we need to perform some steps inside the following order:
• Declare cursor
• Open cursor
• Fetch row from the cursor
• Process fetched row
• Close cursor
• Deallocate cursor

Question 2. What Is Collation?

Answer :

Collation refers to a set of regulations that determine how facts is sorted and as compared. Character records is sorted the use of rules that outline the perfect person series, with options for specifying case sensitivity, accent marks, individual sorts and person width.

SQL Server 2000 Interview Questions
Question three. What Is Difference Between Function And Stored Procedure?

Answer :

UDF can be used within the SQL statements everywhere in the WHERE/HAVING/SELECT section where as Stored strategies can't be. UDFs that return tables may be treated as another rowset. This may be utilized in JOINs with different tables. Inline UDF’s can be notion of as perspectives that take parameters and may be utilized in JOINs and different Rowset operations.

Question 4. What Is Sub-query? Explain Properties Of Sub-question?

Answer :

Sub-queries are regularly referred to as sub-selects, as they allow a SELECT announcement to be done arbitrarily within the frame of another SQL statement. A sub-question is completed by enclosing it in a fixed of parentheses. Sub-queries are usually used to go back a unmarried row as an atomic value, though they will be used to evaluate values towards more than one rows with the IN keyword.

A subquery is a SELECT statement that is nested within any other T-SQL statement. A subquery SELECT announcement if finished independently of the T-SQL statement, in which it is nested, will return a resultset. Meaning a subquery SELECT announcement can standalone and isn't always trusted the statement wherein it's miles nested. A subquery SELECT assertion can return any variety of values, and can be located in, the column listing of a SELECT declaration, FROM, GROUP BY, HAVING, and/or ORDER BY clauses of a T-SQL announcement. A Subquery also can be used as a parameter to a function name. Basically a subquery can be used everywhere an expression can be used.

SQL Server 2008 Tutorial
Question five. What Are Different Types Of Join?

Answer :

Cross Join
A go be a part of that does not have a WHERE clause produces the Cartesian made of the tables worried in the be a part of. The length of a Cartesian product result set is the variety of rows in the first desk expanded through the wide variety of rows inside the 2d table. The common example is when company wants to combine each product with a pricing table to analyze every product at every rate.

Inner Join
A join that presentations only the rows which have a suit in each joined tables is called internal Join. This is the default type of join within the Query and View Designer.

Outer Join
A be part of that consists of rows despite the fact that they do no longer have associated rows in the joined desk is an Outer Join. You can create three distinctive outer be a part of to specify the unmatched rows to be covered:
• Left Outer Join: In Left Outer Join all rows in the first-named desk i.E. “left” table, which appears leftmost inside the JOIN clause are covered. Unmatched rows inside the proper table do now not seem.
• Right Outer Join: In Right Outer Join all rows within the 2d-named desk i.E. “right” desk, which seems rightmost within the JOIN clause are protected. Unmatched rows inside the left desk aren't protected.
• Full Outer Join: In Full Outer Join all rows in all joined tables are included, whether they're matched or now not.

Self Join
This is a selected case whilst one desk joins to itself, with one or  aliases to avoid confusion. A self be part of may be of any type, as long as the joined tables are the equal. A self be a part of is rather particular in that it involves a courting with simplest one desk. The not unusual example is when enterprise has a hierarchal reporting shape wherein one member of personnel reports to some other. Self Join may be Outer Join or Inner Join.

MSBI Interview Questions
Question 6. What Are Primary Keys And Foreign Keys?

Answer :

Primary keys are the unique identifiers for each row. They must include unique values and can not be null. Due to their significance in relational databases, Primary keys are the maximum fundamental of all keys and constraints. A desk could have only one Primary key.

Foreign keys are both a method of making sure data integrity and a manifestation of the relationship among tables.

Question 7. What Is User Defined Functions? What Kind Of User-defined Functions Can Be Created?

Answer :

User-Defined Functions allow defining its own T-SQL functions that may take delivery of 0 or more parameters and go back a single scalar records price or a desk facts kind.

Different Kinds of User-Defined Functions created are:

Scalar User-Defined Function
A Scalar user-defined feature returns one of the scalar facts types. Text, ntext, photograph and timestamp facts types are not supported. These are the type of user-defined features that most builders are used to in different programming languages. You pass in zero to many parameters and you get a go back value.

Inline Table-Value User-Defined Function
An Inline Table-Value user-defined function returns a table records kind and is an first rate alternative to a view because the consumer-defined feature can pass parameters into a T-SQL select command and in essence provide us with a parameterized, non-updateable view of the underlying tables.

Multi-statement Table-Value User-Defined Function
A Multi-Statement Table-Value person-defined characteristic returns a desk and is also an extraordinary opportunity to a view as the feature can support multiple T-SQL statements to construct the final result wherein the view is restricted to a single SELECT declaration. Also, the potential to bypass parameters into a TSQL select command or a group of them gives us the capability to in essence create a parameterized, non-updateable view of the statistics within the underlying tables. Within the create feature command you have to define the table shape that is being again. After growing this kind of person-defined feature, It can be used within the FROM clause of a T-SQL command not like the behavior located when using a saved system which also can go back document sets.

Microsoft Entity Framework Tutorial SQL Server 2005 Interview Questions
Question 8. What Is Identity?

Answer :

Identity (or AutoNumber) is a column that automatically generates numeric values. A begin and increment cost can be set, but most DBA go away those at 1. A GUID column also generates numbers; the cost of this can not be managed. Identity/GUID columns do now not need to be listed.

Question nine. What Is Datawarehousing?

Answer :

• Subject-oriented, meaning that the data inside the database is prepared so that all the facts factors relating to the identical actual-global occasion or item are linked collectively;
• Time-variant, meaning that the changes to the information inside the database are tracked and recorded so that reviews may be produced displaying adjustments over time;
• Non-volatile, meaning that data within the database is in no way over-written or deleted, once committed, the records is static, study-best, however retained for future reporting.
• Integrated, that means that the database consists of records from maximum or all of an organisation’s operational programs, and that this information is made steady.

SSIS(SQL Server Integration Services) Interview Questions
Question 10. What Is Rdbms?

Answer :

Relational Data Base Management Systems (RDBMS) are database management systems that hold facts records and indices in tables. Relationships can be created and maintained throughout and many of the information and tables. In a relational database, relationships between data items are expressed by means of tables. Interdependencies among these tables are expressed by data values instead of through tips. This lets in a high diploma of information independence. An RDBMS has the functionality to recombine the facts items from exceptional documents, supplying powerful equipment for records usage.

LINQ Tutorial
Question 11. What Are The Properties Of The Relational Tables?

Answer :

Relational tables have six residences:
• Values are atomic.
• Column values are of the equal kind.
• Each row is precise.
• The series of columns is insignificant.
• The sequence of rows is insignificant.
• Each column must have a unique name.

SSRS(SQL Server Reporting Services) Interview Questions
Question 12. What Is Normalization?

Answer :

Database normalization is a information layout and organisation system carried out to statistics systems based on policies that help building relational databases. In relational database design, the method of organizing facts to decrease redundancy is called normalization. Normalization generally involves dividing a database into two or greater tables and defining relationships among the tables. The objective is to isolate records in order that additions, deletions, and changes of a discipline may be made in just one table after which propagated through the rest of the database thru the defined relationships.

SQL Server 2000 Interview Questions
Question thirteen. What Are Different Normalization Forms?

Answer :

1NF: Eliminate Repeating Groups
Make a separate table for every set of associated attributes, and supply every table a number one key. Each area incorporates at maximum one fee from its characteristic area.

2NF: Eliminate Redundant Data
If an characteristic depends on most effective a part of a multi-valued key, get rid of it to a separate table.

3NF: Eliminate Columns Not Dependent On Key
If attributes do no longer contribute to a description of the key, eliminate them to a separate desk. All attributes ought to be without delay depending on the primary key.

BCNF: Boyce-Codd Normal Form
If there are non-trivial dependencies between candidate key attributes, separate them out into awesome tables.

4NF: Isolate Independent Multiple Relationships
No table may additionally contain  or more 1:n or n:m relationships that are not directly associated.

5NF: Isolate Semantically Related Multiple Relationships
There may be practical constrains on records that justify isolating logically associated many-to-many relationships.

ONF: Optimal Normal Form
A model constrained to simplest easy (elemental) facts, as expressed in Object Role Model notation.

DKNF: Domain-Key Normal Form
A version loose from all modification anomalies is stated to be in DKNF.

Remember, these normalization tips are cumulative. For a database to be in 3NF, it should first satisfy all the criteria of a 2NF and 1NF database.

Question 14. What Is De-normalization?

Answer :

De-normalization is the method of trying to optimize the overall performance of a database with the aid of adding redundant records. It is once in a while vital because modern-day DBMSs put into effect the relational model poorly. A actual relational DBMS would permit for a totally normalized database at the logical level, even as providing physical garage of records this is tuned for high overall performance. De-normalization is a method to transport from better to lower normal varieties of database modeling with a view to speed up database get admission to.

Question 15. What Is Stored Procedure?

Answer :

A saved manner is a named group of SQL statements that have been previously created and saved in the server database. Stored processes take delivery of input parameters in order that a unmarried system may be used over the community through numerous clients the usage of unique input data. And when the procedure is modified, all customers robotically get the new version. Stored tactics reduce community traffic and enhance performance. Stored approaches may be used to assist make certain the integrity of the database.
E.G. Sp_helpdb, sp_renamedb, sp_depends and so forth.

Microsoft Entity Framework Interview Questions
Question sixteen. What Is Trigger?

Answer :

A trigger is a SQL technique that initiates an movement whilst an occasion (INSERT, DELETE or UPDATE) takes place. Triggers are stored in and controlled by using the DBMS. Triggers are used to keep the referential integrity of records through changing the information in a scientific fashion. A cause can not be known as or achieved; DBMS automatically fires the trigger because of a facts change to the related table. Triggers can be viewed as much like saved approaches in that each consist of procedural common sense this is stored at the database stage. Stored procedures, however, aren't event-pressure and aren't connected to a selected table as triggers are Stored approaches are explicitly done with the aid of invoking a CALL to the procedure whilst triggers are implicitly achieved. In addition, triggers also can execute saved procedures.

Nested Trigger: A cause can also include INSERT, UPDATE and DELETE logic within itself, so when the trigger is fired due to statistics change it can also purpose any other facts amendment, thereby firing every other cause. A cause that incorporates statistics modification good judgment inside itself is known as a nested trigger.

Question 17. What Is View?

Answer :

A simple view can be idea of as a subset of a desk. It can be used for retrieving statistics, in addition to updating or deleting rows. Rows up to date or deleted within the view are updated or deleted inside the desk the view became created with. It should also be cited that as records inside the original desk modifications, so does information within the view, as views are the manner to look at part of the authentic desk. The results of the usage of a view aren't completely stored within the database. The statistics accessed through a view is surely constructed the usage of standard T-SQL pick command and might come from one to many distinctive base tables or maybe other views.

LINQ Interview Questions
Question 18. What Is Index?

Answer :

An index is a physical structure containing hints to the facts. Indices are created in an current desk to locate rows extra quick and efficiently. It is feasible to create an index on one or greater columns of a desk, and each index is given a call. The customers cannot see the indexes; they may be just used to speed up queries. Effective indexes are one of the excellent ways to improve overall performance in a database application. A table scan occurs when there is no index available to assist a query. In a desk experiment SQL Server examines every row within the table to satisfy the question consequences. Table scans are once in a while unavoidable, but on massive tables, scans have a remarkable impact on overall performance.

MSBI Interview Questions
Question 19. What Is A Linked Server?

Answer :

Linked Servers is a concept in SQL Server with the aid of which we will add other SQL Server to a Group and query each the SQL Server dbs the usage of T-SQL Statements. With a related server, you could create very smooth, easy to comply with, SQL statements that allow faraway facts to be retrieved, joined and blended with local facts. Stored Procedure sp_addlinkedserver, sp_addlinkedsrvlogin can be used add new Linked Server.

Question 20. Which Tcp/ip Port Does Sql Server Run On? How Can It Be Changed?

Answer :

SQL Server runs on port 1433. It may be changed from the Network Utility TCP/IP properties -> Port variety, both on consumer and the server.

SQL Server Analysis Services (SSAS) Interview Questions
Question 21. What Are The Difference Between Clustered And A Non-clustered Index?

Answer :

A clustered index is a special sort of index that reorders the way facts in the table are physically stored. Therefore table will have most effective one clustered index. The leaf nodes of a clustered index incorporate the facts pages.

A non clustered index is a unique type of index wherein the logical order of the index does no longer healthy the bodily stored order of the rows on disk. The leaf node of a non clustered index does no longer encompass the statistics pages. Instead, the leaf nodes include index rows.

Question 22. What Are The Different Index Configurations A Table Can Have?

Answer :

A desk may have one of the following index configurations:
• No indexes
• A clustered index
• A clustered index and many nonclustered indexes
• A nonclustered index
• Many nonclustered indexes

Question 23. What Are Different Types Of Collation Sensitivity?

Answer :

Case sensitivity – A and a, B and b, etc.
Accent sensitivity – a and á, o and ó, and many others.
Kana Sensitivity – When Japanese kana characters Hiragana and Katakana are handled in a different way, it is called Kana touchy.
Width sensitivity – A single-byte person (half-width) and the identical person represented as a double-byte character (complete-width) are treated in another way than it's miles width sensitive.

Sql Server Dba Interview Questions
Question 24. What Is Oltp (online Transaction Processing)?

Answer :

In OLTP – on-line transaction processing structures relational database design use the field of records modeling and usually observe the Codd regulations of records normalization which will make certain absolute facts integrity. Using these guidelines complicated information is broken down into its most simple structures (a table) in which all of the man or woman atomic stage factors relate to every different and satisfy the normalization policies.

SQL Server 2005 Interview Questions
Question 25. What's The Difference Between A Primary Key And A Unique Key?

Answer :

Both number one key and precise key enforces strong point of the column on which they may be defined. But via default primary key creates a clustered index on the column, where are particular creates a nonclustered index by default. Another fundamental difference is that, primary key doesn’t allow NULLs, but precise key permits one NULL best.

Question 26. What Is Difference Between Delete & Truncate Commands?

Answer :

Delete command eliminates the rows from a table based at the condition that we provide with a WHERE clause. Truncate will truely dispose of all the rows from a table and there might be no information in the desk after we run the truncate command.

TRUNCATE
• TRUNCATE is faster and makes use of fewer gadget and transaction log sources than DELETE.
• TRUNCATE eliminates the statistics by means of deallocating the statistics pages used to shop the table’s statistics, and best the web page deallocations are recorded inside the transaction log.
• TRUNCATE gets rid of all rows from a desk, however the table shape, its columns, constraints, indexes and so on, stays. The counter used by an identity for brand spanking new rows is reset to the seed for the column.
• You cannot use TRUNCATE TABLE on a desk referenced through a FOREIGN KEY constraint. Because TRUNCATE TABLE is not logged, it can't activate a trigger.
• TRUNCATE can not be rolled again.
• TRUNCATE is DDL Command.
• TRUNCATE Resets identification of the table

DELETE
• DELETE eliminates rows one after the other and records an entry within the transaction log for every deleted row.
• If you need to preserve the identity counter, use DELETE as an alternative. If you need to cast off desk definition and its information, use the DROP TABLE statement.
• DELETE Can be used with or without a WHERE clause
• DELETE Activates Triggers.
• DELETE may be rolled lower back.
• DELETE is DML Command.
• DELETE does no longer reset identity of the table.

Question 27. When Is The Use Of Update_statistics Command?

Answer :

This command is essentially used whilst a big processing of information has came about. If a large quantity of deletions any change or Bulk Copy into the tables has befell, it has to update the indexes to take these adjustments under consideration. UPDATE_STATISTICS updates the indexes on those tables as a result.

SSIS(SQL Server Integration Services) Interview Questions
Question 28. What Is The Difference Between A Having Clause And A Where Clause?

Answer :

They specify a seek situation for a collection or an combination. But the difference is that HAVING can be used most effective with the SELECT declaration. HAVING is commonly used in a GROUP BY clause. When GROUP BY is not used, HAVING behaves like a WHERE clause. Having Clause is essentially used handiest with the GROUP BY function in a query while WHERE Clause is implemented to each row before they are a part of the GROUP BY feature in a question.

Question 29. What Are The Properties And Different Types Of Sub-queries?

Answer :

Properties of Sub-Query
• A sub-question should be enclosed inside the parenthesis.
• A sub-query ought to be placed within the right hand of the comparison operator, and
• A sub-query can't comprise an ORDER-BY clause.
• A query can include a couple of sub-query.

Types of Sub-query
• Single-row sub-query, where the sub-question returns most effective one row.
• Multiple-row sub-question, wherein the sub-question returns more than one rows, and
• Multiple column sub-question, where the sub-question returns more than one columns

Question 30. What Is Sql Profiler?

Answer :

SQL Profiler is a graphical device that allows gadget directors to display activities in an example of Microsoft SQL Server. You can seize and store statistics approximately every event to a report or SQL Server desk to investigate later. For example, you can monitor a manufacturing environment to look which saved methods are hampering performances through executing too slowly.

Use SQL Profiler to monitor most effective the occasions in that you are fascinated. If lines are becoming too huge, you can clear out them based totally at the facts you need, so that handiest a subset of the occasion records is gathered. Monitoring too many events provides overhead to the server and the monitoring procedure and may purpose the trace record or hint table to grow very massive, specially while the monitoring process takes vicinity over a protracted time frame.

Question 31. What Are The Authentication Modes In Sql Server? How Can It Be Changed?

Answer :

Windows mode and Mixed Mode – SQL & Windows.
To change authentication mode in SQL Server click on Start, Programs, Microsoft SQL Server and click SQL Enterprise Manager to run SQL Enterprise Manager from the Microsoft SQL Server application group. Select the server then from the Tools menu pick SQL Server Configuration Properties, and pick out the Security page.

Question 32. Which Command Using Query Analyzer Will Give You The Version Of Sql Server And Operating System?

Answer :

SELECT SERVERPROPERTY ('productversion'), SERVERPROPERTY('productlevel'), SERVERPROPERTY ('version').

Question 33. What Is Sql Server Agent?

Answer :

SQL Server agent performs an crucial role in the every day tasks of a database administrator (DBA). It is often overlooked as one of the most important equipment for SQL Server control. Its purpose is to ease the implementation of duties for the DBA, with its complete-characteristic scheduling engine, which lets in you to agenda your personal jobs and scripts.

SSRS(SQL Server Reporting Services) Interview Questions
Question 34. Can A Stored Procedure Call Itself Or Recursive Stored Procedure? How Much Level Sp Nesting Is Possible?

Answer :

Yes. Because Transact-SQL helps recursion, you could write stored tactics that call themselves. Recursion can be described as a technique of trouble fixing in which the answer is arrived at via repetitively making use of it to subsets of the trouble. A not unusual software of recursive good judgment is to perform numeric computations that lend themselves to repetitive assessment by means of the same processing steps. Stored strategies are nested when one stored procedure calls another or executes controlled code with the aid of referencing a CLR recurring, type, or aggregate. You can nest stored procedures and managed code references up to 32 ranges.

Question 35. What Is Log Shipping?

Answer :

Log transport is the method of automating the backup of database and transaction log files on a manufacturing SQL server, after which restoring them onto a standby server. Enterprise Editions only helps log transport. In log transport the transactional log document from one server is routinely updated into the backup database on the alternative server. If one server fails, the other server may have the same db and can be used this because the Disaster Recovery plan. The key feature of log delivery is that it's going to robotically backup transaction logs all through the day and robotically restore them on the standby server at described c programming language.

Question 36. Name three Ways To Get An Accurate Count Of The Number Of Records In A Table?

Answer :

SELECT * FROM table1
SELECT COUNT(*) FROM table1
SELECT rows FROM sysindexes WHERE identity = OBJECT_ID(table1) AND indid < 2.

Microsoft Entity Framework Interview Questions
Question 37. What Does It Mean To Have Quoted_identifier On? What Are The Implications Of Having It Off?

Answer :

When SET QUOTED_IDENTIFIER is ON, identifiers may be delimited via double citation marks, and literals should be delimited by way of unmarried quotation marks. When SET QUOTED_IDENTIFIER is OFF, identifiers cannot be quoted and ought to comply with all Transact-SQL guidelines for identifiers.

Question 38. What Is The Difference Between A Local And A Global Temporary Table?

Answer :

A neighborhood temporary desk exists best at some stage in a connection or, if defined inside a compound assertion, at some point of the compound announcement.

A global transient table remains within the database permanently, however the rows exist handiest within a given connection. When connection is closed, the information in the global temporary desk disappears. However, the table definition stays with the database for access when database is opened next time.

Question 39. What Is The Stuff Function And How Does It Differ From The Replace Function?

Answer :

STUFF feature is used to overwrite existing characters. Using this syntax, STUFF (string_expression, begin, duration, replacement_characters), string_expression is the string so that it will have characters substituted, start is the beginning position, period is the wide variety of characters inside the string that are substituted, and replacement_characters are the brand new characters interjected into the string. REPLACE feature to update current characters of all occurrences. Using the syntax REPLACE (string_expression, search_string, replacement_string), in which every occurrence of search_string found within the string_expression will get replaced with replacement_string.

Question forty. What Is Primary Key?

Answer :

A PRIMARY KEY constraint is a unique identifier for a row inside a database desk. Every table have to have a number one key constraint to uniquely pick out every row and most effective one primary key constraint may be created for each table. The number one key constraints are used to put into effect entity integrity.

LINQ Interview Questions
Question forty one. What Is Unique Key Constraint?

Answer :

A UNIQUE constraint enforces the distinctiveness of the values in a hard and fast of columns, so no reproduction values are entered. The particular key constraints are used to enforce entity integrity as the primary key constraints.

Question forty two. What Is Foreign Key?

Answer :

A FOREIGN KEY constraint prevents any moves that might wreck links between tables with the corresponding statistics values. A foreign key in a single table points to a number one key in every other table. Foreign keys prevent moves that would go away rows with overseas key values when there are no number one keys with that price. The foreign key constraints are used to enforce referential integrity.

SQL Server Analysis Services (SSAS) Interview Questions
Question 43. What Is Check Constraint?

Answer :

A CHECK constraint is used to limit the values that can be positioned in a column. The take a look at constraints are used to enforce area integrity.

Question forty four. What Is Not Null Constraint?

Answer :

A NOT NULL constraint enforces that the column will not accept null values. The not null constraints are used to implement domain integrity, because the check constraints.

Question forty five. How To Get @@blunders And @@rowcount At The Same Time?

Answer :

If @@Rowcount is checked after Error checking statement then it's going to have zero as the cost of @@Recordcount as it might have been reset. And if @@Recordcount is checked earlier than the error-checking announcement then @@Error would get reset. To get @@blunders and @@rowcount on the identical time do both in identical announcement and store them in local variable. SELECT @RC = @@ROWCOUNT, @ER = @@ERROR

Question 46. What Is A Scheduled Jobs Or What Is A Scheduled Tasks?

Answer :

Scheduled responsibilities let consumer automate approaches that run on regular or predictable cycles. User can time table administrative duties, which includes dice processing, to run during times of gradual business interest. User also can determine the order wherein tasks run via creating activity steps within a SQL Server Agent process. E.G. Returned up database, Update Stats of Tables. Job steps deliver person manage over go with the flow of execution. If one process fails, person can configure SQL Server Agent to maintain to run the ultimate duties or to stop execution.

Question forty seven. What Are The Advantages Of Using Stored Procedures?

Answer :

• Stored method can reduced network traffic and latency, boosting utility overall performance.
• Stored manner execution plans may be reused, staying cached in SQL Server’s memory, decreasing server overhead.
• Stored strategies help promote code reuse.
• Stored strategies can encapsulate common sense. You can exchange stored procedure code without affecting clients.
• Stored approaches offer better protection to your facts.

Question 48. What Is A Table Called, If It Has Neither Cluster Nor Non-cluster Index? What Is It Used For?

Answer :

Unindexed desk or Heap. Microsoft Press Books and Book on Line (BOL) refers it as Heap. A heap is a table that does not have a clustered index and, therefore, the pages are not linked by using suggestions. The IAM pages are the best structures that hyperlink the pages in a table collectively. Unindexed tables are accurate for instant storing of facts. Many times it's far better to drop all indexes from desk and then do bulk of inserts and to repair the ones indexes after that.

Question 49. Can Sql Servers Linked To Other Servers Like Oracle?

Answer :

SQL Server may be linked to any server supplied it has OLE-DB company from Microsoft to permit a hyperlink. E.G. Oracle has an OLE-DB company for oracle that Microsoft provides to feature it as related server to SQL Server group.

Question 50. What Is Bcp? When Does It Used?

Answer :

BulkCopy is a device used to duplicate big quantity of statistics from tables and perspectives. BCP does no longer reproduction the structures same as supply to destination. BULK INSERT command enables to import a statistics file into a database table or view in a user-specific layout.

Question fifty one. How To Implement One-to-one, One-to-many And Many-to-many Relationships While Designing Tables?

Answer :

One-to-One courting can be implemented as a unmarried table and infrequently as two tables with primary and overseas key relationships. One-to-Many relationships are applied with the aid of splitting the information into  tables with primary key and overseas key relationships.

Many-to-Many relationships are applied the usage of a junction table with the keys from each the tables forming the composite number one key of the junction desk.

Question fifty two. What Is An Execution Plan? When Would You Use It? How Would You View The Execution Plan?

Answer :

An execution plan is basically a avenue map that graphically or textually shows the statistics retrieval techniques selected via the SQL Server question optimizer for a saved technique or ad-hoc query and is a very useful device for a developer to understand the performance characteristics of a query or saved method since the plan is the one that SQL Server will vicinity in its cache and use to execute the saved manner or query. From inside Query Analyzer is an alternative referred to as “Show Execution Plan” (positioned at the Query drop-down menu). If this selection is became on it's going to display question execution plan in separate window while question is ran once more.

Question 53. What Are The Basic Functions For Master, Msdb, Model, Tempdb And Resource Databases?

Answer :

The master database holds facts for all databases placed on the SQL Server example and is theglue that holds the engine collectively. Because SQL Server cannot start without a functioning masterdatabase, you have to administer this database with care.
The msdb database stores records concerning database backups, SQL Agent records, DTS programs, SQL Server jobs, and some replication information such as for log transport.
The tempdb holds brief items which include international and nearby transient tables and saved approaches.
The version is basically a template database used within the creation of any new user database created in the example.
The resoure Database is a examine-most effective database that consists of all of the device gadgets that are blanketed with SQL Server. SQL Server machine objects, together with sys.Gadgets, are bodily persevered within the Resource database, but they logically seem within the sys schema of each database. The Resource database does not include consumer data or consumer metadata.

Question fifty four. What Is Service Broker?

Answer :

Service Broker is a message-queuing era in SQL Server that permits developers to combine SQL Server absolutely into distributed applications. Service Broker is feature which gives facility to SQL Server to ship an asynchronous, transactional message. It lets in a database to send a message to some other database with out waiting for the response, so the software will preserve to characteristic if the faraway database is temporarily unavailable.

Question 55. Where Sql Server User Names And Passwords Are Stored In Sql Server?

Answer :

They get stored in System Catalog Views sys.Server_principals and sys.Sql_logins.

Question fifty six. What Is Policy Management?

Answer :

Policy Management in SQL SERVER 2008 allows you to define and put into effect policies for configuring and dealing with SQL Server across the enterprise. Policy-Based Management is configured in SQL Server Management Studio (SSMS). Navigate to the Object Explorer and amplify the Management node and the Policy Management node; you'll see the Policies, Conditions, and Facets nodes.

Question fifty seven. What Is Replication And Database Mirroring?

Answer :

Database mirroring may be used with replication to provide availability for the guide database. Database mirroring includes  copies of a single database that typically reside on different computer systems. At any given time, simplest one reproduction of the database is currently available to customers which can be called the major database. Updates made via clients to the main database are carried out on the other replica of the database, called the replicate database. Mirroring includes applying the transaction log from each insertion, update, or deletion made at the foremost database onto the replicate database.

Question fifty eight. What Are Sparse Columns?

Answer :

A sparse column is another device used to reduce the quantity of physical storage utilized in a database. They are the normal columns which have an optimized storage for null values. Sparse columns reduce the space necessities for null values on the fee of more overhead to retrieve nonnull values.

Question 59. What Does Top Operator Do?

Answer :

The TOP operator is used to specify the quantity of rows to be back by using a query. The TOP operator has new addition in SQL SERVER 2008 that it accepts variables as well as literal values and may be used with INSERT, UPDATE, and DELETES statements.

Question 60. What Is Cte?

Answer :

CTE is an abbreviation Common Table Expression. A Common Table Expression (CTE) is an expression that can be idea of as a transient end result set that's described in the execution of a single SQL announcement. A CTE is much like a derived table in that it is not stored as an item and lasts only at some point of the query.

Question sixty one. What Is Merge Statement?

Answer :

MERGE is a brand new feature that provides an green way to perform multiple DML operations. In preceding variations of SQL Server, we had to write separate statements to INSERT, UPDATE, or DELETE facts based on positive situations, but now, using MERGE announcement we are able to encompass the common sense of such statistics modifications in one assertion that even assessments whilst the records is matched then just replace it and while unrivaled then insert it. One of the maximum important benefits of MERGE declaration is all the statistics is examine and processed best once.

Question 62. What Is Filtered Index?

Answer :

Filtered Index is used to index a part of rows in a table that means it applies clear out on INDEX which improves query overall performance, reduce index preservation expenses, and decrease index storage expenses as compared with complete-desk indexes. When we see an Index created with a few where clause then that is really a FILTERED INDEX.

Question sixty three. Which Are New Data Types Introduced In Sql Server 2008?

Answer :

The GEOMETRY Type: The GEOMETRY facts type is a device .NET commonplace language runtime (CLR) records kind in SQL Server. This kind represents statistics in a -dimensional Euclidean coordinate machine.
The GEOGRAPHY Type: The GEOGRAPHY datatype’s capabilities are the same as with GEOMETRY. The distinction among the 2 is that whilst you specify GEOGRAPHY, you're commonly specifying points in terms of range and longitude.
New Date and Time Datatypes: SQL Server 2008 introduces 4 new datatypes associated thus far and time: DATE, TIME, DATETIMEOFFSET, and DATETIME2.

DATE: The new DATE kind just stores the date itself. It is based totally at the Gregorian calendar and handles years from 1 to 9999.
TIME: The new TIME (n) type stores time with various 00:00:00.0000000 via 23:59:fifty nine.9999999. The precision is permitted with this type. TIME supports seconds all the way down to a hundred nanoseconds. The nin TIME (n) defines this degree of fractional 2d precision, from 0 to 7 digits of precision.
The DATETIMEOFFSET Type: DATETIMEOFFSET (n) is the time-area-aware model of a datetime datatype. The call will seem much less strange while you do not forget what it actually is: a date + a time + a time-area offset. The offset is based totally on how a ways behind or beforehand you are from Coordinated Universal Time (UTC) time.
The DATETIME2 Type: It is an extension of the datetime kind in earlier variations of SQL Server. This new datatype has a date variety covering dates from January 1 of year 1 via December 31 of 12 months 9999. This is a precise improvement over the 1753 lower boundary of the datetime datatype. DATETIME2 now not most effective includes the larger date variety, however additionally has a timestamp and the identical fractional precision that TIME kind provides
Question sixty four. What Are The Advantages Of Using Cte?

Answer :

• Using CTE improves the readability and makes maintenance of complex queries easy.
• The query can be divided into separate, easy, logical constructing blocks which may be then used to build more complex CTEs until very last result set is generated.
• CTE may be described in functions, saved strategies, triggers or even views.
• After a CTE is defined, it is able to be used as a Table or a View and may SELECT, INSERT, UPDATE or DELETE Data.

Question sixty five. How Can We Rewrite Sub-queries Into Simple Select Statements Or With Joins?

Answer :

Yes we can write the use of Common Table Expression (CTE). A Common Table Expression (CTE) is an expression that may be idea of as a brief end result set which is described within the execution of a single SQL assertion. A CTE is just like a derived table in that it isn't always saved as an item and lasts simplest during the question.

E.G.

USE AdventureWorks
GO
WITH EmployeeDepartment_CTE AS (
SELECT EmployeeID,DepartmentID,ShiftID
FROM HumanResources.EmployeeDepartmentHistory
)
SELECT ecte.EmployeeId,ed.DepartmentID, ed.Name,ecte.ShiftID
FROM HumanResources.Department ed
INNER JOIN EmployeeDepartment_CTE ecte ON ecte.DepartmentID =ed.DepartmentID
GO
 

Question sixty six. What Is Clr?

Answer :

In SQL Server 2008, SQL Server gadgets inclusive of user-described functions may be created using such CLR languages. This CLR language assist extends no longer handiest to user-described features, however additionally to stored methods and triggers. You can broaden such CLR accessories to SQL Server the usage of Visual Studio 2008.

Question 67. What Are Synonyms?

Answer :

Synonyms provide you with the capability to provide exchange names for database gadgets. You can alias object names; as an example, the usage of the Employee table as Emp. You can also shorten names. This is particularly beneficial when handling 3 and four element names; for instance, shortening server.Database.Owner.Item to object.

Question sixty eight. What Is Linq?

Answer :

Language Integrated Query (LINQ) provides the capacity to query objects the usage of .NET languages. The LINQ to SQL item/relational mapping (O/RM) framework presents the subsequent basic features:

• Tools to create classes (usually known as entities) mapped to database tables
• Compatibility with LINQ’s general query operations
• The DataContext elegance, with features consisting of entity document monitoring, computerized SQL announcement generation, report concurrency detection, and lots extra.

Question 69. What Is Isolation Levels?

Answer :

Transactions specify an isolation level that defines the degree to which one transaction need to be isolated from useful resource or facts modifications made with the aid of other transactions. Isolation tiers are described in phrases of which concurrency side-effects, including dirty reads or phantom reads, are allowed.

Transaction isolation tiers manipulate:
• Whether locks are taken while facts is study, and what sort of locks are asked.
• How lengthy the read locks are held.
• Whether a read operation referencing rows modified by means of every other transaction:
• Blocks until the exclusive lock at the row is freed.
• Retrieves the devoted model of the row that existed on the time the declaration or transaction started out.
• Reads the uncommitted facts modification.

Question 70. What Is Use Of Except Clause?

Answer :

EXCEPT clause is just like MINUS operation in Oracle. The EXCEPT query and MINUS query returns all rows inside the first question that aren't returned in the 2d question. Each SQL assertion inside the EXCEPT question and MINUS query ought to have the same number of fields within the end result sets with comparable statistics sorts.

Question 71. What Is Xpath?

Answer :

XPath makes use of a fixed of expressions to pick nodes to be processed. The maximum common expression that you’ll use is the location direction expression, which returns lower back a fixed of nodes known as a node set. XPath can use each an unabbreviated and an abbreviated syntax. The following is the unabbreviated syntax for a region course:
/axisName::nodeTest[predicate]/axisName::nodeTest[predicate].

Question seventy two. What Is Nolock?

Answer :

Using the NOLOCK query optimizer hint is typically considered true exercise so that you can improve concurrency on a hectic gadget. When the NOLOCK hint is blanketed in a SELECT declaration, no locks are taken whilst facts is read. The result is a Dirty Read, this means that that some other technique could be updating the facts at the exact time you are analyzing it. There aren't any guarantees that your query will retrieve the maximum recent information. The advantage to performance is that your studying of records will not block updates from taking vicinity, and updates will not block your reading of information. SELECT statements take Shared (Read) locks. This way that more than one SELECT statements are allowed simultaneous get entry to, but different processes are blocked from editing the records. The updates will queue till all the reads have completed, and reads asked after the update will look forward to the updates to finish. The end result in your system is postpone (blockading).

Question 73. How Would You Handle Error In Sql Server 2008?

Answer :

SQL Server now supports the usage of TRY…CATCH constructs for supplying wealthy blunders coping with. TRY…CATCH shall we us build error dealing with at the level we need, inside the manner we need to, by way of setting a vicinity wherein if any blunders takes place, it will get away of the area and head to an mistakes handler. The fundamental structure is as follows:

BEGIN TRY
<code>
END TRY
BEGIN CATCH
<code>
END CATCH
So if any mistakes occurs inside the TRY block, execution is diverted to the CATCH block, and the error can be dealt.

 

Question 74. What Is Raiseerror?

Answer :

RaiseError generates an blunders message and initiates blunders processing for the consultation. RAISERROR can both reference a person-defined message stored in thesys.Messages catalog view or build a message dynamically. The message is returned as a server errors message to the calling software or to an related CATCH block of a TRY…CATCH construct.

Question 75. How To Rebuild Master Databse?

Answer :

Master database is gadget database and it consists of information approximately walking server’s configuration. When SQL Server 2005 is hooked up it commonly creates grasp, version, msdb, tempdb useful resource and distribution device database via default. Only Master database is the one which is definitely ought to have database. Without Master database SQL Server can't be started. This is the cause it's far extremely critical to backup Master database.

To rebuild the Master database, Run Setup.Exe, confirm, and restore a SQL Server example, and rebuild the device databases. This process is most often used to rebuild the grasp database for a corrupted set up of SQL Server.

Question seventy six. What Is Xml Datatype?

Answer :

The xml statistics type lets you keep XML files and fragments in a SQL Server database. An XML fragment is an XML instance that is lacking a single top-degree element. You can create columns and variables of the xml kind and store XML times in them. The xml information kind and related methods help combine XML into the relational framework of SQL Server.

Question seventy seven. What Is Data Compression?

Answer :

In SQL SERVE 2008 Data Compression is available in two flavors:

Row Compression
Page Compression
Row Compression
Row compression changes the format of bodily storage of data. It limit the metadata (column information, duration, offsets and many others) related to each report. Numeric information types and stuck length strings are stored in variable-length storage format, just like Varchar.
Page Compression
Page compression lets in commonplace information to be shared between rows for a given page.

Its uses the following techniques to compress information:

Row compression.
Prefix Compression.
For every column in a web page reproduction prefixes are diagnosed. These prefixes are stored in compression records headers (CI) which is living after web page header. A reference range is assigned to those prefixes and that reference quantity is replaced where ever the ones prefixes are getting used.
Dictionary Compression.
Dictionary compression searches for duplicate values all through the page and shops them in CI. The important difference between prefix and dictionary compression is that prefix is only confined to one column while dictionary is applicable to the entire page.

Question 78. What Is Use Of Dbcc Commands?

Answer :

The Transact-SQL programming language offers DBCC statements that act as Database Console Commands for SQL Server. DBCC instructions are used to perform following obligations.

• Maintenance responsibilities on database, index, or filegroup.
• Tasks that accumulate and show diverse kinds of information.
• Validation operations on a database, table, index, catalog, filegroup, or allocation of database pages.
• Miscellaneous tasks such as allowing hint flags or putting off a DLL from memory.

Question 79. How To Find Tables Without Indexes?

Answer :

Run  following query in Query Editor.
USE <database_name>;
GO
SELECT SCHEMA_NAME(schema_id) AS schema_name
,call AS table_name
FROM sys.Tables
WHERE OBJECTPROPERTY(OBJECT_ID,'IsIndexed') = zero
ORDER BY schema_name, table_name;
GO
Question eighty. How To Copy The Tables, Schema And Views From One Sql Server To Another?

Answer :

There are a couple of approaches to try this.
1. “Detach Database” from one server and “Attach Database” to some other server.
2. Manually script all the objects using SSMS and run the script on new server.
Three. Use Wizard of SSMS.

Question eighty one. How To Copy Data From One Table To Another Table?

Answer :

There are multiple approaches to do that.
1) INSERT INTO SELECT
This approach is used when desk is already created in the database earlier and statistics is to be inserted into this table from some other table. If columns indexed in insert clause and pick out clause are equal, they may be not required to listing them.
2) SELECT INTO
This technique is used while table isn't always created earlier and desires to be created when facts from one table is to be inserted into newly created table from every other table. New desk is created with identical statistics kinds as selected columns.

Question eighty two. What Is Catalog Views?

Answer :

Catalog views go back data that is utilized by the SQL Server Database Engine. Catalog Views are the most wellknown interface to the catalog metadata and offer the maximum green way to gain, transform, and present custom designed styles of this facts. All consumer-to be had catalog metadata is exposed via catalog perspectives.

Question eighty three. What Is Pivot And Unpivot?

Answer :

A Pivot Table can robotically type, be counted, and overall the facts stored in a single table or spreadsheet and create a 2nd table displaying the summarized facts. The PIVOT operator turns the values of a unique column into column names, effectively rotating a desk.
UNPIVOT desk is opposite of PIVOT Table.

Question 84. What Is Filestream?

Answer :

Filestream allows you to keep large items inside the file system and have those files integrated inside the database. It permits SQL Server based programs to store unstructured information consisting of files, photographs, audios, videos and so on. Within the file machine. FILESTREAM essentially integrates the SQL Server Database Engine with New Technology File System (NTFS); it essentially shops the facts in varbinary (max) data type. Using this records type, the unstructured facts is stored within the NTFS document machine and the SQL Server Database Engine manages the link between the Filestream column and the real file positioned in the NTFS. Using Transact SQL statements users can insert, update, delete and select the records saved in FILESTREAM enabled tables.

Question 85. What Is Dirty Read ?

Answer :

A dirty read takes place whilst two operations say, examine and write occurs collectively giving the incorrect or unedited records. Suppose, A has changed a row, however has not dedicated the adjustments. B reads the uncommitted information but his view of the statistics can be incorrect so this is Dirty Read.

Question 86. What Is Sqlcmd?

Answer :

sqlcmd is superior version of the isql and osql and it presents manner greater capability than other two alternatives. In different phrases sqlcmd is better replacement of isql (so that it will be deprecated eventually) and osql (now not protected in SQL Server 2005 RTM). Sqlcmd can work  modes –

BATCH 
interactive modes.
Question 87. What Is Aggregate Functions?

Answer :

Aggregate capabilities carry out a calculation on a hard and fast of values and go back a single price. Aggregate features ignore NULL values except COUNT characteristic.