Category Archives: Tech Notes

Search for Column Name in All Tables in All Databases

Use a text wild card to search for a column name in all tables in each database.  Stored procedure sp_msforeachdb is used to cycle through each database.  The sys tables are used to obtain table and column information.  The output will include Database Name, Schema Name, Table Name, Column Name, and code to easily select the top 10 from a table returned in the result.

Random n% Records – Fastest Method

Method Source: https://msdn.microsoft.com/en-us/library/cc441928.aspx

Row Count on Large Tables Fast

There are ~550 million records in a table I am trying to get a row count on.  Typically I have always used the “COUNT(*)” method to get a row count. I was curious if there is a faster way.  I had read in some forums that “COUNT(1)” or “COUNT(Primary_Key)” could be theoretically faster but there was some argument on the topic.  I decided to run execution plans on all three to see how the query engine is handling it.  According to the execution plan all three operations are treated the same.

row count execution plan

I decided to then prove they are treated the same by running each one three times and taking the average execution time.  Although the results show small differences , this only caused by fluctuations on server load from other processes.   If I took enough measurements they would  converge.

Results:
COUNT(*) = 15.0 seconds
COUNT(1) = 16.3 seconds
COUNT(PK) = 18.0 seconds

Change in Direction

For my purposes I am trying to quickly obtain a count for all tables and databases on a server.  This a large production data warehouse with many databases and hundreds of tables.  I want to minimize the impact to the server and in this case that is more important to me than accuracy.  An alternative method to reading the table directly is to get the statistics from sys.dm_db_partition_stats. This is a Dynamic Management View (DMV) that reads the information about the partition, and by summing it up for a given object you can obtain the row count.  This row count is an estimation.  Any transactions that are occurring would not be captured.

dm_db_partition_stats execution plan

Althought the execution plan looks more complicated, it does not have to read a large table and instead only reads 1 or a handful of records via the system DMV.

Considerations

To stress the point; this is a fast but not necessarily accurate method to get row count.  If you are doing some validation, such as comparing tables, then I recommend using count() and possible checksum().

More Information About dm_db_partition_stats: https://msdn.microsoft.com/en-us/library/ms187737.aspx

HDD to SSD for OS and Windows 7 Key Recovery VB Script

HDD to SSD

I recently purchased a 250GB Solid State Drive made by Crucial to replace my 150GB VelociRaptor Hard Disk Drive for my Operating System.   There are two routes I could think of to go about installing the OS on the SSD.

  1. Copy image from HDD to SSD
    • Pros:  Would allow a fairly seamless transition from one drive to another.  No need to migrate files to a third disk and then reload to the new SSD.
    • Cons: Any inefficiencies/growth Windows has created (software bloat), or any disorganization I have created will be carried over to the new SSD. Any corrupted files or disk sectors would be carried over to the new drive.
  2. Install fresh copy of OS on SSD
    • Pros: A fresh OS always runs better because it has not had time to bloat.
    • Cons: Time; I will have to migrated any files I want to keep to another drive, install Windows, install any applications/drivers I had previously, then migrate files back to main drive.

I decided to go with option 2; installing a fresh copy of Windows 7 to the new SSD.  One of the cons of doing this is having to move the files you want to keep to another drive and then back to the main OS.  This does not impact me because I already back up to google drive, a cloud service, and I also have a secondary 1TB disk drive where I save almost all files.  Also, my current installation of Windows 7 has bloated quite a bit.  I have done some work to remove the bloat but I like the idea of starting over with a new copy of windows.

Since I am reinstalling windows I will need the windows license key.  Microsoft likes to make it difficult to find your license key.  It is not located in any convenient place unless you have the original packaging or purchased a computer with the OS already installed and the sticker is still on the computer case which would contain the windows key.  They probably claim to make it hard to find so that the key can not be easily stolen.  However, if this were truly the case the key would not be placed on a sticker on the outside of many store bought cases.  I personally believe they make it difficult to find the key so that it is hard to keep track of and then many users will just buy a new copy or if they are buying a new computer opt with bundling the OS with the new computer.

One way to find your windows license key is to use a VB script to read it from your registry and then decode it.

Below is the script I used which I obtained from howtogeek.com.
Direct link to article: http://www.howtogeek.com/206329/how-to-find-your-lost-windows-or-office-product-keys/
This article has much more information on this process, and also other processes for recovering your windows key such as third party software.

Paste the code into a text document, save the file with a “.vbs” file extension, close, and then double click the new file to run it.  It should present you with a popup window that contains your windows key.

Set WshShell = CreateObject(“WScript.Shell”)
MsgBox ConvertToKey(WshShell.RegRead(“HKLM\SOFTWARE\Microsoft\Windows NT\CurrentVersion\DigitalProductId”))

Function ConvertToKey(Key)
Const KeyOffset = 52
i = 28
Chars = “BCDFGHJKMPQRTVWXY2346789”
Do
Cur = 0
x = 14
Do
Cur = Cur * 256
Cur = Key(x + KeyOffset) + Cur
Key(x + KeyOffset) = (Cur \ 24) And 255
Cur = Cur Mod 24
x = x -1
Loop While x >= 0
i = i -1
KeyOutput = Mid(Chars, Cur + 1, 1) & KeyOutput
If (((29 – i) Mod 6) = 0) And (i <> -1) Then
i = i -1
KeyOutput = “-” & KeyOutput
End If
Loop While i >= 0
ConvertToKey = KeyOutput
End Function