SQL
Over the years I have implemented a number of various "near me" style searches using third-party zip code databases and a long, complicated formula to find the distance between two different latitudes and longitudes. Well, starting with SQL Server 2008, it is possible to get the same result, with a lot less effort, following two simple steps. In this post I will walk through things step by step.
Read more...
Posted by Mitchel on Monday, January 23, 2012
For those of you that follow me on Twitter you might have heard my recent comments around a DotNetNuke SQL Script that I had to run that would have an impact of between 6 and 9 million SQL statements being executed on a database. As part of this project I was focusing a lot on SQL Server performance, and getting the best overall bang for the buck with the query. Sadly, given what I was working with a Cursor was needed to ensure that I had the best, most stable solution. In this post I'll share a little about the process and the lesson learned with cursors.
Read more...
Posted by Mitchel on Friday, September 23, 2011
One of the most common questions that I have been getting recently has been "what source control provider do you use and why?". This article is the first in a series of two articles about source control. This article takes a quick look at criteria that I believe is important to consider when it comes to evaulating source control systems for use. The next article in the series will be a review of the source control system that I use, in relation to the evaulation criteria that I list in this article.
Read more...
Posted by Mitchel on Wednesday, April 08, 2009
It seems that every client project I have started in recent months has involved a new form of complexity in SQL Server scripts. The most recent task that I had to tackle was with generating a comma separated list based on entries in a table. Now this was something that I needed for each and every row of a result set, so I didn't want to do the actual list creation in .NET. So, reaching back to some content and examples that I built for my "Creating Dynamic SQL Pivots" article which will be published in an upcoming edition of SQL Server I found the following helpful snippet of code.
Read more...
Posted by Mitchel on Friday, January 30, 2009
One of the biggest failure points I see with third-party DotNetNuke modules, including some of my first released modules were failures due to the lack of support for ObjectQualifier or DatabaseOwner values within the SqlDataProvider files. Although the process to add support for these two tokens is easy to add, it is very hard for those of us that like to test our database structures outside of DotNetNuke before we actually commit to building our modules. Thankfully, with a bit of research, I have found a way to build SQL Scripts for DotNetNuke using SQL Server Management Studio (SSMS) in a normal fashion and using a simple find/replace operation to bring everything into compliance with DotNetNuke integration. This post will explore the method that I use to accomplish this.
Read more...
Posted by Mitchel on Monday, January 26, 2009
One very common scenario in when working with User Interface database queries is the concept of a "Dynamic Search Query". A select statement that can have optional items in the where clause. There are a number of methods to implement this, however, they have varying levels of difficulty and, overall each have their own tradeoffs, in implementation, readability, performance, and accuracy. This article explores a few of the most common methods of doing this.
Read more...
Posted by Mitchel on Friday, January 09, 2009
One item that many people find unusual about my DotNetNuke installation guides is my insistance on NOT using the Database.mdf, dynamically attached database file for the creation of a DotNetNuke database. In my tutorials I have quickly dismissed the topic by noting that "issues" come up when trying to use a dynamically attached database and I...
Posted by Mitchel on Monday, November 24, 2008
Finally getting the opportunity to get back to SQL Server 2005 development, and coming to the conclusion that NOT all of my projects have to support SQL Server 2000 I started looking all of those "fun" new items that we all really wanted. This article is going to go through a scenario that demonstrates how to use Common Table Expressions, Rank() and Partition to get results for a very common data scenario. We will present this information with an introduction to the problem, scripts to setup the problem in a test environment, and lastly the implementation, with a review/summary at the end.
Read more...
Posted by Mitchel on Saturday, November 01, 2008
Recently when working with a client I was asked how in a SQL Server database environment with multiple databases how we can prevent users from seeing all other databases on the server. Permissions are configured to restrict who can actually query the database, but in the end, individuals still can see the names of the other databases on the server, getting potentially an idea of who else you are hosting, or information that you just don't want to share. I have found many blog articles that talk about how to get around this, but all of them were a bit choppy, so I thought I would take a moment to walk through a demo of the process. This article walks through the entire scenario.
Read more...
Posted by Mitchel on Wednesday, October 15, 2008
Recently I was tasked with the creation of a large database system that consisted of a database table with 5 date colums, and a varchar primary key. This table was to hold upwards of 3.5 million records, and I needed to know exactly how much disk space was going to be needed to store not only the database, but also the index required to facilitate the search requirements. After looking for a number of different ways to do this, and many free third party tools I decided that the most simple way to do this with the tools that I had available was to generate my own method to populate a test database.
Read more...
Posted by Mitchel on Friday, September 12, 2008
Previous Page
1 of 3
Next Page