SqlServer

Frequently Asked Questions-Sql server Questions & Answers

Here,we are providing a list of frequently asked questions and various programming interviews to developers. I have also shared my answers for these questions for quick revision and easy to understand.

Q) What are the different index configurations a table can have?

A) A table can have different types of index configurations: No indexes , A clustered index , A clustered index and many nonclustered indexes , A nonclustered index , Many nonclustered indexes.

Q) What is constraints?

A) constraints are used to specify rules for the data in a table. It can be specified when the table is created (inside the CREATE TABLE statement) or after the table is created (inside the ALTER TABLE statement). There are following types of constraints.

Unique, Default, Check, Primary Key, Foreign Key, Not Null.

Q) What is Normalization?

A) Database normalization is a data design and organization process applied to data structures based on rules that help building relational databases. In relational database design, the process of organizing data to minimize redundancy is called normalization. Normalization usually involves dividing a database into two or more tables and defining relationships between the tables. The objective is to isolate data so that additions, deletions, and modifications of a field can be made in just one table and then propagated through the rest of the database via the defined relationships.

Q) What's the difference between a primary key and a unique key?

A) Both primary key and unique key enforces 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.

Q) What is the difference between DELETE and TRUNCATE

A) Delete command is used for to delete the data with out memory, which makes it slow. But Trunkate is used for to delete the memory with data, which makes it faster.

Q) What about foreign key? 
A) When a field is one table matches the primary key of another field is referred to as a foreign key. A foreign key is a field or a group of fields in one table whose values match those of the primary key of another table.

Q) What's the maximum size of a row?

A) 8060 bytes.

Q) What are the difference between clustered and a non-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 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.

Q. What is RDBMS?

 A) Relational Data Base Management Systems (RDBMS) are database management systems that maintain data records and indices in tables. Relationships may be created and maintained across and among the data and tables. In a relational database, relationships between data items are expressed by means of tables. Interdependencies among these tables are expressed by data values rather than by pointers. This allows a high degree of data independence. An RDBMS has the capability to recombine the data items from different files, providing powerful tools for data usage.

Q. What are Primary Keys and Foreign Keys?

A) Primary keys are the unique identifiers for each row. They must contain unique values and cannot be null. Due to their importance in relational databases, Primary keys are the most fundamental aspect of all keys and constraints. A table can have only one primary key.

Foreign keys are a method of ensuring data integrity and manifestation of the relationship between tables.

Q : What is a view?

A) A view is a virtual table that consists of fields from one or more real tables. Views are often used to join multiple tables or to control access to the underlying tables.

Q) What is the difference between a HAVING CLAUSE and a WHERE CLAUSE?

A) They specify a search condition for a group or an aggregate. But the difference is that HAVING can be used only with the SELECT statement. HAVING is typically used in a GROUP BY clause. When GROUP BY is not used, HAVING behaves like a WHERE clause. Having Clause is basically used only with the GROUP BY function in a query whereas WHERE Clause is applied to each row before they are part of the GROUP BY function in a query.

Q) What are the authentication modes in SQL Server? How can it be changed? 
A) 2 types of authentications in sql server. there are

Windows Authentication : It does not provides any security to database.Every user can access the database easly .So, it doesnot provide security to database.

Server Authentication : It provides the security based on username and password.

To change authentication mode in SQL Server click Start, Programs, Microsoft SQL Server and click SQL Enterprise Manager to run SQL Enterprise Manager from the Microsoft SQL Server program group. Select the server then from the Tools menu select SQL Server Configuration Properties, and choose the Security page.

Q) What is the Difference between a Function and a Stored Procedure?

A)

Q) What is cursor in SQL Server? 

A) A Cursor is a set of rows together with a pointer that identifies a current row.

Q) What is the outer join?

A) A join that includes rows even if they do not have related rows in the joined table is an Outer Join. You can create three different outer join to specify the unmatched rows to be included:

• Left Outer Join: In Left Outer Join, all the rows in the first-named table, i.e. “left” table, which appears leftmost in the JOIN clause, are included. Unmatched rows in the right table do not appear.

• Right Outer Join: In Right Outer Join, all the rows in the second-named table, i.e. “right” table, which appears rightmost in the JOIN clause are included. Unmatched rows in the left table are not included.

• Full Outer Join: In Full Outer Join, all the rows in all joined tables are included, whether they are matched or not.

Q) What is a Stored Procedure?

A) A stored procedure is a named group of SQL statements that have been previously created and stored in the server database. Stored procedures accept input parameters so that a single procedure can be used over the network by several clients using different input data. And when the procedure is modified, all clients automatically get the new version. Stored procedures reduce network traffic and improve performance. Stored procedures can be used to help ensure the integrity of the database.

Example:

create procedure procedure_name(@name varchar(50), @address varchar(50)) as

begin

insert into table_name values(@name,@address)

end