SQL SERVER INTERVIEW QUESTIONS - Part I

What is COMMIT & ROLLBACK statement in SQL?
Commit statements helps in termination of the current transaction and do all the changes that occur in transaction persistent and this also commits all the changes to the database.COMMIT we can also use in store procedure.

ROLLBACK do the same thing just terminate the current transaction but one another thing is that the changes made to database are ROLLBACK to the database.

What is difference between OSQL and Query Analyzer ?
Both are the same but there is little difference OSQL is command line tool which is execute query and display the result same a query analyzer but query analyzer is graphical and OSQL is a command line tool. OSQL have not ability like query analyzer to analyze queries and show statics on speed of execution and other useful thing about OSQL is that its helps in scheduling.

What is SQL whats its uses and its component ?
The Structured Query Language (SQL) is foundation for all relational database systems. Most of the large-scale databases use the SQL to define all user and administrator interactions. QL is Non-Procedural language . Its allow the user to concentrate on specifying what data is required rather than concentrating on the how to get it.
The DML component of SQL comprises four basic statements:
* SELECT to get rows from tables
* UPDATE to update the rows of tables
* DELETE to remove rows from tables
* INSERT to add new rows to tables

Write some disadvantage of Cursor?
Cursor plays there row quite nicely but although there are some disadvantage of Cursor .Because we know cursor doing roundtrip it will make network line busy and also make time consuming methods. First of all select query gernate output and after that cursor goes one by one so roundtrip happen. Another disadvange of cursor are ther are too costly because they require lot of resources and temporary storage so network is quite busy.

What is Log Shipping and its purpose ?
In Log Shipping the transactional log file from one server is automatically updated in backup database on the other server and in the case when one server fails the other server will have the same DB and we can use this as the DDR(disaster recovery) plan.

Question: What are the null values in SQL SERVER ?
Before understand the null values we have some overview about what the value is. Value is the actual data stored in a particular field of particular record. But what is done when there is no values in the field. That value is something like .Nulls present missing information. We can also called null propagation.

What is difference between OSQL and Query Analyzer?
Both are same for functioning but there is a little difference OSQL is command line tool which execute query and display the result same a Query Analyzer do but Query Analyzer is graphical. OSQL have not ability like Query Analyzer to analyze queries and show statistics on speed of execution .And other useful thing about OSQL is that its helps in scheduling which is done in Query Analyzer with the help of JOB.

What are the different types of Locks ?
There are three main types of locks that SQL Server
(1)Shared locks are used for operations that does not allow to change or update data, such as a SELECT statement.
(2)Update locks are used when SQL Server intends to modify a page, and later promotes the update page lock to an exclusive page lock before actually making the changes.
(3)Exclusive locks are used for the data modification operations, such as UPDATE, INSERT, or DELETE.

Explain some SQL Server 2000 Query?
Here are some sql server 2000 query like Sql Insert Query, Delete Sql Query, Update Sql Query and Sql Create Query:
1) Sql Insert Query:
a) How to encrypt data by using Sql Insert Query.
insert into table_name(Tablecolumn1, tablecolumn2,. . . . .) values ('value1', pwdencrypt('value'),. . . .)

b) How to copy data from one table to another with the help of Sql Insert Query.
--: insert into table_name(column1,column2,. . . . ) select column1, column2, . . . . from table_name2

c) Sql Insert Query using where clause
--: insert into tablename(column1,column2) select column1,column2 from tablename2 where id=value.

What is 'Write-ahead log' in Sql Server 2000 ?
Before understanding it we must have an idea about the transaction log files. These files are the files which holds the data for change in database .
Now we explain when we are doing some Sql Server 2000 query or any Sql query like Sql insert query,delete sql query,update sql query and change the data in sql server database it cannot change the database directly to table .Sql server extracts the data that is modified by sql server 2000 query or by sql query and places it in memory.Once data is stores in memory user can make changes to that a log file is generated this log file is gernated in every five minutes of transaction is done. After this sql server writes changes to database with the help of transaction log files. This is called Write-ahead log.

What do u mean by Extents and types of Extends?
An Extent is a collection of 8 sequential pages to hold database from becoming fragmented. Fragment means these pages relates to same table of database these also holds in indexing. To avoid for fragmentation Sql Server assign space to table in extents. So that the Sql Server keep upto date data in extents. Because these pages are continuously one after another. There are usually two types of extends:-Uniform and Mixed.
Uniform means when extent is own by a single object means all collection of 8 ages hold by a single extend is called uniform.
Mixed mean when more then one object is comes in extents is known as mixed extents.

What is different in Rules and Constraints?
Rules and Constraints are similar in functionality but there is a An little difference between them. Rules are used for backward compatibility. One the most exclusive difference is that we can bind rules to a datatypes whereas constraints are bound only to columns. So we can create our own datatype with the help of Rules and get the input according to that.

What is defaults in Sql Server and types of Defaults?
Defaults are used when a field of columns is almost common for all the rows for example in employee table all living in delhi that value of this field is common for all the row in the table if we set this field as default the value that is not fill by us automatically fills the value in the field its also work as intellisense means when user inputing d it will automatically fill the delhi . There are two types of defaults object and definations.
Object deault:-These defaults are applicable on a particular columns . These are usually deined at the time of table designing.When u set the object default field in column state this column in automatically field when u left this filed blank.
Defination default:-When we bind the datatype with default let we named this as dotnet .Then every time we create column and named its datatype as dotnet it will behave the same that we set for dotnet datatype.