COALESCE and ISNULL and data type precedence

13 years of SQL Server, and you still learn something new. I always thought COALESCE and ISNULL where the same (for 2 arguments) and would return the data type of the first argument. It turns out this is not the case. What do you think happens when you run "SELECT COALESCE('', GETDATE())" Run it and find out. Running "SELECT ISNULL('', GETDATE())" gives a different answer, and what I would have expected for both. See also http://msdn.microsoft.com/en-us/library/ms190309.aspx

A stored proc everyone needs - show space all tables

Everyone should have this stored proc (or something similar) in their database. Microsoft really should have built this in to their product. It shows all the tables and their sizes (including LOB data stored off page), and sorts the tables by size.

Aside from the obvious maintenance applications, this is the first thing I do when I work on an unfamiliar database. The largest tables are not always the most important (our 2nd largest table is a log file used only for troubleshooting), but that is the best place to start.



SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO





CREATE PROC [dbo].[pDBShowSpaceAllTables]
AS
DBCC UPDATEUSAGE(0)

DECLARE @t TABLE
(
id INT,
SchemaName VARCHAR(64),
TableName VARCHAR(64),
NRows INT,
Total FLOAT,
Reserved FLOAT,
TableSize FLOAT,
IndexSize FLOAT,
FreeSpace FLOAT,
LobUsed FLOAT,
LobTotal FLOAT
)

INSERT @t EXEC sp_msForEachTable 'SELECT
OBJECT_ID(''?''),
PARSENAME(''?'', 2),
PARSENAME(''?'', 1),
COUNT(*),0,0,0,0,0,0,0 FROM ?'

DECLARE @low INT

SELECT @low = [low] FROM master.dbo.spt_values
WHERE number = 1
AND type = 'E'

UPDATE @t
SET Reserved = x.r,
IndexSize = x.i
FROM @t T INNER JOIN (
SELECT id, r = SUM(si.reserved), i = SUM(si.used)
FROM sysindexes si
WHERE si.indid IN (0, 1, 255)
GROUP BY id
) x ON x.id = T.id

UPDATE @t SET TableSize = (
SELECT SUM(si.dpages)
FROM sysindexes si
WHERE si.indid < 2
AND si.id = T.id
)
FROM @T T

UPDATE @t
SET TableSize = TableSize + (
SELECT COALESCE(SUM(used), 0)
FROM sysindexes si
WHERE si.indid = 255
AND si.id = T.id
)
FROM @t T

UPDATE @t SET FreeSpace = Reserved - IndexSize

UPDATE @t SET IndexSize = IndexSize - TableSize

UPDATE @t
SET LobUsed = u.used_pages,
LobTotal = u.total_pages
FROM @t T
INNER JOIN sys.partitions p ON T.id = p.object_id
INNER JOIN sys.allocation_units u ON u.container_id = p.partition_id
WHERE u.type = 2

UPDATE @t SET Total = Reserved + LobTotal

SELECT
schemaname,
tablename,
nrows,
Total = LTRIM(STR(
total * @low / 1024.,15,0) +
' ' + 'KB'),
Reserved = LTRIM(STR(
reserved * @low / 1024.,15,0) +
' ' + 'KB'),
DataSize = LTRIM(STR(
tablesize * @low / 1024.,15,0) +
' ' + 'KB'),
IndexSize = LTRIM(STR(
indexSize * @low / 1024.,15,0) +
' ' + 'KB'),
LobUsedSpace = LTRIM(STR(
LobUsed * @low / 1024.,15,0) +
' ' + 'KB'),
LobTotalSpace = LTRIM(STR(
LobTotal * @low / 1024.,15,0) +
' ' + 'KB'),
FreeSpace = LTRIM(STR(
freeSpace * @low / 1024.,15,0) +
' ' + 'KB'),
Total AS Sort
FROM @t
ORDER BY Sort DESC

SELECT nrows = SUM(nrows),
Total = LTRIM(STR(
SUM(total) * @low / 1024.,15,0) +
' ' + 'KB'),
Reserved = LTRIM(STR(
SUM(reserved) * @low / 1024.,15,0) +
' ' + 'KB'),
DataSize = LTRIM(STR(
SUM(tablesize) * @low / 1024.,15,0) +
' ' + 'KB'),
IndexSize = LTRIM(STR(
SUM(indexSize) * @low / 1024.,15,0) +
' ' + 'KB'),
FreeSpace = LTRIM(STR(
SUM(freeSpace) * @low / 1024.,15,0) +
' ' + 'KB')
FROM @t

GO

/*I got annoyed always refreshing my FogBugz inbox to see new issues. FogBugz comes with a feature to e-mail you when a ticket is assigned to you. However new ticket don't go to me, they go to my boss first. And he like us to check the box periodically since he is always busy. So this checks the box for me so I don't have to do it. It creates a colored notification icon, and the colors change whenever a new ticket arrives. I could easily be adapted to a variety of other situations.*/

using System;
using System.Data.SqlClient;
using System.Drawing;
using System.Drawing.Imaging;
using System.Runtime.InteropServices;
using System.Windows.Forms;

namespace BugColor
{
static class Program
{
private static NotifyIcon ni;
private static Timer timer;
private static int count;

private static void menu_Close(object sender, EventArgs e)
{
ni.Visible = false;
Application.Exit();
}

public static Color HSVtoRGB(float h, float s, float v)
//see http://www.cs.rit.edu/~ncs/color/t_convert.html
{
if (0 > h || 360 < h)
{
throw new ArgumentOutOfRangeException(
"h",
h,
"Value must be within a range of 0 - 360.");
}

if (0 > s || 1 < s)
{
throw new ArgumentOutOfRangeException(
"s",
s,
"Value must be within a range of 0 - 1.");
}

if (0 > v || 1 < v)
{
throw new ArgumentOutOfRangeException(
"v",
v,
"Value must be within a range of 0 - 1.");
}

float r, g, b;

if (s == 0)
{
// achromatic (grey)
r = g = b = v;
}
else
{
h /= 60; // sector 0 to 5
int i = (int)(Math.Floor(h));
float f = h - i;
float p = v * (1 - s);
float q = v * (1 - s * f);
float t = v * (1 - s * (1 - f));

switch (i)
{
case 0:
r = v;
g = t;
b = p;
break;
case 1:
r = q;
g = v;
b = p;
break;
case 2:
r = p;
g = v;
b = t;
break;
case 3:
r = p;
g = q;
b = v;
break;
case 4:
r = t;
g = p;
b = v;
break;
default: // case 5:
r = v;
g = p;
b = q;
break;
}
}
return Color.FromArgb(
Convert.ToInt32(r * 255),
Convert.ToInt32(g * 255),
Convert.ToInt32(b * 255));
}

[DllImport("user32.dll", EntryPoint = "DestroyIcon")]
static extern bool DestroyIcon(IntPtr hIcon);
//See http://social.msdn.microsoft.com/Forums/en/winforms/thread/3dd02621-4d7c-470d-b16b-610b11f8213c

private static void timer1_Tick(object sender, EventArgs e)
{
var con = new SqlConnection("Data Source=OPTIMUS-PRIME;Initial Catalog=fogbugz;Trusted_Connection=yes");
var cmd = new SqlCommand("SELECT MAX(ixBug) FROM Bug", con);
SolidBrush brl, brc, brr;
const float inc = 37f;

try
{
con.Open();
int nextCount = (int) cmd.ExecuteScalar();
con.Close();
if (nextCount == count)
return;
count = nextCount;
brl = new SolidBrush(HSVtoRGB(((count - 1) * inc) % 360f, 1, 1));
brc = new SolidBrush(HSVtoRGB((count * inc) % 360f, 1, 1));
brr = new SolidBrush(HSVtoRGB(((count + 1) * inc) % 360f, 1, 1));
}
catch(SqlException)
{
brl = brc = brr = new SolidBrush(Color.Black);
}

var bmp = ni.Icon.ToBitmap();
var g = Graphics.FromImage(bmp);
g.FillRectangle(brl, 0, 0, 5, 16);
g.FillRectangle(brc, 5, 0, 6, 16);
g.FillRectangle(brr, 11, 0, 5, 16);
brl.Dispose();
brc.Dispose();
brr.Dispose();
//ni.Icon.Dispose(); Does not destroy the icon, bug in .NET Framework
DestroyIcon(ni.Icon.Handle);
var h = bmp.GetHicon();
ni.Icon = Icon.FromHandle(h);
}

///

/// The main entry point for the application.
///

[STAThread]
static void Main()
{
Application.EnableVisualStyles();
Application.SetCompatibleTextRenderingDefault(false);

var bmp = new Bitmap(16, 16, PixelFormat.Format24bppRgb);
ni = new NotifyIcon
{
Icon = Icon.FromHandle(bmp.GetHicon()),
Visible = true,
ContextMenu = new ContextMenu()
};
ni.ContextMenu.MenuItems.Add("Close", menu_Close);

timer = new Timer {Interval = 1000};
timer.Tick += timer1_Tick;
timer.Enabled = true;

Application.Run();
}
}
}

C#, almost replacing C++

Here is an article with information I was just looking for in my blog:
http://www.codeproject.com/KB/cs/genericnumerics.aspx

I guess I never blogged anything on this, because someone else wrote a good article first. This is one of the most frustrating things about C#, and now I will be able to find it next time.

If C# had generics that supported primitives in a way that makes sense, I would use C# for 90% of the code I write in C++. C# has a lot more useful stuff built in, I usually can accomplish the same thing with less code, and for almost anything, the memory manager is better. Yet well after 1 decade, they can't fix problems with the simplest arithmetic, so I am stuck in C++ land for anything that does anything complicated with numbers. And no, Java's generics are not better.

While I am ranting, multidimensional array access (especially return and passing arrays as a parameter) stinks. The C# way makes sense, but I can understand C++ not wanting all that overhead. Still, by now, you'd think they'd have a way of making this easy to write and fast for performance - not they I have any idea how to do that.

SQLite

I used a Sqlite database for a school project last night. It is more a substitute for Excel or Access than for SQL Server, and I am not sure how we would use it here, but it is generally useful, and something I wish I learned about years ago.

Some highlights:
1) Free, cross platform and easy to install. One .exe file for a Windows machine, or apt-get for Debian based Linux.
2) You can copy/backup/move a database as 1 file (like Excel and Access, unlike SQL Server).javascript:void(0)
3) Open source.
4) Command line interface only (no GUI). I found something called “Sqlite browser” but am not happy with it.

It has a steep but short learning curve:
1) If you want to work with a database file ‘mydata.db’, you type ‘sqlite mydata.db’ to get started. I am not sure what just typing ‘sqlite’ does, but it starts the program, and then you really cannot do anything until you exit (or at least I haven’t figured out how to do anything useful).
2) Creating a new database is similar, ‘sqlite filename.db’, where filename.db is the file name you want to use.
3) You can exit with ‘.exit’
4) You cannot use ‘SELECT TOP 100 * FROM FileMain’, you have to use ‘SELECT * FROM FileMain LIMIT 100;’
5) The command line interface allows line breaks, which is useful for large queries. You need to type a ‘;’ to process the line. This can be really frustrating at first, since if you have anything on the line, and you type .help or .exit and hit enter, you just get a line break, and you end up having to CTL-Alt-Delete the program unless you can figure out you need to type a ‘;’

Slow Rollback, Reboot fixed, why?

I posted this on SQL Server Central, where it will get more attention than it will here, but wanted to put a copy here as well.

Today I ran an 'emergency' script to fix a problem in production. The script does 40,000 updates, each of which fires a trigger that does 1 insert. I should have disabled the trigger first, but didn't (mistake).

This is a very slow (Pentium 4) server, but it has 2 CPUs. MaxDOP is set to 1 (disabling parallel execution), and hyperthreading is turned off in the BIOS.

After 5 minutes, I realized my mistake not dropping the trigger, and canceled my job, effectively rolling back the changes. I waited another 15 minutes, and as expected the CPU usage of the machine was at 50% during this time. I later found out it was not 1 CPU being used 100% as I expected, but both CPUs actually were used 50%, which I still don't understand. During this time, no one was able to use SQL Server on this machine to do anything, which was also unexpected.

I suggested rebooting, saying SQL would continue doing what it is doing after it rebooted, so logically this makes no sense, but I hate just waiting and not doing anything. After the reboot, I expected the database to take forever to recover, and I came back online almost instantly.

Can anyone tell me what happened and why?

SQL Server Dependencies are Fixed! (almost)

If you look at any good article on what is new in SQL Server 2008, buried at the bottom under a bunch of nonsense about spatial data types (I still haven't found anything I cannot do just as with standard data types, in the unlikely event I will ever need to) and other bell's and whistles, it is possible they will mention dependency tracking has changed. With the possible exception of the new date data types (again, not at the top of most lists of changes) this is the biggest improvement vs. SQL 2005.

For some strange reason, SQL Server's syntax always let you reference a non-existent table without giving you any warning, even though referencing a non-existent column in an existing table gave you a syntax error. Both of these should have been warnings, but should let you create your stored proc anyway, in case the non-existent reference exists at runtime.

What SQL 2008 fixed is it keeps track of the missing dependency - as opposed to early versions which just ignored it, and did not update a reference to it. SQL 2008 is still broken - references can still get out of sync. Before doing anything important, you can updated all the dependencies with:

SELECT
'EXEC sp_refreshsqlmodule N''' + s.[name] + '.' + o.name + ''';' AS [stmt]
, s.[name] AS [schema_name] -- schema name
, o.[name] AS [object_name] -- procedure, function, or view name
, o.[type] AS [object_type] -- type (P, FN, IF, TF, or V)
FROM sys.objects o
INNER JOIN sys.schemas s ON o.[schema_id] = s.[schema_id]
WHERE o.[type] IN ('FN','IF','TF','P','V')
ORDER BY
o.[type]
, s.[name]
, o.[name]

I apologize if you wrote this script - I did grab this from the Internet without noting the author.


You can then view all your "missing" dependencies by running:

SELECT s.name [schema], o.name, d.referenced_entity_name
FROM sys.sql_expression_dependencies d
INNER JOIN sys.objects o ON d.referencing_id = o.object_id
INNER JOIN sys.schemas s ON o.schema_id = s.schema_id
WHERE referenced_id IS NULL
ORDER BY s.name, o.name, d.referenced_entity_name


This is still broken in one more import way - if you have code like:

UPDATE T
SET Price = Price + 1
FROM Table1 T

You will get an unresolved reference to an unknown object 'T' rather than a correct reference to Table1. I believe you can change any update query to a syntax that will work correctly, and you can easily find all the places you used the "problematic" sytnax with the above query.


If you aren't entrenched in SQL, this change may sound trivial, but I have had problems with dependencies since SQL Server 7, and having this fixed is by far the biggest reason for me to use SQL 2008.