Month: May 2018

When I started working with T-SQL, I thought the GO command was optional, kind of like semicolons. It appeared in plenty of SSMS generated scripts, but it seemed like I never had to add it to any queries of my own:

SELECT 1 as Col1 INTO #Test1;
GO
-- ...or....
SELECT 1 as Col1 INTO #Test2;
-- both seem to work equally well!

The above script will fail with the error message “‘CREATE/ALTER PROCEDURE’ must be the first statement in a query batch.”

There are multiple commands (“CREATE DEFAULT, CREATE FUNCTION, CREATE PROCEDURE, CREATE RULE, CREATE SCHEMA, CREATE TRIGGER, and CREATE VIEW”) that require being the first statement of a batch, so using GO is required if you are going to try running other statements as part of your script.

So how do you run that CREATE PROCEDURE statement after first checking and dropping that procedure? Just add GO so that CREATE PROCEDURE is the first statement of the batch:

Executing Commands Multiple Times

So the previous example was one where SQL Server required me to type two extra characters to run certain commands. Boo. What about something actually useful I can do with GO?

Sometimes you may want to run a statement more than once. You can do that by being trigger happy with the F5 key or your mouse button, but you don’t want to do that 10,000 times, do you?

That’s where GO shines. Simply add an integer after GO and SQL Server will execute that batch of statements however many times you specified. For example, the following code will insert 10,000 rows into a table:

INSERT INTO dbo.TestData VALUES (1,2,3)
GO 10000

This is really useful when creating test data or simulating workloads.

Tedious, repetitive tasks are the bane of any lazy programmer. I know, because I am one.

One such repetitive task that I find comparable to counting grains of rice is building database layouts from JSON data sources.

While some online services exist that will parse JSON objects into database structures, I don’t like using them because I don’t trust the people running those sites with my data. Nothing personal against them, I just don’t want to be passing my data through their servers.

My solution to this problem was to write a query that will parse my unfamiliar JSON documents into a series of CREATE TABLE statements.

Automatically Generating A SQL Database Schema From JSON

You can always get the most recent version of the query from GitHub, but I’ll post the current version below so that it’s easier to explain in this post:

In the variables section, we can define our input JSON document string as well as define things like a root table name and default database schema name.

There is also a string padding variable. This padding variable’s value is added to the max value length found in each column being generated, giving each column a little bit more breathing room.

Next in the script is the recursive CTE that parses the JSON string. The OPENJSON() function in SQL Server makes this part relatively easy since some of the work of determining datatypes is already done for you.

I’ve taken the liberty to convert all strings to nvarchar types, numbers to either floats or ints, booleans to bits, and datetime strings to datetime2s.

Two additional CTE expressions add an integer IDENTITY PRIMARY KEY column to each table as well as a column referencing the parent table if applicable (our foreign key column).

Finally, a little bit of dynamic SQL pieces together all of these components to generate our CREATE TABLE scripts.

Limitations

I created this code with a lot of assumptions about my (unfamiliar) JSON data sets. For the purpose of roughly building out tables from large JSON files, I don’t need the results to be perfect and production-ready; I just want the results to be mostly correct so the vast majority of tedious table creation work is automated.

With that disclaimer made, here are a few things to be aware of:

Sometimes there will be duplicate column names generated because of naming – just delete one.

While foreign key columns exist, the foreign key constraints don’t.

This code uses STRING_AGG. I’ll leave it up to you to convert to STUFF and FOR XML PATH if you need to run it in versions prior to 2017.

Summary

This script is far from perfect. But it has eliminated the need for me to build out these tables and columns from scratch. Sure, the output sometimes needs a tweak or too, but for my purposes I’m happy with how it turned out. I hope it helps you eliminate some boring table creation work too.

Normally that’s ok – SQL is a declarative language after all, so we’re supposed to tell it what we want it to do, not how we want it done.

And while that’s fine for most querying needs, it can become really frustrating when SQL Server decides to completely disregard what you explicitly asked it to do.

Why Is My VARCHAR(MAX) Truncated to 8000 Characters?

A prime example of this is when you declare a variable as VARCHAR(MAX) because you want to assign a long string to it. Storing values longer than 8000 characters long is the whole point of VARCHAR(MAX), right?

If we look at the above query, I would expect my variable @dynamicQuery to be 8001 characters long; it should be 8000 letter ‘a’s followed by a single letter ‘b’. 8001 characters total, stored in a VARCHAR(MAX) defined variable.

But does SQL Server actually store all 8001 characters like we explicitly asked it to?

No:

First we can see that the LEN() of our variable is only 8000 – not 8001 – characters long!

Copying and pasting our resulting value into a new query window also shows us that there is no character ‘b’ at position 8001 like we expected.

The Miserly SQL Server

The reason this happens is that SQL Server doesn’t want to store something as VARCHAR(MAX) if none of the variable’s components are defined as VARCHAR(MAX). I guess it doesn’t want to store something in a less efficient way if there’s no need for it.

However, this logic is flawed since we clearly DO want to store more than 8000 characters. So what can we do?

Make Something VARCHAR(MAX)

Seriously, that’s it. You can do something like CAST the single character ‘b’ as VARCHAR(MAX) and your @dynamicQuery variable will now contain 8001 characters:

But casting a single character as VARCHAR(MAX) isn’t very intuitive.

Instead, I recommend casting a blank as VARCHAR(MAX) and prefixing it to the start of your variable string. Leave yourself a comment for the future and hopefully you’ll remember why this superfluous looking piece of code is needed:

-- using CAST('') to force SQL to define
-- as varchar(MAX)
SET @dynamicQuery = CAST('' AS varchar(MAX))
+ REPLICATE('a',8000)+ 'b'