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…
SET TRANSACTION ISOLATION LEVEL
Like I’ve mentioned before, unless you use SET TRANSACTION ISOLATION LEVEL, the server will default to READ COMMITTED. What this means is by default, your queries will only be able to interact with records that have been committed. If another query is affecting those records, you’re not able to see them in any way. I…
SQL Session – DTS to SSIS
If you’re not already a member of the Charlotte SQL Server User Group, go now and sign up. This group is a great resource of knowledge and connections to the SQL Server community in Charlotte, NC. Their plan is to get together once a month and discuss a new and interesting topic on SQL Server….
SQL 102 – Indexes With Included Columns
I’ve shown you how to create indexes, CLUSTERED versus NONCLUSTERED indexes, today I want to build on that. Let’s say you have a lookup query that takes names, and returns an ID for that name, you can actually build your query in a way where it would look up that data using just an index. …
70-433:OUTPUT and IDENTITY
Let’s look at a scenario you’ll need to be able to handle if you hope to pass the 70-433. This scenario requires you to understand the IDENTITY property for a column as well as how to return the identities created for rows inserted. Let’s start by creating a table to store widgets. We want an…
70-432: Unique Constraints
Let’s cover another scenario that uses knowledge you’ll need in order to pass the 70-432. This time we’re going to work with constraints. We all know that you can declare a unique constraint on a column you where you want to be sure you won’t get any duplicates. But do you know how to deal…
70-432: Revoking Permissions
Today we’re going to cover a scenario you might face at work, one that’s also a topic you’d need to know in order to pass the 70-432 Database Administrator’s exam. If you have any questions in your preparation for the 70-432, let me know. I’ll be happy to help in any way! Now for the…