Monthly Archives: September 2014


SQL Server 2012 Multidimensional vs tabular

Which model should you use, Multidimensional vs Tabular?

Well, there is no clear-cut answer, but there are some factors that can make you choose one over the other:

  • If you want to use DAX, you have to use Tabular
  • If you want to use Power View, you have to use Tabular
  • If your dataset is extremely large, go with Multidimensional
  • If you need writeback support, you have to use Multidimensional
  • If you need access to many different external data sources, choose Tabular
  • If you need complex calculations, scoping, and named sets, choose Multidimensional
  • If you need extreme speed and consistently fast query time, choose Tabular
  • If you need Many-to-Many relationships, choose Multidimensional (can be done in Tabular but difficult)
  • If your solution requires complex modeling, choose Multidimensional
  • If you need any of the following features, you must use Multidimensional: Actions, Custom Assemblies, Custom Rollups, Custom Drillthrough Actions (but BIDS Helper
More

URL Rewriting With ASP.NET and C hash

In this article we will learn how to use URL rewriting to map URLs for better search results and user friendly URLs. This comes in handy when we are working with query strings, but don’t actually want to display the query string values to the user. For instance, we could turn the URL ‘~/Default.aspx?page=Books’ into ‘~/Books’.

First, we will need to create a new ASP.NET Empty Web Site and add a new page to it named ‘Default.aspx’. For this example we will be rewriting two URL’s, a ‘Books’ page and an ‘Magazine’ page.

Let’s build our page with links to those pages. To begin, open Default.aspx and:

  • Add a hyperlink control ans set the text  property to Books.
  • Set the NavigateUrl property to ‘~/Books
  • Add a second hyperlink control and set the text property to Magazine.
  • Set the NavigateUrl property to ‘~/Magazine’.

Now we have two different links that we … More


Common Table Expressions

What is CTE (Common Table Expressions)

A common table expression (CTE) is a temporary result set accessible within the execution scope of a single SELECT, INSERT, UPDATE, DELETE, or CREATE VIEW statement.

CTE does not hold any physical space in database like a derived table. But Unlike a derived table, a CTE can be self-referencing and can be referenced multiple times in the same query.

We can also create Nested CTE to staging our data throughout multiple CTE’s

Below are the scenarios where a CTE can best fit:

  1. Recursive Queries Using Common Table Expressions.
  2. Instead of creating a view for only one query we can choose CTE. As like view it will not take create it’s definition inside metadata.
  3. Making queries readable (For complex and long queries)

Advantages : –

Use of CTE offers improved readability and ease in maintenance of complex queries. The query can be divided into blocks … More


Setting document mode of IE based upon browser mode at run time (C hash) 1

Recently I was working on a project that required me to force the user into IE9 document mode, but ONLY if they were using IE9 compatibility view (CV)  or  IE 10 mode.

The main elements that we need to look at are the Version token and the Trident token. Below are the tokens that you will find in each browser:

                                                          VERSION                   TRIDENT

IE9 StandardMSIE 9.0Trident/5.0
IE9 CVMSIE 7.0Trident/5.0
IE8 StandardMSIE 8.0Trident/4.0
IE8 CVMSIE 7.0Trident/4.0
IE7MSIE 7.0No Trident token
IE6MSIE 6.0No Trident token

As you can see, each browser has a unique combination of these two tokens. We can use this knowledge to now create a function that will tell us what browser mode is in use. My function is shown below:


private string GetIEBrowserMode()

{

string mode = "";

string userAgent = Request.UserAgent; //entire UA string

string browser 
More

JOINs in SQL Server 1

What is a join?

Before starting discuss on joins, first we should know what is join?
SQL Server is a RDBMS(Relation database management system), i.e. it has tables which holds rows and columns and relationship between multiple tables using keys. So when we have to get data from more than one table, we use joins to get it. Join in sql is used to combine rows from two or more table based on comparing one or more common fields.

Types of Joins in SQL

INNER JOIN:- Returns all rows when at least one match in both tables.

LEFT JOIN:- Returns all rows from left table and matched rows from right table.

RIGHT JOIN : – Returns all rows from right table and matched rows from left table.

FULL JOIN :- Returns all rows from both tables.… More


Restart Computer Failure issue during SQL Server 2008 uninstall

Problem:

Last night i was uninstalling SQL Server 2008 from my machine and got stuck with an issue “Restart Computer Failure”.

I tried several time but after goggling, i got the solution and now sharing with you all

Solution :

  1. Start Regedit
  2. Go to the key “HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Control\Session Manager”
  3. Delete “PendingFileRenameOperations” entry and try again
More

New features in SQL Server 2014 (In Database Engine)

New features added in SQL 2014 (Headings only)

Below are the enhancement points list :

Database Engine Feature Enhancements
o    Memory-Optimized Tables
o    SQL Server Data Files in Windows Azure
o    Host a SQL Server Database in a Windows Azure Virtual Machine
o    Backup and Restore Enhancements
o    New Design for Cardinality Estimation
o    Delayed Durability
o    AlwaysOn Enhancements
o    Partition Switching and Indexing
o    Managing the Lock Priority of Online Operations
o    Columnstore Indexes
o    Buffer Pool Extension
o    Incremental Statistics
o    Resource Governor Enhancements for Physical IO Control
o    Online Index Operation Event Class
o    Database Compatibility Level
·         Transact-SQL Enhancements
·         System View Enhancements
·         Security Enhancements
·         Deployment Enhancements

Visit below link for details;

http://msdn.microsoft.com/en-in/library/bb510411.aspxMore


Convert a comma separated list into table 4

In this post, we are going to learn “how we can convert a comma separated list into a table using a recursive CTE”. Below is the code:


DECLARE @vc_CommaSepValues VARCHAR(1000)
SET @vc_CommaSepValues = '1,R,TYF,776876,GHGF,6856,TYFT,1,R,TYF,776876,GHGF,6856,TYFT,1,R,TYF,776876,GHGF,6856,TYFT,1'

;WITH CTE AS
(
SELECT CAST(LEFT(@vc_CommaSepValues, CHARINDEX(',', @vc_CommaSepValues + ',') - 1) AS VARCHAR(MAX)) AS Val
,CAST(STUFF(@vc_CommaSepValues, 1, CHARINDEX(',', @vc_CommaSepValues + ','), '') AS VARCHAR(MAX)) AS RecVal
UNION ALL
SELECT CAST(LEFT(RecVal, CHARINDEX(',', RecVal + ',') - 1) AS VARCHAR(MAX)) AS Val
,CAST(STUFF(RecVal, 1, CHARINDEX(',', RecVal + ','), '') AS VARCHAR(MAX)) AS RecVal FROM CTE
WHERE RecVal > ''
)

SELECT Val FROM CTE
OPTION(MAXRECURSION 32767)

Why OPTION MAXRECURSION?

Hint OPTION(MAXRECURSION 32767) is a query hint which instructs to compiler to iterate 32767 times. Default, max recursion of a loop in SQL is 100 and in case we will not put this hint at the end, any string which requires more loop than 100 will raise an … More


Set Width of gridview columns dynamically when AutoGenerateColumns is true 1

Introduction

I faced a lot of problems when I asked to set the width of a gridview dynamically, that is the property AutoGenerateColumns set to AutoGenerateColumns=”true” and the gridview was directly bounded from a dataset in code behind. Thus, we don’t have bound fields to set the width of columns individually. I tried a lot of methods found over the internet but sadly anyone could not help me. But I have overcome the problem using some tricks and am now sharing this article with all of you so that if anyone has a problem like me, he will not get more frustrated.

Background

I used many methods found over the internet, but they could not work for me. Then I used both methods – that is one for bound field and another for setting the AutoGenerateColumns=”true” – individually in two projects and then when page got rendered in browser, I … More


Avoid Use of While Loops to get a Comma Seperated list of rows 1

Problem Detail

In the morning at your workplace, your manager or any colleague comes to you and asks to get the list of all the dates in a comma separated list.

Now the first and the quickest way of getting the output which will knock us is using a loop to get the list.

/*Getting list with While loop*/

DECLARE @fromdate DATETIME; SET @fromdate = '01-Sep-2014'

DECLARE @todate DATETIME; SET @todate = '30-Sep-2014'

DECLARE @vc_PVTColumns VARCHAR(1000); SET @vc_PVTColumns = ''

WHILE (@fromdate <= @todate)

BEGIN

SET @vc_PVTColumns = @vc_PVTColumns + '[' + CONVERT(VARCHAR(25), @fromdate, 106) + '], '

SET @fromdate = DATEADD(d, 1, @fromdate)

END

SELECT @vc_PVTColumns =LEFT(@vc_PVTColumns, LEN(@vc_PVTColumns) - 1)

SELECT @vc_PVTColumns

In above case, DB engine has to iterate for each and every value of day and do the same task again and again. The same thing can be implementedin SET based approach using COALESCE function with a … More