SQL Server Interview Questions And Answers
If you are going to appear SQL Server Interview, you must prepare for it from the SQL Server Interview Questions And Answers that are given below on this page. These questions are for both Freshers and Experienced candidates. All the question and answer are described below by the team of recruitmentresult.com.
1. What is DBMS?
Ans. The database management system is a collection of programs that enables user to store, retrieve, update and delete information from a database.
2. What is RDBMS ?
Ans. Relational Database Management system (RDBMS) is a database management system (DBMS) that is based on the relational model. Data from relational database can be accessed or reassembled in many different ways without having to reorganize the database tables. Data from relational database can be accessed using an API , Structured Query Language (SQL).
3. What is SQL ?
Ans. Structured Query Language(SQL) is a language designed specifically for communicating with databases. SQL is an ANSI (American National Standards Institute) standard.
4. what is a field in a database ?
Ans. A field is an area within a record reserved for a specific piece of data.
Examples: Employee Name, Employee ID etc
5. What is a Record in a database ?
Ans. A record is the collection of values / fields of a specific entity: i.e. an Employee, Salary etc.
6.What Primary key and Unique key?
Ans. Primary key are used with Foreign key to enforce referential integrity. Unique constraints allow nulls to be inserted into the field. But there can’t be null in Primary key.
7.How to join two tables in Sql Server?
Ans. you can write following sql statement
select category.*, categoryparent.categoryparent from category, categoryparent where category.categoryparentid = categoryparent.autoid
I am assuming here that category.categoryparentid (foreign key) is the value of categoryparent.autoid (primary key).
8.What’s the difference between a primary key and a unique key?
Ans. Both primary key and unique 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.
9.What’s the difference between DELETE TABLE and TRUNCATE TABLE commands?
Ans. DELETE TABLE is a logged operation, so the deletion of each row gets logged in the transaction log, which makes it slow. TRUNCATE TABLE also deletes all the rows in a table, but it won’t log the deletion of each row, instead it logs the deallocation of the data pages of the table, which makes it faster. Of course.
10.What is a transaction and what are ACID properties?
Ans. A transaction is a logical unit of work in which, all the steps must be performed or none. ACID stands for Atomicity, Consistency, Isolation, Durability. These are the properties of a transaction. For more information and explanation of these properties, see SQL Server books online or any RDBMS fundamentals text book.
11.What’s the maximum size of a row?
Ans. 8060 bytes. Don’t be surprised with questions like ‘what is the maximum number of columns per table’. Check out SQL Server books online for the page titled: “Maximum Capacity Specifications”.
12.Difference Between Implict Transaction And Explict Transaction
Ans. Implicit Transaction is the auto commit. There is no beginning or ending of the transaction.
Explicit Transaction has the beginning, ending and rollback of transactions with the command
Commit Transaction and
In the explicit transaction, if an error occurs in between we can rollback to the begining of the transaction which cannot be done in implicit transaction.
13.what is the diff between a HAVING CLAUSE and a WHERE CLAUSE?
Ans. You can use Having Clause with the GROUP BY function in query and WHERE Clause is applied to each row before they are part of the GROUP BY function in a query.
14.What is trigger?
Ans. Triggers allows us to execute a batch of SQL code when either an insert, update or delete command is executed against a specific table.
Triggers are special types of stored procedures that are defined to execute automatically in place of or after data modifications. They can be executed automatically on the insert, delete and update operation.
15.What is the difference between Truncate and Delete?
Ans. Delete statement removes rows of a table one by one & deletes triggers on that table fires. But Truncate removes all rows by deallocating the data pages assigned to the table & only these deallocation are recorded in the transaction log.
16. When is the use of UPDATE_STATISTICS command?
Ans. This command is basically used when a large processing of data has occurred. If a large amount of deletions any modification or Bulk Copy into the tables has occurred, it has to update the indexes to take these changes into account. UPDATE_STATISTICS updates the indexes on these tables accordingly.
17. Which TCP/IP port does SQL Server run on? How can it be changed?
Ans. SQL Server runs on port 1433. It can be changed from the Network Utility TCP/IP properties.
18. What are the difference between clustered and a non-clustered index?
Ans. 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.
Non Clustered Index: 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.
Something That You Should Put An Eye On