OK, you know how to set up partitioning for a table and an index, and now you think you’ve solved your deadlocking, and your I/O issues forever, right? Hardly. Once you’ve been in SQL long enough you learn that no answer is forever. Inevitably you’ll have to revisit your old queries and see if their…
Tag: DBA
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…
Introduction to Master Data Services
You find yourself sitting in front of your machine late one evening. Everyone has gone home for the night except for you. You’ve left a scan running to identify all the SQL Servers on your network, and two new ones appear. accountingSQL and humanResourceSQL. You begin to document these two new servers, and notice the…
What is SQL Azure?
I’ve been getting this question or some variant of it for weeks now. Some people are confusing SQL Azure with SQL 2008 R2, and some are thinking it’s SQL 2010. It’s neither of those. It’s its own beast. SQL Azure is a cloud version of the database management system you’ve all come to know and…
Migrating Databases to Azure
When converting a database from an older version of Microsoft SQL to Azure, there will be many gotchas along the way. I’d like to help you learn from the troubles I had along the way, hopefully sparing you a bit of time that was lost during my first conversion. Getting Started I’m going to assume…
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…
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…
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. …
SQL 102 – UNIQUE INDEX
Ok, you understand how to create an INDEX. You understand the differences in CLUSTERED and NONCLUSTERED indexes. Did you know you could index a view? You can. But before you start adding indexes, just like you would for a table, you’ll have to create an UNIQUE CLUSTERED INDEX on that index. The reason for that…