A Fine Slice Of SQL Server
Chris Johnson shows off using the VALUES operator to construct records:
Values blocks are a really useful little bit of code in SQL Server. Basically, they are a block of defined values that you can use pretty much like any other data set.
It’s a nice way quickly to build a derived table. As a bonus, I’ll tie in an older post of mine around using APPLY with VALUES to unpivot data sets.
Dave Bland shows us the Poor SQL formatter for SQL Server Management Studio:
So what is PoorSQL Formatter? Well, as you probably know, formatting is very important when writing T-SQL code. While we try to format our code as we write it, sometimes the code we need to review was written by someone else and maybe doesn’t follow formatting standards. This is where PoorSQL Formatter will come in handy. It will help us take the poorly written code and format it in a manner that is more in line with T-SQL formatting best practices. The best part, it will do the formatting in seconds.
I’ve used that website many a time. It’s not as detailed as I want it to be, but if you just need a quick and dirty way to format a query so that it’s understandable, this does the trick.
Comments closedChris Johnson talks about a concept dear to me:
Basically code noise is anything that pulls your attention away from what the code is supposed to be doing, or obscures the true nature of the code in some way. It’s not something we consider enough when writing T-SQL code, but I think there is a lot to be said for writing code the next person will be able to read.
As a small example, I was debugging something recently and found that all of the insert statements had
ORDER BYclauses. I couldn’t work out why these were making me so angry, after all it’s not doing anything to hurt performance, and in fact isn’t doing anything at all, until one of the other devs in the office pointed out that it’s one example of the code noise that the whole code base is filled with.
Chris provides us a couple examples of noise. My bottom line on this is, develop to the minimum required standards of what the computer needs (i.e., accurate data, fast enough, etc.) and give the humans maintaining the code a fighting chance. Spend more time making it easy for humans and make everybody’s life easier.
Comments closedJohn Sterrett walks through one of the most important set operators:
Selecting the two tables is easy if we know what change occurred and there aren’t many changes. This can get complicated quickly. Therefore, if we just want to quickly know if we have differences lets take a look at my goto method using EXCEPT. To make this example easier to read instead of using “SELECT *” I will just focus on columns that are changing. In a real example, I would want to know if any columns changed.
One of the nicest things about set comparison operators is that they innately understand the concept of NULL.
Comments closedJason Brimhall explains how SQL Server comes up with names for auto-created statistics:
Every now and again I am asked about the meaning behind the automatically generated names for statistics in SQL Server. The quick answer is short, sweet and really easy. I give them a quick explanation and then often refer them to the blog post by Paul Randal on the topic.
The better answer is to show them what the auto-generated names really mean, alongside the great explanation from Paul. Finally, after years of the topic being on my backlog, I am sharing a script that will help decode those names and help to prove out fully what’s in a statistic name.
The proof is in the SQL; click through to see it.
Comments closedAllen White takes us through an easy technique to check database and log file sizes:
As a consultant, I have to be able to quickly spot problems, and one of the problems I frequently find is transaction log files that are incorrectly sized.
There are two catalog views in the master database which make this easy to do – sys.master_files and sys.databases. The sys.master_files view contains the database and individual file names, and the data_space_id column always has a value of 0 for the log file. The size column returns the value in 8KB pages, so we have to multiply the column by 8, then divide by 1024 to get the size in megabytes (MB).
Click through for the demo.
Comments closedLukas Eder takes a look at the MERGE statement in SQL:
A few dialects support MERGE. Among the ones that jOOQ 3.13 supports, there are at least:
– Db2
– Derby
– Firebird
– H2
– HSQLDB
– Oracle
– SQL Server
– Sybase SQL Anywhere
– Teradata
– VerticaFor once, regrettably, this list does not include PostgreSQL. But even the dialects in this list do not all agree on what MERGE really is. The SQL standard specifies 3 features, each one optional:
– F312 MERGE statement
– F313 Enhanced MERGE statement
– F314 MERGE statement with DELETE branchBut instead of looking at the standards and what they require, let’s look at what the dialects offer, and how it can be emulated if something is not available.
This is a really cool overview of an area where several vendors can claim support, but that support can mean quite different things. The one caveat is, I don’t know if any of the other platforms’ MERGE operators are as busted as SQL Server’s in terms of bugs.
Itzik Ben-Gan has started a series on table expressions:
Perhaps this will come as a surprise to some, but I actually do find the use of the term table in common table expression as very appropriate. In fact, I find the use of the term table expression as appropriate. To me, the best way to describe what a CTE is in T-SQL, it’s a named table expression. The same applies to what T-SQL calls derived tables (the specific language construct as opposed to the general idea), views and inline TVFs. They are all named table expressions.
If you can bear with me a bit, I’ll provide the reasoning for my view of things in this article. It occurred to me that both the naming confusion, and the confusion around whether there’s a persistency aspect to table expressions, can be cleared with a better understanding of the fundamentals of our field of relational database management systems. Those fundamentals being, relational theory, how SQL (the standard language) relates to it, and how the T-SQL dialect used in the SQL Server and Azure SQL Database implementations relates to both.
There’s a lot of depth in this post, so I recommend a careful reading.
Comments closedDave Mason runs through a tricky problem with SQL Server Machine Learning Services:
During my testing, I’ve found R handles CHAR and VARCHAR data within the input data set as long as the ASCII codes comprising the data is in the range from 0 to 127. This much is not surprising–those are the character codes for the ASCII table. Starting with character code 128, R begins having some trouble.
Read on to see the problem. Dave’s advice at the end is sound (and frankly, my advice for any string data in SQL Server).
Comments closedAndy Levy tries to make cats and dogs live together:
This tripped me up a few weeks ago, but once I stopped and thought about for a moment it made total sense. I was trying to copy some data into a temp table and got an error I’d never encountered before.
Column, parameter, or variable #1: Cannot find data type MyStringType.What’s that all about? Let’s find out.
I don’t think it spoils things to say that Andy’s story is a tragedy and not a comedy. But in fairness, the number of shops using user-defined types (as opposed to user-defined table types) is probably not enormous.
Comments closed