Friday, September 15, 2017

Creating Hash Values for Strings In MSSQL, DB2 UDB for LUW, and Oracle

It will often occur that we need to search a large character string in a relational database, something like an email address, postal address, etc.

For the most part, when we do such searches, we're searching for a sub-string within that large string of characters.

However, for instances where we know we need an equivalency search, we can make things more efficient by generating a hash of the string and then storing that in an indexed column.

This can be done in all 3 of the major RDBMS platforms.  Links below:

MSSQL uses the CHECKSUM() function.
DB2 LUW uses the DBMS_UTILITY.GET_HASH_VALUE() function.
Oracle uses the ORA_HASH function.

This article by Jeff Reinhard over at SQLServerCentral.com is what inspired me to look for the similar function in DB2 & Oracle, and it does a very nice job of explaining the use case for this using an e-mail address scenario.

If anyone knows how to do this in postgressql or MySQL, I'd love to have you add a few words or link in the comments. Thanks! :)

Wednesday, May 03, 2017

I had previous written very breifly about creating a report on disk space using WMI

A much better, and very concise instruction for querying disk information (including %free) can  be found here:
https://msdn.microsoft.com/en-us/library/aa394173(v=vs.85).aspx
This could be easily adapted to be driven from a list of servers in a text file, in a query, or received from something like a net view command

Write-Host "Drive information for MY_Server"

Get-WmiObject -Class Win32_LogicalDisk -ComputerName MY_Server |
    Where-Object {$_.DriveType -ne 5} |
    Sort-Object -Property Name | 
    Select-Object Name, VolumeName, FileSystem, Description, VolumeDirty, `
        @{"Label"="DiskSize(GB)";"Expression"={"{0:N}" -f ($_.Size/1GB) -as [float]}}, `
        @{"Label"="FreeSpace(GB)";"Expression"={"{0:N}" -f ($_.FreeSpace/1GB) -as [float]}}, `
        @{"Label"="%Free";"Expression"={"{0:N}" -f ($_.FreeSpace/$_.Size*100) -as [float]}} |
    Format-Table -AutoSize

OUTPUT:
Drive information for MY_Server

Name VolumeName FileSystem Description             VolumeDirty DiskSize(GB) FreeSpace(GB) %Free
---- ---------- ---------- -----------             ----------- ------------ ------------- -----
A:                         3 1/2 Inch Floppy Drive                        0             0      
C:              NTFS       Local Fixed Disk        False               59.9          5.83 9.74 
D:   Programs   NTFS       Local Fixed Disk        False                200         18.03 9.02 
E:   Product_1  NTFS       Local Fixed Disk        False                250         99.57 39.83
F:   Product_2  NTFS       Local Fixed Disk        False                 20          7.02 35.09
R:   Product_3  MVFS       Network Connection                         78.13         48.83 62.5 


Tuesday, March 21, 2017

Minimally Logged Operations in SQL Server

I'm posting this mostly so I can find it later :)
The following article has a great table that defines when operations will be minimally logged.
https://technet.microsoft.com/en-us/library/dd425070(v=sql.100).aspx

Here's the table, just in case the link goes away at some point in the future:

Table Indexes

Rows in table Hints Without TF 610 With TF 610 Concurrent possible
Heap
Any
TABLOCK
Minimal
Minimal
Yes
Heap
Any
None
Full
Full
Yes
Heap + Index
Any
TABLOCK
Full
Depends (3)
No
Cluster
Empty
TABLOCK, ORDER (1)
Minimal
Minimal
No
Cluster
Empty
None
Full
Minimal
Yes (2)
Cluster
Any
None
Full
Minimal
Yes (2)
Cluster
Any
TABLOCK
Full
Minimal
No
Cluster + Index
Any
None
Full
Depends (3)
Yes (2)
Cluster + Index
Any
TABLOCK
Full
Depends (3)
No

Creating Hash Values for Strings In MSSQL, DB2 UDB for LUW, and Oracle

It will often occur that we need to search a large character string in a relational database, something like an email address, postal addres...