Interview Questions.

Q1. What Is Primary Key?

A PRIMARY KEY constraint is a unique identifier for a row inside a database table. Every table ought to have a primary key constraint to uniquely identify every row and simplest one number one key constraint may be created for each table. The number one key constraints are used to enforce entity integrity.

Q2. What Is Check Constraint?

A CHECK constraint is used to restriction the values that may be positioned in a column. The check constraints are used to put into effect domain integrity.

Q3. Can Sql Servers Linked To Other Servers Like Oracle?

SQL Server may be linked to any server furnished it has OLE-DB issuer from Microsoft to permit a hyperlink. E.G. Oracle has an OLE-DB company for oracle that Microsoft affords to add it as connected server to SQL Server group.

Q4. What Is Identity?

Identity (or AutoNumber) is a column that robotically generates numeric values. A begin and increment cost can be set, however most DBA leave these at @A GUID column additionally generates numbers; the fee of this can't be managed. Identity/GUID columns do now not want to be listed.

Q5. Can A Stored Procedure Call Itself Or Recursive Stored Procedure? How Much Level Sp Nesting Is Possible?

Yes. Because Transact-SQL helps recursion, you may write saved processes that call themselves. Recursion can be described as a way of trouble solving in which the answer is arrived at with the aid of repetitively applying it to subsets of the hassle. A commonplace utility of recursive common sense is to perform numeric computations that lend themselves to repetitive evaluation by way of the identical processing steps. Stored methods are nested when one stored manner calls any other or executes controlled code by referencing a CLR habitual, type, or combination. You can nest stored procedures and controlled code references as much as 32 ranges.

Q6. How To Copy Data From One Table To Another Table?

There are more than one approaches to try this.

1) INSERT INTO SELECT

This technique is used while desk is already created inside the database in advance and data is to be inserted into this table from every other desk. If columns listed in insert clause and select clause are identical, they're not required to list them.

2) SELECT INTO

This method is used while table is not created earlier and desires to be created while information from one desk is to be inserted into newly created table from any other table. New table is created with equal data kinds as decided on columns.

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

STUFF function is used to overwrite current characters. Using this syntax, STUFF (string_expression, begin, period, replacement_characters), string_expression is the string that will have characters substituted, start is the beginning role, duration is the quantity of characters within the string which might be substituted, and replacement_characters are the new characters interjected into the string. REPLACE characteristic to replace existing characters of all occurrences. Using the syntax REPLACE (string_expression, search_string, replacement_string), in which each occurrence of search_string observed inside the string_expression will get replaced with replacement_string.

Q8. What Is Isolation Levels?

Transactions specify an isolation level that defines the diploma to which one transaction should be isolated from resource or data adjustments made by way of different transactions. Isolation stages are defined in terms of which concurrency facet-results, together with dirty reads or phantom reads, are allowed.

Transaction isolation tiers control:

• Whether locks are taken while facts is study, and what sort of locks are asked.

• How long the study locks are held.

• Whether a read operation referencing rows modified by means of another transaction:

• Blocks until the one-of-a-kind lock at the row is freed.

• Retrieves the dedicated model of the row that existed on the time the declaration or transaction commenced.

• Reads the uncommitted information modification.

Q9. What Are The Advantages Of Using Cte?

• Using CTE improves the readability and makes upkeep of complex queries easy.

• The query can be divided into separate, easy, logical constructing blocks which may be then used to build greater complicated CTEs until final result set is generated.

• CTE may be described in functions, stored procedures, triggers or even perspectives.

• After a CTE is defined, it could be used as a Table or a View and can SELECT, INSERT, UPDATE or DELETE Data.

Q10. What Is Row_number()?

ROW_NUMBER() returns a column as an expression that incorporates the row’s range in the end result set. This is most effective more than a few used in the context of the end result set, if the end result modifications, the ROW_NUMBER() will trade.

Q11. What Is Difference Between Delete & Truncate Commands?

Delete command eliminates the rows from a desk based totally at the condition that we offer with a WHERE clause. Truncate will honestly cast off all the rows from a table and there could be no information within the desk when we run the truncate command.

TRUNCATE

• TRUNCATE is faster and makes use of fewer machine and transaction log sources than DELETE.

• TRUNCATE removes the records by using deallocating the information pages used to store the table’s records, and only the web page deallocations are recorded in the transaction log.

• TRUNCATE gets rid of all rows from a table, however the desk structure, its columns, constraints, indexes and so forth, stays. The counter utilized by an identification for brand spanking new rows is reset to the seed for the column.

• You can't use TRUNCATE TABLE on a table referenced via a FOREIGN KEY constraint. Because TRUNCATE TABLE is not logged, it cannot set off a cause.

• TRUNCATE can not be rolled lower back.

• TRUNCATE is DDL Command.

• TRUNCATE Resets identification of the desk

DELETE

• DELETE eliminates rows separately and information an entry inside the transaction log for each deleted row.

• If you need to retain the identification counter, use DELETE instead. If you want to cast off table definition and its data, use the DROP TABLE assertion.

• DELETE Can be used with or without a WHERE clause

• DELETE Activates Triggers.

• DELETE can be rolled again.

• DELETE is DML Command.

• DELETE does now not reset identity of the desk.

Q12. What Is Bcp? When Does It Used?

BulkCopy is a tool used to copy huge amount of records from tables and perspectives. BCP does no longer reproduction the structures identical as source to destination. BULK INSERT command enables to import a facts record into a database desk or view in a user-distinctive format.

Q13. What Is Log Shipping?

Log transport is the technique of automating the backup of database and transaction log documents on a production SQL server, and then restoring them onto a standby server. Enterprise Editions best helps log delivery. In log shipping the transactional log report from one server is mechanically updated into the backup database on the alternative server. If one server fails, the alternative server will have the same db and may be used this because the Disaster Recovery plan. The key characteristic of log shipping is that it's going to mechanically backup transaction logs at some point of the day and mechanically restore them on the standby server at described c program languageperiod.

Q14. How To Rebuild Master Databse?

Master database is system database and it incorporates data approximately walking server’s configuration. When SQL Server 2005 is mounted it typically creates grasp, model, msdb, tempdb resource and distribution system database by means of default. Only Master database is the one that is certainly must have database. Without Master database SQL Server cannot be started. This is the reason it's miles extremely important to backup Master database.

To rebuild the Master database, Run Setup.Exe, affirm, and repair a SQL Server instance, and rebuild the device databases. This system is most often used to rebuild the grasp database for a corrupted installation of SQL Server.

Q15. What Are Primary Keys And Foreign Keys?

Primary keys are the particular identifiers for every row. They need to comprise particular values and can not be null. Due to their importance in relational databases, Primary keys are the most fundamental of all keys and constraints. A desk may have handiest one Primary key.

Foreign keys are both a technique of making sure facts integrity and a manifestation of the connection among tables.

Q16. What Is Filtered Index?

Filtered Index is used to index a part of rows in a table which means it applies filter on INDEX which improves question performance, reduce index preservation charges, and decrease index storage charges as compared with complete-desk indexes. When we see an Index created with some where clause then that is simply a FILTERED INDEX.

Q17. What Is Unique Key Constraint?

A UNIQUE constraint enforces the distinctiveness of the values in a fixed of columns, so no replica values are entered. The precise key constraints are used to enforce entity integrity because the number one key constraints.

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

The grasp database holds data for all databases placed at the SQL Server example and is theglue that holds the engine together. Because SQL Server can not start with out a functioning masterdatabase, you must administer this database with care.

The msdb database stores records regarding database backups, SQL Agent facts, DTS applications, SQL Server jobs, and some replication statistics such as for log transport.

The tempdb holds transient objects which include worldwide and nearby temporary tables and saved approaches.

The model is largely a template database used within the creation of any new person database created in the instance.

The resoure Database is a read-best database that incorporates all of the machine objects which can be covered with SQL Server. SQL Server machine gadgets, along with sys.Objects, are physically endured in the Resource database, but they logically appear inside the sys schema of each database. The Resource database does no longer include consumer facts or consumer metadata.

Q19. What Is Index?

An index is a bodily shape containing suggestions to the records. Indices are created in an current desk to discover rows greater quickly and efficiently. It is possible to create an index on one or extra columns of a desk, and each index is given a call. The users cannot see the indexes; they may be simply used to speed up queries. Effective indexes are one of the nice methods to improve performance in a database software. A table scan takes place whilst there's no index to be had to help a query. In a desk scan SQL Server examines each row in the table to fulfill the query outcomes. Table scans are occasionally unavoidable, however on massive tables, scans have a extremely good impact on performance.

Q20. What Is Pivot And Unpivot?

A Pivot Table can routinely sort, count number, and overall the information saved in a single table or spreadsheet and create a 2nd table displaying the summarized records. The PIVOT operator turns the values of a distinctive column into column names, efficaciously rotating a desk.

UNPIVOT table is opposite of PIVOT Table.

Q21. When Is The Use Of Update_statistics Command?

This command is largely used while a big processing of facts has came about. If a huge quantity of deletions any amendment or Bulk Copy into the tables has happened, it has to replace the indexes to take those modifications into consideration. UPDATE_STATISTICS updates the indexes on these tables accordingly.

Q22. How To Find Tables Without Indexes?

  Run  following question in Query Editor.

USE <database_name>;

GO

SELECT SCHEMA_NAME(schema_id) AS schema_name

,name AS table_name

FROM sys.Tables

WHERE OBJECTPROPERTY(OBJECT_ID,'IsIndexed') = 0

ORDER BY schema_name, table_name;

GO

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

A local brief desk exists simplest at some point of a connection or, if defined internal a compound statement, in the course of the compound announcement.

A international transient table remains in the database permanently, but the rows exist most effective inside a given connection. When connection is closed, the information in the worldwide brief desk disappears. However, the table definition stays with the database for access while database is opened next time.

Q24. What Is Trigger?

A cause is a SQL process that initiates an motion when an event (INSERT, DELETE or UPDATE) occurs. Triggers are stored in and managed by the DBMS. Triggers are used to maintain the referential integrity of data by converting the information in a systematic style. A trigger cannot be called or performed; DBMS robotically fires the cause as a result of a statistics change to the related desk. Triggers may be regarded as much like stored strategies in that both consist of procedural common sense this is saved on the database stage. Stored techniques, but, aren't occasion-drive and aren't attached to a selected table as triggers are Stored strategies are explicitly done by way of invoking a CALL to the system at the same time as triggers are implicitly performed. In addition, triggers can also execute stored processes.

Nested Trigger: A cause can also comprise INSERT, UPDATE and DELETE good judgment within itself, so whilst the cause is fired due to statistics change it can also motive every other statistics amendment, thereby firing every other cause. A trigger that consists of facts amendment good judgment inside itself is referred to as a nested trigger.

Q25. What Is An Execution Plan? When Would You Use It? How Would You View The Execution Plan?

An execution plan is essentially a street map that graphically or textually indicates the information retrieval methods selected with the aid of the SQL Server question optimizer for a saved procedure or advert-hoc question and is a completely beneficial device for a developer to understand the overall performance traits of a query or stored process because the plan is the one that SQL Server will vicinity in its cache and use to execute the saved technique or query. From inside Query Analyzer is an option referred to as “Show Execution Plan” (placed at the Query drop-down menu). If this option is turned on it'll show query execution plan in separate window whilst query is ran once more.

Q26. Name 3 Ways To Get An Accurate Count Of The Number Of Records In A Table?

SELECT * FROM table1

SELECT COUNT(*) FROM table1

SELECT rows FROM sysindexes WHERE identification = OBJECT_ID(table1) AND indid < 2.

Q27. What Is A Linked Server?

Linked Servers is a concept in SQL Server by which we can add other SQL Server to a Group and query both the SQL Server dbs using T-SQL Statements. 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. Stored Procedure sp_addlinkedserver, sp_addlinkedsrvlogin will be used add new Linked Server.

Q28. What Is Filestream?

Filestream allows you to store large objects in the file system and have these files integrated within the database. It enables SQL Server based applications to store unstructured data such as documents, images, audios, videos etc. In the file system. FILESTREAM basically integrates the SQL Server Database Engine with New Technology File System (NTFS); it basically stores the data in varbinary (max) data type. Using this data type, the unstructured data is stored in the NTFS file system and the SQL Server Database Engine manages the link between the Filestream column and the actual file located in the NTFS. Using Transact SQL statements users can insert, update, delete and select the data stored in FILESTREAM enabled tables.

Q29. What Is Collation?

Collation refers to a set of rules that determine how data is sorted and compared. Character data is sorted using rules that define the correct character sequence, with options for specifying case sensitivity, accent marks, character types and character width.

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

Yes we can write using Common Table Expression (CTE). A Common Table Expression (CTE) is an expression that can be thought of as a temporary result set which is defined within the execution of a single SQL statement. A CTE is similar to a derived table in that it is not stored as an object and lasts only for the duration of the query.

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
 

Q31. What Is Xpath?

XPath uses a set of expressions to select nodes to be processed. The most common expression that you’ll use is the location path expression, which returns back a set of nodes called a node set. XPath can use both an unabbreviated and an abbreviated syntax. The following is the unabbreviated syntax for a location path:

/axisName::nodeTest[predicate]/axisName::nodeTest[predicate].

Q32. What Is Cte?

CTE is an abbreviation Common Table Expression. A Common Table Expression (CTE) is an expression that can be thought of as a temporary result set which is defined within the execution of a single SQL statement. A CTE is similar to a derived table in that it is not stored as an object and lasts only for the duration of the query.

Q33. What Is Nolock?

Using the NOLOCK query optimizer hint is generally considered good practice in order to improve concurrency on a busy system. When the NOLOCK hint is included in a SELECT statement, no locks are taken when data is read. The result is a Dirty Read, which means that another process could be updating the data at the exact time you are reading it. There are no guarantees that your query will retrieve the most recent data. The advantage to performance is that your reading of data will not block updates from taking place, and updates will not block your reading of data. SELECT statements take Shared (Read) locks. This means that multiple SELECT statements are allowed simultaneous access, but other processes are blocked from modifying the data. The updates will queue until all the reads have completed, and reads requested after the update will wait for the updates to complete. The result to your system is delay (blocking).

Q34. What Is Cursor?

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

In order to work with a cursor we need to perform some steps in the following order:

• Declare cursor

• Open cursor

• Fetch row from the cursor

• Process fetched row

• Close cursor

• Deallocate cursor

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

Windows mode and Mixed Mode – SQL & Windows.

To change authentication mode in SQL Server click Start, Programs, Microsoft SQL Server and click SQL Enterprise Manager to run SQL Enterprise Manager from the Microsoft SQL Server program group. Select the server then from the Tools menu select SQL Server Configuration Properties, and choose the Security page.

Q36. What Is Sql Server Agent?

SQL Server agent plays an important role in the day-to-day tasks of a database administrator (DBA). It is often overlooked as one of the main tools for SQL Server management. Its purpose is to ease the implementation of tasks for the DBA, with its full-function scheduling engine, which allows you to schedule your own jobs and scripts.

Q37. What Are The Difference Between Clustered And A Non-clustered Index?

A clustered index is a special type of index that reorders the way records in the table are physically stored. Therefore table can have only one clustered index. The leaf nodes of a clustered index contain the data pages.

A non clustered 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 non clustered index does not consist of the data pages. Instead, the leaf nodes contain index rows.

Q38. What Is Not Null Constraint?

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

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

SQL Server runs on port 143@It can be changed from the Network Utility TCP/IP properties -> Port number, both on customer and the server.

Q40. What Is Policy Management?

Policy Management in SQL SERVER 2008 permits you to outline and put in force guidelines for configuring and coping with SQL Server throughout the agency. Policy-Based Management is configured in SQL Server Management Studio (SSMS). Navigate to the Object Explorer and increase the Management node and the Policy Management node; you may see the Policies, Conditions, and Facets nodes.

Q41. What Is Xml Datatype?

The xml facts kind lets you save XML documents and fragments in a SQL Server database. An XML fragment is an XML example this is missing a unmarried top-level detail. You can create columns and variables of the xml kind and store XML times in them. The xml statistics type and associated techniques assist integrate XML into the relational framework of SQL Server.

Q42. What Are The Advantages Of Using Stored Procedures?

• Stored manner can decreased network visitors and latency, boosting application overall performance.

• Stored procedure execution plans can be reused, staying cached in SQL Server’s reminiscence, reducing server overhead.

• Stored tactics help sell code reuse.

• Stored techniques can encapsulate good judgment. You can trade stored procedure code with out affecting customers.

• Stored procedures offer higher safety to your statistics.

Q43. What Are Synonyms?

Synonyms come up with the capability to offer exchange names for database objects. You can alias object names; for example, using the Employee desk as Emp. You also can shorten names. This is specifically beneficial when managing three and four part names; as an example, shortening server.Database.Owner.Item to object.

Q44. What Is B-tree?

The database server makes use of a B-tree structure to organize index statistics. B-Tree normally has following sorts of index pages or nodes:

• root node: A root node incorporates node hints to branch nodes which can be best one.

• branch nodes: A branch node contains tips to leaf nodes or other branch nodes which can be  or greater.

• leaf nodes: A leaf node contains index items and horizontal pointers to different leaf nodes which may be many.

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

Properties of Sub-Query

• A sub-question must be enclosed in the parenthesis.

• A sub-question must be positioned in the right hand of the evaluation operator, and

• A sub-query cannot contain an ORDER-BY clause.

• A question can include a couple of sub-question.

Types of Sub-question

• Single-row sub-query, wherein the sub-query returns simplest one row.

• Multiple-row sub-question, wherein the sub-question returns a couple of rows, and

• Multiple column sub-query, wherein the sub-question returns multiple columns

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

One-to-One courting can be carried out as a single desk and infrequently as  tables with primary and overseas key relationships. One-to-Many relationships are applied by way of splitting the records into  tables with number one key and overseas key relationships.

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

Q47. What Is Sql Profiler?

SQL Profiler is a graphical tool that permits system directors to monitor occasions in an example of Microsoft SQL Server. You can seize and store information approximately every event to a report or SQL Server table to research later. For instance, you can monitor a manufacturing surroundings to peer which saved processes are hampering performances by using executing too slowly.

Use SQL Profiler to reveal most effective the occasions in which you are involved. If traces have become too big, you may clear out them based at the facts you need, so that only a subset of the occasion records is accrued. Monitoring too many events provides overhead to the server and the tracking process and may cause the hint file or hint table to develop very huge, specifically whilst the tracking process takes area over a long period of time.

Q48. What Are Sparse Columns?

A sparse column is another tool used to lessen the amount of physical storage utilized in a database. They are the regular columns which have an optimized storage for null values. Sparse columns lessen the distance necessities for null values at the price of greater overhead to retrieve nonnull values.

Q49. Which Are New Data Types Introduced In Sql Server 2008?

The GEOMETRY Type: The GEOMETRY information type is a device .NET not unusual language runtime (CLR) records type in SQL Server. This kind represents facts in a two-dimensional Euclidean coordinate device.

The GEOGRAPHY Type: The GEOGRAPHY datatype’s capabilities are similar to with GEOMETRY. The distinction between the two is that when you specify GEOGRAPHY, you're typically specifying factors in terms of latitude and longitude.

New Date and Time Datatypes: SQL Server 2008 introduces four new datatypes related so far and time: DATE, TIME, DATETIMEOFFSET, and DATETIME2.

DATE: The new DATE kind just shops the date itself. It is based on the Gregorian calendar and handles years from 1 to 999@

TIME: The new TIME (n) type stores time with a number of 00:00:00.0000000 via 23:59:59.999999@The precision is allowed with this kind. TIME supports seconds all the way down to a hundred nanoseconds. The nin TIME (n) defines this level of fractional 2nd precision, from zero to 7 digits of precision.

The DATETIMEOFFSET Type: DATETIMEOFFSET (n) is the time-region-conscious model of a datetime datatype. The call will seem much less strange while you recollect what it virtually is: a date + a time + a time-region offset. The offset is based on how far at the back of or ahead you're from Coordinated Universal Time (UTC) time.

The DATETIME2 Type: It is an extension of the datetime type in earlier variations of SQL Server. This new datatype has a date range protecting dates from January 1 of year 1 via December 31 of 12 months 999@This is a definite improvement over the 1753 decrease boundary of the datetime datatype. DATETIME2 no longer most effective includes the bigger date variety, but also has a timestamp and the equal fractional precision that TIME type presents

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

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 aren't linked via suggestions. The IAM pages are the best systems that hyperlink the pages in a table collectively. Unindexed tables are true for immediate storing of information. Many instances it's miles higher to drop all indexes from desk and then do bulk of inserts and to restore the ones indexes after that.