SQL Server

SQL Server gets an ANSI compliant unique index … sort of

If you work with any of the other big database platforms you’ve probably noticed that SQL Server’s implementation of a unique index is “different” than the others.  Until now there hasn’t been a way to fix that without using a trigger.  Until now… Continued »

Keep your databases off the Internet

Over on the SecurityFightClub.com site I wrote a post about why database servers shouldn’t be on the Internet.

Denny

Data Transformation Services vs. SSIS: The key differences

I recently published an article on SearchSQLServer.com about DTS and SSIS where I talk about some of the differences.  Obvsiolly I didn’t cover all the differences between DTS and SSIS, just some of them.  So don’t go railing on me that I didn’t cover something.

Denny

SQL 2005 SP3 Failing to install with password length error

When installing SQL Server 2005 SP3 you may get an error about the password being to short, and it may have nothing to do with your sa password.  It’s because the Install Scripts are trying to create a certificate with a password which doesn’t meet your domain’s password requirements.

If you get this error navigate to the C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Upgrade folder.  Find the script for upgrading the SQL Agent.  Open the script and find the CREATE CERTIFICATE statement.  Make the password longer.  In my case I added 123456@ to the end of the password.  The password is in two different places in the script.

Then open Add/Remove Programs and select SQL Server 2005 and select Change.  Then run though the wizard to complete the installation of the service pack.

Denny

Understanding what the WITH ROLLBACK IMMEDIATE does

Based on some questions on forums and some of the responses that people have been giving there appears to be done misunderstanding about what the WITH ROLLBACK IMMEDIATE option does when added to the ALTER DATABASE command.  My goal here is to try and clear up at least some of the confusion. Continued »

Dates can easily be the hardest datatype to work with.

The datetime data type can be one of the hardest to work with when it comes to index optimization.  Most queries that use a datetime data type for filtering (part of the where clause) only want to match the date portion of the value.  Most people handle this via a convert function around the date column.  This causes the index that you create to become useless as the convert function causes the index to be scanned not seeked.

Continued »

SQL Server Clustering Guide & Webcast Links

A white paper which I wrote for Starwind Software is now available.  The link to register for the webcast (October 21, 2009) has also been put up.  The webcast and white paper go together, so I recommend that you grab the white paper now and I hope to see you at the webcast next month.

Denny

Whitepaper on why database maintenance is important

Today I’m publishing a white paper which I wrote for our Customer Service department to give to our customers. It explains why database maintenance is so important in keeping your database happy and healthy. Continued »

SQL Server 2008 Clustering Article in SQL Server Magazine

For those of you who haven’t check out this months SQL Server Magazine, the cover article was written by yours truly about Clustering SQL Server 2008 on Windows Server 2008.  This is an updated version of the article I did last December on Clustering SQL Server 2005 on Windows Server 2003.

I hope that you find it handy.  (Subscription is required.)

Denny

Difference between an Index and a Primary Key

There seams to be some confusion between what a Primary Key is, and what an Index is and how they are used.

The Primary Key is a logical object. By that I mean that is simply defines a set of properties on one column or a set of columns to require that the columns which make up the primary key are unique and that none of them are null. Because they are unique and not null, these values (or value if your primary key is a single column) can then be used to identify a single row in the table every time. In most if not all database platforms the Primary Key will have an index created on it.

An index on the other hand doesn’t define uniqueness. An index is used to more quickly find rows in the table based on the values which are part of the index. When you create an index within the database, you are creating a physical object which is being saved to disk. Using a table which holds employees as an example:

CREATE TABLE dbo.Employee
 (EmployeeId INT PRIMARY KEY,
 LastName VARCHAR(50),
 FirstName VARCHAR(50),
 DepartmentId INT,
 StartDate DATETIME,
 TermDate DATETIME,
 TermReason INT)

The EmployeeId is the Primary Key for our table as that is what we will use to uniquely identify an employee. If we were to search the table based on the last name the database would need to read the entire table from the disk into memory so that we can find the few employees that have the correct last name. Now if we create an index on the LastName column when we run the same query, the database only needs to load the index from the disk into memory, which will be much quicker, and instead of scanning through the entire table looking for matches, because the values in the index are already sorted the database engine can go to the correct location within the index and find the matching records very quickly.

Hopefully this will help sort out some of the confusion.

Denny