nach oben

2019 | Buch

Kapitel lesen Erstes Kapitel lesen

Dynamic SQL

Applications, Performance, and Security in Microsoft SQL Server

verfasst von: Edward Pollack

Verlag: Apress

Enthalten in: Springer Professional "Wirtschaft+Technik" , Springer Professional "Technik" , Springer Professional "Wirtschaft"

Einloggen, um Zugang zu erhalten

Über dieses Buch

Take a deep dive into the many uses of dynamic SQL in Microsoft SQL Server. This edition has been updated to use the newest features in SQL Server 2016 and SQL Server 2017 as well as incorporating the changing landscape of analytics and database administration. Code examples have been updated with new system objects and functions to improve efficiency and maintainability.

Executing dynamic SQL is key to large-scale searching based on user-entered criteria. Dynamic SQL can generate lists of values and even code with minimal impact on performance. Dynamic SQL enables dynamic pivoting of data for business intelligence solutions as well as customizing of database objects. Yet dynamic SQL is feared by many due to concerns over SQL injection or code maintainability.

Dynamic SQL: Applications, Performance, and Security in Microsoft SQL Server helps you bring the productivity and user-satisfaction of flexible and responsive applications to your organization safely and securely. Your organization’s increased ability to respond to rapidly changing business scenarios will build competitive advantage in an increasingly crowded and competitive global marketplace. With a focus on new applications and modern database architecture, this edition illustrates that dynamic SQL continues to evolve and be a valuable tool for administration, performance optimization, and analytics.

What You'ill Learn

Build flexible applications that respond to changing business needs

Take advantage of creative, innovative, and productive uses of dynamic SQL

Know about SQL injection and be confident in your defenses against it

Address performance concerns in stored procedures and dynamic SQL

Troubleshoot and debug dynamic SQL to ensure correct results

Automate your administration of features within SQL Server

Who This Book is For

Developers and database administrators looking to hone and build their T-SQL coding skills. The book is ideal for developers wanting to plumb the depths of application flexibility and troubleshoot performance issues involving dynamic SQL. The book is also ideal for programmers wanting to learn what dynamic SQL is about and how it can help them deliver competitive advantage to their organizations.

Inhaltsverzeichnis

Frontmatter

Chapter 1. What Is Dynamic SQL?

Abstract

T-SQL is a scripting language that expands with each new release of SQL Server. Success in the world of database development and administration requires flexibility and the ability to adapt constantly to new situations, technologies, and demands. Many of the challenges we face are unknowns, or situations in which we cannot know exactly the data we will be working with until runtime. In order to find the optimal solution in the face of unknowns, one of the best tools at our disposal is dynamic SQL.

Edward Pollack

Chapter 2. Protecting Against SQL Injection

Abstract

There are few SQL vulnerabilities as commonly exploited as SQL injection. This form of database attack has destroyed companies, ruined careers, and is a constant challenge for security officers. As database professionals, data is our greatest asset, and it is our responsibility to guard it above all else. SQL injection is not limited only to dynamic SQL, but is a technique that can be applied to many areas of SQL Server. Therefore, understanding and defending against it are among the most important priorities when considering SQL Server security.

Edward Pollack

Chapter 3. Large Scale Searching

Abstract

One of the most common, versatile, and useful ways to implement dynamic SQL is when performing complex searches. Consider your favorite web sites and the search functionality provided in each. For some, the search may be simple: go to the single text box in the top-right corner, enter some text, and results are returned. For others, such as searching for a hotel, a flight, or a car rental, they can involve dozens (or more) of optional parameters. Dynamic SQL can allow us to pare down our search queries in order to only process what is needed. In addition, we can also greatly customize the search, as well as the data returned. We can even analyze the input to determine the correct course of action, based on its structure.

Edward Pollack

Chapter 4. Permissions and Security

Abstract

In Chapter 2, we did a thorough look into SQL injection and the multitude of ways in which poorly written TSQL can become a target for malicious attacks. Now, we are going to step back and review best practices for SQL Server security, with a focus on dynamic SQL and its typical use cases. Security is an immense topic; one that could easily consume thousands of pages given the opportunity. It also evolves with each day that passes, as new products are released and vulnerabilities are found in older ones. Our goal is to cover the most important and common places where we need to take care while developing database solutions using dynamic SQL, without veering too far into one-offs or edge cases.

Edward Pollack

Chapter 5. Managing Scope

Abstract

Our discussions of security have alluded to the fact that dynamic SQL does not run in the same scope as the remainder of TSQL in the same stored procedure. In addition to breaking the ownership chain, variables declared locally and globally will not have easy access to each other. When writing application code or stored procedures, passing variables into and out requires a bit of planning, ensuring that the inputs and outputs are correct. Working with dynamic SQL is very similar, and luckily we have a variety of ways in which to manage variables effectively without any level of inconvenience.

Edward Pollack

Chapter 6. Performance Optimization

Abstract

No discussion of dynamic SQL would be complete without a dive into its performance. Dynamic SQL can greatly improve performance, but can also increase complexity if not used effectively. As was the case with security, optimization is a topic that could easily occupy significantly larger books than this one. As such, we will try to maintain a focus on dynamic SQL and any performance concerns that relate to it.

Edward Pollack

Chapter 7. Scalable Dynamic Lists

Abstract

Generating lists of data is a common task with a wide variety of use cases. Perhaps we want to output data to an application or file with a specific formatting or syntax. Maybe the best format to read a list of data is in a single line, rather than a tabular format. Maybe you want to store data in a table using a particular string format that needs to be quickly built prior to storing it.

Edward Pollack

Chapter 8. Parameter Sniffing

Abstract

In Chapter 6 we introduced a number of tools, methods, and tips for performance optimization. A critical component that was briefly discussed was the query plan cache. Whenever a query is executed for the first time, an execution plan is generated by the query optimizer. This process is expensive, and therefore it is beneficial to minimize the work that is performed by it. Execution plans are placed into the query plan cache when optimization is complete, where they will remain until sufficient changes occur in execution, available memory, or the underlying data to push that plan out of cache.

Edward Pollack

Chapter 9. Dynamic Pivot and Unpivot

Abstract

PIVOT is an extremely efficient way to alter the structure of a result set, expanding a single column of values into a set of separate columns. UNPIVOT does the exact opposite, taking a set of columns and resolving them into a single output column. Both of these operators can be very useful in reporting, analytics, or when trying to format existing data into a specific structure as required by an application.

Edward Pollack

Chapter 10. Solving Common Problems

Abstract

Dynamic SQL presents a unique opportunity to take common database problems or limitations and solve them quickly. Oftentimes we run into frustrating situations where we are managing different databases, schemas, or settings, and there is no easy way to make changes to a mixed set of objects. This chapter provides an opportunity to explore some examples of situations where dynamic SQL can resolve complex situations. In addition, we will provide general guidelines and techniques that could apply to any similar database problem.

Edward Pollack

Chapter 11. Applications of Dynamic SQL

Abstract

Once we have established the idea that dynamic SQL can solve a wide variety of challenges, we can begin to apply it to larger and more complex problems. Our goal is to leave you with a variety of practical scripts that can be brought into any database environment and tailored to a unique use. There are many real-world challenges for which dynamic SQL is an efficient solution and where we can accomplish a great deal of work in compact, reusable code.

Edward Pollack

Chapter 12. Index Usage and Maintenance

Abstract

For our final chapter, we will apply dynamic SQL to one of its most fitting applications: the care and maintenance of our SQL Servers and databases. These tasks are all too often associated with hard-coded, hacked, messy T-SQL that is written to be forgotten. Of all the places where a developer might copy and paste code from a dubious source, this is the most common.

Edward Pollack

Backmatter

Titel: Dynamic SQL
verfasst von: Edward Pollack
Verlag: Apress
Electronic ISBN: 978-1-4842-4318-3
Print ISBN: 978-1-4842-4317-6
DOI: https://doi.org/10.1007/978-1-4842-4318-3