Oct 7 2009 7:50AM GMT
Posted by: mrdenny
DTS,
SSIS,
SQL Server 2008,
SearchSQLServer.com
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
Oct 5 2009 11:00AM GMT
Posted by: mrdenny
SQL Server 2005,
Installation,
SQL
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
Sep 17 2009 11:00AM GMT
Posted by: mrdenny
T/SQL,
Index Performance,
Index Scan,
Index Seek,
Indexing
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 »
Sep 14 2009 5:24PM GMT
Posted by: mrdenny
SQL Server 2008,
iSCSI,
Starwind,
Webcast
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
Sep 9 2009 1:55AM GMT
Posted by: mrdenny
SQL Server,
Index Performance,
Statistics,
Performance Problems,
SQL Server 2005,
SQL Server 2008,
SQL Server 2000,
White Paper
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 »
Sep 1 2009 6:31PM GMT
Posted by: mrdenny
Article,
SQL Server Magazine,
Clustering,
SQL Server 2008,
Windows 2008
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
Aug 31 2009 6:26PM GMT
Posted by: mrdenny
T/SQL
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