I Did It “MY WAY”

I have spent hours trying to write this post.  It’s very hard to talk about my time at this company without becoming emotional.  But here it goes…

In September 2005 I had just left a very negative work environment.  My previous director had sent an e-mail to me [cc’ing HR] that said, in a very derogatory way, they didn’t need a DBA at their company and that I should just leave before I was to be fired.  It had been very stressful to be in such an environment and when I got this e-mail just made me loose all hope.  The next day I received an e-mail from HR apologizing for his statement and was offered a package to leave the company.  I gladly took it.

It only took me 2 weeks to find my current position.  My 7 year love affair started out as a Sybase contracting job that lead to a full-time SQL Server DBA position, an acquisition by Motricity.  During the years I have done numerous projects [Sybase ASE -> SQL 2005; Sybase IQ -> SQL 2012; etc], TAP engagements and worked closely with some of the best developers and data professionals in our industry.  It is through this job that joined Twitter and met people like Buck Woody, Jimmy May, Mike Ruthruff, Allan Hirt, Cindy Gross, Lara Rubbelke, Susan Price, Denny Lee, Kathy MacDonald, Mark Souza, … [the list goes on and on].   Last night, at my goodbye party, I met old and new friends who came out to celebrate my time here and speak of the future.  It was the best time I have had in many months.  I want to thanks those who came and made me feel special.

I really don’t know what else I can say.  But Frank Sinatra sang it best:

Yes, there were times, I’m sure you knew 
When I bit off more than I could chew 
But through it all, when there was doubt 
I ate it up and spit it out 
I faced it all and I stood tall and did it my way 

I’ve loved, I’ve laughed and cried 
I’ve had my fill, my share of losing 
And now, as tears subside, I find it all so amusing 
To think I did all that 
And may I say, not in a shy way, 
“Oh, no, oh, no, not me, I did it my way” 

For what is a man, what has he got? 
If not himself, then he has naught 
To say the things he truly feels and not the words of one who kneels 
The record shows I took the blows and did it my way!

Simple UDF to generate columnstore index

This is a simple UDF which will generate the SQL code to create a columnstore index on all columns in a given table.  You need to supply the table schema, table name, index name, whether you are dropping an existing index and finally the MAXDOP.  Although the order of the columns does not matter in a columnstore index I kept the column output ordered.

NOTE:  Because computed columns are currently not supported in columnstore indexes the code ignores such columns from the index creation.

Let me know what you think.

 

CREATE FUNCTION [dbo].[GenerateColumnStoreIndex] (@TableSchema VARCHAR(100), @TableName VARCHAR(100), @IndexName VARCHAR(50), @DropExisting BIT, @MAXDOP INT=6 )
RETURNS VARCHAR(MAX)
AS
BEGIN
DECLARE @output VARCHAR(MAX) = ”,
@columnList VARCHAR(MAX) = ”,
@i INT = 0,
@count INT,
@ColumnName VARCHAR(150),
@Drop VARCHAR(20)

SET @DROP = CASE WHEN @DropExisting = 1 THEN ‘ON’ ELSE ‘OFF’ END

— Figure out the number of columns to be added.
DECLARE curColumns CURSOR READ_ONLY
FOR SELECT ‘[‘+ COLUMN_NAME +’]’
FROM INFORMATION_SCHEMA.COLUMNS WITH (READUNCOMMITTED)
WHERE TABLE_NAME = @TableName
AND TABLE_SCHEMA= @TableSchema
AND COLUMN_NAME NOT IN (SELECT sc.name
FROM syscolumns sc
JOIN sysobjects so ON sc.id=so.id
WHERE so.name = @TableName
AND sc.iscomputed=1)
ORDER BY ORDINAL_POSITION ASC

OPEN curColumns

FETCH NEXT FROM curColumns INTO @ColumnName

WHILE( @@FETCH_STATUS = 0 )
BEGIN
SET @columnList += + CHAR(10) + @ColumnName + ‘,’

FETCH NEXT FROM curColumns INTO @ColumnName
END

CLOSE curColumns
DEALLOCATE curColumns

SET @output += ‘CREATE NONCLUSTERED COLUMNSTORE INDEX [‘ +@IndexName+ ‘] ON [‘ +@TableSchema+ ‘].[‘ +@TableName+ ‘]’
SET @output += CHAR(10) + ‘(‘
SET @output += CHAR(9) + LEFT( @columnList, DATALENGTH(@columnList)-1)
SET @output += CHAR(10) + ‘)’ + CHAR(10)
SET @output += ‘WITH (DROP_EXISTING = ‘ +@Drop+ ‘, MAXDOP = ‘ +CAST(@MAXDOP AS VARCHAR)+ ‘)’ +CHAR(10)

RETURN @output
END