SQL Server Questions and Answers

This course is designed to help you better understand how to use SQL Server effectively. The course presents many of the common misconceptions about SQL Server, and then carefully debunks those misconceptions with clear explanations and short but compelling demos, showing you how SQL Server really works.
Course info
Rating
(279)
Level
Intermediate
Updated
Jan 24, 2012
Duration
2h 13m
Table of contents
Introduction
Retrieving IDENTITY value using @@IDENTITY
Concepts Related to Identity Values
Difference between WHERE and HAVING
Order in WHERE clause
Concepts Around Temporary Tables and Table Variables
Are stored procedures pre-compiled?
UNIQUE INDEX and NULLs problem
DELETE VS TRUNCATE
Locks and Duration of Transactions
Nested Transaction and Rollback
Understanding Date/Time Datatypes
Differences between VARCHAR and NVARCHAR datatypes
Precedence of DENY and GRANT security permissions
Identify Blocking Process
NULLS usage with Dynamic SQL
Appendix Tips and Tricks with Tools
Description
Course info
Rating
(279)
Level
Intermediate
Updated
Jan 24, 2012
Duration
2h 13m
Description

This course is designed to help you better understand how to use SQL Server effectively. The course presents many of the common misconceptions about SQL Server, and then carefully debunks those misconceptions with clear explanations and short but compelling demos, showing you how SQL Server really works. This course is for anyone working with SQL Server databases who wants to improve her knowledge and understanding of this complex platform.

About the author
About the author

Pinal Dave is a Pluralsight Developer Evangelist.

More from the author
Getting Started with MariaDB
Beginner
2h 23m
Nov 2, 2018
Monitoring MySQL with Performance Schema
Intermediate
1h 59m
Apr 28, 2017
More courses by Pinal Dave
About the author

Vinod Kumar has worked with SQL Server extensively since joining the industry over a decade ago. Before joining Microsoft, he was a Microsoft MVP for SQL Server for more than 3 years. He now works with MTC as a Technology Architect. He is a well-known speaker at all major Microsoft and third party technical conferences.

More from the author
SQL Server Performance: Indexing Basics
Intermediate
1h 58m
Mar 5, 2012
Section Introduction Transcripts
Section Introduction Transcripts

Introduction
Welcome to this course on SQL Server Questions and Answers. I am Pinal Dave, founder of sqlauthority. com I'm co-authoring this course with Vinod Kumar, founder of extremeexperts. com. This course is conversation revolving around misconceptions inside SQL Server. The outline for this course has five major components. Under Misconception, we describe the anomaly behavior, followed by Resolution, which explains the discrepancy of the concept. Each section will have a demo, which clearly illustrates why the misconceptions and the reality with the resolution. The module ends with Additional Information and Summary.

Retrieving IDENTITY value using @@IDENTITY
In this module, we'll see retrieving IDENTITY value using @@IDENTITY. Hey, Vinod, I've been using @@IDENTITY value to get the latest identity value from the table in current session. It has always worked for me fine. What is the misconception around it? Can you explain?

Concepts Related to Identity Values
In this module, we will look at the various identity value misconceptions. I've been using identity value for ages, and have seen developers having a preset view when it comes to identity values. Can you help me understand? Does identity value always need to be positive? Does identity value increment by one? Can identity value be negative?

Difference between WHERE and HAVING
In this module, we'll explore the difference between WHERE and HAVING clause. Hey Vinod, I quite often seen developers using HAVING clause instead of WHERE clause and WHERE clause instead of HAVING clause. The argument which they have is when they use either of them, the result is the same. Would you please explain, are they really the same or is there some difference between them?

Order in WHERE clause
In this module, we will discuss the order of conditions used in WHERE clause. Hey Vinod, I often see developers rewriting conditions in different order in SQL queries. Their argument is reordering the conditions improves performance because a different index can be used. Does really reordering change any behavior? My experience in the past suggests otherwise. Can you show us examples to this misconception?

Concepts Around Temporary Tables and Table Variables
In this module, let us explore the concepts around Temporary Tables and Table Variables. Have you known, I have heard this from multiple people Table Variables exist in a memory, while Temporary Tables exist in the physical drive. Moreover, one of the limitations is that Table Variables cannot be indexed, and I suspect it's around Transactions. I have heard that both Table Variables and Temporary Tables adhere to Transaction score. How much of these assumptions are true? Can you show us some insights?

Are stored procedures pre-compiled?
In this module, we are going to talk about Stored Procedure and Pre-Compilation. Hey Vinod, there is a very common misconception around stored procedure regarding compilation. Widely people believe that stored procedures are pre-compiled, they are compiled when they are created. Is it true? Can you explain this with an example, what is the reality?

UNIQUE INDEX and NULLs problem
In this module, we'll talk about UNIQUE INDEX and NULLs. Hey Vinod, I have a business need, I want a UNIQUE constraint on column, however the column contains multiple NULL values. What I mean is that, in my column, there are many values with NULL. Now, as I understand, UNIQUE constraint can have of a single row with NULL value. If there are more rows with NULL values, we cannot create UNIQUE constraint on it. What should I do? As my business says, I want UNIQUE constraint, but I want to allow multiple NULL values.

DELETE VS TRUNCATE
In this module, we'll understand the difference between DELETE and TRUNCATE. Hey Vinod, I often hear the argument that TRUNCATE cannot be rolled back. And this comes from the fact that TRUNCATE doesn't get logged. How true is this concept? Is this misconception? Can you explain me if possible with an example?

Locks and Duration of Transactions
In this module, we will learn about locking behavior and the duration of locks inside the transaction scope. Hey Vinod, I see this fight constantly between my developers and DBAs alike, that locks are held for the duration of the transactions, or the locks are always held for the duration of the statement. Who has got this concept right? Will you be able to clarify this in a simple examples?

Nested Transaction and Rollback
In this module, we'll understand the behavior of Nested Transactions with ROLLBACK. Hey Vinod, in recent times, I have seen a developer using a Nested Transaction because he was expecting that in case of error, he will be able to partially roll back the transaction. I believe, in SQL Server, Nested Transaction does not exist. Would you please help me explain this concept to him?

Understanding Date/Time Datatypes
In this module, we will understand the usage and significance of Date/Time Datatypes inside SQL Server. Hey Vinod, there is an understanding that precision for SmallDateTime is one second. How far is this true? And secondly, there is no way to store and consume date and time separately inside SQL Server. The only option around is Date/Time Datatype. Is this a fact? Can you explain to us the concepts?

Differences between VARCHAR and NVARCHAR datatypes
In this module, we'll look at the differences between VARCHAR and NVARCHAR. Hey Vinod, I have an application where they have gone ahead, and used NVARCHAR everywhere to store character data. Does storing in VARCHAR or NVARCHAR data types cause any differences within SQL Server? Aren't they the same? Can you help us understand these data types?

Precedence of DENY and GRANT security permissions
In this module, we'll learn about DENY and GRANT security permissions. Hey Vinod, I read it somewhere that DENY permissions inside SQL Server always take precedence over GRANT operation. Now, this is very interesting concept related to security. How far it is true, can you explain it to me, please?

Identify Blocking Process
In this module, we'll talk about identifying blocking processes. Hey, Vinod, do budding DBAs like me always have this concept that blocking cannot be identified? If we cannot identify blocking, we cannot rectify it. Is this is a misconception? What is the reality behind it? If blocking can be identified, what are the ways to identify it? Can you help me understand this?

NULLS usage with Dynamic SQL
In this module, we'll explore the usage of nulls with dynamic SQL queries. Hey, Vinod, I have an experienced developer who says using static SQL is always better than dynamic SQL. His usage of different SQL server function to mask the null still better than dynamic SQL? Will you be able to show an effective demo to help my understanding?