SQL Server Interview Questions

SQL Server Interview Questions

RDBMS Vs DBMS:
Database management system is considered as the system to store the data for easy retieval and the data were relatively stored as a group. Whereas, Relational database management system will include a relationship between tables (using constraints). The relationship will be based on their logical relations. Considering,
There are two table "employee" which will hold employee information like, employee id, employee dob, where he is working. Another table "employee details" which may hold his personal information like blood group, identification etc..,
For these two tables, there should be a key which will form a relation ship between these two tables. Its nothing but RDBMS. (Eg: SQL Server, Oracle)

SQL Server Vs MS access:
I used to see this question acess vs sql server,
MS access is a RDBMS and can be considered as client level database. It comes along with Microsoft office package. The database will be formed based on filesystem concept, so it wont support multiuser environment. Its preferred for very small databases. It developed on Jet engine.
SQL Server is a RDBMS and works as a Server level database. It will work for a large group of Users with 24/7 availability and its preferred for high end system with huge data and huge support. Its developed on sql server engine.
Normalization:
Normalization is a very important consideration in designing the databases. Normalization includes various steps like,
1. First normal form
2. Second normal form
3. third normal form
4. Backus naur form
5. Extended Backus naur form
6. Fifth normal form
7. Sixth normal form
The above normal forms are used to avoid redundancy of data. To achieve these, we have to segregate the columns to different tables. So that, duplication should not be there, partial dependency should be avoided etc..,

Denormalization:
As the name indicates, it's opposite to normalisation. Introducing redundancy into the database is referred to as denormalization. The database becomes too complex and too many joins may occur to fetch the data. In that case, we used to prefer this denomalization concept. Narrow idea - Joining the divided or segregated tables.

MSDE vs SQL Server :
MSDE is the downgrade version of SQL Server, the same code with initial release of SQL Server was named as MSDE (Microsoft Desktop Enginer).

Extent Vs Page:
Pages are 8 KB size smallest unit to store the data. Inturn, 8 Pages will form the extent.

Delete VS Truncate:
Delete will delete row by row in the table. The syntax is,
delete from tablename
We can give where conditions for deleting the data.
Each delete will be logged in the log file.
Its a DML statement

Trucate will deallocate the reference in the pages instead of deleting the data.
Its DDL statement. The syntax is,
Truncate table tablename
We can provide truncate with tables having foreign key relationships. (Because it wont do any cascade delete on the table).
It wont log any information in the log file.
Delete or truncate the data at a stretch and wont do any row by row manipulation.