SQL Server & Oracle Interview questions

1.Do you know Boyce Code normal form?

2.What is global temp table?


Local temporary tables are visible only to their creators during the same connection to an instance of SQL Server as when the tables were first created or referenced. Local temporary tables are deleted after the user disconnects from the instance of SQL Server.

 Global temporary tables are visible to any user and any connection after they are created and are deleted when all users that are referencing the table disconnect from the instance of SQL Server

3.Identity and scope identity?

identity
It returns the last identity value generated for any table in the current session, across all scopes.
scope identity
It returns the last identity value generated for any table in the current session and the current scope.

The Sales.Customer table has a maximum identity value of 29483. If you insert a row into the table, @@IDENTITY and SCOPE_IDENTITY() return different values.

SCOPE_IDENTITY() returns the value from the insert into the user table, whereas @@IDENTITY returns the value from the insert into the replication system table.

 Use SCOPE_IDENTITY() for applications that require access to the inserted identity value.

4.What is Merge in SQL server?

MERGE Production.ProductInventory AS target
USING (SELECT ProductID, SUM(OrderQty) FROM Sales.SalesOrderDetail AS sod
    JOIN Sales.SalesOrderHeader AS soh
    ON sod.SalesOrderID = soh.SalesOrderID
    AND soh.OrderDate = @OrderDate
    GROUP BY ProductID) AS source (ProductID, OrderQty)
ON (target.ProductID = source.ProductID)
WHEN MATCHED AND target.Quantity - source.OrderQty <= 0
    THEN DELETE
WHEN MATCHED
    THEN UPDATE SET target.Quantity = target.Quantity - source.OrderQty,
                    target.ModifiedDate = GETDATE()

5 Can we update the views?

6. Table types?

1.Temporary Tables
2.Table Variables
3.Derived Table


7. Full outer join and cross join?


8. Why only one cluster index?

A clustered index sorts and stores the data rows in the
table based on the index key values. Therefore only one
clustered index can be created on each table because the
data rows themselves can only be sorted in one order.

9 Increase the performance in stored procedure?

1. Keep database object name short, meaningful and easy to remember ( useful for easy maintenance).

2. Normalize data at least up to 3rd form but not at the cost of query performance. Denormalization up to small extent can boost query performance.

(e.g. Suppose you have 3 columns ItemID, Quantity, Rate in a table (let us call it as ItemStock table). While generating a report with the total, we need to multiply Quantity with Rate. Instead, we can add a total column in the table and store the total over there.

3. Do not use that column in the select statement which are not required. Never user select * statement.

4. Use SP to fetch data and try to avoid program logic in SP. Logic has to be written at the application level and not at the database level. Always use 'set-based approach' instead of a 'procedural approach' for accessing and manipulating data.

5. Use the primary key in the table to filter the data in where clause. Use execution plans to analyze the query. Make sure your queries do an "Index seek" instead of an "Index scan" or a "Table scan." A table scan or an index scan costs more and takes more time for execution. Choose the right indexes on the right columns.

6. Use SET NOCOUNT ON at the beginning of your stored procedures, as this suppresses messages like '(1 row(s) affected)' after executing INSERT, UPDATE, DELETE, and SELECT statements. This improves the performance of stored procedures by reducing network traffic.

7. Also, avoid searching using not equals operators (<> and NOT) as they result in table and index scans.

8. Use table variables instead of temporary tables.

9. 'Derived tables' as they perform better than subqueries

Select Max(Quantity)
From ItemStock
Where ItemID in
(
Select
Top 2 ItemID
From ItemStock
Order By Quantity Desc
)
Same can be written using derived table

Select Max(Quantity)
From
(
Select
Top 2 Quantity
From ItemStock
Order by Quantity Desc

) as A

10.Don't use Having clause.
11.Try to minimize no of sub queries.

http://beginner-sql-tutorial.com/sql-query-tuning.htm


10.Advantages Stored procedure?

11 Merge

Though Execution Plan Retention and Reuse


•Conditionally insert or update rows in a target table.

If the row exists in the target table, update one or more columns; otherwise, insert the data into a new row.

•Synchronize two tables.

Insert, update, or delete rows in a target table based on differences with the source data.

MERGE dbo.Departments AS d
USING dbo.Departments_delta AS dd
ON (d.DeptID = dd.DeptID)
WHEN MATCHED AND d.Manager <> dd.Manager OR d.DeptName <> dd.DeptName
    THEN UPDATE SET d.Manager = dd.Manager, d.DeptName = dd.DeptName
WHEN NOT MATCHED THEN
    INSERT (DeptID, DeptName, Manager)
        VALUES (dd.DeptID, dd.DeptName, dd.Manager)
WHEN NOT MATCHED BY SOURCE THEN
    DELETE
OUTPUT $action,
       inserted.DeptID AS SourceDeptID, inserted.DeptName AS SourceDeptName,
       inserted.Manager AS SourceManager,
       deleted.DeptID AS TargetDeptID, deleted.DeptName AS TargetDeptName,
       deleted.Manager AS TargetManager;

12. Rank?
SELECT a1.Name, a1.Sales, COUNT(a2.sales) Sales_Rank


FROM Total_Sales a1, Total_Sales a2

WHERE a1.Sales <= a2.Sales or (a1.Sales=a2.Sales and a1.Name = a2.Name)

GROUP BY a1.Name, a1.Sales

ORDER BY a1.Sales DESC, a1.Name DESC;

13. 2nd lowest value?




select top 1 * from tbl_Customer touter where touter.Amount > (SELECT

min(tinner.Amount) FROM [Customer].[dbo].[tbl_Customer] as

tinner) order by touter.Amount asc

14.Nth salary

3rd highest salary

    SELECT Salary,EmpName FROM
   ( SELECT Salary,EmpName,ROW_NUMBER() OVER(ORDER BY Salary) As RowNum
      FROM EMPLOYEE ) As A
    WHERE A.RowNum IN (3)

SELECT *

FROM Employee Emp1

WHERE N-1 = (

SELECT COUNT(DISTINCT(Emp2.Salary))

FROM Employee Emp2

WHERE Emp2.Salary > Emp1.Salary)

15. How big the database you are working?
16. How critical is the database?
Cluster, Database Recovery
17. What is the company standard on virtual?
18. Why a virtual Server?
19. Error handling in SQL Server?
20. Integration of a .net compliant language such as C#, ASP.Net?
21. Encryption in SQL Server 2000?
No option built-in expensive third party options.
22. What is join?
Joins combine records from two or more table in a database.
23. Types of joins?
Inner join, left outer join, right outer join, full outer join and self join.
24.What is Inner Join?
An Inner join creates a new result table by combining column values of two tables (A and B) based upon the join predicate. The query compare each row of A with each row of b to find all pair of rows which satisfy the join predicate. When join predicate is satisfied column value of each matched pair of rows of A and B are combined into a result row. Intersection.
25. What is Left Outer Join?
All the records of the left Table(A) even if the join condition  does not find any match record in the right table(B). This means that of on clause matches '0' record in B, the join will still return 0 row in the result but with null in each column of B.

26. What is the cursor?

Cursors (Transact-SQL) Microsoft SQL Server statements produce a complete result set, but there are times when the results are best processed one row at a time. Opening a cursor on a result set allows processing the result set one row at a time.

27.Execute and executeSQL difference?

Use SP_EXECUTESQL rather than EXEC(), it has better performance and improved security.

sp_executesql gives you the possibility to use parameterised statements,

28.Dynamic Columns?

29.What is Unique key?
A unique key must uniquely identify all possible rows that exist in a table. Uniquely identify each row in a table. Unique key column may or may not be null.

30.Active employees query?
select empid from employee1 where resignationdate < '2012-07-02' or resignationdate is NULL

31. Inactive employees query?
select empid from employee1 where resignationdate > '2012-07-02' or resignationdate <> NULL

32.dept wise emp strength?

select deptno, COUNT(*) from emp where to_date is null or to_date < '2004-02-21'  group by deptno33.

33.Less designation employees?

select deptno, COUNT(emp.deptno) from emp where to_date is null or to_date < '2004-02-21'  group by deptno having COUNT(emp.deptno) <3 br="">

34.Performance?

http://msdn.microsoft.com/en-us/magazine/cc163854.aspx

Performance on the Data Tier
Tip 1—Return Multiple Resultsets
Tip 2—Paged Data Access
Tip 3—Connection Pooling
Tip 4—ASP.NET Cache API
Tip 5—Per-Request Caching
Tip 6—Background Processing
Tip 7—Page Output Caching and Proxy Servers
Tip 8—Run IIS 6.0 (If Only for Kernel Caching)
Tip 9—Use Gzip Compression
Tip 10—Server Control View State


35.Query optimization?

http://www.toptal.com/sql/sql-database-tuning-for-developers

36.Cluster index structure?

Clustered

• Clustered indexes sort and store the data rows in the table or view based on their key values. These are the columns included in the index definition. There can be only one clustered index per table because the data rows themselves can be sorted in only one order.
• The only time the data rows in a table are stored in sorted order is when the table contains a clustered index. When a table has a clustered index, the table is called a clustered table. If a table has no clustered index, its data rows are stored in an unordered structure called a heap.

Nonclustered

• Nonclustered indexes have a structure separate from the data rows. A nonclustered index contains the nonclustered index key values and each key value entry has a pointer to the data row that contains the key value.
• The pointer from an index row in a nonclustered index to a data row is called a row locator. The structure of the row locator depends on whether the data pages are stored in a heap or a clustered table. For a heap, a row locator is a pointer to the row. For a clustered table, the row locator is the clustered index key.
• You can add non-key columns to the leaf level of the nonclustered index to by-pass existing index key limits, 900 bytes, and 16 key columns, and execute fully covered, indexed, queries. For more information, see Create Indexes with Included Columns.

37. What is B-Tree?
38. Correlated queries?

In an SQL database query, a correlated subquery (also known as a synchronized subquery) is a subquery (a query nested inside another query) that uses values from the outer query. The subquery is evaluated once for each row processed by the outer query.
Here is an example for a typical correlated subquery. In this example, we are finding the list of all employees whose salary is above average for their departments.

 SELECT employee_number, name
   FROM employees AS Bob
   WHERE salary > (
     SELECT AVG(salary)
       FROM employees
       WHERE department = Bob.department);
39.Self Join?
A table can be joined to itself in a self-join.

40 what is the deference where and having?

41. Multiple databases how will maintain the transactions?

the business layer we need to write the code.

using (TransactionScope scope = new TransactionScope())
  {
     ... Do Stuff with Connection 1 using SqlDataReader
     ... Do Stuff with Connection 2 using Entity Framework
     ... Do Stuff with Connection 3 on another Oracle Database
     ... And for good measure do some stuff in MSMQ or XA or DTC resource
     scope.Complete(); // If you are happy
  }

42. Left JOIN and LEFT outer join difference?

LEFT JOIN and LEFT OUTER JOIN ARE THE SAME
RIGHT JOIN and RIGHT OUTER JOIN ARE THE SAME

43. What is the difference between stored procedure and function?

Basic Difference

1. The function must return a value but in Stored Procedure it is optional( Procedure can return zero or n values).
2. Functions can have only input parameters for it whereas Procedures can have input/output parameters.
3. Functions can be called from Procedure whereas Procedures cannot be called from Function.

Advance Difference

1. The procedure allows SELECT as well as DML(INSERT/UPDATE/DELETE) statement in it whereas Function allows the only SELECT statement in it.
2. Procedures can not be utilized in a SELECT statement whereas Function can be embedded in a SELECT statement.
3. Stored Procedures cannot be used in the SQL statements anywhere in the WHERE/HAVING/SELECT section whereas Function can be.
4. The most important feature of stored procedures over function is to retention and reuse the execution plan while in case of the function it will be compiled every time.
5. Functions that return tables can be treated as another row set. This can be used in JOINs with other tables.
6. Inline Function can be though of as views that take parameters and can be used in JOINs and other Rowset operations.
7. An exception can be handled by try-catch block in a Procedure whereas try-catch block cannot be used in a Function.
8. We can go for Transaction Management in Procedure whereas we can't go in Function.

44.Rename the database?

1. In Object Explorer, connect to an instance of the SQL Server Database Engine, and then expand that instance.
2. Make sure that no one is using the database, and then set the database to single-user mode.
3. Expand Databases, right-click the database to rename, and then click Rename.
4. Enter the new database name, and then click OK.

45.Database tuning?

http://www.codeproject.com/Articles/43629/Top-steps-to-optimize-data-access-in-SQL-Serv

1.Apply proper indexing in the table column in the database.
2.Create appropriate covering index.

create index salesProductidIndex on Sales(productid)
include(salesdate,salesperson)

10.Organize filegroups and files in the database.

• The primary file group must be totally separate, and should be left to have only system objects, and no user defined object should be created on the primary file group. Also, the primary file group should not be set as the default filegroup. Separating the system objects from other user objects will increase performance and enhance the ability to access tables in cases of serious data failures.
• If there are N physical disk drives available in the system, then try to create N files per file group and put each one in a separate disk. This will allow distributing disk I/O load over multiple disks, and will increase performance.
• For frequently accessed tables containing indexes, put the tables and the indexes in separate file groups. This would enable reading the index and table data faster.
• For frequently accessed table containing Text or Image columns, create a separate file group and put the text, next, and image columns in that filegroup on different physical disks, and put the tables in a different file group. This would enable faster data retrieval from the table with queries that don't contain text or image columns.
• Put the transaction log file on a different physical disk that is not used by the data files. The logging operation (Transaction log writing operation) is more write-intensive, and hence it is important to have the log on the disk that has good I/O performance.
• Consider assigning "Read only" tables into a file group that is marked as "Read only". This would enable faster data retrieval from these read-only tables. Similarly, assign "Write only" tables in a different file group to allow for faster updates.
• Do not let SQL Server fire the "Auto grow" feature too often because it is a costly operation. Set an "Auto grow" increment value so that the database size is increased less frequently (say, once per week). Similarly, do not use the "Auto shrink" feature for the same reason. Disable it, and either shrink the database size manually or use a scheduled operation that runs in a timed interval (say, once a month).

2.Apply partition for big fat tables.
3.Defragment indexes if fragment occurs
create maximum 5 indexes per table, if warehouse create 10 indexes per table.

4.Move inline sql from application to database.
5.optimize the sql.

• don't use select *, avoid unnecessary table joins
• Don't use count() aggregate function in a subquery to do an exitance check.
• try to avoid joining two types of columns

SELECT column_list FROM small_table, large_table WHERE
smalltable.float_column = large_table.int_column .

try not to use Count(*)
try to avoid use of temp tables rather use table variables

table variables reside in the memory
temp table reside in tempdb

try not to use OR in the query, use union all
implement the user defined functions. do not call functions within stored procedures
Try to avoid use of triggers
Use schema name with object name

SELECT * FROM dbo.MyTable -- Preferred method
-- Instead ofSELECT * FROM MyTable -- Avoid this method

Don't use prefix SP_ in the stored procedures
Use if exists instead of select *

if exists(select 1 from sysobjects where name='mytable'and type='U')

Use sp_executesql instead of exec

DECLARE @Query VARCHAR(100)DECLARE @Age INT
SET @Age = 25SET @Query = 'SELECT * FROM dbo.tblPerson WHERE Age = ' +CONVERT(VARCHAR(3),@Age)EXEC (@Query)
DECLARE @Query NVARCHAR(100)SET @Query = N'SELECT * FROM dbo.tblPerson WHERE Age = @Age'EXECUTE sp_executesql @Query, N'@Age int', @Age = 25

Try avoid cursors
Use try catch for error handling
Avoid corelated subquires
SELECT c.Name, 
       c.City,
       (SELECT CompanyName FROM Company WHERE ID = c.CompanyID) AS CompanyName 
FROM Customer c

SELECT c.Name, 
       c.City, 
       co.CompanyName 
FROM Customer c 
 LEFT JOIN Company co
  ON c.CompanyID = co.CompanyID

6.Apply denormalization

OLTA-online transaction analytical system-->data ware housing
OLTP-online transaction processing system

46. difference between 2000,2005,2008?

http://www.c-sharpcorner.com/Blogs/15967/differences-between-sql-server-2005-2008-2008r2-2012.aspx

47. Table variable?

As a rule of thumb, for small to medium volumes of data and simple usage scenarios you should use table variables.

48. How to call the functions in stored procedures?
Select @result=FunctionAdd(@a1,@b2)

49.Common table expression?

With T(Address, Name, Age)  --Column names for Temporary table
AS
(
SELECT A.Address, E.Name, E.Age from Address A
INNER JOIN EMP E ON E.EID = A.EID
)
SELECT * FROM T  --SELECT or USE CTE temporary Table
WHERE T.Age > 50
ORDER BY T.NAME

50 Hit ration?

51.In memory database when to use?

  Oracle

1.In which scenario used cluster index and non cluster index?
If table have more insert, delete ,update we have to user cluster index.
Cluster index can be user for frequent used queries.
It can be used range queries like between , <=,>
Used return large result set.
Used join clause, typically these are foreign key column.
Used order by or group by clause
An unique or contain may distinct values
Are accessed sequentially

Non Cluster index 
Table with low update requirements, but large values of data can benefit from may non cluster index used.
Decision support system application that contain primarily read only data can be benefit from many non clustered index.
Queries that don't return large result sets.
Contain columns frequently involved in search condition of a query.


2. I have two databases one database success another database fail how will you handle this scenario?

The best way is to copy the data in a single place. Use a scheme which allows you to abort the copy and continue it at any time (for example, ignore data which you already have or order the select by ID and request only records > MAX(ID) of your copy). Protect this with a transaction. This is not a problem since you're only reading data from the source, so when the transaction fails for any reason, you can ignore the source database. Therefore, this is a plain old single source transaction.

After you have copied the data, process it locally.

3.Which Authentication used?

4.Table lock which isolation Level user?

SERIALIZABLE - lock on full table(on which Select query is fired). This means, B reads the data and no other transaction can modify the data on the table. This is the most secure but slowest way to work with data. Also, since a simple read operation locks the table, this can lead to heavy problems on production: imagine that T table is an Invoice table, user X wants to know the invoices of the day and user Y wants to create a new invoice, so while X executes the read of the invoices, Y can't add a new invoice (and when it's about money, people get really mad, specially the bosses).

5. I lack of records How will retrieve from database?

6.How to you increase the performance of stored procedure?
Set Nocount on-> No of rows effected will not return to sql statement.

7.Whar are advantages of stored procedure?

Stored procedure reduced network traffic
Procedure execution plans can be reused.
stored procedure help promote code reuse
procedure can encapsulate logic
procedure provides better security to your data
you can change stored procedure code without affecting clients

8.sql query tuning?
http://beginner-sql-tutorial.com/sql-query-tuning.htm