Pollack - Dynamic SQL: Applications, Performance, and Security
Here you can read online Pollack - Dynamic SQL: Applications, Performance, and Security full text of the book (entire story) in english for free. Download pdf and epub, get meaning, cover and reviews about this ebook. City: Berkeley;CA, year: 2016, publisher: Apress, genre: Computer. Description of the work, (preface) as well as reviews are available. Best literature library LitArk.com created for fans of good reading and offers a wide selection of genres:
Romance novel
Science fiction
Adventure
Detective
Science
History
Home and family
Prose
Art
Politics
Computer
Non-fiction
Religion
Business
Children
Humor
Choose a favorite category and find really read worthwhile books. Enjoy immersion in the world of imagination, feel the emotions of the characters or learn something new for yourself, make an fascinating discovery.
Dynamic SQL: Applications, Performance, and Security: summary, description and annotation
We offer to read an annotation, description, summary or preface (depends on what the author of the book "Dynamic SQL: Applications, Performance, and Security" wrote himself). If you haven't found the necessary information about the book — write in the comments, we will try to find it.
At a Glance; Contents; About the Author; About the Technical Reviewer; Acknowledgments; Introduction; Chapter 1: What Is Dynamic SQL?; Understanding Dynamic SQL; A Simple Example; The EXEC Statement; Data Types to Use; Dynamic Execution Process; Dynamic SQL in Action; Advantages of Dynamic SQL; Optional or Customized Search Criteria; Customizable Everything; Optimize SQL Performance; Generate Large Amounts of TSQL or Text, Fast!; Execute SQL Statements on Other Servers or Databases; Do the Impossible!; Dynamic SQL Considerations; Apostrophes Can Break Strings; NULL Can Break Strings.;This book is an introduction and deep-dive into the many uses of dynamic SQL in Microsoft SQL Server. Dynamic SQL is key to large-scale searching based upon user-entered criteria. Its also useful in generating value-lists, in dynamic pivoting of data for business intelligence reporting, and for customizing database objects and querying their structure. Executing dynamic SQL is at the heart of applications such as business intelligence dashboards that need to be fluid and respond instantly to changing user needs as those users explore their data and view the results. Yet dynamic SQL is feared by many due to concerns over SQL injection attacks. Reading Dynamic SQL: Applications, Performance, and Security is your opportunity to learn and master an often misunderstood feature, including security and SQL injection. All aspects of security relevant to dynamic SQL are discussed in this book. You will learn many ways to save time and develop code more efficiently, and you will practice directly with security scenarios that threaten companies around the world every day. Dynamic SQL: Applications, Performance, and Security helps you bring the productivity and user-satisfaction of flexible and responsive applications to your organization safely and securely. Your organizations increased ability to respond to rapidly changing business scenarios will build competitive advantage in an increasingly crowded and competitive global marketplace. Discusses many applications of dynamic SQL, both simple and complex. Explains each example with demos that can be run at home and on your laptop. Helps you to identify when dynamic SQL can offer superior performance. Pays attention to security and best practices to ensure safety of your data.
Pollack: author's other books
Who wrote Dynamic SQL: Applications, Performance, and Security? Find out the surname, the name of the author of the book and a list of all author's works by series.
Pollack
Pollack
William Liu
Levenson
Eric Franklin
Don Henderson
Kevin Tatroe
Oleg Sych
Dynamic SQL: Applications, Performance, and Security — read online for free the complete book (whole text) full work
Below is the text of the book, divided by pages. System saving the place of the last page read, allows you to conveniently read the book "Dynamic SQL: Applications, Performance, and Security" online for free, without having to search again every time where you left off. Put a bookmark, and you can go to the page where you finished reading at any time.
Light
Font size:
↓
↑
Reset
Interval:
↓
↑
Bookmark:
Make
Ed Pollack 2016
Edward Pollack Dynamic SQL 10.1007/978-1-4842-1811-2_11. What Is Dynamic SQL?
Edward Pollack 1
(1)
Albany, New York, USA
Electronic supplementary material
The online version of this chapter (doi: 10.1007/978-1-4842-1811-2_1 ) contains supplementary material, which is available to authorized users.
TSQL 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 youll face are unknowns, or situations where you dont know exactly what kind of data you will be working with until runtime. Dynamic SQL is one of the best tools for solving problems in the face of unknowns.
Understanding Dynamic SQL
Dynamic SQL is quite simple to understand, and once youre acquainted with it, the number of applications you can use it for can become staggering. Dynamic SQL seeks to solve scenarios where you want to operate on one or many objects, but do not know all of the pertinent details as you write the code. Parameters can be passed into the code in order to persist sets of important values, but what do you do when the structure of the TSQL is defined by these values?
A Simple Example
Starting with a very simple select statement , we will build a starting point for understanding Dynamic SQL:
SELECT TOP 10 * FROM Person.Person;
This statement returns all columns of data for 10 rows in the table Person.Person . What if you wanted to select data from a table, but did not know the name of the table until runtime? How would you substitute the variable table name into the TSQL? Before answering that question, lets introduce Dynamic SQL by simply rewriting the above query so that you are executing it as a character string, rather than as standard TSQL:
DECLARE @sql_command NVARCHAR(MAX);
SELECT @sql_command = 'SELECT TOP 10 * FROM Person.Person';
EXEC (@sql_command);
This example defines a character string called @sql_command that will be used to hold the Dynamic SQL. What is the Dynamic SQL? Its the string that you are building and then later executing. In this case, it is the same SELECT statement from above, with no alterations. After you set the @sql_command , it is then executed, providing the same results as above.
The EXEC Statement
EXEC is used to execute @sql_command . EXECUTE may also be used. Other ways to execute Dynamic SQL will be presented later in this book, in response to the need for further flexibility or security. Remember to always put parentheses around the @sql_command string. Heres an example that omits the parentheses:
DECLARE @sql_command NVARCHAR(MAX);
SELECT @sql_command = 'SELECT TOP 10 * FROM Person.Person';
EXEC @sql_command;
Failure to do so will result in a somewhat odd error:
Msg 2812, Level 16, State 62, Line 11
Could not find stored procedure 'SELECT TOP 10 * FROM Person.Person'.
The Dynamic SQL command string is treated by SQL Server as a stored procedure when there are no parentheses. Leave them out and youll be unable to run your SQL string, receiving an error similar to the one above.
Data Types to Use
Note that NVARCHAR(MAX) is used as the data type for the command string. While you could use VARCHAR , you would potentially be losing data if any extended Unicode characters were in any of the objects you work with. The size could also be shortened, but if your command string becomes larger than that size, it will be truncated and your Dynamic SQL will become the source of confusing error messages or logical errors.
For consistency and reliability, use NVARCHAR(MAX) as the data type for your Dynamic SQL command strings.
Dynamic Execution Process
In order to understand how Dynamic SQL works and the various ways in which it can be applied to the many problems youll encounter, it is important to consider how Dynamic SQL is built. In addition, becoming familiar with the execution process used by SQL Server in order to parse and run the string of TSQL will make using Dynamic SQL a much easier process.
All Dynamic SQL follows three basic steps:
Create a string variable that will store the Dynamic SQL. Any variable name may be used.
Build a command string and store it in this variable.
Execute the command string.
The benefit of storing the TSQL command as a string is that you are free to use any string manipulation commands on it, building it in one or many steps. Now to tackle your original problem: how to select data from a table that is not defined until runtime. To accomplish this, you remove Person.Person from the string and replace it with a variable that you defined above:
DECLARE @sql_command NVARCHAR(MAX);
DECLARE @table_name NVARCHAR(100);
SELECT @table_name = 'Person.Person';
SELECT @sql_command = 'SELECT TOP 10 * FROM ' + @table_name;
EXEC (@sql_command);
The variable @table_name stores the name of the table you wish to query. Commonly, this would be passed in as a parameter, either from other stored procedures or an application that calls this directly. By building it into @sql_command , you gain the flexibility of querying any table you wish, without hard-coding it ahead of time. While this is a trivial example (how often will you want to select data in this fashion?), it provides the basis for thousands of applications, each of which can save immense time, resources, and complexity. Before diving further into the details of Dynamic SQL and its many uses, lets look at a more practical (and more complex) example of Dynamic SQL in action.
Dynamic SQL in Action
A common maintenance need is to run TSQL against many databases on a server. This maintenance could involve backing up databases, rebuilding indexes, reporting on critical data elements, or many other possibilities. If the database list never changes and no databases are ever renamed, you could hard-code names into each procedure and not worry about changing them in the future. This would work until the one day when you finally experience those inevitable changesmoving or renaming databaseswhich ultimately will break all of those valuable maintenance procedures. Its critical that your maintenance, monitoring, and reporting jobs operate with the highest level of reliability possible.
Listing shows a common example of a statement that could be used to run a backup against a single database, storing it on a local drive.
BACKUP DATABASE AdventureWorks2014
TO DISK='E:\SQLBackups\AdventureWorks2014.bak'
WITH COMPRESSION;
Listing 1-1.
Simple BACKUP Statement
This TSQL will back up the AdventureWorks2014 database to the SQLBackups folder on the E drive, using compression. If you want to perform a custom database backup on a subset of databases that all begin with the text AdventureWorks , for example, you would need to build TSQL that could adapt to collect a list of all databases with that name, and then perform backups on each of them separately. The following TSQL shows one way that this could be accomplished, using Dynamic SQL.
DECLARE @database_list TABLE
(database_name SYSNAME);
INSERT INTO @database_list
(database_name)
SELECT
name
Light
Font size:
↓
↑
Reset
Interval:
↓
↑
Bookmark:
Make
Similar books «Dynamic SQL: Applications, Performance, and Security»
Look at similar books to Dynamic SQL: Applications, Performance, and Security. We have selected literature similar in name and meaning in the hope of providing readers with more options to find new, interesting, not yet read works.
William Liu
Levenson
Eric Franklin
Don Henderson
Kevin Tatroe
Oleg Sych
Reviews about «Dynamic SQL: Applications, Performance, and Security»
Discussion, reviews of the book Dynamic SQL: Applications, Performance, and Security and just readers' own opinions. Leave your comments, write what you think about the work, its meaning or the main characters. Specify what exactly you liked and what you didn't like, and why you think so.