You can subscribe to the sql tag micro-feed.
There are lots of ways to make an identity immutable and unconfusable. I’ll show just a couple, but first the principle behind it. Your identity should be immutable, because you will accumulate lots of threads back to your item that need that identity. If the identity changes, you will be breaking all of the relationships […]
If you want non-clustered indexes to be copied to your replication slaves (you probably do) you simply adjust the setting that you’ll find here… SQL -> Replication -> Local Publications -> Publication Name -> Properties -> Articles -> Article Properties -> Copy nonclustered indexes When you set this to “True”, the indexes will be copied. […]
I have found that Activity Monitor can have a habit of bugging out on you when you try to get recent expensive queries. If you also get this, you may want to try the following query instead. You can adjust the query purposefully by changing the ordering, based on what you are looking for. There […]
I was investigating a number of maintenance plans that were taking serveral hours to complete and I noticed some optimizations that could be made to the plans without actually affecting the behaviour very much. The maintenance plan after the fixes is: Check Database Integrity Maintenance Cleanup Task Rebuild Index Clean Up History Back Up Database […]
I was asked about how to test a query without all of the clever caching that SQL performs, this is how you do it… but read on for important information. DBCC DROPCLEANBUFFERS DBCC FREEPROCCACHE GO SELECT * FROM MyTable Very Important Notes There are some very important things to consider before you use these commands. […]
So you have a SQL Cluster and you want to run a database upgrade using Octopus Deploy… where do you start? There are actually two strategies you can employ to do this, and you can choose the most appropriate one based on how you have things set up. Octopus Deploy is cluster-agnoistic, but you can […]
If you need to find gaps in a sequence (for example, gaps in an ID column, or in a sort order column, etc) you can use the below query. It uses a series of Common Table Expressions to slice up the gaps between islands in the range and presents a list of each gap. I […]
If you ever need to get a list of all your tables and their current identity value (i.e. you want to know CHECKIDENT NORESEED for all your things) you can run this query. The query should be executed against the database you are interested in: SELECT IDENT_CURRENT(TABLE_NAME) AS Id, IDENT_SEED(TABLE_NAME) AS Seed, IDENT_INCR(TABLE_NAME) AS Increment, […]
If you need to find stored procedures that reference a table in a SQL database, you can use this script. Just update the table name in the script. The script returns all objects that reference the table, including stored procedures. SELECT DISTINCT so.name, so.xtype FROM syscomments sc INNER JOIN sysobjects so ON sc.id = so.id […]
A lot of SQL troubleshooting starts like this: sp_who2 The “spootoo” procedure brings back a list of SPIDs and includes information on which SPIDs are blocking. You can quickly track back to a problem SPID by following the information in the “BlkBy” column, which shows the SPID blocking a row. Once you have the SPID, […]