Hello and welcome to yet another “Ronseal” title for a post…. and whilst this may not be something you’ll have to do regularly, searching for values in a query plan may be useful when running unit tests for SQL: you may be using it to confirm that a certain operator is used in the query plan, or whether a seek or scan is used… the possibilities are really endless.
The subject of my last post was to do with returning DBCC CHECKDB results from a temp table, which was populated by running DBCC DBINFO against each database hosted on the SQL instance. I shared a query that returned any databases that had not had a CHECKDB run against it for over 7 days. The query is below:
select dbi.Value from#db dbi
WHERE field LIKE'%lastknow%'AND (CASEWHEN isDate(dbi.value) =1THENCAST (dbi.Value AS DATETIME2(3))END) < DATEADD (DAY,-7, GETDATE())
That “CASE” statement stands out quite a bit doesn’t it. Let’s explore why this query needs a CASE expression. Begin by creating a temp table that contains a sample of the data stored in the temp table.
HOwever, replication is really something that you’re not going to learn through a book. You’re going to learn by working with replication in a real environment. And chances are a real replication environment is going to use more than one publication against groupings of subscribers.
Last week brentozar.com ran one of their Technology Triage Tuesdays on Compression in SQL Server. The video does not appear to be up yet, however it provided good insight, certainly better than the Technet pages on the same subject. As we have a multi-terabyte data warehouse at work, which is on an Enterprise licensed instance of SQL Server 2012, I’m familiar with the subject of data compression in SQL. Until recently however, another one of our other SQL Servers was on a Standard license. Recently this instance was upgraded to Enterprise, and so I was able to compress the database. Although not as large as our data warehouse, it was well worth considering compressing some of the larger tables.