Search this blog

Monday, July 23, 2012

E-Book Gallery for Microsoft Technologies

You can download existing and new content for ASP.NET, Office, SQL Server, Windows Azure, SharePoint Server and other Microsoft technologies in e-book formats. Reference, guide, and step-by-step information are all available. All the e-books are free




Programming Windows 8 Apps with HTML, CSS, and JavaScript (First Preview)


Happy Learning J

Tuesday, May 22, 2012

Isolation Level Of Current Database

Following SQL Code-snippet will helps to find out What is the Isolation Level is configured for Current Database,

SELECT CASE transaction_isolation_level
      WHEN 0 THEN 'Unspecified'
      WHEN 1 THEN 'ReadUncomitted'
      WHEN 2 THEN 'Readcomitted'
      WHEN 3 THEN 'Repeatable'
      WHEN 4 THEN 'Serializable'
      WHEN 5 THEN 'Snapshot' END AS TRANSACTION_ISOLATION_LEVEL
FROM
      sys.dm_exec_sessions
where session_id = @@SPID

The above query will return the Isolation of of current DB. There are many ways to find out this settings. 
You can find it out by using DBCC command as well,
DBCC USEROPTIONS
After execute the given DBCC Command, look at the value of "isolation level" in the result, refer the exhibit given below:-

Monday, April 9, 2012

Difference Between SQL Vs. T-SQL?

SQL and T-SQL are Query languages, both helps to extract, Manipulate data with database system.

SQL – Structured Query Language:-

It is pronounced as SEQUEL, stands for… Structured English QUEry Languages. 
  • SQL Language has several language elements called clauses, phrases, predicates, queries and statements. 
  • SQL Supports  to insert/update/read/delete the data from/to the database, define the new structure, etc., 
  • SQL supports different data types like image, text, varchar, datetime,xml, etc., SQL supports only single line transactions

T-SQL – Transact-SQL:-

T-SQL is advanced/addition to the SQL developed by Microsoft. It has new features like, variable, temp tables, set of transactions, store procedures, functions, cursors etc.., T-SQL supports control flow capabilities by using the following commands: START and TERMINATE, break, CONTINUE, GOTO, RETURN, IF, While and WAITFOR. T-SQL allows the ‘FROM’ clause to be added to DELETE and UPDATE statements. T-SQL allows inserting multiple rows using single statement BULK INSERT.

Friday, March 16, 2012

Introducing Microsoft SQL Server 2012 - EBook Download

The full version of "Introducing Microsoft SQL Server 2012" by "Ross Mistry” and “Stacia Misner” is now available for free download,
"Introducing Microsoft SQL Server 2012" includes 10 chapters:

PART I - DATABASE ADMINISTRATION (by Ross Mistry)
1. SQL Server 2012 Editions and Engine Enhancements
2. High-Availability and Disaster-Recovery Enhancements
3. Performance and Scalability
4. Security Enhancements
5. Programmability and Beyond-Relational Enhancements

PART II - BUSINESS INTELLIGENCE DEVELOPMENT(by Stacia Misner)
6. Integration Services
7. Data Quality Services
8. Master Data Services
9. Analysis Services and PowerPivot
10.Reporting Services

 

Tuesday, March 13, 2012

Find Nth Highest Value – With Using Only Aggregate Function

Problem:

Today I came across a SQL Puzzle, “How to Find Nth Highest value without using any Built in ANSI Functions like Top, Rank() , Dense_Rank(), Row_number(), Etc., you can use only aggregate functions like Min, Max and Count ?”

Solution:

The following queries will help you to identify Nth highest value with using Only Aggregate Functions.

Note: I’ve used Sales.SalesOrderDetail Table from adventure Works Database in my script, you can replace the column and table name as per your table structure.

Use
AdventureWorks

--- To Find Nth Highest Value, Replace N with Some Number in the given Query, For Example, if you want to find 12th Highest value, Use 12 Instead of "N"

SELECT distinct UnitPrice
FROM Sales.SalesOrderDetail E1
WHERE N = 
          (
            SELECT COUNT(DISTINCT E2.UnitPrice)
            FROM Sales.SalesOrderDetail E2
            WHERE E2.UnitPrice >= E1.UnitPrice
          )

---- following Query will returns distinct values in Descending order, you can refer this Query’s output to verify Nth value of previous query output.

select distinct UnitPrice from Sales.SalesOrderDetail A
order by UnitPrice desc

Hope it Helps!

Download Visual Studio 11 Beta today!

Microsoft Released the Visual Studio 11 Beta version, It Includes following new features:-
  • New HTML and CSS editors extend Visual Studio’s rich editing capabilities to make HTML 5 and CSS 3 development more productive, as well as enrich traditional HTML development.  
  • Visual Studio 11 gives you the tools required to build and deploy Windows Azure applications, including project templates, a seamless debugging experience, and easy publishing.
  • New designers make building Windows 8 Metro and traditional Windows desktop applications easier and faster than ever.
  • Flexible agile planning tools (like capacity planning, task boards, and product backlog) allow teams to adopt incremental development techniques and agile methodologies, like SCRUM, at their own pace.
Get Visual Studio 11 Beta today and start preparing for the next generation of
development. 


 

SQL Server 2012 RTM samples databases/samples

Today Microsoft published the samples databases and samples projects for SQL Server 2012 RTM. You can find the downloads and descriptions on the following pages:

CodePlex:

TechNet Wiki:

Download Center:

Sunday, March 11, 2012

Adventure Works For SQL 2012

The AdventureWorks2012 OLTP database name was updated in February for SQL Server 2012 RC0, based on customer feedback from its original RC0 release in December 2011. Basically, the database, filename, and logical files were renamed from AdventureWorks2008R2 to AdventureWorks2012, as indicated on the CodePlex page.

For the RTM release, AdventureWorks2012 will be published along with the RTM versions of AdventureWorksDW2012 and other sample projects by EOD Monday, 3/12.

AdventureWorks2012 will have two versions: AdventureWorks2012_Data.mdf for case-insensitive collation and AdventureWorks2012_CS_Data.mdf for case-sensitive. You will also be able to download a DDL script and .csv files to install the database from script. The dates in the database have remained circa 2008 dates.

Wednesday, March 7, 2012

SQL Server 2012 Launch Announcements‏


Hi... Wanted to give you a quick heads-up on news today from Microsoft regarding SQL Server:

Microsoft announced that SQL Server 2012 has released to manufacturing (RTM). Customers and partners can download an evaluation of the product today and can expect general availability to begin on April 1.

Microsoft also disclosed that it will release a new, limited preview of an Apache Hadoop-based service for Microsoft Windows Azure in the first half of 2012. The Apache Hadoop-based service helps you to glean simple, actionable insights from complex data sets hosted in the cloud by optimizing connectivity with SQL Server 2012 and tools such as Excel, PowerPivot, and Power View. Customers interested in signing up for the latest preview should visit this page. 

I also wanted remind you about Today’s SQL Server 2012 virtual launch event.

For more context and information on this news, check out Ted Kummert’s blog post and visit the SQL Server News Center page.

Monday, March 5, 2012

SQL Database File Extensions

The database file extensions .mdf and .ldf are quite straightforward.

Data files are organized in logical groups called filegroups (FG). Filegroups are your way to control what will be the physical locations of your objects. A database must have at least one filegroup called PRIMARY, and optionally other user filegroups as well. The PRIMARY filegroup contains the primary data file (extension .mdf), and the database's system catalog.

You can optionally add secondary data files (extension .ndf) to PRIMARY. User filegroups contain only secondary data files. You can determine which of the filegroups is marked as the default filegroup. An object is created on the default filegroup when the object creation statement does not explicitly specify the target filegroup. 

For Example: Database Partition

.ldf stands for Log Data File. Although SQL Server can write to multiple data files in parallel, it can only write to one log file at a time, in a sequential manner. Therefore, unlike with data files, having multiple log files does not result in performance benefit. You might need to add log files if the disk drive where the log resides runs out of space.

Reference: Microsoft® SQL Server® 2008 T-SQL Fundamentals by Itzik Ben-Gan

Thursday, February 23, 2012

ColumnStore Index - SQL 2012 New features

Summary:
The SQL Server 11.0 release (code named “Denali”) introduces a new data warehouse query acceleration feature based on a new type of index called the columnstore. This new index, combined with enhanced query processing features, improves data warehouse query performance by hundreds to thousands of times in some cases, and can routinely give a tenfold speedup for a broad range of decision support queries. This can allow end users to get more business value from their data through fast, interactive exploration. IT workers can reduce development costs and ETL times since columnstore indexes limit or eliminate the need to rely on pre-built aggregates, including user-defined summary tables, and indexed (materialized) views. Furthermore, columnstore indexes can greatly improve ROLAP performance, making ROLAP more attractive.


Note: above content is copied from the White paper discussed in this post

How Reporting Services Works?


Here I’ve explained briefly on how Reporting service requests are handled through the components of SSRS architecture.
As shown in the diagram: Firstly, request will be received by Reporting server’s OS through Http.sys Driver, the driver will route the communication to reporting service’s Web service.  Http Listener will receive the request which will be re-routed by Http.sys.
Http Listener transfers the request to Security sub layer (SSL) for authentication.  Requestor is identified using 4 different authentication mechanisms like Kerberos, NTLM, Negotiate and basic authentication.
Each mechanism supports different approach. Once user has been authorized by SSL, user communication will be redirected to either Report Manager or Report Server which is hosted in RS web services (refer the exhibit). These two components will read the report definition, report details like parameter, building report query, and some of the activities which are listed below, etc., will be done with the help of Core Processing unit.
Activities of Core Processing component are:-
  • Scheduling
  • Subscription Management
  • Report Processing
Upon completion of processing all SSRS components, Report output will be rendered on report viewer.
Hope this is helpful!