SQL Server: How to rename an index

When you create a table with a primary key in SQL Server, SQL Server creates an index for you with an automatically generated name, like PK__acctreg__29819341. I find the naming convention they use to be annoying, so I wanted to be able to change the name. Here's how:

-- to find the names of your current indexes
use my_database
select tab.name table_name, idx.name index_name
  from sysobjects tab
  left join sysindexes idx on idx.id = tab.id
 where tab.xtype = 'U'
 order by 1

-- to rename an existing index
exec sp_rename 'company.PK__company__1ED599B2', 'company$companyID', 'INDEX'

SQL Server: What service pack am I on?

Anyone remember the SQL Slammer virus? It turns out that it attacked SQL Server databases (and SQL Server DBA's) that were not protected by the latest Service Pack. I sit around and scare myself by asking myself questions like, what service pack am I on?

Here is a simple query to tell you what service pack you are on and some other interesting information about your SQL Server instance:

select serverproperty ('servername') [Server Name],
       serverproperty ('productversion') [Product Version],
       serverproperty ('productlevel') [Product Level],
       serverproperty ('edition') Edition,
       serverproperty ('licensetype') [License Type],
       serverproperty ('numlicenses') [# Licenses]

Of course you have to connect to the server you are interested in before running this query. The Service Pack level will be in the "Product Level" column.

SQL Server: How to show tables with a column name like...

In SQL Server, I often seek which table has a column with a name that I am not sure about. For example, I need the table and column name for a column like "current loan balance".

I use this query to find which table has a column name like...:

use loan_db

select tab.name table_name, col.name column_name
  from sysobjects tab
  left join syscolumns col on tab.id = col.id and tab.xtype = 'U'
 where col.name like '%current%balance%'
 order by 1,2

The only thing you have to work out then is the LIKE clause.

SQL Server: How to do a fuzzy search

I was asked yesterday to do a fuzzy search in SQL Server. The request was to find some numbers in a field that fell within a certain tolerance.

I worked out how to do this pretty simply using a BETWEEN clause with a fuzziness factor to go with it. In a simplified example, I'm looking for all employees within 3 years of 30. I parameterized both the age and the fuzz factor to make changes to this simple:

-- Here I just create a temporary table for the example.
create table #employee (employee varchar(32), age int)
insert into #employee values ('John', 32)
insert into #employee values ('Mary', 24)
insert into #employee values ('Sam', 40)
insert into #employee values ('Sarah', 28)
insert into #employee values ('Charles', 29)
insert into #employee values ('Henry', 31)

-- Here I declare variables to hold the age to look for
-- and the "fuzziness" of the search
declare @fuzz int
set @fuzz = 3
declare @seek_age int
set @seek_age = 30

-- This is the actual search
select employee, age
  from #employee e
 where e.age between @seek_age - @fuzz and @seek_age + @fuzz

Employee	Age
John	32
Sarah	28
Charles	29
Henry	31

Increasing the @fuzz value increases the possibility of finding records.

In the actual case, I used the BETWEEN clause as the joining clause in a left join to fuzzily join records. This looks pretty scary but works. The following example does this to create a list of employees matched to other employees within 3 years of the same age:

-- Find other employees within three years of an employee's age
declare @fuzz int
set @fuzz = 3

select test.employee test_employee, test.age test_age,
       fuzzy.employee fuzzy_employee, fuzzy.age fuzzy_age
  from #employee test
  left join #employee fuzzy
    on test.age between fuzzy.age - @fuzz and fuzzy.age + @fuzz
   -- here we don't want an employee to match him or herself
   and test.employee <> fuzzy.employee

test_employee	test_age	fuzzy_employee	fuzzy_age
John	32	Charles	29
John	32	Henry	31
Mary	24	NULL	NULL
Sam	40	NULL	NULL
Sarah	28	Charles	29
Sarah	28	Henry	31
Charles	29	John	32
Charles	29	Sarah	28
Charles	29	Henry	31
Henry	31	John	32
Henry	31	Sarah	28
Henry	31	Charles	29

A NULL result mean that that employee has no co-workers with 3 years of their age. Again, the @fuzz factor can be increased to find more matches.

SQL Server: How to list the columns in a table

This is a simple script to list the columns in a given table in SQL Server. It uses the SYSCOLUMNS, SYSTYPES, and SYSOBJECTS system tables:

use mydatabase

select left(col.name, 32) col_name, left(typ.name, 32) col_type, typ.length
  from syscolumns col
  left join sysobjects tab on tab.id = col.id
  left join systypes typ on typ.xtype = col.xtype
 where tab.name = 'TABLE NAME' and tab.xtype = 'U'

go

Bikram Philadelphia Studio

I had a great session this morning. The instructor this morning was Alex, who is currently my favorite instructor-- she seems to get me to do things today that I thought were weeks or months away. I worked a lot on my focus today. I had some difficulty because my left knee has really started to bother me. I think this is because I really upped my running schedule a few weeks back. But the advice I've gotten from Bikram's book is to go ahead and try to continue, but cautiously.

Bikram with a Polar Heart Rate Monitor

I run with a Polar Heart Rate Monitor sometimes. I have been curious for some time about what happens to my hear rate during Bikram, so I wore one during this morning's session. I told my instructor, Alex, what I was doing, and she suggested that I check my hear rate prior to and after the Camel Pose (Ustrasana). She predicted that my heart rate would go up a lot. She was right. Prior to the posture, my heart rate was 122 bpm. After it was 164. Anyway, here were my numbers for the session: Total time: 1:26 Average: 134 KCal: 818 Fat %: 40% I generally get numbers like this when I run about an hour and a half.

Perl script to split MP3 files

This is a simple script I used recently to split a large (50 minute) MP3 file into a series of smaller files. I used the MP3::Splitter module. MP3::Splitter is very versatile, so what I am doing does not use all of its functionality. But this does a good quick and dirty job of chopping up a file into bits. This comes in handy so you don't have a huge file to fast forward through on your iPod or other MP3 player.

The first two parameters of mp3split are the name of the file and a hash with options-- the only one I use is to set "verbose" to true. After that are a series of array references that describe each bit I want written out to a file.

The first parameter is the start time. I start the very first piece at 0 minutes. After that, each bit starts relative to the end of the last bit-- this is marked by the > symbol.

The next parameter shows how long the bit should be in seconds-- I chose to make each one two minutes long.

Since my MP3 file was about 50 minutes long, I just made 25 two minute bits. The last bit has a special marker as the finish: =INF. This means to use all of the rest of the file.

use MP3::Splitter;
mp3split('01-italian.mp3', {verbose => 1}, 
 ["0", "120"],
 [">0", "120"],
 [">0", "120"],
 [">0", "120"],
 [">0", "120"],
 [">0", "120"],
 [">0", "120"],
 [">0", "120"],
 [">0", "120"],
 [">0", "120"],
 [">0", "120"],
 [">0", "120"],
 [">0", "120"],
 [">0", "120"],
 [">0", "120"],
 [">0", "120"],
 [">0", "120"],
 [">0", "120"],
 [">0", "120"],
 [">0", "120"],
 [">0", "120"],
 [">0", "120"],
 [">0", "120"],
 [">0", "120"],
 [">0", "=INF"]
);

Session at Bikram Philadelphia

After swearing I'd get in to the studio (http://www.bikramphiladelphia.com) for a session all last week, I finally made it in today. Except for the half session in my office, it's been two weeks since my last session. Forgive me, Bikram, for I have sinned.... The session was good-- I actually get a little stronger when I take a break. Except this break was too long, so I'm actually a lot weaker. My one insight recently has been that you really have to relax between postures. My instructor, Joel, always says that Hatha Yoga is different partially because we relax completely between postures. But I really had not done it before. It just made me think about how Earl Campbell used to stay completely still until the entire pileup was finished when he got tackled. At first people would think he was hurt, but after he did it a zillion times everyone realized he was just having a rest. Part of the reason for relaxing completely between postures is to allow the effects of the postures to happen, as well. A lot of postures restrict part of the body on purpose-- then when you relax afterwards, that part is unrestricted. Kind of like wringing out a rag. And I was pretty wrung out after this morning's session.

Half session in the office

I've been having some trouble getting into the yoga studio, but I want my practice to go forward, so I tried doing something I've done a couple of times before: a half session on my own. Basically I did one set of each pose as opposed to two. It took me about 40 minutes. I felt great when I was done.

It was lacking some, though. There was no heat (which actually was not all that great of a problem!). And no one was egging me along, so I had to concentrate harder on not giving up a pose when it got difficult.

But I think this may be a good way to keep things going when I can't get to the studio.

By the way, I found this amazing video of Kristina Kireeva that made me realize that I could go a lot further in the Cobra Pose (Bhujangasana).