ISNULL vs. COALESE in T-SQL
As many SQL developers know, COALESCE and ISNULL can be used to achieve the same results. They both essentially check if a value is NULL and return something else if it is. Both functions are widely used from earlier versions of SQL to SQL Sever 2008 development. For anyone needing to brush up on their syntax, here are their MSDN definitions.
COALESCE
ISNULL
Some of the main advantages of using COALESCE over ISNULL are
On the flip-side, some of the advantages of using ISNULL are
Fixing the SQL 2000 MMC snap-in error
Earlier today, I was experiencing a SQL 2000 management console error. Unfortunately I have to maintain a SQL Server 2000 database in addition to developing a SQL Server 2008 database. The error stated "MMC could not create the snap-in". This prevented me from viewing merge and transactional replication status for my SQL 2000 servers. This error can be resolved by installing Microsoft's SQL 2005 backwards compatibility msi package. This sounds ridiculous, as any SQL 2005 package should have no effect on a SQL 2000 installation - but it works. This package can be found here:
http://www.microsoft.com/downloads/details.aspx?FamilyID=d09c1d60-a13c-4479-9b91-9e8b9d835cdc&displaylang=en
Access deleted datatable rows in VB.NET
Earlier today, I was working on a VB.NET project in Visual Studio 2008 where I really needed to access fields from user deleted data-table rows before accepting changes. Here's the full story that led me to researching that action. I have a form with a DataTable that contains the result of a rather simple SQL SELECT query. This DataTable is mapped to a DataGridView control on my form. I want the user to be able to add and delete rows (among other things) without making changes to my database until the confirm their actions in bulk. Because of this, I need to access my uniqueidentifier guid field for each deleted row upon saving changes. Here's essentially how I'm accomplishing this.
For Each dr As DataRow In dt.GetChanges.Rows
If dr.RowState = DataRowState.Deleted Then
Delete(dr("ID", DataRowVersion.Original))
End If
Next
You can access all changed rows in a datatable through the table's GetChanges row collection. Each row in that collection has a "state" which indicates what action was performed on that row. In this case, I'm only interested in rows that were deleted - so I compare each row's state to DataRowState.Deleted. The tricky part is accessing the deleted row's data. The conventional means of accessing datarow fields (row.Item("FieldName")) does not work in this case. You'll get an error stating "Deleted row information cannot be accessed through the row". You need to use the row field's overloaded function and pass in DataRowVersion.Original as a parameter (row("ID", DataRowVersion.Original)). This will access the deleted row's original data. Once your program calls the AcceptChanges method on the DataTable, however, these rows will no longer be available.
Indexes in SQL Server 2008
As many experienced database developers know, table indexes are essential for optimal performance when working with large sets of data. In fact, a program that retrieves data from a large database can be ground to a halt if no database indexing is done. Thankfully, working with indexes are somewhat easier in SQL Server 2008 than they were in SQL Server 2000. First, you'll want to analyze your database tables and any commonly used SELECT queries that are called by your program. One thing to keep in mind about indexes is that too many of them create overhead that will slow down UPDATE, INSERT, and DELETE statements. So - you want to be careful about how many indexes to create per table. Some of the best practices with a T-SQL index are the following.
Consult the Database Engine Tuning Advisor. This is an analytical program that comes with SQL Server 2008. Save some of your program's more commonly used SELECT queries as .sql files. Then run those .sql files against your database via Database Engine Tuning Advisor. It'll come up with some pretty good index and stats suggestions. It's wise to listen to what it suggests in many cases (although it'll often suggest too many indexes).
In your SELECT queries, analyze your WHERE clause and JOIN statements. The more commonly used fields in those two places deserve to be indexed. These should be in non-clustered indexes.
Make sure that your primary keys are indexed. Every table should have a unique primary key. SQL Server 2005/2008 will often generate indexes for those primary keys. In the event that those indexes are missing, create a clustered index on all primary key fields.
CTRL+L is your friend. When designing a query through SQL Server Management Studio, check it's execution plan. A lot of times this will show you a spot in your query that's really bottlenecking the overall execution of the command.
This should serve as guidance for index building.There are a lot of optimization options when it comes to SQL indexes, but I believe that these 4 tips will always lead you on the right path. If anybody has any other index strategies, I'd love to hear it in the comments section.
How to SEO Any WordPress Blog
Since this is a WordPress blog about software and web development, I thought it was only appropriate that the first post be about search engine optimization (SEO) specifically for WordPress blogs. With the popularity of WordPress, there are a number of blog topics out there relating to blog SEO. Some of these have great tips, some are outdated, and some are flat out overkill. Of the tips that I have followed - I've found the following to be most successful.
- Permalinks - The majority of your search engine traffic will point directly to specific posts or subpages within your blog. Having posts and subpages with search engine friendly URLs is extremely important. Google, Yahoo, Bing, et cetera do not think highly of a post with a URL like www.yourblog.com/post.php?p=548. Go to your permalink settings and change them to a custom structure that includes the post name (/%postname%/ is a pretty safe bet).
- Title Tag - You should have a keyword driven title tag, HOWEVER it should be proper English. A good structure is "Blog Title | Keyword driven description of your blog". Make sure whatever niche that your driving towards is referenced in the title in a format that makes sense. Do you make cheesecakes? "Rachel's Oven | New York style gourmet cheesecakes" would be an appropriate SEO title.
- Sitemap - Google really does care about this. Download and activate the "Google XML Sitemaps" plugin and it will do all of the dirty work for you.
- Robots.txt - You don't really want pointless comment feeds and other non-SEO nonsense to be indexed. Create a robots.txt file in your root directory that looks something like this:
User-agent: *
Disallow: /cgi-bin
Disallow: /wp-admin
Disallow: /wp-includes
Disallow: /wp-content/plugins
Disallow: /wp-content/cache
Disallow: /wp-content/themes
Disallow: /trackback
Disallow: /feed
Disallow: /comments
Disallow: /category/*/*
Disallow: */trackback
Disallow: */feed
Disallow: */comments
Disallow: /*?*
Disallow: /*?
Allow: /wp-content/uploads# Google Image
User-agent: Googlebot-Image
Disallow:
Allow: /*# Google AdSense
User-agent: Mediapartners-Google*
Disallow:
Allow: /*# digg mirror
User-agent: duggmirror
Disallow: /Sitemap: http://www.yourblog.com/sitemap.xml
- Cache - Website performance can really kill your search engine ranking. If it takes the googlebot a long time to retrieve any of your pages, it will penalize you. Install and activate the "W3 Total Cache" plugin. It's much simpler than the "WP Super Cache" and it accomplishes the same level of site performance improvement.
- Webhost - Choose your webhost wisely. Their performance issues can also damage your search engine ranking. This is why I would advise anyone to stay away from Dreamhost. There are several faster alternatives out there (maybe this will be it's own topic later).
- Simplicity - Keep your blogs simple. This may be the most important aspect of WordPress SEO. If you download and use a custom theme - analyze its files thoroughly. I've seen themes that have footer.php files that are encoded so that people don't delete the blog creator's name. Some of these footer.php files are over 500kb in size. That's absolutely ridiculous. If you don't know how to trim a theme's footer.php file, choose another theme. It's not worth the performance downgrade.
Those are what I've found to be the most important aspects to consider when optimizing your WP blog for search engines. If anybody has any additional advice, feel free to comment on this article. I'll update the list above as time goes on to prevent it from becoming outdated.