Query to get a combined count of parents (with no children) and children

One of my good friends approached me this morning with this scenario.

1. Parent Id and ChildId are stored in the same table.
2. The relationship goes only one level deep (i.e. a child record is not a parent record for any other child record)

Requirement:
He wanted a combined count of
1. All Parent Ids that do not have any child records
+
2. Count of all Child records.

Here is the script I wrote and gave him.

Hope it is useful to you as well.. :-)



-- CREATE A TABLE FOR OUR SCENARIO
CREATE TABLE T1 (ID INT IDENTITY PRIMARY KEY, PARENT INT REFERENCES T1(ID))
GO 

--POPULATE THE TABLE FOR OUR SCENARIO
-- EXECUTE NEXT 6 INSERT STATEMENTS..THESE 6 STATEMENTS SHOULD CREATE 
-- ABOUT 25 RECORDS OUT OF WHICH 20 RECORDS WILL QUALIFY FOR OUR SCENARIO    
INSERT T1 (PARENT) VALUES (NULL)
GO 10
INSERT INTO T1 (PARENT) VALUES (1)
GO
INSERT INTO T1 (PARENT) VALUES (2)
GO 2
INSERT INTO T1 (PARENT) VALUES (3)
GO 3
INSERT INTO T1 (PARENT) VALUES (4)
GO 4
INSERT INTO T1 (PARENT) VALUES (5)
GO 5

-- DO A SELECT TO MAKE SURE IT LOOKS GOOD.
SELECT * FROM T1
GO

--EXECUTE THE NEXT QUERY THAT GIVES THE RESULTS.
WITH COUNT1(ID) AS 
( SELECT COUNT(DISTINCT a.ID) ID
  FROM T1 a
  WHERE PARENT IS NULL
  AND   NOT EXISTS (SELECT 1 FROM T1 b WHERE a.ID = b.PARENT) 
  UNION
  SELECT COUNT(DISTINCT t2.ID) ID
  FROM T1 t2
  WHERE PARENT IS NOT NULL
 )  
SELECT SUM(ID)
FROM   COUNT1
GO

Basic Database Maintenance Activites

I have been thinking about the core database server maintenance activities that are necessary for a DBA. Here are the basic database maintenance activities that I perform in my servers.

1. Setup Backup Jobs
One of my friends told me once that a DBA needs to have a good backup in place or a good resume in hand..!! Backups are the most important responsibility in a DBA's day to day activities. I usually setup a full backup (once a week or daily depending on the size of the database) and differential backups (only if the full backups are taken once a week) and transaction log backups every hour. I usually retain the backups for 2 weeks (There is also a system level backup happening in my company that is written to tape). I also try to store backups in a separate external hardware from the one where the data files are living..(Though it is not possible always..:-( ). It is also very important to have email notification setup to inform the DBA incase of any failures.

2. Setup Alerts
I usually setup alerts for any SQL Server event with severity 19 or more. This can be easily done using SQL Server Management Studio. I will write a separate post about setting up alerts. The most important thing is to setup email notification when the event happens.

3. Database Integrity Check
A corruption free database means peaceful life for a DBA. I usually check my database consistency once a week. This can also be done using a maintenance plan in SQL Server Management Studio.

4. Index Rebuild / Reorg
A fragmented Index can cause severe performance issues and could be a DBA's nightmare when he is on a hot seat to solve a performance issue. It is always good to be proactive and check the fragmentation level of all the indexes and perform rebuild or reorg as necessary. The thumb rule is to leave the index as is if the fragmentation is less than 10% ReOrg if it is less than 30% and Rebuild if it is more than 30%. Do not use the maintenance task for Index rebuild or Update Statistics if your database is huge. This will cause the job to rebuild all the Indexes in the database which could possibly run for days and would never end. Instead, it can be easily done using a script from the Following Link from books online. This script would only rebuild or reorg an Index if it is necessary.

I am sure there are other important maintenance activities as well. But, according to me, these are the core maintenance activities that any DBA should perform in his/her servers.

Hope this helps..!!