There are quite a few times I need to number rows of data based on some grouping column. I often forget how to do it. Well, here’s a couple of examples:
Create table TestTable1
Insert TestTable1 (ID, InsertDate)
--Note the values are out of order for ID 2
(select count(*) from TestTable1 TT2
where TT1.ID = TT2.ID
and TT1.InsertDate < TT2.InsertDate) as 'rank'
from TestTable1 TT1
Order by ID, rank
Or a simpler way…
ROW_NUMBER() OVER ( PARTITION BY ID ORDER BY insertdate ) as 'rank'
from TestTable1 TT1
Order by ID, rank
Here are some examples and explanations:
- Rewrote one piece of code for archiving data – much simpler now.
- Updated SQL CHECKSUMS for data migration testing.
- Planned data migration testing for the next week.
- Verified the issue with the job that identifies what indexes need rebuilding was a network issue.
- Vacation Thursday and Friday.
Good Reading from the Week:
The Week Ahead:
There’s been many times I’ve tried to find the restore history of a database. Questions like when was the backup taken? Can we re-restore and re-do this? Can we create a copy of the original database? etc. Most of the times I use jobs to restore, but sometimes I’ve got to use LiteSpeed, etc. I had to find the source of a backup this morning – here’s what I came up with for a script:
Declare @DBName varchar(255)
Set @DBName = '<<Database Name Here>>'
Select top 10
BUS.server_name as 'SourceServer',
BUS.database_name as 'SourceDB',
from msdb.dbo.restorehistory RH
Join msdb.dbo.backupset BUS on BUS.backup_set_id = RH.backup_set_id
Join msdb.dbo.backupmediafamily BMF on BMF.media_set_id = BUS.media_set_id
where RH.destination_database_name = @DBName
Order by RH.restore_date desc
Note – Here’s Technet’s documents on the Backup and Restore Tables in MSDB.
- On Call for production support after hours
- One non-sql issue I got pulled into
- An issue with one of our identify indexes that need rebuilding had me up late.
- Rights issue that turned out to be a training issue.
- Data Migration Testing continues…
- The BINARY_CHECKSUM and CHECKSUM_AGG – Combo identified three different static rows (insert vs update statements slightly different)
- One edge case issue
- A couple of non-data migration issues found in data migration testing.
- Re-working one piece of code for archiving data – bad communication on the entire thing.
- Created a HowTo category for quick hitters that help.
Good Reading from the Week (new section):
The Week Ahead (new section):
- New Rev to Deploy and test deployment process.
- Data Migration testing near completed (At least for this round.)
- Service Broker Presentation
- OOO Thursday and Friday
I learned this a long time ago. It helps when building multiple servers, you’ve got a deadline and you need to reboot one or more. Simply, fire up a command prompt and add the –t switch to the end of the ping command.
Ping ###.##.##.## –t
Ping <<ServerName>> –t
The –t will continually ping the server until you stop it (with Ctrl-C)….So after the server shuts down, you’ll get a Request Timed Out – every second until it’s back up. Once you start seeing a response, it’s on the network – it may take another minute or two to be able to RPD to it.
About 8 years ago I worked with a guy who taught me a lot of non SQL IT stuff. He always explained things very well – if I asked. He taught me how to find a server’s name by using the ping command and a switch. I couldn’t remember how we did it and every time I’ve looked up how to do it, I couldn’t figure it out – until today. Open a command prompt and:
Ping -a ###.##.##.##
Note the –a resolves addresses to hostnames. Also, the –a needs to be in the front of the IP! I think that’s the part I couldn’t get figured out. I’ll now have it on my blog if I ever forget again.
- Incremental Deploys vs Full Deploys are causing many challenges.
- Table creations with typos that are later fixed and updated in the initial creation caused errors.
- Table creations with typos that are later fixed and NOT updated in the initial creation caused errors with data migration.
- Issues with new Development environments being build on broken revs of old code
- Data Fixes in the test environments that are caused by features pulled out of this release.
- Development work that was written to spec, but the specs weren’t correct!
- Data Migration – Testing round #2 getting started
- Used BINARY_CHECKSUM and CHECKSUM_AGG for a lot of the data migration testing.
- T-SQL Tuesday written – “T-SQL Tuesday #009: An Ounce of Prevention”
Recently I got an email from a tester claiming that the IDs of values in static data tables are different in one database than they are in another database. To check this out, I used a combo of the
BINARY_CHECKSUM and CHECKSUM_AGG. Here’s a breakdown of each:
- The BINARY_CHECKSUM function returns a checksum value for a row of a table or over a list of expressions.
- A value is computed for each row, this makes it easy to find rows that are different.
- This function returns a group of values.
- Will return a different value for most, but not all, changes to the row, and can be used to detect most row modifications.
- The CHECKSUM_AGG function returns a checksum of the values in a group.
- If one of the values in the expression list changes, the checksum of the list also generally changes. However, there is a small chance that the checksum will not change.
So, I figured that I could first find the BINARY_CHECKSUM of each row and then find the CHECKSUM_AGG of all the BINARY_CHECKSUMs. If the tables were different, most of the time (as noted in bold above) these values would be different.
There are two situations that I find this very useful for (I’ll post about these later):
- Static data – data that needs to be the same from one environment to another
- Data migration – verifying all the columns get migrated correctly.
Note – there are a few data types that BINARY_CHECKSUM does not support. They are text, ntext, image, cursor, xml, and noncomparable CLR user-defined types. In my experience, some of the time update dates and insert dates are not important as well as timestamp data types, so I usually ignore these columns.
So to investigate the tester’s claims, I ran the following on each database:
Select CHECKSUM_AGG(BINARY_CHECKSUM(Col1, Col2, Col3)) from Table1
The values were different, so I removed Col1 from the criteria and ran the following on each database and the values were the same.
Select CHECKSUM_AGG(BINARY_CHECKSUM(Col2, Col3)) from Table1
Therefore, Col2 and Col3 had the same values for both Col2 and Col3 **most likely** but Col1 definitely had different values. The tester was right.
I then removed the CHECKSUM_AGG to find and then compare the values of each row to find the ones that didn’t match.
BINARY_CHECKSUM(Col1, Col2, Col3)
Order by Col1
Here’s an example that shows that this method doesn’t always work – note that the same distinct values are in each table, but each row is not the same.
CREATE TABLE [dbo].[table1](
CREATE TABLE [dbo].[table2](
Insert table1 Values
(1, 'Row #1 in Table1, Row #2 in Table2'),
(2, 'Row #2 in Table1, Row #1 in Table2')
Insert table2 Values
(2, 'Row #1 in Table1, Row #2 in Table2'),
(1, 'Row #2 in Table1, Row #1 in Table2')
---These are equal
Select CHECKSUM_AGG(BINARY_CHECKSUM(col1, col2)) from table1
Select CHECKSUM_AGG(BINARY_CHECKSUM(col1, col2)) from table2
---These are NOT equal
Select BINARY_CHECKSUM(col1, col2) from table1
Select BINARY_CHECKSUM(col1, col2) from table2
Despite the small chance of a checksum being equal, this method makes it very easy to quickly identify data inconsistencies in different databases or environments. If you’re ever need to quickly find inconsistent data, give it a try.
This month’s T-SQL Tuesday, “T-SQL Tuesday #009: Beach Time” is hosted by Jason Brimhall (Blog/Twitter) aka the sqlrnnr. The topic is: What do you do as a DB professional to earn a little “Beach Time?” What do you do prior to “Beach Time” to ensure that the beach time will not involve work?
Most of my vacations involved traveling out of cell range, so my vacations are usually guaranteed to not be interrupted by work. My college buddies and I go on an annual trip to Canada’s Quetico Provincial Park, my parents have a cabin on the Gunflint Trail in northern MN. Being out of cell range is a bit more stressful than it sounds, but that’s why I’ve worked out a system to ensure things run smoothly when I’m gone. My system is all based on taking care of things before I leave the office because – I believe it was Ben Franklin who said, “an ounce of prevention is worth a pound of cure.”
First, I always let the people who I’m working with know I’m going on vacation weeks before. I tell them in meetings and email my boss once plans are firmed up and dates are set. One thing at my current job which I couldn’t stand at first, but now I find useful is that we send out meeting requests to each other saying when we are out of the office. The big keys to sending these out is to disable the reminder, set the time as free time and do not request responses. This way it’s on other’s calendar as a reminder, but that’s about it.
Next, and this is the hard one for me, at times, is I tell people no. No, I can’t get this done before I leave. No, I can’t make your meeting, I’m finishing up Project A. etc. I also decline all meetings and block out my calendar (set to busy) for the days I’m going to be gone.
Finally, I’m a big list maker. I usually list out what I’ve got to do everyday, when I’m going on vacation I’ll add a column to my list and put a secondary name by it. I go over everything I can with the secondary person, so they know what’s going on and if there is an issue while I’m away, they can be contacted. Because I’m a list maker, I’ve also got a checklist of “Things to do for Vacation”. Most of the items are for issues with specific environmental tasks around here, but the big one is to turn on my out of office assistant. I did forget once and had a few of upset users know the best way to get a hold of me is via email and I wasn’t responding.
These things don’t really take too much time to do. If you do these simple things, there will be not only less hassle when you get back in the office – making your vacation stick a bit longer, but also it will give you a chance to fully relax when your on vacation. It’s just an ounce of prevention!
- Data Migration
- Battled a rights issue with AD groups and sub groups and database roles, ect. Works in Dev, didn’t work in Prod Stage.
- Schema Compares – verifying that the incremental builds work out to the same schema as the major builds. Fixed a couple of logic issues.
- Ran into some bad data that was causing errors due to features getting pulled from the project, but data remained