CREATE TABLE Big ( BigID UNIQUEIDENTIFIER DEFAULT NEWID() PRIMARY KEY CLUSTERED, BigStuff BINARY(5000) ) DECLARE @x INT SET @x = 1 WHILE @x < 100000 BEGIN INSERT INTO Big (BigStuff) VALUES (0xABCDEF) SET @x=@x+1 END

• Share This Post:
• 
• 
• 

Sometimes, especially when you're working with SQL Server, it's really handy to know the scale of a decimal number. The .NET version of decimal chews up 128 bits, i.e. 16 bytes. The first 12 of those bytes are used for the numbers (the "mantissa"). Taken all in sequence, when converted to decimal, they offer 28 digits of numeric information. So what about the other 4 bytes left over of the original 16? Well, two are totally unused, one is used for the sign, and another points to where the decimal...

• Share This Post:
• 
• 
• 

Today this question came up for a fair-sized database: Which tables don't have a primary key? Rather than click around forever in Enterprise Manager or Management Studio, I decided to write a query to quickly find all the offending tables: -- Find all tables that don't have a Primary Keyselect name FROM SysObjects WHERE xtype='U' AND id NOT IN (SELECT parent_obj FROM SysObjects WHERE xtype='PK') How does it work? SysObjects contains a row for every object in the database, including views, stored...

• Share This Post:
• 
• 
• 

SQL Server 2005 has lots of new tricks up its sleeve. During our user group meeting this past Wednesday night there was a question about how to use one such enhancement: the PIVOT statement. You can think of this like a two dimensional GROUP BY. It's the same sort of idea as Excel's pivot tables, or the Matrix control from Reporting Services. In this post we'll walk through the process of converting a simple query with JOIN and GROUP BY to use PIVOT. The sample code acts on the Customers, Orders,...

• Share This Post:
• 
• 
• 

Below is a question that was posted to AZGroups earlier this afternoon. I removed the database and table names because I'm downright anal about security :) Having table problems, and I am just stumped.... Our dev website started coming back with the following error when a user logs in: Microsoft OLE DB Provider for SQL Server error '80004005'Warning: Fatal error 605 occurred .... Microsoft's MSDN gives: ERROR_BAD_COMPRESSION_BUFFER 605The specified buffer contains ill-formed data.(http://msdn.microsoft...

• Share This Post:
• 
• 
• 

Here's an interesting place, part jail, part armory, where the Crown Jewels are currently displayed. The Tower of London: Lots of history behind this place, often involving imprisonment and beheadings. In happier news, as promised here's the ASP.NET TreeView sample, which is written in VB.NET in VS2005: Jonathan_Treeview.zip It demonstrates the new TreeView control in ASP.NET 2.0, how recursive routines work, and how to extend the standard TreeNode class to support enhanced functionality. ...

• Share This Post:
• 
• 
• 

Last week I had the pleasure of teaching an ASP.NET class in a little town called Weybridge, just outside of London. Being from the sun-soaked desert, it was quite a treat to spend time in a countryside so verdant. And the history of the city was enthralling. The class was held in a building over 200 years old, the town's old post office. It has now been renovated and is owned by a computer consulting firm: (The presence of the mail van is purely coincidental!) One of the students in my class gave...

• Share This Post:
• 
• 
•