Title

comment code using Razor view engine

Tue, 15 May 2012 00:00:00 GMT

I just learn from mvc tutorial That we can comment code in Razor view engine also This is syntax for comment code Single line @* single line comment. *@ Multiline @* Line1 Line2 Line3 *@ Thank You

HotKeys to comment in VisualStudio/ManagementStudio

Tue, 15 May 2012 00:00:00 GMT

ManagementStudio & Visual Studio:

press [Ctrl+KC] to comment code (works also on selected lines), but comment out only whole lines.
press [Ctrl+KU] to uncomment code (works also on selected lines), but comment out only whole lines.

but for VS, at HTML source, it may comment only parts of code.

A Great Chart for SQL Server Unofficial Builds that made our work easy.

Tue, 15 May 2012 00:00:00 GMT

Few hours before I was checking the special features, fixes, and builds of MS SQL Server. I got a great blog...So interesting....This contains unofficial build chart lists all of the known KB articles, hotfixes and other builds of MS SQL Server 2012, 2008 R2, 2008, 2005, 2000 and 7.0 that have been released.

Compare numbers stored as characters seperated by space

Tue, 15 May 2012 00:00:00 GMT

In SQL forums, one of the memebers asked this question.

"In the table a varchar column has values like '2.2020 30 4.0000', '2.20200 30 4.00', etc. When I pass a parameter with the value '2.202 30 4.0' , the above value should be returned as output. How do I do it?"

(Note that 2.2020 and 2.20200 from column value and 2.202 from parameter value are same when compared as Numeric values)

Well. My first advice is not to store multiple values as characters in the single column. Normalization should be applied. If there is no chance to modify the existing structure, there are two ways to compare

1 Split the column value and parameter value by space and compare each number (This whill be most suggested as usual)

2 Do not use any split function. Make some changes in the column and parameter to effectively compare the value.

Ok. We will see how to implement Method 2

Consider the following set of data

declare @t table(col varchar(100))
insert into @t
select	'2.2020 30 4.0000' union all
select	'2.2020 30 4.05000' union all
select	'2.20200 30 4.00200' union all
select	'2.2020 300 4.00' union all
select	'2.202000 30 4.0000' 


declare @s varchar(100),@sql varchar(max)
set @s= '2.202 30 4.0' 
set @sql=''
select 
	@sql=@sql+
		'select '''+col+''' as original_col,sum('+replace(col,' ','+')+') as col1,sum('+replace(@s,' ','+')+') as col2,'''+@s+''' as variable_val union all ' 
from @t
select @sql='select original_col as col_val,variable_val from ('+left(@sql,len(@sql)-11)+') as t where col1=col2 and replace(replace(original_col,''0'','' ''),'' '','''')=replace(replace(variable_val,''0'','' ''),'' '','''')'
exec(@sql)

Now look at the result

col_val            variable_val
------------------ ------------
2.2020 30 4.0000   2.202 30 4.0
2.20200 30 4.00    2.202 30 4.0

If you have any other method please post it in comment's section

#0160-SQL Server 2012–Deprecated Features-32-bit systems - AWE (Address Windowing Extensions) no longer supported

Mon, 14 May 2012 00:00:00 GMT

For anybody who has worked with software products or recently bought a new piece of computer hardware, the terms “32-bit support” and “64-bit support” would be familiar.

One of the prime differences between the 32-bit & 64-bit systems is the amount of memory that can be addressed by the operating system. 32-bit systems cannot address a memory space that is greater than 3GB. While 64-bit systems can run a 32-bit application under the Windows-On-Windows shell, these applications would not be able to take advantage of the increased memory addressing capabilities of the underlying operating system. Insufficient memory on a SQL Server host puts the system under undue memory pressure . Memory management is therefore a tricky and critical issue for 32-bit applications running on 64-bit environments.

SQL Server 2005 introduced a switch on the SQL Server instance that allows the instance to use “AWE” (Address windowing extensions) for memory allocation. What this switch does is that it allows a 32-bit instance of SQL Server to access memory that is greater than 3GB. The screen-show below shows the location of this switch in the “Server Properties” dialog of the SSMS for SQL Server 2008.

Server properties dialog for SQL Server 2008

However, things are changing with SQL Server 2012 – Address Windowing Extensions (AWE) is no longer supported.

Server properties dialog for SQL Server 2012

This switch also has an equivalent advanced option called – “awe enabled”. Attempting to access this option in SQL Server 2012 results in an error.

--Display advanced configuration options
sp_configure 'show advanced options',1
RECONFIGURE
GO
--Attempt to fetch the value of the AWE flag
sp_configure 'awe enabled'
GO
--Hide advanced configuration options
sp_configure 'show advanced options',0
RECONFIGURE
GO

Here's the result:

Configuration option 'show advanced options' changed from 0 to 1. Run the RECONFIGURE statement to install.
Msg 15123, Level 16, State 1, Procedure sp_configure, Line 62
The configuration option 'awe enabled' does not exist, or it may be an advanced option.
Configuration option 'show advanced options' changed from 1 to 0. Run the RECONFIGURE statement to install.

Soltuions

There are no workarounds to this (in my opinion, teams shouldn't even be thinking in this direction). It is imperative that the 64-bit edition of the SQL Server must be used in order to access over 4GB of physical memory.

References:

On SQL Server Books On Line: Enabling AWE memory for SQL Server
Pinal Dave (B|T) post: AWE Explained in simple words

Until we meet next time,

Be courteous. Drive responsibly.

sysname does not allow null by default

Mon, 14 May 2012 00:00:00 GMT

sysname is a special datatype and by default it does not allow NULL, you need to explicitly add NULL in table definition to allow null. sysname is defined as nvarchar(128) not null Below code will give error declare @t1 table ( c1 int, c2 sysname) insert into @t1 select 1, NULL

Adding Line break in SSRS expression( Reporting Services )

Mon, 14 May 2012 00:00:00 GMT

**& VbCRLF &**

Which TCP/IP port does SQL Server run on?

Mon, 14 May 2012 00:00:00 GMT

SQL Server runs on port 1433.

What is difference between STUFF and REPLACE in SQL Server?

Mon, 14 May 2012 00:00:00 GMT

**REPLACE** is used to replace all the occurances of the given pattern in a string. *Example:* select Replace('Nirav','N','R') *O/P:* Rirav **STUFF:** This function is used to replace the part of string with some other string. *syntax:* STUFF (string_expression, start, length, replacement_characters) *Example:*SELECT STUFF('Nirav''s Blog is USEFUL',9,4,'DATABASE') *Output:* Nirav's DATABASE is USEFUL

Short answer code questions coming soon!

Sat, 12 May 2012 00:00:00 GMT

Microsoft is proud to announce that short answer code questions will soon be available in an exam near you. Short answer code questions test your ability to write code that will solve the problem described in the question.

SQL job scheduled run skipped due to long running job

Fri, 11 May 2012 00:53:00 GMT

If SQL agent finds a job running at schedule job start time, agent skips the execution of job until the next scheduled execution time. This can lead to some missing rows. I have tried to demonstrate the same thing below.

Step-1 Create a Job with runs every two minutes and add below statement in job step just to simulate the real life scenario.

waitfor delay '00:01:10'
go

Step-2 Set schedule to run every minute

Leave job for few minutes. After that if we look at the job history, we can see that few executions of jobs are skipped. We have scheduled it to run very minute but its getting run every two minutes.

Step-3 Delete the job.

Apparently this looks like a not a big deal. But if we are doing some data processing like populating the search table or sending data to third party application or may be running SSIS packages and this condition is not taken into consideration, it can be a data loss.

Actually, today I find this as root cause for search not displaying some data in one proprietary system. Sometimes Job was running for more time due to blocking issues. Of course if proper tracking mechanism is used in Procedure, we can avoid the situation, but sometimes developer tends to miss these little things which can cause some issues.

With help of below query, we can check for any skipped execution due to this problem and having time between two consecutive scheduled executions less than one day.

use msdb;
go
with  scheduleCTE
as
(
select 
case freq_type 
     when 4
     then case freq_subday_type  when 1 then  24*60*60 
                                 when 2 then  freq_subday_interval
                                 when 4 then  freq_subday_interval * 60
                                 when 8 then freq_subday_interval * 60 *60
          end
      when 8
      then case freq_subday_type when 1 then 24*60*60 
                                 when 2 then  freq_subday_interval
                                 when 4 then  freq_subday_interval * 60
                                 when 8 then freq_subday_interval * 60 *60
          end
      when 16
      then case freq_subday_type when 1 then -1
                                 when 2 then  freq_subday_interval
                                 when 4 then  freq_subday_interval * 60
                                 when 8 then freq_subday_interval * 60 *60
          end
      end as scheduledDuration
, schedule_id                                                 
from msdb.dbo.sysschedules
)

select sj.name as jobName 
, dateadd(second,CAST((right(Right('000000' + cast(sjh.run_time as nvarchar(10)),6),2)) as int)
        ,dateadd(minute,CAST((substring(Right('000000' + cast(sjh.run_time as nvarchar(10)),6),3,2)) as int) 
        ,dateadd(hour, CAST((left(Right('000000' + cast(sjh.run_time as nvarchar(10)),6),2)) as int) 
        ,cast(CAST (sjh.run_date as varchar(10)) as datetime)))) runDateTime
, sjh.run_duration as runDurationInSeconds
, sc.scheduledDuration scheduledDurationGapInSeconds
from dbo.sysjobhistory sjh
inner join dbo.sysjobs sj on sj.job_id = sjh.job_id
inner join dbo.sysjobschedules sjs on sjs.job_id = sjh.job_id
inner join scheduleCTE sc on sc.schedule_id = sjs.schedule_id
where sjh.step_id = 0 and sjh.run_duration > sc.scheduledDuration
order by jobName;

Switch active window to different monitor

Fri, 11 May 2012 00:00:00 GMT

I just accidently learned that, below is the shortcut to change the monitor of Active window. **Windows Key + Shift + Left( or Right) direction key**

#0159-SQL Server-Row Constructors-Triggers process the entire batch at once

Thu, 10 May 2012 00:00:00 GMT

Almost every product comes with a set of predefined system default data, which may or may not be editable by the user. When writing about returning result sets from triggers, I realized that there are very few product teams who use row constructors (introduced in SQL Server 2008) to populate their default data.

What these teams fail to realize is that many a times, the conventional approach may have a negative impact on the time it takes to insert the data and also reduces the overall efficiency of the entire data load operation. Let’s see a demo.

The Test Setup

To demonstrate the point, I will create a small table in the tempdb database with an INSERT trigger on it that would return us the inserted values from the INSERTED view.

USE tempdb
GO
CREATE TABLE dbo.RowConstructorDemo (RowId INT IDENTITY(1,1),
                                     RowValue VARCHAR(30),
                                     RowDescription VARCHAR(100)
                                    )
GO

CREATE TRIGGER dbo.trig_RowConstructorDemo_Insert
ON dbo.RowConstructorDemo
FOR INSERT
AS
BEGIN
  --Some business logic here
  SELECT ROW_NUMBER() OVER (ORDER BY inserted.RowId) AS [RowNumber],
         inserted.RowId,
         inserted.RowValue,
         inserted.RowDescription
  FROM inserted
END
GO

The Conventional Method

Now, let us try to insert a couple of rows using the conventional style of data insertion, i.e. row-by-row approach.

USE tempdb
GO
--Conventional approach
--Study the number of times the trigger is called
INSERT INTO RowConstructorDemo (RowValue, RowDescription) SELECT 'OS1','Microsoft Windows 2003 Operating System'
INSERT INTO RowConstructorDemo (RowValue, RowDescription) SELECT 'DB1','Microsoft SQL Server 2005'
GO

The INSERT trigger that we have defined on our test table returns us two (2) result sets:

Using Row Constructors

If we use row constructors to insert data, we see that the INSERT trigger returns us only one (1) result set. This is because it is one INSERT statement, and therefore all records are inserted in a single batch.

USE tempdb
GO
--Row Constructor approach
--Study the number of times the trigger is called
INSERT INTO RowConstructorDemo (RowValue, RowDescription)
VALUES ('OS2','Microsoft Windows 2008 R2 Operating System'),
       ('DB2','Microsoft SQL Server 2012')
GO

Comparing Conventional Method v/s Row Constructors

We have 3 INSERT statements as part of the test. They are simple INSERT statements and therefore, when both approaches are submitted together as a single batch, each INSERT statement contributes to approximately 33% of the total cost.

The Conventional approach has 2 INSERT statements, and therefore, the conventional approach takes up 33 * 2 = 66% of the total cost.

On the other hand, the row constructor method has only one INSERT statement, and therefore takes only 33 * 1 = 33% of the total cost.

Please note that the overall cost & performance analysis is dependent upon multiple factors, but overall, row constructors have always been faster than individual inserts for me. They have also been performant and efficient because the subsequent triggers work on batches of rows as opposed to a single row at a time.

Conclusion

If your application/product uses pre-defined static data, I trust that the above post will at the very least inspire you to revisit the individual INSERTs and replace them with row constructors.

Until we meet next time,

Be courteous. Drive responsibly.

Posting an image/screen shot in a blog post

Thu, 10 May 2012 00:00:00 GMT

There are several things that I consider when uploading an image on my blog posts. I thought of sharing it here hoping that some people may find it helpful (and of course, I expect some of you to disagree with my views :-)

Keep image file size minimal

In my early days, I made several mistakes with the size of image files. I used to capture screen shots with print-screen followed by a paste into MS Paint which by default saved the image files as BMP files. These files resulted in higher download time and disturbed overall blog viewing experience.

My recommendation is that you save the image files into other formats such as JPG or PNG (PNG is my favorite these days) which will produce relatively smaller file size.

Keep image size (width/height) to minimal

In most cases, we may not need an entire capture of the desktop to demonstrate something. According to me, the image given below is bad.

In a case like above, I would not capture an entire screen. Instead, I would prefer to capture only the specific area that I want to highlight. So my version of the above image will be as follows:

This image is more readable/comprehensible than the original version.

What if we need to show the whole window?

Sometimes we may need to show the entire screen to the user. For example, you may be writing a blog post demonstrating the new features added to the SSMS application. In such a case, you may need to show an entire screen and highlight various parts of the toolbar, status bar, menu etc.

In such a case, I would try to resize the window to the smallest possible size (without loosing the relevant information I am trying to show). For example, the the image given below shows a regular image of the SSMS 2012 window.

A resized version of the above image may look like the example given below. It is much more readable than the version given above.

There are a few more tips I would like to share, which I will write as a follow up post. I hope that some of you may find the information posted above helpful.

Guidelines to configure SQL server on developer box

Wed, 09 May 2012 00:00:00 GMT

Warning:- Settings mentioned below are listed by keeping developer workstation in mind. Even if some setting might applicable for live production server, please refrain using the same script or recommendation from this post against SQL production/UAT/QA server.

In developer environment it helps in many ways if you have database is on same machine as your application. This is also needed if need to do some work offline. As Developer edition comes at cost around 2000 Rs. or $50 and if you have proper MSDN subscription, you can install it without encoring any additional cost. Due to all these reasons, many developers have SQL server installed on their workstations.

In our development environment, we need to run lots of other applications other than SQL server, all these applications need resources. It helps if we can reduce the amount of resources used by SQL server. Below is the list of all such options we can consider in development environment.

Please note that all these settings are listed below are for SQL 2008 Developer edition, some of these settings are not applicable to earlier version of SQL or expression edition. Also based on your application.nature of work this setting is not applicable or may produce unwanted results. So, please go through the summary of each setting before applying.

1) Change max server memory

This is most important setting in development environments, as SQL tries to get as much memory available and because of this other applications starves for memory. Generally, I tend to allocate half of memory here. If you have more than one instance of SQL running, this should be a rather less value depending on instance usage. Setting max memory means that, SQL will not use more memory for cache, though there are some SQL processes which use memory outside this limit.

--Query to find out amount of memory on system
select physical_memory_in_bytes/1048576 MemoryAvailableinMB 
from sys.dm_os_sys_info 
go 
exec sp_configure 'show advanced options', 1
go
reconfigure
go
--generally half of total memory availabe
exec sp_configure 'max server memory (MB)', 2000
reconfigure
go

2) Change max degree of parallelism

In development phase, there are chances that we do not have optimal indexes available for query and which tends to increase the cost of a query. Increased cost can lead to the Parallelism. Parallelism has some overhead associated with them, so we can reduce the amount of Parallelism a particular query use, by setting

select count(scheduler_id) as AvailabeSchedulers 
from sys.dm_os_schedulers where scheduler_id < 255
go
exec sp_configure 'max degree of parallelism',2
go

3) Change Cost degree of threshold

If you do not want to change the max degree of parallelism for whatever reason, you can set this option to control the parallelism. If estimated query cost goes beyond this then and then parallelism will be considered. Default cost threshold is 5. The value need to set will differ from application to application. But for example

exec sp_configure 'cost threshold for parallelism', 50
reconfigure
go

4) Change tempDB Size

By default tempDB is created with only 8MB of data size, which leads lots of auto growth of tempDB files. So, for development environment it’s good to set some startup value.

use [master]
go
alter database [tempdb] modify file ( name = N'tempdev', size = 102400KB , filegrowth = 51200KB )
go
alter database [tempdb] modify file ( name = N'templog', size = 51200KB , filegrowth = 25600KB )
GO

5) Enable Backup Compression By default

Many times we face the situation where a colleague wants a DB backup. Compressed backups saves the space and generally backups and restore are quicker if compression is used. For SQL server 2008 and above we can compress the backup by using with Compression option, but this is not the default option. We should definitely set this value.

EXEC sp_configure 'backup compression default', '1';
RECONFIGURE
GO

6) If not needed change recovery mode of all user databases to SIMPLE

It’s good to set the database to SIMPLE recovery mode as we do not need the log and we do not take log backups. Please not that minimally log operations are faster in this recovery mode so if you are doing any performance tuning, see if minimally log operation is in used. This query needs to be executed for all such databases.

--find out all such database
select name,recovery_model_desc from sys.databases where database_id> 4 and recovery_model != 3
go
--this needs to be executed for all databases
alter database [DBName] set recovery simple
GO

7) If you are changing the recovery mode to SIMPLE, its good to truncate the log file

Now, if you have changed the DB mode to Simple, there is no meaning of keeping big log files. This will free up some space on the drive if you running out of the space. Below query will create SQL statements for all user databases in SIMPLE recovery mode.

select 'USE '+ d.name + '; DBCC ShrinkFile(' + quotename(df.Name,'[') + ',TruncateOnly)'
from sys.databases  d
inner join sys.master_files df on d.database_id = df.database_id
where d.database_id> 4 
and d.recovery_model = 3
and df.type = 1
go

8) Mark database which are not used frequently as offline

Sometimes developer environment tends to have more than one copy of production databases of different dates. All databases which are not needed very frequently should be set to offline mode. This will increase the startup time. Please note that of you drop the database while its offline database files are not getting deleted. Below query needs to be executed for all such databases.

alter database [DBName] set offline

9) Enable instant file initialization

Enable instant file initialization speeds up auto-grow of data files, restoring database and SQL startup. Kindly have a look at this post from Brad McGehee to see how to enable instant file initialization. Most of the time in development environment to get this enabled but its worth to check it.

10) There are chances that IT guy has installed all features even if it is not needed. If you are not using any of below services in developer environment, stop the service and disable it.

SQL Server agent (used for scheduling jobs, SSRS subscription etc.)
SQL server Reporting service
SQL server Analysis Service
SQLWriter:- SQL Volume Shadow copy service

All the above settings are based on my observation, if you feel something can be added to list, please post a comment, I will be more than happy to include this in the list.

Update(5/11/2012):- Updated script for point 7 as per comment from Dubelewskyj Oleksandr

Question of the month May 2012 - How does direct usage of column alias work in ORDER BY clause?

Wed, 09 May 2012 00:00:00 GMT

Consider the following two statements

--Query 1

select 
	name, 
	dateadd(day,datediff(day,0,create_date),0) as date_only 
from 
	sys.objects
where 
	date_only>getdate()-100

--Query 2

select 
	name, 
	dateadd(day,datediff(day,0,create_date),0) as date_only 
from 
	sys.objects
order by 
	date_only

While the first query throws an error Invalid column name 'date_only'. ,How does the second query work?

Easy way to Learn Keyboard Shortcuts in SSMS by Enabling ScreenTips

Wed, 09 May 2012 00:00:00 GMT

All of us like Keyboard Shortcuts and use it frequently but memorizing them might be painful and we get to remember them after we use it over a period of time. I just found a very easy way to learn and remember the Keyboard Shortcuts in SQL Server Management Studio (SSMS) using the following steps: 1. Open SSMS, Go to Tools | Customize... 2. In the Customize Window, enable/check the options "*Show ScreenTips on toolbars*" and "*Show shortcut keys in ScreenTips*" 3. Click Close Once you do this, you get to see the name of the action along with the associated Keyboard Shortcut in the Tooltip for pretty much every action/button in SSMS like Execute, Parse, New Query, Comment, Uncomment, and so on. **Note**: The above steps are tested with SSMS 2008/2008 R2. [1]: http://dattatreysindol.com/2012/05/09/tips-n-tricks-ssms-easy-way-to-learn-keyboard-shortcuts-by-enabling-screentips/

Active node name where SQL is running

Wed, 09 May 2012 00:00:00 GMT

With help of below query we can quickly find the node on which sql is currently running. This is useful in clustered environments. Select serverproperty('ComputerNamePhysicalNetBIOS')

Clear Report Builder Cache

Tue, 08 May 2012 00:00:00 GMT

Many times this question comes to me in past and today I got a call from my friend working in previous organization for same thing. Same question is asked in MSDN forum also. So I decided to document this.

We can clear the recent items from the cache by Report Builder Options-> Clear All recent Items

But this will not remove the saved credentials for a particular report server or report server getting connected by default. This gets cached at below location for RB 3.0.

%userProfile%\AppData\Local\Microsoft\Report Builder\10.50.0.0

So, to remove cached data, we need to delete the file(s) from this folder. Please note that doing so, all the recent history and the custom settings (Grouping Pane, property Pane etc.) will be lost.

SQL Server - Which is faster? Union or Union All?

Tue, 08 May 2012 00:00:00 GMT

Answer : Union All is Faster then Union. UNION -> you get DISTINCT records from both the tables. UNION ALL-> Get all records from both the tables with duplicates. If you check execution plan, UNION has extra "sort" operator, which makes it costlier compared to "UNION ALL". However, it's necessary sometimes to use "UNION", if the data sources has duplicate records and we wants to avoid duplicates.

Getting Started with ASP.NET MVC - Part 4: Introduction to Different view engines Razor and ASP.NET

Tue, 08 May 2012 00:00:00 GMT

In previous chapter, we have learned about controller, views, action results etc. In this chapter we will learn about the different type of view engines. **What is view engine?** As we have learned in previous chapters, in ASP.NET MVC, view is used to render response given by the server. Once all server side processing is completed, it will render the view in terms of HTML to browser. View engine is responsible for creating HTML from the view. A view is mostly some kind of mix-up of HTML and programming language. View engine interprets the programming language part and render the response as HTML. **Different type of view engines:** There are two type of view engines available by default with ASP.NET MVC3. 1. ASP.NET Basic View Engine 2. Razor View Engine that comes with ASP.NET MVC3 only. We can select this view engine at the time of creation of ASP.NET MVC project. Let’s take a example where we are going to compare both view engines. So let’s create a simple ASP.NET MVC3 application. To create a new application goto **File->New project** in Visual Studio. It will open up new project dialog box like following: ![New Project][1] Once you click **OK**, it will ask for application type where you have option to choose your view engine like following: ![Type of Applications][2] As you can see in above screenshot, you can see that there are options to choose view engine. Visual studio 2010 supports by default two view engine ASPX (ASP.NET default view engine) and another one is Razor view engine. I have chosen aspx, which is basic view engine for asp.net mvc application. Therefore, once you complete your creation application, it will create asp.net mvc application with aspx view engine. ASPX view engine comes <%:%> syntax. Therefore, if you see index view it will have following syntax. <%@ Page Language="C#" MasterPageFile="~/Views/Shared/Site.Master" Inherits="System.Web.Mvc.ViewPage" %> Home Page

<%: ViewBag.Message %>

To learn more about ASP.NET MVC visit http://asp.net/mvc.

The same view with razor view engine will look like following: @{ ViewBag.Title = "Home Page"; }

@ViewBag.Message

To learn more about ASP.NET MVC visit http://asp.net/mvc.

You can see that only difference between two is the syntax. The aspx view engine supports <%: %> syntax while razor view engine supports @ syntax. Another difference is the extension of views. ASPX view engines will support .aspx extension while Razor view engine will support .cshtml extension. You can see that in the following screenshots. Following is an aspx view engine screenshot: ![ASPX View Engine][3] While below is screenshot for razor syntax: ![Razor View Engine][4] Now let us take simple for-loop example for both to see what are the differences in syntax. Below is syntax for aspx view engine. <% foreach(var item in Model) { %> <%: item.Name %> <% } %> Following is the syntax for razor view engine: @foreach(var item in Model) { @item.Name } Therefore, the only difference is syntax. Razor syntax is clean syntax while aspx syntax is little bit messy as compared to the razor syntax. However, rendering time will be less for aspx syntax and while it will be higher for razor syntax. So based on type of application, you need to choose the view engine. If your application performance is critical and you want to save each and every second of it, then you should go for aspx view engine but If performance is not your criteria and you want a clear and much simpler syntax then you should go for razor syntax. **Third party view engines:** Until now, we have seen in built view engines with ASP.NET MVC but there are some third party view engines are also available which we can use in asp.net MVC. Followings are some of them: 1. **Spark View Engine:** You can fine more information about spark engine from following site. [http://sparkviewengine.com/][5] Once again, here syntax of view engine will be different then above two view engines. It will use ${} syntax to render HTML. Simple example of that is following. beta is my favorite. ${name} is okay too I suppose. You can find more information about Spark view engine syntax from following link where it contains documentation. [http://sparkviewengine.com/documentation/syntax][6] 2. **NHAML:** This view engine pronounced as enamel is pure .NET implementation of Ruby rails popular HAML view engine. You can find lots of information for this view engine from the following link where you will get all the information how to configure that etc. [http://code.google.com/p/nhaml/][7] As we know, here also syntax will be slightly different then above view engines. It will use # and % to render HTML. Following is an example of that: #collection .item .description What a cool item! You can find more information about syntax from following link. [http://code.google.com/p/nhaml/wiki/NHamlLanguageReference][8] 3. **Nvelocity:** Nvelocity is .NET port of the popular Java project velocity. You can find more information about this view engine from the following site. [http://nvelocity.sourceforge.net/][9] There are many more engines available where every view engine is having their own pros and cons. You need select view engine as per your requirement. There is a very good comparison of all the view engines available for asp.net mvc at the following link. You will find lots of view engine information from there. [http://stackoverflow.com/questions/1451319/asp-net-mvc-view-engine-comparison][10] That is it. Hope you like it. In next chapter, we will learn Razor syntax in detail. [1]: http://beyondrelational.com/images/images.ashx?id=2107ede136ed445f81f59cfd99f9d227&w=0&h=0 [2]: http://beyondrelational.com/images/images.ashx?id=e7532366625b44ca9b6340a89c31444e&w=0&h=0 [3]: http://beyondrelational.com/images/images.ashx?id=4829e103ab8046aba9f06a81beb6d496&w=0&h=0 [4]: http://beyondrelational.com/images/images.ashx?id=2d66e2681f2f46618e5a4157636f03d4&w=0&h=0 [5]: http://sparkviewengine.com/ [6]: http://sparkviewengine.com/documentation/syntax [7]: http://code.google.com/p/nhaml/ [8]: http://code.google.com/p/nhaml/wiki/NHamlLanguageReference [9]: http://nvelocity.sourceforge.net/ [10]: http://stackoverflow.com/questions/1451319/asp-net-mvc-view-engine-comparison

What is Cloud Computing?

Mon, 07 May 2012 03:41:00 GMT

You might be hearing people talking much about cloud computing these days . You might be wondering what is Cloud
& Cloud Computing. In this blog post I just wanted to give overview of Cloud & Cloud Computing and continue to write series of blog posts related to Cloud Space.

In technical terms, A Cloud is a infrastructure providing network, compute, and storage capabilities. This is Managed infrastructure.

Cloud computing can be categorized as

1. SAAS Software As A Service

This is used for running applications on the public cloud. Cloud applications accessed to the users via internet.

For example : SalesForce.com , Google (Gmail, Google Site, Google Docs). Microsoft (Dynamics CRM Online, Exchange Online, Office Web apps, Sharepoint online) ,IBM – Lotus Live , ORACLE (Fusion CRM)

2. PASS Platform As A Service

This is platform for Developers to host their application code

This is Virtually Infinite computing resurces where Load balancing is done automatically

ex:Force.com, Microsoft Azure, Google App Engine (GAE), Amazon Web Services (AWS), IBM Virtual Images. .

3. IASS Infrastructure As A Service

ex: Compute Cloud (Amazon EC2), Rack space, Right Scale, VM Ware, IBM BlueHouse, Hexa Grid.

Benefits

Faster Deployment since no local installation is required , Simple clean fast and cheap
Usage based Pricing – Pay for what you use( pay per user per month)
Less Financial risk : With lower up-front cost and try before you buy options
Reduced need for on-premises resources : IT Staff and servers can be reduced
Easier upgrades – with no on-premised software to update

Disadvantages

Trusting provider for availability and data security . critical stuff need to be trusted
can raise legal / regulatory concerns with storing data outside customer premises
Can limit customization : If customers share a multi –tenet application
Can be harder to integrate with on-premise applications
Can have lower performance than on-premises applications

Public Cloud

In case of the Public cloud applications, storage, and all resources are made available to the public by a service provider. These services are free or offered on a pay-per-use model. In this case we have less manageability

Private Cloud

Private cloud is cloud infrastructure that is operated for a single organization, hosted internally or externally

Community Cloud

This will share infrastructure between several organizations from a specific community . This community may have common concerns like security, compliance etc. It can be managed internally or by a third-party . It can be

hosted internally or externally.

Hybrid Cloud

This is composition of two or more clouds public or private that can take advantage of the public or private or community clouds

#0158-SQL Server-Returning result sets from triggers

Mon, 07 May 2012 00:00:00 GMT

This week, I am taking a brief break from my series on deprecated features of SQL Server 2012 to share with you two experiences I had recently. A developer had suspected that one of the triggers in the database had an issue. To debug this, the developer placed a simple SELECT statement within the trigger.

Below is an example reproducing the scenario for demonstration purposes.

--Use tempdb for demonstration purposes
USE tempdb
GO

--Create a demo table
CREATE TABLE dbo.TriggerResultSets (IdCol INT IDENTITY(1,1),
                                    IdName VARCHAR(50)
                                   )
GO

--Create a trigger that returns a result set
CREATE TRIGGER dbo.trig_TriggerResultSets
ON dbo.TriggerResultSets
FOR INSERT
AS
BEGIN
    /*Some business logic here*/
    SELECT INSERTED.IdCol, INSERTED.IdName
    FROM [INSERTED]
    ORDER BY INSERTED.IdCol
END
GO

Upon inserting some data into our demo table, TriggerResultSets, the developer could see the data that was inserted.

--Insert some test data
INSERT INTO dbo.TriggerResultSets (IdName)
VALUES ('Microsoft'),
       ('SQLServer')
GO

The Problem

After resolving the issue that the developer had encountered, the team proceeded to unit test the application, which failed. This was because the SELECT statement was left behind, and the application was not written to handle result sets from triggers.

While the ultimate solution to prevent such a situation in the future is to have proper code review checklists, techniques and methods that would help ensure such code never gets “deployed”, there are workarounds for which developers can be educated for, and a check that all database administrators can use to trap any fall-through occurrences.

The Workaround

The workaround is actually quite simple. Developers can be educated to use PRINT statements instead of the SELECT statements. Because PRINT does not return result sets, the application will remain “happy” and at work as expected. Although, please note that I am not very much in favour of this because data is being exposed here unnecessarily.

--CREATE TABLE statement removed for sake of brevity.

--Create a trigger that returns a result set
CREATE TRIGGER dbo.trig_TriggerResultSets
ON dbo.TriggerResultSets
FOR INSERT
AS
BEGIN
    /*Some business logic here*/
    DECLARE @InsertedId INT
    DECLARE @InsertedName VARCHAR(50)

    SELECT @InsertedId = inserted.IdCol, @InsertedName = inserted.IdName
    FROM [INSERTED]
    ORDER BY INSERTED.IdCol
     
    PRINT 'Id = ' + CAST(@InsertedId AS VARCHAR(10)) + ', Name = ' + ISNULL(@InsertedName,'')
END
GO

INSERT INTO dbo.TriggerResultSets (IdName)
VALUES ('SQLServer')
GO

/*
Result Set:
Id = 1, Name = SQLServer
*/

The Check

Despite the proper developer education, multiple checklists and code reviews, it is quite possible that sometimes things may slip through. In such cases (or until the developer education does not complete), the DBA may need to take extreme steps such as completely disabling functionalities that may cause problems. In the case I am referring to, I turned ON a configuration option - disallow results from triggers. Please note that this is an advanced option.

sp_configure 'show advanced options',1
RECONFIGURE
GO

sp_configure 'disallow results from triggers',1
RECONFIGURE
GO

Once turned ON, the original code with the SELECT statement would fail.

INSERT INTO dbo.TriggerResultSets (IdName)
VALUES ('Microsoft'),
       ('SQLServer')
GO

Msg 524, Level 16, State 1, Procedure trig_TriggerResultSets, Line 9
A trigger returned a resultset and the server option 'disallow results from triggers' is true.

PLEASE NOTE

Please note that the “disallow results from triggers” option has been marked as deprecated from SQL Server 2008 onwards and now that SQL Server 2012 is out, it will be removed from the next version of SQL Server. Future versions of SQL Server will not support returning result sets from triggers and hence the switch is no longer required.

Per Microsoft’s recommendation, I have turned the switch to 1 in my environments.

Read more about this announcement here: http://msdn.microsoft.com/en-us/library/ms186337(v=sql.110).aspx

Until we meet next time,

Be courteous. Drive responsibly.

Sum up digits of a number

Fri, 04 May 2012 00:00:00 GMT

My friend told me that in an interview he was asked to write a code that sums the digits of the number. He asked me if there is a way of doing it without using a WHILE loop. Here are two methods

Method 1 : Dynamic SQL

declare @i int, @sql varchar(8000)
set @i=798273
set @sql=''
select 
	@sql=@sql+substring(ltrim(@i),number,1)+'+' 
from 
	master..spt_values 
where 
	type='p' and number between 1 and len(@i)
select @sql='select '+left(@sql,len(@sql)-1)

exec(@sql+' as sum_of_digits')

Method 2 : Static SQL

declare @i int, @n int
set @i=798273
set @n=0
select 
	@n=@n+substring(ltrim(@i),number,1) 
from 
	master..spt_values 
where 
	type='p' and number between 1 and len(@i)

select @n as sum_of_digits

Method 3 : Without using any variables

declare @i int
set @i=798273
select 
	sum(substring(ltrim(@i),number,1)*1)
from 
	master..spt_values 
where 
	type='p' and number between 1 and len(@i)

All the methods would return the following result

sum_of_digits
-------------
36

Here master..spt_values is a system table which is used as a tally number table. You can use your own table too.

SQL Server Logical Reads - What's it?

Thu, 03 May 2012 00:00:00 GMT

Recently we i was working on tuning of stored procedures and i experienced with one performance issue, that was high Logical Reads.

Logical Reads?

"Number of pages read from the data cache" - It occurs every time when the database engine request a page from buffer cache, otherwise physical reads occurs if currently page is not available in buffer cache.

Let us go through the sample demo and get experience for the logical reads. First we need require objects, so we are creating database and tables inside it.

USE DEMO
GO

-- Creating a table
IF (OBJECT_ID('TblLogicalReads','U') > 0)
  DROP TABLE TblLogicalReads
GO

CREATE TABLE TblLogicalReads
(
 TranId INT,
 TrnData VARCHAR(100),
 TrnDate DATETIME
)

GO

After creating objects, 49999 records will be inserted by following scripts,

DECLARE @cnt BIGINT

SET @cnt  = 1

WHILE (@cnt < 50000)
BEGIN

INSERT INTO TblLogicalReads (TranId,TrnData,TrnDate)
VALUES (@cnt, 'Demo Records ' + CONVERT(VARCHAR(100),@cnt ), GETDATE() - @cnt)

SET @cnt = @cnt +1

END

GO

Now we are checking logical reads for the script which are going to be run.

SET STATISTICS IO ON
SET STATISTICS TIME ON

SELECT 
  TranId,
  TrnData,
  TrnDate
FROM TblLogicalReads 
 WHERE TranId = 5

SELECT 
  TranId,
  TrnData,
  TrnDate
FROM TblLogicalReads 
 WHERE TrnDate = '2009-12-26 18:10:47.653'

SET STATISTICS TIME OFF
SET STATISTICS IO OFF
GO

(Click on image to enlarge)

You can see here , logical reads are high.

How can we reduce it?

There are many factor to reduce it as it depends on But here for example we need to create some required indexes on columns which are used in the queries.

-- Creating indexes on tables 
CREATE CLUSTERED INDEX IX_TranId ON TblLogicalReads(TranId)
GO

CREATE NONCLUSTERED INDEX IX_TrnDate ON TblLogicalReads(TrnDate)
GO

Finally we are on the stage where we need to review logical reads after creating indexes on tables

SET STATISTICS IO ON
SET STATISTICS TIME ON

SELECT 
  TranId,
  TrnData,
  TrnDate
FROM TblLogicalReads 
 WHERE TranId = 5

SELECT 
  TranId,
  TrnData,
  TrnDate
FROM TblLogicalReads 
 WHERE TrnDate = '2009-12-26 18:10:47.653'

SET STATISTICS TIME OFF
SET STATISTICS IO OFF

(Click on image to enlarge)

I hope you understood the logical reads from the example give above. You can share you knowledge for the