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,

Download Introducing Microsoft SQL Server 2012 

"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

 

Download Introducing Microsoft SQL Server 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:

Adventure Works for SQL Server 2012

TechNet Wiki:

SQL Server Samples Readme

Download Center:

Power View and PowerPivot HelloWorldPicnic Samples for SQL Server 2012  

Image Files for HelloWorldPicnic Sample Model in PowerPivot and Power View

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.

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.

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