Frequently Asked Interview Questions And Answers SQL SERVER

What is a Database?                                                          A database is a structured collection of information that is organized so that it can easily be accessed, managed, and updated. The information stored in a database has to continue to exist even after the application that saved and manipulated the data have ceased to run.

What is a Relational Database?                                             A Relational Database is a collection of data items organized as a set of formally-described tables from which data can be accessed or reassembled in many different ways without having to reorganize the database tables. Each table contains one or more data categories in columns. Each row contains a unique instance of data for the categories defined by the columns.

What is DBMS ?                                                                       DBMS is a set of Prewritten programs that controls theorganization, storage, management, and retrieval of data in adatabase. DBMS are categorized according to their data structures or types. The DBMS accepts requests for data from the application program and instructs the operating system to transfer the appropriate data.

What is RDBMS ?                                                                     An RDBMS is a Relational Data Base Management System. which adds the additional condition that the system supports a tabular structure for the data with enforced relationships between the tables. Database in which all data is stored in Relations which are tables, with rows and columns. Each table is composed of records (called Tuples) and each recordis identified by a field (called attribute) containing a uniquevalue. Every table shares at least one field with another table in 'one to one,' 'one to many,' or 'many to many' relationships. These relationships allow the database users to access the data in almost an unlimited number of ways, and to combine the tables as building blocks to create complex and very largedatabases.

What is the Difference between DBMS and RDBMS?

DBMS	RDBMS
A Set of Prewritten Programs dedicated to control the data storage	A DBMS which Supports Tabular structure of Database. Only way to view the data is as a set of tables
Entity Based where all the tables are treated as different entities	Relation Based where tables are dependent and the user can establish various integrity constraints on these tables, so that the ultimate data used by the user remains correct
Single User system, Only one user can access one database at a time.	Multi-User system, Multiple users simultaneously access the same database
DBMS does not impose any constraints or security with regard to data manipulation It is the user's or programmer's  responsibility to ensure the ACID Properties of the database	RDBMS is more Secure than DBMS by defining the integrity constraints for the purpose of holding ACID Properties
Does not necessarily Support Client/Server Architecture & Distributed Databases	Supports Client/Server Architecture & Distributed Databases
Examples: FoxPro data files and earlier Ms Access	Examples: ORACLE, SQL Server and DB2

What is Normalization?                                                           ARelational database is basically composed of tables that contains Related data. So the Process of organizing this Related data into tables is referred to as Normalization. Alternatively, The process of refining tables, keys, columns, and relationships to create an efficient database is calledNormalization.

What are the advantages of Normalization ?                 Normalization Used to reduce Data Redundancy and permits Simple and rapid retrieval of all necessary information. Some of the benefits of Normalization are:                           

• Data Integrity (As there is No Redundant Data)
• Optimized Queries (As Normalized tables produce rapid and efficient  Joins)                                               
• Faster Index creation and sorting (As the tables have fewer   Columns)                                      
• Faster UPDATE Performance (As there are fewer indexes per table)                                       
• Improved Concurrency(As table locks will affect less Data)
• Eliminates Redundancy

What are the Disadvantages of Normalization ?             As a result of Normalization, Data is stored in multiple tables.To retrieve or modify the information, we have to establish joins across multiple tables.Joins are expensive from I/O standpoint.  Also, Multi table Joins can have adverse impact on the performance of the system.

What are Different levels of Normalization?                    There are a few rules for database normalization. Each rule is called a "Normal form." If the first rule is observed, the database is said to be in First Normal Form. Similarly, If the first three rules are observed, then the database is considered to be in Third Normal Form. Although other levels of normalization are possible, Third normal form is considered to be the highest level necessary for most applications.

First Normal Form(1NF):    Remove Repetitive groups  from Record Design.For this,Create a separate Table for each set of Related data and Identify each set of Related data with a primary key.

Second Normal Form(2NF):  Each and every field is Functionally Dependant on Primary Key. Here, Create separate tables for sets of values that apply to multiple records and Relate these tables with a foreign key. Records should not depend on anything other than a table's primary key. 

Third Normal Form(3NF): Transitive dependencies are removed from Record Design. For this,Eliminate fields that do not depend on the key.

Boyce-Codd Normal Form(BCNF): A relation is in Boyce/Codd Normal Form if and only if the only determinants are candidate key. (A determinant is any attribute on which some other attribute is (fully) functionally dependent.)

4th Normal Form(4NF): A table is in 4NF if it is in BCNF and if it has No multi-valued dependencies. 

5th Normal Form(5NF or PJNF): A table is in 5NF, also called "Projection-Join Normal Form", if it is in 4NF and if every join dependency in the table is a consequence of the candidate keys of the table.

Domain/key Normal Form(DKNF): A key uniquely identifies each row in a table. A domain is the set of permissible values for an attribute. By enforcing key and domain restrictions, the database is assured of being freed from modification anomalies. DKNF is the normalization level that most designers aim to achieve.

How do you implement one-to-one, one-to-many and many-to-many relationships while designing tables?One-to-One relationship can be implemented as a single table and rarely as two tables with primary and foreign key relationships. One-to-Many relationships are implemented by splitting the data into two tables with primary key and foreign key relationships. Many-to-Many relationships are implemented using a junction table with the keys from both the tables forming the composite primary key of the junction table.

What is DeNormalization and when would you go for it?DeNormalization is the Reverse process of Normalization. It allows the designers to keep redundant data in the system. This improves the query performance as the number of joins could be reduced. We will go for the DeNormalization, where the data does not change much frequently.

What are all the Basic things we need to observe before DeNormalizing a Database?                                                

• Be sure that we have a good Over all understanding of logical design of system
• Don’t make an attempt to DeNormalize entire Database at a time
• Determine whether we need virtual columns
• Understand Data Integrity issues & storage techniques for the data
• Determine the frequency at which data might change

What are the Essential DeNormalization Techniques?Keeping Redundant Data                                            Using Virtual Columns                                              Performing Horizontal Data Partition                     Performing Vertical Data Partition

What are Constraints? Explain Different types of Constraints?                                                                         Constraints enable the RDBMS to enforce the integrity of the database automatically, without creating the triggers, rule or defaults. Again,Constraints can defined as Restrictions Placed either at a Column level or Table level which ensures that the data meets certain Data integrity rules

Types of constraints: NOT NULL, CHECK, UNIQUE, PRIMARY KEY, FOREIGN KEY.

What is  Entity Integrity Constraint?                              Entity Integrity Constraint ensures that each and every row should have a unique value for a column or combination of columns.

What is  Domain Integrity Constraint?                              Domain Integrity Constraint refers to a pool of Data, for a column of a table, from which the column will take its values.

Define Candidate key, Primary Key,  Alternate key and Composite key.                                                                        A Candidate key is one that can identify each row of a table uniquely. Generally a candidate key becomes the primary key of the table. If the table has more than one candidate key, one of them will become the primary key, and the rest all are called alternate keys. A key formed by combining at least two or more columns is called composite key.

Define Foreign key                                                      When Primary key of one table appears as an attribute in another table, then it is called Foreign key in the second table.

What is the Referential Integrity? Referential integrity refers to the consistency that must be maintained between primary and foreign keys. Referential Integrity ensures that for every value of foreign key, there is a matching value of primary key.

What is the Difference between a Primary Key and a Unique Key?                                                                              Both primary key and Unique key enforce uniqueness of the column on which they are defined. The differences between these two are as shown below:

Primary Key	Unique Key
Does not  Allow Null Values	Allows Null Values
Only one Primary key exists in a table	More than one Unique key exists in a table
Creates a Clustered index on the column	Creates a Non Clustered Index on the column

What is DDL, DML, DCL and TCL Commands.               Data Definition Language(DDL): Data Definition Language statements are used to define the database structure or schema.
Ex : CREATE,ALTER,DROP,TRUNCATE,RENAME

Data Manipulation Language(DML) :  Data Manipulation Language statements are used for manipulate or manage the data within the schema objects
Ex : INSERT,UPDATE,DELETE

Data Control Language(DCL) :  Data Control Language statements are used to create roles, permissions, and referential integrity
Ex : GRANT,REVOKE

Transaction Control Language(TCL) : Transaction Control Language statements are used to manage different transactions occurring within a database.
Ex : COMMIT,SAVEPOINT,ROLLBACK

What is a Transaction?                                                             Transaction can be defined as a logical unit of work a grouping of one or more DBMS commands that are treated as a whole.

What are ACID Properties.                                                   Atomicity                                                      Consistency                                                        Isolation                                                              Durability

Explain Different Isolation Levels                                      An isolation level determines the degree of isolation of data between concurrent transactions.                                The Isolation levels are :

• Read Committed
• Read Uncommitted
• Repeatable Read
• Serializable

What are the Different Data types  in SQL Server ?Different types of Data types :                                 Exact & Approximate Numerics :                           TinyInt, SmallInt, Int, BigInt, Numeric, Decimal, Float,Real,Bit

Character & Unicode Character Strings :                     Char, Varchar, text, nchar, nVarchar, ntext                     

Binary Strings :                                                           Binary, varbinary, Image                                                

Date and time :                                         SmallDatetime, Datetime                                            

Monetary :                                                            SmallMoney, Money                                               

Other Data Types :                                               Cursor, unique identifier, Timestamp, Rowversion, Sysname, table, sql_variant, xml,User Defined Data type

How many bytes will TinyInt, SmallInt, Int and BigInt  Occupy?                                                                                TinyInt, SmallInt, Int and BigInt will Occupy 1,2,4,8 bytes  respectively.

What is the Difference between Numeric and Decimal?    In general,There is no difference between Numeric and Decimal. we can use both of them interchangeably to store integer and floating-point numbers scaled from 1 to 38 places, inclusive of both sides of a decimal. Generally we will use this data type to control the accuracy of our calculations in terms of the number of decimal digits.

What is the Difference between Float and Real?           The only difference between float and real data types are their minimum and maximum values and their storage values. Float ranges from -1.79E + 308 to 1.79 + 308, while real ranges from     -3.40E + 38 to 3.40E + 38. The storage size of Float is 8 bytes while that of Real it is 4 bytes. The real data type is the same as float(24) — a floating data type with 24 digits to the right of the decimal point.

What is a Bit data type?                                                   Bit is used to store the logical information like True/False or  Yes/No. These Information will be stored as an Integer data type that can take a value of 1, 0, or NULL. Bit Occupies 1 byte for every 8 bit columns.

Difference Between Char/Varchar/nvarchar datatype?Char[(n)]     - Fixed-length, Non-Unicode character data with length of n bytes. n must be a value from 1 through 8,000. Storage size is n bytes.
Varchar[(n)] - Variable-length, Non-Unicode character data with length of n bytes. n must be a value from 1 through 8,000. Storage size is the actual length in bytes of the data entered, not n bytes. The data entered can be 0 characters in length.                                                            nvarchar(n)  - Variable-length, Unicode character data of n characters. n must be a value from 1 through 4,000. Storage size, in bytes, is two times the number of characters entered. The data entered can be 0 characters in length.                                                                     

What is the Difference between text and Image Data type?
Individual text, ntext, and image values can be a maximum of 2GB, which is too long to store in a single data row.All that is stored in the row is not the actual data but it is a pointer to the database pages that contain the Actual data. Coming to the difference between text and Image, Text data type is used for character data for storing more than 8000 characters. Image is used for binary large objects (BLOBs) such as digital images.

Name the Datatype which cannot be assigned to a table column.                                                              Cursor data type is the only system data type that cannot be assigned to a table column.

What is a Cursor? Is it a Data type or database object? 
Cursor data type is the only system data type that cannot be assigned to a table column in a CREATE TABLE statement. It can be used only with variables and stored procedure output parameters that contain a reference to a cursor. That means,  It is either declared with the FOR clause that will bind it to a SELECT statement or it will act as an alias for another cursor, with the SET statement.

What is UniqueIdentifier Data type?                            The UniqueIdentifier data type stores 16-byte binary values that operate as globally unique identifiers (GUIDs). A GUID is a unique binary number; no other computer in the world will generate a duplicate of that GUID value. The main use for a GUID is for assigning an identifier that must be unique in a network that has many computers at many sites

What is a Table Data type?
The Table data type is an alternative way to create a temporary table. The variable declaration requires the column definition for the table and it creates a temporary table in tempdb.Table datatype is used to store a result set for later processing. We cannot use this datatype for a column in a CREATE TABLE statement. You should use DECLARE @local_variable to declare variables of type table. Table variables should be used instead of temporary tables, whenever possible, because table variables are cleaned up automatically at the end of the function, stored procedure, or batch in which they are defined and these table variables require less locking and logging resources.

What is the difference between Timestamp and Rowversion?                                                                   Timestamp and Rowversion are two different names for the exact same data type. Timestamp is the synonym for the Rowversion data type. Microsoft deprecated timestamp data type and replaced the same with Rowversion In SQL 2005.In general These types has nothing at all to do with dates or times.Rowversion Is a data type that exposes automatically generated, unique binary numbers within a database and generally used as a mechanism for version-stamping table rows.The storage size is 8 bytes.

What is the difference between UniqueIdentifier  and Identity?

TIMESTAMP	UNIQUEIDENTIFIER
Size of TIMESTAMP value is 8 bytes	size of UNIQUEIDENTIFIER is 16 bytes
not based on system date or time	based on the computer's MAC addresses and system date time.
purpose to TIMESTAMP is to track the operation in tables on the database level	purpose is to have a unique value assigned to a row. It remains unique in any system in the world.
It should not be a primary key	it may be a primary key

What is SQL_variant Data type?                              Sql_variant data type is used to store values of various SQL Server supported data types, except text, ntext, timestamp, and sql_variant. The maximum length of sql_variant data type is 8016 bytes. You can store in one column of type sql_variant, the rows of different data types. For example in the Address column of sql_variant data type, we can store int, bigint, char and varchar values.

What is User defined data type?                                 User defined Data type can be used to create custom data types that are based on system data types using the system stored procedure sp_addtype.

What is Sysname datatype?                                              sysname data type is used for table columns, variables, and stored procedure parameters that store object names.Sysname is functionally the same as nvarchar(128) except that, sysname is NOT NULL.

What are the Different Types of Database Objects?Table, View, Procedure, User Defined Function, Trigger, Rule, Check Constraint, Default Constraint, PRIMARY KEY  Constraint, FOREIGN KEY Constraint, UNIQUE Constraint, Synonym, System Base Table