Press "Enter" to skip to content

Author: Kevin Feasel

Inconsistencies With SQL_VARIANT

Published 2017-03-17 by Kevin Feasel

Erik Darling warns against using SQL_VARIANT data types:

I half-stumbled on the weirdness around SQL_VARIANT a while back while writing another post about implicit conversion. What I didn’t get into at the time is that it can give you incorrect results.

When I see people using SQL_VARIANT, it’s often in dynamic SQL, when they don’t know what someone will pass in, or what they’ll compare it to. One issue with that is you’ll have to enclose most things in single quotes, in case a string or date is passed in. Ever try to hand SQL those without quotes? Bad news bears. You don’t get very far.

Read on for the demo.  I have never used SQL_VARIANT in any project.  I’ve done a lot of crazy things with SQL Server (some of them intentionally) but never this.

Comments closed

Scripting Tables With SSMS

Published 2017-03-17 by Kevin Feasel

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

Comments closed

Invoke-SqlCmd

Published 2017-03-16 by Kevin Feasel

Mike Fal defends Invoke-SqlCmd’s honor:

Why do people gripe so much about Invoke-SqlCmd then? Well, to understand this is to understand the history of SQLPS. For a long while, the SQL Server module for PowerShell was klunky and buggy. There were a lot of challenges with loading it and using it, such that many scripters decided to throw it out and write their own functions. In many cases, PowerShell folks would skip Invoke-SqlCmd not because it was bad, but because it came packaged with the rest of SQLPS and they wanted to avoid the entire module.

Now that the SQL Tools team has been reworking the module as SqlServer, this has become less of a concern. The module is less of a burden to load and the other components do not get in the way. There are also improvements and updates to the code to make it work better and serve more needs.

Mike makes good points, like how you can pretty much guarantee that Invoke-SqlCmd will be available, whereas you can’t always guarantee that third-party libraries (even if better) will be available on all systems at all times.

Comments closed

Local Filesystem Source Control

Published 2017-03-16 by Kevin Feasel

Steve Jones shares a tale of woe related to source control neglect:

One of the first things I found was that all our stored procedures in the production server were encrypted. I wasn’t sure why, since we hosted our machines, but that wasn’t a big deal.

Until it was.

One day we had an issue on one of our SQL Server 2000 servers (we had two, supposedly identical). In troubleshooting and putting some sample data in both systems for a fake customer, we got different results. Hmmm, not what I wanted to see.

I checked the VCS (SourceSafe at the time) and checked out the code. I then loaded my test data and … got a third, different result. Now I was concerned as this was a production bug that was delaying work for a customer.

I’ve become convinced over the past few years that having all of your code in source control (including database scripts!) is a key differentiator between a good work situation and a bad work situation.

Comments closed

Indexing Woes

Published 2017-03-16 by Kevin Feasel

Shane O’Neill relates a tale of trying to create an index with a SQL Agent job.  Easy, right?

Now I’m angry too since I count these failures as personal and I don’t like failing, so I get cracking on the investigation.
Straight away, that error message doesn’t help my mood.
I’m not indexing a view!
I’m not including computed columns!
It’s not a filtered index!
The columns are not xml data types, or spatial operations!
And nowhere, nowhere am I using double quotes to justify needing to set QUOTED_IDENTIFIER on!

SO WTF SQL SERVER, WHY ARE YOU GIVING ME THESE ERRORS???

Read the whole thing.

Comments closed

Coalescing In DocumentDB

Published 2017-03-16 by Kevin Feasel

Melissa Coates shows how to use the null coalesce operator in DocumentDB to provide default values for missing attributes:

This is a quick post to share how we can use the coalesce operator in Azure DocumentDB (which is a schema-free, NoSQL database) to handle situations when the data structure varies from file to file. Varying data structure is a common issue in big data and analytics projects. A schema-free database like DocumentDB allows us to ingest and store the data with varying structures without a lot of upfront effort. However, accommodating these varying data structures is challenging later when we want to analyze the data. When querying the data (think Schema on Read here), I do need to impose a consistent structure on the data to perform analytics.

Read the whole thing.

Comments closed

Disk Space Shenanigans

Published 2017-03-16 by Kevin Feasel

Meagan Longoria writes about an outage due to improper file layout:

One day, a manager asked me if I could help on an urgent matter: the application suddenly could no longer execute transactions on the production database and the database connection was intermittently failing. The system admin was busy with other duties, so I was the closest thing they had to a DBA.  All they could tell me was the production database had crashed and they got an error message about insufficient disk space.

Click through for the rest of the story.

Comments closed

Replication And TDE

Published 2017-03-16 by Kevin Feasel

Drew Furgiuele looks at how replication interacts with Transparent Data Encryption:

But what happens if we set up a transactional replication publication on this database and do a snapshot? Remember that when you create a publication, your distributor and subscriber(s) need to know which network share (or FTP server) to drop all the data and schema definitions to so they can be read in by the distribution agent and recreated. In my example, I’m dropping them to a network share. Once the snapshot completes, let’s go check out our subscriber database…

Uh oh. The same query returned zero results at the subscriber. Which means no encryption! Replication won’t replicate encryption, at all. So if you have a requirement to encrypt your data at the source, you’ll need to do it on your subscribers too.

Drew points out a couple important gotchas which might lead to you exposing information you didn’t intend to make available.

Comments closed

NULL Parameters

Published 2017-03-16 by Kevin Feasel

Riley Major turns this T-SQL Tuesday into thoughts on procedure parameterization:

But what if the caller wanted the date to be “empty” (i.e. 1900-01-01)? And what if a NULL is passed?

In our environment, we’ve disallowed NULLs from our table fields. We understand that NULL is actually information– it says that the data is unknown– but we believe that for most data fields, there are non-NULL values which just as effectively represent unknown. Typically, 0’s and empty strings (and the “blank” date 1900-01-01) serve that purpose. And those values are more forgiving during coding (they equal things; they don’t make everything else “unknown”), and we accept the risk of paying little attention to which parts of our logic touched “unknown” values.

It’s an interesting look at dealing with optional and default parameters within procedures.

Comments closed