Q.1-Which TCP/IP port does SQL Server uses?How we can change it?
SQL Server uses port 1433. you can change from the Network Utility TCP/IP properties.

Q.2-What is a PRIMARY KEY?
1-A primary key column always has unique values, it is used to uniquely identify rows in a table.
2-Primary key column can not have NULL value.
3-When you create a primary key on a table, it creates cluster index on that table.
4-A table can have only one primary key.

Q.3-What is UNIQUE KEY constraint?
A UNIQUE constraint enforces the uniqueness of the values in a column, so you can not insert duplicate values in this column.

Q.4-What's the difference between a PRIMARY KEY and a UNIQUE KEY?
Primary key and Uunique key enforces uniqueness of the column on which they are defined.
When we create primary key on a column it creates a clustered index on the column,but when we create unique on a column then it creates a nonclustered index by default.
primary key doesn't allow NULLs, but unique key allows NULL but one NULL only.

Q.5-What is a FOREIGN KEY ?
When a primary key column value is used in another table to add a relation between these two tables then other tables which is using that primary key column of first table is called a foreign key for second table. For example, the salary of an employee is stored in salary table. The relation is established via foreign key column "EmployeeID" of SALARY table which refers "EmployeeID" field in the EMPLOYEE table.



Q.6-What is a COMPOSITE KEY?
A Composite primary key is a set of columns whose values uniquely identify every row in a table. For example - if "HOUSE_NO" and "STREET_NO" in a table is combined to uniquely identify a row( in our example to find house) its called a Composite Key.

Q.7-What is CHECK Constraint?
A CHECK constraint is used to restrict the values that can be insert in a column. If we have applied check constraint on a column then only those values can be insert in that column which satisfy the check condition The check constraints are used to enforce domain integrity.

Q.8-What is the difference between a Local and a Global temporary table?
Local Temporary Table:A local temporary table exists only for the duration of a connection or, if defined inside a compound statement, for the duration of the compound statement.
Global Temporary Table:A global temporary table remains in the database permanently, but the rows exist only within a given connection. When connection is closed, the data in the global temporary table disappears. However, the table definition remains with the database for access when database is opened next time.

Q.9-What is the differentiate between a HAVING CLAUSE and a WHERE CLAUSE?
HAVING CLAUSE:In Select statement HAVING CLAUSE is used with GROUP BY clause,If GROUP BY is not used, HAVING works like a WHERE clause.
WHERE CLAUSE:It is applied to each row before they become a part of the GROUP BY function in a query.

Q.10-What is difference between DELETE and TRUNCATE?
1-Truncate keeps the lock on table while Delete keeps the lock on each row.
2-Truncate can not be rolled back while Delete can be.
3-Truncate resets the counter of the Identity column while Delete doesn't do so.
4-In Truncate, Trigger is not fired while the Trigger gets fire on Delete.
5-You can use WHERE clause on DELETE but can not use with TRUNCATE.



Q.11-What are COMMIT and ROLLBACK in SQL?
COMMIT statement is used to end the current transaction and once the COMMIT statement is exceucted the transaction will be permanent and undone.

Q.12-What is a Database Lock ?
Database lock tells a transaction that the data item is currently being used by other transactions.

Q.13-What is a stored procedure?
Stored Procedure group of SQL statement used to perform a task. The procedure can take inputs , process them and send back output.

Q.14-What are the advantages of Stored procedure?
1-Stored Procedure provides code reusability.
2-Stored procedure is cached in SQL Server’s memory,so helps in reducing network traffic and latency.
3-Stored procedures provide better security to your data.
4-Stored procedures help us in the encapsulation of the code. The code of the stored procedure can be changed without affecting application.

Q.15-What is Trigger?
Trigger is a group of SQL statements that executed when you INSERT, DELETE or UPDATE data in the table. Triggers are useful when you want to perform any automatic actions such as cascading changes through related tables, enforcing column restrictions, comparing the results of data modifications and maintaining the referential integrity of data across a database.

Q.16-What are Magic tables in SQL Server?
In SQL Server,"Inserted" and "Deleted" called Magic tables. Magic tables are not the physical tables, these are virtual tables and are used in triggers to retrieve the inserted, deleted or updated rows. When a record is inserted in the table that record will be there on INSERTED Magic table. When a record is deleted from that table that record will be there on DELETED Magic table. When a record is updated in the table that existing record will be there on DELETED Magic table and modified data will be there in INSERTED Magic table.

Q.17-What is a view?
The views are virtual tables, it contain queries that dynamically retrieve data when used. Views are used when we don't want to give access to physical table or restrict someone to view only some columns.

Q.18-What is difference between clustered and non clustered index?
1-A table can have only one Clustered Index which is created when primary key is created on a table but a table can have more than one non clustered indexes (maximum up to 999)
2-Cluster Index physically rearrange the table records but Non-Cluster index logically arrange the records.
3-The leaf level of clustered index is actual data pages of the table. Whereas in case of non-clustered index the leaf level is a pointer to the data.
4-Non-clustered index is faster than clustered index because when we use DML statement on clustered index, it has to update the index every time a DML statement is executed.

Q.19-What is Normalization?
Normalisation is the process of organizing the data in the database to minimize data redundancy. Normalization is a systematic approach of decomposing tables to eliminate data redundancy and Ensuring data is logically stored.

Q.20-Explain 1NF,2NF and 3NF in Database?
1NF:- 1NF says that two rows of a table must not contain repeating group of information i.e each set of column must have a unique value and each row should have a primary key that distinguishes it as unique So that multiple columns cannot be used to fetch the same row. The Primary key is created on a single column,but sometimes more than one column can be used to find a record in a table.

2NF:-A table is in 2NF if it is 1NF plus there must not be any partial dependency of any column on primary key. It means that for a table that has primary key, each column in the table that is not part of the primary key must depend upon the entire primary key for its existence.

3NF:-A table should be in 2NF plus if any column doesn't depend on primary should be removed from the table and moved to another table and create a relationship between these tables by foreign key.


Q.21-What is Function in SQL?
Function is a group of SQL statement used to perform a task. The function can take inputs , process them and send back output. Functions are of two types Single Value function and Tabular function. Functions are used to provides code reusability, functions are cached in SQL Server’s memory,so helps in reducing network traffic and latency. A function can have only SELECT statements it can not have INSERT,UPDATE and DELET statements.

Q.22-What is difference between Function and Stored Procedure in SQL?
1. Function must return a value,where as a procedure may or may not return a value.
2- Function can be called in SELECT statement but procedure can not be called in SELECT statement.
3- Funcation cannot have DML (insert,update, delete) statements but a procedure can.
4- A function can be called from a stored procedure but a procedure can not be called from a function.
5. Function returns 1 value only but Procedure can return multiple values (max 1024).
6. Stored procedure is precompiled execution plan where as functions are not.

Q.23-Explain ACID property of transaction?
1.Atomicity: Atomicity says that all statement in a transaction should be completed on none. if any statement in a transaction fails, then entire transaction should fail, and the database state should be left unchanged. An atomic system must guarantee atomicity in each and every situation, including power failures, errors, and crashes.
2-Consistency:The consistency property of transaction says that any transaction will bring the database from one valid state to another. Any data written to the database must be valid according to all defined rules, including constraints, cascades, triggers, and any combination thereof.
3-Isolation:The isolation property of transaction says that each transaction should be isolated to each other. execution of one transaction should not affect the other. If any erro occurs in one transaction it should not affect other running transactions.
4-Durability:Durability means that once a transaction has been committed, it will remain so, even in the event of power loss, crashes, or errors. Hence once the transaction is committed it must be recorded in a non-volatile memory.

Q.24-Explain JOINS in SQL Server?
SQL Server JOINS are used to retrieve data from multiple tables based on a common field between them. that common field shows the relationship between the tables.

Q.25-Explain INNER JOIN,LEFT OUTER JOIN and RIGHT OUTER JOIN JOINS in SQL Server?
1-INNER JOIN :INNER JOINS return all rows from multiple tables where the join condition is met.
2-LEFT OUTER JOIN:LEFT OUTER JOIN returns all rows from the LEFT table(specified at the LEFT of ON) and only those rows from the RIGHT table(specified at the RIGHT of ON) those met the JOIN condition.
3-RIGHT OUTER JOIN:RIGHT OUTER JOIN returns all rows from the RIGHT table(specified at the RIGHT of ON) and only those rows from the LEFT table(specified at the LEFT of ON) those met the JOIN condition.