An Abridged Introduction to Dynamic SQL
5.0 (2 ratings)
Instead of using a simple lifetime average, Udemy calculates a course's star rating by considering a number of different factors such as the number of ratings, the age of ratings, and the likelihood of fraudulent ratings.
40 students enrolled
Wishlisted Wishlist

Please confirm that you want to add An Abridged Introduction to Dynamic SQL to your Wishlist.

Add to Wishlist

An Abridged Introduction to Dynamic SQL

Learn Real World Techniques You can Apply Immediately
5.0 (2 ratings)
Instead of using a simple lifetime average, Udemy calculates a course's star rating by considering a number of different factors such as the number of ratings, the age of ratings, and the likelihood of fraudulent ratings.
40 students enrolled
Created by Mike West
Last updated 1/2017
English
Curiosity Sale
Current price: $10 Original price: $20 Discount: 50% off
30-Day Money-Back Guarantee
Includes:
  • 1 hour on-demand video
  • 5 Articles
  • 1 Supplemental Resource
  • Full lifetime access
  • Access on mobile and TV
  • Certificate of Completion
What Will I Learn?
  • By the end of this course you'll be able to take what you've learned and immediately apply it to the real world.
View Curriculum
Requirements
  • Learners will need a strong foundation in Transact-SQL
  • You'll need an Instance of SQL Server
  • You'll need a copy of the adventureworks training database
Description

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

Who is the target audience?
  • If you're a mid-level to senior level SQL Server Resource that wants to learn to harness the power of Dynamic SQL then this course is for you.
Students Who Viewed This Course Also Viewed
Curriculum For This Course
26 Lectures
01:05:07
+
Dynamic SQL Overview
8 Lectures 23:00

Let's learn what this course is about. 

Preview 01:30

The big three. 

What are they? 

Let's find out. 

Preview 04:09

We can use this tip inside larger code segments. 

Preview 02:09

Most people don't document their code but with dynamic SQL it's probably a really solid practice. 

Preview 04:42

In this lecture let's add some more complexity.

Backing Up All AdventureWorks Databases
06:17

Let's add a date and time stamp to our adventureworks backup script. 

Add a Date stamp to our Adventureworks Database
03:39

Course Downloads
00:04

Summary
00:30

Quiz
5 questions
+
Digging Into the Minutia
6 Lectures 11:30

In this lesson let's learn about using the correct data type length within our strings. 

Strings and Truncation
01:46

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. 

sp_executesql
01:41

Let's talk about strings and NULL values. 

Concatentation Vs Building the Strings
03:20

Since we are building strings let's go over some string function that will help us build dynamic strings. 

String Functions
02:30

Let's learn how the apostrophe can break our dynamic code. 

The Apostrophe
00:57

Summary
01:16

Quiz
10 questions
+
Protect Against SQL Injection
6 Lectures 12:18

Let's define SQL Injection and see an injection attack example. 

What is SQL Injection
04:08

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. 

Probing Schema Objects Using SQL Injection
01:47

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. 

Password Collection and Removal Hack
01:57

Let's clean up our inputs and how prevent sql injection. 

Cleaning Up Our Inputs
02:18

Let's learn how to use QUOTENAME to prevent sql injection.

QUOTENAME or REPLACE
01:12

Summary
00:56

Quiz
7 questions
+
Dynamic Use Cases
6 Lectures 18:22

Let's learn how to implement dynamic sql in the WHERE clause. 

Dynamic Where Clause
07:38

Let's learn how to create an automated CHECKDB job. 

Dynamic CHECKDB
02:18

Let's look at how I integrated a very simple dynamic sql statement in an application I built. 

Dynamic Unused Index Removal
03:10

In this lecture let's learn how to use temp tables within our dynamic sql statements. 

Dynamic Temp Tables
02:07

In this lecture let's learn how to use Like, IN and ORDER BY inside some dynamic code. 

Dynamic Like, IN and ORDER BY
02:27

Summary
00:42
About the Instructor
Mike West
4.1 Average rating
2,625 Reviews
43,320 Students
40 Courses
SQL Server and Machine Learning Evangelist

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

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

Personal

Born in Ohio, raised and educated in Pennsylvania, I currently reside in Atlanta with my wife and two children.