Title

Relog command

Fri, 25 May 2012 00:00:00 GMT

RELOG command can be used to convert the performance counters collected in binary format to CSV format. There are many more options available. relog C:\PerfLogs\Baseline.blg -o C:\PerfLogs\Baseline.csv -f csv

#0163–SQL Server 2012–Deprecated Features–Table Hint: FASTFIRSTROW-Msg: 321

Thu, 24 May 2012 00:00:00 GMT

Every version of SQL Server comes with a lot of optimizations to the database engine, and these changes require changes to the T-SQL syntax. New statements are introduced or modified and existing ones are dropped. Often table and query hints are optimized and modified in a similar fashion. These table and query hints are often used (in many cases, indiscriminately) to “improve the performance” of Microsoft SQL Server.

Let me start outright by saying that till date, I have never encountered the need to use the hint that we are about to discuss today.

Table v/s Query hints

Before going any further, I thought it best to revisit the simple method of identifying which is a table hint and which one is a query hint. For example, take the query below:

--Query for demonstration purposes only!
USE AdventureWorks2012
GO
SELECT *
FROM HumanResources.Employee WITH (NOLOCK) -- WITH (NOLOCK) is the table hint
WHERE OrganizationLevel = 3
OPTION (MAXDOP 1)                          -- OPTION (MAXDOP 1) is the query hint

The WITH (NOLOCK) hint is applied to the table/CTE/sub-query, i.e. a data source. Hence the name – table hint.

The OPTION (MAXDOP 1) is applied to the entire query, and hence the name – query hint.

[Edit]: You may also find the following post from Pinal Dave, a.k.a. “SQLAuthority” (B|T), useful in understanding Query Hints: http://blog.sqlauthority.com/2012/05/09/sql-server-quiz-and-video-introduction-to-basics-of-a-query-hint/

FASTFIRSTROW

According to SQL Server Books On Line, the FASTFIRSTROW is a table hint that helps the developer control the behaviour of the optimizer such that the optimizer,

Optimizes for overall minimum cost (minimum processing time/resources, etc), OR
Optimizes such that the time taken for the first row to be returned is minimized

The FASTFIRSTROW tells the optimizer that it is important to have the first row returned as fast as possible, allowing the application to return results to the users faster. You can see the difference by comparing the actual execution plans produced by the queries below:

--Query for demonstration purposes only!
USE AdventureWorks2008
GO
--Without the table hint
SELECT *
FROM HumanResources.Employee 
GO
--With the table hint
SELECT *
FROM HumanResources.Employee WITH (FASTFIRSTROW)
GO

OPTION (FAST n)

Running the above query in SQL Server 2012 results in the following error:

Msg 321, Level 15, State 1, Line 3
"FASTFIRSTROW" is not a recognized table hints option. If it is intended as a parameter to a table-valued function or to the CHANGETABLE function, ensure that your database compatibility mode is set to 90.

The message says it all – FASTFIRSTROW is no longer available, and if you would like to use it, the database compatibility level needs to be set to 90 (i.e. SQL Server 2005).

The replacement available is not a table hint, but a new query hint – FAST number_rows. This query hint specifies that the query is optimized for fast retrieval of the first number_rows. This is a nonnegative integer. After the first number_rows are returned, the query continues execution and produces its full result set. SQL Server 2012 has given more flexibility to the developer/administrator because the table hint only optimized for the first row, whereas the query hint allows you to specify a custom number of first rows to optimize for.

As a replacement of FASTFIRSTROW, you can use OPTION (FAST 1).

--Query for demonstration purposes only!
USE AdventureWorks2012
GO
--Without the table hint
SELECT *
FROM HumanResources.Employee 
GO
--With the table hint
SELECT *
FROM HumanResources.Employee
OPTION (FAST 1);
GO

References:

Table hints supported by SQL Server 2008 R2: http://msdn.microsoft.com/en-us/library/ms187373(v=sql.105).aspx
Query Hints: http://msdn.microsoft.com/en-us/library/ms181714.aspx
As I was learning about this table hint, I came across the following post, which I thought might make interesting reading: http://sqlblog.com/blogs/kalen_delaney/archive/2008/12/02/using-a-nonclustered-index-to-avoid-a-sort.aspx
http://blog.sqlauthority.com/2012/05/09/sql-server-quiz-and-video-introduction-to-basics-of-a-query-hint/

Until we meet next time,

Be courteous. Drive responsibly.

SQL Server - How do you find un-used stored procedures and un-used views?

Thu, 24 May 2012 00:00:00 GMT

There is no in-built mechanism to identify the un-used stored procedures. When an stored procedure is called, an entry will be created in "sys.dm_exec_query_stats". Based on this information, we can find un-used stored procedures by using below query. However, this information will be deleted once the server re-starts. SELECT * from sys.procedures P LEFT OUTER JOIN ( SELECT x.objectid FROM sys.dm_exec_query_stats AS s1 CROSS APPLY sys.dm_exec_sql_text(sql_handle) AS x WHERE OBJECTPROPERTYEX(x.objectid,'IsProcedure') = 1 )T on P.object_id = T.objectid WHERE T.objectid is null For views, it will store the execution plan in DMVs. Below query can be used to get un-used views. When the server is re-started, this information will be cleared. Also, when the server has more run out of procedure cache, it will dump the old execution plans. so, this query result might not be approximate. SELECT v.name [ViewName] FROM sys.views v WHERE v.is_ms_shipped = 0 EXCEPT SELECT o.Name [ViewName] FROM master.sys.dm_exec_cached_plans p CROSS APPLY sys.dm_exec_sql_text(p.plan_handle) t INNER JOIN sys.objects o ON t.objectid = o.object_id [here][1] (Above script taken from Michael K. Campbell article [here][1]) [1]: http://www.sqlmag.com/blog/practical-sql-server-45/sql-server-2008-r2/finding-potentially-unused-views-databases-140294[here][1]93

BIDS No More Exist in SQL Server 2012.

Wed, 23 May 2012 00:00:00 GMT

Once you will installed SQL Server 2012 you could not find the BIDS to develop SSAS/SSIS/SSRS application. BIDS will replace with SSDT i.e. SQL Server Data Tools.

Comparison of SQL Server 2012 Vs SQL Server 2008 R2. You can get more idea on below Screen shot how SQL Server 2012 looks compare with SQL Server 2008 R2 components.

SQL Server 2012 Components

SQL Server Data Tools
SQL Server Management Studio
Analysis SErvices
Configuration Tools
Data Quality Services
Documentation & Community
Integration Services
Master Data Services
Performance Tools

So in SQL Server 2012 we have below four new components available.

SQL Server Data Tools
Data Quality Services
Documentation & Community
Master Data Services

Today will discuss more on SSDT i.e. SQL Server Data Tools.

SSDT (SQL Server Data Tools)

1- Developing SSAS/SSIS/SSRS Projects.
2- Developing Databases in Visual Studio like working in SQL Server Management studio.

Will discuss the how we can use SSDT one by one.

1-Developing SSAS/SSIS/SSRS Projects.

·         If you are looking for BIDS (Business Intelligence Development Studio) in SQL Server 2012, then you will not find it. It is replaced with SQL Server Data Tools (SSDT) in SQL Server 2012.
·         You can start the SSRS/SSAS/SSIS Projects as we created in BIDS in SQL Server 2008/R2.
·         SQL Server 2012 installs "SQL Server Data Tools" under the "SQL Server 2012" Start Menu option - this includes BIDS but not Database Projects.
·         In this use can create the SSAS Tabular Projects called as BISM.

2-Developing Databases in Visual Studio like working in SQL Server Management studio.

·         In this you can create new Database Design features with schema management etc in Visual Studio more like working in SQL Server Management studio.
·         The idea is that developers want to develop databases using similar methods to working with regular (i.e. .NET code) object within VS. To that end, SSDT includes offline projects, object creation interfaces (i.e. for tables, views, etc…) schema snapshot and compare, database version targeting (for SQL Server 2005, 2008, 2012 and for SQL Azure) and refactoring with preview.
·         Database Projects installs "MICROSOFT SQL Server Data Tools" under the root of the Start Menu - this doesn't have BIDS functionality included if installed separately.

Features

Responsive SSDT'S GUI as compared to SQL Server Management Studio.
Fast and reliable build of deployment scripts.
Detection of isolated changes in the target database.
Resolve references to other production and system databases like msdb & databases connected by a linked server.
Support of migration scripts to enable data preservation and to add static data.
Possibility of executing and deploying a single file.
Comfortable T-SQL writing.

Suhas R. Kudekar

Retrieve the queries that are Most expensive / use the Most IO

Tue, 22 May 2012 00:00:00 GMT

The core functionality of database systems is to retrieve data, and this is reflected in the amount of I/O involved, total CPU time, logical reads/writes etc. We can look at the queries that are most expensive (taking more time to execute), use the most I/O to determine whether they can be changed to retrieve data more efficiently.

The script below helps to identify the top 10 queries that use the most I/O as ordered by Total IO, taking more time to execute (means most expensive) across all databases on the server.

Query:

The query calculates the Total IO by summing the columns total_logical_reads and total_logical_writes.
The Average IO is calculated by dividing the sum of the total_logical_reads and total_logical_writes by the number of times the query has executed (execution_count).
The other output column are:
- Database Name
- Individual Query
- Parent Query (from where the query is executing)
- Total elapsed time in seconds
- Query’s cached plan (query_plan)
- The output is sorted by Total IO in descending order as of now.

You can modify the query as per your requirement like filter on database name only, set required Order by.

SELECT TOP 10
        [Individual Query] = SUBSTRING(qt.TEXT,
                                       ( qs.statement_start_offset / 2 ) + 1,
                                       ( ( CASE qs.statement_end_offset
                                             WHEN -1 THEN DATALENGTH(qt.TEXT)
                                             ELSE qs.statement_end_offset
                                           END - qs.statement_start_offset )
                                         / 2 ) + 1),
        [Total IO] = ( qs.total_logical_reads + qs.total_logical_writes ),
        [Average IO] = ( qs.total_logical_reads + qs.total_logical_writes ) / qs.execution_count,
        [Execution Count] = qs.execution_count,
        [Total Logical Reads] = qs.total_logical_reads,
        [Total Logical Writes] = qs.total_logical_writes,
        [Total Worker Time/CPU time] = qs.total_worker_time,
        [Total Elapsed Time In Seconds] = qs.total_elapsed_time / 1000000,
        [Parent Query] = qt.text,
        [DatabaseName] = DB_NAME(qt.dbid),
        [Query Plan] = qp.query_plan
FROM    sys.dm_exec_query_stats qs
        CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) qt
        CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) qp
ORDER BY [Total IO] DESC --IO
-- ORDER BY [Total Elapsed Time In Seconds] DESC  --elapsed time
-- ORDER BY qs.total_logical_reads DESC -- logical reads
-- ORDER BY qs.total_logical_writes DESC -- logical writes
-- ORDER BY qs.total_worker_time DESC -- CPU time

To reduce reads you need to look at a couple of things, first being query design, secondly being indexing. If your query is pulling a large number of records that could be filtered by getting a smaller set prior to pulling that data then you can always cut down on reads that way. With improved indexing, specifically with covering indexes, you can reduce the number of pages that are being read as well.

I hope this will be helpful somewhere sometime. J

Appendix

Logical reads - number of pages read from the data cache
- Logical read indicates total number of data pages needed to be accessed from data cache to process query. It is very possible that logical read will access same data pages many times, so count of logical read value may be higher than actual number of pages in a table. Usually the best way to reduce logical read is to apply correct index or to rewrite the query.
Physical reads - number of pages read from disk
- Physical read indicates total number of data pages that are read from disk. In case no data in data cache, the physical read will be equal to number of logical read in most of the cases. And usually it happens for first query request. And for subsequent same query request the number will be substantially decreased because the data pages have been in data cache.

NULL, NULL, NULL and nothing but NULL

Mon, 21 May 2012 00:00:00 GMT

NULL. This is a magical word in Database programming. Here are some interesting facts about NULL in SQL Server.

1 NULL can be defined as absense of value, undefined, or the value which is unknown at this point of time.
2 All datatypes can be defined with NULL constraint
3 Direct usage of artimetic or logical operations on NULL will not work as expected
4 The system functions ISNULL, COALESE and NULLIF are used only to deal with NULL
5 NOT NULL constraint cannot be defined for a computed column until it is also PERSISTED
6 The only datatypes that will interpret NULL differently are rowversion and timestamp

Run this code and see the output

declare @rv rowversion , @ts timestamp 
select @rv=null, @ts=null
select @rv as rowversion,@ts as timestamp

Output is

rowversion         timestamp
------------------ ------------------
0x                 0x

7 NULL=NULL will never be true unless SET ANSI_NULLS is OFF

While the first query returns nothing, the second will return 6

--Query 1
set ansi_nulls on
select 6
where null=null

--Query 2
set ansi_nulls off
select 6
where null=null

The condition WHERE col IS NULL will not be affected by the above setting

8 The default datatype of NULL is INT. Refer Default datatype of NULL for more information
9 Column with UNIQUE constraint will allow only one NULL value in SQL Server (But not true in other RDBMSs)
10 NULL will make SQL Server to use short circuit logic in some cases

Consider this example

select 6/0/null

select null/6/0

While the first query throws an error the second query returns NULL

11 The value NULL is not equal to string value 'NULL'
12 By default NULL values come first when a column is ordered in ascending order and come last when ordered in descending order
13 If you dont use GROUP BY clause, the aggregate functions will always return single value (NULL) when the condition is false

select sum(col) as col from
(
	select 45 as col
) as t
where 1=0

The above returns NULL

14 NULL values are by default omitted in all aggregate functions

Consider the following example

select sum(col) as col_cum,count(col) as col_count,avg(col*1.0) as col_avg from
(
	select 1 as col union all
	select null as col union all
	select 2 as col union all
	select 3 as col 
) as t

The output is

col_cum     col_count   col_avg
----------- ----------- -----------
6           3           2.000000

15 Agrregate functions cannot be directly applied over NULL value

This code

select sum(null) as null_sum

returns the following error

Msg 8117, Level 16, State 1, Line 1
Operand data type NULL is invalid for sum operator.

You can wonder whether NULL is a datatype

#0162–SQL Server 2012–Deprecated Features–Modify database options with sp_dboption–Msg 2812

Mon, 21 May 2012 00:00:00 GMT

If you have been into database administration & development for a while now, what is the first thing that comes to your mind when someone asks to modify database options like setting the database read-only, or taking it offline? Answer: The sp_dboption system stored procedure.

The typical way to modify these database options would be:

--"Before" state
SELECT 'Before',is_read_only,state,state_desc,* FROM sys.databases db WHERE db.name = 'TestDB'
GO
--Set the database to READ-ONLY mode
sp_dboption 'TestDB','read only','TRUE'
GO
--"After" state
SELECT 'Post Read Only',is_read_only,state,state_desc,* FROM sys.databases db WHERE db.name = 'TestDB'
GO
--Set the database OFFLINE
sp_dboption 'TestDB','offline','TRUE'
GO
--"After" state
SELECT 'All done',is_read_only,state,state_desc,* FROM sys.databases db WHERE db.name = 'TestDB'
GO

SQL Server 2012 – Replacement – ALTER DATABASE…SET

However, starting SQL Server 2012, attempting to change the database options this way would produce the following results.

Msg 2812, Level 16, State 62, Line 1
Could not find stored procedure 'sp_dboption'.

The system could not find the system stored procedure! That’s simply because it has been deprecated! The replacement is the ALTER DATABASE statement, and the existing sp_dboption calls need to be modified as demonstrated in the script below:

--"Before" state
SELECT 'Before',is_read_only,state,state_desc,* FROM sys.databases db WHERE db.name = 'TestDB'
GO
--Set the database to READ-ONLY mode
ALTER DATABASE TestDB SET READ_ONLY
GO
--"After" state
SELECT 'Post Read Only',is_read_only,state,state_desc,* FROM sys.databases db WHERE db.name = 'TestDB'
GO
--Set the database OFFLINE
ALTER DATABASE TestDB SET OFFLINE
GO
--"After" state
SELECT 'All done',is_read_only,state,state_desc,* FROM sys.databases db WHERE db.name = 'TestDB'
GO

The sp_dboption system stored procedure was marked as “discontinued” ever since SQL Server 2005. Yet, I have seen a lot of production code this year itself that uses the sp_dboption statement. All of this code will break unless it is replaced with ALTER DATABASE…SET statement.

References:

sp_dboption - http://msdn.microsoft.com/en-us/library/ms187310(v=sql.90).aspx
ALTER DATABASE statement - http://msdn.microsoft.com/en-us/library/bb522682.aspx

Until we meet next time,

Be courteous. Drive responsibly.

The sp_dboption system stored procedure was marked as “discontinued” SQl Server 2012

Mon, 21 May 2012 00:00:00 GMT

I just learn from [Nakul''s Blog][1] That sp_dboption store procedure is “discontinued” SQl Server 2012 Replacement is the ALTER DATABASE statement, for existing sp_dboption ALTER DATABASE TestDB SET READ_ONLY Thank You [1]: http://beyondrelational.com/modules/2/blogs/77/nakuls-blog.aspx?tab=posts&bs=43

Displaying your photo when google shows your beyondrelational.com posts in search results

Mon, 21 May 2012 00:00:00 GMT

You may have noticed that google shows a picture of the author when displaying certain posts in the search results. The steps given below will help you to configure your beyondrelational.com profile so that when google search results displays your posts it may also show your photograph. Here is an example of how it may look like when google displays one of your posts created at beyondrelational.com, in search results. ![Paresh prajapati search result][1] **Step 1 - Create a Google Plus profile (if you do not have one already)** The photograph comes from google plus profile. So the first step is to create a google plus profile if you do not have one already. If you already have one, then make sure that you have uploaded a profile picture that you would like to see in the search results. **Step 2 - Add your beyondrelational.com profile URL to Google Plus profile** The next step is to connect your Google Plus Profile with beyondrelational.com profile. To do that, click on the **profile** button on your google plus profile. ![Google plus profile][2] Then click on **edit profile** button. ![edit profile button][3] Next, locate the section **other profiles** or **Contributor to** towards the bottom of the page. Click on the link and select **Add a custom link**. ![add custom link to beyondrelational.com profile][4] Give a proper label such as "My beyondrelational.com profile" and add the URL of your beyondrelational.com profile in the second text box. You can open your beyondrelational.com profile by clicking on your name on the top-right corner of the web page if you are logged in to beyondrelational.com ![locate your profile page at beyondrelational.com][5] Save the information. **Step 3 - Add your Google Plus profile URL to your beyondrelational.com profile** Next, go to your profile at beyondrelational.com. Click on the **edit** link shows on the center-top of the profile section. ![beyondrelational.com profile snippet][6] Copy and paste your Google Plus profile URL into the last text box and save the information. ![enter google plus profile URL into beyondrelational.com profile][7] **What Next?** If you have completed all the steps given above, your profile should be configured correctly by now. The next step is to wait for a few **weeks** before you start seeing your profile photo when google displays search results that includes your posts. **Notes** 1. Please note that this does not guarantee that the profile photo will be listed in google search results. 2. Steps listed above are just recommendations and Google may or may not show your profile photo when displaying search results 3. The steps listed above are identified by our own experiment [1]: http://media.beyondrelational.com/images.ashx?id=44c7dc79956946cfa3850a6cc30c8a7d&w=-1&h=-1 [2]: http://media.beyondrelational.com/images.ashx?id=769dc9fd39874b80b80770763564a5aa&w=-1&h=-1 [3]: http://media.beyondrelational.com/images.ashx?id=2fb77739a61844f1a5683f8a12241a1e&w=-1&h=-1 [4]: http://media.beyondrelational.com/images.ashx?id=ae3577695edb46b691a88fd968dd01f7&w=-1&h=-1 [5]: http://media.beyondrelational.com/images.ashx?id=28b029a595c84f7789cc8ccb9c8e6216&w=-1&h=-1 [6]: http://media.beyondrelational.com/images.ashx?id=ca4baa27a31448459fb43ca3cbfb5f06&w=-1&h=-1 [7]: http://media.beyondrelational.com/images.ashx?id=65906407e8274486b721368c0e39565b&w=-1&h=-1

What is wrong with IsNumeric()?

Sat, 19 May 2012 00:00:00 GMT

T-SQL's ISNUMERIC() function has a problem. It can falsely interpret non-numeric letters and symbols (such as D, E, and £), and even tabs (CHAR(9)) as numeric.

5 Ways to Make Ajax Calls with jQuery

Sat, 19 May 2012 00:00:00 GMT

There is five way make Ajax calls with Jquery load(): Load a piece of html into a container DOM. $.getJSON(): Load a JSON with GET method. $.getScript(): Load a JavaScript. $.get(): Use this if you want to make a GET call and play extensively with the response. $.post(): Use this if you want to make a POST call and don’t want to load the response to some container DOM. $.ajax(): Use this if you need to do something when XHR fails, or you need to specify ajax options (e.g. cache: true) on the fly.

What is wrong with IsNumeric()?

Sat, 19 May 2012 00:00:00 GMT

I face very strange thing with IsNumeric() function in sql

select IsNumeric('1234D234')
select IsNumeric('123A123')

Both query have alphanumeric value and both give different answer. very strange.

After a long time spent on google i get solution. We can say or cannot say that there are bug in IsNumeric() function.

IsNumeric() can falsely interpret non-numeric letters and symbols (such as D, E, and £, $), and even tabs (CHAR(9)) as numeric.

With this character, Why IsNumeric() can not give right answer? because D stand for Decimal. that value consider as a decimal value, E stand for exponential and $,£ is stand for currency symbol. so Isnumeric() fail to convert those values which has like D,E,$,£ characters.

so to get around this problem, we can create user define function which find the value is integer/numeric. as below.

CREATE FUNCTION dbo.CheckNumeric
(
@num VARCHAR(64)
)
RETURNS BIT
BEGIN
IF LEFT(@num, 1) = '-'
SET @num = SUBSTRING(@num, 2, LEN(@num))

DECLARE @pos TINYINT

SET @pos = 1 + LEN(@num) - CHARINDEX('.', REVERSE(@num))

    RETURN CASE
        WHEN PATINDEX('%[^0-9.-]%', @num) = 0
          AND @num NOT IN ('.', '-', '+', '^')
          AND LEN(@num)>0
          AND @num NOT LIKE '%-%'
         AND
            (
               ((@pos = LEN(@num)+1)
                     OR @pos = CHARINDEX('.', @num))
               )
         THEN   1
         ELSE
            0
      END
   END
GO

CREATE FUNCTION dbo.CheckInteger
(
@num VARCHAR(64)
)
RETURNS BIT
BEGIN
IF LEFT(@num, 1) = '-'
SET @num = SUBSTRING(@num, 2, LEN(@num))

   RETURN CASE
         WHEN PATINDEX('%[^0-9-]%', @num) = 0
            AND CHARINDEX('-', @num) <= 1
            AND @num NOT IN ('.', '-', '+', '^')
            AND LEN(@num)>0
            AND @num NOT LIKE '%-%'
         THEN
            1
         ELSE
            0
         END
     END
GO

http://codesimplified.com/2012/05/18/what-is-wrong-with-isnumeric/

Learn From: http://classicasp.aspfaq.com/general/what-is-wrong-with-isnumeric.html

Thanks.

SSIS – TransactionOption NotSupported

Fri, 18 May 2012 00:00:00 GMT

When I take interview for SSIS I tend to ask this question one way or other. Here goes the question: “What will happen if I have a sequence container with TransactionOption set to Required. This Sequence Container contains 3 Execute SQL Tasks. Out of the 3 Execute SQL Tasks 2 have TransactionOption set to Supported while the third is set to NotSupported. What will be the impact on the 1st 2 Execute SQL Task’s operation if the 3rd Execute SQL Task Fails?”

Let me show the scenario that is described above.

The details of the tasks setup is available in the table below:

TASK	Task/Container	TransactionOption	Query
Tran_Required	Sequence Container	Required	NA- Container
Tran_Supported	Execute SQL Task	Supported	Select 1
Tran_Supported1	Execute SQL Task	Supported	Truncate Table Tbl GO INSERT INTO [Tbl] ([id]) VALUES (1)
Tran_NOT_Supproted	Execute SQL Task	NOT Supproted	Select 1/0

So coming back to the interview, 99% of cases I get the answer that there will be no impact of the 3rd Execute SQL Task (Tran_NOT_Supported) failing. They say that the record inserted by the task Tran_Supported1 will not be rolled back and the table will have one more entry created. Let us see what really happens.

The package executes as shown below.

In the data base too there are no records inserted.

SELECT COUNT(1) FROM Tbl

Outcome:

Count
0

So now we see that NotSupported is not working the way we expect it to. Let us see how can we overcome this. There are multiple ways to achieve this I will show 2 methods to do this.

First approach is go to the EventHandler of the Tran_NOT_Suppoerted. On the Event OnError enable it. Select the system variable Propogate and set its value to False. That’s all you need to do and it will work the way we expect it to work.

When we execute the package now we get the below behaviour:

In the database we get the below result

SELECT COUNT(1) FROM Tbl

Outcome:

Count
1

Second approach is go to the properties of the Sequence Container Tran_REQUIRED. Change the value of the property MaxErrorCount to ‘2’ from default value ‘1’. That’s even simpler compared to the first approach(esp. for lazy people like me).

If you execute the package now you will get the expected behaviour as in the previous case.

By these 2 examples you should I guessed the reason why NotSupproted does not work the way it should or we feel that way.

What is happening is that the NotSupproted is working as it is, but as a child fails the error propagates to the parent container and the Parent fails due to which the Transaction is rolled back.

The transaction is not getting rolled back due the failure of the not Supported Tasks but the failure of the Sequence container.

Use system functions cleverly

Thu, 17 May 2012 00:00:00 GMT

My Co-worker complained me that AVG function is not properly working in SQL Server whereas it works correctly in mysql. I immediately told him that AVG does the implicit convertion by default and the result may be wrong (based on datatype).

Consider the following set of data

select avg(col) from
(
	select 1 as col union all
	select 2
) as t

The result is

average
-----------
1

As you see, the result is not 1.5 but it is 1 becuase the return type of AVG function is same as that of column it is applied for. The datatype of col is INT and AVG function CASTs 1.5 into 1. To avoid this you need to CAST the datatype into DECIMAL (or multiply column by 1.0)

select avg(col*1.0) as average,avg(cast(col as decimal(12,2))) as average from
(
	select 1 as col union all
	select 2
) as t

The result is

average                                 average
--------------------------------------- ---------------------------------------
1.500000                                1.500000

So you need to aware of this when using system functions whose datatype depends on the datatype of the column

#0161 - SQL Server 2012–Deprecated Features-CREATE TRIGGER-WITH APPEND option

Thu, 17 May 2012 00:00:00 GMT

There would hardly be a database developer who hasn’t written triggers in their career. It is fairly common knowledge that you can have more than one trigger of the same type, i.e. INSERT/UPDATE/DELETE on the same table. Therefore, the following is perfectly valid:

USE tempdb
GO
CREATE TABLE dbo.AppendTriggerTest (RowId INT IDENTITY(1,1),
                                    RowValue VARCHAR(50)
                                   )
GO

CREATE TRIGGER dbo.MainTrigger
ON dbo.AppendTriggerTest
FOR INSERT
AS
BEGIN
  PRINT '<<<< INFO >>>> Main Trigger fired...'
END
GO

CREATE TRIGGER dbo.AppendTrigger
ON dbo.AppendTriggerTest
FOR INSERT
AS
BEGIN
  PRINT '<<<< INFO >>>> Append Trigger fired...'
END
GO

However, this was not the case in the days of SQL Server 6.5. In order to have a more than one trigger of the same type on the same table, the WITH APPEND clause needs to be used, which is effectively interpreted as AFTER. In the days of SQL Server 6.5, the trigger “AppendTrigger” will need to be defined as:

CREATE TRIGGER dbo.AppendTrigger
ON dbo.AppendTriggerTest
FOR INSERT
WITH APPEND     --NOTICE THE WITH APPEND CLAUSE HERE, 
                --DEPRECATED FROM SQL 2012 ONWARDS
AS
BEGIN
  PRINT '<<<< INFO >>>> Append Trigger fired...'
END
GO

Starting SQL Server 2012, the compatibility levels lower than 90 (i.e. SQL Server 2000 and below) are no longer supported. In accordance to this, the WITH APPEND clause has also been deprecated.

I assume that you are not using this in your production code, if you are, please upgrade! There is no workaround/alternate to this.

Reference:

SQL 2000 Books-On-Line page for CREATE TRIGGER: http://msdn.microsoft.com/en-us/library/aa258254(v=sql.80).aspx

Until we meet next time,

Be courteous. Drive responsibly.

SQL Server - What a table is called, if it has no clustered index?

Thu, 17 May 2012 00:00:00 GMT

Answer:Heap

What is the use of SQL Server Browser ?

Wed, 16 May 2012 00:00:00 GMT

SQL Server Browser contributes to the following actions: 1.Browsing a list of available servers. 2.connecting to correct server instance. 3.Connecting to dedicated administrator connection(DAC).

Getting Started with ASP.NET MVC - Part 5: How to do programming with razor syntax

Tue, 15 May 2012 00:00:00 GMT

In previous chapter, we have learnt about different view engines. In this chapter, we are going learn about how to do programming with the Razor syntax. **What is razor syntax?** As we all know, with ASP.NET MVC3 Microsoft has introduced new view engine, Razor, which was developed to simplify earlier aspx view engine syntax. Here you can write things @character, which called magic character in razor syntax. In razor view engine, you have .cshtml and .vbhtml for view. Where .cshtml is for C# language and .vbhtml is for VB.NET language. **Fundamentals of razor syntax:** - @character is most important character in the Razor Syntax. You can do many things with @Character. Like to print the current datetime you can use @character as shown below:

    Current DataTime: @DateTime.Now

It will print current date time on the browser. ![Date Time][1] - You can also write single line statement and variable declaring with @{} syntax as shown below:

    @{ var number = 10; }

    Number is: @number

It will print number in browser as below: ![Numbers][2] - Same way you can also have multiline syntax with @{} syntax like following.

    @{ 
    var firstName = "jalpesh";
    var lastName="vadgama";
    var nameMessage = string.Format("My full name is : {0} {1}", firstName, lastName);
    }

    My name is :@nameMessage

It will print the name as shown below: ![Full Name][3] - You can also print a path with “\” character. It supports escape character also.

    @{ var filePath = @"C:\ApplicationFolder\"; }

    The file path is: @filePath

It will print filepath in browser as follows: ![Escape Sequence][4] Therefore, from all above syntax you can see @ is a magic character for the razor syntax and you can print anything with @character. **Advance syntaxes for razor:** - **HTML Expressions in razor syntax:** Like other view engine, @razor syntax also supports the HTML expression seamless. You can write composite code with html tags and css etc. @ character with razor syntax supports it all. Some of the examples are:

    @{ var name = "Jalpesh"; }

    The Name: @name

It will print name in italic characters. - **IF and Else loop with razor syntax:** Like any other programming language Razor syntax also supports the ‘if and else’ loop. We can write if and loop like following.

    @{
    var message = string.Empty ;
    if(IsPost)
    {
    message = "Page was postback";
    }
    else
    {
    message = "Page was not posback";
    }
    }

    
    Posback Status:@message

As you can see in the above code I have checked IsPost with ‘if and else’ loop and assigning a message-to-message variable. In addition, I have a button for postback. Also, I have taken a form with post method on a submit button once we click it will post back the form. So first, we run application without postbacking the form. It will print message in browser like following: ![No Postback][5] Now once you click on **submit** button it will look like following: ![With Postback][6] - **For loop in Razor Syntax:** Razor syntax supports for loops also. Let’s take a simple example of for loop and then we will print a number with help of for loop.

    @for(var i = 1; i < 10; i++)
    {
    Number @i
    }

This will print number like following in browser: ![For Loop][7] - **For each loop:** For each loop in razor syntax look like following. Here I am going print all the variable collection.

    @foreach (var variables in Request.ServerVariables)
    {
    }

It will print server variables name like following. ![For each loop][8] - **Switch case statement in Razor Syntax:** Following is a example of switch case syntax and based on case selected it will print the output.

    @{
    var number=1; var message = string.Empty;
    switch(number)
    {
    case 1:
    message = "Number is 1";
    break;
    case 2:
    message = "Number is 2";
    break;
    default:
    message = "Number is 0";
    break;
    }
    @message
    }

It will print case 1 message as we have number assigned to 1 in browser like following: ![Switch Case][9] - **Commenting in Razor Syntax:** Like any other languages, Razor syntax also have comments. Following are the syntaxs for single line and multi-line comments.

    @* single line comment. *@

    @*
    Multi - Line
    Comment
    *@

That is it. As you can see Razor syntax is one of easiest syntax to learn. Hope you liked it. Stay tuned for next chapter. In next chapter, we are going to learn about entity framework and how we can use that in ASP.NET MVC. [1]: http://beyondrelational.com/images/images.ashx?id=241325a4a18f4d9ea282693030c5b786&w=0&h=0 [2]: http://beyondrelational.com/images/images.ashx?id=05cc5b7b6785410a94a281662e3ffe30&w=0&h=0 [3]: http://beyondrelational.com/images/images.ashx?id=1d75a35ae83b453e8f28d96c71c8a37e&w=0&h=0 [4]: http://beyondrelational.com/images/images.ashx?id=e42062764fd240f0b8f1613b4578ad92&w=0&h=0 [5]: http://beyondrelational.com/images/images.ashx?id=818fe6e005be433e87e2bc34c822a895&w=0&h=0 [6]: http://beyondrelational.com/images/images.ashx?id=d87e92f15b5b4504b1f372ee878071e7&w=0&h=0 [7]: http://beyondrelational.com/images/images.ashx?id=c8c953e909ac439e849218c501f883c6&w=0&h=0 [8]: http://beyondrelational.com/images/images.ashx?id=d14ac9bd2bca415298a9f5daf865241e&w=0&h=0 [9]: http://beyondrelational.com/images/images.ashx?id=dad1394ae10341bd9baedcdff1c87490&w=0&h=0

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

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.

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 &**

#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.