Sampe Script to Create Varchar(Max) from Adventure Works DB

IF OBJECT_ID(‘dbo.SQLServerLog’, ‘U’) IS NOT NULL
DROP TABLE dbo.SQLServerLog;

create table dbo.SQLServerLog
(
SQLLogID Int Identity(1,1),
SQLLog varchar(max)
)
Insert into dbo.SQLServerLog
SELECT
”” + Convert(Varchar(max),DatabaseLogID) +
”” + ‘,’ + ”” + DatabaseUser +
”” + ‘,’ + ”” + [Event] +
”” + ‘,’ + ”” + [Schema] +
”” + ‘,’ + ”” + [Object] +
”” + ‘,’ + ”” + [TSQL] +
”” + ‘,’ + ”” + Convert(varchar(max),XmlEvent) + ””
FROM [AdventureWorks2012].[dbo].[DatabaseLog]
Where DataLength(XMLEvent) > 8000
–Order by 1 desc

Select * from dbo.SQLServerLog

Difference between Continuous Integration & Continuous Delivery

What is Continuous Integration and What is not Continuous Delivery ?

Often I have been asked “What is the difference between Continuous Integration and Continuous Delivery”. Thought I will shed some light on this topic.

Martin Fowler has described Continuous Integration as “A development practice that requires developers to integrate code into a shared repository … ”

In addition Thought works also explain Continuous Delivery as “An approach in which teams ensure that every change to the system is releasable, and release any version with the push of a button.”

So what is the difference?

Continuous integration allows multiple developers to integrate the source code into a single code base. This helps the team members to identify integration related issues early and address quickly. CI will lead to minimal SIT cycles.

CI

Once you have implemented Continuous Integration, then you should start working towards Continuous Delivery. Continuous delivery will make sure the changes are ready to deploy to production if required.

CD

Fancy to read more ?

 

Why do I need to learn PowerShell now?

I am well settled and very happy with my day-to-day job, So why do I care about learning PowerShell script ?  This is the thought I had in my mind few years back. However a SQLBits session unleashed the potential of PowerShell. Since then I am learning and using PowerShell a lot. The PowerShell knowledge enable me to automate all activities related to deployment and helped me to achieve the Continuous Integration in the early stages.
So if you are like minded person and asking the same question then read further. I have highlighted the areas where I have seen tremendous benefits of PowerShell.

 
1)    Deployment automation for Microsoft BI
PowerShell will help you to access the functionality of all Microsoft Business Intelligence applications (SSIS, SSAS and SSRS). You can access SQL server to deploy new tables or you can execute a SSIS package.

 

2)    Microsoft’s recommended way for automation
Microsoft has got a good support for PowerShell. It is the standard and recommended automation tool. PowerShell can be used for Windows Server, SharePoint, Office  365, Azure , SSIS, SSAS and SSRS. PowerShell is the first choice to automate the configuration and management activities.

 

3)    PowerShell Automation is a key skill
If you are managing a release for many servers at the same time, then many things could go wrong. Now stay cool with PowerShell script automation. This will reduce manual errors and increase the productivity within the team. It is a key skill for release managers and DevOps consultants.

 

4)    Greater support for DevOps / Continuous Integration tools
Most of the popular tools in DevOps (Chef, Puppet, Jenkins, Bamboo, TeamCity,etc)  does support PowerShell. So you can leverage the PowerShell to support for Build and deployment automation.

 

5)    Easy to learn
If you are familiar with .net framework, then you will be able to learn PowerShell quickly, as the PowerShell has been developed based on .NET.
So, what are you waiting for? Start your PowerShell journey today.

 

Learn PowerShell basics here
https://www.darkoperator.com/powershellbasics/
Bit lazy to read ? You can watch a video to gear up your skill here
https://channel9.msdn.com/Blogs/Taste-of-Premier/PowerShellBasicsPart1
Super excited and want to use PS in your day to day activities?
Deploy your SSIS projects with the help of PowerShell
https://www.simple-talk.com/sql/ssis/deploying-multiple-ssis-projects-via-powershell/
Enable continuous delivery for SSRS
https://www.mssqltips.com/sqlservertip/4481/implement-continuous-delivery-for-sql-server-reporting-service-reports/

Continuous Integration (CI) Tools

ci

After my last blog post, I have got very good response from individuals and organisations for help. Based on their request, I have shortlisted and booked few appointments to help them.

Last week, I had a session with an individual to help him to implement continuous integration for their Data Warehouse. I have discussed about the possible options and recommended few approaches to proceed. I am glad, I was able to share my experience and help them to take the first step.

During the session we have discussed about various CI tools available in the market and thought I will provide a little blog post to help others as well.

Continue reading “Continuous Integration (CI) Tools”

Giving back to the community & Free Goodies

I have learned a lot from the community and it’s my turn to give back to the community now. I am available (free of cost) for 30 mins consultation for each week.
Where can I help ?

I worked on these areas extensively and I would like to offer help.
1)    Continuous Integration / Continuous Delivery for BI applications
2)    Deployment automation
3)    Building test framework

How does it work ?
You can contact me on using the “Contact me”  page on my blog. I recommend you to write few lines about the problem you are having in your organisation. This will enable me to understand whether I can be of any help. If I can help, then I will let you know the date and time for a 30 min call.

During this call, I will share my thoughts and may propose a solution. This may not resolve all your issues. But at least it will give you a direction to proceed.
After the call, I will write a blog post about the problem and the suggested solution. This would help others, if they come across the same issue.

Few rules:
1)    Provide an official mail id for communication. I am committed and serious about this initiative and I expect the same from you
2)    Time duration is strictly 30 min
3)    No training or Career counselling during this time

Null – Special Data Value

What is NULL ?

Null denotes that a data value which is not exist in the database. The term “NULL” has been coined by E.F.Codd.

Null usually represent Inapplicable or Missing information in the system. In SQL Server NULL is a keyword/reserved word.

 

Is Null as same as Zero ?

No, Missing or inapplicable information can’t be assumed as Zero value.

 

The price of a product can be a numeric  value or “Don’t know the price”. If we don’t know the value of price then it will be classified as null.

 

NULL Keyword

SQL server uses the keyword NULL to differentiate among other data values.

 

A simple example to understand about null

 

SQL  Code Snippet:

Declare @tabEmployee table (ID int, Namenvarchar(255), Department nvarchar(50), Salarydecimal(18,2))

 Insert into @tabEmployee (ID,Name,Department,Salary)

values (1000,’Sam’,’Sales’,50000),

(1001,’Mark’,’Operations’,Null),

(1002,’Tom’,Null,70000),

(1003,’Nancy’,”,90000)

Select ID ,Name,Department,Salary from@tabEmployee

 

Null for numeric data type:

Salary_Null

It has been observed that we don’t have the department information about the employee Mark. Hence a null has been presented to denote the missing information.

 

Null for string data type:

Department_Null

As we don’t know the  Department name for the employee Tom, it has been represented as NULL.

 

How to compare null value ?

As null is a special data value , it is not possible to compare using the equal sign.

Null_Compare

Hence we need a special operator to handle values with NULL. The “IS NULL” operator will be useful to compare against the NULL data values.

IS_NULL

The operator “IS NULL” can be used for string data types as well

IS_NULL_String

Is zero length string  or blank value is as same as Null ?

No,  The below mentioned query and result set confirms the fact that only nullable values can be compared using “IS NULL”.

NULL_Blank_String

Agile Estimates for SSRS Report Development

How to do Agile Estimates for SSRS report development?

Agile estimation is an art and it will take reasonable time to gain experience. These simple steps will help you out.

Step 1: Assess the complexity of the report by asking these high level questions

  1. How many sources have been involved?
  2. How many datasets need to be developed (Apprx) ?
  3. How many Tablix / Table can be expected ?
  4. How many parameters are needed to be generate the report ?
  5. Presentation and formatting requirements
  6. How many no of Sub Reports / Drill Down reports are associated with this report ?

Step 2: Now decide the story point (or use the T-shirt size estimation techniques)

 

General Guidelines from experts:

  • As a general practice, it is accepted that the Development team will be in a better position to estimate after Sprint-2  or 3. Only after this time, the Sprint velocity can be used for future sprint planning.
  • Avoid estimating like an expert. Always assume that a developer who has got reasonable experience will be doing the development.
  • As a team, make use of the Sprint retrospectives and learn from mistakes.

Frequently Asked Questions:

  1. How to do the estimation for Sprint-1 ?

You could use previous experience to come up with a rough estimation.

Learn more about SQL Server

How do we improve our SQL knowledge and access what we know ?

The following links will help to improve SQL Server knowledge

http://www.sqlservercentral.com/

You will learn about a specific topic in SQL Server. The stairway series are an excellent way to accelerate knowledge in no time.

 

https://www.mssqltips.com/

You can find a solution to an uncommon problem here. Quick and easy to read tip in a page.

 

http://blog.sqlauthority.com/

An SQL server encyclopaedia !. I can’t even imagine how he is able to write about each and every feature about SQL server.

 

Joes 2 Pros Book Series

A SQL book has been written in easy to understandable format. I love Rick Morelan’s examples for every concepts.

 

http://beyondrelational.com/puzzles/

The SQL puzzles are a great way to access your knowledge. Its fun J. Take a challenge a day to improve your confidence.

 

Online SQL Test

I have written a post about online SQL Test