I’m beginning to see questions come up over “which should I use, SQL Azure or Azure Table Services?” The answer for now seems to be, “it depends.” Azure Tables seem to have better pricing. The scalability also seems to be a push. SQL Azure will be quicker to learn for most of us, since it…
Category: Microsoft SQL
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…
Converting VARCHAR to DATETIME
Recently a post was made on LinkedIn asking how one would convert a date stored in a VARCHAR field could be converted to a DATETIME so date math could be performed. Unfortunately, this problem is more common than it should be. If users are given a free-form field to enter data, they’ll often enter unreliable…
SQL Injection License Plate
While it would be nice to put my knowledge of SQL to work in such a way, I highly doubt this injection attack would do much to prevent getting a ticket. But it should make you consider the possibility that this code could do damage to one of your projects, should someone try to enter…
UNION, INTERSECT, and EXCEPT
Earlier today I was discussing methods you can use to differences in data sets between two tables or views. My colleague was discussing the usual method SELECT columName(s) FROM table1 t1 (READCOMMITTED) LEFT JOIN table2 (READCOMMITTED) ON t1.sharedKey = t2.sharedKey WHERE t2.sharedKey IS NULL While this is a perfectly cromulent way of finding out what…
SQL 201-Formatting Dates in Microsoft SQL
One of the more frequent questions I get for SQL Server is how to format a DATETIME into a specific date format. Here’s a summary of the different date formats that come standard in SQL Server as part of the CONVERT function. Following the standard date formats are some extended date formats that are often…
SQL 301 – GROUPING
In our previous lesson we covered WITH ROLLUP and WITH CUBE. During that lesson you may have noticed that when we saw the summary rows, the columns had NULL as the value. Hopefully you wondered to yourself, how do I know programatically which of the rows are summary lines and which are rows that just…
SQL 301 — WITH CUBE and ROLLUP
Have you been asked to summarize some data into a nice report for management? They want to see subtotals and grand totals you say? You don’t have to drop the results of your GROUP BY statement into a temp table, and summarize the subtotals yourself. The SQL ROLLUP and CUBE commands offer a valuable tool…
Sample Databases for SQL Azure
Ready to head into the clouds and try your hand at a version of SQL quite different from those you know today? Sign up for a SQL Azure account, then grab this sample database to start you off. It appears that Microsoft has finally explained why there is no performance clustering in their SQL product……