While 99% of what you do in SQL is supported in SQL Azure, there is a small list of things you’ll have to redesign, or at least reconsider before implementing your code on SQL Azure. The following is a list of items that are well documented, but I want to create a more complete list. …
Tag: DEV
SQL 102-SELECT * Overhead
How many of you have heard me rant against using “SELECT * FROM tableName”? If you have I’m sure you’ve heard me mention the following reasons why SELECT * is evil. the more data you ask for, the longer it takes to return that data extra paging table locking hinders future attempts to create a…
Microsoft Continues to Support PHP
Microsoft continued their support of PHP by releasing a Community Technology Preview for the SQL Server Driver for PHP 2.0. With this release PHP developer can use PHP Data Objects with the SQL Server driver! This will serve to reduce the complexity of targeting multiple databases on the back end. As I’ve noticed before, Business…
Equals Sub Queries Versus Exists Sub Queries
I’d like to introduce you to the idea that you can replace an equal sub query with an exists sub query. Doing this can change a seek operation to a scan operation. This usually results in a faster query, with fewer resources used. Check out this example using the adventureworks database. USE AdventureWorks GO –…
Sorting Numbers in VarChar field
Over at Experts-Exchange, I saw a question I get every once in a while. It all has to do with how SQL Server orders data in a VARCHAR column. SQL Server tries to sort a VARCHAR column in a dictionary order, 0 comes before 1. The problem is you may have mixed numbers in your…
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…
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…
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…