Archive for January, 2013

Why do we Blog? Looking back … January 30th, 2013

Vinod Kumar

A decade back the concept of blogging was not that common and I am sure a lot of them including me jumped into the wagon. I started blogging back then with a number of different thoughts in mind. I am sure it has changed almost every couple of years. Now with close to 1400+ posts done, I thought of taking a reflection to what was done in the past and what motivates me / others into blogging. These are purely my views and would love to read your opinions too. Feel free to chime in.

Your blog is your unedited version of yourself ~ Lorelle

Early Days 

My early days were different. I didn’t quite understand what this whole blogging was. It was some sort of Links and resources that I used to post. Least did I understand what was the use for my audiences and how they can consume this data. But then the new social wave of Twitter and Facebook made me think twice. I found those as a great medium of sharing quickly the links that I have been reading. I started to re-think what should I blog now?

The first thing you need to decide when you build your blog is what you want to accomplish with it, and what it can do if successful ~ Ron Dawson

What will be the content ?

~3 years back, this brain wave did hit me and I needed to change my style. For a matter of fact, I didn’t have a style to be frank :). I started thinking – what to write? There are two schools of thought. Write something new, completely interesting and something that is not available elsewhere. Now the probability for creating such content is almost extinct. Trust me, I know few friends who keep saying this logic and I can see them almost non-existent in the blogging space. It is impossible to be truly+completely creative, innovative and new in every blog post you write – I would say almost impossible. The second school was to share our perspectives and our learning’s. That necessarily doesn’t mean they are completely new, but those can be our view. I loved to take that route now.

Blogging is best learned by blogging…and by reading other bloggers ~ George Siemens

Focus on your topics 

Here again there are two schools of thoughts. Pick a core narrow topic and dive deeply over your blogs or find one broad area and start exploring / expanding on that concept. Trust me, I thought this is my blog and let me share everything that I play with and things that symbolizes me. So here I talk from SQL Server, Management, Microsoft Office Productivity, Phone, SharePoint, Events I attend and more. This is not a personal blog but still professional enough to get everyone’s interest on various topics. So as you create your blog, find your comfort zone and the next point details how I mind-map things. Don’t forget, blog is your identity – it is YOU.

Build your own brand 

Once you have a topic / areas identified, it is critical we have a plan on how we execute the same. If you recollect over my blog, I have sort of various theme’s. Standard technical SQL content, What’z new in next version, Management and Leadership content, Personal Development content, Picture Posts, Links and Resources, Tips and Tricks as various sections. The rough workflow would include:

Readers subscribe to blogs when they provide an informational or entertainment value so great that it would be a loss to not subscribe to it ~ Maki

  1. Find the area’s where you feel there is enough you can write. Most importantly that needs to be interesting :).
  2. Outline few possible posts that you plan to write on them.
  3. Pick the obvious easy ones and get ready to write.
  4. Think about your readers and what they are going to expect out of it every time. You need to be cognizant of your current readers as well as potential future readers who will land here. Think, rethink if the topics are of great interest at large.
  5. Make a mind map on potential new people you want to target with your blogs.
  6. With 4 and 5 in place, balance the possible needs, interests, topics and questions our readers might ask on the very subject. Address them in one blog post.
  7. The best way you can get rid of your potential readers is by giving tons of jargons and getting them into a spiral of search engines where they finally forget where they started and what they were reading. Be careful of this syndrome and keep it simple.
  8. Make your posts creative with anecdotes and share your experience. People love to read and learn from your mistakes.
  9. Now once you have done with close to 10-15 posts on a area / topic. Move to the next ones.
  10. Do not try to experiment too much. Show come consistency and keep your audiences locked to what you write.

Don’t focus on having a great blog. Focus on producing a blog that’s great for your readers ~ Brian Clark

Why do you blog?

  1. … to be heard
  2. … to share your experience on a subject
  3. … to share the fun and learning
  4. … to air your opinion when you disagree
  5. … to use it like a diary and travel-log. Great way to look back.
  6. … to socialize and make this between close friends only
  7. … to reach as many people as possible with your ideas
  8. … to use it like an marketing engine and position your product
  9. … to get potential clients for a product / services company
  10. … to change something I think can be better
  11. … to make money. There is no shortcut here :) … Personally I haven’t liked the idea of ads till date over my blog / website. I think it will be the case for a while.
  12. … to reflect your inner self. Let that inner voice be heard.
  13. … to let your mind wander and write as you think. It is freedom of expression.
  14. … to get reactions on things that you believe otherwise.
  15. … to write something which we never get to write. I started writing and this has helped me to become a book writer way later (after 7+ yrs). I am still learning. This is your playfield.

Final words

Blogs are whatever we make them. Don’t box your thoughts, think as if there is no box to contain your thoughts. Blogs need to be natural, true to what you are, open, opinionated and ultimately must feel we are here to share. Thanks for reading this far. Do feel free to pass your comments.

Success is the sum of small efforts, repeated day in and day out ~ Robert Collier

Continue reading...


 

Excel Tip – Find invalid data January 28th, 2013

Vinod Kumar

Recently in our SQL Server Bangalore Usergroup we had an interesting question and I thought to write this blog so that others can also benefit from the same. The question from our member was, they have an Excel sheet which is a dump of data from another system (some Export). His problem involved identifying the invalid records from that dataset of dates which the file contains. Here we are not going to talk about moving the records, but in this post we will try to quickly identify which of these are invalid rows.

One possible Solution

I don’t want to say this is the ONLY option. But this for sure has been the easiest option for me. I would love to hear if you have other methods. We can use macros and other mechanisms too. But that is out of scope of this blog.

I start the task by formatting our column in the format it has been presented to us. For this I have used a quick format option by selecting the “column B” in this example. We can select custom format based on the input we receive.

image

Next we will use the DATA Tab to add some validations. Here I have selected “Data Validations” and given some range based on the values I have got. We can also give values “greater than”, “lesser than” etc. In our example I have given between two date ranges.

image

Now the interesting part comes. Use the drop down of “Data Validation” and select “Circle Invalid Data”.

image

The magic of Excel starts and we can see the invalid rows marked. The sample output looks like this:

image

Hope you enjoyed this exploration. These options have been inside Excel for a long time, we just need to identify them and explore these options to make Excel out tool for productivity. If you want to remove the validation Circles, just select “Data Validation” and “Clear Validation Circles”.

In future blogs, I will share other tips that I come across using Excel and other Office productivity tools. Do feel free to drop-in your comments if you found this interesting.

Continue reading...


 

Picture Post: How to beat your competition January 24th, 2013

Vinod Kumar

If you’re not making mistakes, you’re not taking risks, and that means you’re not going anywhere. The key is to make mistakes faster than the competition, so you have more changes to learn and win.

Do we have a situation where we don’t have any competition? I doubt there is no place in this world to hide. One of the biggest suggestions I give my friends is to draw a bigger line when we are challenged with competition. We cannot make the competitions line smaller, that is futile – best is to make us better than them. Do things that control your outcome rather than negatively working on your competition. Think bigger and move forward.

PS: I know a lot of people use circle for these diagram, I thought to use the same concept in rectangular figure :). Nothing wrong in being different.

I have been up against tough competition all my life. I wouldn’t know how to get along without it – Walt Disney

The concept is simple. When working with competition:

  1. If you do things differently but constantly care about what competition is doing. Just the negative thought will bring you down.
  2. If you don’t care of the competition but you constantly do the same thing as your competition. You are no different from them – you are bound to fail.
  3. The victory spot is – Do things differently without blurring your thoughts of what competition does is a great place.

Finally, I don’t say you should not track competition. Know them, keep them at an arms distance.

You have competition every day because you set such high standards for yourself that you have to go out every day and live up to that – Michael Jordan

Continue reading...


 

MS Access functions inside SQL Server January 23rd, 2013

Vinod Kumar

Many a times an enterprise application has its roots in smaller application developed using a small-scale backend database services. Microsoft Access is a premier Rapid Application Development tool which a rich client application using JET engine. When such application needs to be migrated to use Microsoft SQL Server engine or SQL Azure, there is a challenge in porting the code. Since MS Access database provides access to certain expression calculation and built-in functions, it may become a non-trivial exercise to MS Access based application. To assist with this, SQL Server 2012 has introduced few built-in functions, which are syntactically and semantically same as MS Access.

CHOOSE

Returns the item at the specified index from a list of values.

Usage: CHOOSE ( index, val_1, val_2 [, val_n ] )

Post from SQLAuthority.

DATEFROMPARTS

DATETIME2FROMPARTS

DATETIMEFROMPARTS

DATETIMEOFFSETFROMPARTS

TIMEFROMPARTS

SMALLDATETIMEFROMPARTS

Returns a specific date or datetime data type value for the specified year, month, and day.

Usage: DATEFROMPARTS ( year, month, day )

  1. DATETIME2FROMPARTS ( year, month, day, hour, minute, seconds, fractions, precision )
  2. TIMEFROMPARTS ( hour, minute, seconds, fractions, precision )
  3. DATETIMEFROMPARTS ( year, month, day, hour, minute, seconds, milliseconds )
  4. DATETIMEOFFSETFROMPARTS ( year, month, day, hour, minute, seconds, fractions, hour_offset, minute_offset, precision )
  5. SMALLDATETIMEFROMPARTS ( year, month, day, hour, minute )

Post from SQLAuthority.

EOMONTH

Returns the last day of the month that contains the specified date, with an optional offset. The offset is month (and not date). This would be a good function for many Line Of Business and reporting applications. It would be quite easy to find out what was last day of month 3 months ago or 3 months from now.

Usage: EOMONTH ( start_date [, month_to_add ] )

Post from SQLAuthority.

FORMAT

Returns a value formatted with the specified format and optional culture. Recommended use of this function is to do a location (culture) aware conversion of strings to date time data types. For general conversion, CAST and CONVERT are still preferred built-in functions.

Usage: FORMAT ( value, format [, culture ] )

This function relies on existence of CLR being installed. If CLR is not installed (and or cannot be loaded in SQL Server 2012), the call to FORMAT function will fail. Also execution of this function will load CLR within SQL Server if not already loaded.

Details from SQLAuthority.

IIF

Returns one of two values, depending on whether the Boolean expression evaluates to true or false. IIF is a shorthand way for writing a CASE statement and is internally converted as such. All the limitations of CASE statement also apply to IIF.

Usage: IIF ( boolean_expression, true_value, false_value )

Post from SQLAuthority.

TRY_CONVERT

Returns a value cast to the specified data type if the cast succeeds; otherwise, returns null. The function can return error if casting to destination data type is explicitly not allowed. For example if you try using this function to cast an INT value to XML value, it will return an error (and not just return NULL).

Usage: TRY_CONVERT ( data_type [ ( length ) ], expression [, style ] )

Post from SQLAuthority.

LOG/LOG10

LOG returns the natural logarithm of the specified float expression, and optionally takes base as input. LOG10 returns the base-10 logarithm of the specified float expression.

Usage: LOG ( float_expression [, base ] )

LOG10 ( float_expression )

CONCAT

Returns a string that is the result of concatenating two or more string values. This is simply a string concatenation function. Return type is dependent on what input types are sent. Typically, return value is of highest precedent data type. For example, multiple varchar and nvarchar of less than 4000 bytes are sent, final output is of nvarchar of 4000 bytes in size (and may be truncated). On the other hand, if at least one value is nvarchar(max) or varchar(max), the final output is of nvarchar(max) size. Note, your arguments could be of non-string data type as well as long as they can be implicitly converted to string data type.

Usage: CONCAT ( string_value1, string_value2 [, string_valueN ] )

Details from SQLAuthority.

PARSE/TRY_PARSE

Returns the result of a string expression, translated to the requested data type. PARSE function will return error if cast/translation fails, while TRY_PARSE will return null if the cast fails. This can be useful function for location (culture) aware expression calculation.

Usage: TRY_PARSE ( string_value AS data_type [ USING culture ] )

PARSE ( string_value AS data_type [ USING culture ] )

Details from SQLAuthority.

Continue reading...


 

SQL Server 2012: Memory Counter Changes January 15th, 2013

Vinod Kumar

Memory Counter

In performance tuning exercises I am used to querying and using Performance counters at large. This is one source of high-level troubleshooting that we advocate to our customers for a quick effective start. Once we know where the problem is, we get into detailed analysis using other tools. Several memory counters that were present in the Buffer Pool objects – for example, Buffer Manager, Buffer Partition and Buffer Node are removed in SQL Server 2012. New counters that reflect status of memory manager components are added to the system monitor objects: Memory Manager, Memory Node. It is key to know what was added because these help us in future troubleshooting.

Counters removed in SQL Server 2012

SQLServer:Buffer Manager

  1. Free pages
  2. Total pages
  3. Target pages
  4. Reserved pages
  5. Stolen pages
  6. AWE lookup maps/sec
  7. AWE stolen maps/sec
  8. AWE write maps/sec
  9. AWE unmap calls/sec
  10. AWE unmap pages/sec

SQLServer:Buffer Partition

  1. Free pages
  2. Free list requests/sec
  3. Free list empty/sec

SQLServer:Buffer Node

  1. Free pages
  2. Total pages
  3. Foreign pages
  4. Stolen pages
  5. Target pages

The following memory related counters have been added in SQL Server 2012:

SQLServer:Memory Manager

  1. Database Cache Memory (KB)
  2. Free Memory (KB)
  3. Reserved Server Memory (KB)
  4. Stolen Server Memory (KB)

SQLServer:Memory Node

  1. Database Node Memory (KB)
  2. Free Node Memory (KB)
  3. Foreign Node Memory (KB)
  4. Stolen  Memory Node (KB)
  5. Target Node Memory
  6. Total Node Memory

SQLServer:Buffer Node

  1. Local Node page lookups/sec
  2. Remote  Node page lookups/sec

These were important to know because during performance tuning, I have a tendency to use perfmon templates. In a recent testing, I found that some of these counters were different and were missing in my analysis. On closer debugging is when I understood, some of these counters have been removed because of the memory changes made to SQL Server.

Continue reading...