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
Wednesday, October 20, 2010
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
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
USING
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
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
Monday, September 27, 2010
"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
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
-D
Tuesday, September 21, 2010
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
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
•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
Monday, September 20, 2010
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
http://weblogs.asp.net/scottgu/archive/2010/09/18/important-asp-net-security-vulnerability.aspx
- D
Tuesday, July 20, 2010
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
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/
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
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
Wednesday, July 07, 2010
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
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
Thursday, April 22, 2010
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
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
Watch the video from the PnP site here - http://www.pnpguidance.net/post/EnterpriseLibrary5VideoPnPSummit.aspx
Thanks - D
Monday, November 30, 2009
Accenture Plc and Adchemy Teams to Create Enterprise-Ready Digital Marketing Platform...
Accenture Plc and Adchemy Teams to Create Enterprise-Ready Digital Marketing Platform
Accenture Plc and Adchemy, a company in audience relationship management (ARM), announced that they have formed a global alliance to help large companies and their agency partners build more effective digital experiences with their customers. The alliance will give clients a new way to identify, engage and acquire customers. As part of the alliance agreement, Accenture has taken a minority equity stake in Adchemy and Tim Breene, Accenture`s senior managing director of Strategic Initiatives, has joined Adchemy`s Board of Directors.
http://www.reuters.com/finance/stocks/keyDevelopments?symbol=ACN
-D
Accenture Plc and Adchemy, a company in audience relationship management (ARM), announced that they have formed a global alliance to help large companies and their agency partners build more effective digital experiences with their customers. The alliance will give clients a new way to identify, engage and acquire customers. As part of the alliance agreement, Accenture has taken a minority equity stake in Adchemy and Tim Breene, Accenture`s senior managing director of Strategic Initiatives, has joined Adchemy`s Board of Directors.
http://www.reuters.com/finance/stocks/keyDevelopments?symbol=ACN
-D
Sunday, November 22, 2009
MS Application Architecture Guide...
Looking for an "Application Architecture Guide" that is intended to help solution architects/designers design and build effective, high quality applications?
Microsoft has released 2nd edition of Microsoft Application Architecture guide @
http://www.microsoft.com/downloads/details.aspx?displaylang=en&FamilyID=ce40e4e1-9838-4c89-a197-a373b2a60df2
This guide helps you to:
1 Understand the underlying architecture and design principles and patterns for developing successful solutions on the Microsoft platform and the .NET Framework.
2 Identify appropriate strategies and design patterns that will help you design your solution's layers, components, and services.
3 Identify and address the key engineering decision points for your solution.
4 Identify and address the key quality attributes and crosscutting concerns for your solution.
5 Create a candidate baseline architecture for your solution.
6 Choose the right technologies for your solution.
7 Identify patterns & practices solution assets and further guidance that will help you to implement your solution.
- D
Microsoft has released 2nd edition of Microsoft Application Architecture guide @
http://www.microsoft.com/downloads/details.aspx?displaylang=en&FamilyID=ce40e4e1-9838-4c89-a197-a373b2a60df2
This guide helps you to:
1 Understand the underlying architecture and design principles and patterns for developing successful solutions on the Microsoft platform and the .NET Framework.
2 Identify appropriate strategies and design patterns that will help you design your solution's layers, components, and services.
3 Identify and address the key engineering decision points for your solution.
4 Identify and address the key quality attributes and crosscutting concerns for your solution.
5 Create a candidate baseline architecture for your solution.
6 Choose the right technologies for your solution.
7 Identify patterns & practices solution assets and further guidance that will help you to implement your solution.
- D
Sunday, November 08, 2009
Visual Studio 2010 Professional download and .NET Framework 4 Training kit...
Visual Studio 2010 and .NET Framework 4 Beta 2 Ready to Download...
VS 2010 Professional version-
http://www.microsoft.com/downloads/details.aspx?FamilyID=a80dfb5d-51c6-4778-8656-a9ff29d3a132&displaylang=en
There's a training available for Preview as well here on MSDN site -
http://www.microsoft.com/downloads/details.aspx?familyid=752CB725-969B-4732-A383-ED5740F02E93&displaylang=en
-D
VS 2010 Professional version-
http://www.microsoft.com/downloads/details.aspx?FamilyID=a80dfb5d-51c6-4778-8656-a9ff29d3a132&displaylang=en
There's a training available for Preview as well here on MSDN site -
http://www.microsoft.com/downloads/details.aspx?familyid=752CB725-969B-4732-A383-ED5740F02E93&displaylang=en
-D
Thursday, November 05, 2009
Accenture LLP receives P&G Supplier Excellence Award...
Accenture LLP receives a "Supplier excellence award" from P&G for FY 2009 yesterday night for consistently performing against expectations for service delivery, innovation and being a great business partner. One of the 55 suppliers that received an award among P&G’s 80,000 suppliers and the only one in our IT field besides HP beating our competitors who are not listed anywhere :)
http://www.pg.com/news/supplier_awards_release_final.shtml
Cheers - time to celebrate :) - DJ
http://www.pg.com/news/supplier_awards_release_final.shtml
Cheers - time to celebrate :) - DJ
Tuesday, September 22, 2009
ROI from MS
Here's a nice link for ROI from MS - http://nucleusresearch.com/microsoft-roi-support-center/
Sunday, February 08, 2009
Accenture Vision 2009 - "Our Elastic Future"
Featured in the CIO article "Our Elastic Future" Accenture Technology Labs identified 5 dimensions of this "Everything Elastic" world and major trends for 2009.
You can read the entire version on "Our Elastic Future" in the CIO magazine.
Thanks - Dipesh
You can read the entire version on "Our Elastic Future" in the CIO magazine.
Thanks - Dipesh
Monday, September 29, 2008
Silverlight 2 RC released
Silverlight 2 RC released.... from Scott's blog ....
http://weblogs.asp.net/scottgu/archive/2008/09/25/silverlight-2-release-candidate-now-available.aspx
http://weblogs.asp.net/scottgu/archive/2008/09/25/silverlight-2-release-candidate-now-available.aspx
Visual Studio 2010 and .NET 4.0 Framework
Microsoft Unveils Next Version of Visual Studio and .NET Framework
The press release just went live over on MS press pass. Go and have a look at the enticing screenshots and read the Visual Studio Team System (VSTS) 2010 overview on MSDN. This is the release formally code-named “Rosario”.
And best of all, Brian Keller is running Visual Studio Team System 2010 Week on Channel9. With new videos every day this week, they are not to be missed!
Reveals extensive enhancements for simplified application life-cycle management, provides sneak peek at all key focus areas for Visual Studio 2010 and the .NET Framework 4.0.
Details on Microsoft site:
http://www.microsoft.com/presspass/press/2008/sep08/09-29VS10PR.mspx
The press release just went live over on MS press pass. Go and have a look at the enticing screenshots and read the Visual Studio Team System (VSTS) 2010 overview on MSDN. This is the release formally code-named “Rosario”.
And best of all, Brian Keller is running Visual Studio Team System 2010 Week on Channel9. With new videos every day this week, they are not to be missed!
Reveals extensive enhancements for simplified application life-cycle management, provides sneak peek at all key focus areas for Visual Studio 2010 and the .NET Framework 4.0.
Details on Microsoft site:
http://www.microsoft.com/presspass/press/2008/sep08/09-29VS10PR.mspx
Thursday, August 28, 2008
Monday, August 25, 2008
Silverlight Beta 2 Released...
Silverlight Beta 2 Released
Scott Guthrie just published this on his blog here -
http://weblogs.asp.net/scottgu/archive/2008/06/06/silverlight-2-beta2-released.aspx
Scott Guthrie just published this on his blog here -
http://weblogs.asp.net/scottgu/archive/2008/06/06/silverlight-2-beta2-released.aspx
Monday, August 11, 2008
VS .net 3.5 and TFS SP1 shipped
Brian Harry just posted this news that - Microsoft released VS .net 3.5/TFS SP1.
At a very high level, the improvements include:
Improved WPF designers
SQLServer 2008 support
ADO.NET Entity designer
Visual Basic & Visual C++ improvements (including an MFC-based Office 2007 style 'Ribbon')
Team Foundation Server improvements - detailed here
Richer JavaScript support, enhanced AJAX and data tools and web site deployment improvements
A new .NET profile for client applications that is much smaller and installs much faster
Substantial .NET performance improvements (particularly in startup time)
And much, much more...
Here are the downloads -
Visual Studio Team System 2008 Team Foundation Server Service Pack 1 -http://go.microsoft.com/fwlink/?LinkId=124829
NET Framework 3.5 Service Pack 1 - http://go.microsoft.com/fwlink/?LinkId=124150
If you have setup issues, a great first resource is Heath's blog: http://blogs.msdn.com/heaths/archive/tags/VS+2008+SP1/default.aspx
Here's a useful entry about collecting setup log information: http://blogs.msdn.com/heaths/archive/2008/05/09/visual-studio-and-net-log-collection-utility.aspx
At a very high level, the improvements include:
Improved WPF designers
SQLServer 2008 support
ADO.NET Entity designer
Visual Basic & Visual C++ improvements (including an MFC-based Office 2007 style 'Ribbon')
Team Foundation Server improvements - detailed here
Richer JavaScript support, enhanced AJAX and data tools and web site deployment improvements
A new .NET profile for client applications that is much smaller and installs much faster
Substantial .NET performance improvements (particularly in startup time)
And much, much more...
Here are the downloads -
Visual Studio Team System 2008 Team Foundation Server Service Pack 1 -http://go.microsoft.com/fwlink/?LinkId=124829
NET Framework 3.5 Service Pack 1 - http://go.microsoft.com/fwlink/?LinkId=124150
If you have setup issues, a great first resource is Heath's blog: http://blogs.msdn.com/heaths/archive/tags/VS+2008+SP1/default.aspx
Here's a useful entry about collecting setup log information: http://blogs.msdn.com/heaths/archive/2008/05/09/visual-studio-and-net-log-collection-utility.aspx
Subscribe to:
Posts (Atom)