Worth Corner, Crawley, RH10 7SL

Support Hours - Mon-Sat, 8.00-18.00

Search Results...

2014 Cardinality Estimator Exponential Backoff
Produced: 11/12/2016 11:38:00
This is going to be a very basic explanation as to the differences seen in my previous post (about TF 9481) in which we encountered the new and old cardinality estimators providing different estimates to what looked like a very simplistic query.

A Little Happiness in SSMS
Produced: 04/03/2016 06:52:00
This is basically an introduction into something I want to play with in some upcoming posts, but to start with I thought I would introduce the topic with a little bit of happiness.

Access a Network Drive Using SQL Server
Produced: 25/12/2015 07:38:00
This is a handy hint as to how to access a network drive from within SQL Server. It just happened to be something that I was using in some code and was asked what the code was doing.

Accessing Synonyms Via Linked Server
Produced: 25/06/2017 17:48:00
This was new to me and something which has appeared as a major downside for me when considering whether to use synonyms or views within my databases.

Adding and Removing Extended Properties
Produced: 16/07/2017 11:54:00
This is an obvious continuation of my previous post about Extended Properties in which I will show you how easy it is to add and remove Extended Properties from a table in SQL Server.

Agent History Cleanup - Distribution Transaction Log Full
Produced: 31/03/2014 20:45:00
A company I was visiting were having problems with the "Agent History Clean up" job that Microsoft installs as standard on a Distributor server. They had disabled the job because, as the job history showed, it was no longer completing as expected but was running forever and in doing so was causing the distribution database log file to rapidly increase in size until it Windows reported the disk as full. Once full they were having to kill the job, restart SQL Server (very risky as the job was now in rollback but couldn't comlpete due to disk space), truncate the transaction log, and then fix the backup chain for the sake of their disaster recovery requirements. This was clearly a huge problem for them as, without this job running, the distribution database would never stop increasing in size.

Alerting For Replication Latency Using Tracer Tokens
Produced: 15/01/2016 07:53:00
This is a direct follow up to my previous post on Tracer Tokens and Replication Latency.

This post will include some simple code to check for excessive latency in your replication and, if appropriate, send out an email informing you of the problem.

Alternative to sp_rename
Produced: 13/11/2016 10:08:00
There are a few flaws with the sp_rename method when switching tables or even simply renaming them… note that these aren’t bugs, they’re simply annoyances encountered which can otherwise be avoided.

An Alternative to a Data Accessing Function
Produced: 23/10/2016 08:59:00
Once again I’m going to refer to previous posts here as this is actually an extension of my previous two posts.

This was something I came across recently when working on a tuning project… the original code was very slow and this was quickly traced to a good old function.

An Introduction to Policy Management
Produced: 30/06/2014 17:31:00
This was a new feature added to SQL Server 2008 and it’s just superb. I honestly don’t think it gets the appreciation that it deserves as it seems to be rarely seen or used despite the control it can give you over your instances.

Applying Policies to Multiple Servers
Produced: 21/07/2014 19:28:00
The good thing about a Policy is that you don’t have to have a copy on every single server to be able to enforce it. This makes Policy Based Management very appealing to DBAs who have a central server which they can use to create and hold policies to then run against multiple other instances on the network in order to bring them into line without having to create numerous different policies on numerous different sets of servers.

Automatic Update Statistics Threshold
Produced: 27/03/2015 17:25:00
This is a trace flag I only learnt about recently which actually fixes something that has been a large bug bear of mine for quite some time.

Basically we all know that statistics are one of the most important things in SQL Server and therefore it’s also critical to know how they’re maintained.

Backup a Database Using SQL Server Management Studio
Produced: 20/01/2014 20:12:00
Making backups using the GUI is a little longer than when using T-SQL (here), but they do provide a much easier way to see and customise your backup options.

Backup a Database Using TSQL
Produced: 27/01/2014 20:17:00
This is actually the cleanest and quickest way to produce a backup as it is a one line command in the form of:

Backup Types
Produced: 16/12/2013 09:14:00
There are 3 backup types within SQL Server… Full, Differential, and Transactional.

The reason for there being 3 types are not immediately obvious but understanding them is key to having a good backup strategy within your system. These are also relevant to the Recovery Mode of your databases.

Basic DELETE statement
Produced: 22/07/2013 20:26:00
Okay, this time an UPDATE simply won’t suffice. Something is badly wrong and we need to remove a record rather than just update it.

Basic GROUP BY Usage
Produced: 29/07/2013 09:45:00
This is a key phrase when referring to any aggregate function as all aggregate functions require grouping in order for the data to have any meaning. As such this is a mandatory key phrase to be used within all aggregation.

Basic Guide to SQL Server Configuration (sp_configure)
Produced: 05/08/2013 11:49:00
Configuring your SQL Server correctly is one of the key tasks that must be completed in order to get the best performance out of your system.

Basic INSERT statement
Produced: 15/07/2013 14:59:00
This article is a simple and brief introduction to the INSERT statement within SQL Server. This, as the name suggests, is the method used to enter data into our databases.

For this example we'll use a Person table which you'll need to create using the following script:

Basic Look At Statistics
Produced: 13/03/2015 11:42:00
People are always mentioning Statistics… “Keep your statistics up to date” etc. but, although in my last post I gave a good example as to why you really should keep them up to date, there aren’t that many places explaining what Statistics actually are. Therefore I figured I would give a quick overview.

Basic OUTPUT statement
Produced: 24/06/2013 20:26:00
Within SQL Server there is a very useful clause that you can combine with all statements (INSERT, UPDATE, DELETE) in order to provide further information or details as to what’s happening… that is the OUTPUT statement.

Basic SELECT statement (with WHERE clause)
Produced: 08/07/2013 17:28:00
This article is a simple and brief introduction to the SELECT statement within SQL Server. This is the most basic and most useful tool in the SQL language. It allows us to see what data is being held in our databases.

Basic UPDATE statement
Produced: 01/07/2013 11:29:00
Okay, we’ve inserted data, selected data, but what happens if we find some of our data is wrong? That’s where we need to UPDATE our records.

Basic Warning About UNION
Produced: 17/04/2015 10:28:00
I was looking for some sample code to help me with a personal C# application I was coding and in looking around I saw a perfect example as to what not to do when manipulating a database.

Be Very Careful with Sampled Statistics
Produced: 18/12/2016 16:03:00
Okay, we all know that Sampled Statistics are not perfect otherwise there would be no need for a Full Scan stats update. However, there’s an issue I encountered recently that has really bothered me in regard to these.

Better Row Estimates with Table Variables
Produced: 17/06/2016 12:54:00
In this episode of “Fun with Flags” we’ll be looking at TF 2453.

Having been recently playing with SQL Server 2014 I was intrigued to see the following in the list of fixes included for Cumulative Update 3 (note this is also included in SQL Server 2012 Service Pack 2)…

Blocking Chain
Produced: 21/04/2014 16:04:00
There are numerous variants of sp_who2 kicking around the internet and most of them go to absurd levels of intricacy.

Bug: Implementing Incremental Statistics
Produced: 02/04/2017 18:36:00
I’ve covered Incremental Statistics over a couple of posts now and although they are a fantastic feature, there is a bug which you need to be aware of. Luckily it’s by no means a showstopper, more just an annoyance.

Calculating Cumulative Totals
Produced: 01/04/2016 14:55:00
Following on from my last post really, this was another aspect of the MI I was helping out with which benefitted from the use of some more modern code (SQL 2012 or above) in order to make code both clearer and more concise. It is also a lot faster and less strain on the server using the newer method.

Check SQL CPU Utilisation vs Other
Produced: 20/11/2015 09:46:00
This is a simple piece of code pinched from the Microsoft Performance Dashboard, but it’s useful and needs highlighting on its own as I tend to use it extensively.

Check Your Database Growth Settings
Produced: 01/05/2015 08:43:00
This is another bug-bear of mine… it’s something I believe should always be set at the time of creating a database and then periodically checked and altered where necessary... and this applies not only to the data file but to the log file as well.

Clustered Index Orders Pages Not Rows
Produced: 16/04/2017 09:01:00
This is something I’ve had mentioned to me a lot recently… that a clustered index ensures that records must be stored in order on the data pages.

But it’s just not true.

Clustered Index vs Non-Clustered on Heap
Produced: 13/02/2015 10:46:00
I had a question recently in which someone asked the following:

"I'm trying to determine what the overall performance difference would be in the following situation…

Assume I have a large table, 500M records that have a non-unique RecordID column (eg. RecordID (BIGINT), SubID (BIGINT), Name, Detail)

I'm only ever going to select * from Table where RecordID = ?

If I create a clustedIndex on RecordID the execution plan shows only two steps

Select + Clustered Index Seek

If I leave the table as a HEAP and create a no-clustered index on RecordID the execution plan shows 5 steps

Nested Loops Inner Join <- ((Compute Scaler + Index Seek) + RID Lookup)

Clearly I'm going to get better insert performance when inserting into the HEAP, especially when page spits are required on the clustered index.

What I don't know is what the select performance difference would be under load.

e.g. I can expect the RID lookup to be X% slower than the Clustered Index Seek"

The answer I gave was as follows:

Columnstore A Table
Produced: 15/01/2017 19:15:00
At some point I will explain the nuances of the Columnstore index, how they work, when and where you should use them, and what they’re for… but in the mean time I have recently had situations in which I have upgraded to SQL 2014 and have needed to Columnstore a table.

Configure Distribution
Produced: 09/12/2013 18:25:00
In order to get up and running creating Publications on your server, you first need to either set up a Distributor, or register a pre-existing remote Distributor. To do this we’ll use the Wizard.

In SSMS, right click Replication and select “Configure Distribution…”:
Contention and Multiple TempDB Files
Produced: 30/04/2017 16:07:00
We all know that tempDB is the scratchpad for SQL Server and is used for all manner of things from, obviously, housing our temp tables through to being used for memory spills during query execution.

Continuous or Scheduled Replication (my two cents)
Produced: 11/11/2013 14:37:00
This defines how often the SQL Server Agent job runs… does it run to a schedule, or does the job run continuously.

Copying User Access From Database to Database
Produced: 12/12/2014 16:59:00
This topic came about the other day when I was required to move some tables into a completely new database as part of a schema restructure and new project.

Copy-Only Backup
Produced: 03/02/2014 09:50:00
This is a crucial addition to SQL Server which is invaluable at times. It basically takes a snapshot of the database rather than an actual backup. Therefore it can be used to restore a database and to provide a copy, but crucially it does not break the backup chain.

Could not find stored procedure dbo.sp_MScheckIsPubOfSub
Produced: 23/12/2013 15:46:00
I was working on one site when we got a very strange replication error appear. I’ve still no idea what caused it, but that won’t stop me posting the error message itself along with the simple solution…

Create a Database Using SSMS
Produced: 26/08/2013 13:04:00
IMPORTANT: The majority of the settings you’re about to see are actually taken from the system database “model”. The model database is, as its name implies, the “model” by which all others are created. Therefore if you change settings, initial sizes, growth rates, and recovery options in the model database, then those are the defaults you will be presented with when creating a new database from scratch following the method below. If you have some settings that you wish to apply generically to all databases, then simply change them on the model database.

Create a Database Using SSMS
Produced: 26/08/2013 13:04:00
IMPORTANT: The majority of the settings you’re about to see are actually taken from the system database “model”. The model database is, as its name implies, the “model” by which all others are created. Therefore if you change settings, initial sizes, growth rates, and recovery options in the model database, then those are the defaults you will be presented with when creating a new database from scratch following the method below. If you have some settings that you wish to apply generically to all databases, then simply change them on the model database.

Create UNIQUE Clustered Index
Produced: 22/04/2016 10:55:00
This is a bug bear of mine but something which I see all over the place in people’s code… if you’re going to create a clustered index on a table (including temp tables) and the key you’re using is unique, then let SQL Server know. You’ll be surprised how much difference it makes.

Creating a Database Snapshot
Produced: 01/01/2017 22:04:00
This is pretty simple really, but it’s useful code and therefore I wanted it on my website mainly for my reference on those days in which I can’t remember the syntax and want a simple copy and paste option. We all have our forgetful moments after all :o)

Creating a Partitioned View
Produced: 23/04/2017 09:08:00
This is an alternative to native table Partitioning, and something I would potentially rather use instead whether I have Enterprise Edition or not.

Creating a Simple Backup Maintenance Plan
Produced: 13/01/2014 19:23:00
It is possible and perfectly acceptable to put T-SQL commands into a scheduled task in order to create a full backup plan, but a Maintenance Plan is Microsoft’s way of allowing you the power of the GUI backup with the convenience of a scheduled task so that you don’t have to code your own.

Critical Alerting Costs Nothing
Produced: 17/07/2015 09:53:00
I’ve yet to come across a SQL Server which actually makes use of some of the free alerts which SQL Server will hand you if you let it. These are alerts which are already logged inside of SQL Server and are just waiting for you to set up some notifications around them.

CTEs - Formatting Not Performance
Produced: 31/07/2015 19:13:00
I was reading a forum the other day in which someone was asking whether they would be better off changing their code to use CTEs for performance gains. I was surprised by the amount of responses by people thinking that CTEs are a performance tool.

CTEs are Updateable
Produced: 03/06/2016 11:31:00
Now to all those people saying “well, duh”, this clearly isn’t a post for you, but this is genuinely something I didn’t realize until I saw it the other day and went to try it out for myself.

Current State of Scheduled Jobs
Produced: 11/12/2015 07:22:00
This is another useful piece of code (well, I find it handy anyway) which I wrote to help populate a dashboard.

Database Consistency Checks
Produced: 24/07/2015 19:27:00
This is vital to the health and safety of your data and databases. Running regular consistency checks will allow you to spot any corruption within your database at any and all levels. Nearly all databases will have some element of corruption at some time or other and therefore you need to be prepared.

Database Data and Log File Sizes and Growth
Produced: 21/08/2016 20:51:00
This isn’t going to anything in depth around the subject such as what you should or shouldn’t have as your standard settings etc. but instead it’s actually just a handy piece of code I knocked together which I happen to use quite a lot, especially when in a new company, just to see how SQL Server is set up.

Database Mail Working But No SQL Agent Alerts
Produced: 23/06/2014 15:20:00
This is a common “problem” that I see within SQL Server and was asked about just yesterday, but 99% of the times, and with this particular instance as well, it’s actually not a problem at all just a missing setting within the SQL Server Agent.

Date and Time Formats in SQL Server
Produced: 14/10/2013 09:27:00
There are numerous times you might want your dates returned in a different format and SQL Server will cater for this.

Produced: 16/09/2013 18:41:00
I’ve always found that no matter what I end up coding, I always have to manipulate a date or two. Whether it’s finding last week’s date, extracting the minute from a datetime, or calculating how long someone has taken between orders, it always comes down to manipulating dates.

Datepart DW Giving Inconsistent Results
Produced: 04/09/2016 22:13:00
I would imagine that most people tend to write “set nocount on” at the start of most code blocks and procedures as standard these days but, having been caught out with this recently, I have found a new “set” command that I’ve started to add to my code as well.

Deadlock Alerts via Email
Produced: 16/01/2015 11:36:00
In the second episode of Sheldon Cooper’s Fun with Flags, we’re going to be looking for deadlocks.

There are numerous ways of tracking deadlocks within SQL Server but as we rarely know when they’re going to occur, don’t want traces running all day, and want to react quickly, the best method I’ve found is to have them sent to me in an email each time they appear.

Delayed Durability - What is it?
Produced: 25/12/2016 16:06:00
This is one of my favourite new features of SQL Server 2014 as it can make an absolutely huge difference to your servers when performing inserts, updates, and deletes.

Different Query Results with row_number
Produced: 12/09/2014 18:30:00
Today I was asked a question that left me baffled for a good few minutes whilst trying to come up with an answer. Sadly my mistake all along was that I trusted a developer. Ah well, happens to everyone sometimes.

Don't Panic Your Code Isn't Lost
Produced: 05/12/2014 16:58:00
The other day I had a very stressed developer coming to see me… he had been in the middle of coding something when his PC pulled the ever inventive “Blue Screen of Death” trick on him.

Drop Failed For User
Produced: 18/03/2016 12:11:00
This was an error that I hadn’t seen in a while and therefore I thought I would mention it. The error normally seen here is around the user owning a schema and so it cannot be dropped, but this was different:

Dynamically Script a Table
Produced: 08/10/2017 11:30:00
This is a useful script that will let you dynamically script a table… yes, I realise that SSMS offers a point and click function which does the same, but if you’re building a script that you wish to run over and over on a changing architecture then you can’t afford to spend your time scripting from SSMS and maintaining all changes to the scripts over time.

Easily Baseline Your Servers
Produced: 25/09/2015 12:04:00
This is a common requirement in all companies… servers must be baselined otherwise how do you know if changes are detrimental or beneficial? How do you know where to look if something suddenly goes wrong?

Easily Calculating End of Month
Produced: 15/04/2016 16:54:00
Something I’ve had to do a lot recently is to calculate financials which involved using an end of month date. This has always been a pain to calculate in SQL Server. There are a lot of ways of doing it, but all are annoying.

Email Alert from On Change Log Only
Produced: 07/07/2014 19:07:00
Having Policy Management set up is great… being able to schedule a job to start up, check your systems, and report anything that doesn’t fit your ideal is fantastic. But what about those policy breaches which cannot be automatically rolled back (On Change: Prevent is not an option) but for which you want to be immediately notified?

Every Statement Is Parameterized
Produced: 09/10/2015 10:15:00
This is a common misconception that I hear from devs and DBAs alike… people seem to believe that SQL Server will parameterize every statement it runs and store the plan for re-use later.

Execution Plans and XML Documents
Produced: 13/05/2016 09:49:00
Over the past few months I’ve been working with a lot of XML and stored procedures and it’s been a different experience, and not something I’d always refer to as “fun”.

Fill Factor With Large Row Sizes
Produced: 08/05/2015 08:47:00
This is just a little something to look out for when administrating a database as I’ve seen it catch a few people out.

Basically it’s all about making sure you have the correct fill factor for your indexes so that you don’t unnecessarily bloat your databases as a whole.

Forcing Parallelism (TF 8649)
Produced: 28/11/2014 16:54:00
In this first episode of Sheldon Cooper’s Fun with Flags I’m going to be looking at my favourite flag of the moment… Trace Flag 8649.

And yes, before anyone points it out, I know having a favourite trace flag is infinitely more disturbing than having a favourite real life flag but I’ve learnt to live with it and you should too.

Foreign Keys Are Allowed to be Null
Produced: 11/09/2016 06:31:00
Something that came up the other day when talking to a developer was that they were unsure as to whether or not they could have a foreign key relationship to a table when they didn’t necessarily have a foreign key value for all records.

Full Backup Does Not Truncate Log
Produced: 24/04/2015 08:30:00
This is a massive misconception that has been going around for a long while and it needs to be wiped out.

FULL Recovery Model - Policy Management
Produced: 14/07/2014 19:17:00
In most SQL Server environments with fast moving data we want to ensure that we will suffer the least amount of data loss in an outage or disaster. This means using the FULL recovery model. There are some circumstances in which someone might wish to change the database to a SIMPLE mode in order to, for example, shrink a runaway transaction log.

Gathering Tracer Token Data
Produced: 08/01/2016 07:50:00
After my last post surrounding Tracer Tokens I was told by a friend that it was a little harsh to simply end saying that you can just join to a few system tables and make something meaningful. Therefore I’ve given in and this post will show you how you can use tracer token and system data to your advantage.

Gradual Database Shrink
Produced: 04/12/2015 07:17:00
First things first… NEVER shrink your database unless you REALLY have to.

Grant Execute To All Procedures
Produced: 18/12/2015 07:34:00
I’ve been in numerous situations now in which people require security in their SQL Server (obviously) but find that the provided db_dataReader and db_dataWriter aren’t quite good enough. They require execute rights on all procedures, but find this is hard to set up.

Guide to System Databases
Produced: 23/09/2013 12:46:00
There are 4 main system databases and 1 additional database. These are:
Handy Trick in SSMS
Produced: 09/04/2017 14:50:00
This is a quick post about something I only found out recently and which has made my life so much easier in many circumstances. It’s something I also imagine you’ll soon use a lot too if you don’t already.

Hash Join Operator
Produced: 14/11/2014 13:56:00
This is the last of the join operators and possibly the most powerful. It is possibly the most common join operator and, for some reason, one of the ones I hear people most saying they’d like to avoid. Personally I wouldn’t agree with that at all… although it does have a few down sides (which I’ll cover later in this post), it’s by far and away the best choice when joining large datasets together and should therefore be welcomed.

Help for Undocumented DBCC Commands
Produced: 27/11/2016 19:55:00
This episode of Fun with Flags is used in conjunction with my previous post about TF 3604.

Undocumented DBCC commands are written about all over the place and, although undocumented and therefore also generally unsupported, people seem to love them. I use them myself in all manner of places, never in production code, but they still get used. The biggest problem though, is knowing how to use them.

How Inserted and Deleted Tables Work
Produced: 20/08/2017 12:16:00
This is another post about Triggers, but in this one we’re going to focus more on the Inserted and Deleted tables that a Trigger has access to.

How Many Pages In My Table
Produced: 05/06/2015 18:48:00
This is the natural follow-up to my previous post about the 8k page. It’s all well and good knowing that SQL Server works in 8k pages and that these directly influence your read figures, but we don’t want to sit there and calculate how many records there are per page in order to know how many data pages exist in our table.

How to Create a Database Using T-SQL
Produced: 12/08/2013 10:26:00
IMPORTANT: The majority of the settings you’re about to see are actually taken from the system database “model”. The model database is, as its name implies, the “model” by which all others are created. Therefore if you change settings, initial sizes, growth rates, and recovery options in the model database, then those are the defaults you will be presented with when creating a new database from scratch following the method below. If you have some settings that you wish to apply generically to all databases, then simply change them on the model database.

How to Create a Database Using T-SQL
Produced: 12/08/2013 10:26:00
IMPORTANT: The majority of the settings you’re about to see are actually taken from the system database “model”. The model database is, as its name implies, the “model” by which all others are created. Therefore if you change settings, initial sizes, growth rates, and recovery options in the model database, then those are the defaults you will be presented with when creating a new database from scratch following the method below. If you have some settings that you wish to apply generically to all databases, then simply change them on the model database.

How to Create a Publication
Produced: 21/10/2013 16:27:00
This is generic guide as, to be honest, setting up all replication is pretty much the same. There are a couple of extra screens for Merge replication and I’ll highlight those where necessary. Otherwise it’s all the same.

How to Create a Table in SQL Server
Produced: 19/08/2013 19:00:00
There are a couple of ways of achieving this, the first is using SQL Server Management Studio as a “point and click” tool, and the other is using T-SQL code.

How to easily move a replicated database in SQL Server
Produced: 28/04/2014 14:48:00
This is a topic which I encountered myself a few months back and it drove me mad for a while, but the solution is actually incredibly simple.

How to Find Partition Range Values
Produced: 26/02/2017 18:19:00
This is something I struggled to put together the first time I needed it because partitioning uses internal tables with some very strange IDs and even stranger links between tables.

How to Fire a Trigger on Demand
Produced: 16/06/2014 15:14:00
Now, first of all, let me say that I’ve rarely found a good reason for needing to bypass a trigger or only wanting to fire one under certain circumstances (I have done this myself, but only in a cross site dual distributor replication using service broker… therefore not exactly a common requirement!!!), however, it is possible to achieve and surprisingly simple to do.

How to Move the Master Database in SQL Server 2008 R2
Produced: 10/03/2014 14:14:00
It's not a frequent occurence but times have arisen in which it's necessary to relocate the master database within your SQL Server. With SQL Server 2008 this is an incredibly simple operation and shouldn't cause you any problems.

How to Move the Model and MSDB Databases in SQL Server 2008 R2
Produced: 10/02/2014 17:38:00
This came to light when I was working with a company who were struggling with their MSDB database. They had a requirement to maintain huge volumes of historical job data and backup information and didn't want to archive this to another database. Therefore this meant a large MSDB which had filled their drive and they needed to relocate it.

How To Move The tempDB Database
Produced: 03/03/2014 12:45:00
New, faster disks arrived? Maybe you''ve done a reshuffle and ended up with a clean, unused physical drive? Either way your tempDB needs to be relocated. Doing so is actually incredibly simple and requires just a few short steps.

How to obtain the size of a specific Index
Produced: 21/08/2015 09:31:00
We know that sp_spaceUsed is a great way to obtain a few simple figures about a table such as row count, table size, and index size… but these are all cumulative. Therefore what happens if you want to know the specific size of an individual index?

How to Partition a Table
Produced: 05/02/2017 18:10:00
Partitioning is an Enterprise only feature and, if you happen to be lucky enough to be using Enterprise, it can be an incredibly powerful and useful feature.

How to Set Up a Linked Server Using SSMS
Produced: 25/11/2013 11:42:00
In environments in which multiple SQL Servers (or other databases such as mySQL) are present, there are many times in which server A could require data from a database on server B. In order to accommodate this scenario SQL Server presents us with the Linked Server.

How to Subscribe to a Publication
Produced: 04/11/2013 19:50:00
Once you have created your publication you will need to subscribe to it (otherwise it’s pretty pointless really).

How To Turn Off the New 2014 Cardinality Estimator
Produced: 04/12/2016 11:33:00
Before I start, I want to point out that I like the new estimator and it does have its advantages over the old one, BUT because it does produce different estimates in certain circumstances it can cause you to get completely different execution plans and therefore, as part of upgrade testing, you will likely need to turn it on and off, hence this post.

Huge tempDB Log File
Produced: 19/12/2014 16:27:00
I had an interesting problem appear this morning in which the tempDB Log file began to grow… and by grow I mean it went from just 10GB to well over 150GB in one morning and showed no signs of stopping.

Identity Management in Merge Replication
Produced: 17/02/2014 11:37:00
One of the most crucial and most complex stages in selecting the Article Properties of a table with an Identity column is how to utilise replication's Identity Range Management.

Incremental Statistics Performance
Produced: 26/03/2017 18:33:00
Based on my last post we now know how to create incremental statistics on a partitioned table and I mentioned that it was a massive improvement for performance of maintenance, but just how much?

Individual Partition Compression
Produced: 05/03/2017 18:23:00
To continue with partitioning there’s another good feature to consider which involves combining it with another Enterprise feature… compression.

Instant File Initialization
Produced: 13/11/2015 18:19:00
This is actually a Windows level setting, but something that is hugely beneficial to a SQL Server and should be enabled where possible.

Is SQL Server Installed?
Produced: 15/05/2015 09:21:00
This came about due to incredibly bizarre request I received which happened to throw me a little. Basically I was told by a business that they actually had no idea how many SQL Servers they had or on which machines they could be running. They asked if I knew a way to find them.

Is SQL Server Showing Memory Pressure
Produced: 31/07/2016 07:50:00
This is something I’ve never really been able to prove but have now found, courtesy of 2 VERY good posts (Grant Fritchey and Jonathan Kehayais respectively - links to follow), that it’s actually quite simple to track and monitor in order to ascertain whether or not your SQL Server is experiencing memory pressure.

Job History Timeout
Produced: 10/07/2015 09:37:00
This is actually an extension to my previous No Job History Showing post in which I pointed out how easy it is to end up without any job history in your SQL Server Agent and therefore not be able to diagnose problems.

Kill All User Processes
Produced: 22/01/2016 05:01:00
This is a quick and dirty bit of code which I’ve used on occasion when trying to put a database which is in single user mode back into multi user mode but which is accessed so frequently that all you receive is an error message stating you are the deadlock victim.

Lead and Lag Built In Functions
Produced: 06/11/2016 17:48:00
These aren’t new functions as they were brought in for SQL 2012, but I was using them the other day in a SQL 2014 environment and the person I was talking to at the time had never heard of them, so I thought I’d make mention of them here in case other people hadn’t heard of them either.

List of Datatypes
Produced: 09/09/2013 18:46:00
A datatype is exactly how it sounds... it defines the type of data. We use these all the time in our normal lives but may not even realise it.

Local Variable Gotcha
Produced: 24/10/2014 17:11:00
We all use local variables and in many cases they’re invaluable… however, there is a lesser known (in my experience anyway) gotcha with them which can badly affect your queries and leave you wondering why performance has unexpectedly dropped.

Looking for Inserts Updates and Deletes in the Transaction Log
Produced: 20/05/2016 10:56:00
This will form the basis of my next post but it needed covering separately in the first place… that being how you can see Inserts, Updates, and Deletes within the transaction log, and how you can tell which table they were performed against.

Memory Grant Affecting Query Performance
Produced: 27/02/2015 11:31:00
My last post about how much difference caching can make to a query left me thinking of other reasons that can cause a query to hand you a seemingly random set of execution times. This is one of the reasons I came up with… memory grants.

Memory Settings in sp_configure
Produced: 21/11/2014 14:27:00
Now this is a fun thing that I REALLY should have known, but have never really played with the therefore was mystified when it came up in conversation recently. I’ve asked a few other SQL guys and they were unaware of the true meaning of this setting either (which made me feel a little better). It’s not something I’ll be forgetting anytime soon though.

Merge a Partition
Produced: 17/09/2017 10:54:00
I’m going to briefly dip back into Partitioning for a few posts because this is something I was working on recently and I realized I didn’t have the code to hand on my blog… not that I rely on all code being present, but it’s definitely easier having something to hand than not.

Merge Operator
Produced: 07/11/2014 17:42:00
Continuing with the posts on Join Operators I’m going to move on from the Nested Loop join and give a very brief explanation of the MERGE operator.

Merge Replication Commands in Pipeline
Produced: 03/04/2015 11:38:00
Ever been in one of those situations in which you see endless blocking whilst replication makes new generations and you’re left wondering if there’s a specific table causing it or whether it’s just generic replication volume which is causing your problem?

Merge Replication Records Stuck with genStatus4
Produced: 24/03/2014 14:40:00
This is an issue I’ve only seen a few times in our system, but when it does appear the effects are very noticeable indeed.

Merry Christmas
Produced: 18/09/2016 08:52:00
Well, based on some relatively recent posts I’m pretty sure you can all guess what this will be… it’s Christmas and therefore we need a Christmas drawing!!!

Moving a Partition to a Different Filegroup
Produced: 01/10/2017 11:26:00
This is a very common thing to need to do and will put together the pieces of my last two posts into one large post in order to achieve the desired outcome.

Naming Constraints on Temp Tables
Produced: 06/11/2015 18:13:00
This post comes about due to my spending an annoying large amount of time on a code failure that turned out to be utterly infuriating, but ultimately interesting as well.

Nested Loop Operator
Produced: 31/10/2014 17:36:00
Join operators within SQL Server seem to be very misunderstood in general as people are forever telling me that they want a query to use this or that types of operator for the joins in their plan and they will even use hints to force these. Sadly it seems to come from a misunderstanding that certain types are better than others. This is not the case as they are all designed for a reason and each have their speciality.

New Arrival
Produced: 11/03/2016 12:08:00
So I’ve been a little quiet on here recently, well there’s a good reason… 3 weeks ago today I became a dad for the first time and therefore I’ve been a little too tied up to keep my website up to date.

Nicely Formatted HTML Email of SQL Table
Produced: 10/10/2014 16:49:00
Okay, in my last post I showed you how easy it is to include a table of results in an email, but by all accounts it was a little bit bland when produced. So in this post I’ll deviate a little from SQL Server to give you some HTML which will turn this:

No Job History Showing
Produced: 26/06/2015 22:58:00
This is a common complaint I hear whenever I see a new server for the first time… I see a failed job, go to open the Job History, and am told “oh, there’s never anything in there. Why doesn’t SQL ever hold enough?”. Well, the answer is that it simply hasn’t been told to.

Numeric DataTypes
Produced: 02/09/2013 09:50:00
I will presume that you know the most common numerical datatypes (if not, please refer to the list here).

Obtain Job Name from sp_who2
Produced: 03/07/2015 23:25:00
This is a personal pet hate of mine and of many people I’ve spoken to… we have all these lovely tools to obtain a list of what’s happening on our SQL Server but invariably sp_who, sp_who2, and sysprocesses all find it highly entertaining to provide you with a seemingly encrypted result instead of a job name.

Obtain Start of Day from current_timestamp
Produced: 23/01/2015 17:07:00
This is genuinely one of the things I’m most asked, and by all manner of people from DBAs to Devs because it’s never as simple as it looks and that is how to obtain the start of day from a current_timestamp (or any other datetime value).

Obtaining the Max Values Across Columns
Produced: 30/10/2016 17:45:00
In some code I was writing recently I needed to perform this action which, having had a quick look on the internet, seems to be a topic that annoy people as it’s not something natively built in to SQL Server and to obtain it yourself can be a pain.

Online Partition Index Rebuild
Produced: 12/03/2017 18:26:00
Yet more partitioning stuff… this time it’s the fact that we can rebuild an index on just a single partition of a table… and online. Now that’s handy!!!

Only Update Stats You Need
Produced: 03/09/2017 12:22:00
I’ve had a LOT of issues with stats over the years, mostly due to ascending keys, and I’m not the only one. However, the way in which I see people try to deal with these issues is quite interesting.

Optimize For Ad-Hoc Workloads
Produced: 16/10/2015 09:18:00
Since finding out about this option it has been an sp_configure setting that I’ve generally recommended everyone turns on within their SQL Server.

Part Populating Temp Tables
Produced: 14/08/2015 19:43:00
The other day I was going through some code looking for a reduction in reads (as they were rather higher than expected), and whilst doing so I came across a piece of code that seems logical yet is actually quite a performance killer.

Partition Elimination in Action
Produced: 12/02/2017 18:13:00
There are many reasons to use partitioning in order to make maintenance easier (stats and reindexing) and allowing for partition switching (fast loading and deleting of data), but there is another which is Partition Elimination.

Passing a Table to a Stored Procedure
Produced: 16/10/2016 11:47:00
This is a direct follow on from my previous post really as one of the most common uses for the Table Type is that you can use it (in SQL Server 2008 onwards) to pass a table as a parameter into a Stored Procedure. This is incredibly useful as one of the things that I see most often asked on the internet is how to pass tabular data to a stored procedure.

Split a Partition
Produced: 24/09/2017 11:22:00
Cleary this is a direct follow-up to my last post in which I showed how to merge a partition… this is the exact opposite in which we’ll split a partition into 2.

SQL 2016 Install Sets Multiple TempDB Files
Produced: 07/05/2017 16:10:00
This is something that I’ve been hoping would make an appearance in SQL Server at some point, and also why I included my last post about tempDB contention. It’s the ability to choose the number of tempDB files during install rather than having to add them yourself at a later date.

SQL Column to Comma Separated List
Produced: 26/09/2014 17:57:00
This is a handy little “trick” which I use all the time when I need to turn the column of a table into a comma separated list. It’s incredibly quick, has very little overhead, and it’s effective.

SQL Server "Distribution clean up: distribution" Job Failing Large MSRepl_commands
Produced: 17/03/2014 16:04:00
This was something I came across when working in a heavily replicated environment… the replication created job “Distribution clean up: distribution” suddenly began to fail constantly. This led me on a trail which had such drastic results that I felt compelled to document it.

SQL Server 2008 backup compression... instance level or not?
Produced: 07/04/2014 16:34:00
Initially I would have said to set this within sp_configure because although it is CPU intensive, I have always found that the cost is more than covered by the level of reward.

SQL Server 2008 R2 Installation Guide
Produced: 18/11/2013 14:01:00
Here I will run through a basic installation of SQL Server 2008 R2. This does not include Analysis Services or Reporting Services as they are out of the scope of this article. This literally covers the basic install and key settings that need to be considered prior to running SQL Server for the first time.

SQL Server 2008 Swapping Database Names (sp_renameDB Bug)
Produced: 14/04/2014 19:08:00
This is a bug I found within SQL 2008 (and R2) which has caused me endless problems within my system. I now have a workaround, but it’s clumsy and I really don’t appreciate having to do it.

SQL Server Agent Job Ownership
Produced: 07/08/2015 19:57:00
I’ve come across a few issues recently with regard to the ownership of SQL Server Agent jobs which caused some scheduled tasks to fail and therefore critical maintenance tasks weren’t carried out. Therefore I thought I would share my thoughts on Job Ownership.

SQL Server AUTOCOMMIT within a while loop
Produced: 24/02/2014 13:15:00
This is just a little tip in case you ever have to make a lot of updates or inserts using a while loop and don’t mind a short amount of blocking while you do it.

SQL Server Trigger Examples
Produced: 13/08/2017 12:15:00
As mentioned in my previous post, we have 2 types of Trigger available in SQL Server… AFTER and INSTEAD OF.

In this post I’m going to go through a few examples to show syntax and a couple of uses.

SQL Server Triggers
Produced: 06/08/2017 12:10:00
Triggers are kind of like stored procedures… they’re simply pieces of code that can execute based on another event… ie. They’re “triggered” by another event.

SQL Table of Results in an HTML email
Produced: 03/10/2014 16:44:00
To be honest I couldn’t think of a good title for this particular post so I’ve gone with the above. Basically what I’m referring to is that sometimes a simple text email isn’t enough and you want to include some output with it… therefore what you need is a table.

SQLSkills.com Immersion Event
Produced: 30/10/2015 18:06:00
I’ve been wanting to attend one of these ultimate training courses from Paul Randal and Kimberly Tripp for a long time now and finally I managed to get the time off, the money together and book myself on.

Therefore at the beginning of this month I was out in Chicago partaking in some of the most intensive, yet enjoyable, SQL training money can buy. (Aside from my own, of course :o))

SSIS Basic Package
Produced: 28/08/2015 10:46:00
I’ve been using a lot of SSIS recently and whilst doing so I figured I should really add a few basics to the blog in case anyone either hasn’t used it before or simply hasn’t used it for a while and wants a few pointers.

SSIS Dynamic String Connection
Produced: 11/09/2015 14:44:00
This is something that’s really useful to know. It threw me for a little while when I first thought of doing it, but now it’s something I use quite frequently and to good effect. It’s really simple to set up once you know what you’re doing.

SSIS Pass Variable Between SQL Tasks
Produced: 04/09/2015 11:32:00
This is something that I found tricky when I first started to work with SSIS packages and something that I regularly see asked on the internet, therefore I figured it was something worth documenting.

Statistics Affecting Query Performance
Produced: 06/03/2015 11:36:00
Another way in which query performance can suffer is down to statistics. These are the numbers and mathematical information that SQL Server holds in regard to indexes and tables. By using these SQL Server estimates the number of rows a query will return at each stage of the execution plan.

Statistics On Views Via Linked Server
Produced: 02/07/2017 17:51:00
As a follow-up to my last post about accessing synonyms via a linked server (you can’t), I stated that it was therefore better to use a view. However, that has issues of its own to consider.

Table Types
Produced: 09/10/2016 11:42:00
This was something which I used recently within my own personal work at home because I was writing a stored procedure which involved regularly creating tables with the same structure.

Table Value Variables
Produced: 30/12/2013 12:06:00
As with single value variables, these are very simple and powerful objects which are also only active and present during the execution of your code. They are automatically dropped and tidied up once execution finishes.

Temp Table Caching
Produced: 07/08/2016 08:09:00
I had a discussion with someone the other day who stated that all temp tables should be dropped at the end of a procedure because otherwise they never get cleaned up.

I could understand the statement, but it’s not actually how SQL Server works because within a stored procedure temp table caching generally makes dropping redundant.

Temp Tables vs Variable Tables
Produced: 19/05/2014 16:39:00
This is possibly one of the most contentious issues I’ve come across in SQL Server. If you look online then there are numerous people arguing numerous aspects of these two types of table and weighing the benefits and pitfalls of each. This includes where they’re stored, how they’re used, which is best etc.

Test for Compression Savings
Produced: 29/01/2017 19:21:00
This is actually an addendum to my last post as I referred to using SQL compression and provided a script in which you could compress a set of tables.

However, the problem is knowing which tables you should compress?

The 8k Page
Produced: 22/05/2015 10:25:00
This will be a relatively short post simply explaining the way in which SQL Server stores databases records on disk. The reason that I’ve included this post is because I’ve come across a good few people who didn’t realise this was how SQL Server worked and yet this is an important precursor to my next few posts.

The Awesomeness of Incremental Statistics
Produced: 19/03/2017 18:29:00
I did a post a while ago about why you need to be careful with Sampled Statistics and therefore why a FULLSCAN is really the only reliable way to go… BUT there has always been the problem that a FULLSCAN update can take a VERY long time.

Oh, and yes… this is also to do with partitioning!!!

The Best Way to Obtain Percentages
Produced: 25/03/2016 14:17:00
Again, this is something that I decided to write about because I see code in a lot of places used in a lot of different ways and within most MI there is always a need to obtain a percentage figure across a dataset and, for some reason, no-one seems to use SUM with OVER but rather they choose an older, longer method.

The MERGE Statement
Produced: 26/05/2014 11:07:00
So you’re working with some data and maybe you have another table which has newer, updated data... how do we combine these two tables to create one, correct, dataset?

Produced: 12/02/2016 05:09:00
I’ve done a couple of posts about RAISERROR, but if you’re using SQL Server 2012 and above then there’s a new syntax also available which I think is a little better. That syntax is THROW.

Tracer Tokens
Produced: 01/01/2016 07:42:00
If you’re using Transactional Replication then these can be invaluable to you. These are incredibly lightweight, easy to use, and they will help you with all manner of reporting and troubleshooting within your replicated environment.

Transaction Log Tracking Inserts Updates and Deletes Over Time
Produced: 27/05/2016 11:21:00
Have you ever wanted something which will reliably keep you informed as to the number or Inserts, Updates or Deletes against table in your system? Maybe to keep an eye on the volume of modifications happening in a replicated environment?

Transactions and Table Variables
Produced: 14/08/2016 19:53:00
Now, anyone who’s read enough of my blog will probably know that I’m not too fond of the Table Variable as I see it being used too often and generally in a way that it’s not designed.

Unable to begin a Distributed Transaction
Produced: 05/05/2014 18:13:00
This occured on a client's machine after they had migrated their SQL Server to new hardware with a fresh install of Windows Server 2008 R2. They attempted to use a linked server and received the following error message:

Unique Index with Union All
Produced: 29/04/2016 09:36:00
This follows on directly from the previous post in regard to ensuring that you use UNIQUE if possible on an index as I’ve also found that not having UNIQUE can make quite a hefty difference when using UNION ALL.

Updating Varchar Column Values In Replicated Tables
Produced: 02/10/2015 08:01:00
This is just a quick post as it’s something I came across the other day and I thought it warranted a note and word of caution.

Produced: 06/05/2016 09:43:00
This is another new function in SQL Server 2012 which is incredibly useful and I’ve been using everywhere I can in place of the previous ISNULL or COALESCE functions.

Using Duration in Profiler and SSMS for Query Tuning
Produced: 17/10/2014 16:57:00
This is a quick post regarding a couple of things I found someone doing the other day when using Profiler and SSMS to tune one of his queries. Basically he was getting frustrated because he had been making changes to his code which he believed would speed up his query and yet he wasn’t getting anything consistent out of either SSMS or Profiler to prove this.

Using OPENROWSET instead of a Linked Server
Produced: 28/07/2014 17:30:00
Ever been writing some code and found that you need some data from another SQL Server instance? You have security access to the other instance so what do you do?

Well you could easily use a linked server to obtain it, but what if there is no linked server set up and you lack the rights to (or maybe there’s a company rule forbidding) setting one up?
Produced: 05/02/2016 05:07:00
Now we’ve used RAISERROR (as per my previous post) we need to address a specific issue which RAISERROR has and which we need to overcome in order to make this more useful. That would be that issue that SQL Server doesn’t show messages until the very end of execution. This will fix that issue.

Using sp_executeSQL with Variables
Produced: 26/02/2016 05:29:00
When looking through other people’s code I tend to find that people struggle with dynamic SQL and how to write it in the best way. Specifically when it comes to using variables within the dynamic string.

Vertical Partitioning To Reduce Reads
Produced: 12/06/2015 18:51:00
Directly following on from my posts about Reads and the 8k Page, Vertical Partitioning your tables can suddenly make a lot more sense and it’s very quick and easy to see how vast improvements can be made to the speed of your queries and their impact on your disks.

Vertical Partitioning Using sp_tableOption
Produced: 27/11/2015 09:48:00
This was an incredibly cool thing that I learnt the other week whilst attending a SQLSkills course. I have previously made mention of Vertical Partioning and how useful it can be, BUT it does have the downside of re-architecting… this can change that.

Wait Stats Per Query
Produced: 23/10/2015 09:35:00
This was something that I have wanted to be able to do for a while. I like having wait stats to look at (I’ll do a blog about those at some point - this is for people who already know what they are)… but until Extended Events came about these were only available, to my knowledge, at the server level. This was great, but what if you wanted to know what your specific query was doing?

What a Difference Caching Makes
Produced: 20/02/2015 11:17:00
I was visited the other day by an irate developer who was complaining that he couldn’t get consistent results from his testing and queries were running in anything from 5 to 30 seconds and he thought there was something wrong with SQL Server.

What Are Extended Properties
Produced: 09/07/2017 11:50:00
Extended Properties are a feature of SQL Server which are, in my opinion at least, massively underutilized and should be embraced a lot more frequently.

What Are Filegroups
Produced: 14/05/2017 16:13:00
Well, let’s be honest here… in a nutshell a Filegroup is… wait for it… a group of files.

Bet you didn’t see that one coming.

What is a Key Lookup?
Produced: 28/05/2017 22:12:00
I was asked this the other day and it’s actually something that I’m surprised hasn’t come up before as people all seem to know it’s a bad thing, but many people don’t know why and don’t even know what one is in the first place.

What is a Read?
Produced: 29/05/2015 12:12:00
I’ve spoken to a good few people now who didn’t actually know what a Read was inside SQL Server. They were discussing tuning of queries and, correctly, knew that less reads were advantageous and therefore that’s what they were looking for in a better performing query, but didn’t really know why.

What Statistics Are Being Used
Produced: 20/03/2015 17:18:00
You’re running a query and you’re looking at the execution plan… it’s all well and good, there are tables, indexes, cardinality estimates… all manner of information. We know these are all derived from statistics and that our statistics should be kept up to date. But the question is… exactly which statistics were used or considered by the optimizer?

This is where these trace flags come into their own… they will give you just that information.

Which Columns Are Being Updated
Produced: 26/12/2014 10:14:00
This came about as a result of a series of mass updates being applied to a replicated table. This series of updates was causing replication to lock up and become so far behind that the only acceptable course of action was a full rebuild. This is by far and away the course of action I least like taking, but in this example it was unavoidable.

Which Table or Index is Hogging the Buffer Pool?
Produced: 25/10/2017 14:37:00
In the last post I showed how we can easily tell which database is hogging our precious resources but, although that’s useful in helping us narrow down rogue processes, it would be a lot more helpful to be able to go a step further, picking out the exact table or index.

Missing Index Information
Produced: 01/02/2018 19:51:00
Now, this post is one that I’ve had in the pipeline for quite a while but I’ve always been VERY reluctant to write it… why, I hear you ask… well it’s because it’s incredibly dangerous…

What’s Running on my SQL Server?
Produced: 10/02/2018 12:13:00
I’d be shocked if anyone reading this blog post hasn’t heard someone in their company saying the following… “SQL Server is slow… what’s running on it?”

Plan Cache Hunt
Produced: 20/02/2018 03:35:00
This is a follow-up to a previous blog post entitled “Which Table or Index is Hogging the Buffer Pool?”… inventive and exciting title, I know…

How to Clear the Buffer Pool
Produced: 01/03/2018 19:54:00
Since my last post I’ve had a question emailed to me which I just wanted to take a second to clarify as it’s a very good question and one that, I imagine, a lot of people have had or might not fully realise.

Clean and Dirty Pages
Produced: 12/03/2018 15:25:00
In an extension to my previous post about using CHECKPOINT in conjunction with DROPCLEANBUFFERS I thought I would give an example to highlight the situation.

Semi Joins – SQL Training Question
Produced: 19/03/2018 21:34:00
This was something which came up in one of my online courses recently… everyone was fine with all the main join types that appear in SQL Server both syntactical (inner join, left join, right join, full outer join) and internal (nested loops, merge, and hash), but a question arose surrounding some of the joins that you can only see listed on an execution plan and do not specify yourself.
In this article I’ll quickly cover one of those… Semi Join:

Anti Semi Join - SQL Training Question
Produced: 28/03/2018 13:49:00
This is related to my previous post about the Semi Join and came up under the same context. But this time we’re looking at the Anti Semi Join.

ANY and SOME - Lesser Known T-SQL Keywords
Produced: 07/04/2018 09:01:00
This was something else that came up in training and I can’t, for the life of me, remember how and why, but we seemed to end up on the topic and this came out. I thought I’d write a little blog about them just because I was amused (doesn’t take much) by how few people knew these even existed in the T-SQL language.
So what are they? Well, they’re basically the same as EXISTS, just a different syntax:

Fun With Flags - What’s the Optimizer Doing?
Produced: 17/04/2018 16:43:00
This is quite a fun episode of Fun With Flags because I like poking about inside the Optimizer to see what’s really going on. Doesn’t mean I can influence it… but it can be quite enlightening to have a poke about inside and see what bubbles up to the surface.

Where Exists - Quick Tip
Produced: 25/04/2018 08:26:00
Yet another question from one of my training courses (a very useful and fun way to obtain blog topics) was surrounding the difference between the following and whether one or the other should be being used:

Using In-Memory Temp Tables
Produced: 17/06/2018 10:40:00
Following on from the few posts I’ve written about in-memory tables I wanted to mention this useful tip you can use in order to potentially speed up your temp tables whether that be within stored procedures or maybe within an ETL process.

What's Using the Buffer Pool?
Produced: 18/10/2017 11:09:00
We all know that SQL Server stores data in memory in a place it calls the Buffer Pool (and if you don’t, then speak up and I’ll explain) but, for a lot of us, that’s where our knowledge ends.

How Effective Is Data Compression?
Produced: 01/11/2017 07:07:00
In this day and age with such a prolific speed in processor it’s relatively rare (although not unheard of by any stretch) that I hear people saying that they don’t have the CPU resources to run compression on their databases… but what I do regularly hear is people asking what space saving benefits it will give them.

New Website Lauched
Produced: 07/01/2018 15:35:00
This is obviously not a SQL Server based post, but it does explain the lull in blogs over the last few weeks… basically I realised that my website was a little outdated and things had grown to the point where I needed a web presence which required an actual skillset to produce and design rather than my feeble efforts with CSS.

Which Filegroup Holds My Data?
Produced: 15/01/2018 23:46:00
Well… now that I’ve got a shiny new website (see previous post)… it’s back to the task of posting about SQL Server again…

How Big Are Our Tables?
Produced: 25/01/2018 10:07:00
This is another query that I’ve been asked… although I have to admit that I’m a little confused as to why because there’s a built in report that achieves the same result… but I’ve never shied away from a challenge, so I was happy to oblige.

A Foreign Key Optimisation
Produced: 04/05/2018 15:57:00
I’ve had so many arguments surrounding foreign keys and why they’re there, whether they can be ignored and avoided and removed etc. Personally I’m a fan for the simple fact that I like a proper database with proper referential integrity enforced. But for those who think that’s not important, there are other reasons too and this happens to be one of them:

What is In-Memory OLTP?
Produced: 12/05/2018 09:48:00
This post has been a long time coming as this was a feature added in SQL Server 2014, just over 4 years ago now. Despite having learnt the ins and outs of the feature at the time and thinking it was a potential great leap forward, facts are that I rarely ever used it in any live situation, therefore other posts always came to the fore instead (as it’s a lot easier to knock up demos of features you’re currently using than trying to invent something completely from scratch with no use case).

In-Memory Filegroup
Produced: 21/05/2018 04:15:00
I didn’t know whether to place this in its own post or not, but in the end thought it was wiser than hiding this inside another, loosely related, post…

Creating an In-Memory Table
Produced: 30/05/2018 16:22:00
You know what in-memory tables are, and you have your in-memory filegroup, so now you need to know how to create one…

Indexing In-Memory Data
Produced: 09/06/2018 22:49:00
This is a tricky topic because indexing an in-memory table isn’t the same as indexing a disk-based table. With disk based tables we have clustered or nonclustered indexes to work with and these are the indexes we all know and love. They simply order the data we’re interested in and therefore we can easily understand how SQL uses them to seek and scan. However, this is very much not the case with in-memory indexes.

Row Level Security Example
Produced: 04/08/2018 02:57:00
This follows on from the last post in which we discussed Row Level Security. This post provides a nice and simple example based on the example proffered in the previous blog post.
We have the following query:

Easily Testing User Permissions
Produced: 14/08/2018 20:57:00
This is a quick post just to highlight a simple concept that many people don’t know exists.
Every DBA should be implementing tough security rules and permissions across their estate, but this can be very hard to do without having the ability to test any security amendments you may be making.

Column Level Security Examples
Produced: 24/08/2018 07:22:00
I’ve done a few posts about Row Level Security so I thought I would make one about Column Level as well as, many times, people want to restrict access to columns as well and believe the only way is through re-architecting their tables or hiding everything behind a view layer.

Greyed out Column Permissions in SSMS
Produced: 31/08/2018 21:30:00
I’ve had this question a few times now so I thought I would follow up my last post on column permissions with this little note about the SSMS implementation.
Basically, I’ve had people ask “Why can’t I add Column Permissions in SSMS? It’s always greyed out.”. This is what they’re talking about:

Slowly Changing Dimension Designs - Basic Date Range
Produced: 10/11/2018 10:51:00
In a previous post I went through what a Slowly Changing Dimension is and how it can be a pain to all and sundry when designing a database. Before I move on to implementation in regard to code, I just wanted to touch on a few design thoughts in regard to the tables themselves as there is more than a couple of ways to store a slowly changing dimension. There’s no right or wrong, therefore these are just a few ideas to consider depending on what you feel suits you best or you’re most comfortable with.

Slowly Changing Dimension Designs - Date Range with Parent
Produced: 21/11/2018 00:47:00
In a previous post I went through what a Slowly Changing Dimension is and how it can be a pain to all and sundry when designing a database. Before I move on to implementation in regard to code, I just wanted to touch on a few design thoughts in regard to the tables themselves as there is more than a couple of ways to store a slowly changing dimension. There’s no right or wrong, therefore these are just a few ideas to consider depending on what you feel suits you best or you’re most comfortable with.

Slowly Changing Dimension Designs - History tables
Produced: 01/12/2018 16:34:00
In a previous post I went through what a Slowly Changing Dimension is and how it can be a pain to all and sundry when designing a database. Before I move on to implementation in regard to code, I just wanted to touch on a few design thoughts in regard to the tables themselves as there is more than a couple of ways to store a slowly changing dimension. There’s no right or wrong, therefore these are just a few ideas to consider depending on what you feel suits you best or you’re most comfortable with.

New Blog - C #
Produced: 24/09/2020 16:46:00
Throughout my career I have spent a lot of time with C# in different guises. For example, I troubleshoot SQL Server issues by tracking C# applications which reference it, and I have written this website in C# asp.net (yes, I'm informed that MVC is better but I needed a website and wanted to stick to what I know).

It was in the latest re-working of my website that I realised I wished I had noted down a lot of the things I've learnt along the way and, with that in mind, I decided now was the time to expand my blog to encompass other areas.

New Blog - PowerShell
Produced: 24/09/2020 16:46:00
PowerShell was something I didn't really give much consideration to when it first arrived on the scene. It seemed a little bit of overkill compared to existing tools and required a whole new language and way of thinking (pipes versus processes).

For this reason I now have to apologise to PowerShell and point out that I vastly underestimated it as a technology and now I find myself more and more frequently turning to it when I need to get jobs done.

Therefore I figured it needed its own blog.

A VERY Brief History of PowerShell
Produced: 02/01/2021 07:07:00
As a note, this will not be a large discussion around why PowerShell exists and whether it should or shouldn’t exist, how performant it may or may not be etc. I’m literally just going to explain why I use it, why I think it’s worth investing some time in, briefly why it exists, what it can do, and how and why you, likely as a SQL Server professional, should consider reading some of these blog posts.
So firstly, most SQL Server professionals who have been around a good few years (I’ve been around more than I care to admit) have, at some point or another, had to fall into the clutches of MS-DOS. This was, for me at least, always a painful process.

PowerShell vs PowerShell ISE
Produced: 08/01/2021 10:51:00
PowerShell has provided us with 2 methods of interaction; a basic console, and the ISE (Integrated Scripting Environment). Personally, I rarely use PowerShell in the console only form, preferring to use the ISE at all times.
With that in mind, I’m going to show them both, but I will focus on the ISE in terms of how I set it up for my coding (you may prefer a different look, but this will let you see how I do it and therefore adapt it to your own preferences).

Linux SQL Server on Windows with Docker
Produced: 28/01/2021 14:39:00
First things first, we need to get started. This means downloading and installing Docker. Only then can we start to get to work with SQL Server on Docker.

Luckily there are guides for this from the Docker website itself and therefore I’ll leave that detail up to them.

Connecting a GUI to our SQL Server
Produced: 31/01/2021 16:10:00
In the previous blog post we walked through getting our SQL Server 2019 Docker Image and booting up our Container.

This gave us the Linux version of SQL Server 2019 on our Windows Desktop.

However, no-one likes using SQLCMD for everything, so we’ll want to connect a GUI.

Installing PowerShell Core on Windows
Produced: 20/03/2021 06:01:00
If you’re thinking of dipping your toe into PowerShell Core to see what it’s about, how it works, and what it does (it’s basically the same as PowerShell 5.1 except cross-platform as discussed in my previous post), then you need to install PowerShell Core.

Writing to the Console
Produced: 15/01/2021 07:51:00
When you start testing and writing code in PowerShell, one of the most useful things you can do is to return data to the console in order to see what’s happening, what you’ve done etc and keep track of your code as it runs.
PowerShell provides this in a very simple way and, unlike ECHO in DOS, PowerShell provides us with some formatting too, which can make your code look nice and friendly to both testers and developers.

Downsides to In-Memory Tables
Produced: 24/06/2018 18:28:00
Had I written this post just a few versions ago when in-memory was released as part of SQL Server 2014 then this would have been a very long post indeed. However, I’m pleased to say that, just a few iterations later, this feature has been improved considerably and is definitely something I now consider when looking to re-architect or tune an environment.

Quick Note About Backup File Extensions
Produced: 03/07/2018 00:53:00
This was something I was asked about the other day which I’d not really given any thought to but which was causing others a great deal of confusion…
We’re all aware that the default file type for a SQL Server backup file is “.bak”, but does it have to be?

Column Compression? Compress and Decompress
Produced: 13/07/2018 16:43:00
This was something which came up recently when discussing storage of XML columns within a SQL Server database and the large amounts of space it can take up. Most of us know about the compression which has been around for years now, those being Row level and Page level compression…

Basics of Row Level Security
Produced: 24/07/2018 08:31:00
As with most other people I know, I have been working with several companies lately around security in the wake of the GDPR law changes. As an offshoot of this I figured I would write a few posts surrounding some of the data security features within SQL Server that I’ve been playing with. The first of these is Row Level Security.

Parameter Sniffing in SQL Server
Produced: 16/02/2019 13:17:00
This is something I was sure I had written about before, but it turns out I haven’t. I’m also aware that there’s a lot of information out there on the topic, but I wanted to post about Adaptive Query Joins and this is a significant precursor, which is why I’m putting this blog out first.

Adaptive Query Joins
Produced: 24/02/2019 17:45:00
This is a new feature added into SQL Server 2017 to try and alleviate the pain of an age old problem… parameter sniffing.
For anyone who doesn’t know what this is, I have outlined the problem with a couple of examples in the previous post, therefore have a quick read through… but if you’re already aware of the problem then read on…

Window Functions in Batch Mode
Produced: 04/03/2019 09:04:00
We all love Window Functions. Where would we be without ROW_NUMBER and RANK? But these can sometimes be performance killers as they loop through our data performing sorts and scans as they go.
Now, in SQL Server 2016+, there is a Batch Mode version of Window Functions and the improvement is huge.
So let’s have a look with a couple of quick examples:

Row Level Security on In-Memory Tables
Produced: 08/09/2018 15:19:00
Quick note to combine the last few sets of posts… can you use Row Level Security on in-memory tables?

Produced: 17/09/2018 08:47:00
This is a fun one because I’ve seen numerous people struggling with identity columns in temp or freshly generated tables.
For example, let’s take a look at the Person.Person table in AdventureWorks:

SQL Server Login versus SQL Server User
Produced: 26/09/2018 23:44:00
In a good many of my demo scripts I create Users with the caveat “without Login” which led to someone asking me the other day what the difference is in SQL Server between Logins and Users. This was actually asked via text which resulted in a somewhat stunted answer but, in essence, an accurate one and therefore I’m going to keep this relatively short as well…

Create User without Login
Produced: 05/10/2018 09:23:00
Following on from my last post in which I explained the difference between a Login and a User, I’m going to expand a little and explain the concept I use quite a lot within some of my posts… the “login-less user”.

Create a Login with no User
Produced: 12/10/2018 20:22:00
Continuing the theme of Users and Logins, I was asked if there was a circumstance in which you would ever require a Login without an affiliated User? The answer is yes, and it’s more common than you think.

Paging using Native T-SQL Commands
Produced: 22/10/2018 13:07:00
One of the most common requirements within websites and other application is the ability to page results. This used to be an onerous task and, in many cases I’ve seen, people have tended to simply pull a full dataset from SQL Server, cache the results in the application, and then let the application itself page out the results accordingly. However, there is an easier way to do this directly from within SQL Server without using complex code.

What is a Slowly Changing Dimension?
Produced: 31/10/2018 02:05:00
In all databases, data mart architects and developers have inevitably been faced with the one peril we all wish was easier… Slowly Changing Dimensions.

Slowly Changing Dimension Designs - Temporal Tables
Produced: 12/12/2018 06:59:00
In a previous post I went through what a Slowly Changing Dimension is and how it can be a pain to all and sundry when designing a database. Before I move on to implementation in regard to code, I just wanted to touch on a few design thoughts in regard to the tables themselves as there is more than a couple of ways to store a slowly changing dimension. There’s no right or wrong, therefore these are just a few ideas to consider depending on what you feel suits you best or you’re most comfortable with.

Slowly Changing Dimension Implementations - Synchronous Triggers
Produced: 22/12/2018 15:23:00
In the previous post I went through what a Slowly Changing Dimension is and why they can be such a problem for people when designing database solutions. Here I’ll start to work through a few implementations. Note there is no real “this is the way to go” solution because each has its advantages and pitfalls and needs to be considered carefully by each architect for the design at hand but, hopefully, knowing a few solutions will mean that you can try and few and find one that you’re most comfortable with.

Slowly Changing Dimension Implementations - Asynchronous Methods
Produced: 30/12/2018 21:42:00
Finally I think I’m done with the topic of Slowly Changing Dimensions for the moment. This doesn’t mean this is all you need to know about them, or that I’ve covered every angle and consideration (I haven’t), but this should now suffice for most people to make an informed and considered decision as to when to use them, how to use them, and how best to populate them.

Window Functions - Row Mode Performance
Produced: 13/03/2019 19:11:00
My last post was on the wonders of Window Functions in Batch mode (which is awesome – if you’ve not read the post then go… now…). This post will focus on any of us who don’t happen to have the latest and greatest (at time of writing) and aren’t able to force batch mode through our queries.
So, what’s the big deal? Window Functions are just Window Functions? Yes?

New Blog - Docker
Produced: 24/09/2020 11:54:00
This is a technology I'm in the process of learning and therefore, whilst it's fresh in my mind, I wanted to start a blog in order to document my findings and create a kind of walk through.

Docker is the newest technology in which SQL Server has adopted and I wanted to get in somewhere near the ground floor (not on the ground floor by any stretch, but as long as I'm vaguely near then I'll be happy).

What Are Temporal Tables in SQL Server?
Produced: 07/01/2019 13:00:00
Temporal Tables (also known as system-versioned temporal tables) are essentially tables which return data based on a specific point in time.
These are ideal for use with slowly changing dimensions in data warehouses, or simply if you wish to easily and simply keep a queryable history of your data for audit or reporting purposes.

Creating a Temporal Table in SQL Server
Produced: 15/01/2019 06:06:00
Continuing from the previous post, which was a brief introduction to Temporal Tables, we’ll now move on to creating one and seeing what special syntax is involved in that process.
To be honest this tends to look a little confusing when you first see it, but it very quickly makes sense and you can start using it right away.

Querying Temporal Tables in SQL Server
Produced: 22/01/2019 06:22:00
Again, following on from my last post, we’ll be looking at Temporal Tables a little more. Last time we simply created one and entered a row of data whereas this time we’ll be looking to query them a little more and see what syntax is required to do so.

Temporal Tables – Notes and Gotchas
Produced: 31/01/2019 13:37:00
Here we’ll go through a few of the quirks of Temporal Tables, both things that I’ve been asked most frequently, and also some of the known considerations when looking to use these in your environments.

Temporal Tables – Editing Historical Data
Produced: 08/02/2019 10:19:00
We all know that Temporal Tables don’t allow us to edit data in the Historical table. This is for all manner of incredibly sensible reasons (auditing etc) and therefore shouldn’t be breached.

Splitting a Table Full of Strings into Columns
Produced: 11/03/2021 09:52:00
This was a fun afternoon I spent getting this working nicely. I don’t know if there are better ways of doing this, but if there are then the internet isn’t the place to look because if you type in string split you’ll get a whole bunch of pages about splitting a string… but just an individual string.

What Is Docker?
Produced: 15/01/2021 07:11:00
Before we start using Docker we should really know what it is and why we might like it.

Now I’m no Docker expert and I don’t like any of the explanations I’ve seen around the internet, therefore I’ll just give my own version as to what I think it is and why it’s useful. Mostly focusing on the latter.

Change Image and Container Location for Windows
Produced: 21/01/2021 18:47:00
By default Docker will download and store your Container images on your C drive. This is less than helpful on most Windows machines (even desktops).

Therefore, we want to avoid this if possible, placing them on a drive and location of our choice.

Importing Data into a Linux Container
Produced: 03/02/2021 06:15:00
I’ve specified Linux Container here although if you follow this then it’s also pretty easy to do the equivalent in a Windows Container. But I’m currently working through my Linux Container posts and therefore that’s what we’re sticking with.

So, the main point of this post is that we now have a Linux SQL Server up and running on our Windows Desktop but it’s of little to no use if it has no data for us to play with.

Stop, Start, and Remove Containers
Produced: 05/02/2021 09:22:00
I’ve covered a lot of the creation and usage of a Container, but it’s very unusual that you’d want to create one, get it running, and then leave it that way.

Generally you want to stop and start the Container on demand, and eventually you’ll want to remove it entirely.

Therefore let’s have a look at that.

PowerShell Core - What is it and Why
Produced: 18/03/2021 09:04:00
I’ve been asked this a lot by people recently and I can understand the confusion because it seems that Core is everywhere and yet casual programmers and developers (who, as a SQL guy, are those I tend to deal with) seem unsure what it is and why it exists, yet are being told that they should be using it.
Hence I thought I’d just put a quick note in the PowerShell blog about Core even though it’s not a PowerShell exclusive thing, it’s everywhere now, but this is the blog in which it fits best.

PowerShell Core ISE (Visual Studio Code)
Produced: 22/03/2021 10:41:00
Now there’s a clue in the title on this one. There is no ISE for PowerShell Core. This is because the ISE was a Windows thing and the whole point of Core is that it’s Cross Platform and therefore they haven’t tried to build in a specific ISE that will work for all OS variants.
However, there’s always an alternative and we have one in the form of Visual Studio Code, our friendly cross platform multi-language supporting application.

Performance Dashboard
Produced: 10/04/2015 18:09:00
This is an incredibly valuable, and free, tool you can use with SQL Server in order to get a real feel as to how your server is performing, where to look for issues, and miscellaneous other nuggets of information.

Performance Impact of a Key Lookup
Produced: 04/06/2017 22:16:00
Following on from my last post about Key Lookups I wanted to emphasize the performance problems that you can get when these appear in your execution plans.

Piecemeal Database Restore
Produced: 21/05/2017 16:16:00
This is a direct follow up from my last post about filegroups because without those we wouldn’t be able to perform what is called a Piecemeal database restore and, trust me, this is something you want to know how to do.

Pitfalls of the MERGE Statement and NULL Values
Produced: 02/06/2014 11:28:00
We all know and love the MERGE statement (if you don’t know it, then read this first), but it does have its quirks, especially when using null values and these can be a major problem if they go undetected as they can leave huge holes in your data if you’re not careful.

Practical Applications of Extended Properties
Produced: 30/07/2017 12:01:00
So you might be wondering what use Extended Properties could have outside of the simplistic “this helps explain columns” approach that I mentioned?

Proof A Failed Insert Increases the Value of an Identity Column
Produced: 02/10/2016 09:59:00
This is something I’ve seen cause plenty of confusion and so I thought I would investigate. It’s actually lucky that I did before decommissioning my old SQL 2008 R2 server as the same proof can’t be run on SQL Server 2014 (I’ve not tried SQL Server 2012).

Push vs Pull Subscription
Produced: 30/09/2013 20:14:00
When Subscribing to a Publication you will need to decide whether you want to use a Push or a Pull subscription. The difference may not sound much on paper, but there are some key points that really need to be noted before you make your choice.

Query Tuning - "not in"
Produced: 04/08/2014 17:58:00
This is something I’ve seen a lot recently and rarely has it been anywhere near the best way to achieve a result. In essence I’m talking about this type of query:

Query Tuning - Scalar Value Functions
Produced: 05/09/2014 18:16:00
Now, let me start by saying that if you have a scalar function which includes table access then you REALLY need to read this as they are bad... so, so bad that I never want you to use them. Seriously. These are horrible, horrible, horrible things when used in queries… please just abandon them now… there are better ways to achieve results.

Querying Extended Properties
Produced: 23/07/2017 11:58:00
Now you know all about Extended Properties, I’ll provide a quick script that allows you to query them effectively and quickly.

Quick Case Statement Quiz
Produced: 10/06/2016 11:44:00
Everyone uses the case statement… I see it all over the place and, to be honest, nearly everyone uses it completely correctly.

Quick Guide to Partition Switching
Produced: 19/02/2017 18:16:00
Partition Switching is an incredibly fast way to insert or delete data from a partitioned table and works very well in data warehousing environments, for example in which large overnight loads take place.

Quick Guide to RAISERROR
Produced: 29/01/2016 05:04:00
Have you ever written code and you want custom error messages to be returned as you go? Or to stop execution where YOU define an error has occurred even when it’s not a real error? You can use raiserror to achieve this. This is simple syntax and quite powerful.

Produced: 19/02/2016 05:14:00
The debate about INSERT INTO vs SELECT INTO can be quite long and get people quite heated and therefore I’m not going to go into the entire thing right now, but what I did want to mention was something that I encountered the other day and which could be worth considering next time you’re deciding which approach to take.

Quick Warning about Variables and Truncation
Produced: 25/09/2016 08:48:00
Well, after a short break let’s start the new year with a nice and short post.

This one is simply because I was reviewing some code for someone the other day as they had an issue they couldn’t track down and, as it turned out, it was something that I’ve definitely been stung by in the past so I thought I’d make a note here.

Replication and the Disabled Index
Produced: 12/05/2014 17:53:00
This is something that I never thought to blog about as I figured it was a common practice. I’ve no idea where I got that idea from as I’d never spoken to anyone about it, just used it within a couple of environments.

Replication Article Properties
Produced: 02/12/2013 17:32:00
When adding articles to replication one of the most important things to consider are its properties. Get these wrong and your replication may not work as expected, but get them right and you’ll be plain sailing.

Replication Verbose Logging
Produced: 30/01/2015 17:22:00
I’ve had numerous occasions in which I see something going wrong with replication, but rarely are the error messages clear and, to be honest, most of these messages simply specify something along the line of “enable verbose logging”.

Returning DBCC Messages to SSMS
Produced: 20/11/2016 19:33:00
There are some DBCC commands, such as DBCC PAGE which seem not to do anything because you see nothing in SSMS to tell you otherwise. As it turns out this isn’t the case just that SQL Server is suppressing the information messages.

Revealing Predicates in Execution Plans (TF 9130)
Produced: 08/04/2016 21:21:00
In this episode of Fun with Flags I'm going to discuss trace flag 9130. I found this flag when watching a video on performance tuning by Joe Sack… it’s something that I never knew existed, but that I’ve actually now used a few times because it turned out to be surprisingly handy.

Rolling Back to a Database Snapshot
Produced: 08/01/2017 22:06:00
One of the best features of a database snapshot is the ability to roll back your changes and revert to the snapshot.

Scalar Value Functions and Parallelism
Produced: 02/01/2015 16:38:00
I’ve complained before about Scalar Value Functions and how much I dislike them but, in case you weren’t convinced the first time around, here’s another reason for you.

Script to Compress All Tables
Produced: 22/01/2017 19:19:00
This is a simple script that’s held on my website mostly for reference as it simply takes any table and then compresses it with PAGE level compression.

Script to Update Specific Stats
Produced: 10/09/2017 12:27:00
In my previous post I was stating that we need to be more intelligent with our stats updates rather than hitting a whole table with a sledgehammer and potentially suffering poor performance as a result.

Setting Trigger Order
Produced: 27/08/2017 12:19:00
This is something that comes up rarely, but when it does it can be a real pain.

I’ve seen situations in which you have multiple triggers on the same table and these Triggers could also be set to fire from the same action.

This can cause a lot of issues.

Should I Use Synonyms or Views
Produced: 18/06/2017 17:44:00
This is a discussion I’ve had recently and it’s a bit of a weird one because there are actually very few times in which I would ever use a synonym, more often than not I would favour the view.

SHRINK a data file? Just say NO!!!
Produced: 09/06/2014 14:47:00
DBCC SHRINKFILE, DBCC SHRINKDATABASE, and Auto-shrink… they’re all truly, truly evil and should not be allowed near any system… ever!!!

Shrink Log Without Breaking a Backup Chain
Produced: 28/08/2016 20:39:00
This is something I see asked all over the internet and it’s also something for which there are a LOT of incorrect responses, dodgy solutions, and poor advice (including on the MSDN website). Therefore as I’ve had to resize a few transaction logs recently I thought I’d share the code and process I’ve used.

Shrinking a Database with TRUNCATEONLY
Produced: 24/06/2016 06:51:00
I received a comment the other day on my previous post about never shrinking a database in which I was asked whether TRUNCATEONLY was safe to use as Books Online state that no data pages are moved.

Simple Cluster Failover Checker
Produced: 09/01/2015 16:40:00
Obviously I would be shocked if you don’t already have something in place which will let you know that your SQL Server Cluster has failed over to another node. But I always like to have my own backups to any other automated alerting in order to keep me informed.

Single Column Indexes Rarely Work
Produced: 11/06/2017 22:20:00
Again, following on from my previous posts about Key Lookups, I wanted to dispel a myth that I see used in production systems everywhere… that single column indexes are useful. In the majority of cases they just plain aren’t. Get used to it.

Single Value Variables
Produced: 06/01/2014 19:02:00
These are incredibly useful when writing complex T-SQL or stored procedures etc. as they are very versatile and simple to use. They are also only active and present during the execution of your code. They are automatically dropped and tidied up once execution finishes.

Some Artwork Using SSMS
Produced: 01/07/2016 07:11:00
I did a post a while back called “A Little Happiness in SSMS” in which I abused the Spatial Results tab in SSMS using the Geometry data type in order to make a smiley face.

At the time I did say that I’d delve a little deeper into this datatype but, as you can tell, I’ve never quite gotten around to it.

Random Number Generator
Produced: 19/06/2015 13:03:00
Surprisingly this is something that I come across quite often and it’s not the easiest thing to achieve in SQL Server in certain circumstances… a solid piece of code which generates a random number.

Remove Rogue Large Transactions From Replication
Produced: 06/02/2015 17:40:00
Yesterday I had an issue in which someone had accidentally run a tidy up script on a transactionally replicated table without considering the consequences this would have on replication.

Produced: 07/10/2013 09:44:00
This is a very common function when performing string manipulation… you simply pass in a character or string that you wish SQL to look for, the text in which to look, and what you want the character or string replacing with.

Produced: 19/09/2014 17:51:00
There are numerous times in which I, and many devs in the company, need to merge new data into existing tables and prior to the MERGE command this tended to be done in a procedure which created a “dev” table (table prefixed with “dev_”), executed multiple comparisons and joins, and then proceeded to perform an sp_rename to swap the dev table for the live one.

Produced: 18/09/2015 18:46:00
This was something I created due to being fed up with constantly having to set up Profiler, having it “forget” my custom traces, and also wanting the data in SSMS so that I could query it if necessary.

Produced: 28/10/2013 18:46:00
In terms of functionality, this is very similar to CHARINDEX in that it simply looks for a pattern within a string.

However, where it differs is that PATINDEX requires you to use wildcard characters as part of your search, something that CHARINDEX will not, but it is less powerful in some ways in that it does not allow you to specify a starting point and therefore will only ever find the first occurrence of a pattern in a string.


SQL  World  CEO
Kevin  Urquhart

iPhone Selfie

I am a SQL Server DBA, Architect, Developer, Trainer, and CEO of SQL World. This is my blog in which I’m simply trying to share my SQL knowledge and experiences with the world.


© Copyright 2020 SQLTraining Ltd.