SQL Server – Issues I Have With SSDT Based Deployments: Part 4.2

Continuing the section on trying to get a large legacy database imported into an SSDT project that actually builds, we’re at a point now where we should have genuine reference issues to clean up; all our cross database references should be sorted via a database reference to a DACPAC (with or without changing everything to use SQLCMD variables or not).

At this point, we’re going to get really annoyed with all the legacy, simply doesn’t work anyway, code left in our database over years of not tidying up.  Technical debt sucks.

66829535

Lets look at some of the things that are going to annoy us! Continue reading

SQL Server – Issues I have with SSDT based deployments: Part 4.1

Continuing my series on SSDT based deployments, in this part, I’m going to look into something that’s not entirely SSDTs fault, but I’m sure could be less painful: importing pre-existing databases into an SSDT project.  Particularly ones that have cross database references, or three part naming of objects (database.schema.object) which effectively make them a self referencing object.

Firstly, I want to state that I don’t really like cross database references.  In a world of filegroups, schemas and so on, I think a large number of multiple database setups should probably just be merged into a single db.  I address quite a few of these thoughts in my article SQL Server – When is a new database appropriate?

However, I’m also aware that this refactoring takes time, and that the damage is already there.  Add this into the fact that these databases will most likely not have been developed with any source control, and so somehow we have to drag them, kicking and screaming, into TFS and an SSDT project…..

1581831440-jesus_kicking_and_screaming_cartoon

Continue reading

SQL Server – SYSDATETIME() versus GETDATE()

From SQL Server 2008, the datetime2 datatype was introduced, with greater flexibility for precision and also with an added bonus that it was truly compliant with ANSI and ISO 8601.  For more info, read up on the Books Online entry for datetime2.

While not everybody (including Microsoft!) have adopted this datatype, if you really want accuracy for your datetime columns, you’re better off switching to this type, and I know quite a few implementations that do.

Continue reading

SQL Server – When is a new database appropriate?

I frequently get requests from our developers to create new databases.  This is no biggie, and using the Standardising new database creation using Powershell and SMO scripts takes no time at all to implement.  However, something that usually isn’t considered at the point of a request is: why do we need a new database?

Continue reading

SQL Server – Delayed Durability and Log Flushes

This one stems from a an interesting discussion around Delayed Durability and In Memory OLTP on LinkedIn, looking at how Delayed Durability might be the winner for performance that In Memory OLTP is billed to be.  I think they have different purposes, but simply to improve throughput on specific workload types involving very high volume single record transactions, Delayed Durability could be a viable performance winner for you, with one caveat: you absolutely must be comfortable with some data loss.

Continue reading

SQL Server – Why storing dates as strings is a bad idea

Consider the following value: ’01-02-10′.  Is it the 1st February 2010?  Is it the 2nd of January 2010?  Is it the 10th of February 2001?

All three of those answers are potentially valid, and you can convert that string into all three:-

DECLARE @DateString CHAR(8) = '01-02-10';

SET DATEFORMAT DMY;

SELECT CONVERT(DATE, @DateString);

SET DATEFORMAT YMD;

SELECT CONVERT(DATE, @DateString);

SET DATEFORMAT MDY;

SELECT CONVERT(DATE, @DateString);

whichdateisright

71902217

Continue reading

SQL Server – Understanding the TOP operator and implications for batch processing

I was debating using a picture of Kylie Minogue in this blog post, from her video for the song “Spinning Around”.  Because it is relevant, and not just because it’s a picture of Kylie….

kylie-289808
Kylie likes loops

Anyway.  Today, we had a problem where “all of a sudden” CPU was maxing out on a server, and the disk I/O was going through the roof.  Uh-oh, I thought, today is going to be rubbish.  Rolling up my sleeves, I opened up SQL Sentry Performance Advisor to have a look what was going on.

Continue reading