Programming

SQL Server Replication and Non-Clustered Indexes

Posted on

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. […]

Programming

SQL Maintenance Plan Optimization

Posted on

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 […]

Programming

Testing SQL Query Performance

Posted on

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. […]

Programming

Find the Current Identity of All Tables in SQL

Posted on

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. It returns the identity and associated information for all your tables. The query should be executed against the database you are interested […]

Programming

Find Stored Procedures that Reference a Table

Posted on

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 […]