A Fine Slice Of SQL Server
Dave Mason wants to remove quoted identifiers from a flat file:
I haven’t shown all the columns, but you get the idea–every column in the result set has data enclosed in double quotes. That’s exactly how it appears in the source data file.
Dave has a method which works for plenty of versions of SQL Server. If you’re using 2017 or later, the FIELDQUOTE parameter was added to solve this problem, though to be fair, I haven’t actually tried it to see if it works as expected.
Erik Darling doesn’t want to mess with your cursors (that much):
Read the code. Understand the requirements.
I tune queries all day long. The number of times someone has said THIS CURSOR IS A REAL BIG PROBLEM and been right is pretty small.
Often, there was a tweak to the cursor options, or a tweak to the query the cursor was calling (or the indexes available to it) that made things run in a more immediate fashion. I want to tune queries, not wrestle with logic that no one understands. Old code is full of that.
I’ll grant the premise (and add my own case where a cursor was necessary to solve the problem), though I did work at one company where the entire product logic was driven by nested cursors 5 or 6 levels deep. Those were really big problems. I think you’ll find the problem most frequently in shops with a heavy dose of Oracle, as Oracle cursors do perform well.
Comments closedEduardo Pivaral takes us through the OUTPUT clause:
Even when the code is easy to read, but if you use this pattern over all your codebase, maintain it can become difficult if you have to change object names or implement it on another system.
T-SQL language provides the OUTPUT clause, that allows you to retrieve information from a DML statement in the same batch.
This is pretty useful for performance tuning in some scenarios, but also for simplifying multi-step processes.
Comments closedTaiob Ali helps us figure out when SQL Server inlines your scalar UDF:
Starting SSMS 18.2 a new attribute was added in QueryPlan. When the inline scalar UDF feature is enabled ‘ContainsInlineScalarTsqludfs’ value will be true. Let’s look at this in action. Run below tsql using the latest version of SSMS and after turning on the actual execution plan in SSMS.
Read on to see it in action.
Comments closedEd Pollack covers a topic of importance for database developers:
Within a data set, an island of data is any ordered sequence where each row is in close proximity to the rows around it. For some data types and analysis, “close proximity” will mean consecutive. Dates, integers, and letters of the alphabet can be ordered sequentially where two adjacent values will not be able to have additional values in between them.
For example, there are no dates between October 23rd and October 24th. Similarly, there are no integers between 17 and 18 and no English letters between E and F. For these examples, an island of data could be defined as a sequence of consecutive values. A gap can be defined as a sequence of missing values.
There are a lot of difficult problems which gap & island analysis makes much easier by pivoting the way you think about the problem.
Comments closedThe problem is that when I tried to index it:
CREATE INDEX dummy
ON dbo.Users(DisplayNameComputed);I got this error:
Msg 537, Level 16, State 3, Line 21
Invalid length parameter passed to the LEFT or SUBSTRING function.And when I tried to select data from the table, the same error.
Click through to find the real query killer.
Comments closedThe plan is created under CL 140, but the estimation number of rows for the table variable is not 1 but the actual one. Even if you would specify the FORCE_LEGACY_CARDINALITY_ESTIMATION hint, the query will be deferred compiled and behavior of table variable would be the same.
Table variable deferred compilation respects settings at the database scope and ignores all hints except one.
Click through to learn what that one hint is.
Comments closedRandolph West asks a pair of questions:
On Twitter recently, I asked:
Does anyone I know use the
COMPRESSandDECOMPRESSfeatures in T-SQL?To those who replied in the affirmative, I asked:
What made you decide on this as opposed to
ROWorPAGEcompression?
Read on to learn the responses. In my case, I’ve seen COMPRESS and DECOMPRESS used in two places. First, compressing large product descriptions (large enough to go to LOB). Second, I use it to compress binary models created via SQL Server Machine Learning Services. Some of those models compress quite nicely.
Jared Poche continues an investigation into the TOP operator:
I’ve explained what a blocking operator is and provided a few examples, but maybe this doesn’t seem important. It’s affecting the TOP operator, sure, but don’t people just use this to look at the TOP 1000 rows of their tables in SSMS?
The TOP operator is useful for many operations, especially in a large environment. Large operation can timeout or fail for a variety of reasons, consuming resources without providing the results you need. A small, batch-sized operation is more likely to succeed and tends to perform more consistently. Many maintenance operations make sense to run with a TOP operator, so we should make sure those operations aren’t stymied by blocking operators.
Read on for several examples.
Comments closedItzik Ben-Gan takes us through some of the complexities of NULL:
NULL handling is one of the trickier aspects of data modeling and data manipulation with SQL. Let’s start with the fact that an attempt to explain exactly what a NULL is is not trivial in and of itself. Even among people who do have a good grasp of relational theory and SQL, you will hear very strong opinions both in favor and against using NULLs in your database. Like them or not, as a database practitioner you often have to deal with them, and given that NULLs do add complexity to your SQL code writing, it’s a good idea to make it a priority to understand them well. This way you can avoid unnecessary bugs and pitfalls.
This article is the first in a series about NULL complexities. I start with coverage of what NULLs are and how they behave in comparisons. I then cover NULL treatment inconsistencies in different language elements. Finally, I cover missing standard features related to NULL handling in T-SQL and suggest alternatives that are available in T-SQL.
This is definitely worth the read.
Comments closed