Kasper de Jonge PowerPivot Blog

This is something I have used many times, and always had to look up on internet. I’m designing a new datawarehouse and have to populate my date dimension. In SQL server 2008 we have common table expressions (CTE),  We can use it to run a sequence from to a date. This blog post will explain how you can generate dates: http://smehrozalam.wordpress.com/2009/06/09/t-sql-using-common-table-expressions-cte-to-generate-sequences/

You can set you regional date / time by using the Set language dutch (where dutch is your regional setting).

I was searching for some query to get information from my DWH without me having actually access, i found a set of pretty heavily commented queries that are very useful for detecting and diagnosing many common performance issues with SQL Server 2008 created by Glenn Berry

Check them here:

http://glennberrysqlperformance.spaces.live.com/blog/cns!45041418ECCAA960!2015.entry

While Virtualization of hardware is getting hotter by the day, Ms teched created a blog post about virtualization:

Should I virtualize SQL Server? What benefits will I see?  What do I need to consider when making this decision?  These are some of the questions we are frequently asked as Virtualization continues to be a hot topic.  The short answer is yes – Microsoft Virtualization (Windows Server 2008 R2 Hyper-V + Microsoft System Center) delivers significant benefits including reduced costs through server consolidation and power and space savings, improved server utilization, greater agility for responding to dynamic business needs, rapid server provisioning, reduced management complexity and reduced downtime during failover with Hyper-V Live Migration.

Read the rest at: http://blogs.technet.com/dataplatforminsider/archive/2009/09/02/microsoft-virtualization-best-choices-for-sql-server.aspx

I was at a client and they had some performance issues calling stored procedures from code. They were unable to find the sql statementfor responsible the performance degrade.

You can use the SQL profiler to show you the TSql statement within your stored procedure and their trace properties, to do this go to trace properties, check Show all events and you will get the possibility to check 2 events that will look inside the SP’s and show you alle the Tsql statements.

Having to de-duplicate rows from source tables is a very common task in the data integration world, we were trying to do all different of Tsql to filter out the duplicate values. We needed to pick the row based upon a certain criteria. On the internet i found the TSQL function Rank()

It works like this:

No matter where the source data is stored, as a part of the ETL workflow, we will stage source data in a SQL Server 2005 table.

This is the staging table:

create table SourceWithDuplicates
(SurrogateKey int identity(1,1),
Code varchar(12),
Description varchar(20),
OtherAttribute1 int,
OtherAttribute2 Varchar(10),
TieBreakerColumn Varchar(12))

As a general rule I always add a surrogate key column to my staging tables (that makes some DBAs happy); an in this case it would actually help in the de-duplication process.

Some sample data:

Insert into SourceWithDuplicates values(‘Abc’, ‘Row Description 1′, 1, ‘Other1′,’1′)
Insert into SourceWithDuplicates values(‘Abc’, ‘Row Description 1′, 1, ‘Other1′, ’1′)
Insert into SourceWithDuplicates values(‘Abc’, ‘Row Description 1′, 1, ‘Other2′,’2′)\
Insert into SourceWithDuplicates values(‘Def’, ‘Row Description 2′, 1, ‘Other4′,’a')
Insert into SourceWithDuplicates values(‘Def’, ‘Row Description 2′, 1, ‘Other5′,’a')
Insert into SourceWithDuplicates values(‘Ghi’, ‘Row Description 3′, 1, ‘Other5′,’a')
Insert into SourceWithDuplicates values(‘Jkl’, ‘Row Description 4′, 1, ‘Other5′,’a')

The requirements:

Code and description values need to be unique in the target table; in case they are duplicates, choose the row with the highest value in the tie breaker column. If the value in the tiebreaker column is also duplicated; then choose any of the rows.

So the data looks like:

SurrogateKey Code Description OtherAttribute1 OtherAttribute2 TieBreaker ———— ———— ——————– ————— ————— ———-
1 Abc Row Description 1 1 Other1 1
2 Abc Row Description 1 1 Other1 1
3 Abc Row Description 1 1 Other2 2
4 Def Row Description 2 1 Other4 a
5 Def Row Description 2 1 Other5 a
6 Ghi Row Description 3 1 Other5 a
7 Jkl Row Description 4 1 Other5 a

Following the requirements, only 4 rows kept should be chosen; those with surrogate keys:2 3, 4 or 5, 6 and 7
This is the query using the Rank() function that resolve our issue:

Select *,
rank() over (Partition by Code, description order by TieBreakerColumn desc, SurrogateKey) MyRank
from SourceWithDuplicates

and this is its output:

Surrogatekey Code Description MyRank
———— ———— ——————– ——————–
3 Abc Row Description 1 1
1 Abc Row Description 1 2
2 Abc Row Description 1 3
4 Def Row Description 2 1
5 Def Row Description 2 2
6 Ghi Row Description 3 1
7 Jkl Row Description 4 1

Notice that if we filter the result from the previous query using WHERE MyRank=1 we will get the rows to be kept.

Very nice ! I  found this at:

http://rafael-salas.blogspot.com/2007/04/remove-duplicates-using-t-sql-rank.html

Today two colleagues wanted to schedule the output of a SQL command to a text file so they could check some values on a daily base. After some clicking in SQL Server i found a very simple solution,

I created a SQL server Job Step which executed the SQL command

Then went to the Advanced tab and selected an output file to where the data can be written (even appended):

Very simple way to log query output to a file at a regular interval.

I needed to determine tha last day of previous month in a view, i found this on internet and works great:

Following script demonstrates the script to find last day of previous, current and next month.
----Last Day of Previous Month
SELECT DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,GETDATE()),0))
LastDay_PreviousMonth
----Last Day of Current Month
SELECT DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,GETDATE())+1,0))
LastDay_CurrentMonth
----Last Day of Next Month
SELECT DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,GETDATE())+2,0))
LastDay_NextMonth

found this at http://blog.sqlauthority.com/2007/08/18/sql-server-find-last-day-of-any-month-current-previous-next/

I was testing the perfomance of a query from different perspectives and wanted to make sure SQL Server didn’t cache Query results, to achieve this:

run DBCC DROPCLEANBUFFERS, which clears all data from the cache. Then run DBCC FREEPROCCACHE, which clears the stored procedure cache.
 

found at:

http://www.devx.com/tips/Tip/14401