By Thomas Hundley on 12/13/2008 12:58 PM

SQL: Guids v Integers

I have heard many debates about using integers v unique identifiers as primary keys. When asked my opinion, as a consultant, the only correct as answer is this: “it depends”. There is no right or wrong answer and each situation must be examined on its own merits. I will state, however, that most people don’t have thorough enough knowledge of SQL  to understand the implications of using guids as primary keys, so their arguments quickly lose validity. The concepts are really quite simple.

Let’s start with discussing the difference between a primary key and a clustered index. When a primary key is created on SQL server, it is created as the clustered index by default. The clustered index is simply the physical sorting of the data. Many people don’t realize that the clustered index may be changed and it does not have to be the primary key. This affects index fragmentation and is directly related to the fill factor of the data pages. Fill factor is simply the percentage of each page...

Read More »

By Thomas Hundley on 7/12/2008 9:19 PM

A security update from MS broke the production server for the first time in who knows how long.  The automatic installation of the hotfix for KB948109 failed and messed the SQL services up.  I rebooted and tried to reinstall it and the same thing happened, although this time simply restarting SQL worked to fix it.

I'm going to look into this a bit later and get it fixed on but wanted to throw this up on the blog.  It also brought to light that I need to get a monitoring system put back on this server.  I used Alert Site in the past and they were great and were reasonably priced, so I'll probably go with them again.

Anyway, cheers for now... Tom Hundley Elegant Software Solutions, LLC

...

Read More »

By Thomas Hundley on 6/14/2008 8:48 PM

 

I only need to do this every so often, but I always forget how to and end up looking it up.  Thus, I'm posting it here for reference.

 

 

USE DatabaseName

GO

BACKUP LOG DatabaseName> WITH TRUNCATE_ONLY

DBCC SHRINKFILE(TransactionLogName>, 1)

Tom Hundley Elegant Software Solutions, LLC



...

Read More »

By Thomas Hundley on 6/14/2008 8:39 PM

Here is a script to rebuild all of your indexes on your database.  I find that indexes are often ignored once an application moves to production.  It's a good idea to evaluate your usage patterns and setup automated maintenance jobs to proactively rebuild indexes before fragmentation has a chance to adversely effect performance.  This is especialy important if you are using guids as primary keys and don't move the clustered index to a more logically sequential column. 

 

 -- Show fragmentation for all tables EXEC sp_MSforeachtable @command1="print '?' DBCC SHOWCONTIG('?')"



 

--Rebuild all indexes (note this method locks the tables while the indexes are being rebuilt)

USE [myDatabase]

 

DECLARE @TableName varchar(255)

 

DECLARE TableCursor CURSOR FOR

SELECT table_name FROM information_schema.tables

WHERE table_type = 'base table'

 

OPEN TableCursor

 

FETCH NEXT FROM TableCursor INTO @TableName

...

Read More »

By Thomas Hundley on 2/3/2008 4:12 PM

Did you know you can connect to SQLServer from your application using SSL?

Read More »

By Thomas Hundley on 2/3/2008 4:10 PM

Quick reference to the Microsoft SQL Server 2005 Whitepaper

Read More »

By Thomas Hundley on 4/21/2007 12:44 AM

Viewing and editing an existing SSIS package is limited to SQL Server Business Intelligence Development Studio (SSBIDS) or Visual Studio 2005 Team Suite (VSTS).

Read More »

By Thomas Hundley on 4/21/2007 12:42 AM

I used SQL Server Integration Services (SSIS) Import & Export Wizard from SQL Server Management Studio (SSMS) to create an SSIS package and saved it to the server. I beat my head against the wall trying to find it via SSMS after I saved it.

Read More »