microsoft sql

t-sql

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

t-sql

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

t-sql

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

sql202

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

sql202

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

dev

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

SQL102

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

SQL301

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

t-sql

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,

Certification

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

Certification

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

Certification

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