SQL 2000 : Useful TSQL

What is the name of the primary key?

DECLARE @VALUE NVARCHAR(255)
SET @VALUE= (SELECT NAME
               FROM SYSOBJECTS
              WHERE XTYPE = 'PK'
                AND PARENT_OBJ = (OBJECT_ID('MY_TABLE'))
            )
SELECT @VALUE

Check for the existence of column? (With drop statement too!)

IF EXISTS ( SELECT a.name, b.name
                  FROM sysobjects a
                  INNER JOIN syscolumns b
                  ON a.id=b.id
                  WHERE a.xtype='u'
                  AND a.name = 'MY_TABLE'
                  AND b.name= 'MY_COLUMN'
BEGIN
      ALTER TABLE MY_TABLE DROP COLUMN MY_COLUMN
END

Free Tool : JKFragmenter

JKFragmenter is a free tool that allows you to deliberately break up your files, scattering them all over the drive! I'm glad I found it, as I can emulate the situation on some servers I have come across recently.

Basically the data files on the forementioned servers were not presized so along with some bad autogrowth settings. To illustrate the performance impact this had on sql, I set up a database and deliberately fragmented the .MDF and .LDF files on the filesystem (I set the DB offline whilst I did so).

It is a command line tool, taking the form -
jkfragmenter -p 100 -s 500 filename
-p = fragment count to split file into. Default is 10. Use 0 or 1 to defragment.
-s = size in kilobytes, Default is 1000Kb (approx 1Mb)

Output and screenshot of usage -

c:\tools>jkfragmenter -p 10 "C:\Data\sql_tools.mdf"
Fragmenter v1.2, 2008 J.C. Kessels

Commandline argument '-p' accepted, parts = 10

Processing: C:\Data\sql_tools.mdf
File already exists.
Fragment list (before):
Extent 1: Lcn=17248693, Vcn=0, NextVcn=250
250 clusters, 1 fragments.
Fragmenting:
Largest gap: 28827794 - 29342048 (514254 clusters)
Moving 25 clusters from offset=0 to LCN=29084908
Largest gap: 29084933 - 29342048 (257115 clusters)
Moving 25 clusters from offset=25 to LCN=29213478
Largest gap: 28827794 - 29084908 (257114 clusters)
Moving 25 clusters from offset=50 to LCN=28956338
Largest gap: 12795714 - 12978718 (183004 clusters)
Moving 25 clusters from offset=75 to LCN=12887203
Largest gap: 13031538 - 13212410 (180872 clusters)
Moving 25 clusters from offset=100 to LCN=13121961
Largest gap: 28956363 - 29084908 (128545 clusters)
Moving 25 clusters from offset=125 to LCN=29020623
Largest gap: 29084933 - 29213478 (128545 clusters)
Moving 25 clusters from offset=150 to LCN=29149193
Largest gap: 29213503 - 29342048 (128545 clusters)
Moving 25 clusters from offset=175 to LCN=29277763
Largest gap: 28827794 - 28956338 (128544 clusters)
Moving 25 clusters from offset=200 to LCN=28892053
Largest gap: 3669806 - 3794003 (124197 clusters)
Moving 25 clusters from offset=225 to LCN=3731892
Fragment list (after):
Extent 1: Lcn=29084908, Vcn=0, NextVcn=25
Extent 2: Lcn=29213478, Vcn=25, NextVcn=50
Extent 3: Lcn=28956338, Vcn=50, NextVcn=75
Extent 4: Lcn=12887203, Vcn=75, NextVcn=100
Extent 5: Lcn=13121961, Vcn=100, NextVcn=125
Extent 6: Lcn=29020623, Vcn=125, NextVcn=150
Extent 7: Lcn=29149193, Vcn=150, NextVcn=175
Extent 8: Lcn=29277763, Vcn=175, NextVcn=200
Extent 9: Lcn=28892053, Vcn=200, NextVcn=225
Extent 10: Lcn=3731892, Vcn=225, NextVcn=250
250 clusters, 10 fragments.

Finished, 1 files processed.

Perfmon : Monitoring File Fragmentation

Fragmentation seems like such a simple problem. Take systems offline and DEFRAGMENT the drive with any one of a number of free tools. In the SQL server world, hopefully you're presizing data/log files, eliminating the need for autogrowth and fragmentation in the first place.

Defraggler or Conrig.exe can both show the fragmentation status of files, without performing the defragementation hence you can see if a drive is fragmented.

You can tell if fragmentation is affecting your disk throughput by monitoring the following counters -
LogicalDisk\Split IO/sec or PhysicalDisk\Split IO/sec

Technet : Examining and Tuning Disk Performance

Out of Memory Oddity

This function (well, a similar non obfuscated one) caused me issues today.

CREATE FUNCTION [dbo].[history] (@id INT) 
RETURNS @history TABLE  (row_num INT IDENTITY(0,1)
            ,history_id AS id + '|' + CONVERT(VarChar(20), row_num)
          ,history_title  VARCHAR(50))
AS 
BEGIN 

INSERT INTO @history(history_id,history_title)

SELECT a.value1 AS history_id
      ,a.title  AS history_title
FROM  mytable a
WHERE a.id = @id

RETURN

END

GO

It would throuw 'Out of memory' errors on SQL 2008. How could one function achieve this on a box with 8GB of memory? After a great deal of investigation, the below solution turned out to be the fix. The alculated column in table definition of the orignal was the issue so my reolution was to use 2 table variables. The fix had to work for clients running SQL versions 2000 - 2008.

CREATE FUNCTION [dbo].[history] (@id INT) 
RETURNS @history TABLE  (row_num  INT 
            ,history_id VARCHAR(50)
          ,history_title  VARCHAR(50))
AS 

DECLARE @histtemp TABLE (row_num  INT IDENTITY(0,1)
            ,history_id AS id + '|' + CONVERT(VarChar(20), row_num)
          ,history_title  VARCHAR(50))


INSERT INTO @histtemp (history_id,history_title)
SELECT a.value1 AS history_id
      ,a.title  AS history_title
FROM   mytable a
WHERE a.id = @id

INSERT INTO @history(row_num, History_id, history_id) 
SELECT row_num, History_id, history_id
FROM @histtemp 

RETURN

END

GO

SQL 2008 + : Prevent saving changes that require table re-creation

Adding a new column for a clustered primary key (don’t ask..) I came up against this on my development box.

This is sensible. After all, adding a new clustered primary key is going to need the whole table to change, a potentially lengthy operation.

Generating the script for the operation proves this as it -

1. renames the existing table
2. creates the new one with the additional column
3. copies the data back

(and deals with removing and recreating indexes and constraints before and after respectively)

The option to control Management Studio’s behaviour is here -

Tools -> Options > Designer > ‘Prevent saving changes that require table re-creation’

ref; Brian Knight : Sql 2008 Designer Behaviour Change

ALTER USER ... WITH LOGIN to fix orphaned users

sp_change_users_login is deprecated.

From sql 2005 SP2, ALTER USER .... WITH LOGIN comes into play to achieve the same, i.e. remapping orphaned users to logins

ALTER USER Username WITH LOGIN = LoginName

I like to keep usernames and logins name the same where possible, hence -

ALTER USER Doermouse WITH LOGIN = Doermouse

MSDN : ALTER USER


Here is what works in SQL 2000 / 2005 -

Lists usernames that are not mapped to logins
exec sp_change_users_login 'report'

Map db username to server login if names match -

exec sp_change_users_login 'update_one', 'username'

Maps db username to server login if names match, If no login exists, it creates one with the password given.

exec sp_change_users_login 'auto_fix', 'username' , 'password'

Links -

USP_FixUsers - Works for all users in a db
USP_FixOrphans - Works for all users in all dbs on a server
Mapping SQL Server Logins to Database Users
Fix Orphaned Users SQL 2005
MSDN : Sp_change_users_login
MSDN : Deprecated Database Engine Features in SQL Server 2008 R2

Virtualbox : Increasing the size of a Virtualbox drive

How to increase the size of a virtualbox drive (.vdi file). -

C:\VirtualBox> \"program files"\oracle\virtualbox\vboxmanage modifyhd xppro_workbench.vdi --resize 20000 
0%...10%...20%...30%...40%...50%...60%...70%...80%...90%...100%

C:VirtualBox>

Then have to go into VM OS and create a new partition in the space or expand a partition if the OS supports this (not the drive hosting the OS itself).

VirtualBox : Network Types

You have 4 types of Network in VirtualBox. But how to chose?
Here are the basics...

NAT
NAT (Netork Address Translation) means the virtual machines will have private IP addresses on the virtualBox virtual network
VMs are not available from other pcs networking but can initiate connections to outside resources (servers, internet etc)

Host :

Guest :

C:\>ipconfig Windows IP Configuration Wireless LAN adapter Wireless Network Connection: Connection-specific DNS Suffix . : Link-local IPv6 Address . . . . . : fe80::8c63:1859:487c:f577%10 IPv4 Address. . . . . . . . . . . : 192.168.0.9 Subnet Mask . . . . . . . . . . . : 255.255.255.0 Default Gateway . . . . . . . . . : 192.168.0.1 Ethernet adapter VirtualBox Host-Only Network: Connection-specific DNS Suffix . : Link-local IPv6 Address . . . . . : fe80::7804:1a06:c47b:f37d%17 IPv4 Address. . . . . . . . . . . : 192.168.56.1 Subnet Mask . . . . . . . . . . . : 255.255.255.0 Default Gateway . . . . . . . . . :

C:\>ipconfig Windows 2000 IP Configuration Ethernet adapter Local Area Connection: Connection-specific DNS Suffix . : IP Address. . . . . . . . . . . . : 10.0.2.15 Subnet Mask . . . . . . . . . . . : 255.255.255.0 Default Gateway . . . . . . . . . : 10.0.2.2 C:\>

Bridged Adapter

Bridged Adapter connects virtual machines via the host network card.
They are on the same subnet and get an IP from the same router/dhcp server that the host does.
VMs are effectively on the physical network.

Host :

Guest :

C:\>ipconfig Windows IP Configuration Wireless LAN adapter Wireless Network Connection: Connection-specific DNS Suffix . : Link-local IPv6 Address . . . . . : fe80::8c63:1859:487c:f577%10 IPv4 Address. . . . . . . . . . . : 192.168.0.9 Subnet Mask . . . . . . . . . . . : 255.255.255.0 Default Gateway . . . . . . . . . : 192.168.0.1 Ethernet adapter VirtualBox Host-Only Network: Connection-specific DNS Suffix . : Link-local IPv6 Address . . . . . : fe80::7804:1a06:c47b:f37d%17 IPv4 Address. . . . . . . . . . . : 192.168.56.1 Subnet Mask . . . . . . . . . . . : 255.255.255.0 Default Gateway . . . . . . . . . :

C:\>ipconfig Windows 2000 IP Configuration Ethernet adapter Local Area Connection: Connection-specific DNS Suffix . : IP Address. . . . . . . . . . . . : 192.168.0.3 Subnet Mask . . . . . . . . . . . : 255.255.255.0 Default Gateway . . . . . . . . . : 192.168.0.1

Host-only Adapter

VirtualBox acts as the router (via adapter vboxnet0) and allocates IP addresses to VMs
By default this is a private network although you can assign a gateway (to grant internet access) making a a Bridged Adapter anyway.

Host :

Guest :

C:\>ipconfig Windows IP Configuration Wireless LAN adapter Wireless Network Connection: Connection-specific DNS Suffix . : Link-local IPv6 Address . . . . . : fe80::8c63:1859:487c:f577%10 IPv4 Address. . . . . . . . . . . : 192.168.0.9 Subnet Mask . . . . . . . . . . . : 255.255.255.0 Default Gateway . . . . . . . . . : 192.168.0.1 Ethernet adapter VirtualBox Host-Only Network: Connection-specific DNS Suffix . : Link-local IPv6 Address . . . . . : fe80::7804:1a06:c47b:f37d%17 IPv4 Address. . . . . . . . . . . : 192.168.56.1 Subnet Mask . . . . . . . . . . . : 255.255.255.0 Default Gateway . . . . . . . . . :

C:\>ipconfig Windows 2000 IP Configuration Ethernet adapter Local Area Connection: Connection-specific DNS Suffix . : IP Address. . . . . . . . . . . . : 192.168.56.101 Subnet Mask . . . . . . . . . . . : 255.255.255.0 Default Gateway . . . . . . . . . :

Internal Network

VMs can see each other, but not the host or the outside world :(

Host :

Guest :

C:\>ipconfig Windows IP Configuration Wireless LAN adapter Wireless Network Connection: Connection-specific DNS Suffix . : Link-local IPv6 Address . . . . . : fe80::8c63:1859:487c:f577%10 IPv4 Address. . . . . . . . . . . : 192.168.0.9 Subnet Mask . . . . . . . . . . . : 255.255.255.0 Default Gateway . . . . . . . . . : 192.168.0.1 Ethernet adapter VirtualBox Host-Only Network: Connection-specific DNS Suffix . : Link-local IPv6 Address . . . . . : fe80::7804:1a06:c47b:f37d%17 IPv4 Address. . . . . . . . . . . : 192.168.56.1 Subnet Mask . . . . . . . . . . . : 255.255.255.0 Default Gateway . . . . . . . . . :

C:\>ipconfig Windows 2000 IP Configuration Ethernet adapter Local Area Connection: Connection-specific DNS Suffix . : Autoconfiguration IP Address. . . : 169.254.21.26 Subnet Mask . . . . . . . . . . . : 255.255.0.0 Default Gateway . . . . . . . . . :

VirtualBox - Change UUID

" Cannot Register the hard disk ‘harddisk_2.vdi’ {93476107-f8f8-44ae-83b9-e6be00432ab8} because a hard disk ‘harddisk.vdi’ with UUID {93476107-f8f8-44ae-83b9-e6be00432ab8} already exists. "

You cannot just copy hard drive files and expect them to work on the same VirtualBox instance. You need to change the UUID, like this -

C:\>cd\program files\oracle\virtualbox

C:\Program Files\Oracle\VirtualBox>vboxmanage internalcommands sethduuid "c:\!rd

\virtualbox\w2000_2.vdi"

UUID changed to: cb850c97-570f-4051-bebb-92951a90e6cf

Free Tool : FreshDiagnose

Just a quick post to share a free tool I found today.
I wanted to get a figure for disk throughput on my desktop and after browsing some tools ended up downloading FreshDiagnose. You do have to register to get an code to unlock it's functionality, but an email address and password is as much as you need to supply.

It can benchmark just about any aspect of your pc, the screenshot below shows the disk benchmarking for my laptop. It's free and I can see it being highly useful as my role increasingly requires me to look at different systems.