Here this article is about SQL Server Top 10 Interview Questions which are very useful for the seekers in the future when you attend interview regarding SQL Server. These Question and answers are explained in a brief way and in a simple manner where the readers can understand easily without any difficulties. After reading these please share your testimonial with us. All the best
Introduction to SQL Server
SQL Server is Microsoft’s relational database management system (RDBMS) Generically, any database management system (DBMS) that can respond to queries from client machines formatted in the SQL language. As a database, it is a software product whose primary function is to store and retrieve data as requested by other software applications. It is a full-featured database primarily designed to complete against competitors Oracle Database and MySQL.
1) Define trigger?
Ans: A trigger allows us to execute a batch of SQL code when 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.
2) What is Clustered & Non-Clustered Index?
Ans: Clustered Index: Clustered index physically rearrange the
data that users inserts in your tables. It is nothing but a dictionary type
data where actual data remains.
Non-Clustered Index: It Non-Clustered Index contains
pointers to the data that is stored in the data page. It is a kind of index
backside of the book where you see only the reference of a kind of data.
3) Mention the types of triggers are there?
Ans: There are four types of triggers. They are
- Insert
- Delete
- Update
- Instead of
4) Define constraints?
Ans: SQL Server users constraints to enforce limitations on the
data that can be entered into a particular column in table. There are following
types of constraints. Unique, Default, Check, Primary Key, Foreign Key, Not
Null.
5) What is the difference between Truncate and Delete?
Ans: Delete statement removes rows of a table one by one &
delete triggers on that table fires. But Truncate removes all rows by de-allocating
the data pages assigned to the table & only these deallocation are recorded
in the transaction log.
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) What is 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 de-allocation of the data pages of the table, which
makes it faster.
8) Define 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, and 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.
9) 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".
10) What is the difference between Primary key Constraint and
Unique key Constraint in SQL Server?
Ans: Unique Key Constraint:
- The column values should retain uniqueness.
- It allows null values in the column.
- It will create non-clustered index by default.
- Any number of unique constraints can be added to a table.
Primary Key Constraint:
- Primary key will create column data uniqueness in the table.
- It Wont allow Null values.
- By default Primary key will create clustered index.
- Only one Primary key can be created for a table.
- Multiple columns can be consolidated to form a single primary key.
