1 . Types of Indexes – cluster index – Primary Key , Non Clustered index – , covering index
2. Fine Tuning — NOLOCK, Joins,
3. Types of Join — INNER, LEFT, RIGHT, CROSS JOIN, SELF JOIN
What is normalization?
The Process of organizing relational data into the database tables. There are five normal forms exist.
What is denormalization?
Denormalization is the reverse process of normalization.
It improve the query performance as the number of joins could be reduced.
It is basically merging the tables so that it will avoid more number of joins in the table.
Explain different isolation levels ?
The default isolation level is Read Committed.
other isolation levels : Read Uncommitted, Read Committed, Repeatable Read, Serializable.
What is a Stored Procedure?
Its a set of T-SQL statements combined to perform a single task. There should be only one select statement at the end of the procedure. This select statement will be return as an output.
Which TCP/IP port does SQL Server runs on?
what is one-to-one, one-to-many and many-to-many relationships in tables?
One-to-One – Relationship on the same table
One-to-Many – Relationship on two tables with primary key and foreign key relationships.
Many-to-Many – Relation ship is formed from more than one key column between two tables.
What is an user defined datatypes(UDD)?
User defined datatypes gives a descriptive name and format to the database tables.
example: suppose if a column named Phone with varchar(10) which appears in many tables.
you can create a UDD called Phone_code_Type of varchar(10) and use it across all the tables of the database.
What’s the difference between a primary key and a unique key?
Both primary key and unique enforce uniqueness of the column on which they are defined.
By default primary key creates a clustered index on the column
Unique key creates a nonclustered index by default
primary key doesn’t allow NULLs but unique key allows one NULL value.
Define candidate key, composite key , alternate key ?.
A candidate key is one that can identify each row of a table uniquely.
A candidate key becomes the primary key of the table.
A primary key formed by combining at least two or more columns is called composite key.
If a table has more than one candidate key then one become the primary key and the rest are called as alternate keys.
What is an default?
When there is no value supplied to a column. Then the value in the default gets used.
what is an ACID property?
ACID stands for Atomicity, Consistency, Isolation, Durability.
What’s the difference between DELETE and TRUNCATE TABLE statement?
DELETE TABLE : Delete is done on row by row basis and it logs the transaction which is slow.
TRUNCATE TABLE : Deletes all the rows at a time which makes it faster.
TRUNCATE TABLE can be rolled back.
What are constraints and its types ?.
Constraints are used to enable the RDBMS integrity of the database
Types of constraints: NOT NULL, CHECK, UNIQUE, PRIMARY KEY, FOREIGN KEY
What is an index?
Indexes in SQL Server are similar to the indexes in books.
It is used to fetch the information in faster way.
What are the types of indexes?
Clustered indexes : All the rows will be stored in the order of the primary key. There can be only one clustered index per table.
Non-clustered indexes : All the columns which are not primary key can be define as non clustered index and they are stored as B-tree structure with the leaf level nodes having the index key value.
What is a join?
Join is used to fetch information from different table by establishing the relationship
Types of joins:
1. INNER JOIN
2. OUTER JOIN classified as:
LEFT OUTER JOIN
RIGHT OUTER JOIN
FULL OUTER JOIN
3. CROSS JOIN
What is a self join?
A table which join on to itself to fetch some information in the same table
what is an SQL Profiler?
SQL Profiler utility is used to analyse the activities going on a particular server. Various options provided where users are allowed to choose which activity they need to trace
What is a Linked Server?
To add another server and to get information from that server we use the linked server
What is a cursors?
Cursors allow row-by-row prcessing of the resultset of a query. It will process one by one row like for loop
Types of cursors: Static, Dynamic, Forward-only, Keyset-driven.
What are the disadvantages of cursors?
Cursor will loop each and every record and require more resources and storage and results in more IO operation. we can avoid it by using correlated sub query.
What is a trigger?
Trigger is a set of sql statement which gets invoked automatically when an INSERT, UPDATE or DELETE operation takes place on a table.
What is BCP?
BulkCopy is used to copy huge volume of data from tables and views.
What is a deadlock? How to overcome it?
Deadlock is a situation when two processes wait indefinitely for the other to release the resource which forms a lock situation. use NOLOCK in the tables to avoid deadlock situation
When update statistics is required?
1. Change in the key values in the index (OR)
2. Huge volume of Data in an indexed column is either added, changed, or deleted
How to find which service pack currently installed on SQL Server machine?
— I thank all the teachers who taught me the concepts of SQL Server