2016-06

2016-04

2016-03

"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.

2016-02

2016-01

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 (CASE WHEN isDate(dbi.value) = 1 THEN CAST (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.

2015-12

2015-11

2015-10

2015-09

2015-08

2015-07

2015-06

2015-05

2015-04

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.

2015-03

2014-10

when a query contains table joins in SQL Server 2014.

The poor performance may show up if all the following apply:

  • You query contains an inner join
  • both tables have a Clustered Columnstore Index
  • There is a hash join executing in batch mode
  • the plan decides that a bitmap filter will be selective enough to be useful
  • the bitmap filter used is a complex bitmap filter (filter is made up of multiple columns and data types, whilst simple is a single column filter)

2014-09

2014-08

SQL Server 2014 Clustered Columnstore Indexes and Partitioning. The script below will create the database and the objects necessary. I’m going to state the obvious, but read through the scripts first before you run them; things like file locations on boxes are always different:

2014-07

2014-06

2014-05

2014-03

2014-02

ApexSQL Recover) if you’re quick enough.

2013-10

2013-05

2013-04

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.

2013-03

2013-01