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 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.
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.
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?