Tables with a comma delimited list of columns

My coworker wants to auto-build code to construct some Columnstore indexes. Why not generally a sound practice, I though it'd be fun to write the basic script. It's a simple CTE to generate the source list of schema, tables and columns. I then use the FOR XML trick to create a comma delimited list in the UQ section and then with the glory that is the CONCAT function in 2012, we create a boatload of columnstore indexes

; WITH SRC AS
(
    -- Generate a list of all the tables and columns in a database
    SELECT
        QUOTENAME(SCHEMA_NAME(T.schema_id)) AS table_schema
    ,   QUOTENAME(T.name) AS table_name
    ,   QUOTENAME(SC.name) AS colum_name
    FROM
        sys.tables T
        INNER JOIN
            sys.columns SC
            ON SC.object_id = T.object_id
)
, UQ AS
(
-- Retrieve the unique list of schemas and tables
SELECT DISTINCT
    SRC.table_schema
,   SRC.table_name
    -- voodoo here for rollup
,   STUFF((SELECT ',' + CAST(R.colum_name AS varchar(50)) FROM SRC R WHERE R.table_name = SRC.table_name AND R.table_schema = SRC.table_schema FOR XML PATH('')), 1, 1, '') AS column_list
FROM 
        SRC
)
SELECT
    -- Generate the create statements for all the tables
    CONCAT('CREATE NONCLUSTERED COLUMNSTORE INDEX ', 'ixcs_', REPLACE(REPLACE(uq.table_name, ']', ''), '[', ''), ' ON ', UQ.table_schema, '.', UQ.table_name, '(', uq.column_list, ' )') 
FROM
    uq;

BIDS Auto Connect missing in Denali CTP3 (and CTP1)

I am fairly certain I am the only person who ever noticed or used this feature, but I found it handy. In Visual Studio, an option existed for "Business Intelligence Designers" project types that allowed you to define Auto Connect options for the Control Flow and Data Flow. It wasn't perfect, but the general concept was whenever an item was selected on the canvas, a double click to an item in the toolbox would add it to the canvas at the "correct" distance and in a straight line. For those quasi-OCD folks like myself, it was a quick way to add items without having to immediately jiggle boxes to satisfy the inner demons.

From Denali_CTP3

Thus far, Denali seems to have dropped this feature. Maybe it's just hidden in a new menu but thus far, I haven't found it.

From Denali_CTP3

SSIS Project Protection Level

My first post about new features in the SQL Server Denali CTP1, aka "Engine of the Devil", was going to be about SSIS package deployment until I noticed this little nugget: project protection level. It's like someone tweeted earlier: it's the little things, undo and redo that make this a better release. There are steps I take every time I spin up a new package: logging, configuration, transactions, and set the package protection level. Full disclosure: I have never had cause to use anything but "Don't save sensitive." In v.Next, I noticed a new option in the project properties. You can now define the SSIS Project Protection Level. Simple, yes but now I will have one less thing to worry about when junior developers or outside contractors develop SSIS packages.

From ProfessionalBlog

From ProfessionalBlog