CMssqlCommandBuilder

public function applyJoin($sql,$join)
{
    if(trim($join)!=='')
        $sql=preg_replace('/^\s*DELETE\s+FROM\s+((\[.+\])|([^\s]+))\s*/i',"DELETE \\1 FROM \\1",$sql);
    return parent::applyJoin($sql,$join);
}

public function applyLimit($sql, $limit, $offset)
{
    $limit = $limit!==null ? (int)$limit : -1;
    $offset = $offset!==null ? (int)$offset : -1;
    if ($limit > 0 && $offset <= 0) //just limit
        $sql = preg_replace('/^([\s(])*SELECT( DISTINCT)?(?!\s*TOP\s*\()/i',"\\1SELECT\\2 TOP $limit", $sql);
    elseif($limit > 0 && $offset > 0)
        $sql = $this->rewriteLimitOffsetSql($sql, $limit,$offset);
    return $sql;
}

protected function checkCriteria($table, $criteria)
{
    if ($criteria->offset > 0 && $criteria->order==='')
    {
        $criteria->order=is_array($table->primaryKey)?implode(',',$table->primaryKey):$table->primaryKey;
    }
    return $criteria;
}

protected function createCompositeInCondition($table,$values,$prefix)
{
    $vs=array();
    foreach($values as $value)
    {
        $c=array();
        foreach($value as $k=>$v)
            $c[]=$prefix.$table->columns[$k]->rawName.'='.$v;
        $vs[]='('.implode(' AND ',$c).')';
    }
    return '('.implode(' OR ',$vs).')';
}

public function createCountCommand($table,$criteria,$alias='t')
{
    $criteria->order='';
    return parent::createCountCommand($table, $criteria,$alias);
}

public function createDeleteCommand($table,$criteria)
{
    $criteria=$this->checkCriteria($table, $criteria);
    return parent::createDeleteCommand($table, $criteria);
}

public function createFindCommand($table,$criteria,$alias='t')
{
    $criteria=$this->checkCriteria($table,$criteria);
    return parent::createFindCommand($table,$criteria,$alias);

}

public function createUpdateCommand($table,$data,$criteria)
{
    $criteria=$this->checkCriteria($table,$criteria);
    $fields=array();
    $values=array();
    $bindByPosition=isset($criteria->params[0]);
    $i=0;
    foreach($data as $name=>$value)
    {
        if(($column=$table->getColumn($name))!==null)
        {
            if ($table->sequenceName !== null && $column->isPrimaryKey === true) continue;
            if ($column->dbType === 'timestamp') continue;
            if($value instanceof CDbExpression)
            {
                $fields[]=$column->rawName.'='.$value->expression;
                foreach($value->params as $n=>$v)
                    $values[$n]=$v;
            }
            elseif($bindByPosition)
            {
                $fields[]=$column->rawName.'=?';
                $values[]=$column->typecast($value);
            }
            else
            {
                $fields[]=$column->rawName.'='.self::PARAM_PREFIX.$i;
                $values[self::PARAM_PREFIX.$i]=$column->typecast($value);
                $i++;
            }
        }
    }
    if($fields===array())
        throw new CDbException(Yii::t('yii','No columns are being updated for table "{table}".',
            array('{table}'=>$table->name)));
    $sql="UPDATE {$table->rawName} SET ".implode(', ',$fields);
    $sql=$this->applyJoin($sql,$criteria->join);
    $sql=$this->applyCondition($sql,$criteria->condition);
    $sql=$this->applyOrder($sql,$criteria->order);
    $sql=$this->applyLimit($sql,$criteria->limit,$criteria->offset);

    $command=$this->getDbConnection()->createCommand($sql);
    $this->bindValues($command,array_merge($values,$criteria->params));

    return $command;
}

public function createUpdateCounterCommand($table,$counters,$criteria)
{
    $criteria=$this->checkCriteria($table, $criteria);
    return parent::createUpdateCounterCommand($table, $counters, $criteria);
}

protected function findOrdering($sql)
{
    if(!preg_match('/ORDER BY/i', $sql))
        return array();
    $matches=array();
    $ordering=array();
    preg_match_all('/(ORDER BY)[\s"\[](.*)(ASC|DESC)?(?:[\s"\[]|$|COMPUTE|FOR)/i', $sql, $matches);
    if(count($matches)>1 && count($matches[2]) > 0)
    {
        $parts = explode(',', $matches[2][0]);
        foreach($parts as $part)
        {
            $subs=array();
            if(preg_match_all('/(.*)[\s"\]](ASC|DESC)$/i', trim($part), $subs))
            {
                if(count($subs) > 1 && count($subs[2]) > 0)
                {
                    $name='';
                    foreach(explode('.', $subs[1][0]) as $p)
                    {
                        if($name!=='')
                            $name.='.';
                        $name.='[' . trim($p, '[]') . ']';
                    }
                    $ordering[$name] = $subs[2][0];
                }
                //else what?
            }
            else
                $ordering[trim($part)] = 'ASC';
        }
    }

    // replacing column names with their alias names
    foreach($ordering as $name => $direction)
    {
        $matches = array();
        $pattern = '/\s+'.str_replace(array('[',']'), array('\[','\]'), $name).'\s+AS\s+(\[[^\]]+\])/i';
        preg_match($pattern, $sql, $matches);
        if(isset($matches[1]))
        {
            $ordering[$matches[1]] = $ordering[$name];
            unset($ordering[$name]);
        }
    }

    return $ordering;
}

protected function joinOrdering($orders, $newPrefix)
{
    if(count($orders)>0)
    {
        $str=array();
        foreach($orders as $column => $direction)
            $str[] = $column.' '.$direction;
        $orderBy = 'ORDER BY '.implode(', ', $str);
        return preg_replace('/\s+\[[^\]]+\]\.(\[[^\]]+\])/i', ' '.$newPrefix.'.\1', $orderBy);
    }
}

protected function reverseDirection($orders)
{
    foreach($orders as $column => $direction)
        $orders[$column] = strtolower(trim($direction))==='desc' ? 'ASC' : 'DESC';
    return $orders;
}

protected function rewriteLimitOffsetSql($sql, $limit, $offset)
{
    $fetch = $limit+$offset;
    $sql = preg_replace('/^([\s(])*SELECT( DISTINCT)?(?!\s*TOP\s*\()/i',"\\1SELECT\\2 TOP $fetch", $sql);
    $ordering = $this->findOrdering($sql);
    $originalOrdering = $this->joinOrdering($ordering, '[__outer__]');
    $reverseOrdering = $this->joinOrdering($this->reverseDirection($ordering), '[__inner__]');
    $sql = "SELECT * FROM (SELECT TOP {$limit} * FROM ($sql) as [__inner__] {$reverseOrdering}) as [__outer__] {$originalOrdering}";
    return $sql;
}