Author: Brent Ozar

This week at the 24 Hours of PASS: Summit Preview Edition, I was honored to give a one-hour session about hacking your career. Here’s the abstract:

This is not yet another career session that tells you to be friendly and network. Forget that – this is about using your IT skills to reinvent the way you get paid. I’ll explain how I went from DBA to MVP to MCM to business founder.

I’ll show you simple techniques to build a blog, a brand, and a business without that pesky personal networking stuff. I’ll explain why you have to give everything away for free, and why you cannot rely on the old methods to make money anymore.

It will not be easy – and that is why this session is level 500. This session is about radical methods that achieve radical results.

Here’s the slides:

Check out the other 24HOP sessions which preview the awesome kinds of stuff you’ll get by attending the PASS Summit next month in Seattle. I’ll be there giving a much more in-depth half-day version of this workshop, plus a new version of my How to Think Like the Engine session. See you there!

If you think that’s big, you should see the size of the drink

I happened to be in Seattle this week after finishing up an Alaska cruise, and I had the chance to sit down with some of the PMs responsible for upcoming SQL Server features. It turns out they’re already deep in development with some truly revolutionary capabilities.

Now, I probably shouldn’t be sharing these with you, dear reader, but I bet you’re going to be as excited as I am, so here goes: SQL Server 2018’s upcoming feature list:

• Edible Result Sets
• Self destructing transactions
• New SSMS color schemes by Karl Lagerfeld
• Deep South Parent/Child Referential Integrity (I didn’t wanna ask for details there)
• Eventual consistency
• Scented error messages
• Duraflame Transaction Log
• Access storage engine
• MAIM command – like KILL, but lets you keep up to half of the result sets that have already been delivered
• Renaming SQL Server Agent to Cortana
• Twitch integration for video replay of cool transactions
• Azure Power Enterprise Server – I’m not quite sure what this is because the description changed three times while we were talking, and the name changed four
• Opinion-based triggers
• TSA-grade security

Erik says: I’m kinda excited about the new upcoming family valued functions, emoji support, and Haskell functions. The best, though, has to be IntentiSense, which replaces IntelliSense, and detects what you meant to write.

Background: a company came to us with a request: they had a lot of smart people who wanted to learn the basics of T-SQL in a day. Could we build an intro-to-T-SQL course that didn’t suck? Well, sure – as long as we could give it away free on the site. So here it is:

You work for a company that’s got a lot of data in Microsoft SQL Server, and you want to work with it – but you’re not sure how to start. You’re also afraid that your queries might be “bad,” and you don’t want to cause problems on someone else’s server – even when you need to work with a lot of data.

In this one-day self-paced course, we’re going to cover:

• How to write a SELECT query to get data out of one table
• How to filter the data to find just what we want
• How to sort data
• How to join multiple tables together
• How to tell if our query will be slow before we run it
• What books, videos, and web sites to use for learning more

To explain it all, we’re going to use the StackOverflow.com site, database, and their open-source Data Explorer (it’s like a web-based SSMS). You’ll learn the basics of the StackOverflow database, look at a page on StackOverflow.com, and then write a query to deliver similar data.

Head on over and get started, and let us know what you think.

At first glance, if you set up log shipping the way most folks do, it looks like you can’t run CHECKDB on a subscriber:

Database cannot be opened. It is in the middle of a restore. Please close the bathroom door.

On SQL Server Enterprise Edition, you can try working around it by creating a database snapshot – but that won’t work either:

The database must be online to have a database snapshot. Please stop taking pictures in the bathroom.

Here’s the trick: your database needs to be in standby mode instead of norecovery. When you run restores with the standby option, you’re able to query the database in between restores.

You don’t even have to restore a transaction log in order to switch the database over to standby mode – you just have to run the restore with standby command, like this:

RESTORE DATABASE DBAmetrics WITH STANDBY = 'E:\MSSQL\DATA\Standby.ldf';

Operators are standing by

Now, you can run CHECKDB against that database – or run any ol’ select queries that you want:

DBCC CHECKDB at your service

Now you can feel confident that your log shipped secondary is ready to go. Remember, it’s only applying log file changes from the primary – just like Always On Availability Groups, just because CHECKDB succeeds on the primary doesn’t mean the secondary is free from corruption.

Let’s say, just theoretically, you wanted to demo a query that takes SQL Server a long time to compile.

And in order to make SQL Server’s job tougher, you wanted to create the maximum number of indexes possible on a table.

You might do something like this:

SELECT TOP 999 'CREATE INDEX [IX_' + c1.COLUMN_NAME + '_' + c2.COLUMN_NAME + '_' + c3.COLUMN_NAME + ']'
    + ' ON [' + c1.TABLE_SCHEMA + '].[' + c1.TABLE_NAME + '] ' 
    + '([' + c1.COLUMN_NAME + '], [' + c2.COLUMN_NAME + '], [' + c3.COLUMN_NAME + ']);' 
FROM INFORMATION_SCHEMA.COLUMNS c1
INNER JOIN INFORMATION_SCHEMA.COLUMNS c2 ON c1.TABLE_SCHEMA = c2.TABLE_SCHEMA AND c1.TABLE_NAME = c2.TABLE_NAME 
    AND c1.COLUMN_NAME <> c2.COLUMN_NAME 
    AND (c2.CHARACTER_MAXIMUM_LENGTH <> -1 OR c2.CHARACTER_MAXIMUM_LENGTH IS NULL)
INNER JOIN INFORMATION_SCHEMA.COLUMNS c3 ON c1.TABLE_SCHEMA = c3.TABLE_SCHEMA AND c1.TABLE_NAME = c3.TABLE_NAME 
    AND c3.COLUMN_NAME NOT IN (c1.COLUMN_NAME, c2.COLUMN_NAME) 
    AND (c3.CHARACTER_MAXIMUM_LENGTH <> -1 OR c3.CHARACTER_MAXIMUM_LENGTH IS NULL)
WHERE c1.TABLE_SCHEMA = 'dbo' AND c1.TABLE_NAME = 'Users'
AND (c1.CHARACTER_MAXIMUM_LENGTH <> -1 OR c1.CHARACTER_MAXIMUM_LENGTH IS NULL)
ORDER BY c3.COLUMN_NAME, c2.COLUMN_NAME

Which would generate output like this:

Look, you’re the one who comes here to “learn stuff”

The CHARACTER_MAXIMUM_LENGTH <> -1 OR IS NULL stuff is because I don’t want to try to index NVARCHAR(MAX) fields, but I do want to index NVARCHAR(50) fields.

The ORDER BY is because I wanted to get a wide variety of leading fields. If I just ordered by c1.COLUMN_NAME, I wouldn’t get all of the first fields covered in the first 999 indexes. (And I’m not even using a large table.)

I could have dumped all these into strings and executed them, but depending on the speed of the system, the demos I’m trying to do, the fields in the indexes, etc, I may not want them all created right away. Sometimes I experiment with just 25-50 indexes before moving on to, uh, 999.

As part of an experiment, I needed to build a really long query. (Don’t ask.)

From another recent experiment, I know that SQL Server won’t let a query return more than 65,535 columns. I set about writing a one-line query that would return 65,535. I’m a big fan of writing the simplest reproduction scripts possible – I don’t want them to rely on tables if they don’t have to – so we’ll start a CTE like this:

My favorite Thanksgiving side dish

That’s a simple CTE which gives me a fake table called Stuffing, with one field named Stuff.

I want to select that Stuffing field 65,535 times, but ideally, I’d like to return that field with a different name each time. (I might use this to artificially insert 65,535 columns later.) I’ll need a trusty numbers table (like Method #7 from this StackOverflow answer), which will give me 65,535 rows that I can use to build dynamic SQL:

Including 8, 6, 7, 5, 3, 0, and 9

Now we’ll use those to build dynamic SQL:

Using SQL to Build SQL

And I can just copy/paste that stuff into my SELECT statement and run it:

Hey, Mister DJ, I thought you said we had a deal

Alright, so we’ve learned that I can’t return more than 65,535 columns, AND I can only use 4,096 elements in my SELECT. I can think of several workarounds there – 65,535 / 4096 = about 16, which means I could create a few tables or CTEs and do SELECT *’s from them, thereby returning all 65,535 columns with less than 4,096 things in my SELECT. But for now, we’ll just start with 4,096 things in my SELECT:

Lots of Stuffing

Presto! We’ve got stuffing. As long as we’re here, it’s interesting to see how SQL Server clips off queries in execution plans. If we hover the mouse over the query in a plan, we can see the query – or at least, the beginning of it:

Hovercraft

Right-click on the plan and click Show Execution Plan XML, and you can see exactly where SSMS clips it:

Cuts out at n156 (of 4096)

This is why I say you should never comment your code. You want to make sure you can see your entire query in its glorious glory. If you absolutely have to comment your code, avoid vowels at all costs, and type like a 14 year old texter.