Today, I am writing this blog because I did not have a chance to conduct the SQL training with the support team yesterday before I departed for a new adventure; I feel like I owe it to them to give them something. This is part one of the training session. This part is about SQL basics. Part two is about using patterns to gain efficiencies in your day-to-day work. Let’s start with part one: SQL Basics.
Continue reading SQL BasicsCategory: SQL
How to use SQL to Extract a Number from a String
Recently I had a need to extract the number from a string in the database. The string would be something like ‘Monitor 16″‘ or “16 inch Monitor”. I would need to get the size for various reasons including fees and reports, but no real good way of doing it. I have come up with the following Scalar SQL Function to do this very job.
Continue reading How to use SQL to Extract a Number from a String
SQL Function – Find a Value in a String of Values
In a table I have a field that you can use comma delimited value (example 1,2,3,22). When I tried to filtering use a “LIKE” statement on the value 2, I was being returned 2 and 22. ARG!
So what do you do? You create a simple function the returns a 1 or 0. 1 being successfully found and a 0 meaning failed to find. Seems simple enough? Well it was and the function can be easily tweaked to match the solution you need! Here is the function as I used it:
Continue reading SQL Function – Find a Value in a String of Values
MaxMind GEOIP Look-up for Microsoft SQL
Recently I implemented a GEOIP solution on a client site; however, the site was built on a Microsoft SQL database engine and I could not find a solution for looking up the country name easily. Through some quick Google searches I had tripped on the site for which I started at ( http://dev.maxmind.com/geoip/csv ). The site maxmind.com gave me the MySQL solution and the mathematical solution for creating the integer. Based on this information, I created my own function in MS SQL to retrieve me the calculated IP integer for easier use.
SQL Pagination
I have often encountered the culture of query for thousands of results, estimate the paging and then only display a small segment of the results that were retrieved from the database. This is particularly bad in a web environment. This results in the following issues:
- A large amount of data to be transferred from the database to the website
- Longer than necessary database queries
- Script Errors and timeouts
- Frustrated users/customers
Checking Active Process on Sql Server
I have needed to monitor the SQL servers a little more than usual. I am mostly concerned about memory and CPU usage. By properly setting our program names in the SQL connection string, I am able to find the offending applications a little easier. There are times, that I set the application and routine in the connection string when the offending process is not so obvious.
Here is my script that I use to view the offending SQL processes
SQL Query using SysProcess
use master
declare @memlimit as int, @cpulimit as int
set @memlimit = 100 — Memory Filter Limit
set @cpulimit = 200 — CPU Filter Limit
select spid, login_time, last_batch, hostname, program_name, memusage,cpu
from sysprocesses
where ( memusage > @memlimit or cpu > @cpulimit )
Once I have a list of offending processes, I seek and fix the code has the issue. If the process is a closed application like the BlackBerry® Mobile Data Server Connection Service, I check for updates or in rare cases set up a scheduled task to restart the service.