What is #temp table and @table variable in SQL Server?

#temp Table (Temporary Table)

temp table is a temporary table that is generally created to store session specific data. Its kind of normal table but it is created and populated on disk, in the system database tempdb — with a session-specific identifier packed onto the name, to differentiate between similarly-named #temp tables created from other sessions.

The data in this #temp table (in fact, the table itself) is visible only to the current scope. Generally, the table gets cleared up automatically when the current procedure goes out of scope, however, we should manually clean up the data when we are done with it.

Syntax:

-- create temporary table

CREATE TABLE #myTempTable (

AutoID int,

MyName char(50) )

-- populate temporary table

INSERT INTO #myTempTable (AutoID, MyName )

SELECT AutoID, MyName

FROM myOriginalTable

WHERE AutoID <= 50000

-- Drop temporary table

drop table #myTempTable

@table variable
table variable is similar to temporary table except with more flexibility. It is not physically stored in the hard disk, it is stored in the memory. We should choose this when we need to store less 100 records.

Syntax:

DECLARE @myTable TABLE (

AutoID int,

myName char(50) )

INSERT INTO @myTable (AutoID, myName )

SELECT YakID, YakName

FROM myOriginalTable

WHERE AutoID <= 50

-- to select the data from Temp variable

SELECT * FROM @myTable

What is a deadlock?
Deadlock is a situation when two processes, each having a lock on one piece of data, attempt to acquire a lock on the other’s piece. Each process would wait indefinitely for the other to release the lock, unless one of the user processes is terminated.
SQL Server detects deadlocks and terminates one user’s process

Difference between varchar and char:
varchar are variable length strings with a maximum length specified. If a string is less than the maximum length, then it is stored verbatim without any extra characters.
char are fixed length strings with a set length specified. If a string is less than the set length, then it is padded with extra characters so that it's length is the set length.

Use varchar when your strings do not have a fixed length (e.g. names, cities, etc.)

Use char when your strings are always going to be the same length (e.g. phone numbers, zip codes, etc).

What is Subquery in SQL Server?
A subquery is a query that is nested inside a SELECT, INSERT, UPDATE, or DELETE statement, or inside another subquery. Subquery is an inner query or inner select, while the statement containing a subquery is also called an outer query or outer select.

What are magic tables?
Sometimes we need to know about the data which is being inserted/deleted by triggers in database. With the insertion and deletion of data, tables named “INSERTED” and “DELETED” gets created in SQL Server which contains modified/deleted data.
Here, “INSERTED” and “DELETED” tables are called magic tables.

What is COMMIT and ROLLBACK statement in SQL
Commit statement helps in termination of the current transaction and do all the changes that occur in transaction persistent and this also commits all the changes to the database.
ROLLBACK do the same thing just terminate the currenct transaction but one another thing is that the changes made to database are ROLLBACK to the database.

How do we find the duplicate records of a table?
Using the query

SELECT empname, COUNT(*) AS n FROM employee GROUP BY empname HAVING COUNT(*)>1

What is join??

An SQL JOIN clause is used to combine rows from two or more tables, based on a common field between them.

There are many types of join.

1. Inner Join

· Equi-join

· Natural Join

2. Outer Join

· Left outer Join

·Right outer join

·Full outer join

3. Cross Join

4. Self Join

1) Inner Join

The join that displays only the rows that have a match in both the joined tables is known as inner join.

select e1.Username,e1.FirstName,e1.LastName,e2.DepartmentName _

from Employee e1 inner join Departments e2 on e1.DepartID=e2.id

1.1 Equi-Join

Equi join is a special type of join in which we use only equality operator. Hence, when you make a query forjoin using equality operator, then that join query comes under Equi join. Equi join has only (=) operator in join condition.

1.2 Natural-Join

The Natural join is same as our Equi join but only the difference is it will restrict to display redundant values

SELECT * FROM table_name1 t1 NATURAL JOIN table_name2 t2

2) Outer Join

Outer join returns all the rows of both tables whether it has matched or not.

2.1 Left Outer join

Left join displays all the rows from first table and matched rows from second table like that..

SELECT * FROM Employee e1 LEFT OUTER JOIN Departments e2 ON e1.DepartID = e2.id

2.2 Right Outer join

Right outer join displays all the rows of second table and matched rows from first table like that..

SELECT * FROM Employee e1 RIGHT OUTER JOIN Departments e2 ON e1.DepartID = e2.id

2.3 Full Outer join

Full outer join returns all the rows from both tables whether it has been matched or not...

SELECT * FROM Employee e1 FULL OUTER JOIN Departments e2 ON e1.DepartID = e2.id

3) Cross Join

A cross join that produces Cartesian product of the tables that are involved in the join. The size of a Cartesian product is the number of the rows in the first table multiplied by the number of rows in the second table like this.

SELECT * FROM Employee cross join Departments e2

4) Self Join

Joining the table itself called self join. Self join is used to retrieve the records having some relation or similarity with other records in the same table. Here, we need to use aliases for the same table to set a self join between single table and retrieve records satisfying the condition in where clause.

SELECT e1.Username,e1.FirstName,e1.LastName from Employee e1 _

inner join Employee e2 on e1.id=e2.DepartID

Cursor:

Cursor is a database object used by application to manipulate data in a set on a row by row basis, its like recordset and visual basic.

Declare @track table (awbno nvarchar(100),

shipmentstatus nvarchar(250),

recievedby nvarchar(250))

declare cur cursor for

select distinct awbno from customersupporttbl where awbno IN (Select * from Split(@awbno,','))

open cur

declare @id bigint

fetch next from cur into @id

while (@@FETCH_STATUS = 0)

begin

Insert into @track(awbno,shipmentstatus,recievedby)

select top 1 awbno,shipmentstatus,receivedby from customersupporttbl where awbno=@id order by id desc

fetch next from cur into @id

end

close cur

deallocate cur

select * from @track order by awbno asc

Case Condition:-

SELECT   ProductNumber, Category =

      CASE ProductLine

         WHEN 'R' THEN 'Road'

         WHEN 'M' THEN 'Mountain'

         WHEN 'T' THEN 'Touring'

         WHEN 'S' THEN 'Other sale items'

         ELSE 'Not for sale'

      END,

   Name

FROM Production.Product

ORDER BY ProductNumber;

GO

SQL GROUP Functions

Group functions are built-in SQL functions that operate on groups of rows and return one value for the entire group. These functions are: COUNT, MAX, MIN, AVG, SUM, DISTINCT

SQL COUNT (): This function returns the number of rows in the table that satisfies the condition specified in the WHERE condition. If the WHERE condition is not specified, then the query returns the total number of rows in the table.

For Example: If you want the number of employees in a particular department, the query would be:

SELECT COUNT (*) FROM employee WHERE dept = 'Electronics';

The output would be '2' rows.

If you want the total number of employees in all the department, the query would take the form:

SELECT COUNT (*) FROM employee;

SQL DISTINCT(): This function is used to select the distinct rows.

For Example: If you want to select all distinct department names from employee table, the query would be:

SELECT DISTINCT dept FROM employee;

To get the count of employees with unique name, the query would be:

SELECT COUNT (DISTINCT name) FROM employee;

SQL MAX(): This function is used to get the maximum value from a column.

To get the maximum salary drawn by an employee, the query would be:

SELECT MAX (salary) FROM employee;

SQL MIN(): This function is used to get the minimum value from a column.

To get the minimum salary drawn by an employee, he query would be:

SELECT MIN (salary) FROM employee;

SQL AVG(): This function is used to get the average value of a numeric column.

To get the average salary, the query would be

SELECT AVG (salary) FROM employee;

SQL SUM(): This function is used to get the sum of a numeric column

To get the total salary given out to the employees,

SELECT SUM (salary) FROM employee;

select * from emp where empname Not like '[^1-9]'

// Get Only Find the 1to9 number available in record column

Merge:

Merge statement is one of the interesting T-Sql enhancements of Sql Server 2008. With Merge statement we can very efficiently perform multiple DML operations like INSERT, UPDATE and DELETE on the target table data based on Source table data and the join condition specified between them.

Example:

MERGE StudentTotalMarks AS x USING (SELECT StudentID,StudentName FROM StudentDetails) AS y
ON x.StudentID = y.StudentID
WHEN MATCHED AND x.StudentMarks > 250 THEN DELETE
WHEN MATCHED THEN UPDATE SET x.StudentMarks = x.StudentMarks +25
WHEN NOT MATCHED THEN INSERT(StudentID,StudentMarks) VALUES(sd.StudentID,25);

Set Operators in SQL

SQL supports few Set operations to be performed on table data. These are used to get meaningful results from data, under different special conditions.

UNION Example

The following statement combines the results of two queries with the UNION operator, which eliminates duplicate selected rows.

UNION ALL Example
The UNION operator returns only distinct rows that appear in either result, while the UNION ALL operator returns all rows. The UNION ALL operator does not eliminate duplicate selected rows:

INTERSECT Example
The following statement combines the results with the INTERSECT operator, which returns only those rows returned by both queries:

MINUS Example
The following statement combines results with the MINUS operator, which returns only rows returned by the first query but not by the second:

For Example:

Table1 Table2

[1,2,3,4,5] [3,4,5,6,7]

UNION={1,2,3,4,5,6,7}

UNION All={1,2,3,4,5,3,4,5,6,7}

INTERSECT={3,4,5}

MINUS={1,2}

View:

A view is defined by a query and only shows the data which is defined at the time of creation of the view. If you hide particular columns then use the view. A view hides the complexity of the underlying database schema, or customizes the data and schema for a set of users.

Simple View

A Simple View is a user defined view. A Simple View takes the data from a single table and has no function. The user defined view is created by the user as needed. We are showing a Simple View using a student table.

Complex View

A Complex View is created for multiple tables and contains functions and group data.

Constraint in SQL – Server

Enforce rules on the data table whenever a row is inserted, updated, deleted from that table the constraint must be satisfied for the operation to succeed.

NOT NULL - Indicates that a column cannot store NULL value
UNIQUE - Ensures that each row for a column must have a unique value
PRIMARY KEY - A combination of a NOT NULL and UNIQUE. Ensures that a column (or combination of two or more columns) have an unique identity which helps to find a particular record in a table more easily and quickly
FOREIGN KEY - Ensure the referential integrity of the data in one table to match values in another table
CHECK - Ensures that the value in a column meets a specific condition
DEFAULT - Specifies a default value when specified none for this column

Column Level Constraint:

CREATE TABLE Products

(

ProductID INT CONSTRAINT pk_products_pid PRIMARY KEY,

ProductName VARCHAR(25)

);

Table Level Constraint:

CREATE TABLE Products

(

ProductID INT,

ProductName VARCHAR(25),

CONSTRAINT pk_products_pid PRIMARY KEY(ProductID)

);

Forign key Constraint:

Create table emp

(

Empid int,

Name nvarchar(50),

Constraint empconfk FOREIGN KEY(empid) References empdept(empid)

)

Index:

Index can be thought as index of the book that is used for fast retrieval of information.
Index uses one or more column index keys and pointers to the record to locate record.
Index is used to speed up query performance.
Both exist as B-tree structure.
Kind of the indexes are clustered and non-clustered.

There are only two different types of indexes. Clustered and NonClustered. There can only be one clustered index on a table and the reason is simple:

· A Clustered index is the data of table sorted according to the columns you choose.

Ex:- CREATE CLUSTERED INDEX myIndex ON myTable (id_column)

· A NonClustered index is just like the index of a book. It contains data sorted so that it’s easy to find, then once found, it points back to the actual page that contains the data. (In other words, it points back to the clustered index)

Ex:- create NONCLUSTERED INDEX [adminusertbl_indexs1] ON [adminusertbl]

( [userid] ASC, [password] ASC)

What is a Trigger

A trigger is a special kind of a store procedure that executes in response to certain action on the table like insertion, deletion or updation of data. It is a database object which is bound to a table and is executed automatically. You can’t explicitly invoke triggers. The only way to do this is by performing the required action no the table that they are assigned to.

Types Of Triggers:-

After Trigger (using FOR/AFTER CLAUSE)

This trigger fires after SQL Server completes the execution of the action successfully that fired it.

Instead of Trigger (using INSTEAD OF CLAUSE)

This trigger fires before SQL Server starts the execution of the action that fired it. This is much more different from the AFTER trigger, which fires after the action that caused it to fire. We can have an INSTEAD OF insert/update/delete trigger on a table that successfully executed but does not include the actual insert/update/delet to the table.

For Example:

Select @a=i.empid from deleted i -----For deleted

Select @a=i.empid from inserted i -----For Updated

Select @a=i.empid from inserted i -----For Inserted

Create Triger instrig on tblname

Instead of Delete

Declare @username nvarchar(100)=null;

select @username=i.userid from Deleted i;

delete from usermgt where username=@username

Define Normalization and De- Normalization.

Normalization is the process of reducing data redundancy and maintains data integrity. This is performed by creating relationships among tables through primary and foreign keys. Normalization procedure includes 1NF, 2NF, 3NF, BCNF, and then the data is normalized.

Denomalization on the contrary is the process of adding redundant data to speed up complex queries involving multiple table JOINS. One might just go to a lower form of Normalization to achieve Denormalization and better performance. Data is included in one table from another in order to eliminate the second table which reduces the number of JOINS in a query and thus achieves performance.

Benefits of Normalization :

a. Eliminate data redundancy

b. Improve performance

c. Query optimization

d. Faster update due to less number of columns in one table

e. Index improvement

1. First Normal Form (1NF)

A database table is said to be in 1NF if it contains no repeating fields/columns. The process of converting the UNF table into 1NF is as follows:

1. Separate the repeating fields into new database tables along with the key from unnormalized database table.

2. The primary key of new database tables may be a composite key

1NF of above UNF table is as follows:

2. Second Normal Form (2NF)

A database table is said to be in 2NF if it is in 1NF and contains only those fields/columns that are functionally dependent(means the value of field is determined by the value of another field(s)) on the primary key. In 2NF we remove the partial dependencies of any non-key field.

The process of converting the database table into 2NF is as follows:

1. Remove the partial dependencies(A type of functional dependency where a field is only functionally dependent on the part of primary key) of any non-key field.

2. If field B depends on field A and vice versa. Also for a given value of B, we have only one possible value of A and vice versa, Then we put the field B in to new database table where B will be primary key and also marked as foreign key in parent table.

2NF of above 1NF tables is as follows:

3. Third Normal Form (3NF)

A database table is said to be in 3NF if it is in 2NF and all non keys fields should be dependent on primary key or We can also said a table to be in 3NF if it is in 2NF and no fields of the table is transitively functionally dependent on the primary key.The process of converting the table into 3NF is as follows:

1. Remove the transitive dependecies(A type of functional dependency where a field is functionally dependent on the Field that is not the primary key.Hence its value is determined, indirectly by the primary key )

2. Make separate table for transitive dependent Field.

3NF of above 2NF tables is as follows:

4. Boyce Code Normal Form (BCNF)

A database table is said to be in BCNF if it is in 3NF and contains each and every determinant as a candidate key.The process of converting the table into BCNF is as follows:

1. Remove the non trival functional dependency.

2. Make separate table for the determinants.

BCNF of below table is as follows:

5. Fourth Normal Form (4NF)

A database table is said to be in 4NF if it is in BCNF and primary key has one-to-one relationship to all non keys fields or We can also said a table to be in 4NF if it is in BCNF and contains no multi-valued dependencies.The process of converting the table into 4NF is as follows:

1. Remove the multivalued dependency.

2. Make separate table for multivalued Fields.

4NF of below table is as follows:

6. Fifth Normal Form (5NF)

A database table is said to be in 5NF if it is in 4NF and contains no redundant values or We can also said a table to be in 5NF if it is in 4NF and contains no join dependencies.The process of converting the table into 5NF is as follows:

1. Remove the join dependency.

2. Break the database table into smaller and smaller tables to remove all data redundancy.

5NF of below table is as follows:

DOTNET Interview Question

Pages

SQL Server