Category Archives: SQL Server

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

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

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

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)

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

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

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)

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