Datta's Ramblings on Business Intelligence 'N' Life

Reviewing My Resolutions at the End of 3 Months of the Year 2012 (1st Calendar Quarter)

noreply@blogger.com (Dattatrey Sindol (Datta)) — Sat, 31 Mar 2012 11:49:00 +0000

In the beginning of the year 2012, I had made a resolution about few things from a Professional, Career, and Personality Development standpoint (More about that here). Most of us make resolutions, and set goals etc. and all that is fine. But none of these is useful if we do not follow them sincerely with full commitment. So the question is, how do we know what we are upto, where we are headed, and what needs to be done (improvements/corrections) to meet our resolutions/goals. The simple answer to that is "monitor the progress" and "evaluate/measure at regular intervals" (more about this in a future post), in my case I have set the interval as 3 months, and hence this post.

Let me go through one by one.

Certifications - On Track

Had planned to take a couple of certifications this year and in fact I took the Certification Exam 70-448 (Microsoft SQL Server 2008, Business Intelligence Development and Maintenance) and cleared it. It was a great experience and in my opinion the real secret in cracking this examination is to read the book "MCTS Self-Paced Training Kit (Exam 70-448): Microsoft® SQL Server® 2008 - Business Intelligence Development and Maintenance". It is an excellent book and is helpful not only for certification but also a great reference guide.

All looks good here, but still more to go! I am preparing for MCITP and will be taking it in the coming months. Let me see how it goes.

Blogging - Slightly Off Track

It's been quite a bit of time that I blogged though I planned to blog very frequently in the beginning of the year. Couple of reasons for that is, firstly little hectic days during this month and secondly, little bit of health problems now and then. But now everything looks on track and I would definitely start blogging as frequently as possible, and in fact I have planned a lot of things and will start posting stuffs starting in about a couple of weeks. So stay tuned for more in the coming weeks.

Though overall, I didn't blog much, but still I managed to publish a bunch of articles as listed below.

Publish Date	Article Title
28-Mar-2012	SSAS Interview Questions Part I: Questions on Basic Concepts, Data Sources, and Data Source Views - MSSQLTips.com
23-Mar-2012	Windows Services for SQL Server - MSSQLTips.com
13-Feb-2012	Data Profiling Using T-SQL: Part 2 - Column Length Distribution
06-Feb-2012	Extracting Report URL from ReportServer database in SSRS
31-Jan-2012	SSRS - Report History is still maintained even after deleting the report from Report Manager - BeyondRelational.com
20-Jan-2012	Cumulative Aggregates in SQL Server Reporting Services - MSSQLTips.com
11-Jan-2012	SSIS Allows Creating Tasks with Same Name in Different Scopes - BeyondRelational.com
05-Jan-2012	Get First Hand Experience with Power View (Previously Crescent) using Live Sandbox
04-Jan-2012	Excellent Video Tutorials on C# Fundamentals for Beginners

Community Contributions - On Track

One of the goals/resolutions was to do as much community contribution as possible. Have been participating more frequently in the MSDN forums and there is quite a bit of improvement in terms of where I was in the beginning of the year and where I stand today in terms of Number of Answers, Number of Replies, Number of Points, Number of Medals etc. on the forums. Below is a quick snapshot of "Report as of 1st Jan 2012" vs. "Report as of 31st March 2012".

It gives a great amount of satisfaction to contribute in forums or any other websites or on this blog. I feel happy when the content I post solves someone's problem, save someone some time, helps someone in some way. I will try my best to continue to contribute more and more and stick to my resolution for the rest of this year.

Training - On Track
When it comes to giving trainings or delivering sessions, I have been preparing for few trainings and sessions in the coming days within the organization. Overall, I feel that I am still on track and things are looking good at this end. Hoping to give more sessions and trainings and help more and more people to get on-boarded to SQL Server BI (Microsoft BI).

Weight Loss - On Track
This is one of the areas, which I thought would be off-track, but to my surprise it's on track :)
Yes I have lost 3 KG of weight (Hurray!!) and it makes me really happy :-) and I feel proud of myself as I have been able to gain some control over my eating habits and managed to lose some weight without sacrificing much. Still a long way to go! Hoping to lose some more weight in the coming months and get to the state that I used to maintain during my college days.

Before we close on this, I am working on quite a few things and will start giving updates starting in next few weeks and hoping to see a positive response and timely feedback from my readers either online (comments on the blog) or offline (through email, social networking etc.). So stay tuned for more!!

Data Profiling Using T-SQL: Part 2 - Column Length Distribution

noreply@blogger.com (Dattatrey Sindol (Datta)) — Mon, 13 Feb 2012 04:30:00 +0000

I had written an article long ago on determining Column Null Ratio (Data Profiling) using T-SQL. While I was reviewing some of my articles, it occurred to me that, I forgot to post the next part of the Data Profiling series. Hence this blog post.

In this blog post, I will be showing a simple T-SQL script to capture the Column Length Distribution for a table.

-- Provide the Schema Name and the Table/View Name

DECLARE @DatabaseName NVARCHAR(255) = N'AdventureWorksDW'

DECLARE @SchemaName NVARCHAR(255) = N'dbo'

DECLARE @TableName NVARCHAR(255) = N'DimProduct'

BEGIN

SET NOCOUNT ON

-- Declare the parameters internal to query

DECLARE @SQLString NVARCHAR(MAX) = N''

DECLARE @ParamDefinition NVARCHAR(MAX) = N''

DECLARE @ColumnList TABLE (

ColumnId INT IDENTITY(1,1)

, ColumnName NVARCHAR(255)

, ColumnMaxLength INT

, ColumnMinLength INT

)

DECLARE @ColumnMaxLength INT

DECLARE @ColumnMinLength INT

DECLARE @ColumnCount INT = 0

DECLARE @LoopCounter INT = 1

DECLARE @ColumnName NVARCHAR(255)

SET @SQLString =

N'SELECT COLUMN_NAME FROM '

+ @DatabaseName + N'.' + N'INFORMATION_SCHEMA.COLUMNS

WHERE

DATA_TYPE IN (''CHAR'', ''NCHAR'', ''VARCHAR'', ''NVARCHAR'')

AND TABLE_SCHEMA = @SchemaName

AND TABLE_NAME = @TableName'

SET @ParamDefinition = N'@SchemaName NVARCHAR(255), @TableName NVARCHAR(255)'

INSERT INTO @ColumnList (ColumnName)

EXECUTE sp_executesql @SQLString, @ParamDefinition, @SchemaName, @TableName

SELECT @ColumnCount = COUNT(*) FROM @ColumnList

WHILE (@LoopCounter <= @ColumnCount)

BEGIN

SELECT @ColumnName = ColumnName

FROM @ColumnList

WHERE ColumnId = @LoopCounter

SET @SQLString =

N'SELECT '

+ '@ColumnMinLength = MIN(LEN(' + @ColumnName + '))'

+ ', @ColumnMaxLength = MAX(LEN(' + @ColumnName + '))'

+ ' FROM '

+ @DatabaseName + N'.' + @SchemaName + N'.' + @TableName

+ ' WITH (NOLOCK) '

SET @ParamDefinition = N'@ColumnMinLength INT OUTPUT, @ColumnMaxLength INT OUTPUT'

EXECUTE sp_executesql @SQLString, @ParamDefinition, @ColumnMinLength OUTPUT, @ColumnMaxLength OUTPUT

UPDATE @ColumnList

SET

ColumnMinLength = @ColumnMinLength

, ColumnMaxLength = @ColumnMaxLength

WHERE ColumnId = @LoopCounter

SET @LoopCounter += 1

END

SELECT

ColumnName AS [Column Name]

, ColumnMinLength AS [Column Minimum Length]

, ColumnMaxLength AS [Column Maximum Length]

FROM @ColumnList

ORDER BY [Column Name]

END

Below is a sample output of the above T-SQL script and also the output of Data Profiling Task in SSIS with the same Database Table as input.

T-SQL Script Output

SSIS Data Profiling Task Output

Here are the highlights of the above T-SQL script:

Takes Database Name, Schema Name and Column Name as inputs and produces the Column Length Distribution as output.
With few minor changes, this script can be used in any of the SQL Server versions.
This script is a one step process to get the Column Length Distribution whereas data profiling using SSIS Data Profiling Task is a two step process - First generate the xml file with the profile and then use Data Profile Viewer to view the generated profile.
Only SQL Server Management Studio (SSMS) with necessary connectivity to the required server/database is sufficient to capture the profile whereas in case of SSIS Data Profiling Task, it requires an ADO.Net Connection Manager.
Knowledge of T-SQL is sufficient whereas the SSIS Data Profiling Task requires some basic knowledge of SSIS.
It does not provide the distribution of values in each of the individual columns whereas in case of SSIS, we can get the distribution of values in each of the columns.

Since I am a great lover of T-SQL, I prefer using T-SQL as much as possible. However, SSIS being a powerful ETL tool has its own advantages and T-SQL cannot beat SSIS when it comes to ETL.

Note: This demonstration is based on SQL Server 2008 R2.

Extracting Report URL from ReportServer database in SSRS

noreply@blogger.com (Dattatrey Sindol (Datta)) — Mon, 06 Feb 2012 03:30:00 +0000

I was working on a report for monitoring the usage of reports on a Report Server instance. As part of that, I wanted to display a list of reports with values for various metrics along with few additional links under each of the reports. One of the links was to Go to the Report in question, for which I would require the exact URL of the report that is hosted on Report Server. After doing some research on the data available in the ReportServer database I found an approach to get the URL.

There are various useful tables in the ReportServer database of which, there is a table called "Catalog" which stores the list of all the reports, data sources etc. which are hosted on the Report Server. The table also contains various properties for each of these objects like Object Id, Object Name, Path, Parent Id etc.

I used the following simple query to extract the Report URL.

DECLARE @BaseReportURL VARCHAR(512) = 'http://<<servername>>:<<portnumber>>/Reports<<_InstanceName>>/Pages/Report.aspx?ItemPath='

DECLARE @ReplacementForSlash VARCHAR(10) = '%2f'

DECLARE @ReplacementForSpace VARCHAR(10) = '+'

SELECT

Name AS ReportName

, @BaseReportURL + REPLACE(REPLACE([Path],'/',@ReplacementForSlash),' ',@ReplacementForSpace)AS ReportURL

FROM [dbo].[Catalog]

In the above query, BaseReportURL remains the same for all the reports on a particular instance of Reporting Services. Path column in the Catalog table contains the full path of the report like "/MSBI Demo/Sample Reports Folder/SSRS Cummulative Totals". To get the Report URL, I replaced the "/" symbol with "%2f" and space with "+" in the Path field and then concatenate it with the BaseReportURL.

Let us take a look at the following example.

The output of the above example is as shown below, which is exactly the same URL which I got when I tried to hit the report by name "SSRS Cummulative Totals" on the specified instance of Report Server.

http://sindol:8080/Reports_MSSQLServer2K8R2/Pages/Report.aspx?ItemPath=%2fMSBI+Demo%2fSample+Reports+Folder%2fSSRS+Cummulative+Totals

Note: Above demonstration is based on a named instance of SQL Server 2008 R2 with a standalone installation in Native Mode.

Happy B'day to Me :)

noreply@blogger.com (Dattatrey Sindol (Datta)) — Tue, 31 Jan 2012 18:30:00 +0000

Here comes another b'day of mine!! I just felt like I celebrated my last b'day just a couple months ago :) Time is moving so fast and so is the world.

B'day reminds me of my childhood days (especially when I was in primary school) when I used to get new clothes for my b'day atleast a week or two in advance, pester my parents to buy me good chocolates ;) which I can distribute to all my friends and teachers in school and so on.

I still remember the Parle Kismi chocolates, which my parents used to buy me about half a kilogram or so, which I used to distribute to all my friends, teachers etc. in school and ofcouse, I would let my very close friends take two chocolates ;) including teachers also ;)

On b'day like other kids, I used to go in new color dress (not the regular boring uniform), so people can easily make out it's my b'day. The best part was, on b'day one could take any number of excuses like no scoldings or beatings for making noise, not doing homework and what not :P

Those were the best day of my life!!

So here's wishing myself a Very Happy B'day!! May God bless me with good health and lots of fun and happiness!! May I have a wonderful and successful year ahead!!

Will try my best to do more and more knowledge sharing in this year, share my learnings and learn from others learnings :)

Get First Hand Experience with Power View (Previously Crescent) using Live Sandbox

noreply@blogger.com (Dattatrey Sindol (Datta)) — Wed, 04 Jan 2012 18:40:00 +0000

As most of you are aware, Microsoft BI (SQL Server BI) is getting stronger and better after each release and is one of the top BI products in the market and also the most widely hosted in house database server.

Those of you, who are tracking the Denali release closely, must be aware of a band new product from Microsoft called "Crescent", which is a Self Service BI Tool. This has been renamed as "Power View" as part of the SQL Server 2012 RC0 release.

Power View installation/setup requires SharePoint as well, which not everyone in the community might have it at their disposal, but still every Microsoft BI enthusiast wants to get a first-hand experience with the latest tools/features of Microsoft BI. If you are one among them, then you can play with existing reports or create new reports using Power View on the Live Sandbox.

Here are few additional resources on Power View:

Happy Power Viewing :)

Excellent Video Tutorials on C# Fundamentals for Beginners

noreply@blogger.com (Dattatrey Sindol (Datta)) — Wed, 04 Jan 2012 02:30:00 +0000

While I was browsing through the videos on Channel 9, I came across a nice video series explaining the fundamentals of C# development. If you are completely new to C# and want to start learning the language, this is one of the best video series which explains right from the very basic concepts.

Happy New Year 2012

noreply@blogger.com (Dattatrey Sindol (Datta)) — Sun, 01 Jan 2012 04:30:00 +0000

Here we come at the beginning of another exciting New Year :)

Wishing You All a Very Happy and Prosperous New Year 2012 !!

Have a Exciting, Fun Filled and a Fruitful Year Ahead !!

Looking forward to an exciting new year ahead, with a great experience, learning, sharing, networking and lot more. I plan to make this year more meaningful with following resolutions from a Professional, Career, and a Personality Development standpoint:

Certifications: Take up couple of certifications
Blogging: Blog more frequently on this blog
Community Contribution: Participate in other community sites and forums with increased participation and contribution
Training: Deliver more trainings/sessions
Weight Loss: Lose some weight (Haven't been so successful at this due to my food loving nature :()

A Quick Glance at the Year 2011

noreply@blogger.com (Dattatrey Sindol (Datta)) — Sat, 31 Dec 2011 17:34:00 +0000

Here we come to the end of another wonderful year. Year 2011 has been really wonderful and I had varied experiences in terms of Learning, Sharing, Networking, and much more.

Learning

From a Technical Standpoint, I have been doing a considerable amount of reading thereby learning new things and improving my skill set in the Microsoft BI space. Exploring the new features of SQL Server Denali was really fun and exciting as more and more feature additions/improvements were made with each CTP release. Currently exploring the SQL Server 2012 RC0 and will continue to explore it in the coming days.

From a Management Standpoint, it has been quite interesting and at times stressful. Though I have been focused more on the technology side of the things right from the beginning of my career, I did a little bit of management work here and there. And, during 2009 I was involved in a considerable amount of management work when I was at the client location and was working as an Onshore Lead in an Onsite-Offshore model. Thereafter, starting from 2010 I was again focused mostly on the technology side of things working as a Technical Lead after I came back to India. During 2011, again I moved into a Techno-Management Role and have been working as a Development Lead for about 6 months now. So far the journey has been pretty good though little stressful at times, as it involves dealing with people, processes, and lot more.

Irrespective of which role we are in, some amount of management experience is essential as we go higher up the ladder and I have gained sufficient understanding of the management aspects based on my last 6 months of journey and I believe it is sufficient for folks at my level of experience in the industry.

I strongly believe that, learning is a continuous process and there is no end to it. We learn something new every day be it technical or non technical. In the coming days, I will focus more on the technology side and try my best to share my learning's with the community.

Sharing
Sharing is a very important aspect of learning. Learning is of no use if we do not share our learning with others. Sharing knowledge with others offers great benefits like opportunity to connect with people, learning from their experiences, and also learning new things based on the interactions made as part of sharing and much more.

As part of the sharing exercise, I have delivered few trainings/sessions within the organization on SQL Server, Query Processing, T-SQL, SSAS, & SSRS etc. It was an exciting and a wonderful experience. Will try my best and continue to contribute towards trainings/sessions and anyone interested can contact me through my blog and I will be happy to help you get started :)

Blogging
Blogging is another way to learn and share, and I have been doing quite a bit of blogging though it got slowed down towards the end of the year. I have written quite a few articles on the new features of Denali and have started posting short tips at JustLearned on BeyondRelational.com and have submitted a couple of articles on MSSQLtips.com. I plan to continue to blog and at a more increased pace by publishing articles more frequently and participating more and more on forums etc.

Here is a list of all the tips and articles that I have written during 2011 both on this blog and also on external SQL Server Community Websites.

Publish Date	Article Title
28-Dec-2011	Response Time of SSMS in SQL Server 2012 RC0 is Very Slow
24-Dec-2011	Stop Sharing a Folder in Windows 7
13-Dec-2011	SQL Server - 3 part naming Cannot be used when dropping Views,Functions or Procedures. - BeyondRelational.com
09-Dec-2011	Querying SQL Server Agent Job Information - MSSQLTips.com
11-Nov-2011	SQL Server Row Count for all Tables in a Database - MSSQLTips.com
25-Oct-2011	Adding a Not Null Column to a Table in SQL Server
04-Oct-2011	Guidelines on Reading, Writing, and Managing Emails
24-Sep-2011	Coding Standards and Best Practices
14-Sep-2011	Schema name is not mandatory while querying Compatibility Views and mandatory while querying Catalog Views - BeyondRelational.com
04-Sep-2011	Quickest way to SELECT All Columns from a Table - BeyondRelational.com
06-Aug-2011	Intellisense in SSMS 2008 Lists the Objects Even Before Creating them - Pretty Intelligent
16-Jul-2011	SQL Server Code Named "Denali" CTP3 is Available for Download
08-Jul-2011	Internet Explorer - Click the scroll wheel to open a link in a new tab - BeyondRelational.com
27-Jun-2011	Online Tools for Formatting T-SQL Queries - BeyondRelational.com
21-Jun-2011	Shortcut to View the Properties of a File or Folder in Windows - BeyondRelational.com
10-Jun-2011	Combining SELECT INTO and UNION ALL in T-SQL - BeyondRelational.com
10-Jun-2011	Applying Logical Operators in TFS Work Item Queries
07-Jun-2011	Free E-book on Data Quality and Master Data Management
05-Jun-2011	Videos for Learning Microsoft BI
15-May-2011	Always Check-Out the Latest Version of Code from TFS
28-Apr-2011	Resources to Learn More about New Features in SQL Server code-named Denali CTP1
20-Mar-2011	Use of DISTINCT, UNION and UNION ALL in SQL Server
10-Mar-2011	Deploying Multiple Reports to Report Manager in One Go in SSRS
06-Mar-2011	Data Profiling Using T-SQL: Part 1 - Column Null Ratio
26-Feb-2011	Working with CHECKSUM Function in SQL Server
13-Feb-2011	Shortcuts for Starting, Stopping and Restarting SQL Server Service
09-Feb-2011	Formatting Date and Time in SQL Server Reporting Services (SSRS) Reports
25-Jan-2011	Display the Solution Name in Solution Explorer by Default in SQL Server Business Intelligence Development Studio (BIDS)
12-Jan-2011	Improved Description about Transformations in SSIS Toolbox in BIDS in Denali CTP1 - Usability Enhancements in BIDS-SSIS in SQL Server code-named Denali - Part 3
10-Jan-2011	Formatting Shortcuts in SSIS Package Designer in Business Intelligence Development Studio (BIDS)
07-Jan-2011	New & More Organized Toolbox for SSIS in BIDS in Denali CTP1 - Usability Enhancements in BIDS-SSIS in SQL Server code-named Denali - Part 2
05-Jan-2011	Surround With Feature in SSMS in Denali CTP1 - Usability Enhancements in SSMS in SQL Server code-named Denali - Part 3
03-Jan-2011	Insert Snippet Feature in SSMS in Denali CTP1 - Usability Enhancements in SSMS in SQL Server code-named Denali - Part 2
02-Jan-2011	Microsoft SQL Server - The Most Widely Hosted In House Database Servers

All in all, it has been a wonderful year with an increased amount of activity compared to previous year. Looking forward to a new year with lot of learning, fun and excitement!!

Response Time of SSMS in SQL Server 2012 RC0 is Very Slow

noreply@blogger.com (Dattatrey Sindol (Datta)) — Wed, 28 Dec 2011 12:39:00 +0000

I just installed SQL Server 2012 RC0 couple days back to try and explore the features of RC0. However, after the installation, I noticed that the SSMS is really really slow and even a single click takes about a few seconds and is kind of irritating.

As everyone else, just did a quick search to see if this is only a problem with my installation or if anyone else in the community is facing the same problem, I found that quite a few of them are facing similar problem.

Following are couple of connect items related to this issue. If you are facing the same issue, please go ahead and vote.

Hopefully this gets fixed in the next release of 2012.

I guess now I need to switch back to CTP3 :(

Stop Sharing a Folder in Windows 7

noreply@blogger.com (Dattatrey Sindol (Datta)) — Sat, 24 Dec 2011 09:20:00 +0000

Sharing contents on a computer is a common thing and each one of us share the stuffs on our computers (connected to network) for others to access. And sometimes, the computer's share becomes cluttered due to huge number of folders being shared and might require a cleanup (stop sharing some of the folders).

I have been sharing project related folders with other team members for various purposes. At times, after their use, I used to remove the sharing, and at other times, the folder used to remain shared. Last day when I saw my own computer's share, I noticed that, a lot of folders were shared (Present in the path \\<<computername>>\) and when I wanted to stop sharing some of the old folders which are no more needed by others, I could not remember the exact location of those folders within my computer's file system. Example: Say I have a folder called "201012" shared with others and that folder is present in "D:\Datta\LearningAndDevelopment\SQLTraining\". However, in my scenario, I could not remember where the "201012" shared folder was located on my computer. After playing around with some of the options, I found an option to remove the folder from sharing without knowing the original location of the folder. Follow the below steps to remove the folder from sharing.

1. Go to computer share (\\<<ComputerName>>\)

2. Right Click on the folder which you want to stop sharing and Select Properties from the context menu.

3. In the Properties window, Select Sharing tab and then Click on Advanced Sharing..., which will open the Advanced Sharing window.

4. In the Advanced Sharing window, uncheck the Share this folder checkbox, which is selected/checked for the folders which are shared.

5. After unchecking the Share this folder option, click on Apply, click on OK and that's it ! Your folder is no more shared with others.

Removing a folder from a computer's network share is as simple as that :)

My First Article is Published on MSSQLTips.com :)

noreply@blogger.com (Dattatrey Sindol (Datta)) — Sat, 12 Nov 2011 04:30:00 +0000

It's one of the happy moments of my life from a technical/professional standpoint as my first article has been published on MSSQLTips.com.

The article is published on a very special date - 11-11-11 (11th November 2011).

Here are more details about the article:

Title: SQL Server Row Count for all Tables in a Database
URL: http://www.mssqltips.com/sqlservertip/2537/sql-server-row-count-for-all-tables-in-a-database/

I will continue to blog on this blog as well as try and contribute to other blogs/sites/forums in the SQL Server Community.

Adding a Not Null Column to a Table in SQL Server

noreply@blogger.com (Dattatrey Sindol (Datta)) — Tue, 25 Oct 2011 18:05:00 +0000

Today one of my colleagues was working on a work item which involved adding a Not Null column to an existing table which had few million records in it already. When I looked at his approach, I somehow felt there should be a more simple approach and in this post, I will outline the approach which he was following and the simple approach that I came up with.

Following are the two approaches which he was following:

Approach 1

Add a Null Column to a Table
Update the Column with some dummy data
Alter the Column to make it Not Null
Pull the data from Source and Update the newly added Column with actual data. The data coming from the source for this column will be NOT NULL for sure.

However, with this approach, he was facing memory issues due to huge amount of data. Then he mentioned about another approach which he tried, the Approach 2 outlined below.

Approach 2

Add a Null Column to a Table
Update the Column with some dummy data
Alter the Column to make it Not Null
Pull the data from Source and Update the newly added Column with actual data in Batches. The data coming from the source for this column will be NOT NULL for sure.

With this approach for few tables it worked fine but for few bigger tables he started getting similar memory related issues.

After I looked at the above two approaches, I felt that there should be some simple approach to deal with this problem and I thought it could have been tackled with the help of a Default Constraint. However, I was not quite sure of the syntax [I am not so good at remembering syntaxes ;)]. So I just went up to my best friend (well, most of our best friend), msdn and did some search around the Default Constraint and the result of that is this simple approach as outlined below with the help of a simple script.

Approach 3 (Simple Approach)

-- Create a Sample Table for demo

CREATE TABLE dbo.NotNullColumnAdditionDemo (

ID INT IDENTITY(1,1) NOT NULL,
Name NVARCHAR(255) NOT NULL
)
GO

-- Insert some sample data

INSERT INTO dbo.NotNullColumnAdditionDemo (Name)
SELECT 'Datta' AS Name
UNION ALL
SELECT 'Raghu' AS Name
UNION ALL
SELECT 'Venkat' AS Name
GO

-- Check the data in the table
SELECT * FROM dbo.NotNullColumnAdditionDemo
GO

Now, let us add the new column as NOT NULL along with a DEFAULT Constraint.

-- Alter Table to add a new NOT NULL Column with a Default Constraint
ALTER TABLE dbo.NotNullColumnAdditionDemo
ADD City NVARCHAR(255) NOT NULL CONSTRAINT [DF_NotNullColumnAdditionDemo_City] DEFAULT ('Bengaluru')
GO

-- Check the data in the table
SELECT * FROM dbo.NotNullColumnAdditionDemo
GO

As you can see after executing the previous SELECT Statement, a new column City has been added with a value Bengaluru populated into it for the existing records as well.

Now that we have added the column and we know for sure that the data which will be loaded into this new column will be NOT NULL for sure (Based on the scenario explained above), let's go ahead and drop the Constraint.

-- Drop the Constraint
ALTER TABLE dbo.NotNullColumnAdditionDemo
DROP CONSTRAINT [DF_NotNullColumnAdditionDemo_City]
GO

-- Check the data in the table
SELECT * FROM dbo.NotNullColumnAdditionDemo

GO

That's it !
And the best part about this approach is, it takes very less time to add column, populate default value and drop default constraint when compared to the Approach 1 & Approach 2 outlined above.

Until next time, Happy Reading & Happy Learning :)

Related Article: Working with Default Constraints in SQL Server

Reference: http://msdn.microsoft.com/en-us/library/ms190273.aspx

Never Send an Email when You are Angry or Frustrated !!!

noreply@blogger.com (Dattatrey Sindol (Datta)) — Fri, 07 Oct 2011 17:25:00 +0000

Golden Rule of Emailing - Never Send an Email while You are Angry or Frustrated !!!

Source: Forwarded Email.

Guidelines on Reading, Writing, and Managing Emails

noreply@blogger.com (Dattatrey Sindol (Datta)) — Mon, 03 Oct 2011 18:44:00 +0000

Emails are a part of our daily work life especially in the IT industry where majority of communications are through emails. Based on my experience in the IT industry on reading, writing and managing emails, I have put together a set of guidelines.

Following is a list of Guidelines on Reading, Writing and Managing Emails:

1. Attend to emails regularly and do not keep any emails unattended for more than 72 hours.

We should respond to all the emails that are addressed to us, within 24-48 hours. This is very important, since the sender might be expecting some critical information and he/she might be blocked if the required information is not provided on time.

Weekends, Vacations, Holidays, Emergencies etc. are an exception to this guideline, where the response time can go beyond 72 hours. The basic idea here is that one should attend to emails within 1-2 working days (24-48 hours) and in extreme situations within 3 working days (72 hours).

2. Use of Out of Office Replies with relevant contact and backup information.

If you are going on vacation or any other planned leaves then ensure that you set the Out of Office automated replies which includes the following information:

(i) Contact information of your backup at work.

(ii) Your contact information where you can be reached in case of any urgencies.

Out of Office replies should be set even if you are not in office for one working day.

3. Do not leave any meeting request unattended.

Often we send meeting requests to invite people for meetings, trainings, sessions etc. We should make sure that these invites are either Accepted, Declined, or set Tentative. In case you Decline or set as Tentative, edit the response to include appropriate justification before sending the response.

Leaving a meeting request unattended causes confusion and in certain cases where your presence is important, if the meeting goes on as per the schedule in your absence, it may not be as fruitful as expected.

4. Give an advance notice to concerned people about your upcoming vacation/leave plans.

While working in large teams especially when the team is spread across different geographical locations, there will be lot of dependencies on each other. To ensure that the operations continue smoothly, one should send out a notification about upcoming vacation/leave plans to all the concerned people so that people can plan their work/dependencies accordingly.

Send a meeting request and do not request response to this request (Uncheck the Request Responses option if you are using say Outlook 2007).

5. Use relevant and meaningful subject for all the emails.

The subject of an email is very important and should server to give some context to the recipient on what the email is about. Email subject also can help in getting necessary attention from people and get quick responses.

People might tend to ignore emails with less relevant or ineffective subjects.

It's a good habit to have the subject something like "<<Project/Module Name>> | Title of item being discussed".

6. Change the email subject if the topic of discussion on the email thread changes drastically.

Quite often it so happens that, when many people start responding to emails the topic being discussed in the email might change drastically and altogether a new topic might be discussed even though the original topic is logically closed. In such scenarios, one should change the subject of the email to be more relevant and should represent the topic currently being discussed.

7. Send a quick one liner text when the recipient is not reachable or if you want to give a quick heads up.

Sometimes, you might just want to give a quick heads up on behalf of your team member that he/she is not well, so he will be taking a day off. In such scenarios send a quick email with no email body and only subject with a short note something like "Datta on Leave Today (Not feeling well) ". In this scenario, ensure that the person on whose behalf you are sending the email is added in the Cc list.

Similar approach can be followed when you want to reach a person who is neither reachable on messenger (Office Communicator, Lync etc.) nor on phone. Send a quick note something like "Please ping me when you see this email. Need some information urgently. "

8. Mark the emails as High Importance, Low Importance, or Normal Importance appropriately.

Quite often the emails that we send are of normal importance (Neither marked as High Importance nor as Low Importance). However, there can be a scenario where there is some very important task/item which needs attention/action from specific people then we should mark such emails as high importance.

Though everyone hates spam, there can be scenarios where you need to send an email which is not so important. For example, you asked for some information from one of your team members and he/she responded quickly to your email with required information. In this case just send a quick note something like "Thanks for the timely help :)". Though this email is not important to the recipient, it goes a long way in establishing smooth relationships.

9. Keep your Reminder & Follow-up emails Soft & Polite.
Often people tend to ignore your emails, leave them unread, miss them due to their busy schedule etc. Irrespective of the scenario, you end up not getting a response to your email. In such scenarios, send a reminder email to the recipient and keep it soft and polite something like:

Hello <<Recipient>>,

A soft reminder on the below email or highlighted item etc.

<<Your Signature>>

In case you are referring to the highlighted item in the below email thread, ensure that the color used to highlight the content which you are referring to is not used anywhere else in the thread else it might lead to confusion.

10. Use Smiley Faces wherever appropriate.
Use Smiley Faces while writing emails wherever appropriate. Smiley Faces though are a simple thing, they play a very important role in changing the perception of the recipient about your email. It can help put across a hard/heavy request in a polite/soft way.

However, ensure that Smiley Faces are used diligently and not too much. Also, take care to ensure Smiley Faces are used in appropriate places. Use of Smiley Faces in inappropriate places might lead to representing sarcasm even though you are not sarcastic in the email.

11. Use Sections, Bullet Points and Paragraphs to Keep the email Clean & Crisp.
An email should be short enough to cover the matter and long enough to convey the matter. Use Sections, Bullet Points and Paragraphs to keep the content clear and crisp so that the recipient can understand the message that you want to convey.

12. Ensure that the Images are not lost in subsequent responses/threads.
While including images in the email, paste them in the email to make them a part of the email body instead of attaching the file. The advantage of this approach is that, even when others reply on top of this email, the images are maintained in the email which might play an important role in conveying the required information/message.

If the images are attached as files instead of including in email body, when someone replies on top of your email, the attachment is lost and it might become difficult for others to get a better understanding of the content without the images, which were attached for the purpose of better understanding.

13. Use Inline Comments to provide Inputs/Responses to a set of Questions/Pointers.
While providing your inputs/responses to a set of questions or pointers make use of inline comments (with appropriate font style, color etc.) to make it more clear. Inline comment can be something like this:

What guidelines should be used for writing emails ?
[Datta]: I will be publishing an article on the standards/guidelines on writing emails in next couple days and you can follow those guidelines.

The above highlighted text represents an Inline Comment.

14. Mark Corrections in a Previously Sent email as appropriate.
Though, there is an option to recall a sent message in Outlook, it's better to mark corrections instead of recalling a message. The reason being, marking corrections will help the person understand, what was the issue with previously sent email, where as recalling an email does not tell the recipient why an email was recalled.

Mark corrections with appropriate font style, color, background etc. as shown below.

I will be publishing an article on "~~Best Practices~~ Guidelines on Reading, Writing, and Managing Emails".

As shown in the above example, strike out the invalid/inappropriate text and add new text with appropriate font style, color, & background.

15. Use Bcc... field wherever required to Avoid Spam.
Bcc... field is used in many scenarios like to add yourself on the Bcc... list so that you get a copy of the email that you are sending, to send information to a broader audience etc.

Often, when you send useful information (which could be just an FYI...) to broader audience, or while requesting some information like say you are collecting the details of certifications by each of the team members in your team etc. make sure to include the intended audience in Bcc... so that even if the recipient does a "Reply All" by mistake (which is usually the case with few people), only you would get the email there by avoiding spam.

Remember to add a note at the bottom of the email indicating the list of people or distribution groups which are added in Bcc... field. This will ensure 100% transparency.

16. Loop in Additional People in case they need Visibility into the item being discussed in the email.
In case you need to loop in additional people into the previously sent email (you might have forgotten to include few people) or into an email which you just received and you feel that the additional people whom you are adding need visibility into the item being discussed in the email. Following format can be used to loop in people into the emails:

Do a "Reply All" and say + John, David, & James as they are part of Testing Team.
In the above example, John, David, & James are the folks whom you are adding to the loop and "as they are part of Testing Team" is the justification on why those people are being added.

Justification is optional but is good to include. In case you cannot think of any appropriate justification, in most cases, it is ok to say something like "+ John, David, & James for visibility".

17. Use Email Signature with Appropriate & Useful Information.
Email Signatures are meant to provide additional information to the recipients. However, they can also be used to market yourself or your product or to publicize your skills, your team, or your organization etc. Ensure that useful information is included in the email signatures, which includes some of these common details:

(i) Contact Information - Address (Optional), Phone Number etc.
(ii) Role/Designation
(iii) Link to Company Website/Help Center
(iv) Additional Email Addresses like the Support@XYZCorporation.com etc.
(v) In case of people working in support, you can also include the SLA details etc.
(vi) Additional Information/Content - Other additional content which one can include is your favorite quote (make sure you don't present your emotions here), your certifications/awards/accolades, company's certifications/awards/accolades etc.

18. Do a thorough Spelling Check before sending an email.
This is the last but not the least of all the guidelines on writing emails. Ensure that proper spelling is used in the email and you have performed a thorough spell check before sending the email.

* These guidelines are written from official emails standpoint and not from personal emails standpoint.

** This list is updated as and when there is anything to be added/modified.

Coding Standards and Best Practices

noreply@blogger.com (Dattatrey Sindol (Datta)) — Sat, 24 Sep 2011 06:21:00 +0000

As part of any software development process, different developers work on different parts of the module/project. As a result of this there is a possibility of variation in the guidelines being followed across different parts of the code based on the thought process of different developers which is a bad sign. As the size of the module/project and the team grows, the inconsistency across different parts of the code becomes more visible and turns out to be a challenge especially when it comes to the maintenance phase of the project.

To overcome the above problem, it is very important that we define and follow Coding Standards and Best Practices to ensure that the inconsistencies are eliminated and a high quality product/service is delivered.

SQL Server/Microsoft BI development is no different from this and requires following specific Coding Standards and Best Practices.

Following are some of the benefits of following Coding Standards and Best Practices:

Ensures consistency in terms of the standards across different parts of the code.
Any new resource who is on-boarded to the project, can easily adopt these standards as the standards in the code will have a pattern which is easy to identify.
Subsequent code changes and debugging of issues becomes easy during the maintenance phase.

I will be writing a series of articles on Coding Standards and Best Practices in the coming days. I will be posting links to each of the articles in this blog post.

Fun Part of Taking Leave in the IT Industry

noreply@blogger.com (Dattatrey Sindol (Datta)) — Sun, 21 Aug 2011 18:30:00 +0000

Here is another hilarious moment around taking a leave especially for a week long vacation in the IT Industry :)

Have you ever experienced this yourself ? If yes, leave a comment below to share your experience :)

Intellisense in SSMS 2008 Lists the Objects Even Before Creating them - Pretty Intelligent

noreply@blogger.com (Dattatrey Sindol (Datta)) — Sat, 06 Aug 2011 08:34:00 +0000

As most of you us know that Intellisense feature was introduced as part of SQL Server Management Studio (SSMS) which shipped with SQL Server 2008. There after it is getting better in subsequent releases with a whole lot of new features in Denali SSMS.

Basically Intellisense assists the user while writing queries by providing necessary suggestions on the Object Names, Object Properties (like column names etc.) etc. Usually people say that SSMS provides suggestions for those objects which exist on the database/server. However, there is an interesting fact about SSMS which many people do not know and that is, Intellisense provides suggestions even on those objects for which the DDL has just been written (not executed yet!) in the same window in which it is being accessed.

Let us take a look a simple example.

1. Go to SSMS and Open a New Query Window

2. Type in the following statements - one to change the database context to tempdb and the next one to create a new table SampleTable:

USE tempdb

CREATE TABLE [dbo].[SampleTable] (

ID INT IDENTITY(1,1),

Value1 NVARCHAR(255),

Value2 NVARCHAR(255)

)

3. Do not execute any of the above two statements.
4. Now start typing the below statement after the above two statements:

INSERT INTO dbo.S

As soon as you type in INSERT INTO dbo., it will list the SampleTable in the list of suggestions even though we have just written the script for SampleTable creation but have not created it yet. Below screenshot shows this in action.

Here is another instance of using a SEELECT * FROM query on the SampleTable before creating it.

Now write the INSERT INTO Statement or SELECT * FROM Statement completly to make it a valid SQL Statement and try to execute the completed statement without executing the CREATE TABLE Statement and you will see that you will get an error like the one below which shows that the object does not exist.

Sounds Simple but is Really Interesting :)

If you guys have come across any such interesting things, do share it by leaving a comment below.

My Experiences - Bad Experience at MEGAMART - Do Not Shop at MEGAMART

noreply@blogger.com (Dattatrey Sindol (Datta)) — Wed, 20 Jul 2011 15:21:00 +0000

Today I had a really bad experience with one of the so called big brands "MEGAMART".

I wanted to buy a pair of socks, so was thinking where I can buy as I could not buy during weekend as I was busy and if I had to buy during weekdays, I have to do it after my office hours. Since MEGAMART is on the way to my house, I decided to go buy there.

I have been purchasing stuffs at MEGAMART for quite some time and have shopped for about 20K or more in the past 1 or 1.5 years. I even have a membership card.

But, when I went to buy the socks today, first of all, I had less choice of colors and each pack had all socks of similar colors, which I didn't want. Somehow, after struggling for a while, I was able to get a pack with different colors (2 pairs same color, 1 pair different color) and I took a sigh of relief. The pack of socks which I took was worth 314 Rupees (INR).

Now, here comes the worst part.

I went to the billing counter, gave my membership card and asked that guy to bill it and pack it. To my surprise, the guy at billing counter asked me if I wanted a carry bag (to carry the socks); I thought he was asking me casually. So I said, why are you asking? Is there anything specific? You guys will anyways pack it right? Then that guy says "Sir if you need a carry bag to carry the stuffs which you buy, you need to pay 3 Rupees (INR) extra (apart from the cost of the purchased goods)" and I got really pissed off.

This is one of the worst experiences in the services industry. When there is so much competition around, to provide quality services, these so called big brands like MEGAMART are giving ads on their offers which look so attractive, but unfortunately when people go to buy stuffs, it simply sucks!!!

My Personal Experience in Simple English - "Do Not Shop at MEGAMART!!! It Just SUCKS!!!".

[Updated on 2011-07-30]: To my surprise, I found that out of the 3 pairs of socks in the pack, two pairs were ancle length and one was full length though I was told that all 3 are ancle length. MEGAMART SUCKS!!!

SQL Server Code Named "Denali" CTP3 is Available for Download

noreply@blogger.com (Dattatrey Sindol (Datta)) — Sat, 16 Jul 2011 18:02:00 +0000

This week has been a really exciting week for the SQL Server BI Community as Microsoft announced the release of SQL Server codenamed "Denali" CTP3 for evaluation/feedback from the community. I am really excited about this new CTP release and eager to start exploring the features.

This week I have been busy working on some training programs and will be busy for next few days. Planning to download and install this early next week and start exploring the new features. I will be posting about the new features and my experiences about CTP3 release.

Here are some useful links related to this release:

SQL Server Code Named "Denali" CTP3 Release Notes
System Requirements for Installing SQL Server Code Named "Denali" CTP3
Installation Steps/Options for SQL Server Code Named "Denali" CTP3
SQL Server Code Named "Denali" CTP3 Download Details (32-bit & 64-bit versions available)
Books Online for SQL Server Code Named "Denali" (MSDN)
Developer Reference for SQL Server Code Named "Denali" (MSDN)

So stay tuned to know more about the new features in this release.

Applying Logical Operators in TFS Work Item Queries

noreply@blogger.com (Dattatrey Sindol (Datta)) — Thu, 09 Jun 2011 18:43:00 +0000

Last day I was querying the work items in TFS to track the status of some of the work items. Usually I used to query the work items (Bugs, Tasks etc.) which are assigned to me. However, last day I needed to query the work items which are assigned to couple of other team members as well. I was able to easily add all the clauses in the query except for the "Assigned To" where I had to do a logical OR operator. Let's take a look at what my query looked like and how I got it working :)

So, basically I wanted the following output using the above query:

"List of all the Work Items which are Assigned To either of my 2 team members (2nd & 3rd Assigned To clauses in the above screenshot), which are not in a Closed State (They can be in Active, Resolved etc. states)".

Here is a pseudo code which is equivalent of the query in the above screenshot.

SELECT [All Types of Work Items]
FROM [One or More TFS Backend Database Tables]
WHERE
[Work Item Assigned To] = "1st Team Member"
OR [Work Item Assigned To] = "2nd Team Member"
AND [Work Item State] <> "Closed"

But I wanted my query to be something like this:

SELECT [All Types of Work Items]
FROM [One or More TFS Backend Database Tables]
WHERE
(
[Work Item Assigned To] = "1st Team Member"
OR [Work Item Assigned To] = "2nd Team Member"
)
AND [Work Item State] <> "Closed"

Now, as we can see that the output which I wanted as stated above, can be achieved using the above query. And as we can see that, the difference between the above two queries is the presence of brackets in the 2nd query which makes it logically correct.

But I was not able to achieve the equivalent of the 2nd query in TFS. After doing some research, I figured out that there is an option to Group Clauses in Work Item queries. Select the Clauses which you want to group (Hold shift key and select multiple clauses present one after the other in a sequence), right click and select Group Clauses from the context menu as shown below:

Once the clauses are grouped, then the TFS Work Item query looks something like this.

Now, in case we need to remove those brackets (grouping) then we just need to select the same set of clauses, right click and select Ungroup Clauses from the context menu as shown below.

It's as simple as that !!!

Reference: How to: Group or Ungroup Work Item Query Clauses on MSDN

Free E-book on Data Quality and Master Data Management

noreply@blogger.com (Dattatrey Sindol (Datta)) — Tue, 07 Jun 2011 09:47:00 +0000

SolidQ has released a new E-book on Data Quality and Master Data Management. This E-book is available for free download.

You can download the E-book using the following URL:

Data Quality and Master Data Management with Microsoft SQL Server 2008 R2 - Dejan Sarka & Davide Mauri

Happy Learning :)

Videos for Learning Microsoft BI

noreply@blogger.com (Dattatrey Sindol (Datta)) — Sat, 04 Jun 2011 20:32:00 +0000

When it comes to learning a new tool/technology, I have seen people working on other technologies especially the ones who are new to the technology, often struggle to find proper resources to learn or to get up to speed on their respective tool/technology.

Fortunately this is not the case with Microsoft BI (In general any Microsoft technologies). Especially with more and more organizations adopting Microsoft technologies and with the contributions made by a lot of people to the fast growing Microsoft BI community, there is a lot of useful content/resources (Websites/Blogs/Articles/Videos/Podcasts) available online for learning Microsoft BI. This content not only includes the basics of the technology, but also a lot of advanced concepts are included as well.

Below are some of the links to videos related to Microsoft BI. And the best part is, most them are free :)

Learning/Training

Community Events

Virtual TechDays
Tech-Ed
Professional Developers Conference (PDC)
SQL Down Under (Podcast/Audio Show)

In my experience, I have not come across any other products (Non-Microsoft) having as good documentation as that of Microsoft BI (SQL Server BI) on SQL Server Books Online. Also, the support offered by Microsoft and the community through msdn forums is excellent.

If you know of any other video content worth sharing with the community, leave a comment below with the URL and I will update the above list.

Happy Learning !!!

The World Without Engineers...!!!

noreply@blogger.com (Dattatrey Sindol (Datta)) — Wed, 18 May 2011 02:31:00 +0000

Have you ever imagined how this world would have been without engineers ? Here is you go :)

Be Proud to be an Engineer !!! An Imagineer !!!

Reference: Forwarded email from a friend.

Always Check-Out the Latest Version of Code from TFS

noreply@blogger.com (Dattatrey Sindol (Datta)) — Sun, 15 May 2011 10:05:00 +0000

When working with large teams, when multiple team members are working on the same project/module or the same piece of code, it is very essential that there is a strong version control system in place. And when it comes to Microsoft Platform, Visual Studio 2010 Team Foundation Server (TFS) is one of the most efficient version control tools available in the market.

When multiple developers are working on the same set of files, it is important that all the dependencies are taken care properly and before making any changes on the server, it is essential that we get a latest version of code from TFS to our local workspace, Check-Out the code and post that we can make changes and Check-In the code.

Often, when we perform a Check-Out operation in TFS, the workspace version of the code gets checked out, which sometimes may not be same as the server version due to various reasons like some other team member might have modified the file on the server, the developer himself might have modified the file in local (workspace version) without Checking-Out the latest version of file from server etc. As a result of this, when we simply perform a Check-Out operation without getting the latest version of code then we may end up losing the changes made by someone else on the server.

To avoid this, we can follow the below mentioned simple steps which will ensure that even if we just perform a simple Check-Out operation, TFS will get the latest version of code to our local workspace and then does a Check-Out which is effectively checking out the server version of code.

Open Visual Studio 2010
Connect to Team Project on Team Foundation Server
Go to Tools -> Options...
Go to Source Control in the left navigation pane and expand it
From the expanded menu, select Visual Studio Team Foundation Server
Now check the checkbox "Get latest version of item on check out" as shown below

Now, even if you forget to get latest before doing a Check-Out, Visual Studio/TFS will automatically ensure that a latest version of code is downloaded to your workspace and then a Check-Out is performed.

This makes our life a lot more simpler !!!

Until next time, happy learning :)

Note: This demonstration is based on Visual Studio 2010 Team Foundation Server (TFS)

Reference: http://msdn.microsoft.com/en-us/library/bb385989.aspx

Resources to Learn More about New Features in SQL Server code-named Denali CTP1

noreply@blogger.com (Dattatrey Sindol (Datta)) — Wed, 27 Apr 2011 18:53:00 +0000

Well, as we all know, CTP1 release of SQL Server 2011 code-named Denali is around for sometime and there are many articles written by folks from SQL Server Community. I have put together a list of some of the major features along with the links to external resources like articles, white papers etc.

This post is more of a self note. However, it might help anyone else interested to learn more about Denali CTP1.

Database Engine Enhancements

Programmability Enhancements

Columnstore Indexes [More Information: Link1, White Paper]
OFFSET N ROWS FETCH NEXT N ROWS ONLY (Extended ORDER BY Clause) for easy paging [More Information: Link1, Link2]
Sequences for generating sequence numbers [More Information: Link1, Link2, Link3]
Support for New Collation Type [More Information: Link1]
Full-Text Search [More Information: Link1, Link2]
Circular Arc Segment Support for Spatial Types [More Information: Link1, Link2, Link3]
EXECUTE Statement [More Information: Link1]
New THROW keyword to enhance error handling in TRY...CATCH blocks [More Information: Link1]
New Collation Support [More Information: Link1]
New DMV's and DMF's [More Information: Link1]
Performance Counter Changes [More Information: Link1]

Security Enhancements

Ability to Create New User-defined Server Roles [More Information: Link1, Link2]
Introduction of New Encryption Algorithms - The HASHBYTES function now supports the SHA2_256, and SHA2_512 algorithms for encryption
New Permissions [More Information: Link1]

Manageability Enhancements

Contained Databases for improved database portability [More Information: Link1, Link2, Link3]
Startup Options [More Information: Link1]

Usability Enhancements (SQL Server Management Studio - SSMS)

Zoom Query Surface [More Information: Link1]
Insert Snippet [More Information: Link1]
Surround With [More Information: Link1]
Floating Query Windows - Support for multiple monitors [More Information: Link1]
Debugger Enhancements [More Information: Link1]
Clipboard Ring [More Information: Link1, Link2]

SQL Server Integration Services (SSIS)

Programmability Enhancements

Project Improvements [More Information: Link1, Link2]
Introduction of Reverse Engineering [More Information: Link1]
Introduction of Parameters [More Information: Link1]

Project Parameters
Package Parameters

Execute Package Task Improvements [More Information: Link1]
Ability to execute SSIS Package from within T-SQL Scripts [More Information: Link1]

Migration and Deployment

Deployment Enhancements [More Information: Link1, Link2, Link3]

Impact Analysis and Data Lineage

Determining Impact and Data Lineage [More Information: Link1]
Dependency Services [More Information: Link1]

Usability Enhancements (Business Intelligence Development Studio - BIDS)

SSIS Toolbox - New Categories, Organization, & Improved Descriptions [More Information: Link1, Link2, Link3]
Flexible Authoring in the Data Flow Designer - Resolve References Editor [More Information: Link1, Link2]
Source and Destination Assistants [More Information: Link1]
Undo and Redo - [More Information: Link1]
Collapsible Grouping in the Data Flow Designer [More Information: Link1]
Simplified Data Viewer Configuration [More Information: Link1]
Zoom Package Design Surface [More Information: Link1]

Changes in Installation/Setup

Windows XP, Windows 2003, Windows 2008 RTM Operating Systems are Not Supported. [More Information: Link1]
Windows Vista SP2, Windows Server 2008 SP2, Windows 2008 R2 & Windows 7. [More Information: Link1]

Deprecated Features in Denali

Additional Links

Note: Don't install Denali BIDS side by side with a 2008 instance

In case there are any other interesting enhancements/features of Denali CTP1 or any other interesting articles related to the features of Denali CTP1, please let me know by leaving a comment so that I can update the above list accordingly for my reference as well as for the reference of my blog's readers.