What new features are present in SQL Server Denali, R2, 2008

Do you know what features are introduced in 2008/R2/Denali (2011) ?

Is there a single url to check it out ?

Yea, Thanks to this post. This msdn blog is your one stop shop for all SQL Server versions/features to get started....

Features in SQL Server Denali - Please check whitepaper link
Denali Learning Series - Videos from Channel9 - link

Selected Interesting Videos on Denali features

• Columnstore Indexes Unveiled
• Full text Search in SQL Server 2008 and Denali
• The T-SQL Cookbook: What's Cool in Microsoft SQL Server 2008 R2 and New in SQL Server Code-Named "Denali"
• Microsoft SQL Server Code-Named "Denali" AlwaysOn Series,Part 1: Introducing the Next Generation High Availability Solution
• What's New in Microsoft SQL Server Code-Named "Denali" for SQL Server Analysis Services and PowerPivot 

Few more good sql learning resouces from the blog

• Queries with UPDLOCK hints on small tables causing blocking
• How to use SQLDiag, SQLNexus and PAL tools to analyze performance issues in SQL Server
• Notes - SQL Server Index Fragmentation, Types and Solutions

Happy Learning!!!

QA Free Tool - Aptimize Latency Simulator

Simulating page load with slow internet connection is very crucial for ecommerce / banking sites. Today found useful tool to simulate slow user connection in internet explorer

• Aptimize Latency Simulator Tool can be downloaded from link
• Walkthru video is provided in link

After installing the tool you would see below snapshot for starting the tool

Flipkart Page Load in Normal Internet Connection

• Green Color - Latency (Time to Request Info & Start of Data Transfer)
• Blue Color - Data Transfer (Download Time)
• Cold Load - Viewing web page for first time
• Reload Warm  - Viewing already visited page

For Firefox browser as well there must be some add-on to simulate latency. For checking latency/data transfer you can use firebug for firefox browser

Happy Learning!!!

SSRS 2008 Deployment Error - [rsInvalidReportDefinition] The report definition is not valid. Details: The report definition has an invalid target namespace

Learning's on fixing Error - [rsInvalidReportDefinition] The report definition is not valid.  Details: The report definition has an invalid target namespace

Step 1. Developed a SSRS Report in SQL Server 2008 R2 Environment and Tried Deploying in SQL Server 2008 Environment

Step 2. Got below Error
[rsInvalidReportDefinition] The report definition is not valid.  Details: The report definition has an invalid target namespace 'http://schemas.microsoft.com/sqlserver/reporting/2010/01/reportdefinition' which cannot be upgraded.

Step 3. Below link was useful

Step 4. Based on one of the recommendations, Set the Target as SQL Server 2008, Build the Project, Copy the generated rdl file into 2008 target environment

• In SSRS 2008 R2, open the solution, right click on the project file name, and click properties. And the set the TargetServerVersion to SQL Server 2008.

Step 5. Report Project property in SQL Server 2008 R2, You can see target version set to SQL Server 2008

Step 6. Report Project property in SQL Server 2008 Project

This workaround fixed the Error.

Happy Learning!!!

Cloud Based Performance Testing Tool

This post is based on my Stackoverflow QA Answer for Question - What are the tiers of testing that should be done on a large scale distributed system?

On top of it I did some more readings and found some more interesting reads bookmarked below

• The great debate: Windows Azure vs. Amazon Web Services
• Apple icloud runs on both Windows Azure and AWS
• The Evolution of IT Towards Cloud Computing

Coming to utilizing cloud for QA purpose. Soasta is a leader in cloud based performance / functional testing. Developing Test Expertise in cloud based apps is different from traditional test approach. Particularly, How much load cloud can scale up is the key.

One of biggest advantage on their approach mentioned in blogpost. Using Cloudtest-grid to scale up/scale down load for Performance Testing, Simulating live traffic. CloudTest Lite is a cloud based free tool for performance testing Web apps/Cloud based apps.

Many thanks to Patrica for her post on CloudTest Lite. This post was the beginning of my learning on CloudTest Lite. I am looking forward to explore this tool in coming weeks. Tool looks promising for performance testing web applications.

Happy Learning!!

Managing SQL Stored Procedures - Categorize for better Maintenance

Next Free Tool is SQLTreeO. I came to know about this tool from SQL blog post. Andy Leonard has provided clear walkthrough for the tool.

Installation is pretty easy. This tool is a great way to categorise and organize procedures. Please find below snapshot. I have categorised procedures for validation, reporting, loading data.



Happy Learning!!!

Revising TSQL Enhancements in SQL 2005, SQL 2008

If you are looking for SQL 2011 - Denali TSQL enhancements, Please check blogpost in brentozar site Leaving the Windows Open.

I will keep this post as my reference to learn Denali TSQL Improvements. I wanted to summarize TSQL enhancements for SQL 2005/2008. This post is for that purpose

SQL 2005 TSQL Enhancements

• SQL 2005 - TSQL Enhancements - This post covers CTE, ROW_NUMBER( ), CTE, Pivot, rank( ), ntile( ), dense_rank( )
• Example use of feature - CTE, ROW_NUMBER( ), OUTPUT - Basics - Deleting Duplicate Entries using ROW_NUMBER() Function
• T-SQL Set Operators INTERSECT, EXCEPT, and UNION
• Using NTILE with Cross Reference Values to Create Dimensions

SQL 2008 TSQL Enhancements

This is based on TSQL 2008 enhancement session that I took couple of years back for one of my previous employer.

• New Date/Time Data Types were Introduced - DATE, TIME, DATETIME2, DATETIMEOFFSET 
• Declare and Initialize variables feature. Declare and initialize in same statement
• New Assignment Operators - +=, -=, *=, /=, %=. Example
   
• DECLARE @a AS INT =5, @b AS INT =5, @C AS INT = 5
  SET @b+=5
  SET @a+= @b
  SET @C*=5
  PRINT @a
  PRINT @b
  PRINT @C
  
  
  
  
• ROW CONSTRUCTORS - Simplify Insert Operations. Insert multiple records in a single statement
   
• Table Value Parameter - Pass Array of Elements. Eliminates Parse, SQL Injections. Please find below example on using TVP. Example  

• --Step 1

  --Diff Between Table Type and Table variable

  DROP TABLE TEST;

  DROP PROCEDURE AddRecords;

  DROP TYPE TvpTest;
  
  

  --Step 2

  CREATE TABLE TEST (Id int not null, Name varchar(100))
  

  GO

  CREATE TYPE TvpTest AS TABLE (Id int not null, Name varchar(100))

  GO
  
  
  
  --Step 3

  --2, READ ONLY OPTION MANDATORY
  CREATE PROCEDURE AddRecords (@TableVariable TvpTest READONLY)
  

  AS

  BEGIN

  INSERT INTO TEST

  SELECT * FROM @TableVariable

  END

  GO
  
  
  --Step 4, ROW CONSTRUCTORS

  DECLARE @TTTABLE AS TvpTest
  

  INSERT INTO @TTTABLE VALUES

  (1,'Sijo'),

  (2,'Sivaram'),

  (3,'Adam')

  EXEC AddRecords @TableVariable = @TTTABLE

  SELECT * FROM TEST
  
• Merge Feature  - Conditionally insert, update and delete data
   
• --Step 1
  IF OBJECT_ID('Table1') IS NOT NULL
      DROP TABLE Table1;
  CREATE TABLE Table1 (
      Id   INT       PRIMARY KEY CLUSTERED,
      Name CHAR (50)
  );
  GO
  DROP TYPE TVPMerge;
  
  
  

  
  

  --Step 2, Row Constructors
  INSERT  INTO Table1
  VALUES (1, 'Sijo'),
  (2, 'Sivaram'),
  (3, 'Adam'),
  (4, 'Mike');
  
  
  
  --Step 3
  CREATE TYPE TVPMerge  AS TABLE (
      Id   INT      ,
      Name CHAR (50));
  GO
  
  
  
  --Step 4
  DECLARE @TVPTABLE AS TVPMerge;
  INSERT  INTO @TVPTABLE
  VALUES (1, 'Sijo LastName'),
  (2, 'Sivaram LastName1'),
  (3, 'Adam LastName2'),
  (100, 'New Joinee');
  MERGE INTO Table1
   AS A
  USING @TVPTABLE AS B ON A.Id = B.Id
  WHEN MATCHED THEN UPDATE
  SET A.Name = B.Name
  WHEN NOT MATCHED THEN INSERT (Id, Name) VALUES (B.Id, B.Name)
  WHEN NOT MATCHED BY SOURCE THEN DELETE;
  GO
  SELECT *
  FROM   Table1;
  
• Spatial Data - Geometry and Geography Data Types
• Grouping Sets, ROLLUP and CUBE( ), GROUPING SETS in SQL Server 2008, Grouping Sets in SQL Server 2008 - Example II
  
• CREATE TABLE TABLEA (COL1 INT, COL2 VARCHAR(10), COL3 INT)
  GO
  INSERT INTO TABLEA VALUES (1, 'A', 10), (1, 'A', 20),(1, 'A', 30),(1, 'B', 90),(2, 'A', 30),(2, 'A', 100),(3, 'C', 110),(3, 'C', 120)
  GO
  
  
  --GROUP BY DIFFERENT OPTIONS
  SELECT COL1, COL2, COUNT(*) AS COUNTS, SUM(COL3) TOTALVALUE
  FROM TABLEA
  GROUP BY GROUPING SETS
  (
  (COL1, COL2),
  (COL2),
  (COL1)
  )
  ORDER BY 1
  GO
  
  
  --EQUIVALENT OF ABOVE GROUPING SETS USING TSQL
  SELECT COL1,  NULL AS 'COL2', COUNT(*) AS COUNTS, SUM(COL3) TOTALVALUE
  FROM TABLEA
  GROUP BY COL1
  UNION
  SELECT NULL AS 'COL1', COL2, COUNT(*) AS COUNTS, SUM(COL3) TOTALVALUE
  FROM TABLEA
  GROUP BY COL2
  UNION
  SELECT COL1, COL2, COUNT(*) AS COUNTS, SUM(COL3) TOTALVALUE
  FROM TABLEA
  GROUP BY COL1, COL2
  GO
• FileStream Support for Storing Files, HierarchyId - Storing Hierarchical / Tree Structure
• Filtered Indexes - Nonclustered index which can be created only on certain set of records, Subset of records

Happy Learning!!!