-- My MDX Experiments

select from [Adventure Works2008];

-- Use of ALLMEMBERS and CHILDREN functions

select [Measures].[Vacation Hours]  on columns,
[Employee].[Last Name].ALLMEMBERS on rows
 from [Adventure Works2008];


 select [Measures].[Vacation Hours]  on columns,
[Employee].[Last Name].CHILDREN on rows
 from [Adventure Works2008];

 -- tuples
 select  ([Employee].[Birth Date]. &[1942-04-03T00:00:00],
 [Employee].[Birth Date].&[1941-11-17T00:00:00]} on rows,
[Employee].[Last Name] on columns
 from [Adventure Works2008];

 -- cross join
select {[Employee].[Job Title].CHILDREN * [Employee].[Marital Status].CHILDREN} on rows,
[Measures].[Sick Leave Hours] on columns from
[Adventure Works2008];

select crossjoin([Employee].[Job Title].CHILDREN , [Employee].[Marital Status].CHILDREN) on rows,
[Measures].[Sick Leave Hours] on columns from
[Adventure Works2008];

--eliminate null values

select
non empty crossjoin([Employee].[Job Title].CHILDREN , [Employee].[Marital Status].CHILDREN) on rows,
[Measures].[Sick Leave Hours] on columns from
[Adventure Works2008];

-- Apply Slicing using Where Clause

select [Employee].[Last Name].MEMBERS on columns,
[Measures].[Sick Leave Hours] on rows
from [Adventure Works2008]
where [Employee].[Birth Date].&[1946-11-09T00:00:00];

-- filtering

select [Employee].[Last Name].MEMBERS on columns,
filter([Employee].[Job Title].MEMBERS,
[Measures].[Sick Leave Hours] <100 nbsp="" on="" p="" rows="">from [Adventure Works2008]
where {[Employee].[Birth Date].&[1946-11-09T00:00:00]};

-- sorting

select [Measures].[Sick Leave Hours] on columns,
order([Employee].[Last Name].MEMBERS,[Measures].[Sick Leave Hours],desc) on rows
from [Adventure Works2008]
where {[Employee].[Birth Date].&[1946-11-09T00:00:00],
[Employee].[Birth Date].&[1946-05-06T00:00:00]};

What's new in SQL Server 2014

http://msdn.microsoft.com/en-us/library/bb500435.aspx

1. Delayed Durability
SQL Server 2014 lets us mark certain transactions as delayed durable, meaning control returns to the client before the log record is written to disk, as opposed to fully durable, which hardens the transaction log to disk before returning control to the client. Also called Lazy Commit, delayed durability can help reduce latency related to log I/O by keeping the transaction log records in memory and writing to the transaction log in batches.  If you’re experiencing log I/O contention, it can also help reduce waits in the system. This setting – which we can control at the database, commit, or atomic level – provides for many new scalability opportunities and challenges. I’m looking forward to the solutions our community will create to leverage this capability.

2. New Cardinality Estimation Design
The cardinality estimator has been redesigned in SQL Server 2014 to improve query plan quality and query performance. This new estimator calculates cardinality, essentially the number of rows the optimizer processes in an operation, using assumptions and algorithms that support modern OLTP and data warehousing workloads. The Microsoft SQL Server engineering team did a lot of research on these workloads to deliver a modern algorithm set that is customer tested and proven. 

3. Clustered Columnstore Indexes
Since SQL Server 2012 introduced nonclustered columnstore indexes, many of us have been looking forward to clustered columnstore indexes and seeing this new SQL Server 2014 feature in action in our own environments. A clustered columnstore index will improve data compression and query performance for many data warehousing workloads, especially in read-heavy and bulk-loading scenarios. And because the CCI is updatable, we can perform Selects, Inserts, Updates, and Deletes on these tables while still getting the performance of a clustered columnstore.

SQL Server Query to find all the tables in a database with a specific column name

Following Query return all columns with name 'Col_name'
===============================================

GO
SELECT
t.name AS table_name
,SCHEMA_NAME(schema_id) AS schema_name
,c.name AS column_name
FROM
sys.tables AS t
INNER JOIN sys.columns c
ON t.OBJECT_ID = c.OBJECT_ID
WHERE
c.name LIKE '%Col_name%'
ORDER BY
schema_name
,table_name;

Ways to Insert data from one table to another - SQL Server

Method 1 :  INSERT INTO SELECT


Use this method if the destination table is already created in the destination database.  
USE  MyDb
GO


INSERT INTO DestinationTable(FirstName, LastName)

SELECT FirstName, LastName

FROM SourceDatabase.SourceTable
WHERE condition= 1


Method 2 :  SELECT INTO


Use this method if the destination table is not already created in the destination database. The SELECT statement would create the new table with same data types in the destination database along with copying the data.

SELECT FirstName, LastName


INTO DestinationTable

FROM SourceDatabase.SourceTable

WHERE condition= 1

In this case new table 'DestinationTable' will be created with same data type as the selected columns.

Insert Multiple Records Using One Insert Statement - SQL Server

How to insert multiple records using one insert statement(using UNION ALL ) instead of going for individual INSERT statements for each row. Here is the sample:


USE YourDbName

GO

INSERT INTO YourTable(FirstColumn, SecondColumn)

SELECT 'First' ,1

UNION ALL

SELECT 'Second' ,2

UNION ALL

SELECT 'Third' ,3

UNION ALL

SELECT 'Fourth' ,4

UNION ALL

SELECT 'Fifth' ,5

GO

Select Top 10% of rows - SQL Server 2008

Execute the following SQL Server T-SQL scripts in SSMS Query Editor to list the top 5 percent result set with ties and without ties:

USE AdventureWorks;



SELECT TOP ( 10 ) PERCENT WITH TIES Employee = FirstName + ' ' + Lastname,

e.Title,

Gender,

Rate

FROM HumanResources.Employee e

JOIN HumanResources.EmployeePayHistory eph

ON e.EmployeeID = eph.EmployeeID

JOIN Person.Contact c

ON c.ContactID = E.ContactID

ORDER BY Rate DESC;

GO

=====================================================================



SELECT TOP ( 10 ) PERCENT Employee = FirstName + ' ' + Lastname,

e.Title,

Gender,

Rate

FROM HumanResources.Employee e

JOIN HumanResources.EmployeePayHistory eph

ON e.EmployeeID = eph.EmployeeID

JOIN Person.Contact c

ON c.ContactID = E.ContactID

ORDER BY Rate DESC;

PrintDialog in C#

A PrintDialog control is used to open the Windows Print Dialog and let user select the printer, set printer and paper properties and print a file. A typical Open File Dialog where you select a printer from available printers, set printer properties, set print range, number of pages and copies and so on. Clicking on OK button sends the document to the printer.

Creating a PrintDialog
We can create a PrintDialog at design-time as well as at run-time

Design-time
To create a PrintDialog control at design-time, you simply drag and drop a PrintDialog control from Toolbox to a Form in Visual Studio.

Run-time
Creating a PrintDialog control at run-time is simple. First step is to create an instance of PrintDialog class and then call the ShowDialog method. The following code snippet creates a PrintDialog control.

PrintDialog PrintDialog1 = new PrintDialog();

PrintDialog1.ShowDialog();


Printing Documents


PrintDocument object represents a document to be printed. Once a PrintDocument is created, we can set the Document property of PrintDialog as this document. After that we can also set other properties. The following code snippet creates a PrintDialog and sends some text to a printer.

private void PrintButton_Click(object sender, EventArgs e)

{

PrintDialog printDlg = new PrintDialog();

PrintDocument printDoc = new PrintDocument();

printDoc.DocumentName = "Print Document";

printDlg.Document = printDoc;

printDlg.AllowSelection = true;

printDlg.AllowSomePages = true;

//Call ShowDialog

if (printDlg.ShowDialog() == DialogResult.OK)

printDoc.Print();



}