XML | SQL Server Interview Questions

XML | SQL Server Interview Questions

1. How can I convert data in a Microsoft Access table into XML format?
The following applications can help you convert Access data into XML format: Access 2002, ADO 2.5, and SQLXML. Access 2002 (part of Microsoft Office XP) enables you to query or save a table in XML format. You might be able to automate this process. ADO 2.5 and later enables you to open the data into a recordset, then persist the recordset in XML format, as the following code shows:
rs.Save "c:\rs.xml", adPersistXML
You can use linked servers to add the Access database to your SQL Server 2000 database so you can run queries from within SQL Server to retrieve data. Then, through HTTP, you can use the SQLXML technology to extract the Access data in the XML format you want.

2. @@IDENTITY ?
Returns the last-inserted identity value.

PERMISSIONS | SQL Server Interview Questions

1.Different Authentication modes in Sql server? If a user is logged under windows authentication mode, how to find his userid?
There are Three Different authentication modes in sqlserver.

•     Windows Authentication Mode
•     SqlServer Authentication Mode
•     Mixed Authentication Mode

“system_user” system function in sqlserver to fetch the logged on user name.

2. Give the connection strings from front-end for both type logins(windows,sqlserver)?
This are specifically for sqlserver not for any other RDBMS
Data Source=MySQLServer;Initial Catalog=NORTHWIND;Integrated Security=SSPI (windows)
Data Source=MySQLServer;Initial Catalog=NORTHWIND;Uid=” ”;Pwd=” ”(sqlserver)

3. What are three SQL keywords used to change or set someone’s permissions?
Grant, Deny and Revoke

4. If a user does not have permission on a table, but he has permission to a view created on it, will he be able to view the data in table?
Yes.

TOOLS | SQL Server Interview Questions

1. Have you ever used DBCC command? Give an example for it.
The Transact-SQL programming language provides DBCC statements that act as Database Console Commands for Microsoft® SQL Serve 2000. These statements check the physical and logical consistency of a database. Many DBCC statements can fix detected problems. Database Console Command statements are grouped into these categories.

2. What is the output of DBCC Showcontig statement?
Displays fragmentation information for the data and indexes of the specified table.

3. How do I reset the identity column?
You can use the DBCC CHECKIDENT statement, if you want to reset or reseed the identity column. For example, if you need to force the current identity value in the jobs table to a value of 100, you can use the following:
USE pubs
GO
DBCC CHECKIDENT (jobs, RESEED, 100)
GO

4. About SQL Command line executables

5. What is DTC?
The Microsoft Distributed Transaction Coordinator (MS DTC) is a transaction manager that allows client applications to include several different sources of data in one transaction. MS DTC coordinates committing the distributed transaction across all the servers enlisted in the transaction.

6. What is DTS? Any drawbacks in using DTS?
Microsoft® SQL Server™ 2000 Data Transformation Services (DTS) is a set of graphical tools and programmable objects that lets you extract, transform, and consolidate data from disparate sources into single or multiple destinations.

7. What is BCP?
The bcp utility copies data between an instance of Microsoft® SQL Server™ 2000 and a data file in a user-specified format.
C:\Documents and Settings\sthomas>bcp
usage: bcp {dbtable | query} {in | out | queryout | format} datafile
[-m maxerrors] [-f formatfile] [-e errfile]
[-F firstrow] [-L lastrow] [-b batchsize]
[-n native type] [-c character type] [-w wide character type]
[-N keep non-text native] [-V file format version] [-q quoted identifier]
[-C code page specifier] [-t field terminator] [-r row terminator]
[-i inputfile] [-o outfile] [-a packetsize]
[-S server name] [-U username] [-P password]
[-T trusted connection] [-v version] [-R regional enable]
[-k keep null values] [-E keep identity values]
[-h "load hints"]

8. How can I create a plain-text flat file from SQL Server as input to another application?
One of the purposes of Extensible Markup Language (XML) is to solve challenges like this, but until all applications become XML-enabled, consider using our faithful standby, the bulk copy program (bcp) utility. This utility can do more than just dump a table; bcp also can take its input from a view instead of from a table. After you specify a view as the input source, you can limit the output to a subset of columns or to a subset of rows by selecting appropriate filtering (WHERE and HAVING) clauses.
More important, by using a view, you can export data from multiple joined tables. The only thing you cannot do is specify the sequence in which the rows are written to the flat file, because a view does not let you include an ORDER BY clause in it unless you also use the TOP keyword.
If you want to generate the data in a particular sequence or if you cannot predict the content of the data you want to export, be aware that in addition to a view, bcp also supports using an actual query. The only "gotcha" about using a query instead of a table or view is that you must specify queryout in place of out in the bcp command line.
For example, you can use bcp to generate from the pubs database a list of authors who reside in California by writing the following code:
bcp "SELECT * FROM pubs..authors WHERE state = 'CA'" queryout c:\CAauthors.txt -c -T -S

9. What are the different ways of moving data/databases between servers and databases in SQL Server?
There are lots of options available, you have to choose your option depending upon your requirements. Some of the options you have are: BACKUP/RESTORE, detaching and attaching databases, replication, DTS, BCP, logshipping, INSERT...SELECT, SELECT...INTO, creating INSERT scripts to generate data.

10. How will I export database?
Through DTS - Import/Export wizard
Backup - through Complete/Differential/Transaction Log

11. How to export database at a particular time, every week?
Backup - Schedule
DTS - Schedule
Jobs - create a new job

12. What is OSQL and ISQL utility?
The osql utility allows you to enter Transact-SQL statements, system procedures, and script files. This utility uses ODBC to communicate with the server.
The isql utility allows you to enter Transact-SQL statements, system procedures, and script files; and uses DB-Library to communicate with Microsoft® SQL Server™ 2000.
All DB-Library applications, such as isql, work as SQL Server 6.5–level clients when connected to SQL Server 2000. They do not support some SQL Server 2000 features.
The osql utility is based on ODBC and does support all SQL Server 2000 features. Use osql to run scripts that isql cannot run.

13. What Tool you have used for checking Query Optimization? What is the use of profiler in sql server?  What is the first thing u look at in a SQL Profiler?
SQL Profiler is a graphical tool that allows system administrators to monitor events in an instance of Microsoft® SQL Server™. You can capture and save data about each event to a file or SQL Server table to analyze later. For example, you can monitor a production environment to see which stored procedures is hampering performance by executing too slowly.
Use SQL Profiler to:

•   Monitor the performance of an instance of SQL Server.
•   Debug Transact-SQL statements and stored procedures.
•   Identify slow-executing queries.
•  Test SQL statements and stored procedures in the development phase of a project by single-stepping through statements to confirm that the code works as expected.
•  Troubleshoot problems in SQL Server by capturing events on a production system and replaying them on a test system. This is useful for testing or debugging purposes and allows users to continue using the production system without interference.

Audit and review activity that occurred on an instance of SQL Server. This allows a security administrator to review any of the auditing events, including the success and failure of a login attempt and the success and failure of permissions in accessing statements and objects.

BASIC Concepts I SQL Server Interview Questions

1. What are the constraints for Table Constraints define rules regarding the values allowed in columns and are the standard mechanism for enforcing integrity. SQL Server 2000 supports five classes of constraints.NOT NULL
   CHECK
   UNIQUE
   PRIMARY KEY
   FOREIGN KEY
2. There are 50 columns in a table. Write a query to get first 25 columnsAns: Need to mention each column names.
3. How to list all the tables in a particular database?USE pubs
   GO
   sp_help
4. What are cursors? Explain different types of cursors. What are the disadvantages of cursors? How can you avoid cursors?Cursors allow row-by-row processing of the result sets.
   Types of cursors: Static, Dynamic, Forward-only, Keyset-driven.
   Disadvantages of cursors: Each time you fetch a row from the cursor, it results in a network roundtrip. Cursors are also costly because they require more resources and temporary storage (results in more IO operations). Further, there are restrictions on the SELECT statements that can be used with some types of cursors.
   How to avoid cursor:
• Most of the times, set based operations can be used instead of cursors. Here is an example: If you have to give a flat hike to your employees using the following criteria:
  Salary between 30000 and 40000 -- 5000 hike
  Salary between 40000 and 55000 -- 7000 hike
  Salary between 55000 and 65000 -- 9000 hike
  In this situation many developers tend to use a cursor, determine each employee's salary and update his salary according to the above formula. But the same can be achieved by multiple update statements or can be combined in a single UPDATE statement as shown below:
  UPDATE tbl_emp SET salary =
  CASE WHEN salary BETWEEN 30000 AND 40000 THEN salary + 5000
  WHEN salary BETWEEN 40000 AND 55000 THEN salary + 7000
  WHEN salary BETWEEN 55000 AND 65000 THEN salary + 10000
  END
• You need to call a stored procedure when a column in a particular row meets certain condition. You don't have to use cursors for this. This can be achieved using WHILE loop, as long as there is a unique key to identify each row. For examples of using WHILE loop for row by row processing, check out the 'My code library' section of my site or search for WHILE.
5. What is Dynamic Cursor? Suppose, I have a dynamic cursor attached to table in a database.  I have another means by which I will modify the table.  What do you think will the values in the cursor be?Dynamic cursors reflect all changes made to the rows in their result set when scrolling through the cursor. The data values, order, and membership of the rows in the result set can change on each fetch. All UPDATE, INSERT, and DELETE statements made by all users are visible through the cursor. Updates are visible immediately if they are made through the cursor using either an API function such as SQLSetPos or the Transact-SQL WHERE CURRENT OF clause. Updates made outside the cursor are not visible until they are committed, unless the cursor transaction isolation level is set to read uncommitted.
6. What is DATEPART?Returns an integer representing the specified datepart of the specified date.
7. Difference between Delete and Truncate?TRUNCATE TABLE is functionally identical to DELETE statement with no WHERE clause: both remove all rows in the table.
   (1) But TRUNCATE TABLE is faster and uses fewer system and transaction log resources than DELETE. The DELETE statement removes rows one at a time and records an entry in the transaction log for each deleted row. TRUNCATE TABLE removes the data by deallocating the data pages used to store the table's data, and only the page deallocations are recorded in the transaction log.
   (2) Because TRUNCATE TABLE is not logged, it cannot activate a trigger.
   (3) The counter used by an identity for new rows is reset to the seed for the column. If you want to retain the identity counter, use DELETE instead.
   Of course, TRUNCATE TABLE can be rolled back.
8. What are global variables? Tell me some of them?Transact-SQL global variables are a form of function and are now referred to as functions.
   ABS - Returns the absolute, positive value of the given numeric expression.
   SUM
   AVG
   AND
9. What is DDL?Data definition language (DDL) statements are SQL statements that support the definition or declaration of database objects (for example, CREATE TABLE, DROP TABLE, and ALTER TABLE).
   You can use the ADO Command object to issue DDL statements. To differentiate DDL statements from a table or stored procedure name, set the CommandType property of the Command object to adCmdText. Because executing DDL queries with this method does not generate any recordsets, there is no need for a Recordset object.
10. What is DML?Data Manipulation Language (DML), which is used to select, insert, update, and delete data in the objects defined using DDL
11. What are keys in RDBMS? What is a primary key/ foreign key?There are two kinds of keys.
    A primary key is a set of columns from a table that are guaranteed to have unique values for each row of that table.
    Foreign keys are attributes of one table that have matching values in a primary key in another table, allowing for relationships between tables.
12. What is the difference between Primary Key and Unique Key?Both primary key and unique key enforce uniqueness of the column on which they are defined. But by default primary key creates a clustered index on the column, where are unique creates a nonclustered index by default. Another major difference is that, primary key doesn't allow NULLs, but unique key allows one NULL only.
13. Define candidate key, alternate key, composite key?
    A candidate key is one that can identify each row of a table uniquely. Generally a candidate key becomes the primary key of the table. If the table has more than one candidate key, one of them will become the primary key, and the rest are called alternate keys.
    A key formed by combining at least two or more columns is called composite key.
14. What is the Referential Integrity?Referential integrity refers to the consistency that must be maintained between primary and foreign keys, i.e. every foreign key value must have a corresponding primary key value.
15. What are defaults? Is there a column to which a default can't be bound?
    A default is a value that will be used by a column, if no value is supplied to that column while inserting data. IDENTITY columns and timestamp columns can't have defaults bound to them.
16. What is Query optimization? How is tuning a performance of query done?
17. What is the use of shell commands? xp_cmdshellExecutes a given command string as an operating-system command shell and returns any output as rows of text. Grants nonadministrative users permissions to execute xp_cmdshell.
18. What is use of shrink database?Microsoft® SQL Server 2000 allows each file within a database to be shrunk to remove unused pages. Both data and transaction log files can be shrunk.
19. If the performance of the query suddenly decreased where you will check?
20. What is a pass-through query?Microsoft® SQL Server 2000 sends pass-through queries as un-interpreted query strings to an OLE DB data source. The query must be in a syntax the OLE DB data source will accept. A Transact-SQL statement uses the results from a pass-through query as though it is a regular table reference.
    This example uses a pass-through query to retrieve a result set from a Microsoft Access version of the Northwind sample database.SELECT *
    FROM OpenRowset('Microsoft.Jet.OLEDB.4.0',
    'c:\northwind.mdb';'admin'; '',
    'SELECT CustomerID, CompanyName
    FROM Customers
    WHERE Region = ''WA'' ')
21. How do you differentiate Local and Global Temporary table?You can create local and global temporary tables. Local temporary tables are visible only in the current session; global temporary tables are visible to all sessions. Prefix local temporary table names with single number sign (#table_name), and prefix global temporary table names with a double number sign (##table_name). SQL statements reference the temporary table using the value specified for table_name in the CREATE TABLE statement:
    CREATE TABLE #MyTempTable (cola INT PRIMARY KEY)
    INSERT INTO #MyTempTable VALUES (1)
22. How the Exists keyword works in SQL Server?USE pubs
SELECT au_lname, au_fname
FROM authors
WHERE exists
   (SELECT *
   FROM publishers
   WHERE authors.city = publishers.city)When a subquery is introduced with the keyword EXISTS, it functions as an existence test. The WHERE clause of the outer query tests for the existence of rows returned by the subquery. The subquery does not actually produce any data; it returns a value of TRUE or FALSE.
23. ANY?USE pubs
    SELECT au_lname, au_fname
    FROM authors
    WHERE city = ANY
    (SELECT city
    FROM publishers)
24. to select date part onlySELECT CONVERT(char(10),GetDate(),101)--to select time part onlySELECT right(GetDate(),7)
25. How can I send a message to user from the SQL Server? You can use the xp_cmdshell extended stored procedure to run net send command. This is the example to send the 'Hello' message to JOHN:EXEC master..xp_cmdshell "net send JOHN 'Hello'"To get net send message on the Windows 9x machines, you should run the WinPopup utility. You can place WinPopup in the Startup group under Program Files.
26. What is normalization? Explain different levels of normalization? Explain Third normalization form with an example?The process of refining tables, keys, columns, and relationships to create an efficient database is called normalization. This should eliminates unnecessary duplication and provides a rapid search path to all necessary information.
    Some of the benefits of normalization are:
• Data integrity (because there is no redundant, neglected data)
• Optimized queries (because normalized tables produce rapid, efficient joins)
• Faster index creation and sorting (because the tables have fewer columns)
• Faster UPDATE performance (because there are fewer indexes per table)
• Improved concurrency resolution (because table locks will affect less data)
• Eliminate redundancy

There are a few rules for database normalization. Each rule is called a "normal form." If the first rule is observed, the database is said to be in "first normal form." If the first three rules are observed, the database is considered to be in "third normal form." Although other levels of normalization are possible, third normal form is considered the highest level necessary for most applications.

1. First Normal Form (1NF)
• Eliminate repeating groups in individual tables
• Create a separate table for each set of related data.
• Identify each set of related data with a primary key.

Do not use multiple fields in a single table to store similar data.
Example

	Subordinate1	Subordinate2	Subordinate3	Subordinate4
Bob	Jim	Mary	Beth
Mary	Mike	Jason	Carol	Mark
Jim	Alan

Eliminate duplicative columns from the same table.  Clearly, the Subordinate1-Subordinate4 columns are duplicative. What happens when we need to add or remove a subordinate?

	Subordinates
Bob	Jim, Mary, Beth
Mary	Mike, Jason, Carol, Mark
Jim	Alan

This solution is closer, but it also falls short of the mark. The subordinates column is still duplicative and non-atomic. What happens when we need to add or remove a subordinate? We need to read and write the entire contents of the table. That’s not a big deal in this situation, but what if one manager had one hundred employees? Also, it complicates the process of selecting data from the database in future queries.
Solution:

	Subordinate
Bob	Jim
Bob	Mary
Bob	Beth
Mary	Mike
Mary	Jason
Mary	Carol
Mary	Mark
Jim	Alan

2. Second Normal Form (2NF)
• Create separate tables for sets of values that apply to multiple records.
• Relate these tables with a foreign key.

Records should not depend on anything other than a table's primary key (a compound key, if necessary).
For example, consider a customer's address in an accounting system. The address is needed by the Customers table, but also by the Orders, Shipping, Invoices, Accounts Receivable, and Collections tables. Instead of storing the customer's address as a separate entry in each of these tables, store it in one place, either in the Customers table or in a separate Addresses table.

3. Third Normal Form (3NF)
• Eliminate fields that do not depend on the key.

Values in a record that are not part of that record's key do not belong in the table. In general, any time the contents of a group of fields may apply to more than a single record in the table, consider placing those fields in a separate table.
For example, in an Employee Recruitment table, a candidate's university name and address may be included. But you need a complete list of universities for group mailings. If university information is stored in the Candidates table, there is no way to list universities with no current candidates. Create a separate Universities table and link it to the Candidates table with a university code key.
Another Example :

MemberId	Name	Company	CompanyLoc
1	John Smith	ABC	Alabama
2	Dave Jones	MCI	Florida

The Member table satisfies first normal form - it contains no repeating groups. It satisfies second normal form - since it doesn't have a multivalued key. But the key is MemberID, and the company name and location describe only a company, not a member. To achieve third normal form, they must be moved into a separate table. Since they describe a company, CompanyCode becomes the key of the new "Company" table.

The motivation for this is the same for second normal form: we want to avoid update and delete anomalies. For example, suppose no members from the IBM were currently stored in the database. With the previous design, there would be no record of its existence, even though 20 past members were from IBM!
Member Table

MemberId	Name	CID
1	John Smith	1
2	Dave Jones	2

Company Table

CId	Name	Location
1	ABC	Alabama
2	MCI	Florida

4. Boyce-Codd Normal Form (BCNF)A relation is in Boyce/Codd normal form if and only if the only determinants are candidate key. Its a different version of 3NF, indeed, was meant to replace it. [A determinant is any attribute on which some other attribute is (fully) functionally dependent.]
5. 4th Normal Form (4NF)A table is in 4NF if it is in BCNF and if it has no multi-valued dependencies. This applies primarily to key-only associative tables, and appears as a ternary relationship, but has incorrectly merged 2 distinct, independent relationships.
   Eg: This could be any 2 M:M relationships from a single entity. For instance, a member could know many software tools, and a software tool may be used by many members. Also, a member could have recommended many books, and a book could be recommended by many members.

Software

member

Book

6. The correct solution, to cause the model to be in 4th normal form, is to ensure that all M:M relationships are resolved independently if they are indeed independent.

Software

membersoftware

member

memberBook

book

7. 5th Normal Form (5NF)(PJNF)A table is in 5NF, also called "Projection-Join Normal Form", if it is in 4NF and if every join dependency in the table is a consequence of the candidate keys of the table.
8. Domain/key normal form (DKNF). A key uniquely identifies each row in a table. A domain is the set of permissible values for an attribute. By enforcing key and domain restrictions, the database is assured of being freed from modification anomalies. DKNF is the normalization level that most designers aim to achieve.

Remember, these normalization guidelines are cumulative.  For a database to be in 2NF, it must first fulfill all the criteria of a 1NF database.

27. If a database is normalized by 3 NF then how many number of tables it should contain in minimum? How many minimum if 2NF and 1 NF?
28. What is denormalization and when would you go for it?As the name indicates, denormalization is the reverse process of normalization. It's the controlled introduction of redundancy in to the database design. It helps improve the query performance as the number of joins could be reduced.
29. How can I randomly sort query results? To randomly order rows, or to return x number of randomly chosen rows, you can use the RAND function inside the SELECT statement. But the RAND function is resolved only once for the entire query, so every row will get same value. You can use an ORDER BY clause to sort the rows by the result from the NEWID function, as the following code shows:SELECT *
    FROM Northwind..Orders
    ORDER BY NEWID()
30. sp_whoProvides information about current Microsoft® SQL Server™ users and processes. The information returned can be filtered to return only those processes that are not idle.
31. Have you worked on Dynamic SQL? How will You handled “ (Double Quotes) in Dynamic SQL?
32. How to find dependents of a table?Verify dependencies with sp_depends before dropping an object
33. What is the difference between a CONSTRAINT AND RULE?Rules are a backward-compatibility feature that perform some of the same functions as CHECK constraints. CHECK constraints are the preferred, standard way to restrict the values in a column. CHECK constraints are also more concise than rules; there can only be one rule applied to a column, but multiple CHECK constraints can be applied. CHECK constraints are specified as part of the CREATE TABLE statement, while rules are created as separate objects and then bound to the column.
34. How to call a COM dll from SQL Server 2000?sp_OACreate - Creates an instance of the OLE object on an instance of Microsoft® SQL Server
    Syntax
    sp_OACreate progid, | clsid,
        objecttoken OUTPUT
        [ , context ]

context - Specifies the execution context in which the newly created OLE object runs. If specified, this value must be one of the following:
1 = In-process (.dll) OLE server only
4 = Local (.exe) OLE server only
5 = Both in-process and local OLE server allowed 

Examples

A. Use Prog ID - This example creates a SQL-DMO SQLServer object by using its ProgID.

DECLARE @object int

DECLARE @hr int

DECLARE @src varchar(255), @desc varchar(255)

EXEC @hr = sp_OACreate 'SQLDMO.SQLServer', @object OUT

IF @hr <> 0

BEGIN

   EXEC sp_OAGetErrorInfo @object, @src OUT, @desc OUT 

   SELECT hr=convert(varbinary(4),@hr), Source=@src, Description=@desc

    RETURN

END

B. Use CLSID - This example creates a SQL-DMO SQLServer object by using its CLSID.

DECLARE @object int

DECLARE @hr int

DECLARE @src varchar(255), @desc varchar(255)

EXEC @hr = sp_OACreate '{00026BA1-0000-0000-C000-000000000046}',

    @object OUT

IF @hr <> 0

BEGIN

   EXEC sp_OAGetErrorInfo @object, @src OUT, @desc OUT 

   SELECT hr=convert(varbinary(4),@hr), Source=@src, Description=@desc

    RETURN

END

35. Difference between sysusers and syslogins?sysusers - Contains one row for each Microsoft® Windows user, Windows group, Microsoft SQL Server™ user, or SQL Server role in the database.
    syslogins - Contains one row for each login account.
36. What is the row size in SQL Server 2000?8060 bytes.
37. How will you find structure of table, all tables/views in one db, all dbs?//structure of table
    sp_helpdb tbl_emp
    //list of all databases
    sp_helpdbORSELECT * FROM master.dbo.sysdatabases
    //details about database pubs. .mdf, .ldf file locations, size of databasesp_helpdb pubs
    //lists all tables under current databasesp_tablesORSELECT * FROM information_schema.tables WHERE (table_type = 'base table')ORSELECT * FROM sysobjects WHERE type = 'U' //faster
38. B-tree indexes or doubly-linked lists?
39. What is the system function to get the current user's user id? USER_ID(). Also check out other system functions like USER_NAME(), SYSTEM_USER, SESSION_USER, CURRENT_USER, USER, SUSER_SID(), HOST_NAME().
40. What are the series of steps that happen on execution of a query in a Query Analyzer?1) Syntax checking
    2) Parsing
    3) Execution plan
41. Which event (Check constraints, Foreign Key, Rule, trigger, Primary key check) will be performed last for integrity check?Identity Insert Check
    Nullability constraint
    Data type check
    Instead of trigger
    Primary key
    Check constraint
    Foreign key
    DML Execution (update statements)
    After Trigger
     
42. How will you show many to many relation in sql?Create 3rd table with 2 columns which having one to many relation to these tables.
43. When a query is sent to the database and an index is not being used, what type of execution is taking place?A table scan.
44. What is #, ##, @, @@ means?@@ - System variables
    @ - user defined variables
45. What is the difference between a Local temporary table and a Global temporary table? How is each one denoted?Local temporary table will be accessible to only current user session, its name will be preceded with a single hash (#mytable)
    Global temporary table will be accessible to all users, & it will be dropped only after ending of all active connections, its name will be preceded with double hash (##mytable)

TRANSACTION | SQL Server Interview Questions

1. What is Transaction?
A transaction is a sequence of operations performed as a single logical unit of work. A logical unit of work must exhibit four properties, called the ACID (Atomicity, Consistency, Isolation, and Durability) properties, to qualify as a transaction:

•  Atomicity - A transaction must be an atomic unit of work; either all of its data modifications are performed or none of them is performed.
•  Consistency - When completed, a transaction must leave all data in a consistent state. In a relational database, all rules must be applied to the transaction's modifications to maintain all data integrity. All internal data structures, such as B-tree indexes or doubly-linked lists, must be correct at the end of the transaction.
•  Isolation - Modifications made by concurrent transactions must be isolated from the modifications made by any other concurrent transactions. A transaction either sees data in the state it was in before another concurrent transaction modified it, or it sees the data after the second transaction has completed, but it does not see an intermediate state. This is referred to as serializability because it results in the ability to reload the starting data and replay a series of transactions to end up with the data in the same state it was in after the original transactions were performed.
• Durability - After a transaction has completed, its effects are permanently in place in the system. The modifications persist even in the event of a system failure.

2. After one Begin Transaction a truncate statement and a RollBack statements are there. Will it be rollbacked? Since the truncate statement does not perform logged operation how does it RollBack?
It will rollback.
 Given a SQL like
Begin Tran
   Select @@Rowcount
Begin Tran
   Select @@Rowcount
Begin Tran
   Select @@Rowcount
Commit Tran
   Select @@Rowcount
RollBack
   Select @@Rowcount
RollBack
   Select @@Rowcount
What is the value of @@Rowcount at each stmt levels?
Ans : 0 – zero.
@@ROWCOUNT - Returns the number of rows affected by the last statement.
@@TRANCOUNT - Returns the number of active transactions for the current connection.
Each Begin Tran will add count, each commit will reduce count and ONE rollback will make it 0.

VIEW | SQL Server Interview Questions

1. What is View? Use? Syntax of View?
A view is a virtual table made up of data from base tables and other views, but not stored separately.

• Views simplify users perception of the database (can be used to present only the necessary information while hiding details in underlying relations)
• Views improve data security preventing undesired accesses
•  Views facilite the provision of additional data independence

2. Does the View occupy memory space?
 There is No occupy memory space in Views

3. Can u drop a table if it has a view?
Views or tables participating in a view created with the SCHEMABINDING clause cannot be dropped. If the view is not created using SCHEMABINDING, then we can drop the table.

4. Why doesn't SQL Server permit an ORDER BY clause in the definition of a view?
SQL Server excludes an ORDER BY clause from a view to comply with the ANSI SQL-92 standard. Because analyzing the rationale for this standard requires a discussion of the underlying structure of the structured query language (SQL) and the mathematics upon which it is based, we can't fully explain the restriction here. However, if you need to be able to specify an ORDER BY clause in a view, consider using the following workaround:
USE pubs
GO
CREATE VIEW AuthorsByName
AS
SELECT TOP 100 PERCENT *
FROM authors
ORDER BY au_lname, au_fname
GO
The TOP construct, which Microsoft introduced in SQL Server 7.0, is most useful when you combine it with the ORDER BY clause. The only time that SQL Server supports an ORDER BY clause in a view is when it is used in conjunction with the TOP keyword. (Note that the TOP keyword is a SQL Server extension to the ANSI SQL-92 standard.)

LOCK | SQL Server Interview Questions

1. What are locks?
   Microsoft® SQL Server™ 2000 uses locking to ensure transactional integrity and database consistency. Locking prevents users from reading data being changed by other users, and prevents multiple users from changing the same data at the same time. If locking is not used, data within the database may become logically incorrect, and queries executed against that data may produce unexpected results. 
2. What are the different types of locks?SQL Server uses these resource lock modes.

Lock mode	Description
Shared (S)	Used for operations that do not change or update data (read-only operations), such as a SELECT statement.
Update (U)	Used on resources that can be updated. Prevents a common form of deadlock that occurs when multiple sessions are reading, locking, and potentially updating resources later.
Exclusive (X)	Used for data-modification operations, such as INSERT, UPDATE, or DELETE. Ensures that multiple updates cannot be made to the same resource at the same time.
Intent	Used to establish a lock hierarchy. The types of intent locks are: intent shared (IS), intent exclusive (IX), and shared with intent exclusive (SIX).
Schema	Used when an operation dependent on the schema of a table is executing. The types of schema locks are: schema modification (Sch-M) and schema stability (Sch-S).
Bulk Update (BU)	Used when bulk-copying data into a table and the TABLOCK hint is specified.

3. What is a dead lock? Give a practical sample? How you can minimize the deadlock situation? What is a deadlock and what is a live lock? How will you go about resolving deadlocks?Deadlock is a situation when two processes, each having a lock on one piece of data, attempt to acquire a lock on the other's piece. Each process  would wait indefinitely for the other to release the lock, unless one of the user processes is terminated. SQL Server detects deadlocks and terminates one user's process.
   A livelock is one, where a request for an exclusive lock is repeatedly denied because a series of overlapping shared locks keeps interfering. SQL Server detects the situation after four denials and refuses further shared locks. (A livelock also occurs when read transactions monopolize a table or page, forcing a write transaction to wait indefinitely.) 
4. What is isolation level?An isolation level determines the degree of isolation of data between concurrent transactions. The default SQL Server isolation level is Read Committed. A lower isolation level increases concurrency, but at the expense of data correctness. Conversely, a higher isolation level ensures that data is correct, but can affect concurrency negatively. The isolation level required by an application determines the locking behavior SQL Server uses.
   SQL-92 defines the following isolation levels, all of which are supported by SQL Server:
• Read uncommitted (the lowest level where transactions are isolated only enough to ensure that physically corrupt data is not read).
• Read committed (SQL Server default level).
• Repeatable read.
• Serializable (the highest level, where transactions are completely isolated from one another).

Isolation level	Dirty read	Nonrepeatable read	Phantom
Read uncommitted	Yes	Yes	Yes
Read committed	No	Yes	Yes
Repeatable read	No	No	Yes
Serializable	No	No	No

5. nolock? What is the difference between the REPEATABLE READ and SERIALIZE isolation levels?
   Locking Hints - A range of table-level locking hints can be specified using the SELECT, INSERT, UPDATE, and DELETE statements to direct Microsoft® SQL Server 2000 to the type of locks to be used. Table-level locking hints can be used when a finer control of the types of locks acquired on an object is required. These locking hints override the current transaction isolation level for the session.

Locking hint	Description
HOLDLOCK	Hold a shared lock until completion of the transaction instead of releasing the lock as soon as the required table, row, or data page is no longer required. HOLDLOCK is equivalent to SERIALIZABLE.
NOLOCK	Do not issue shared locks and do not honor exclusive locks. When this option is in effect, it is possible to read an uncommitted transaction or a set of pages that are rolled back in the middle of a read. Dirty reads are possible. Only applies to the SELECT statement.
PAGLOCK	Use page locks where a single table lock would usually be taken.
READCOMMITTED	Perform a scan with the same locking semantics as a transaction running at the READ COMMITTED isolation level. By default, SQL Server 2000 operates at this isolation level.
READPAST	Skip locked rows. This option causes a transaction to skip rows locked by other transactions that would ordinarily appear in the result set, rather than block the transaction waiting for the other transactions to release their locks on these rows. The READPAST lock hint applies only to transactions operating at READ COMMITTED isolation and will read only past row-level locks. Applies only to the SELECT statement.
READUNCOMMITTED	Equivalent to NOLOCK.
REPEATABLEREAD	Perform a scan with the same locking semantics as a transaction running at the REPEATABLE READ isolation level.
ROWLOCK	Use row-level locks instead of the coarser-grained page- and table-level locks.
SERIALIZABLE	Perform a scan with the same locking semantics as a transaction running at the SERIALIZABLE isolation level. Equivalent to HOLDLOCK.
TABLOCK	Use a table lock instead of the finer-grained row- or page-level locks. SQL Server holds this lock until the end of the statement. However, if you also specify HOLDLOCK, the lock is held until the end of the transaction.
TABLOCKX	Use an exclusive lock on a table. This lock prevents others from reading or updating the table and is held until the end of the statement or transaction.
UPDLOCK	Use update locks instead of shared locks while reading a table, and hold locks until the end of the statement or transaction. UPDLOCK has the advantage of allowing you to read data (without blocking other readers) and update it later with the assurance that the data has not changed since you last read it.
XLOCK	Use an exclusive lock that will be held until the end of the transaction on all data processed by the statement. This lock can be specified with either PAGLOCK or TABLOCK, in which case the exclusive lock applies to the appropriate level of granularity.

7. For example, if the transaction isolation level is set to SERIALIZABLE, and the table-level locking hint NOLOCK is used with the SELECT statement, key-range locks typically used to maintain serializable transactions are not taken.
   USE pubs
   GO
   SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
   GO
   BEGIN TRANSACTION
   SELECT au_lname FROM authors WITH (NOLOCK)
   GO

8. What is escalation of locks?Lock escalation is the process of converting a lot of low level locks (like row locks, page locks) into higher level locks (like table locks). Every lock is a memory structure too many locks would mean, more memory being occupied by locks. To prevent this from happening, SQL Server escalates the many fine-grain locks to fewer coarse-grain locks. Lock escalation threshold was definable in SQL Server 6.5, but from SQL Server 7.0 onwards it's dynamically managed by SQL Server.