"First impressions are "Finally, a practicing educator" Course delivery is smooth and spot on. Right before you lose hope a gem like this pops up - thanks." - Don Councill
Welcome to An Abridged Introduction to Dynamic SQL.
Dynamic SQL is a programming technique that enables you to build SQL statements dynamically at runtime. You can create more general purpose, flexible applications by using dynamic SQL because the full text of a SQL statement may be unknown at compilation.
Dynamic SQL is often underused, misused, or overused.
If you are completely unfamiliar with Dynamic SQL, this will be an opportunity to learn, practice, and immediately apply it.
If you already have experience in writing and using Dynamic SQL, this will be a chance to learn new applications while getting a refresher on those you have worked with in the past.
Anyone with a healthy interest in database administration or development can benefit from the topics covered in this course.
Dynamic SQL seeks to solve scenarios where you want to operate on one or many objects, but do not know all the details but don’t know all the details as your write the code.
Dynamic SQL gives us an opportunity to customize statements to match performance needs.
Removing excess objects, removing joins and subqueries, and reducing the size of a SQL statement can result in faster executions times and reduce resource consumption.
The course will be exercise laden. Because Dynamic SQL is unfamiliar to many we will start of basic and transition into more detailed examples.
There are many reasons why you would want to incorporate Dynamic SQL into your everyday arsenal of SQL Server tools and in the course we will demonstrate several real world example.
I’d also like to add that an automated software engine I’ve authored called sqlblackbox relies heavily on Dynamic SQl and in the course I’ll show you a few simple lines I use in the application.
Thank you for your interest in An Abridged Introduction to Dynamic SQL and we will see you in the course.
The big three.
What are they?
Let's find out.
Most people don't document their code but with dynamic SQL it's probably a really solid practice.
In this lecture let's add some more complexity.
Let's add a date and time stamp to our adventureworks backup script.
In this lesson let's learn about using the correct data type length within our strings.
sp_executesql executes a Transact-SQL statement or batch that can be reused many times, or one that has been built dynamically.
Let's learn why this is our preferred choice when building dynamic sql strings.
Let's talk about strings and NULL values.
Since we are building strings let's go over some string function that will help us build dynamic strings.
Let's learn how the apostrophe can break our dynamic code.
Let's define SQL Injection and see an injection attack example.
When querying against dynamically defined database schema, such as tables, views, columns, or stored procedures, you can’t parameterize the database objects.
Let's learn what we can do to prevent an injection.
Let's collect some passwords... then delete all of them.
This password hack is simple and would have most companies down for quite some time.
Let's clean up our inputs and how prevent sql injection.
Let's learn how to use QUOTENAME to prevent sql injection.
Let's learn how to implement dynamic sql in the WHERE clause.
Let's learn how to create an automated CHECKDB job.
Let's look at how I integrated a very simple dynamic sql statement in an application I built.
In this lecture let's learn how to use temp tables within our dynamic sql statements.
In this lecture let's learn how to use Like, IN and ORDER BY inside some dynamic code.
I've been a production SQL Server DBA most of my career.
I've worked with databases for over two decades. I've worked for or consulted with over 50 different companies as a full time employee or consultant. Fortune 500 as well as several small to mid-size companies. Some include: Georgia Pacific, SunTrust, Reed Construction Data, Building Systems Design, NetCertainty, The Home Shopping Network, SwingVote, Atlanta Gas and Light and Northrup Grumman.
Experience, education and passion
I learn something almost every day. I work with insanely smart people. I'm a voracious learner of all things SQL Server and I'm passionate about sharing what I've learned. My area of concentration is performance tuning. SQL Server is like an exotic sports car, it will run just fine in anyone's hands but put it in the hands of skilled tuner and it will perform like a race car.
Certifications are like college degrees, they are a great starting points to begin learning. I'm a Microsoft Certified Database Administrator (MCDBA), Microsoft Certified System Engineer (MCSE) and Microsoft Certified Trainer (MCT).
Born in Ohio, raised and educated in Pennsylvania, I currently reside in Atlanta with my wife and two children.