Now that many of us are upgrading to SQL 2008, we need a new sample database. If you’ve obtained a copy of SQL 2008 Developer Edition, if not, check out Amazon! I got my copy for $45! After you’ve obtained your copy, go to the link below and grab your sample databases! Reference: http://msftdbprodsamples.codeplex.com/releases/view/37109
Category: Microsoft SQL
Sample Databases for R2
..or as I call him, Ar-too. In case you haven’t heard, SQL 2008 R2 has been released as a preview. If you have downloaded it, and are looking for a sample database, codeplex has come to save the day. Hit the link below and you can download their latest sample database. Enjoy! Reference: http://msftdbprodsamples.codeplex.com/releases/view/24854
Find Stored Procedures Referencing…
Time and time again, I find myself trying to find all the stored procedures that reference a specific database object. It usually comes around when I’m working on changes to a table’s structure, or I’m replacing a table with a view. I’ll usually reach for the following query (or something like it. 1: SELECT ROUTINE_NAME…
Code Review
I’d like to start a new series that will help you learn some of the techniques I’ve used in the past. Some of these techniques are useful, some aren’t. Using SQL is a constant learning process. You have to be willing to look at your solutions and throw out those ideas that no longer work…
INDEX ON partition_scheme_name, filegroup_name, and default
Consider the CREATE INDEX statement below: CREATE INDEX ix_tableName_columnName ON tableName (columnName) ON { partitionSchemeName (columnName) | fileGroupName | default } What’s it all about? Let’s take these one at a time, and dig in a little bit. partitionSchemeName (columnName) Before you can use this option, you have to have your partition scheme defined. Check…
ASC, DESC, and Filtered Views
I know I’ve covered quite a bit of the CREATE INDEX statement, but there is even more to learn. Today I want to cover ASC, DESC, and the WHERE clause for indexes. ASC | DESC By default, indexes are created in ascending (ASC) order. This is fine most of the time. You’re going to be…
SET IDENTITY_INSERT
Let’s say you have the following table on two servers, one in development and one in production. CREATE TABLE person ( personID INT IDENTITY(1,1) , firstName VARCHAR(50) , lastName VARCHAR(50) ) You’re about to implement a new query, but you need to test it out in development before you move it to production. The problem…
Disabling Constraints
Constraints are great for maintaining the integrity of the data in your database. I’m not a big fan of running your database without them. It just opens up the door for too many problems. But there are a few times where you’re going to need to be able to disable them. Let’s say you’re loading…
SQL 202 – FILLFACTOR and Indexes
FILLFACTOR specifies the percentage for how full the Database Engine should make the leaf level of each index page during index creation or rebuild. FILLFACTOR must be an integer value from 1 to 100. The default is 0. If FILLFACTOR is 100 or 0 (MS SQL treats these the same), the Database Engine creates indexes…
Referential Integrity
OK, I’ve talked about Primary Keys and Foreign Keys. Now let’s talk about Referential Integrity. Basically this just means that any Foreign Key value in one table has to have that same value defined in the table where that Foreign Key is the Primary Key. Let’s look at two tables, and try to explain this…