Monday, 21 January 2013

SQL Server Interview Questions



What is Identity?
Identity (or AutoNumber) is a column that automatically generates numeric values. A start and increment value can be set, but most DBA leave these at 1.
A GUID column also generates numbers; the value of this cannot be controlled.
Identity/GUID columns do not need to be indexed.

What are temp tables? What is the difference between global and local temp tables?
Temporary tables are temporary storage structures. You may use temporary tables as buckets to store data that you will manipulate before arriving at a final format. The hash (#) character is used to declare a temporary table as it is prepended to the table name. A single hash (#) specifies a local temporary table.
CREATE TABLE #tempLocal (nameid int, fname varchar(50), lname varchar(50))
Local temporary tables are available to the current connection for the user, so they disappear when the user disconnects.
Global temporary tables may be created with double hashes (##). These are available to all users via all connections, and they are deleted only when all connections are closed.
CREATE TABLE ##tempGlobal (nameid int, fname varchar(50), lname varchar(50))
Once created, these tables are used just like permanent tables; they should be deleted when you are finished with them. Within SQL Server, temporary tables are stored in the Temporary Tables folder of the tempdb database.

How are transactions used?
Transactions allow you to group SQL commands into a single unit. The transaction begins with a certain task and ends when all tasks within it are complete. The transaction completes successfully only if all commands within it complete successfully. The whole thing fails if one command fails. The BEGIN TRANSACTION, ROLLBACK TRANSACTION, and COMMIT TRANSACTION statements are used to work with transactions. A group of tasks starts with the begin statement. If any problems occur, the rollback command is executed to abort. If everything goes well, all commands are permanently executed via the commit statement.

What is the difference between a clustered and a nonclustered index?
A clustered index affects the way the rows of data in a table are stored on disk. When a clustered index is used, rows are stored in sequential order according to the index column value; for this reason, a table can contain only one clustered index, which is usually used on the primary index value.
A nonclustered index does not affect the way data is physically stored; it creates a new object for the index and stores the column(s) designated for indexing with a pointer back to the row containing the indexed values.
You can think of a clustered index as a dictionary in alphabetical order, and a nonclustered index as a book’s index.

What is the difference between truncate and delete?
Truncate is a quick way to empty a table. It removes everything without logging each row. Truncate will fail if there are foreign key relationships on the table.
Delete command removes rows from a table, while logging each deletion and triggering any delete triggers that may be present.

What does the NOLOCK query hint do?
Table hints allow you to override the default behavior of the query optimizer for statements. They are specified in the FROM clause of the statement. While overriding the query optimizer is not always suggested, it can be useful when many users or processes are touching data. The NOLOCK query hint is a good example because it allows you to read data regardless of who else is working with the data; that is, it allows a dirty read of data — you read data no matter if other users are manipulating it. A hint like NOLOCK increases concurrency with large data stores.
SELECT * FROM TableName (NOLOCK)
Microsoft advises against using NOLOCK, as it is being replaced by the READUNCOMMITTED query hint.

What is a CTE?
A common table expression (CTE) is a temporary named result set that can be used within other statements like SELECT, INSERT, UPDATE, and DELETE.
It is not stored as an object and its lifetime is limited to the query. It is defined using the WITH statement as the following example shows:
WITH ExampleCTE (id, fname, lname)
AS
(
            SELECT id, firstname, lastname FROM table
)
SELECT * FROM ExampleCTE
A CTE can be used in place of a view in some instances.

What is a view? What is the WITH CHECK OPTION clause for a view?
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.
The WITH CHECK OPTION for a view prevents data modifications (to the data) that do not confirm to the WHERE clause of the view definition. This allows data to be updated via the view, but only if it belongs in the view.

What is a query execution plan?
SQL Server has an optimizer that usually does a great job of optimizing code for the most effective execution. A query execution plan is the breakdown of how the optimizer will run (or ran) a query. There are several ways to view a query execution plan. This includes using the Show Execution Plan option within Query Analyzer; Display Estimated Execution Plan on the query dropdown menu; or use the SET SHOWPLAN_TEXT ON command before running a query and capturing the execution plan event in a SQL Server Profiler trace.

How can I create an empty table emp1 with same structure as emp?
Create Table emp1 As
Select * From emp Where 1=2;

Select all records where dept no of both emp and dept table matches.
Select * From emp
Where Exists(Select * From dept Where emp.deptno=dept.deptno)

If there are two tables emp1 and emp2, and both have common record. How can I fetch all the records but common records only once?
(Select * From emp) Union (Select * From emp1)

How to fetch only common records from two tables emp and emp1?
(Select * From emp) Intersect (Select * From emp1)

How can I retrieve all records of emp1 those should not present in emp2?
(Select * From emp1) Minus (Select * From emp2)

Count the totalsal deptno wise where more than 2 employees exist.
SELECT deptno, SUM(sal) As totalsal
FROM emp
GROUP BY deptno
HAVING COUNT(empno) > 2

To fetch ALTERNATE records from a table. (Even Numbered)
Select * From emp Where rowid in (
Select Decode(Mod(rownum,2),0,rowid, null) From emp
);

To select ALTERNATE records from a table. (Odd Numbered)
Select * From emp Where rowid in (
select decode(Mod(rownum,2),0,null ,rowid) from emp
);

Find the 3rd MAX salary in the emp table.
Select distinct sal From emp e1
Where 3 = (Select Count(Distinct sal) From emp e2 Where e1.sal <= e2.sal);

Find the 3rd MIN salary in the emp table.
Select Distinct sal From emp e1
Where 3 = (Select Count(Distinct sal) From emp e2 Where e1.sal >= e2.sal);

Select FIRST n records from a table.
Select * From emp Where rownum <= n;

Select LAST n records from a table
Select * From emp
Minus
Select * From emp Where rownum <= (Select Count(*) - n From emp);

List dept no., Dept name for all the departments in which there are no employees in the department.
Select * From dept Where deptno Not In (
Select deptno From emp);  
Alternate solution:
Select * From dept a Where Not Exists (
Select * From emp b Where a.deptno = b.deptno);
Alternate solution:
Select empno, ename, b.deptno, dname From emp a, dept b
Where a.deptno(+) = b.deptno And empno Is Null;

How to get 3 Max salaries?
Select Distinct sal From emp a
Where 3 >= (Select Count(Distinct sal) From emp b
Where a.sal <= b.sal)
Order By a.sal Desc;

How to get 3 Min salaries?
Select Distinct sal From emp a
Where 3 >= (Select Count(Distinct sal) From emp b
Where a.sal >= b.sal);

How to get nth max salaries?
Select Distinct hiredate From emp a
Where n = (select Count(Distinct sal) From emp b
Where a.sal >= b.sal);

Select DISTINCT RECORDS from emp table without DISTINCT keyword in query.
Select * From emp a
Where rowid = (Select Max(rowid) From emp b
Where a.empno=b.empno);

How to delete duplicate rows in a table?
Delete From emp a
Where rowid != (Select Max(rowid) From emp b
Where a.empno=b.empno);

Count of number of employees in department wise.
Select Count(EMPNO), b.deptno, dname from emp a, dept b 
Where a.deptno(+)=b.deptno 
Group By b.deptno, dname;

Suppose there is annual salary information provided by emp table. How to fetch monthly salary of each and every employee?
Select ename, sal/12 as monthlysal From emp;

Select all record from emp where job not in SALESMAN or CLERK.
Select * From emp where job not in ('SALESMAN','CLERK');

Select all record from emp where ename in 'BLAKE', 'SCOTT', 'KING' and 'FORD'.
Select * From emp Where ename In('JONES','BLAKE','SCOTT','KING','FORD');

What are DMVs?
Dynamic management views (DMVs) and functions return server state information that can be used to monitor the health of a server instance, diagnose problems, and tune performance; that is, they let you see what is going on inside SQL Server. They were introduced in SQL Server 2005 as an alternative to system tables. One example is viewing operating system wait statistics via this query:
SELECT * FROM sys.dm_os_wait_stats;
Another example is examining current sessions, much like the sp_who2 command:
SELECT * FROM sys.dm_exec_sessions;

What are DBCC commands?
Basically, the Database Consistency Checker (DBCC) provides a set of commands (many of which are undocumented) to maintain databases — maintenance, validation, and status checks. The syntax is DBCC followed by the command name. Here are three examples:
DBCC CHECKALLOC — Check disk allocation consistency.
DBCC OPENTRAN — Display information about recent transactions.
DBCC HELP — Display Help for DBCC commands.

What does the SQL Server Agent Windows service do?
SQL Server Agent is a Windows service that handles scheduled tasks within the SQL Server environment (aka jobs). The jobs are stored/defined within SQL Server, and they contain one or more steps that define what happens when the job runs. These jobs may run on demand, as well as via a trigger or predefined schedule. This service is very important when determining why a certain job did not run as planned — often it is as simple as the SQL Server Agent service not running.

What is the default port number for SQL Server?
If enabled, the default instance of Microsoft SQL Server listens on TCP port 1433. Named instances are configured for dynamic ports, so an available port is chosen when SQL Server starts. When connecting to a named instance through a firewall, configure the Database Engine to listen on a specific port, so that the appropriate port can be opened in the firewall.

56 comments:

  1. As a fresher, I learned much precious information about .net from your blog, Thanks for taking time to discuss about the beauty of dot net language.
    Regards,
    DOT NET Training in Chennai|DOT NET Course in Chennai

    ReplyDelete
  2. Candid institute Spring and Hibernate course provides a comprehensive introduction to Spring and Hibernate open source frameworks as well as Web Services and AJAX. Suitable for both Spring3/Hibernate3 and Spring4/Hibernate4, the course includes coverage of the core Spring and Hibernate capabilities, as well as the integration capabilities provided by Spring.
    spring hibernate training institutes in chennai

    ReplyDelete
  3. Very interesting content which helps me to get the in depth knowledge about the technology.Data Mining Projects Center in Chennai | Data Mining Projects Center in Velachery

    ReplyDelete
  4. Needed to compose you a very little word to thank you yet again regarding the nice suggestions you’ve contributed here.

    https://www.besanttechnologies.com/training-courses/java-training

    ReplyDelete
  5. Needed to compose you a very little word to thank you yet again regarding the nice suggestions you’ve contributed here.

    selenium Training in Bangalore

    ReplyDelete
  6. Your new valuable key points imply much a person like me and extremely more to my office workers. With thanks; from every one of us.


    white label website builder

    mobile website builder

    ReplyDelete
  7. Appreciation for really being thoughtful and also for deciding on certain marvelous guides most people really want to be aware of.

    AWS Training in Chennai

    ReplyDelete
  8. Your good knowledge and kindness in playing with all the pieces were
    very useful. I don’t know what I would have done if I had not
    encountered such a step like this.digital marketing training in chennai

    seo training in chennai

    ReplyDelete
  9. It's very great post... Really you are... done a wonderful job Keep up the good work and continue sharing like this.
    Java Training in Chennai | Java Training in Kanchipuram

    ReplyDelete
  10. This comment has been removed by the author.

    ReplyDelete
  11. Very informative blog. Thanks for sharing such good information and keep on updating.
    Android Training in Chennai | Android Training in Kanchipuram

    ReplyDelete
  12. This is really too useful and have more ideas from yours. keep sharing many techniques and thanks for sharing the information.
    Robotic Process Automation Training in Chennai | Robotic Process Automation Training in Taramani

    ReplyDelete
  13. It's very great post... Really you are... done a wonderful job Keep up the good work and continue sharing like this.
    Microsoft Advanced Excel and VBA Macros Training in Chennai | Microsoft Advanced Excel and VBA Macros Training in Kanchipuram

    ReplyDelete
  14. I have read your blog its very attractive and impressive. I like it your blog.
    iOS Training in Chennai | iOS Training in Tambaram

    ReplyDelete
  15. Thanks its Great Article… I love to read your articles because your writing style is too good, its is very very helpful for all of us.
    Dot net Training in Chennai | Dot net Training in Perungudi

    ReplyDelete
  16. Good Post! Thank you so much for sharing this pretty post, it was so good to read and useful to improve my knowledge.
    Python Training in Chennai | Python Training in Madipakkam

    ReplyDelete
  17. very interesting topic.Helps to gain knowledge about lot of information. Thanks for posting information in this blog.
    ISTQB Training in Chennai | ISTQB Training in Guindy

    ReplyDelete
  18. Nice post. This post is very helpful. Thank you so much for sharing this post….
    Software Testing Training in Chennai | Software Testing Training in Saidapet

    ReplyDelete
  19. Great post and informative blog.it was awesome to read, thanks for sharing this great content to my vision.
    Graphic Designing Training in Chennai | Graphic Designing Training in Keelkattalai

    ReplyDelete
  20. very interesting topic.Helps to gain knowledge about lot of information. Thanks for posting information in this blog.
    Ethical Hacking Training in Chennai | Ethical Hacking Training in Thiruvanmiyur

    ReplyDelete
  21. This is a nice post in an interesting line of content.Thanks for sharing this article.
    Android Training in Chennai | Android Training in Porur

    ReplyDelete
  22. I have read your blog. It’s very informative and useful blog. You have done really great job. Keep update your blog.
    Java Training in Chennai | Java Training in St.Thomas Mount

    ReplyDelete
  23. I have read your blog its very attractive and impressive. I like it your blog...
    Java Training in Chennai | Java Training in Meenambakkam

    ReplyDelete
  24. very interesting topic.Helps to gain knowledge about lot of information. Thanks for posting information in this blog.
    Best Software Testing Training Institute in Chennai | Best Software Testing Training Institute in T.Nagar

    ReplyDelete
  25. Awesome post. Really you are shared very informative concept... Thank you for sharing. Keep on updating...
    Software Testing Course in Chennai | Software Testing Course in Perungudi

    ReplyDelete
  26. Pretty article! I found some useful information in your blog, it was awesome to read, thanks for sharing this great content to my vision, keep sharing.
    Best Ethical Hacking Training Institute in Chennai | Best Ethical Hacking Training Institute in Velachery

    ReplyDelete
  27. Very interesting content which helps me to get the in depth knowledge about the technology.
    Software Testing Course in Chennai | Software Testing Course in Keelkattalai

    ReplyDelete
  28. Nice Post! It is really interesting to read from the beginning and Keep up the good work and continue sharing like this.
    IOS Training Institute in Chennai | IOS Training Institute in Pallavaram

    ReplyDelete
  29. Your blog is really amazing with smart and cute content.keep updating such an excellent article..
    Selenium Automation Course in Chennai | Selenium Automation Course in Pallikaranai

    ReplyDelete
  30. Pretty article! I found some useful information in your blog, it was awesome to read, thanks for sharing this great content to my vision, keep sharing.
    Best Java J2EE Training Institute in Chennai | Best Java J2EE Training Institute in Madipakkam

    ReplyDelete
  31. Nice Post! It is really interesting to read from the beginning and Keep up the good work and continue sharing like this.
    Advanced SoftwareTesting Course in Chennai | Advanced SoftwareTesting Course in Saidapet

    ReplyDelete
  32. This comment has been removed by the author.

    ReplyDelete
  33. Really i enjoyed very much. And this may helpful for lot of peoples. So you are provided such a nice and great article within this.
    Best Java J2EE Training Institute in Chennai | Best Java J2EE Training Institute in Medavakkam

    ReplyDelete
  34. This comment has been removed by the author.

    ReplyDelete
  35. Thanks for sharing this valuable information.. I saw your website and get more details..Nice work...
    Microsoft Azure Training Institute in Chennai | Microsoft Azure Training Institute in Thiruvanmiyur

    ReplyDelete
  36. Pretty article! I found some useful information in your blog, it was awesome to read, thanks for sharing this great content to my vision, keep sharing.
    IOS Training Institute in Chennai | IOS Training Institute in Tambaram

    ReplyDelete
  37. This is a nice post in an interesting line of content.Thanks for sharing this article.
    Summer Course Training in Chennai | Summer Course Training in OMR

    ReplyDelete
  38. I am reading your post from the beginning, it was so interesting to read & I feel thanks to you for posting such a good blog, keep updates regularly.
    Vacation Course Training Institute in Chennai | Vacation Course Training Institute in Pallavaram

    ReplyDelete
  39. Great post and informative blog.it was awesome to read, thanks for sharing this great content to my vision.
    Summer Course in Chennai | Summer Course in Medavakkam

    ReplyDelete
  40. Nice Post! It is really interesting to read from the beginning and Keep up the good work and continue sharing like this.
    Best Microsoft Azure Training Institute in Chennai | Best Microsoft Azure Training Institute in Pallikaranai

    ReplyDelete
  41. Really very great information to be provided and the All points discussed were worth reading and i’ll surely work with them all one by one.
    Best C and C++ Programming Training Academy in Kanchipuram

    ReplyDelete
  42. Awesome post. Really you are shared very informative concept... Thank you for sharing. Keep on updating...

    Best Java Training Academy in Kanchipuram

    ReplyDelete