Partitioning was a feature added with Microsoft SQL 2005 to allow users to split up large tables across multiple storage locations. Partitioning can be applied to tables, indexes and indexed views. By partitioning the data across multiple locations you can speed up query times, reduce contention between queries, and improve overall performance under certain conditions….
Tag: SQL202
Partition Schemes
In my last article I started covering partitioning. I’m going to pick that up and continue with how to create your database to use multiple filegroups. Without multiple filegroups, you aren’t going to see how partitioning can really improve your database’s performance. Let’s set up the database that’s going to house our table of items…
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…
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…
NULL Values, Blank Values, and Table Design
Have you dealt with NULL yet? A field is referred to as NULL if it has not received a value. To specify the NULL-ability of a column using T-SQL, just add NULL after the datatype for the column. To specify that values are required, add NOT NULL. If you don’t specify NULL or NOT NULL,…
Comparing Query Performance
A significant portion of your time as a database programmer or administrator will be to improve the performance of queries. While there are several benchmarks you can compare two or more queries, the actual comparision of the two queries can lean you do make false assumptions. When you run a query, data and execution plans…
A Better sp_who2
If you’ve ever needed to work with sp_who2 to figure out what’s going on, then you know it would be nice to be able to sort the results, right? Well, if you use the following code, then you’ll get exactly that functionality. Enjoy! CREATE TABLE #who2 ( spid INT , [status] SYSNAME , [sid]…
Incomplete Tables
One of the things I’ve noticed while performance tuning is many of the tables in my current environment are incomplete. They all have columns defined, and they all have data, but they’re still missing something. Many are missing a primary key. You don’t always have to have a primary key, but when you’re joining against…