Deleting a duplicate record

Recently a friend of mine asked me about deleting a duplicate record from a table without deleting both records. Having been in a situation I drafted up a sample script and emailed it to him.

DECLARE @tbl1 TABLE
(
   id int,
   color varchar(50)
)
INSERT INTO @tbl1 (id, color) 
VALUES (1,'blue')
,(1,'blue')
,(2,'red')
,(3,'green')
,(4,'yellow')
,(5,'orange');

First off let’s see all the rows in the table.

SELECT * FROM @tbl1;

00.allrecords

Now let’s list just the duplicate records

SELECT
   color
   ,COUNT(color)
FROM @tbl1
GROUP BY color
HAVING (COUNT(color) > 1);

01.listduplicaterecords

Now let’s look closer at these duplicate records to see if there is any uniqueness

SELECT * FROM @tbl1 WHERE (color = 'blue');

03

As you can see there is nothing unique about either of the records. So how can you delete just one as opposed to deleting both? You could try the following but that is similar to the query above and it will target both records.

DELETE FROM @tbl1 WHERE (color = 'blue');

Well the quickest way to accomplish would be to do the following and use the SET ROWCOUNT statement.

SET ROWCOUNT 1;
DELETE FROM @tbl1 WHERE (color = 'blue');

--REVERT ROWCOUNT
SET ROWCOUNT 0;

-- LIST ALL ROWS
SELECT * FROM @tbl1;

02.deletedrecord

Consolidate multiple records into a single row

I worked on a restore script that had to consume values from the network share and produce a restore statement. This database happened to be striped to eight files. I needed a way to construct the restore statement into a single row and I was able to achieve the desired results using the following syntax.

DECLARE @temp TABLE
(
	id INT IDENTITY(1,1) PRIMARY KEY CLUSTERED
	,colors VARCHAR(30)
)

INSERT INTO @temp (colors)

VALUES ('Red')
,('Blue')
,('Green')
,('Yellow')
,('Brown');


SELECT colors

FROM @temp


SELECT 'Colors' = 'RESTORE ' + 
(SELECT colors + ';'

FROM @temp 

FOR XML PATH (''), type).value('.', 'varchar(max)')

Here’s the end results.

results

Needing a change… Modify your Filename

A friend of mine told me that he felt a need for a change. Without question I said cool. Change is always inviting as it keeps things interesting. Little did I know or realize he was referring to the changing of his database file name. So he issued the simple ALTER DATABASE MODIFY FILE statement and specified a new name for his database.

ALTER DATABASE AdventureWorks
MODIFY FILE (NAME = AdventureWorks_data, FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\AdventureWorks_data.mdf')
ALTER DATABASE AdventureWorks
MODIFY FILE (NAME = AdventureWorks_Log, FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\AdventureWorks_log.ldf')

and in the results were

The file "AdventureWorks_data" has been modified in the system catalog. The new path will be used the next time the database is started.
The file "AdventureWorks_Log" has been modified in the system catalog. The new path will be used the next time the database is started.

So he took this message as needing to restart the SQL Server Engine Service. Which he did; however, that did not satisfy the underlying issue. After refreshing the databases he attempted to expand the db and received a nice little error indicating that the database was not accessible. Which is when I received the call.

Having experienced this myself I remembered that:

1. the database needed to be taken offline

ALTER DATABASE AdventureWorks SET OFFLINE WITH ROLLBACK IMMEDIATE

2. he would need to physically rename the files accordingly

3. bring the database back online

ALTER DATABASE AdventureWorks SET ONLINE WITH ROLLBACK IMMEDIATE

Once these steps were completed he was back in business. On a side note this permitted the changing of the file names but you can also change the actual file path location for each of the files as well using the same syntax. If you wanted to update the logical name for both the data and log file below is the syntax for doing so.

ALTER DATABASE [AdventureWorks] MODIFY FILE (NAME=N'AdWks', NEWNAME=N'AdventureWorks_data')
ALTER DATABASE [AdventureWorks] MODIFY FILE (NAME=N'AdWks_log', NEWNAME=N'AdventureWorks_log')

Get row count from all tables

I was reviewing some of my older scripts and I came across one in particular that stood out. I wrote it quite a while ago and looking back does indicate where I came from in terms of logic. To give a little background my script was written to grab the row counts from all tables within the given database. The problem… it created a temp table then loaded the temp table from querying the INFORMATION_SCHEMA.TABLES. Then it looped through each of the table names loading them it into a variable and then passed them to an update statement which updated the temp table. As you can see this was a very inefficient way to go about it.

I was reading up on catalog views and it seemed that I could query sys.sysobjects and join sys.sysindexes. So I figured I would get this verified. I posed the question and following are the responses I received.

Yep. sys.sysindexes is depreciated in Denali. Information here: msdn.microsoft.com

So here is my script. I originally left out line 15 and when I did I saw a lot of duplication in the results set. So I surfed around and found a post titled, “Display Row count for all tables” by Shyam Skj that used the indid field from sysindexes to filter the records. The closes thing I found was the index_id from sys.partitions and lo and behold using similar syntax the results returned nicely.

/* Check if object already exists */
IF OBJECT_ID('usp_GetTableRowCounts') IS NOT NULL
/* If available drop object */
BEGIN
DROP PROCEDURE usp_GetTableRowCounts
END
GO
/* Create procedure */
CREATE PROCEDURE usp_GetTableRowCounts
AS
SELECT o.name 'TableName', p.rows 'Rows'
FROM sys.objects o
JOIN sys.partitions p ON o.object_id = p.object_id
WHERE (o.type = 'U')
AND (p.index_id IN (0,1))
ORDER BY p.rows DESC

AdventureWorks Results

T-SQL Tuesday: Lucky 7-Resolutions

Official TSQL2sday Logo

TSQL2sday

Every year I make a resolution list just like anyone else and often I am able to achieve them while other items are pushed aside for reasons beyond my control. This year however is slightly different now that I have completed my Bachelors of Science degree in Computer Information Systems with a focus on Database Management. This alone makes some of my resolutions much more attainable. With the added time that I have I can finally focus on certification studies among other things that pertain to personal and professional development. If I am able to juggle my time wisely I can achieve the ultimate ying-yang of personal time management.

So to get down to business here is my list of things I want to accomplish in 2011:

1. Health

Without no surprise in order to achieve my goals I need to be healthy. I dropped about 30 lbs during 2010 and have about 20 lbs to go to reach my ideal weight, which is 190. So before 2011 comes to a close I should be at or around 190 lbs. Chances are I will meet that goal before mid year. I am pretty confident it will be definitely before mid year.

2. Certifications

I currently hold three certifications, but it’s time to buckle down and concentrate on SQL Server centric certifications. Before the end of the first quarter I want to complete the MCITP Database Administrator certification for SQL Server 2008. Which means I started studying for exam 70-432 the beginning of this year. And in order to make things more realistic I am going to schedule for the test by this Friday (to be taken in the near future) which will add the much-needed pressure thus making accountability that more important.

3. ETL Stizzuf

There is just something about Business Intelligence that tickles my fancy. It’s hard to describe but I love reports, especially those I don’t have to hand write. 2011 will be the year I become a BI bad ass so-to-speak. To paint a picture I want to strive to become the west coast version of Brian Knight but in a smaller scale all while wearing Adidas kicks or sandals with socks on because I feel strange without socks… all other attire is optional. At one point or another within the year I want to actually spend a night in a warehouse while developing a data warehouse. I think that would be peachy! Icing on the cake if you will.

4. Get more involved

I touched the waters last year and started to participate where I could. To my surprise I found that people are actually willing to lend a helping hand and share the wealth of knowledge with one another. When I was handed the keys to manage the organization’s data I really didn’t know where to begin or who to consult in the event I ran into a brick wall. I picked up some books, started browsing blogs and joined 26 social networking sites. Then after learning more about social networking I found that twitter and facebook seem to have the highest concentration of SQL Socialites. I can’t say they accepted me, but I can say they have made an impact on my life professionally and personally. I won’t get into details but know that I am grateful.

5. MVP or runner up

This might be a long shot given the pool of MVPs out there. So the stakes are high and quite frankly I have no idea how to toss my hat into the ring. This is something I have only read about lightly and never really asked someone how they achieved MVP status. But nonetheless I need goals to keep me aiming high and moving in the right direction.

6. Organize a SQLSaturday

This might be cheating along with a bit of self promotion since we are slated to launch #sqlsat47 this February 19th, 2011 at Chandler-Gilbert Community College. This ties into list (Item 4) about getting more involved. I am a newbie in terms of event coordinating but I plan all with my co-organizer @coneybeer to deliver a successful SQLSaturday. There is still time to register for the event… I hope to see you there!!!!

7. Participate in the TSQL2sDay blog party

I have seen many of these pop up last year and I find myself very intrigued. I did some research about #TSQL2sDay (which was founded by Adam Machanic) and anyone can become a host provided they participate in at minimum two #TSQL2sDay events which are held monthly, keep an active blog followed up with an email top Adam Machanic. So with the list I have this year and the participation that I plan on doing I should be ready come next year to host a #TSQL2sDay event. Check out the

Format Seconds into HH:MM:SS

Without having to use replication monitor I decided to put together a stored procedure that will return the same results. In the MSmerge_sessions table of the Distribution database the field duration is returned in raw seconds. Meaning that if the process took 2 mins and 2 secs it displayed as 122. Well I suppose I could live with it but I am hard-headed and wanted the outcome to look similarly formatted HH:MM:SS. Without really getting into the specifics on joining tables I used the MSmerge_Agents a and MSmerge_Sessions s joining on s.agent_id to a.id and added a.subscriber_name to my query to display the subscriber name but that is beside the point of this post.

Calculating Hours

The first thing you should know is how to calculate seconds into hours and minutes. Below outlines how to do each, but before we jump into that let’s try it manually.

There are 60 seconds in one minute and there are 60 minutes in one hour. So it is 60*60 which equals 3600. 3600 is the amount of seconds in one hour.

Now let’s take 3723 seconds and divide it by 3600.

What do we get?
3723/3600 = 1.03416 just a tad more than one hour

If you run the following in Management Studio you will notice that one is returned. You will not get (.03416) trailing the one. So calculating hours is easy since it returns whole numbers.

SELECT (3723/3600)

Calculating Minutes

In order to calculate minutes you will need to perform a calculation similar to the one above with the exception of asking for the remainder using the modulo operator. Which is stated as: (3723 % 3600). However this alone will not return the results you are looking for. You need to take it a step further and divide it by 60.

Now we have:
((3723 % 3600) / 60) = 2 minutes

If you run the following in Management Studio you will get the same result as I stated above.

SELECT ((3723 % 3600) / 60)

Calculating Seconds

Alright we are almost there. Now we need to get the seconds after everything is all said and done. This is another easy one. We take 3723 and get the remainder by using 3723 % 60.

SELECT (3723 % 60)

Run it together

Here is a quick and non-formatted way to see the results.

DECLARE @secs INT
SET @secs = 3723

SELECT (@secs / 3600)		'Hrs'
SELECT ((@secs % 3600)/60)	'Mins'
SELECT (@secs % 60)			'Secs'

Stop! Format Time!

The purpose of this post was to format this field appropriately and here is what I did.

DECLARE @secs INT
SET @secs = 3723

SELECT	
CONVERT(VARCHAR(10),CONVERT(datetime,CAST((@secs / 3600) AS VARCHAR) 
+ ':' + CAST(((@secs % 3600)/60) AS VARCHAR) 
+ ':' + CAST((@secs % 60) AS VARCHAR)),108)

Final Results Set

Pad then Add Leading Zeros

In the event you need to add leading zeros to an integer, the process is pretty straightforward. I am using the AdventureWorks database and SQL Server 2008 R2. For this example I will use the Sales.SalesPerson table for their SalesPersonID field which contains 3 digit IDs. First let’s take a look at the data. As you can see the data looks fine so let’s get started.

SELECT SalesPersonID
FROM [AdventureWorks].[Sales].[SalesPerson]

Padding

The first thing we need to do is to pad the field with leading spaces using the STR function and specify 6 spaces.

SELECT STR(SalesPersonID, 6) 'SalesPersonID'
FROM [AdventureWorks].[Sales].[SalesPerson]

Replacing

Now we just need to replace those spaces with zeros using the REPLACE function

SELECT REPLACE(STR(SalesPersonID, 6),SPACE(1),'0') 'SalesPersonID'
FROM [AdventureWorks].[Sales].[SalesPerson]

That’s it… nothing to it.