SQL SERVER – Interview Questions and Answers – Frequently Asked Questions – Complete Downloadable List – Day 0 of 31

This blog post is running list of the blog posts in the series of Interview Questions and Answers. At the end of the 31st day of the month, a FREE PDF will be posted here which can be downloadable for offline review.

SQL SERVER – Interview Questions and Answers – Frequently Asked Questions – Introduction – Day 1 of 31

In this very first blog post – various aspect of the interview questions and answers are discussed. Some people like the subject for their helpful hints and thought provoking subject, and others dislike these posts because they feel it is nothing more than cheating.  I’d like to discuss the pros and cons of a Question and Answer format here.

SQL SERVER – Interview Questions and Answers – Frequently Asked Questions – Day 2 of 31

  • What is RDBMS?
  • What are the Properties of the Relational Tables?
  • What is Normalization?
  • What is De-normalization?
  • How is ACID property related to Database?
  • What are the Different Normalization Forms?

SQL SERVER – Interview Questions and Answers – Frequently Asked Questions – Day 3 of 31

  • What is a Stored Procedure?
  • What is a Trigger?
  • What are the Different Types of Triggers?
  • What is a View?
  • What is an Index?
  • What is a Linked Server?
  • What is a Cursor?
  • What is Collation?

SQL SERVER – Interview Questions and Answers – Frequently Asked Questions – Day 4 of 31

  • What is the Difference between a Function and a Stored Procedure?
  • What is subquery? Explain the Properties of a Subquery?
  • What are Different Types of Join?
  • What are Primary Keys and Foreign Keys?
  • What is User-defined Functions? What are the types of User-defined Functions that can be created?

SQL SERVER – Interview Questions and Answers – Frequently Asked Questions – Day 5 of 31

  • What is an Identity?
  • What is DataWarehousing?
  • What languages BI uses to achieve the goal?
  • What is Standby Servers? Explain Types of Standby Servers.
  • What is Dirty Read?
  • Why can’t I use Outer Join in an Indexed View?
  • What is the Correct Order of the Logical Query Processing Phases?

SQL SERVER – Interview Questions and Answers – Frequently Asked Questions – Day 6 of 31

  • Which TCP/IP port does the SQL Server run on? How can it be Changed?
  • What are the Difference between Clustered and a Non-clustered Index?
  • What are the Different Index Configurations a Table can have?
  • What are Different Types of Collation Sensitivity?
  • What is OLTP (Online Transaction Processing)?
  • What’s the Difference between a Primary Key and a Unique Key?
  • What is Difference between DELETE  and TRUNCATE Commands?

SQL SERVER – Interview Questions and Answers – Frequently Asked Questions – Day 7 of 31

  • What are Different Types of Locks?
  • What are Pessimistic Lock and Optimistic Lock?
  • When is the use of UPDATE_STATISTICS command?
  • What is the Difference between a HAVING clause and a WHERE clause?
  • What is Connection Pooling and why it is Used?
  • What are the Properties and Different Types of Sub-Queries?
  • What are the Authentication Modes in SQL Server? How can it be Changed?

SQL SERVER – Interview Questions and Answers – Frequently Asked Questions – Day 8 of 31

  • Which Command using Query Analyzer will give you the Version of SQL Server and Operating System?
  • What is an SQL Server Agent?
  • Can a Stored Procedure call itself or a Recursive Stored Procedure? How many levels of SP nesting is possible?
  • What is Log Shipping?
  • Name 3 ways to get an Accurate Count of the Number of Records in a Table?
  • What does it mean to have QUOTED_IDENTIFIER ON? What are the Implications of having it OFF?
  • What is the Difference between a Local and a Global Temporary Table?
  • What is the STUFF Function and How Does it Differ from the REPLACE Function?
  • What is PRIMARY KEY?
  • What is UNIQUE KEY Constraint?
  • What is FOREIGN KEY?

SQL SERVER – Interview Questions and Answers – Frequently Asked Questions – Day 9 of 31

  • What is CHECK Constraint?
  • What is NOT NULL Constraint?
  • What is the difference between UNION and UNION ALL?
  • What is B-Tree?
  • How to get @@ERROR and @@ROWCOUNT at the Same Time?
  • What is a Scheduled Job or What is a Scheduled Task?
  • What are the Advantages of Using Stored Procedures?
  • What is a Table Called, if it has neither Cluster nor Non-cluster Index? What is it Used for?
  • Can SQL Servers Linked to other Servers like Oracle?
  • What is BCP? When is it Used?

SQL SERVER – Interview Questions and Answers – Frequently Asked Questions – Day 10 of 31

  • What Command do we Use to Rename a db, a Table and a Column?
  • What are sp_configure Commands and SET Commands?
  • How to Implement One-to-One, One-to-Many and Many-to-Many Relationships while Designing Tables?
  • What is Difference between Commit and Rollback when Used in Transactions?
  • What is an Execution Plan? When would you Use it? How would you View the Execution Plan?

SQL SERVER – Interview Questions and Answers – Frequently Asked Questions – Day 11 of 31

  • What is Difference between Table Aliases and Column Aliases? Do they Affect Performance?
  • What is the difference between CHAR and VARCHAR Datatypes?
  • What is the Difference between VARCHAR and VARCHAR(MAX) Datatypes?
  • What is the Difference between VARCHAR and NVARCHAR datatypes?
  • Which are the Important Points to Note when Multilanguage Data is Stored in a Table?
  • How to Optimize Stored Procedure Optimization?
  • What is SQL Injection? How to Protect Against SQL Injection Attack?
  • How to Find Out the List Schema Name and Table Name for the Database?
  • What is CHECKPOINT Process in the SQL Server?

SQL SERVER – Interview Questions and Answers – Frequently Asked Questions – Day 12 of 31

  • How does Using a Separate Hard Drive for Several Database Objects Improves Performance Right Away?
  • How to Find the List of Fixed Hard Drive and Free Space on Server?
  • Why can there be only one Clustered Index and not more than one?
  • What is Difference between Line Feed (\n) and Carriage Return (\r)?
  • Is It Possible to have Clustered Index on Separate Drive From Original Table Location?
  • What is a Hint?
  • How to Delete Duplicate Rows?
  • Why the Trigger Fires Multiple Times in Single Login?

SQL SERVER – Interview Questions and Answers – Frequently Asked Questions – Day 13 of 31

  • What is Aggregate Functions?
  • What is Use of @@ SPID in SQL Server?
  • What is the Difference between Index Seek vs. Index Scan?
  • What is the Maximum Size per Database for SQL Server Express?
  • How do We Know if Any Query is Retrieving a Large Amount of Data or very little data?
  • What is the Difference between GRANT and WITH GRANT while Giving Permissions to the User?
  • How to Create Primary Key with Specific Name while Creating a Table?
  • What is T-SQL Script to Take Database Offline – Take Database Online
  • How to Enable/Disable Indexes?
  • Can we Insert Data if Clustered Index is Disabled?
  • How to Recompile Stored Procedure at Run Time?
  • Is there any Performance Difference between IF EXISTS (Select null from table) and IF EXISTS (Select 1 from table)?
  • What is Difference in Performance between INSERT TOP (N) INTO Table and Using Top with INSERT?
  • Does the Order of Columns in UPDATE statements Matter?

SQL SERVER – Interview Questions and Answers – Frequently Asked Questions – Day 14 of 31

  • What are the basic functions for master, msdb, model, tempdb and resource databases?
  • What is the Maximum Number of Index per Table?
  • Explain Few of the New Features of SQL Server 2008 Management Studio
  • Explain IntelliSense for Query Editing
  • Explain MultiServer Query
  • Explain Query Editor Regions
  • Explain Object Explorer Enhancements
  • Explain Activity Monitors

SQL SERVER – Interview Questions and Answers – Frequently Asked Questions – Day 15 of 31

  • What is Service Broker?
  • Where are SQL server Usernames and Passwords Stored in the SQL server?
  • What is Policy Management?
  • What is Database Mirroring?
  • What are Sparse Columns?
  • What does TOP Operator Do?
  • What is CTE?
  • What is MERGE Statement?
  • What is Filtered Index?
  • Which are the New Data Types Introduced in SQL SERVER 2008?

SQL SERVER – Interview Questions and Answers – Frequently Asked Questions – Day 16 of 31

  • What are the Advantages of Using CTE?
  • How can we Rewrite Sub-Queries into Simple Select Statements or with Joins?
  • What is CLR?
  • What are Synonyms?
  • What is LINQ?
  • What are Isolation Levels?
  • What is Use of EXCEPT Clause?
  • What is XPath?
  • What is NOLOCK?
  • What is the Difference between Update Lock and Exclusive Lock?

SQL SERVER – Interview Questions and Answers – Frequently Asked Questions – Day 17 of 31

  • How will you Handle Error in SQL SERVER 2008?
  • What is RAISEERROR? What is RAISEERROR?
  • How to Rebuild the Master Database?
  • What is the XML Datatype?
  • What is Data Compression?
  • What is Use of DBCC Commands?
  • How to Copy the Tables, Schema and Views from one SQL Server to Another?
  • How to Find Tables without Indexes?

SQL SERVER – Interview Questions and Answers – Frequently Asked Questions – Day 18 of 31

  • How to Copy Data from One Table to Another Table?
  • What is Catalog Views?
  • What is PIVOT and UNPIVOT?
  • What is a Filestream?
  • What is SQLCMD?
  • What do you mean by TABLESAMPLE?
  • What is ROW_NUMBER()?
  • What are Ranking Functions?
  • What is Change Data Capture (CDC) in SQL Server 2008?

SQL SERVER – Interview Questions and Answers – Frequently Asked Questions – Day 19 of 31

  • How can I Track the Changes or Identify the Latest Insert-Update-Delete from a Table?
  • What is the CPU Pressure?
  • How can I Get Data from a Database on Another Server?
  • What is the Bookmark Lookup and RID Lookup?
  • What is Difference between ROLLBACK IMMEDIATE and WITH NO_WAIT during ALTER DATABASE?
  • What is Difference between GETDATE and SYSDATETIME in SQL Server 2008?
  • How can I Check that whether Automatic Statistic Update is Enabled or not?
  • How to Find Index Size for Each Index on Table?
  • What is the Difference between Seek Predicate and Predicate?
  • What are Basics of Policy Management?
  • What are the Advantages of Policy Management?

SQL SERVER – Interview Questions and Answers – Frequently Asked Questions – Day 20 of 31

  • What are Policy Management Terms?
  • What is the ‘FILLFACTOR’?
  • Where in MS SQL Server is ’100’ equal to ‘0’?
  • What are Points to Remember while Using the FILLFACTOR Argument?
  • What is a ROLLUP Clause?
  • What are Various Limitations of the Views?
  • What is a Covered index?
  • When I Delete any Data from a Table, does the SQL Server reduce the size of that table?
  • What are Wait Types?
  • How to Stop Log File Growing too Big?
  • If any Stored Procedure is Encrypted, then can we see its definition in Activity Monitor?

SQL SERVER – Interview Questions and Answers – Frequently Asked Questions – Data Warehouseing Concepts – Day 21 of 31

  • What is Data Warehousing?
  • What is Business Intelligence (BI)?
  • What is a Dimension Table?
  • What is Dimensional Modeling?
  • What is a Fact Table?
  • What are the Fundamental Stages of Data Warehousing?
  • What are the Different Methods of Loading Dimension tables?
  • Describes the Foreign Key Columns in Fact Table and Dimension Table?
  • What is Data Mining?
  • What is the Difference between a View and a Materialized View?

SQL SERVER – Interview Questions and Answers – Frequently Asked Questions – Data Warehouseing Concepts – Day 22 of 31

  • What is OLTP?
  • What is OLAP?
  • What is the Difference between OLTP and OLAP?
  • What is ODS?
  • What is ER Diagram?

SQL SERVER – Interview Questions and Answers – Frequently Asked Questions – Data Warehouseing Concepts – Day 23 of 31

  • What is ETL?
  • What is VLDB?
  • Is OLTP Database is Design Optimal for Data Warehouse?
  • If denormalizing improves Data Warehouse Processes, then why is the Fact Table is in the Normal Form?
  • What are Lookup Tables?
  • What are Aggregate Tables?
  • What is Real-Time Data-Warehousing?
  • What are Conformed Dimensions?
  • What is a Conformed Fact?
  • How do you Load the Time Dimension?
  • What is a Level of Granularity of a Fact Table?
  • What are Non-Additive Facts?
  • What is a Factless Facts Table?
  • What are Slowly Changing Dimensions (SCD)?

SQL SERVER – Interview Questions and Answers – Frequently Asked Questions – Data Warehouseing Concepts – Day 24 of 31

  • What is Hybrid Slowly Changing Dimension?
  • What is BUS Schema?
  • What is a Star Schema?
  • What Snow Flake Schema?
  • Differences between the Star and Snowflake Schema?
  • What is Difference between ER Modeling and Dimensional Modeling?
  • What is Degenerate Dimension Table?
  • Why is Data Modeling Important?
  • What is a Surrogate Key?
  • What is Junk Dimension?
  • What is a Data Mart?
  • What is the Difference between OLAP and Data Warehouse?
  • What is a Cube and Linked Cube with Reference to Data Warehouse?
  • What is Snapshot with Reference to Data Warehouse?
  • What is Active Data Warehousing?
  • What is the Difference between Data Warehousing and Business Intelligence?
  • What is MDS?
  • Explain the Paradigm of Bill Inmon and Ralph Kimball.

SQL SERVER – Azure Interview Questions and Answers – Guest Post by Paras Doshi – Day 25 of 31

Paras Doshi has submitted 21 interesting question and answers for SQL Azure.

1.What is SQL Azure?
2.What is cloud computing?
3.How is SQL Azure different than SQL server?
4.How many replicas are maintained for each SQL Azure database?
5.How can we migrate from SQL server to SQL Azure?
6.Which tools are available to manage SQL Azure databases and servers?
7.Tell me something about security and SQL Azure.
8.What is SQL Azure Firewall?
9.What is the difference between web edition and business edition?
10.How do we synchronize On Premise SQL server with SQL Azure?
11.How do we Backup SQL Azure Data?
12.What is the current pricing model of SQL Azure?
13.What is the current limitation of the size of SQL Azure DB?
14.How do you handle datasets larger than 50 GB?
15.What happens when the SQL Azure database reaches Max Size?
16.How many databases can we create in a single server?
17.How many servers can we create in a single subscription?
18.How do you improve the performance of a SQL Azure Database?
19.What is code near application topology?
20.What were the latest updates to SQL Azure service?
21.When does a workload on SQL Azure get throttled?

SQL SERVER – Interview Questions and Answers – Guest Post by Malathi Mahadevan – Day 26 of 31

Malathi has asked a simple question which have several answer. Each answer make you think and ponder about the reality of the IT world. Look at the simple question – ‘What is the toughest challenge you have faced at your present job and how did you handle it’? and its various answers. Each answer has its own story.

SQL SERVER – Interview Questions and Answers – Guest Post by Rick Morelan – Day 27 of 31

Rick Morelan of Joes2Pros has written excellent blog post on subject how to find top N values. Most people are fully aware of how the TOP keyword works with a SELECT statement. After years preparing so many students to pass the SQL Certification I noticed they were pretty well prepared for job interviews too. Yes, they would do well in the interview but not great. There seemed to be a few questions that would come up repeatedly for almost everyone. Rick addresses similar question in his lucid writing skills.

SQL SERVER – Interview Questions and Answers – Guest Post by Nakul Vachhrajani – Day 28 of 31

Nakul explains the word – ‘interview ‘ with entire different angle to the subject. What really attacks in Nakul’s writing is his extremely clear ideas which are super crisp and to the point. Nakul talks about ‘Educational Qualification is never the only requirement’ with such a decent argument, that makes this article a must read.

SQL SERVER – Interview Questions and Answers – Guest Post by Feodor Georgiev – Day 29 of 31

Feodor is our most read gust blog author. He always comes up with interesting subject. Feodor start writing with interesting concept that – the job interview is like an enchanted dance between a potential employee and a potential employer which plays a defining role in their entire collaboration. This article has many witty comments and many interesting subject. A long article which has not a single dull moment.

SQL SERVER – Interview Questions and Answers – Guest Post by Jacob Sebastian – Day 30 of 31

Jacob Sebastian never likes to talk on easy subject. He always talks on difficult subjects. This time he talks about the philosophy of XML. The purpose of the questions and their answers given below is to ensure that the people who read them and learn them will get a basic understanding of the XML functionalities provided by SQL Server. Jacob makes the dry subject like XML to worth reading and learning it.

SQL SERVER – The Difficult Interview Question – Moment of the Life – Day 31 of 31

I guess the title says all – Interview is the moment of life. Final write up on this subject by myself.

Reference: Pinal Dave (http://blog.SQLAuthority.com)

104 thoughts on “SQL SERVER – Interview Questions and Answers – Frequently Asked Questions – Complete Downloadable List – Day 0 of 31

  1. Pingback: SQL SERVER – Interview Questions and Answers – Frequently Asked Questions – Introduction – Day 1 of 31 Journey to SQLAuthority

  2. Pingback: SQL SERVER – Interview Questions and Answers – Frequently Asked Questions – Day 2 of 31 Journey to SQLAuthority

  3. Hello Pinal,

    I am a front end .NET developer and i only know what i should know about SQL. ;)

    I would like you to post templates for creating new stored procedure for each desired work like for Insert, Update, Delete and simple Select.

    So each time when developer like me who is not so good in SQL can use those templates and can create the SPs according to their need. They then just need to write their own logic into template and boom its done.

    Hope you will consider it and no need to say that you are doing a marvelous job for all.

    Jayshit Ladani.

    Like

  4. Pingback: SQL SERVER – Interview Questions and Answers – Frequently Asked Questions – Day 3 of 31 Journey to SQLAuthority

  5. Pingback: SQL SERVER – Interview Questions and Answers – Frequently Asked Questions – Day 4 of 31 Journey to SQLAuthority

  6. Pingback: SQL SERVER – Interview Questions and Answers – Frequently Asked Questions – Day 5 of 31 Journey to SQLAuthority

  7. Pingback: SQL SERVER – Interview Questions and Answers – Frequently Asked Questions – Day 6 of 31 Journey to SQLAuthority

  8. I read all 6 days Q & A. Please more questions related to DBA Q & A. All Q & A are really important and very good simple to understand and remember the easy language of answers. Thanks

    Like

  9. Hi Pinal.

    You have shared very useful information.

    You website is very interesting, Although I’m a first time visitor of this site, Now I think I should have visit your website daily.

    Thanks for such a nice blog.

    Like

  10. Pingback: SQL SERVER – Interview Questions and Answers – Frequently Asked Questions – Day 7 of 31 Journey to SQLAuthority

  11. Nice list. The first 4 days are things developers (SQL or otherwise) should really try to understand. Do you plan to have any questions in this series relating to the BI components of SQL Server as well?

    Like

  12. Pingback: SQL SERVER – Interview Questions and Answers – Frequently Asked Questions – Day 8 of 31 Journey to SQLAuthority

  13. Pingback: SQL SERVER – Interview Questions and Answers – Frequently Asked Questions – Day 9 of 31 Journey to SQLAuthority

  14. Pingback: SQL SERVER – Interview Questions and Answers – Frequently Asked Questions – Day 10 of 31 Journey to SQLAuthority

  15. Pingback: SQL SERVER – Interview Questions and Answers – Frequently Asked Questions – Day 11 of 31 Journey to SQLAuthority

  16. Pingback: SQL SERVER – Interview Questions and Answers – Frequently Asked Questions – Day 12 of 31 Journey to SQLAuthority

  17. Pingback: SQL SERVER – Interview Questions and Answers – Frequently Asked Questions – Day 13 of 31 Journey to SQLAuthority

  18. Pingback: SQL SERVER – Interview Questions and Answers – Frequently Asked Questions – Day 14 of 31 Journey to SQLAuthority

  19. Pingback: SQL SERVER – Interview Questions and Answers – Frequently Asked Questions – Day 15 of 31 Journey to SQLAuthority

  20. Pingback: SQL SERVER – Interview Questions and Answers – Frequently Asked Questions – Day 16 of 31 Journey to SQLAuthority

  21. Pingback: SQL SERVER – Interview Questions and Answers – Frequently Asked Questions – Day 17 of 31 Journey to SQLAuthority

  22. Pingback: SQL SERVER – Interview Questions and Answers – Frequently Asked Questions – Day 18 of 31 Journey to SQLAuthority

  23. Pingback: SQL SERVER – Interview Questions and Answers – Frequently Asked Questions – Day 19 of 31 Journey to SQLAuthority

  24. Pingback: SQL SERVER – Interview Questions and Answers – Frequently Asked Questions – Day 20 of 31 Journey to SQLAuthority

  25. Pinal, This is very Nice article with brief explanation of each and every question. Here i have a request for you, can you please post more question on BI(SSIS,SSAS, SSRS, MDX) and this will be very very useful.

    Like

  26. Pingback: SQL SERVER – Interview Questions and Answers – Frequently Asked Questions – Data Warehouseing Concepts – Day 21 of 31 Journey to SQLAuthority

  27. Pingback: SQL SERVER – Interview Questions and Answers – Frequently Asked Questions – Data Warehouseing Concepts – Day 22 of 31 Journey to SQLAuthority

  28. Pingback: SQL SERVER – Interview Questions and Answers – Frequently Asked Questions – Data Warehouseing Concepts – Day 23 of 31 Journey to SQLAuthority

  29. Very simple and easy to understand and end to end information in a succint form. Great stuff Pinal. One small suggestion ask your reades what question they faced during their interviews and include in this wonderfull popular series.

    Like

  30. Pingback: SQL SERVER – Interview Questions and Answers – Frequently Asked Questions – Data Warehouseing Concepts – Day 24 of 31 Journey to SQLAuthority

  31. Puzzle: we want the insert, update, delete to the table name and the amount he received Stored procedures and dynamic range to be implemented?

    Like

  32. Pingback: SQL SERVER – Azure Interview Questions and Answers – Guest Post by Paras Doshi – Day 25 of 31 Journey to SQLAuthority

  33. Pingback: SQL SERVER – Interview Questions and Answers – Guest Post by Malathi Mahadevan – Day 26 of 31 Journey to SQLAuthority

  34. Pingback: SQL SERVER – Interview Questions and Answers – Guest Post by Rick Morelan – Day 27 of 31 Journey to SQLAuthority

  35. Pingback: SQL SERVER – Interview Questions and Answers – Guest Post by Feodor Georgiev – Day 29 of 31 Journey to SQLAuthority

  36. Thanks for posting.. Most of the time I do work on Framework and UI. But now I’ll start work on SQL. During my 2 years of experience, Today first time I used union all, partition by SQL query execution plan, and Indexing. Lot more to explore. Thanks that this series will help me a lot.

    Vikas Patel.

    Like

  37. Pingback: SQL SERVER – Interview Questions and Answers – Guest Post by Jacob Sebastian – Day 30 of 31 Journey to SQLAuthority

  38. I Really really like the way you write Pinal, it’s easy to understand for novise to experts (also enjoys alot). When are plannig to write your forst book on SQL Server Pinal??? Iam looking forward to it.
    Really great stuff Pinal! thanks
    RB

    Like

  39. Pingback: SQL SERVER – Win a Book a Day – Contest Rules – Day 0 of 35 Journey to SQLAuthority

  40. Pingback: SQL SERVER – The Difficult Interview Question – Moment of the Life – Day 31 of 31 Journey to SQLAuthority

  41. Hello Pinal,
    This is indeed one of the most useful articles for SQL learners.
    However I couldn’t find the link for downloading the offline version of this series. Could you please post it .

    Thanks a ton.
    Nilesh

    Like

  42. Hi pinal,

    In current comapny i am doing basic sql server admin work like (backup,restoration,user creation and maintaing part)

    i want to becaome sql server production dba

    would you please tell me how i become sql server production dba

    Like

  43. Pingback: SQLAuthority News – An Year Worth Remembering and Looking Forward to Better Next Year « SQL Server Journey with SQLAuthority

  44. Hi,
    I am a SQL database developer, Please can you send me a best answer of this question—-
    “give an example of an issue you had and how you went about solving it on your own”

    shawkat asif

    Like

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s