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.
I just published a new article over on SearchSQLServer.com about “Clearing the Windows page file and its effect on server performance“. In the article I talk about the pros and cons of telling Windows to clear out the page file when the server is rebooted.
Writing release scripts for a database is easy. What ever new objects you want you create you create, and what ever objects you need to change you change. Piece of cake. Rollback scripts however are a bit trickier since you need to know what everything should look like after the script is done. Continued »
When you create a column on a database, and you put that column on the right of the table the column shouldn’t be added almost instantly. However if you are assigning a default value to the column and setting the column to now allow NULL values the operation will take quite a lot longer. This is because when the operation happens the SQL Server has to write the values to each table.
If you allow the column to have NULL values then the database doesn’t have to write the values to the column so the operation completes very quickly.
Taking a full backup when doing major database upgrades is a great idea. However if you are taking differential backups this one off full backup will break the differential backup chain. Continued »
In theory the auto close setting is a great idea. Free up resources for other databases to use when all the users are finished with the database.
In practice, its not so great. If a single user connects and disconnects over and over (like say a single user using your website or even a few users using your website) every time a user connects the database has to be spun up and the data loaded into memory. When the user disconnects the data is removed from cache, and the connection to the file is closed. Then when the next user runs a query the file is opened again, and the data is loaded into cache.
This causes the users queries to run slower as the data must be pulled from disk each time instead of pulled from memory. Plus there’s the time spent spinning up the IO thread, and opening the file. And as I said last time, more time is bad. Plane and simple.
OK, that’s not entirely true, but it’s pretty damn close. The auto grow feature of SQL Server shouldn’t be counted on. It should be disabled, or at least used on as an emergency basis only.
Having the auto grow setting enabled will cause your database to grow when ever it needs to, not when you want it to. It will also cause fragmentation on the disk, as the physical database files will end up becoming fragmented as your various database all grow as they need fragmenting the files across the disks.
Look at your databases, and figure out how much they are growing. You’ll want to preallocate the space to the database so that the database space is allocated all in a single chunk on the disk. This will allow the disk to more easily load data from the disk into the buffer cache as all the data from a single database will be contiguous on the disks. If the databases and fragmented because of auto grow then as you are trying to load data from the disk, the disk will need to keep moving from place to place sporadically reading data from the disk which takes more time; and more time is bad.
Last night I presented two presentations for the Orange County SQL Server Users Group. I started with my Virtualization presentation and my Storage for the DBA presentations. The slide decks can be downloaded from those links.
When you detach a database from Microsoft SQL (I’m talking SQL Server 2005 and up here) the SQL Server automatically changes the NTFS permissions on the file so that only the user who told the SQL Server to detach the file has access to them. SQL Server does this to ensure that an unauthorized person isn’t able to access the data files. Continued »
This Thursday I’ll be speaking at the Orange County SQL Server Users Group. The group meets at the New Horizons Computer Learning Center in Anaheim, CA which is at 1900 S. State College Blvd Suite 100.
It’s right behind Angles stadium, you can’t miss it.
We haven’t decided on a topic yet, I’ll probably bring a few presentations with me, and let the group pick the topic from the list. After the presentation I’ll post what presentation we did, and the slide deck, etc for it.
The meeting starts at 7, but people show up as early as 6:30. There’s usually some pretty good eating, so drop on by. I’m pretty sure that they charge $5 at the door to cover food, etc.
See you there.