A Fine Slice Of SQL Server
Koen Verbeeck hits a concatenation issue:
I was writing some dynamic SQL that generates some SQL statements to load my facts and dimensions into a data warehouse. Some of those SQL statements can become very long, for example if a dimension has a lot of columns. When debugging, I noticed a couple of statements failing with various errors. Turns out, they were truncated after 4000 characters. What was going on?
Read on to see what happened.
Leave a CommentSoheil Bakhshi doesn’t want everything in Power BI to be decimals and strings:
In my consulting experience working with customers using Power BI, many challenges that Power BI developers face are due to negligence to data types. Here are some common challenges that are the direct or indirect results of inappropriate data types and data type conversion:
- Getting incorrect results while all calculations in your data model are correct.
- Poor performing data model.
- Bloated model size.
- Difficulties in configuring user-defined aggregations (agg awareness).
- Difficulties in setting up incremental data refresh.
- Getting blank visuals after the first data refresh in Power BI service.
Read on for a dive into these problems.
Leave a CommentErik Darling tells the unpleasant truth about NULL:
Table definitions have a similar effect on developers. In today’s post, I’m going to use temp tables as an example, but the same thing can happen with regular tables, too.
The issue isn’t with NULL values themselves, of course. The table definition we’re going to use will allow NULLs, but no NULLs will be present in the data.
The issue is with how you query NULLable columns, even when no NULLs are present.
As a card-carrying member of the League of No-Null Workers, I am happy to tell you all about how much I hate NULL in databases. And this isn’t even the big reason.
Comments closedErik Darling theorizes about strings:
If you write the good kind of dynamic SQL, that is:
1. Parameterized
2. Executed with sp_executesql
You’ll probably have run into some silly-ish errors in the practice. Namely, that sp_executesql expects your SQL string and your Parameter string to be NVARCHAR(…).
Read on for some thoughts on data types, max-ness, and the like.
Comments closedKenneth Fisher sorts out data types:
With the expectation that you would get back a decimal. Did you wonder why it converted the integers to decimals rather than the other way around? If not you probably should have. I’ll give you a hint. It’s not because it’s the first value in the equation, although that does make a difference.
Thought about it? In case you didn’t figure it out T-SQL (and again, I’m going to say all programing languages where implicit conversions are allowed) has a conversion precedence order. In other words in any calculation where an implicit conversion is required the datatype with a value higher on the list is converted to the datatype with the lower value on the list.
The parenthetical statement above is correct. This makes things really convenient until you have an implicit conversion you weren’t expecting. There’s an advantage to explicit-only languages, though those do involve more typing.
Comments closedDid you know that 1/2=0? If you didn’t you could be in for some interesting surprises.
1/2 returns 0 because 1 and 2 are both integers. When you divide two integers you get back an integer. Now, we, as human beings, know that what you probably wanted was to get back 0.5. Unfortunately, computers really aren’t all that smart and we have to be very explicit with them.
Read on for more details, as well as a chart of which conversions may be implicit and which must be explicit.
Comments closedMeagan Longoria needs more Unicode:
You may have used the UNICHAR() function in DAX to return Unicode characters in DAX measures. If you haven’t yet read Chris Webb’s blog post on the topic, I recommend you do. But did you know there is a Power Query function that can return Unicode characters? This can be useful in cases when you want to assign a Unicode character to a categorical value.
Click through to see how this works.
Comments closedAs I was troubleshooting a performance issue, I noticed that there was an implicit conversion (SQL Server automatically converts the data from one data type to another) happening in my join. The join was on a column that was named the same in both tables, but one was datatype INT (integer) and the other was a datatype of VARCHAR(50) (variable character up to 50 places).
Read on for one way to resolve this issue…so long as no other calling code expects a string on a call.
Comments closedDave Saunders describes the concept of linear data structures:
As we said in the introduction, a data structure is ‘linear’ if the elements form a sequence.
That means that the data structure has a first and last element, and each element is connected to its previous and next element.
– An ‘array’ is a linear data structure; the items are stores sequentially.
– A ‘graph’ is not a linear data structure; any node can be linked to any other node in the graph – there is no fixed ‘sequence’.
Click through for several examples of linear data structures.
Comments closedKevin Wilkie lays out an argument:
If you truly need the UNICODE characters in your data, go ahead and use them! If not though, please make your DBA happy by not using them. Since UNICODE characters take up twice the amount of space as the ASCII versions do, then your DBAs will recommend to use the ASCII versions if you are not going to be using any UNICODE characters.
Read on for the justification. But I’m still NVARCHAR (Almost) Everywhere.
Comments closed