Different ranking functions in SQL Server 2005 and 2008 and strategy to remove duplicate records in ETL type apps

Quick glance of different ranking functions that we can use in SQL Server -

Ranking functions return a ranking value for each row in a partition. Depending on the function that is used, some rows might receive the same value as other rows. Ranking functions are nondeterministic.

SQL Server introduced four different ranking functions to rank the rows in partition or in general -
1 RANK
2 NTILE
3 DENSE_RANK
4 ROW_NUMBER

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

Example on different strategies to remove duplicate records in SQL server in a typical ETL OR OLTP type applications -

http://www.mssqltips.com/tip.asp?tip=1952

http://www.mssqltips.com/tip.asp?tip=1918

Thanks - D

Typical Sync up of tables scenario in ETL type projects

If you are faced by a scenario where you typically have to sync up 2 tables by comparing source, destination most of the times and writing Insert, Delete and Update statements to achieve that, SQL Server 2008 introduces MERGE feature which is very powerful and fairly easy to achieve such sync up issues quick.

Example - Products dimension table has information about the products; you need to sync-up this table with the latest information about the products from the source table. You would need to write separate INSERT, UPDATE and DELETE statements to refresh the target table with an updated product list or do lookups. Though it seems to be straight forward at first glance, but it becomes cumbersome when you have do it very often or on multiple tables, even the performance degrades significantly with this approach. In the article below the author walks you through how to use the MERGE statement and do this in one pass. :)

USE - The MERGE statement basically merges data from a source result set to a target table based on a condition that you specify and if the data from the source already exists in the target or not. The new SQL command combines the sequence of conditional INSERT, UPDATE and DELETE commands in a single atomic statement, depending on the existence of a record. The new MERGE SQL command looks like as below:


SYNTAX - MERGE [AS TARGET]
USING [AS SOURCE]
ON
[WHEN MATCHED
THEN ]
[WHEN NOT MATCHED [BY TARGET]
THEN ]
[WHEN NOT MATCHED BY SOURCE
THEN ];


Nice and easy article for quick reference on MERGE - http://www.mssqltips.com/tip.asp?tip=1704

Thanks - D

Use of "Coalesce" in SQL Server 2008

Use of "Coalesce" in SQL Server - efficent way to script and avoid mutiple Case statements and ISNULL.

COALESCE - Returns the first nonnull expression among its arguments.

Syntax
COALESCE ( expression [ ,...n ] )

Arguments
expression - Is an expression of any type.

n - Is a placeholder indicating that multiple expressions can be specified. All expressions must be of the same type or must be implicitly convertible to the same type.

Return Types - Returns the same value as expression.

Remarks - If all arguments are NULL, COALESCE returns NULL.

COALESCE(expression1,...n) is equivalent to this CASE function:

CASE
WHEN (expression1 IS NOT NULL) THEN expression1
...
WHEN (expressionN IS NOT NULL) THEN expressionN
ELSE NULL

Simple real life example from MSDN - http://msdn.microsoft.com/en-us/library/aa258244(SQL.80).aspx

Other many uses as well - http://www.mssqltips.com/tip.asp?tip=1521

(Fav one from this article = was to KILL all transactions forecfully that are locked and needed to be released before restoring the database -

------------------------------------------
DECLARE @SQL VARCHAR(8000)

SELECT @SQL=COALESCE(@SQL,'')+'Kill '+CAST(spid AS VARCHAR(10))+ '; '
FROM sys.sysprocesses
WHERE DBID=DB_ID('AdventureWorks')

PRINT @SQL --EXEC(@SQL)

Replace the print statement with exec to execute will give you a result set such as the following.
-----------------------------------------------

Thanks - D

"Google lecture" learning videos

Here are some interesting learning videos from "Google lectures" - http://www.youtube.com/watch?v=yjPBkvYh-ss

Talks include topics like - Cluster Computing and MapReduce...

Cheers - D

Fix for ASP.net security gap - this TU

Glad that Microsoft had such a quick fix :) - http://weblogs.asp.net/scottgu/archive/2010/09/27/asp-net-security-update-shipping-tuesday-sept-28th.aspx

-D

CAPTCHA Server Control for ASP.NET - protecting from spam

Looking to protect your public facing web sites from automated submission/spam - leverage CAPTCHA Server Controls readily availbale for ASP.NET -

ASP.NET captcha implementation - http://www.codeproject.com/KB/custom-controls/CaptchaControl.aspx

Telerik Captcha - www.telerik.com/products/aspnet-ajax/captcha.aspx

Google's ReCaptcha - https://www.google.com/recaptcha

-Dipesh

Security workaround for Sharepoint Server and Services 3.0

Microsoft recently released a Security Advisory for a vulnerability affecting ASP.NET. This post documents recommended workarounds for the following SharePoint products:

•SharePoint 2010
•SharePoint Foundation 2010
•Microsoft Office SharePoint Server 2007
•Windows SharePoint Services 3.0
•Windows SharePoint Services 2.0

Does impact - SharePoint Server 2007 and Windows SharePoint Services 3.0

Check for latest updates on MSDN blog here -

http://blogs.msdn.com/b/sharepoint/archive/2010/09/21/security-advisory-2416728-vulnerability-in-asp-net-and-sharepoint.aspx

-D

Security gap in ASP.net

Waiting for the patch to be released to plug the security gaps in ASP.net, Sharepoint sites or Dotnetnukes that allows client user to download UR most sensitive web.config file or decrypt UR Viewstate...

http://weblogs.asp.net/scottgu/archive/2010/09/18/important-asp-net-security-vulnerability.aspx

- D

URL Rewrite Module 2.0 reloaded

Check the powerful features with URL Rewrite Module 2.0 reloaded. you can download the extension from Micorsoft site here -
http://www.microsoft.com/web/gallery/install.aspx?appid=urlrewrite2

Check the video and more articles here -
http://www.iis.net/download/URLRewrite

Download samples (with source code) on examples on how to -
Store the rewrite or redirect mappings in a SQL database;
Store the rewrite or redirect mappings in a text file;
Store the lookup substrings in a text file.

Source Code

Cheers - D

SQL Server Parameter Sniffing issue - resolved

If you have a performance issue while calling the stored proc from ASP.net apps or SSRS reports then try this tip suggested by Chris on SQL parameter siffing fix.

Create PROC SP_NAME @param1 nvarchar(20)
AS
BEGIN
Select * from Customers where CustomerID = @param1
END

*** Declaring a local variable within stored proc and passing it to SQL just improves the performance of the execution of the stored proc while calling from .net applications.

Create PROC SP_NAME @param1 nvarchar(20)
AS

BEGIN
Declare @localvariable nvarchar(20)
SET @localvariable = @param1
Select * from Customers where CustomerID = @localvariable
END

http://elegantcode.com/2009/06/24/repost-watch-out-for-sql-server-parameter-sniffing/

Optimize your web applications to have faster response times by preloading your apps

IIS Application Warm-Up for IIS 7.5 enables IT Professionals to improve the responsiveness of their Web sites by loading the Web applications before the first request arrives. By proactively loading and initializing all the dependencies such as database connections, compilation of ASP.NET code, and loading of modules, IT Professionals can ensure their Web sites are responsive at all times even if their Web sites use a custom request pipeline or if the Application Pool is recycled.

The best feature which I think is the ability to customize which web pages you want to be loaded faster and for which users as well by customizing the pre-loading of applications.

IIS Application Warm-Up can be configured to initialize Web applications by using specific Web pages and user identities. This makes it possible to create specific initialization processes that can be executed synchronously or asynchronously depending on the initialization logic. In addition, these procedures can use specific identities in order to ensure a proper initialization.

You can download the extension here -
http://www.iis.net/download/applicationwarmup

Thanks - D

IE 9 test drive

Most of you may be aware... but Microsoft is making the IE9 platform preview available online here:
http://ie.microsoft.com/testdrive/

Some of the demos seem exciting but seem fairly limited at this moment. It seems to provide the capability to test your existing web applications on IE9 build version. :) However, it needs Vista or 7 image. Cheers – Dipesh

Building Large-Scale Applications with Microsoft Silverlight and developing using the Model-View-ViewModel pattern

Build Large-Scale Applications with Microsoft Silverlight @ MS PDC -

Learning video on Silverlight from John @ PDC - about Silverlight application development patterns such as composite applications with Prism, developing using the Model-View-ViewModel (MVVM) pattern, and methods of implementing large, modular, multi-page applications within your team. Hear about frameworks created to help assist in rapid development using these patterns without sacrificing good application development patterns.


HD video - http://microsoftpdc.com/Sessions/CL22
WMV viode (lighter version) - http://microsoftpdc.com/Sessions/CL22?type=wmv

Thanks - Dipesh

Enterprise Library 5 released...

Enterprise Library 5.0 released.... http://entlib.codeplex.com/Wikipage

Watch the video from the PnP site here - http://www.pnpguidance.net/post/EnterpriseLibrary5VideoPnPSummit.aspx

Thanks - D