One of the important settings on SQL Server is the job history log size and the maximum rows per job. Without these set accurately you may not be able to track down job failure issues. These are easy to locate through the SSMS dialogs, but if you are managing many servers you may not want to dig through all the …
This is part 3 on left outer 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 …
TSQL Basics Part 3: LEFT OUTER JOIN – Video Instruction Read more »
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 »
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 »
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 »
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 »
Here listed is the current percentages of SQL server versions running our Daily Check-up with Database Health Monitor. Compare this month’s percentages against Last Month’s Percentages
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;
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. …
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… …