Book I recently read: Accelerated C# 2005 by Trey Nash, Apress

This is a great book if you are already familiar with object oriented languages such as Java or C++.  Unlike some of the other bricks out there on C# this book assumes you know object oriented coding and glosses over some of the basics.  It get’s right to the stuff you need to get up and running with C# quickly.  I also liked the first few chapters that explain the Common Language Runtime (CLR) and the .Net Framework.  These chapters give you the knowledge you need to understand how the system internals work and what the CLR and the .Net Framework take care of for you.  After that, the book mixes some chapters on the basic syntax of C# with some advanced concepts.  The basics include a general C# syntax overview, working with strings, and detailed info on Structs, Classes, Objects and Arrays.  It also includes some more advanced topics such as Threading, Generics, Delegates, Events and Operator Overloading.  Coming from an object oriented background I loved this book.

Determining Table Sizes of Your DB in SQL 2005

I currently have a SQL 2005 database that seems larger than it should be.  It’s running about 5 gig, which is not very big, but once you add in all backups and such it is taking up too much space for my Virtual Server.  In SQL 2000 there was a HTML based page you could open to determine the size of you tables in number of rows and in KBs.  This page is nowhere to to be found in SQL 2005.  I did some research and came accross a great article on how to get this information here.  I will also detail how I handled it below:

First I created a table in my DB named TableSizes.  Here is the script for that:

CREATE TABLE [dbo].[TableSizes](

            [tablename] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

            [rows] [int] NULL,

            [reserved] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

            [reserved_int] [int] NULL CONSTRAINT [DF__TableSize__reser__33928E3B]  DEFAULT ((0)),

            [data] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

            [data_int] [int] NULL CONSTRAINT [DF__TableSize__data___3486B274]  DEFAULT ((0)),

            [index_size] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

            [index_size_int] [int] NULL CONSTRAINT [DF__TableSize__index__357AD6AD]  DEFAULT ((0)),

            [unused] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

            [unused_int] [int] NULL CONSTRAINT [DF__TableSize__unuse__366EFAE6]  DEFAULT ((0))

) ON [PRIMARY]

Next I created a the stored proc similar to the one in the article I mentioned above.  Except instead of using a temp table I’m storing the data in my TableSizes table.  I don’t have anything against temp tables I just like the idea of that data being saved of so I can use it later for reporting and such.  Here is the stored proc:

CREATE PROCEDURE dbo.spPopulateTableSize

AS

 

truncate table TableSizes

 

-- Populate the temp table...

EXEC sp_MSforeachtable @command1=

         "INSERT INTO TableSizes

           ([tablename],[rows],[reserved],[data],[index_size],[unused])

          EXEC sp_spaceused '?'"

  

-- Strip out the " KB" portion from the fields

UPDATE TableSizes SET

   [reserved_int] = CAST(SUBSTRING([reserved], 1,

                             CHARINDEX(' ', [reserved])) AS int),

   [data_int] = CAST(SUBSTRING([data], 1,

                             CHARINDEX(' ', [data])) AS int),

   [index_size_int] = CAST(SUBSTRING([index_size], 1,

                             CHARINDEX(' ', [index_size])) AS int),

   [unused_int] = CAST(SUBSTRING([unused], 1,

                             CHARINDEX(' ', [unused])) AS int)

  

-- Return the results...

SELECT * FROM TableSizes order by tablename

So now I can call this SP anytime I want and get a great view of the tables, and thier sizes, in my DB.  Hope this helps!

Select Method on a DataTable

I thought I would bring this up as an FYI.  This method is very useful for doing in memory searches of your DataTables.  The Select method takes a filter expression as a parameter and returns an array of DataRows that can be looped through.  It’s also a very easy way to determine if certain rows exist in your DataTable.  If you do the select and the length of the array returned is zero, then no rows exist and you can move on.  I’ve used it a few times and thought I’d pass it along.