THE SQL Server Blog Spot on the Web

Welcome to - The SQL Server blog spot on the web Sign in | |
in Search

Browse by Tags

All Tags » sql   (RSS)
Showing page 1 of 2 (16 total posts)
  • How would you do dynamic pivot SQL in Amazon Redshift? (This elegant script might be used in many other databases as well)

      Several days ago I have spent a few hours of my life figuring out how to do dynamic pivot in Amazon Redshift. To tell you the truth, I have expected much more from this DBMS SQL language. Redshift is based on Postgre SQL 8.0.2 ( which was released in 2005 !!!! ) Anything you would want for this, not too difficult task,  does not ...
    Posted to Maria Zakourdaev (Weblog) by Maria Zakourdaev on November 7, 2017
  • Automate ETL Testing: Cross-Server Data Comparison with PowerShell

    In collaboration with a current client over the past year or so, I've been working on the issue of Test Driven Development for ETL work, especially in the area of automated testing for data. He graciously agreed to allow me to rewrite some of the code we originally developed in Ruby as a set of PowerShell demo scripts and share it through ...
    Posted to Merrill Aldrich (Weblog) by merrillaldrich on November 22, 2015
  • Fetch once, write many times

    As I’ve said before these days I’m spending time working on Hadoop rather than SQL Server and one of my colleagues today alerted me to a rather cool feature Of Hive (in a nutshell Hive is the technology that makes enables us to write SQL statements against Hadoop). Check this out: Pretty cool, no? From a single tablescan I can summarise and ...
    Posted to Jamie Thomson (Weblog) by jamiet on May 12, 2015
  • SQL Server Denali Wallpaper

    The SQL Server team today tweeted (on their @SQLServer account) a SQL Server Denali Desktop Background, a.k.a. Wallpaper – and they were kind enough to let me post it here. Here is the regular version: And here is the Widescreen version: Enjoy!
    Posted to Argenis Fernandez (Weblog) by Argenis on June 9, 2011
  • Powershell, SMO and Database Files

    In response to some questions about renaming a physical file for a database, I have 2 versions of Powershell scripts that do this for you, including taking the database offline and then online to make the physical change match the meta-data. First, there is an article about this at  This ...
    Posted to Ben Miller (Weblog) by dbaduck on February 14, 2011
  • Debunking Kimball Effective Dates

    Those who are familiar with Ralph Kimball’s theories on data warehousing modelling may be familiar with his assertion that a type 2 dimension member record should have a StartDate and an EndDate that defines the effective period of that record. He outlines this approach in his paper Slowly Changing Dimensions, Types 2 & 3 from the October 2008 ...
    Posted to Jamie Thomson (Weblog) by jamiet on November 28, 2009
  • Override the optimizer or give it an easy task?

    When your query must always return in a certain amount of time, trusting the optimizer might not be the best choice. Suppose your select must return in one second no matter what, and it usually returns in 50 ms, but sometimes, once in 100K calls, it takes full five seconds to complete, and clearly such query does not meet the requirement to ...
    Posted to Alexander Kuznetsov (Weblog) by Alexander Kuznetsov on June 16, 2009
  • When correlation between columns fools the optimizer

    When two columns are correlated, it may fool the optimizer and cause it to choose a wrong plan. Here is a simple script that demonstrates it.The following script creates a table and populates some sample ...
    Posted to Alexander Kuznetsov (Weblog) by Alexander Kuznetsov on June 5, 2009
  • Set based calculation of products of several numbers.

    You can use SUM to calculate the sum of several numbers, but you cannot directly use set-based logic to calculate a product. Yet there is a very simple trick – you can use EXP(SUM(LOG(…))) and get a product of several numbers without a loop. The trick was originally posted on newsgroups by Tom Cooper. For example, suppose that you have to ...
    Posted to Alexander Kuznetsov (Weblog) by Alexander Kuznetsov on March 17, 2009
  • Defensive database programming: eliminating IF statements.

    The following pattern is quite common in database programming: IF EXISTS(some query) BEGIN   DO SOMETHING; END When such code runs in high concurrency situations, it may not work as expected. I will provide a repro when such logic fails 40% of the time. The following script provides a test table and attempts to ...
    Posted to Alexander Kuznetsov (Weblog) by Alexander Kuznetsov on November 27, 2008
1 2 Next >
Privacy Statement