Contents tagged with sql server
-
Using ROW_NUMBER to get the most recent record from a table
Quite a while ago I wrote about using SQL Server CLR to add "earliest" and "latest" aggregate functions that could be used to retrieve the, well, earliest and latest Id values from a table based on …
-
Making sure Windows doesn't start your service before SQL Server
This is more of a "note to self" of the syntax, but, if you have a service that depends on another (such as a local SQL Server instance for local logging) then you can tell Windows about that …
-
What's an Estimated Subtree Cost?
When you're looking at the execution plan for a query (you do that, right?) one of the data-points that's shown is "Estimated Subtree Cost". Looking at this is one of the things I review when I've …
-
Creating a linked server that has a different name to the "real" server
It's sometimes useful to be able to have a linked server where the name is misleading. One example of this could be where you don't have a perfectly isolated test or staging infrastructure so you …
-
Quick Tip: Randomising a resultset from a SQL query
Sometimes I need a random (random enough for my purposes - I don't know, nor care, if it meets a formal definition of random) sample of N rows of data from a table. There's a very quick way to do …
-
Always make sure you specify the length to CONVERT in SQL
Over the years I've seen this potential blackhole of data loss several times, a developer has written CONVERT(VARCHAR, [ColumnName]) without specifying the length of the VARCHAR to convert the column …
-
SQL Server 2016 syntax for dropping objects
SQL Server 2016 introduced a new syntax for dropping objects, that I've known about for quite a while. I've been surprised by the number of people that aren't familiar with it so thought I'd put …
-
Structuring UPDATE and DELETE queries so that they're a little bit safer
Quite often I've seen someone who wants to update a table, or delete rows from a table write a SELECT query to work out what data they want then separately write an UPDATE or DELETE query to …
-
TIL: Queries against linked servers... Be careful how you structure them!
The fact that a query across a SQL linked server (to another SQL server) is sometimes off-loaded to the remote server and sometimes not even when the entire query is operating on the remote data is …
-
The content of table variables is retained even when a transaction is rolled back
You learn something new every day! Thanks to the superbly detailed answer by Martin Smith on the question What's the difference between a temp table and table variable in SQL Server? at Database …