SQL Server DEV Q and A

This page contains SQL Server Developer Interview Questions. I would be adding many questions and answers from time to time, I suggest you to bookmark it, I’m sure you will have lots of Interview questions that are useful for your Interview preparation.

1. Can you write a SELECT statement without FROM Clause?

  • Yes you can write a SELECT statement without having a FROM Clause.

         Examples.

         SELECT @@VERSION

         OR

         SELECT ‘HELLO WORLD’

2. What are the different constraints in SQL Server?

  • Primary Key Constraint.
  • Foreign Key Constraint.
  • Unique Key Constraint.
  • Default Constraint.
  • Check Constraint.

3. How do you display only few # of rows from any table?

  • You can use TOP Clause to return only the # of rows from an underlying table.

4. What are the enhancements in SQL Server 2005 over SQL Server 2000 from a developer’s stand point?

  • The below is a list of enhancements in SQL Server 2005 from a developer’s point of view.
    • Common Table Expressions (CTE)
    • Covering Indexes.
    • Schemas
    • EXCEPT and INTERSECT
    • PIVOT and UNPIVOT
    • Synonym

5. How will you rename a database or a database Object using T-SQL?

  • Using SP_Rename system stored procedure you can rename a database object such as a table, view, stored procedure etc. Using SP_RenameDB you can rename a database. For more information read one of my blogs on this topic — Renaming database and database objects using T-SQL

6. What is the benefit of WHERE Clause?

7. How can you display the top n no. of records in a table based on a column?

  • It is possible to return such a result-set by using Top n clause along with Order By the column name.

8. How many Clustered and Non-Clustered Indexes can be created on a sinlge table?

  • A table can contain a single Clustered Index at any time, where as the limit for Non-Clustered Indexes is 249. SQL Server 2008 supports upto 999 Non-Clustered Indexes.

9. How many columns can be created in a single table?

  • A normal table, can contain 1024 columns and wide table can contain 30,000 columns per table.
Added on Nov 16th 2011

10. What are constraints? What are the different constraints in SQL Server?

  • Constraints are the way using which we can enforce and maintain database Integrity.
  • Primary Key, Foreign Key, Unique, Check, Not Null are the various Constraints available in SQL Server.

11. What is the difference between Inner Join and Outer Join?

  • Inner Joins return the matching records in both the tables so the result set displays only the data that is matching in both the tables on the joining condition. Where as the result set in an Outer Join displays the matching data in both tables along with data from one of the tables where there is no matching records. The table from which additional data is to be displayed is based on the Right or Left Outer Join.

12. What is difference between Primary Key and Unique Key?

  • Primary Key and Unique Key ensures that all the records are unique with the exception that a Unique Key constraint allows a single null value where as a Primary Key constraint does not allow null value.
  • Multiple Unique Key constraints can be defined on a single table where as a only 1 Primary Key constraint can be defined.

13. What is the functionality of UNION in a T-SQL statement?

  • UNION Operator combines data from two or more result sets and displays one final result set. The columns in the result sets should match in all of the result sets. UNION does not display duplicate rows (if there are any). 

Added on Dec 7th 2011

14. What is the functionality of UNION ALL in a T-SQL statement?

  • UNION ALL operator combines data from two or more result sets and displays one final result set including duplicate records. The columns in the result sets should match in all of the result sets. (Thanks to Ash posting a comment and bringing my notice about the mistake, it is corrected now)…

15. What is the difference between a Delete and Truncate?

  • Delete is a logged operation, by that every record that is deleted is written / logged in the Transaction Log of the database, there by enabling the user to recover what was deleted. A Delete statement can be controlled by using a filtering condition, using “WHERE” clause.
  • Truncate deletes the entire data of the table by deleting all the data pages of the table. It does not write in the log files, so it is quite fast, also it is not possible to recover the data  using the Log backups, the only possibility is to recover back before the Truncate was run, using any and all kinds of available backups.

16. What is the difference between a Truncate Table and Drop Table?

  • Truncate deletes the entire data in a table, but the table structure is still intact, the Indexes, constraints etc are all still available.
  • Drop table deletes the table itself, resulting that the table and the underlying indexes, constraints etc are also deleted.

Feedback and comments are appreciated. That is the only way I know this blog was helpful to you and it motivate me to write more.

Email me at bru@learnsqlwithbru.com

– Bru Medishetty

20 thoughts on “SQL Server DEV Q and A

  1. I want SQL Server developer question and answers with scenario.
    Its like behavioral question and answer.

  2. Will be adding new ones. Recently, I added few more question and answers and I am sure there are plenty to come in the near future.

    All the Best,
    Bru Medishetty

  3. Hi, Can you please clarify about UNION ALL. Does it really ignore all the duplicates? I thought it includes all the duplicates.

  4. Hi Ash, thanks for bringing that into notice. It should have been the other way around for UNION and UNION ALL.. (I am going to make changes in the answers)

    And to confirm, yes you are right, UNION ALL includes duplicates…

    Sorry for the confusion..

  5. Thanks for the quick clarification. I’m looking into diving deeper into BI or ETL in specific in terms of data analysis, patterns and trend changes. Is there any good site or book you would suggest?

  6. Ash,

    From what you say, it looks like you are looking to dig deep into SQL Server Analysis Services right? Coz ETL in SQL Server would be SSIS and the rest would be in SSAS, am I right? Trying to understand better before making any suggestions..

  7. Hi,

    Truncate is a data pages level operation so it also can be written by recover the log at data pages level.

Leave a Reply