TSQL – MidnightDBA

1NF – 1st Normal Form

Here I’m talking about 1NF, not only in the context of a table, but also in the context of a DB or even an enterprise.

T-SQL: Switching data with a Single Partition

This is the fastest way to move data to a new table. Of course, you have to move all the data, but it’s quite useful. Tables are partitioned in SQL by default so this is a great technique.

Microsoft SQL Server – T-SQL: Switching data with a Single Partition

This is the fastest way to move data to a Continue reading “Microsoft SQL Server – T-SQL: Switching data with a Single Partition”

Most DBAs don’t think of it, but xp_cmdshell has a
default proxy account. In fact, this proxy is responsible for its permissions, and actually follows Microsoft’s current external script security strategy. So xp_cmdshell isn’t unique and it doesn’t do anything special security-wise.
Here you’ll see how to test which credentials xp_cmdshell is using. It’s actually pretty easy.

Master Stored Procedure Workshop: Part 2

This is part 2 of the previous session from SQLSaturday Continue reading “Master Stored Procedure Workshop: Part 2”

Master Stored Procedure Workshop: Part 1

This is from SQLSaturday Richmond, VA on 3/30/2019.
I’m teaching stored Continue reading “Master Stored Procedure Workshop: Part 1”

REGEX for DBAs

This is the version of my session that works with Continue reading “REGEX for DBAs”

What is a Stored Procedure

Here Jen explains what a stored procedure is, and how to create a basic one with no parameters. She also compares it with other Continue reading “What is a Stored Procedure”

Insert Into vs. Select Into

Knowing how and when to use Insert Into or Select Into can quite often make or break your load process. Here I show you Continue reading “Insert Into vs. Select Into”

Querying Dev Metadata

Sometimes you need to get info on objects you’ve created. Here you can see how you can query for this info, and even learn Continue reading “Querying Dev Metadata”

Best Habit for Coders

There’s a step you can make in your code to improve it many times over. It’s an easy step to take, but it can Continue reading “Best Habit for Coders”

UNION and UNION ALL

Here I talk about UNION and UNION ALL, and the different rules about using ORDER BY, GROUP BY, SELECT INTO, and INSERT INTO with a Continue reading “UNION and UNION ALL”

OR is bad, IF is good

Here I tell you about a common mistake – using OR instead of IF – and show you that IF is better.

Designing Efficient Functions

Here I get on my cert soapbox again. I was going through a practice cert exam and came across an answer to a question Continue reading “Designing Efficient Functions”

T-SQL Code Sins

SPEAKER: Jen McCown. Do you know how to set yourself for good coding practices? Well Jen does. Come see the advice she Continue reading “T-SQL Code Sins”

Relational Division

Here we discuss relational division, the counterpart to relational multiplication (cross joins). We’ll hit a couple of examples, using subqueries to implement two different Continue reading “Relational Division”

Relational Multiplication

Here’s a quick talk on relational multiplication that gets us to take a closer look at what our joins are really doing.

Using TFS in SSMS

Here I show you how to set SSMS up to work with Team Foundation Server

T-SQL: Find Last Day of Month

Follow Jen as she steps you through 3 progressively elegant solutions for finding the last day of the month.

Grouping Sets

Here Jen and I go over grouping sets the only way we can. I hope you guys enjoy us late at night.

T-SQL: Over() and Partition By

Show how to use OVER and PARTITION BY to get groups of data with aggregation.

Insert Non-duplicate Rows

Here we work our way through a coding scenario—inserting rows—that should illustrate the process of solving a problem from the simplest case to your intended Continue reading “Insert Non-duplicate Rows”

Source Control in SSMS

Here I show you how to setup source control in SSMS and who should and shouldn’t use it.

Dirty Tricks: Text Manipulation

Dirty tricks are the little tricks we use that aren’t official, may not be “proper”, but are insanely useful. Today we look at script building Continue reading “Dirty Tricks: Text Manipulation”

Dirty Tricks 2: Get List From Command Line

Here’s one of my dirty tricks: getting a list of folders or files from a command prompt, and formatting it for use in code using Continue reading “Dirty Tricks 2: Get List From Command Line”

TSQL: Grouping Basics

Here’s a ground level intro to grouping, with a lovely 80s arcade theme. This is a reshoot of the original Grouping Basics video (now Continue reading “TSQL: Grouping Basics”

T-SQL: CASE Statement

Here’s an intro to case statements, in the form of a conversation with Sean. I cover simple case and searched case, and the special Continue reading “T-SQL: CASE Statement”

Sending HTML SQL Emails

I’m actually excited to be able to bring you this video because this is a topic that comes up quite often in DBA circles. Continue reading “Sending HTML SQL Emails”

Write Good Dynamic SQL

Here I show you a couple methods for getting around really hard to read dynamic SQL.

Simple Backup Procedures

This is a tutorial not only on how to turn your backup commands into SPs, but also the basics of stored procedures are discussed.

Select Into with Identity()

This video shows you how to use the Identity() function to create an identity column on the fly during a select into statement. You don’t Continue reading “Select Into with Identity()”

Category: TSQL