Scripting Tables With SSMS

Tim Cost shows a few ways to script tables using SQL Server Management Studio:

Still … there is a trick here, and I don’t see a lot of people using it.  Maybe it’s just me, maybe I’m lazier than the average dev, but I often find myself using the Script Table As menu and choosing SELECT To and Clipboard.  This creates a nice select statement with all my fields wrapped in hard brackets.  I can then copy this into an INSERT query I might be working on to save myself some typing.  I can quickly copy the field list from the Query ‘Script Table As’ gives me and use it in the top of my INSERT query, then I can copy the entire SELECT query into the bottom of my INSERT query and Bob’s yer Uncle, I’ve got a simple INSERT query ready to go.  Note:  This is most useful when I’m trying to create a new table based on an existing table with only minor changes to field names.  I use this frequently when I’m establishing a reporting database based on staging tables.

That’s three ways to do it in Management Studio; the next step in the process is using SMO to script using a .NET language (C#, F#, Powershell).

Dynamic Data Masking For Lower Environments

Joey D’Antoni shows how to use Dynamic Data Masking to help prevent sensitive production data from getting to lower environments:

Well at PASS Summit, both in our booth and during my presentation on security in Azure DB, another idea came up—exporting data from production to development, while not releasing any sensitive data. This is a very common scenario—many DBAs have to export sensitive data from prod to dev, and frequently it is done in an insecure fashion.

Doing this requires a little bit of trickery, as dynamic data masking does not work for administrative users. So you will need a second user.

Read on for details.

Adaptive Query Processing

Kendra Little speculates a bit:

Perhaps speculation feels like the right topic today because Microsoft folks talked a lot about the importance of prediction in the keynotes at the PASS Summit last week.

SQL Server 2016 features R Services. This brings the ability to learn patterns and make predictions into the database engine.

Using this new feature came up a lot in the keynote. And not just for performing predictions for a user application, either: there were quite a few references about using SQL Server’s predictive powers to make SQL Server itselfsmarter.

So what might that mean?

It may be speculation, but it’s quite interesting.

A Better Get-SQLErrorLog

Drew Furgiuele steps up with an improved version of Get-SQLErrorLog:

It doesn’t have to be so bad, though, because we can make it better. In my mind, my perfect error log cmdlet should:

  1. Be usable when a SQL Server instance is down, and
  2. Be relatively quick, and
  3. Parse error messages by number, severity, and state, and
  4. Incorporate date/time range filtering, and
  5. Return an object

What’s that, you say? You want to solve each of these and write our own, better cmdlet? I got you, fam. At the bottom of this blog post you’ll find my code, and you can skip ahead if you don’t care about the “hows” or “whys” of what you’ll be looking at.

Very nice work Drew did in putting this together.

Alerting On Drastic Changes

Rob Collie has a post on using Power BI to spot outliers:

The basic idea here is “alert me if something has changed dramatically.”  If there’s a corner of my business that has spiked or crashed in a big way, I want to know.  If something has dramatically improved in a particular region, I may want to dive into that and see if it’s something we can replicate elsewhere.  And if something has fallen off a cliff, well, I need to know that for obvious reasons too.  And both kinds of dramatic change, positive and negative, can easily be obscured by overall aggregate values (so in some sense this is a similar theme to “Sara Problem?”)

So the first inclination is to evaluate distance from average performance.  And maybe that would be fine with high-volume situations, but when we’re subdividing our business into hundreds or perhaps thousands of micro-segments, we end up looking at smaller and smaller sample sizes, and “normal” variation can be legitimately more random than we expect.

This looks really cool.  If you read the comments, Rob notes that performance does break down at some point.  If you start hitting that point, I’d think about shifting this to R.

Career-Limiting Moves

Randolph West has a series of career-limiting moves, which sadly I had missed until now.  I’ll make up for that by linking the whole series.

First, dropping a table:

For whatever reason, we ran the script in the Oracle SQL*Plus client, which Wikipedia generously describes as “the most basic” database client.

Cut to the part where we run the script. The DROP TABLE command was run successfully and committed, but the previous step where the data was moved had failed.

The entire table was gone.

Second, saying “no” at the wrong time:

My job was to provide technical support to a senior staff member, and I said no because I was busy on something that was, for all intents and purposes, not as important.

This was of course escalated very quickly to the managing director, who in turn shouted at my boss, who in turn shouted at me. If I recall correctly, my boss eventually helped his colleague with her important problem and only reamed me out after the fact.

Third, playing the blame game:

She explained to me that whether or not that was the case, the language was totally inappropriate and calling a vendor on the weekend for something that did not constitute an emergency was unprofessional. In any number of scenarios, I could have been fired for my behaviour.

Chastened, I took away several important lessons: it doesn’t matter whose fault something is. The job had to be done, and I was around to do it. Furthermore, it is important never to be caught bad-mouthing someone on the record, no matter how good a relationship you have with a vendor. It will always come back to bite you.

 

His current in the series is Reply All, in which he’s looking for your stories.

“Talk Me Through This Query”

Brent Ozar is working on a series of SQL interview questions.  Today’s is “talk me through this query:”

Last month’s post “For Technical Interviews, Don’t Ask Questions, Show Screenshots” was a surprise hit, and lots of folks asked for more details about the types of screenshots I’d show. Over the next few weeks, I’ll share a few more.

Normally I’d show this query as a screenshot, but for easier copy/pasting into comments, I’m showing it as code here.

I’d say to the job candidate, “You’ve been asked to take a quick look at this code as part of a deployment. Explain what the business purpose of the code is, and tell me if there’s anything that concerns you.”

I like where Brent is going with this series and plan to incorporate some of these into my in-person interviews.

SQL Server 2016 Launch Event

Scott Guthrie and Satya Nadella are hosting a SQL Server 2016 live event starting at 10 AM Eastern (via Scott Guthrie).

Yeah, it’s a marketing event, but there might be something interesting and exciting to come out of it.

Strings Are Hard

Kenneth Fisher on varchar versus nvarchar:

In any study of Data Types in SQL Server you are going to have to look at the various string data types. One important component is the difference between nChar vs Char and nVarChar vs VarChar. Most people reading this are probably thinking “Well that’s ridiculously easy.” If you are one of them then I want you to read these two facts about these data types.

Char and VarCharnChar and nVarChar
StoresASCIIUNICODE
SizeAlways one byte per character.Always two bytes per character.

One of these is incorrect. Do you know which one?

The correct answer is “both are wrong.”  Then you get into debates about what a “character” is, how certain languages (like Hebrew and Arabic) have layers of modifiers which modify semantic context, etc. etc.  Strings are probably even harder than dates.

Network Load Testing

Tim Radney uses iperf to perform network load testing:

Poor network performance can be a silent killer for application performance and my personal experience has shown this to be the case on many occasions. Often an application would start having performance issues and the application engineer would say that the application server looks good and starts to point their finger at the database. I would get a call to look at the database server and all indications showed that the database server was in good health (and this is where monitoring for key performance indicators and having a baseline helps!). Since the application and database teams were saying everything was good, we would ask the network team to check things out. The network team would look at a few things and give the all clear on their side as well. Each team troubleshooting and reviewing their respective systems took time, meanwhile the application performance was still suffering. The issue would then get escalated until all the teams would be asked to join a conference bridge to troubleshoot together. Eventually someone would start a deeper network test and determine that we either had a port saturation, routing, or some other complex networking issue. A few clicks or changing something on their end would eventually resolve the application slowness.

iperf is a nice tool for checking to see if your network throughput looks reasonable.

Categories

April 2017
MTWTFSS
« Mar  
 12
3456789
10111213141516
17181920212223
24252627282930