A Fine Slice Of SQL Server
Slava Murygin gives us unique file names:
That is pretty common task to generate new files with a timestamp in their names.
It gives you ability to easily identify them, sort them and make them pretty unique.
However, if you have a very busy process it is possible that duplicate name will be produced and you might loose some data.
To avoid that situation I’ve came up with following solution.
It is difficult to envision this solution going wrong.
Comments closedBert Wagner reminds us that integer math is a thing:
To determine how much lumber I would need for building the new walls, I decided to write a SQL query to help with my framing calculations. I was building a 6 foot wall and wanted to put a stud every 16 inches. Easy enough to do the math on this:
SELECT (6*12)/16The output of the query above was 4, indicating the number of studs I would need for one wall section.
What’s interesting is that if we do this same equation in a calculator, we get a slightly different answer: 4.5.
Click through for Bert’s thoughts on the issue. Other languages and platforms do this as well, so it’s not unique to SQL Server, but if you’re not used to it, you might be in for a surprise.
Comments closedDaniel Hutmacher has a painful but realistic problem to solve:
Here’s an example customer. You’ll notice right off the bat that we’re sending this customer an invoice every day on the 20th of the month. To add some complexity, the customer will arbitrarily pay parts of the invoiced amount over time, and to add insult to injury, the banking interface won’t tell us which invoice the customer is paying for, so we’ll just decide that each payment goes towards the oldest outstanding invoice.
Our task is to calculate how many days have elapsed, for each invoice, from invoice date to payment in full.
Daniel has an excellent solution to the problem, so check it out.
Comments closedBert Wagner is on a quest to find out who moved his cheese:
Have you ever wondered who was the last person (or process) to modify a piece of data in your database?
SQL Server offers plenty of system views and functions that provide insight into how your server is running and how your queries are performing. However, it doesn’t offer much information about who last modified your data.
There are a few workarounds, though they aren’t great.
Comments closedItzik Ben-Gan wraps up an outstanding series:
When people want to pivot data using T-SQL, they either use a standard solution with a grouped query and CASE expressions, or the proprietary PIVOT table operator. The main benefit of the PIVOT operator is that it tends to result in shorter code. However, this operator has a few shortcomings, among them an inherent design trap that can result in bugs in your code. Here I’ll describe the trap, the potential bug, and a best practice that prevents the bug. I’ll also describe a suggestion to enhance the PIVOT operator’s syntax in a way that helps avoid the bug.
If you use the PIVOT operator, you definitely want to read this article.
Erik Darling has a card trick for us:
I always try to impart on people that SQL injection isn’t necessarily about vandalizing or trashing data in some way.
Often it’s about getting data. One great way to figure out how difficult it might be to get that data is to figure out who you’re logged in as.
There’s a somewhat easy way to figure out if you’re logged in as sa.
Wanna see it?
Of course you do.
Comments closedMax Vernon takes us through column dropping:
Dropping a column that is not referenced by any other object lets the storage engine simply mark the column definition as no longer present. Deleting the meta-data invalidates the procedure cache. Any query that subsequently references the affected table will result in the plan for that query be recompiled. The recompile operation can only return columns that currently exist in the table. As a result, the storage engine skips the bytes stored in each page for the dropped column, as if the column no longer exists.
This has some nice benefits in practice around minimizing deployment-releated downtime.
Comments closedTim Weigel continues a series on change tracking:
If you don’t provide a retention period, SQL Server’s default is 2 days. Auto-cleanup defaults to ON unless you tell it otherwise.
Easy!
The table level commands aren’t any more complicated. Before we get started, please note that change tracking requires a primary key on the table you want to track. This is reasonable – you need some kind of unique identifier to tell you which row has changed.
Read on for the scripts and further explanation.
Comments closedMax Vernon points out how transaction isolation levels work when combined with sp_executesql:
Imagine you have a piece of code where you don’t care about the downsides to the “read uncommitted” isolation level, and do your due diligence by adding
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;at the start of your code. The code following that statement will run under that isolation level, as expected. However, if you call dynamic T-SQL withsys.sp_executesql, and set the isolation level there, the dynamic code will run under theREAD UNCOMMITTED, however the isolation level will not be changed for the calling code. In other words, be careful about where you set the isolation level.
Click through for a demonstration of this.
Comments closedThomas Rushton has a script for us:
Why Script This? What’s Wrong With SSMS’s GUI?
Well, although SSMS does allow you to look at the error logs, it’s not very helpful for filtering – you can only filter for items that match, rather than exclude items. There are a few other filters as well – I guess the whole thing is just a wrapper around xp_readerrorlog below…
But Thomas has a better way for us.
Comments closed