By Posted on Posted in Replication No Comments Tagged with ,

I have been doing more and more replication work lately, and when recently testing some replication work I needed to create and drop some test databases and re-test the replication work. After configuring a database for replication, then deleting the publisher and the subscriber I attempted to drop the database and get the following error: Msg 3724, Level 16, State …

Cannot drop the database because it is being used for replication Read more »

By Posted on Posted in JOIN Types, TSQL No Comments Tagged with , , , ,

This is part 2 “inner JOIN” of a 19 part series on TSQL Basics. You will first gain an understanding of the differences between each of these types of joins, and when they should be used. Then we’ll explore some common uses for joins, such as replacing a not in clause with join to get the same results with better …

TSQL Basics Part 2: Inner Join – Video Explanation Read more »

By Posted on Posted in JOIN Types, TSQL No Comments Tagged with , , , ,

This is part 1 “basic JOIN” of a 19 part series on TSQL Basics. You will first gain an understanding of the differences between each of these types of joins, and when they should be used. Then we’ll explore some common uses for joins, such as replacing a not in clause with join to get the same results with better …

TSQL Basics Part 1: Basic JOIN – Video Explanation Read more »

By Posted on Posted in TSQL No Comments Tagged with , , ,

One of the common task that I have come across is the need to convert a list of results into a comma separated list.  There are many ways to use this, one if which is in reports. Download the AsCSV.sql sample here. Often times I have told developers, here is how you do it, and if you Google on it …

Converting part of a result set to a comma separated list Read more »

By Posted on Posted in SQL Server 2 Comments

I needed a way to list the indexes for an entire database, but I was running into a problem using sys.indexes that I could only see the indexes for the current database. Below is the solution that I put together to list off of the indexes in the entire database. CREATE TABLE ##allindexes(databse_id INTEGER,table_id INTEGER,index_id INTEGER,table_name      VARCHAR(1024));EXECUTE Sp_msforeachdb’use [?];INSERT INTO ##AllIndexesselect DB_ID() as database_id, object_id as table_id, index_id, namefrom sys.indexeswhere name is not null’ SELECT *FROM   ##allindexes;DROP TABLE ##allindexes;

By Posted on Posted in CTE No Comments Tagged with ,

The following question came up when working on my CTE presentation for SQL Saturday. Does a query that JOINs a CTE to itself execute the CTE query once or twice? For instance:  ;WITH deptCTE(id, department, parent)    AS (SELECT id,department,parent FROM   Departments)SELECT q1.department,q2.department  FROM deptCTE q1 INNER JOIN deptCTE q2 ON q1.id = q2.parent WHERE q1.parent IS NULL;  The following execution plan is produced showing that the Departments table is hit twice with a table scan each time with the same cost. …

CTE Query Performance Read more »

By Posted on Posted in Performance No Comments Tagged with

by Steve Stedman and the Stedman Solutions Team Optimize For Ad-Hoc Workloads – Video Tip Tue, 27 Apr 2021 Learn how Optimize For Ad-Hoc Workloads can save some space in your plan cache. Transcription: Steve Stedman 0:09Hi, this is Steve Stedman and I’m here to talk about daily checkup or database health quick scan items that we come across Read more…​ …

April Blog Post Wrap-up Read more »

By Posted on Posted in Corruption No Comments Tagged with

These are 5 questions asked after our February 2020 corruption presentation. Hope these answers help you as well. Transcription: 0:10Q: So our very first question is in regards to check DB when you’re when you’re mentioning people who are colorblind. He asks, “Can you use with no messages?” Steve Stedman 0:24A: Yes, absolutely. And you know, it’s check DB. And …

Questions Following Corruption Presentation – Video Read more »