http://www.dind.com/blog/ms-sql-server/ en-us Fri, 18 May 2012 19:50:59 PDT Fri, 18 May 2012 19:50:59 PDT SiteCrafting.com GearBox 1.1 (beta) http://www.sitecrafting.com/blog/group-by-queries-ms-sql/ Back in the day, we worked on developing a specialty application that was basically a lead generation system with a database that stored over 200,000 records, with a potential for a lot more. The web application displayed numerous reports that calculated totals from disparate sources. We discovered that once our client began adding all their data that those reports were running slower and slower and slower. The problem was that we had one primary query to pull the records out, then, as the code looped through each record, several other queries were needed to calculate the disparate totals. That resulted in numerous database calls that slowed the entire web application. That's when Mike discovered MySQL Wizardry, that used the SUM(IF()) and the GROUP BY clause, problem solved.One way to improve the speed and efficiency of a dynamic page is to pull all the needed information that is to be displayed in one query, that's where the MySQL Wizardry comes into play. This wizardry performs cross tabulations, which are great for statistical analysis. If you're planning on creating reports, I strongly suggest you read this article.Ever since we discovered this methodology, I've used it on numerous specialty applications, from calculating amounts and costs for a shopping list to determining totals for outstanding invoices.Below is an example of the MySQL Wizardry query.MySQL Query SELECT SUM( IF( company_size = "large", 1, 0) ) as vLargeCount, SUM( IF(company_size = "medium", 1, 0) ) as vMediumCount, SUM( IF(company_size = "small", 1, 0) ) as vSmallCount, SUM( IF( company_age < = 4, 1, 0) ) as vYoungCompaniesCount, SUM( IF( company_age > 4, 1, 0) ) as vOlderComaniesCount, COUNT(companies.id) AS vTotalCompanyCountFROM companiesThe key here is the SUM(IF()) statements, this simple little piece of query magic aggregated a number of different queries into one query and turned out to be extremely efficient. As a side note, it's also important to index fields that are are used for the LEFT JOIN, in this case campaign.id and contacts_mail.campaign.Now, the Pierce County Library was developed in ASP.NET 2.0 and MS SQL Server 2005, but I wanted to apply the MySQL Wizardry magic to MS SQL server. The problem was that MS SQL Server does not have an IF statement, but this was easily remedied by using the CASE statement. Below is an example of the MySQL Wizardry for MS SQL Server.MS SQL Server QuerySELECT movies.title, movies.date,COUNT(movie_reviews.review_id) AS vReviewCount, SUM(CASE movie_reviews.thumbs WHEN "UP" THEN 1 ELSE 0 END) AS vThumbsUp, SUM(CASE movie_reviews.thumbs WHEN "DOWN" THEN 1 ELSE 0 END) AS vThumbsDown, FROM  movies LEFT OUTER JOIN movie_reviews  ON movies.code = movie_reviews.movie_codeGROUP BY movies.title, movies.dateORDER BY movies.date DESCVoil�, SUM(IF()) is replaced by SUM(CASE) and we now have MS SQL Server Wizardry. There are two things that are important to note about this query. First is that MS SQL Server GROUP BY queries are much more restrictive than MySQL's GROUP BY functionality. Each data field (NOT expressions) in the SELECT statement MUST BE in the GROUP BY clause. The second item is that MS SQL Server does not allow ntext, text, and xml data types in the SELECT statement when performing a GROUP BY. There may be a way to include these data types, but since I was short on time and didn't really need those fields and decided to tackle this problem at a later date.Tweet Tue, 24 Oct 2006 09:41:00 PDT http://www.sitecrafting.com/blog/ms-sql-server-2005-ntext-1/ How I discovered that text, ntext, and image data types have been deprecated and replaced by varchar(max), ntext(max), and varbinary(max).Recently, I needed to perform a query to update a number of links for the Pierce Count Library website. I thought I could use a simple REPLACE() string function, unfortunately, this turned out not to be the case. In order to update text and ntext datatypes using a query you're limited to a few functions, in my case I would be forced to use SUBSTRING() or UPDATETEXT().My original plan was to update all links in a field using a query like this:UPDATE web_pages SET web_pages.article1 = REPLACE( web_pages.article1, "http://bad_link//", "http://")Of course, when I tried this, I got an error that ntext datatypes are not allowed for the REPLACE function. So, I went online and begin searching for a function similar to REPLACE() that would work with ntext, that's when I found the msdn page for working with text, ntext, and image data types. I could have figured out a way to use SUBSTRING() or UPDATETEXT(), but I determined that it would be much quicker to just look for the records that contained the bad links and update them manually.Later, I decided to do some research on the subject, because I just couldn't believe that there wasn't a way to use simple string functions with text and ntext. That's when I came across an informit article about Date, Math and Text Functions in SQL Server 2000 that explains how string functions don't work with text and ntext. Undaunted, I continued researching some more and that's when I found out that text, ntext, and image data types have been depracated for SQL 2005 and replaced by varchar(max), ntext(max), and varbinary(max). In fact, in future releases text, ntext, and image will no longer be supported. View the list at Deprecated Database Engine Features in SQL Server 2005.If I had only known. I would have designed all the fields to use nvarchar(max) instead of ntext. This would have solved a couple of problems for me. The first problem is that ntext data types can't be used in GROUP BY clauses and the second I would have been able to update the bad links a whole lot quicker by being able to use string functions. And if there was a need, I would have been able to use string functions in select statements to manipulate the text. Fri, 27 Oct 2006 11:25:00 PDT http://www.sitecrafting.com/blog/to-full-search/ Here's a quick how-to on implementing Full-Text Searching using Microsoft SQL Server 2005. Originally, I planned on just using LIKE statements in the WHERE clause of an sql query, however, this would not be possible since, as I posted earlier in MS SQL Server 2005 text and ntext, that string functions do not work on text and ntext data types.That's when I remembered Joe's blog entry about Cross Table Content Search, which he also mentioned in the office a couple of times before his entry. After that, I've been wanting to implement the Cross Table Content Search, and did not get the opportunity until developing the search page for the Pierce County Library. So, I delved into applying Cross Table Content Search and began researching ways to do that for Microsoft Server 2005. There are basically three parts, adding full-text to the database, creating a stored procedure, and creating a dataset to use that stored procedure.Add Full-Text IndexingThe very first step is ensuring that full-text indexing has been enabled for the database. The instructions I read online mentioned that this should be enabled when a new database is created, but I found this not to be the case for both our local testing server and the live server.These are steps to use when using SQL Server Management Studio for Microsoft Server 2005.Enable Full-text. The link is a how to, please note that the "Use full-text indexing" is a check-box near the top.Enable A Table for Full Text Indexing. Following these will cause a wizard to start. The first part is selecting which fields from the table should be indexed.The second part is creating a catalog or using an existing catalog. You can give a new catalog any name you wish and all the indexed fields will be added to this catalog.There's also options for creating a job that runs to update the catalog at a certain date and time, but this can be ignored if you chose to update the catalog automatically.After the wizard completes, right-click the table and then click Full-Text Indexing->Start Full Population.The article Understanding SQL Server Full-text Indexing explains the wizard for SQL Server 200. Please look under the heading Enabling Full Text Indexing.Continue to enable each table that you wish to have Full-Text Indexing.Create a Stored ProcedureInitially, I intended to simply create a query in an asp.net 2.0 dataset and use a parameter for the search term. However, I quickly learned that queries and views can't use parameters when using the FREETEXT() and CONTAINS() predicates. Meaning Select * from kewl_gadgets Where CONTAINS(description, @search_terms) would fail. However, the parameter will work for a stored procedure.Here's a sample stored procedure:USE [kewlDatabase]GO/****** Object:  StoredProcedure [dbo].[spKewlSearch]    Script Date: 11/01/2006 17:18:14 ******/SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGO-- =============================================-- Author:        Ken Foubert-- Create date: 11/1/2006-- Description:    Full Text Search on kewl gadgets content-- =============================================CREATE PROCEDURE [dbo].[spKewlSearch]( @searchWords AS nvarchar(255) ) AS   SELECT kewl_gadgets.name, kewl_gadgets.item_no, kewl_gadgets.costFROM kewl_gadets LEFT OUTER JOIN kewl_gadget_features ON kewl_gadgets.gadget_number = kewl_gadgent_features.gadget_numberWHERE CONTAINS( (kewl_gadgets.name, kewl_gadgets.description), @searchWords) OR CONTAINS(kewl_gadget_features.*, @searchWords) returnThere are two ways to tell which fields that CONTAINS() should look at.List field names from a table that should be searched on. This list needs to have parenthesis and the fields need to be full-text indexed, ie (kewl_gadgets.name, kewl_gadgets.description) Use table_name.* to tell CONTAINS()  to do a search on all the fields that have been full-text indexed.Create a CONTAINS() predicate for each table you wish to search on.NOTE: When passing the @searchTerm values, please make sure that the words have quotes around them when using the CONTAINS(). @searchTerm = "kewl radios". You can parse out the search term to be @searchTerm = "kewl" and "radios" or @searchTerm = "kewl" near "radios", etc.This stored procedure is good for simple searches. You have an option of also using FREETEXT() which looks for words that are similar to the search terms by using a thesaurus.To learn more click CONTAINS or FREETEXT.In addition, with Full-text indexing you can get results that return a relevancy number by using CONTAINSTABLE or FREETEXTTABLE. For additional information take a look at Full-Text Search Developer InfoCenter.Create DatasetThe final step is to create a dataset or data adapter to use the stored procedure. I will not go into detail on how to do this. I leave that up to you. Fri, 03 Nov 2006 10:51:00 PST http://www.sitecrafting.com/blog/gui-mess-or-productivity-booster/ While writing up a review on a database tool I discovered today, I was inspired to spark a discussion about database GUIs in general. The value of GUI tools for administering database systems like MySQL has been a topic of much debate.The purist/traditional camp argues that a GUI imposes limitations on the user when working with the database. They will claim that it presents the user with a subset of the available features for a given database. They also suggest that the addition of another layer (a "presentation layer" perhaps?) increases security risks and can degrade performance.In the other camp we have supporters that argue a GUI can increase productivity tremendously. Repetitive tasks can be automated, complex commands can be assembled in seconds rather than minutes, and for new users the learning curve can become quite a bit more forgiving.I tend to lean towards the pro-GUI group, as it is hard to deny the productivity gains when using an admin tool that is well designed. However, I must acknowledge the importance of having command line experience. There are a number of reasons for this. For one, there will always be those complex queries and processes that a GUI designer will not have anticipated that you will have to crunch out manually. Perhaps more importantly, you will have a greater understanding of the inner-workings of whatever database you are working with. This knowledge can be essential when it comes to debugging and performing complex admin tasks (even from within a GUI).The nutshell version: I think GUIs are great, but use them and don't abuse them. Make sure you know how to perform a task without an admin GUI before you use one. It may not be fun or easy, but it will definitely pay off in the end.So now I pose the question to you - on which side of the line do you stand? Are thare any pluses/minuses to each side that haven't been mentioned yet? Wed, 03 Oct 2007 15:59:00 PDT http://www.sitecrafting.com/blog/got-api/ gotAPI.com is one of the most useful online resources I've come across, primarily because it places resources spread all over the internet into one simple site. I've been using this for quite some time, and have for the most part I have taken its usefulness for granted. Then it occurred to me that I might not be the only one that could find this tool useful (I know, it was a big 'DUH!' moment). So now I will share this gem with others...gotAPI acts as a portal to almost any API/language reference available on the internet. Wondering what that PHP string function is that splits a string into an array? Pour yourself a nice tall glass of gotAPI and find it in seconds. Or if JavaScript is more your thing, browse the DOM to find the function or property you need. The advantage is that it can all be accessed from here, no need to remember countless URLs or stumble through a poorly designed site to find the documentation page. Simply hit up the site and you're set.Customization is another advantage, with the tabbed interface allowing you to pull up multiple reference pages (all searchable with an expandable tree view to your left). Load up all the API's related to your current project finding an obscure function is now seconds away. Fantastic!The fact that I am still excited about this site after months of using it should tell you just how nifty it is. So stop wasting time reading blogs and go check it out!Link: gotAPI.com Mon, 31 Mar 2008 11:40:00 PDT