A Fine Slice Of SQL Server
John McCormack wants to store a single space in a CHAR(1) column:
I was asked by a colleague why his where clause wasn’t being selective when filtering on a space value. The column was a char(1) data type. To understand the curious case of the space in char(1), we need to understand how the char data type works and also a bit more about the need for it in this scenario.
The ANSI standard makes sense, but it is something you have to keep in mind in cases like this.
Comments closedPretty recently hit very interesting problem of how SQL Server concatenates strings.
The very same SELECT clause in some cases produced VARCHAR(MAX), but in same cases it cut the result to VARCHAR(8000) ( or NVARCHAR(4000) )
After a research I’ve discovered following:
Read on to see what Slava has discovered.
Comments closedKenneth Fisher takes us through procedure parameters:
If you are executing a stored procedure with a bunch of parameters it can be a bit of a pain if you have to pass a value in for each of them. Fortunately, it’s pretty easy to make some parameters required and others optional. You simply give them a default value.
Kenneth also points out that functions don’t behave this way, and shows how to handle parameters where you don’t want to accept NULL under any circumstances. This is useful when NULL is just a placeholder for “I don’t really want to use this parameter” but the application doesn’t know how to avoid sending the parameter in the first place.
Comments closedIn SQL Server you can use LIKE operator to search for particular string in a column value. However it is important to note that when you search for a string which is within square braces, you need to escape the opening square brace.
Click through for an example.
Comments closedJana Sattainathan gives us a few options to delete data in a way which avoids major log growth:
Today, I had to perform a DELETE that happened to remove quite a bit of data. This caused two issues for me
1. Log file growth which fills up the Transaction Log of the DB
2. TempDB was getting fullTo avoid both problems I deleted in chunks.
Click through for two ways of doing it.
Comments closedErik Darling has a series on getting the highest value using CROSS APPLY. Part 1 covers the no-index route:
Let’s say you wanna get the highest thing. That’s easy enough as a concept.
Now let’s say you need to get the highest thing per user. That’s also easy enough to visualize.
There are a bunch of different ways to choose from to write it.
Part 2 covers the yes-index route:
In this round, row number had a tougher time than other ways to express the logic.
It just goes to show you, not every query is created equal in the eyes of the optimizer.
I don’t think I’m spoiling too much by saying that you really want a good index in place when using CROSS APPLY in this manner.
Nisarg Upadhyay shows three ways to calculate the median in T-SQL:
To calculate the median of any dataset, we first need to arrange all values from the dataset in a specific order. After arranging the data, we must determine the middle value of the specified dataset. If the dataset contains an odd number of values, than the middle value of the entire dataset will be considered as a median. Following is the example:
Median (M) = value of ((X + 1)/2) th item. (x is the number of values in the dataset)
Honestly, CLR’s probably the best approach here if you want a fast calculation for a reasonably large number of rows. Using ML Services and R/Python is another alternative, though the launchpad spinup time will probably make it slower than CLR.
Comments closedErik Darling wants you to simplify your life queries:
See, when a query is big and complicated to you, there’s a pretty good chance you’re gonna get a big and complicated query plan, because it’s big and complicated to the optimizer, too.
This isn’t to say the optimizer is dumb or bad or ugly; it’s just that there’s only so long it’s willing to spend coming up with a plan.
Remember, cheap plan fast. Not perfect, not great, maybe good enough.
It’s a good operating philosophy: if you have a query which has gone off the rails, one of the best things you can do is try to turn the query into several small steps. It’s possible to reduce complexity that way…though you may also gain complexity in the process if you do it wrong.
Comments closedPamela Mooney shows us how we can find broken code on our SQL Server instances:
Before we approached our last major SQL Server upgrade, I was curious about what might break. Yes, I had used the DEA to check our code against deprecated or discontinued code. But I am talking about code that might not have been used in some time and would break because objects no longer existed, or other things like that. So I wrote these scripts to refresh the sprocs, views and functions in our (non-production) environment.
Pamela’s motivation was to handle code which breaks during an update. You can also use this to see what you can probably deprecate—if the view doesn’t work, it can’t be in use. That means either someone should fix it or drop it.
Comments closedSo we proceed to execute an alter view over the first view:
ALTERVIEWdbo.[vi_invoices_received_by]ASSELECT
ConfirmedReceivedByas[Receivedby],
COUNT(InvoiceID)as[#ofInvoices],
CustomerIDFROMSales.InvoicesGROUPBYConfirmedReceivedBy, CustomerID;GOSo we should now be able to see the updated column names on the second view, right?
but if you execute the view again, you will obtain the same results as before:
Read on to see what’s gone wrong and how you can fix it.
Comments closed