Monthly Archives: October 2011

RunCore mSata SSD on Dell precision laptops


What I was planning to write on this post is a total different story from the one you will read here. I was ready to describe in detail how to open the laptop, put the drive in and combine the speed of SSD with the capacity of traditional spin disks on your laptop.

Well, you won’t read anything about that here: despite being advertised as compatible on the product page, this mSata drive won’t work on Dell precision laptops. Full stop.

It’s still unclear to me if it is a firmware issue or a more troublesome problem. The fact remains: this product is not suitable for a Dell precision mobile workstation.

After reading this review, I decided to buy the 120 GB drive (RCP-V-T501B-MC) for my Dell precision M4600. I could find one only at MyDigitalDiscount, an online shop I didn’t know at the time. However, the item was showing as available and I placed the order on August 29.


Item price:    $ 359
Shipping cost: $  35
--------------------
Total:         $ 394

The next day, the item was showing as “available in 5-10 business days” and remained that way for three weeks. The product was actually sent my way on September 22 and arrived in Italy on September 27.

The Italian customs kept it until October 13 and I finally had the drive in my hands on October 14.


VAT and customs expenses: $  95
--------------------------------
Grand total:              $ 489

You can imagine my frustration when the drive didn’t work. You may be interested to know that I’m not alone: this thread on notebookreview.com proves that other people were tricked by RunCore’s compatibility claims.

I contacted the vendor to return the item and I requested a refund. I will keep you posted on the status of my request: it could tell much about this vendor’s credibility.

On the other hand I also contacted RunCore to see if the issue can be solved (I highly doubt it). No news to date: I will keep you posted on this one as well, for the same reasons as above.

UPDATE 2011-10-19: MyDigitalDiscont customer service contacted me today and looks like I can be refunded. I still don’t know if the refund will cover shipping costs (back and forth), but it’s a starting point. On the compatibility issue, they say that they received the list from RunCore, which I can believe, since it’s displayed on RunCore’s site.

UPDATE 2011-10-20: MyDigitalDiscount will refund the returned item’s cost completely and will also pay for the shipping of a new product in substitution. I will have to pay the shipping costs for the return and I will also pay the customs expenses for the old and new order.
I must say that I’m very pleased with their behaviour and, honestly, I can’t blame them for the compatibility issue.

UPDATE 2011-10-21: I contacted RunCore directly and they say that they will perform some compatibility tests shortly. I’m waiting for these tests to complete before arranging the return with MyDigitalDiscont: maybe there’s a way to make the drive work.

UPDATE 2011-11-02: RunCore support contacted me today and put an end to my expectations. The drive is NOT compatible with Dell Precision laptops and needs an hardware modification. Meanwhile, their product detail page still claims this drive’s compatibility with Dell laptops:

UPDATE 2011-12-01: RunCore finally decided that it was time to remove the part that claimed compatibility with Dell Precision laptops from their product page. I still wonder what would one do with this drive, as I don’t think there is any single compatible laptop on the market. Maybe this product was conceived future hardware. Go figure.

Concatenating multiple columns across rows


Today I ran into an interesting question on the forums at SQLServerCentral and I decided to share the solution I provided, because it was fun to code and, hopefully, useful for some of you.

Many experienced T-SQL coders make use of FOR XML PATH(‘’) to build concatenated strings from multiple rows. It’s a nice technique and pretty simple to use.
For instance, if you want to create a list of databases in a single concatenated string, you can run this statement:

SELECT CAST((
    SELECT name + ',' AS [text()]
    FROM sys.databases
    ORDER BY name
    FOR XML PATH('')
) AS varchar(max))

The SELECT statement produces this result:

allDBs
------------------------------------------------------------------
BROKEN,LightHouse,master,model,msdb,tempdb,TEST,test80,TOOLS,WORK,

Great! But, what if you had to concatenate multiple columns at the same time? It’s an unusual requirement, but not an impossible one.
Let’s consider this example:


-- =================================
-- Create a sentences table
-- =================================
DECLARE @Sentences TABLE (
    sentence_id int PRIMARY KEY CLUSTERED,
    sentence_description varchar(50)
)

-- =================================
-- Sentences are broken into rows
-- =================================
DECLARE @Rows TABLE (
    sentence_id int,
    row_id      int,
    Latin       varchar(500),
    English     varchar(500),
    Italian     varchar(500)
)

-- =================================
-- Create three sentences
-- =================================
INSERT INTO @Sentences VALUES(1,'First sentence.')
INSERT INTO @Sentences VALUES(2,'Second Sentence')
INSERT INTO @Sentences VALUES(3,'Third sentence')

-- =================================
-- Create sentences rows from 
-- "De Finibus bonorum et malorum" 
-- by Cicero, AKA "Lorem Ipsum"
-- =================================
INSERT INTO @Rows VALUES(1, 1, 
    'Neque porro quisquam est,',
    'Nor again is there anyone who',
    'Viceversa non vi è nessuno che ama,')
INSERT INTO @Rows VALUES(1, 2, 
    'qui dolorem ipsum quia dolor sit amet,',
    'loves or pursues or desires to obtain pain',
    'insegue, vuol raggiungere il dolore in sé')
INSERT INTO @Rows VALUES(1, 3, 
    'consectetur, adipisci velit, sed quia non numquam',
    'of itself, because it is pain, but because occasionally',
    'perché è dolore ma perché talvolta')
INSERT INTO @Rows VALUES(1, 3, 
    'eius modi tempora incidunt',
    'circumstances occur in which',
    'capitano circostanze tali per cui')
INSERT INTO @Rows VALUES(1, 3, 
    'ut labore et dolore magnam aliquam quaerat voluptatem.',
    'toil and pain can procure him some great pleasure.',
    'con il travaglio e il dolore si cerca qualche grande piacere.') 
INSERT INTO @Rows VALUES(2, 1, 
    'Ut enim ad minima veniam,',
    'To take a trivial example,',
    'Per venire a casi di minima importanza,')
INSERT INTO @Rows VALUES(2, 2, 
    'quis nostrum exercitationem ullam corporis suscipit laboriosam,',
    'which of us ever undertakes laborious physical exercise,',
    'chi di noi intraprende un esercizio fisico faticoso')
INSERT INTO @Rows VALUES(2, 3, 
    'nisi ut aliquid ex ea commodi consequatur?',
    'except to obtain some advantage from it?',
    'se non per ottenere da esso qualche vantaggio?') 
INSERT INTO @Rows VALUES(3, 1, 
    'Quis autem vel eum iure reprehenderit qui in ea voluptate',
    'But who has any right to find fault with a man who chooses to enjoy a pleasure',
    'O chi può biasimare colui che decide di provare un piacere')
INSERT INTO @Rows VALUES(3, 2, 
    'velit esse quam nihil molestiae consequatur,',
    'that has no annoying consequences,',
    'che non porta conseguenze negative,')
INSERT INTO @Rows VALUES(3, 3, 
    'vel illum qui dolorem eum fugiat quo voluptas nulla pariatur?',
    'or one who avoids a pain that produces no resultant pleasure?',
    'o che fugge quel dolore che non produce nessun piacere?')

The setup code creates two tables: Sentences and Rows. The first one is the master table, that contains the sentence_id and a description. The second one contains the actual sentences, broken into rows and organized with languages in columns.

For the purposes of this test, I inserted in the Rows table an excerpt of Cicero’s “De Finibus bonorum et malorum”, also known as “Lorem Ipsum”, the printing and typesetting industry’s standard dummy text since the 1500s.

Here’s how the input data looks like:

What we want to do is concatenate all the rows for each sentence, keeping the languages separated. It could be accomplished very easily concatenating each column separately in a subquery, but what if the input data comes from a rather expensive query? You don’t want to run the statement for each language, do you?

Let’s see how this can be done in a single scan:

SELECT sentence_id, sentence_description, Latin, English, Italian
FROM (
    SELECT Sentences.sentence_id, sentence_description, language_name, string 
    FROM   @Sentences AS Sentences
    OUTER APPLY (
        SELECT *
        FROM (
			-- =================================
			-- Create a Languages inline query
			-- =================================
                      SELECT 'Latin'
            UNION ALL SELECT 'English'
            UNION ALL SELECT 'Italian'
        ) Languages (language_name)
        CROSS APPLY (
			-- =================================
			-- Concatenate all the rows for 
			-- the current sentence and language
			-- from an UNPIVOTed version of the
			-- original rows table
			-- =================================
            SELECT sentence_id, string = (
                SELECT string + ' ' AS [data()] 
                FROM @Rows AS src
                UNPIVOT ( string FOR language_name IN (Latin, English, Italian) ) AS u
                WHERE sentence_id = Sentences.sentence_id
                    AND language_name = Languages.language_name
                ORDER BY row_id
                FOR XML PATH('')
            )
        ) AS ca
    ) AS oa
) AS src
-- =================================
-- Re-transform rows to columns
-- =================================
PIVOT ( MIN(string) FOR language_name IN ([Latin],[English],[Italian])) AS p

If you don’t like PIVOT and UNPIVOT, you can always use CASE expressions to create a crosstab.
Here’s the final result:

With a little of PIVOT, UNPIVOT and FOR XML you can achieve really surprising results, you just need to unleash your creativity.

Formatting dates in T-SQL


First of all, let me say it: I don’t think this should ever be done on the database side. Formatting dates is a task that belongs to the application side and procedural languages are already featured with lots of functions to deal with dates and regional formats.

However, since the question keeps coming up on the forums at SQLServerCentral, I decided to code a simple scalar UDF to format dates.

/*
 * AUTHOR: Gianluca Sartori @spaghettidba
 * Returns a data formatted according to the format String.
 * The format string can contain the following tokens in any order:
 *
 * yy	--> Year, two digits
 * YYYY	--> Year, four digits
 * MM	--> Month, two digits
 * m	--> Month, one digit
 * DD	--> Day, two digits
 * d	--> Day, one digit
 * HH	--> Hour, two digits
 * h	--> Hour, one digit
 * NN	--> Minute, two digits
 * n	--> Minute, one digit
 * SS	--> Second, two digits
 * s	--> Second, one digit
 * AP	--> AM/PM
 * 
 * Any character not in the token list gets concatenated
 * to the string and left untouched.
 *
 * EXAMPLE: 
 * SELECT dbo.formatDate(GETDATE(), 'YYYY-MM-DD hh:nn:ss')
 * OUTPUT: 2007-01-25 17:35:21
 *
 * SELECT dbo.formatDate(GETDATE(), 'DD-MM-YYYY')
 * OUTPUT: 25-01-2007
 */
CREATE FUNCTION [dbo].[formatDate](@date as datetime, @format_string as varchar(50)  )
RETURNS varchar(50) 
AS  
BEGIN 
    DECLARE @format varchar(50)
    DECLARE @result AS varchar(50)
    DECLARE @iter AS int
    DECLARE @prevchar AS char(1) 
    DECLARE @currchar AS char(1) 
    DECLARE @currtoken AS varchar(4)
    

    SET @iter = 1
    SET @result = ''
    SET @format = CONVERT(varchar(50),@format_string) COLLATE Latin1_General_CS_AS

    WHILE @iter <= LEN(@format)
    BEGIN
        SET @currchar = CONVERT(char(1),SUBSTRING(@format,@iter,1)) COLLATE Latin1_General_CS_AS
        IF @currchar <> @prevchar OR @iter = LEN(@format)
        BEGIN
            SET @currtoken = 
                CASE (@prevchar) COLLATE Latin1_General_CS_AS -- Use a case-sensitive collation
                    WHEN 'Y' THEN RIGHT('0000' + CAST(YEAR(@date) AS varchar(4)),4)
                    WHEN 'y' THEN RIGHT('00' + CAST(YEAR(@date) AS varchar(4)),2)
                    WHEN 'M' THEN RIGHT('00' + CAST(MONTH(@date) AS varchar(2)),2)
                    WHEN 'm' THEN CAST(MONTH(@date) AS varchar(2))
                    WHEN 'D' THEN RIGHT('00' + CAST(DAY(@date) AS varchar(2)),2)
                    WHEN 'd' THEN CAST(DAY(@date) AS varchar(2))
                    WHEN 'H' THEN RIGHT('00' + CAST(DATEPART(hour,@date) AS varchar(2)),2)
                    WHEN 'h' THEN CAST(DATEPART(hour,@date) AS varchar(2))
                    WHEN 'N' THEN RIGHT('00' + CAST(DATEPART(minute,@date) AS varchar(2)),2)
                    WHEN 'n' THEN CAST(DATEPART(minute,@date) AS varchar(2))
                    WHEN 'S' THEN RIGHT('00' + CAST(DATEPART(second,@date) AS varchar(2)),2)
                    WHEN 's' THEN CAST(DATEPART(second,@date) AS varchar(2))
                    WHEN 'A' THEN CASE WHEN DATEPART(hour,@date) >= 12 THEN 'PM' ELSE 'AM' END
                    WHEN ' ' THEN ' '
                    ELSE RTRIM(@prevchar)
                END
            SET @result = @result + @currtoken
        END
        SET @prevchar = @currchar COLLATE Latin1_General_CS_AS
        SET @iter = @iter + 1
    END
    RETURN @result
END

Let’s see this function in action:

SELECT dbo.formatDate(GETDATE(), 'YYYY-MM-d h:NN:SS AP')
-- RETURNS: 2011-10-5 18:07:09 PM
SELECT dbo.formatDate(GETDATE(), 'YYYY-MM-DD HH:NN:SS')
-- RETURNS: 2011-10-05 18:07:09

The code is simple and (I hope) clear enough. It’s not intended to be the best way to format dates in T-SQL and, honestly, I hope it contains some nasty hidden bug, because you shouldn’t be using this code at all!

For more information on custom date formats in SQLServer, see this post: SQL Server and custom date formats

Follow

Get every new post delivered to your Inbox.

Join 455 other followers