Calendar table , again...

Hello friends.
I am back to start writing posts about all things that related to SQL Server. There are lots of examples over a internet about how create a calendar table in T-SQL. 
I would like to show you a little bit different technique to create a calendar table which based on the time interval. Let say we need to create a table with one hour interval, I have done it recently for our BI team to create Power BI reports.
Simple we are going to use a recursivequery to create such table.

with tmp(plant_date) as

(

   select CAST('20200101' as datetime)  --- Start date

   union all

      select DATEADD(hour, 1, plant_date)

     from tmp

    where DATEADD(hour, 1, plant_date)   < '20281231'

)/*After we create a simple table  you can extract an year , month, a day and etc.*/

select plant_date,cast(plant_date as date) date,YEAR(plant_date) Year,

CASE WHEN DATEPART(MONTH, plant_date)<10 THEN '0'

              +

              CAST(DATEPART(MONTH, plant_date) AS VARCHAR(2))

              ELSE CAST(DATEPART(MONTH, plant_date) AS VARCHAR(2))

              END Month,

                DATENAME(MONTH,plant_date),

              CASE WHEN DATEPART(day, plant_date)<10 THEN '0'

              +

              CAST(DATEPART(day, plant_date) AS VARCHAR(2))

              ELSE CAST(DATEPART(day, plant_date) AS VARCHAR(2)) END day,

              DATEPART(hour,plant_date) hour

  from  tmp

option (maxrecursion 0)

I hope the above script helps  you.

Make sure that you write/create optimized SSIS Packages

Recently I worked on the package that loops many text files and insert the data into a staging table and then from the staging into original table which has  five columns only defined as VARCHAR(50) in the database. Pretty simple , not? Text files have many data and the destination table has many columns (90+) and very wide data tape client defines (VARCHAR(3000)). Actually he needs to get only few columns but  who cares, right? We have observed that the memory during the package execution grow  rapidly and very quickly MSSQL Server service was become unavailabe. You can imagine that during this spike nobody can work and a local DBA started getting complains from the end users. I would like to say that the server is very strong (4CPU+ 28RAM)...So what's problem?

At the source component , the estimated sized of a row is determined by the maximum columns sizes of all columns returned by the query (remember we have 90+ columns defined as VARCHAR(3000)). This is where  the performance problem resides.

After sometime investigations we dropped the wide stage table and created a stage table with exactly same structure as original table has.
That means columns have VARCHAR(50) and we also changed the job schedule to run it frequently to deal with small data sets.  So performance was drastically improved. We have not seen any more memory growing and not reducing, there is no more compliance from the end user.

Please read this article if you deal with packages especially if it works with large data sets.
http://technet.microsoft.com/en-us/library/cc966529.aspx

How to fix the fragmentation on heaps?

We know that heaps are the tables without clustered index on. It may have many non clustered index  (NCI) but still it is considered a heap. So consider a huge table (heap) that is fragmented. How would  you fix it? Technically, you cannot defrag a heap table.  But remember we have ALTER table REBUILD command which works very well on the heaps. But there is one big But. If you have many NCI on the table this command will rebuild all of them at once. What does it mean? It can produce transaction-log bloat which hits the over all performance. Think about the process the scan the log or perhaps Log Shipping job that needs to move the log file to the remote server. All this affects performance.

So, to answer the question ,we need to create a clustered index on that table..That is simple answer to the question.

Happy New Year to all!

One more method of using UNPIVOT command

Hi friends
I had recently a client who has a table with more than 90's column and his requirement was to return the all columns that IS NOT NULL. So starting with sample T-SQL you will need to filter each column to check for NULLs, like WHERE col1 IS NOT NULL or col2 IS NOT NULL... or perhaps even using an aggregation  to eliminate NULLs. But I found pretty nice solution, so take a look at below DDL. We have a table with number of columns (Dayn) to be checked for NULLs. I used simple UNPIVOT output of multiple rows into multiple columns in a single row.

DECLARE @Table TABLE
(
UserId INT,
Day1 INT NULL,
Day2 INT NULL,
Day3 INT NULL,
Day4 INt NULL,
Day5 INT NULL,
Day6 INT NULL,
Day7 INT NULL,
Day8 INt NULL,
Day9 INT NULL,
Day10 INt NULL
);

INSERT INTO @Table(UserId,
 Day1, Day2, Day3, Day4, Day5, Day6,Day7, Day8,Day9, Day10)
VALUES(1,null,null,20,3,null,null,null,null,null,null);
INSERT INTO @Table(UserId,
 Day1, Day2,  Day3, Day4,Day5, Day6,Day7, Day8,Day9, Day10)
VALUES(2,50,25,15,5,null,null,null,null,null,null);


SELECT UserId, Col_NotNull FROM @Table
UNPIVOT (Col_NotNull FOR DayNumber
IN (Day1, Day2,  Day3, Day4,Day5, Day6,Day7, Day8,Day9, Day10)) AS c


UserId Col_NotNull
1        20
1        3
2        50
2        25
2       15
2       5

SQL Server 2012 IIF function may replace CASE expression?

Hi
Did you install already SQL Server 2012? Well, see a new T-SQL function named IIF you can use sometimes instead of CASE expression.
http://msdn.microsoft.com/en-us/library/hh213574.aspx


CREATE TABLE #t (id INT)

INSERT INTO #t VALUES (1),(2),(3)

DECLARE @a int = 3;
DECLARE @b int = 2;

SELECT * FROM #t WHERE id= IIF ( @a > @b, @a, @b )

Returns Id=3.

You can even using nested IIF commands.
SELECT * FROM #t WHERE id= IIF ( @a > @b, IIF(@a>0,@b,0), @b )

Identity property remains gaps

Let say you have a table and use identity property. It is ok, but I really hope that you are aware of the bug that remains gaps in identity if you restart MS SQL Server service. Please vote http://connect.microsoft.com/SQLServer/feedback/details/739013/alwayson-fail

over-results-in-reseed-of-identity

Thanks

Successful upgrade to SQL Server 2012

Hi everyone. Today I successfully upgraded our production database to the new version -SQL Server 2012. Actually everything went ok, and after running Upgrade Advisor and restored the database into a new server. The "challenge" was to upgrade existing SSRS reports and SSIS packages. What I would recommend is to open a new project in SQL Data Tools (yes BIDS is gone) and adding report by report to the project. SQL Server automatically upgraded them for first time I run them. Another thing is that now you can much easily to configure SSRS and even if you specified not to "configure" during the installation. I have not noticed any performance degradation since we moved from SQL Server 2005.So lets enjoy new features that were introduced and happy working with SQL Server 2012.