Magic Tables in SQL Server

Magic Tables in SQL Server

Magic Table in SQL Server:
Magic tables are used to put all the deleted and updated rows. We can retrieve the
column values from the deleted rows using the keyword "deleted"

To project the deleted data into the deleted table we will use "output" clause
Below is the sample code to retrieve the deleted data.

Code Snippet

DECLARE @DeletedTable TABLE(DeletedTableID INT, DeletedData VARCHAR(20)) DELETE VENKATOutput OUTPUT Deleted.KeyID, Deleted.Name INTO @DeletedTable WHERE KeyID > 3 SELECT * FROM @DeletedTable

Similarly, we can retrieve the updated data and old data too using the keyword "Inserted"

Deadlocks in SQL Server

Deadlock occurs if multiple users tried to access the resources holding some resources with them to finish the task. Dead lock can be avoided by using Lock methods in sql server.Locks can be classified in various types like

1. Rowlevel locking,
2. Page level locking,
3. Table level locking.

Row level locking: A specific row in the table will be locked by the user and meantime no one can access those rows.
Page level locking: In this case, we used to lock a particular page which hold 8 KB of data.
Table level lock: Here, entire table will be locked.

Conceptually there are various types of locks like,

Shared lock : The lock is shared by the users. One person can update the data meantime other persons can select the data.
Exclusive lock: No one can access the data until the concerned person releases the lock.

Database Query tuning

Database Query tuning

To enhance the performance of the queries, we have to tune the queries,

1. Avoid using "select *" instead specify the needed columns in the select clause.

2. Index the frequent usage columns (which are used in the where clause). The index may be clustered or non clustered based on your business functionality.

3. Specify the table using [servername].[databasename].[owner or schema name].[table name] (This can be priorly used in the transactions involving huge number of tables and huge number of databases.

4. Avoid using cursors in looping the table rows which will make a server roundtrip for each row. Instead, you can opt while loop (transfer the data to temp tables).

5. Avoid using more temporary tables instead you can use table variables.

6. Use set nocount on in the start of your stored procedure. This will avoid frequent updation details about the affected rows to the server.
7. Avoid using views to retrieve the datas instead we can directly hit the tables.

8. Normalisation is the key concept to increase the performace of database.

9. Frequently updating the statistics will surely improve the performance.
10. Reindexing or reorganising the database will improve the performance of the databases.

Stored Procedure Vs User Defined functions in sql server

Stored Procedure:
Its a pre-compiled statements incorporating a set of T-SQL statements.
We can't use it in DML statements.
We can't use SP in Joins.
Stored procedure won't return table variables.
User defined functions:
Its a set of T-SQL statements. Repeated T-SQL statements can be grouped as a User defined functions.
We can use UDF's in DML statements and in joins.
UDF returns table variables.

"Like" Condition in sql server

"Like" condition is used to match the column datas with the necessary condition.
Consider my example i want to check for the names which starts with 'a' then the query resembles as below,
select name from venkatTable where name like 'a%'
Am trying to create a stored procedure to get the count with the column having the word "Ven"

Code Snippet

create procedure ImplementingLike as begin select count(*) from VenkatTable where Name like '%Ven%' end

Temporary Table Vs Table variable

Usage of Temporary table:
If we want to use the table for some other stored procedure or else to be used after my scope of the query. We will prefer temporary table.
Manually, before creating a temporay table we have to delete the existing one
Two types of tables can be created,
1. Local Temporary table
2. Global Temporary table
If we restart the server, temporary tables will get deleted.
Usage of table variable:
If we need table's existence to be restricted with in the scope of the query and no need to be used in future. then we can prefer table variable.
The table variables will automatically get deleted after the scope may be between a begin and end statements.

A new table variable cannot be created if an another table variable exists in the scope.

No need to explicitly drop the table.

Table variables are comparatively too fast in accessing the data because it will reduce the round trip like accessing the temp database to retrieve the data.

Constraints Vs Rules in sql server

Constraints:
Constraints are the condition provided on a column. If the column gets deleted by default, my constraint will get deleted.
Before applying the constraint the column should satisfy the condition.If i try to put a constraint on this column like the column should be unique, then my compiler will indicate an error like its not possible to create the constraint.

Rules:
Rules are nothing but the condition or terms created in the database and the column or table who want to implement that rule can use it.
If your trying to bind the rule with my column it wont think about your past data in the table and will try to abide for the future data

Cursor Vs While loop in sql server

Cursor Vs While loop in sql server

Avoid using cursors in looping the table rows which will make a server roundtrip for each row. Instead, you can opt while loop (transfer the data to temp tables).

In cursors, we have to create the cursor, open it, use it, close it and deallocate the cursor. So we are facing additional overhead in case of using cursors.

Cursors are heavy weight object holds huge memory when compare to while loop.

In case of any breakage in the mid of looping the cursor we have to take care about the exception handling mechanism to de-allocate the cursor. Its an additional overhead for us.

Clustered index Vs Non Clustered index

Clustered index is used to sort the data physically. Where as non clustered index will have a reference to the data stored.

Clustered index is too fast where as non clustered index is comparatively slower.

Clustered index will hold huge memory for creation and for working when compared to non clustered index.

Only one clustered index can be created for a table where as 249 non clustered index can be created.

SQL server interview questions

SQL server interview questions

Let see some basic T-SQL. On reading this article, you can straight forward attend an interview or you can take a SQL Server task.

CREATE TABLE EMPLOYEE(EmpID INT PRIMARY KEY , EmpFirstName VARCHAR(100),EmpLastName VARCHAR(100), City VARCHAR(10),ManagerID INT, Salary int ,Designation varchar(10))

CREATE TABLE EMPLOYEEDETAILS(EmpID INT , EmpQualification VARCHAR(100),EmpExtn INT)

1. Adding Constraint between two tables, to update a foreign key relation ship. You need to alter the child table.

ALTER TABLE EMPLOYEEDETAILS
ADD CONSTRAINT emp_for FOREIGN KEY (EmpID)
REFERENCES EMPLOYEE(EmpID)

2. Trying to insert into the child table.

INSERT INTO EMPLOYEEDETAILS VALUES(1,'BE',200)

You will get an error "The INSERT statement conflicted with the FOREIGN KEY constraint"
Its because, you need to populate data in the master table and corresponding column should be populated in the child table.

3. Trying to insert into the master table.

INSERT INTO EMPLOYEE(EmpID, EmpFirstName,EmpLastName,City,Salary,Designation) VALUES(1,'Venkat','Prabu','Dharmapuri',1000,'PM')

Output:
(1 row(s) affected)

INSERT INTO EMPLOYEE(EmpID, EmpFirstName,EmpLastName,City,Salary) VALUES(1,'Venkat','Prabu','Dharmapuri',1000)

On executing the above statement, we will get
Violation of PRIMARY KEY constraint 'PK__EMPLOYEE__3C69FB99'. Cannot insert duplicate key in object 'dbo.EMPLOYEE'.
Its because we are trying to insert duplicate key into the primary key column.
Let make a try with other insert statements,

INSERT INTO EMPLOYEE(EmpID, EmpFirstName,EmpLastName,City,Salary,ManagerID,Designation) VALUES(2,'Arun','Prabu','Dharmapuri',500,1,'Dev')
INSERT INTO EMPLOYEE(EmpID, EmpFirstName,EmpLastName,City,Salary,Designation) VALUES(3,'Karthi','Prabu','Salem',2000,'Lead')
INSERT INTO EMPLOYEE(EmpID, EmpFirstName,EmpLastName,City,Salary,ManagerID,Designation) VALUES(4,'Lakshmi','Prabu','Chennai',100,3,'Dev')

select * from Employee

Lets make a try to insert a row in the child table.

INSERT INTO EMPLOYEEDETAILS VALUES(1,'BE',200)
INSERT INTO EMPLOYEEDETAILS VALUES(2,'MBA',100)
INSERT INTO EMPLOYEEDETAILS VALUES(3,'BA',10)
INSERT INTO EMPLOYEEDETAILS VALUES(4,'BE',50)
INSERT INTO EMPLOYEEDETAILS VALUES(4,'BE',500)

--delete from Employeedetails where EmpID=1
select * from Employeedetails

4. Employee earning maxiumum salary

select max(salary) from EMPLOYEE

I need the max salary on each designation

select max(salary),designation from Employee Group by designation

I need max salary based on employee city
select max(salary),city from Employee Group by city

I need to check the max salary of the employees who is having salary >=1000 based on city.
select max(salary),city from Employee where Salary >=1000
Group by city

Total salary given to employees by the company
select sum(salary) from Employee

Total salary given to employees grouped by designation
select sum(salary),designation from Employee Group by designation

Total employees available in the company
select count(*) from employee

5. Employee Search

I need an employee with First name as "Venkat"
select EmpID,EmpFirstName from employee where EmpFirstName='Venkat'

I need an employee with Firstname start with "V"
select EmpID,EmpFirstName from employee where EmpFirstName like 'V%'

I need an employee with Firstname having second letter as "e"
select EmpID,EmpFirstName from employee where EmpFirstName like '_e%'

I need an employee with firstname ended with the letter "t"
select EmpID,EmpFirstName from employee where EmpFirstName like '%t'

I need an employee whose city is "Chennai"
select EmpID,EmpFirstName from employee where city ='Chennai'

6. List down the employees having more than one extension number
select empid from employeedetails group by empid having count( EmpExtn)>1

7. Fetch the employee based on EmpQualification
select a.EmpID,a.EmpFirstName,a.Salary,a.Designation, b.EmpQualification from Employee a
inner join EmployeeDetails b on a.EmpID=b.EmpID

To avoid duplicates we need to used the distinct keyword
select distinct a.EmpID,a.EmpFirstName,a.Salary,a.Designation, b.EmpQualification from Employee a
inner join EmployeeDetails b on a.EmpID=b.EmpID

INSERT INTO EMPLOYEE(EmpID, EmpFirstName,EmpLastName,City,Salary,Designation) VALUES(5,'Santhi','Jayakantham','Dharmapuri',1000,'PM')

To list down all the rows from the left table with matching columns in the right table. This can be achieved using Left outer join.
select distinct a.EmpID,a.EmpFirstName,a.Salary,a.Designation, b.EmpQualification from Employee a
left outer join EmployeeDetails b on a.EmpID=b.EmpID

Appropriate right tables column will be appended with Null value.
What about getting the all the right table row with corresponding matching column from the left table.

select distinct a.EmpID,a.EmpFirstName,a.Salary,a.Designation, b.EmpQualification from Employee a
right outer join EmployeeDetails b on a.EmpID=b.EmpID

Getting the matching data from two tables. Suppose I need to list the employees who is having qualification = 'BE'
select distinct a.EmpID,a.EmpFirstName,a.Salary,a.Designation, b.EmpQualification from Employee a
inner join EmployeeDetails b on a.EmpID=b.EmpID where b.EmpQualification='BE'

8. List down all the managers and their employee ID
select b.ManagerID,a.EmpFirstName as ManagerName from employee a
inner join employee b on a.EmpID=b.ManagerID
List down the employees who is having managers,
select b.EmpID,b.EmpFirstName,b.ManagerID,a.EmpFirstName from employee a
inner join employee b on a.EmpID=b.ManagerID

List down all the employees with managers details(If exists)
select b.EmpID,b.EmpFirstName,b.ManagerID,a.EmpFirstName from employee a
right outer join employee b on a.EmpID=b.ManagerID

TSQL Interview Questions - Part2

TSQL Interview Questions - Part2

T-SQL Interview Questions: (SQL Server Interview questions)
Considering am having two tables,

Considering am having two tables,
create table Employees(empid int, empname varchar(10),desig varchar(10),salary int, mgrid int)
insert into employees values(1,'aa','pm',10000,1)
insert into employees values(2,'bb','pm',10000,1)
insert into employees values(3,'cc','pl',500,2)
SELECT * FROM employees
SELECT * FROM employees
create table employeedetails (empid int, City varchar(10), Telephone int)
insert into employeedetails values(1,'sydney',10)
insert into employeedetails values(1,'sydney1',10)
SELECT * FROM employeedetails
The table structure should resemble as below,

Employees (empid, empname, Designation, salary, mgrid )
EmployeeDetails (empid, City, Telephone)
5. Group the employees based on the designation. Need the number of employees in each designation
SELECT Designation,COUNT(Designation) FROM employees GROUP BY Designation

6. Retrieve the number of employees having more than one phone number
SELECT distinct * FROM employeedetails A
INNER JOIN
(select empid,telephone,count(*) as cnt from employeedetails group by empid,telephone)t
ON A.empid = t.empid
WHERE t.cnt>1
7. Select the details of 3 max salaried employees from employee table.SELECT TOP 3 empid, salary
FROM employee
ORDER BY salary DESC

8. Update 5 percent salary increment for all employees

UPDATE employees SET Salary = (Salary *105)/100
9. Display all managers from the table. (manager id is same as emp id)

select * from employees where empid IN (SELECT DISTINCT mgrid FROM employees)

10. Listing all the employees with Managers available
select a.empid,a.empname,b.mgrid,b.empname from employees a
inner join employees b
on a. mgrid =b.empid

SQL Server Interview questions

SQL Server Interview questions

What is the significance of NULL value and why should we avoid permitting null values?Null means no entry has been made. It implies that the value is either unknown or undefined.
We should avoid permitting null values because
Column with NULL values can't have PRIMARY KEY constraints. Certain calculations can be inaccurate if NULL columns are involved.

What is SQL whats its uses and its component ?
The Structured Query Language (SQL) is foundation for all relational database systems. Most of the large-scale databases use the SQL to define all user and administrator interactions. It enable us to retrieve the data from based on our exact requirement. We will be given a flexibility to store the data in our own format.

The DML component of SQL comprises four basic statements:
* SELECT to get rows from tables
* UPDATE to update the rows of tables
* DELETE to remove rows from tables
* INSERT to add new rows to tables

What is DTS in SQL Server ?
Data Transformation Services is used to transfer the data from one source to our required destination. Considering am having some data in sql server and I need to transfer the data to Excel destination. Its highly possible with dialogue based tool called Data Transformation services. More customization can be achieved using SSIS. A specialized tool used to do such migration works.
What is the difference between SQL and Pl/Sql ?
Straight forward. SQL is a single statement to finish up our work.
Considering, I need some data from a particular table.
“Select * from table” will fetch the necessary information. Where as I need to do some row by row processing. In that case, we need to go for Programming Logic / SQL.What is the significance of NULL value and why should we avoid permitting null values?
Null means no entry has been made. It implies that the value is either unknown or undefined.
We should avoid permitting null values because
Column with NULL values can't have PRIMARY KEY constraints. Certain calculations can be inaccurate if NULL columns are involved.Difference between primary key and Unique key?
Both constraints will share a common property called uniqueness. The data in the column should be unique. The basic difference is,
· Primary key won’t allow null value. Whereas, unique key will accept null value but only one null value.
· On creating primary key, it will automatically format the data inturn creates clustered index on the table. Whereas, this characteristics is not associated with unique key.
· Only one primary key can be created for the table. Any number of Unique key can be created for the table.

What is normalization?
Normalization is the basic concept used in designing a database. Its nothing but, an advise given to the database to have minimal repetition of data, highly structured, highly secured, easy to retrieve. In high level definition, the Process of organizing data into tables is referred to as normalization.

What is a stored procedure:
Stored procedures are precompiled T-SQL statements combined to perform a single task of several tasks. Its basically like a Macro so when you invoke the Stored procedure, you actually run a set of statements. As, its precompiled statement, execution of Stored procedure is compatatively high when compared to an ordinary T-SQL statement.

What is the difference between UNION ALL Statement and UNION ?
The main difference between UNION ALL statement and UNION is UNION All statement is much faster than UNION,the reason behind this is that because UNION ALL statement does not look for duplicate rows, but on the other hand UNION statement does look for duplicate rows, whether or not they exist.

Example for Stored Procedure?
They are three kinds of stored procedures,
1.System stored procedure – Start with sp_
2. User defined stored procedure – SP created by the user.
3. Extended stored procedure – SP used to invoke a process in the external systems.
Example for system stored procedure
sp_helpdb - Database and its properties
sp_who2 – Gives details about the current user connected to your system.
sp_renamedb – Enable you to rename your database

What is a trigger?
Triggers are precompiled statements similar to Stored Procedure. It will automatically invoke for a particular operation. Triggers are basically used to implement business rules.

What is a view?
If we have several tables in a db and we want to view only specific columns from specific tables we can go for views. It would also suffice the needs of security some times allowing specfic users to see only specific columns based on the permission that we can configure on the view. Views also reduce the effort that is required for writing queries to access specific columns every time.
What is an Index?
When queries are run against a db, an index on that db basically helps in the way the data is sorted to process the query for faster and data retrievals are much faster when we have an index.

What are the types of indexes available with SQL Server?
There are basically two types of indexes that we use with the SQL ServerClustered - 1. It will format the entire table, inturn physically sort the table.
2. Only one clustered index can be created for a table.
3. Data will be located in the leaf level.
4. By default, primary key will create clustered index on the table.
Non-Clustered Index – 1. It wont touch the structure of the table.
2. It forms an index table as reference to the exact data.
3. A reference to the data will be located in the leaf level.
4. For a table, we can create 249 non clustered index.

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.

SQL Interview Questions

SQL Interview Questions

Local and Global Cursor :
While declaring the cursors, we can specify a cursor as Local or Global cursor.

Local Cursor will exists for the particular object and it cannot be reference by an external object. By default, cursors are Local. If we need to make it as global and can be accessed by all the objects. We need to specify it as global.
If the cursor is declared as Global, it can be accessed by all the objects in the existing connection.

Subquery:Subquery is an important concept in T-SQL. The outer query will provide the result based on the result given by the subquery.

Select id from table1 where id = (select max(id) from table1) -Red color is the subquery.

The outer table's output "id" depends on the subquery "id" value.
Here equal to (=) is used so that the subquery will retrieve only one id. If the subquery gives more than one value we have to use "IN" operator.
Select id from table1 where id in (select id from table1)
generally, the outer query is waiting for the subquery to finish its execution. So, it will hit the performance in a huge application with complex joins. So, we should avoid subqueries.

Correlated subquery:
These queries are little bit interesting one. The inner query depends on the outer queries input and there will be a mutal co-ordination occurs between these inner and outer query.
select name from venkat1 v
where id in (select id from venkat1 v1 where v.id=v1.id)
In the above query, the inner query depends on the outer queries output.