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.

Read More >>
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.

Read More >>
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.

Read More >>
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.

Read More >>
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.

Read More >>
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.

Read More >>
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.

Read More >>
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.

Read More >>
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.

Read More >>
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.

Read More >>
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.

Read More >>
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.

Read More >>
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.

Read More >>
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:

Read More >>
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.

Read More >>
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.

Read More >>
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.

Read More >>
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.

Read More >>
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:

Read More >>
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.

Read More >>
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.

Read More >>
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.

Read More >>
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.

Read More >>
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.

Read More >>
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.

Read More >>
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)…

Read More >>
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.

Read More >>
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.

Read More >>
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.

Read More >>
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.

Read More >>
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.

Read More >>
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.

Read More >>
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:

Read More >>
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.

Read More >>
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…”:
Read More >>
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.

Read More >>
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.

Read More >>
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.

Read More >>
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.

Read More >>
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…

Read More >>
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.

Read More >>
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.

Read More >>
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.

Read More >>
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)

Read More >>
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.

Read More >>
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.

Read More >>
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.

Read More >>
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.

Read More >>
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.

Read More >>
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.

Read More >>
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.

Read More >>
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.

Read More >>
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.

Read More >>
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.

Read More >>
DATEADD, DATEPART, and DATEDIFF
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.

Read More >>
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.

Read More >>
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.

Read More >>
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.

Read More >>
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.

Read More >>
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.

Read More >>
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:

Read More >>
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.

Read More >>
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?

Read More >>
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.

Read More >>
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?

Read More >>
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.

Read More >>
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”.

Read More >>
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.

Read More >>
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.

Read More >>
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.

Read More >>
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.

Read More >>
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.

Read More >>
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.

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

Read More >>
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.

Read More >>
Guide to System Databases
Produced: 23/09/2013 12:46:00
There are 4 main system databases and 1 additional database. These are:
Read More >>
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.

Read More >>
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.

Read More >>
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.

Read More >>
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.

Read More >>
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.

Read More >>
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.

Read More >>
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.

Read More >>
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.

Read More >>
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.

Read More >>
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.

Read More >>
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.

Read More >>
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.

Read More >>
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.

Read More >>
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.

Read More >>
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.

Read More >>
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?

Read More >>
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.

Read More >>
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.

Read More >>
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).

Read More >>
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.

Read More >>
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.

Read More >>
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.

Read More >>
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?

Read More >>
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.

Read More >>
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.

Read More >>
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.

Read More >>
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.

Read More >>
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.

Read More >>
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.

Read More >>
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.

Read More >>
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.

Read More >>
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.

Read More >>
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.

Read More >>
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.

Read More >>
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.

Read More >>
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.

Read More >>
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.

Read More >>
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?

Read More >>
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.

Read More >>
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!!!

Read More >>
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.

Read More >>
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.

Read More >>
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.

Read More >>
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.

Read More >>
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:

Read More >>
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.

Read More >>
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).

Read More >>
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.

Read More >>
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).

Read More >>
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.

Read More >>
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!!!

Read More >>
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.

Read More >>
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.

Read More >>
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.

Read More >>
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.

Read More >>
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.

Read More >>
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.

Read More >>
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.

Read More >>
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.

Read More >>
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.

Read More >>
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.

Read More >>
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.

Read More >>
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.

Read More >>
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.

Read More >>
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.

Read More >>
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.

Read More >>
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.

Read More >>
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.

Read More >>
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))

Read More >>
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.

Read More >>
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.

Read More >>
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.

Read More >>
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.

Read More >>
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.

Read More >>
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.

Read More >>
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.

Read More >>
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.

Read More >>
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.

Read More >>
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?

Read More >>
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.

Read More >>
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!!!

Read More >>
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.

Read More >>
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?

Read More >>
THROW instead of RAISERROR
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.

Read More >>
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.

Read More >>
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?

Read More >>
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.

Read More >>
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:

Read More >>
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.

Read More >>
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.

Read More >>
Using CONCAT
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.

Read More >>
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.

Read More >>
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?
Read More >>
Using RAISERROR with NOWAIT
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.

Read More >>
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.

Read More >>
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.

Read More >>
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.

Read More >>
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?

Read More >>
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.

Read More >>
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.

Read More >>
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.

Read More >>
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.

Read More >>
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.

Read More >>
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.

Read More >>
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.

Read More >>
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.

Read More >>
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…

Read More >>
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?”

Read More >>
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…

Read More >>
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.

Read More >>
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.

Read More >>
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:

Read More >>
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.

Read More >>
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:

Read More >>
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.

Read More >>
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:

Read More >>
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.

Read More >>
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.

Read More >>
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.

Read More >>
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.

Read More >>
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…

Read More >>
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.

Read More >>
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:

Read More >>
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).

Read More >>
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…

Read More >>
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…

Read More >>
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.

Read More >>
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:

Read More >>
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.

Read More >>
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.

Read More >>
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:

Read More >>
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.

Read More >>
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.

Read More >>
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.

Read More >>
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.

Read More >>
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.

Read More >>
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.

Read More >>
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?

Read More >>
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…

Read More >>
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.

Read More >>
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.

Read More >>
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…

Read More >>
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:

Read More >>
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?

Read More >>
SELECT INTO with IDENTITY
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:

Read More >>
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…

Read More >>
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”.

Read More >>
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.

Read More >>
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.

Read More >>
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.

Read More >>
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.

Read More >>
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.

Read More >>
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.

Read More >>
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?

Read More >>
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).

Read More >>
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.

Read More >>
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.

Read More >>
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.

Read More >>
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.

Read More >>
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.

Read More >>
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.

Read More >>
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.

Read More >>
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.

Read More >>
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.

Read More >>
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?

Read More >>
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).

Read More >>
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.

Read More >>
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:

Read More >>
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.

Read More >>
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.

Read More >>
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.

Read More >>
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.

Read More >>
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.

Read More >>
Quick Note About INSERT INTO vs SELECT INTO
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.

Read More >>
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.

Read More >>
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.

Read More >>
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.

Read More >>
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”.

Read More >>
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.

Read More >>
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.

Read More >>
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.

Read More >>
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.

Read More >>
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.

Read More >>
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.

Read More >>
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.

Read More >>
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.

Read More >>
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!!!

Read More >>
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.

Read More >>
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.

Read More >>
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.

Read More >>
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.

Read More >>
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.

Read More >>
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.

Read More >>
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.

Read More >>
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.

Read More >>
REPLACE
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.

Read More >>
sp_Merge
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.

Read More >>
QuickTrace
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.

Read More >>
PATINDEX
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.

Read More >>

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.

Categories


© Copyright 2020 SQLTraining Ltd.