Category Archives: SQL Server

Home-grown PCIE SSD VM vs. Azure (Update 12/7)

Posted on December 6, 2014 by Bob L

More testing today with larger database, multiplied the number of rows by 10x up to 1 billion rows in the test dataset.  The latest results indicate I/O about 3 times slower on an Azure D13 server using similar CPU/Ram specs … Continue reading →

Posted in Azure, Cloud, SQL Server | Tagged , , | Leave a comment

Solving Sudoku with T-SQL

Posted on May 9, 2014 by Bob L

Note: This is an update to the earlier post to include the query to actually display the solved puzzle (for the skeptics… ) Recently I had to make a short trip and took a look at a Sudoku puzzle in … Continue reading →

Posted in Problem Solving, SQL Server, SQL Tips and Techniques | Tagged , | 1 Comment

Normalizing time-series data using a table function in SQL

Posted on February 13, 2014 by Bob L

Recently I had to normalize quarterly, monthly, and ad-hoc reported metrics into daily rows in order to do correlative analysis. Below is the function that I came up with. The idea is to generate values that are in line with … Continue reading →

Posted in SQL Server, SQL Tips and Techniques | Tagged | Leave a comment

Moving Databases from One Instance to Another Painlessly (Attach Statement Generator)

Posted on November 23, 2013 by Bob L

If you’ve ever had a bunch of databases and need to just move them to another instance without having to manually do the attach, these two scripts may be helpful. I recently was faced with this situation and have learned … Continue reading →

Posted in SQL Server, SQL Tips and Techniques | Tagged | 2 Comments

Script to auto-add surrogate primary keys

Posted on September 7, 2013 by Bob L

Here’s an example of how to use information_schema tables to automatically add clustered primary surrogate keys select ‘alter table ‘ +  table_name + ‘ add ‘ + table_name + ‘_Id int identity;’ from INFORMATION_SCHEMA.tables t where not exists (select 0from … Continue reading →

Posted in SQL Server | Tagged | Leave a comment

Auto-discovery of data relationships minus referential integrity

Posted on August 30, 2013 by Bob L

My experience is that unfortunately far too many databases lack referential integrity in the schema. There are a few reasons for this, almost all of them bad. One of the worse is the “our application controls integrity” rationale with a … Continue reading →

Posted in SQL Server | Tagged | Leave a comment

Concept for a High-Performance Database (HPD)

Posted on August 24, 2013 by Bob L

Recently, I’ve been studying Microsoft PDW as part of an ETL integration project. This got me thinking about the distributed database computing paradigm and scaling out performance for SQL Server for OLTP purposes. PDW utilizes multiple compute nodes that are … Continue reading →

Posted in Fusion-io, SQL Server | Tagged | Leave a comment

Now at 5.11 GB/s SQL backup to Null/3.12 GB/s local backup with I/O Accelerators

Posted on October 30, 2012 by Bob L

This will be my last update for a while, but I did some more shuffling of the database files among the various PCIE SSD logical drives (HP I/O Accelerators which are HP-OEM Fusion-Io cards). See earlier post for info about … Continue reading →

Posted in Fusion-io, SQL Server | Tagged | 1 Comment

A couple of corrections on video

Posted on October 29, 2012 by Bob L

I should have said that 40 Gbe and 100 Gbe are not widely implemented rather than not available  – they are available. But, even 100 GbE is only adequate for 12 GBPS – and with Gen 3 PCIE SSD reading … Continue reading →

Posted in Fusion-io, SQL Server | Leave a comment