Top 12 Features of SQL Server 2012

Top 12 Features of SQL Server 2012

Microsoft has introduced SQL Server 2012 to the world and it's time for IT professionals to start to come to speed on what's new in this highly anticipated version of SQL Server.

1. AlwaysOn Availability Groups -- This feature takes database mirroring to a whole new level. With AlwaysOn, users will be able to fail over multiple databases in groups instead of individually. Also, secondary copies will be readable, and can be used for database backups. The big win is that your DR environment no longer needs to sit idle.

2. Windows Server Core Support -- If you don't know what Windows Server Core is, you may want to come up to speed before Windows 8 (MS is making a push back to the command line for server products). Core is the GUI-less version of Windows that uses DOS and PowerShell for user interaction. It has a much lower footprint (50% less memory and disk space utilization), requires fewer patches, and is more secure than the full install. Starting with SQL 2012, it is supported for SQL Server.

3. Columnstore Indexes -- This a cool new feature that is completely unique to SQL Server. They are special type of read-only index designed to be use with Data Warehouse queries. Basically, data is grouped and stored in a flat, compressed column index, greatly reducing I/O and memory utilization on large queries.

4. User-Defined Server Roles -- DBAs have always had the ability to create custom database role, but never server wide. For example, if the DBA wanted to give a development team read/write access to every database on a shared server, traditionally the only ways to do it were either manually, or using undocumented procedures. Neither of which were good solutions. Now, the DBA can create a role, which has read/write access on every DB on the server, or any other custom server wide role.

5. Enhanced Auditing Features -- Audit is now available in all editions of SQL Server. Additionally, users can define custom audit specifications to write custom events into the audit log. New filtering features give greater flexibility in choosing which events to write to the log.

6. BI Semantic Model -- This is replacing the Analysis Services Unified Dimensional Model (or cubes most people referred to them). It's a hybrid model that allows one data model will support all BI experiences in SQL Server. Additionally, this will allow for some really neat text infographics

7. Sequence Objects -- For those folks who have worked with Oracle, this has been a long requested feature. A sequence is just an object that is a counter -- a good example of it's use would be to increment values in a table, based a trigger. SQL has always had similar functionality with identity columns, but now this is a discrete object.

8. Enhanced PowerShell Support -- Windows and SQL Server admins should definitely start brushing up on their PowerShell scripting skills. Microsoft is driving a lot of development effort into instrumenting all of their server-based products with PowerShell. SQL 2008 gave DBAs some exposure to it, but there are many more in cmdlets in SQL 2012.

9. Distributed Replay -- Once again this is answer to a feature that Oracle released (Real Application Testing). However, and in my opinion where the real value proposition of SQL Server is, in Oracle it is a (very expensive) cost option to Enterprise Edition. With SQL, when you buy your licenses for Enterprise Edition, you get everything. Distributed replay allows you to capture a workload on a production server, and replay it on another machine. This way changes in underlying schemas, support packs, or hardware changes can be tested under production conditions.

10. PowerView -- You may have heard of this under the name "Project Crescent" it is a fairly powerful self-service BI toolkit that allows users to create mash ups of BI reports from all over the Enterprise.

11. SQL Azure Enhancements -- These don't really go directly with the release of SQL 2012, but Microsoft is making some key enhancements to SQL Azure. Reporting Services for Azure will be available, along with backup to the Windows Azure data store, which is a huge enhancement. The maximum size of an Azure database is now up to 150G. Also Azure data sync allows a better hybrid model of cloud and on-premise solutions

12. Big Data Support -- I saved the biggest for last, introduced at the PASS (Professional Association for SQL Server) conference last year, Microsoft announced a partnership with Hadoop provider Cloudera. One part of this involves MS releasing a ODBC driver for SQL Server that will run on a Linux platform. Additionally, Microsoft is building connectors for Hadoop, which is an extremely popular NoSQL platform. With this announcement, Microsoft has made a clear move into this very rapidly growing space.

SQL 2012 is a big step forward for Microsoft -- the company is positioning itself to be a leader in availability and in the growing area of big data. As a database professional, I look forward to using SQL 2012 to bring new solutions to my clients.

MDX Date Formatting in SSRS

MDX Date Formatting in SSRS:

Suppose there is a Date Dimension in Cube which returns date as below through MDX.

Issues:

1. Date Format should be "MM/dd/yyyy" like  12/31/2042 in SSRS Reports.
2. Unknown should appear as Blank.

Condition: 

          No Admin rights in Cube. We can right expression in SSRS only.

Solution:

         We can use below expression in SSRS report for getting above formatted result:

=IIF(Year( IIF( ISDATE(Fields!Date.Value) = FALSE, "1900-01-01",Fields!Date.Value )  )="1900" , Nothing ,  FORMAT( CDATE( IIF(ISDATE(Fields!Date.Value) = FALSE, "1900-01-01",Fields!Date.Value) )  ,"MM/dd/yyyy"  ) )

After using above expressions, below result will appear on report:

Unknown values are appearing as Blank and other date formatting coming as MDY format.

Thanks.

Export data from MDX Query directly on Excel file.

Export data from MDX Query directly on Excel file.

Hi Guys,

While generating data result thru MDX query some time we face many issues like taking or coping data result on SSMS, because MDX query generally does not give column names on sql management studio. It gives the column names only for the Measures.
also if you are using any property field of any dimension in MDX that field also does not come in result set.

Now I am sharing below scenario to extract data with column names and property fields.

First we have to make a data source connection thru excel data tab as below screen shot.

1. Now enter your server name .. click on next
2. Select Database and Cube.. click on next
3. Save Data Connection and Finish.
4. One pop up window will appear "Import Data". Click on CANCEL button.

NOW, go to this datasource location, by default it will be under Libraries\Documents\My Data Sources. There would be a .ODC file. Open with / Edit this in notepad by right clicking on .odc file.
below are some steps that should be edit.

We have to first change highlighted text Cube (as highlighted in yellow) and write "MDX" (as highlighted in Green) then remove CubeName (as highlighted in yellow) with your MDX query (as highlighted in Green).

Save it and close it.

Come to excel sheet -> go to open file -> open that datasource->click on enable
now you will get desired MDX output on same sheet.

Some extra columns could be appeared which you have to remove it like below mentioned.

Now save this excel file and use it. :)

SSRS - Unchecked some values by Default from Multiple Value Parameter

SSRS - Unchecked some values by Default from Multiple Value Parameter.

There was one requirement in SSRS that Client wants to select some values in multiple value parameter by default but they need the functionality to see all values in same parameter. For example please have look in below screen shot..

here client wants to unchecked Afghanistan and Albania (marked in Red) by default but they want to see these values in parameter so that in future they can run report for Afghanistan and Albania by selecting these values. For these such type of functionality I tried a scenario as below:

Step-1
Make connection with AdventureWorks Database..

Step-2
Add Dataset1 in SSRS with SQL - select * from Person.CountryRegion.

Step-3
Add Dataset2 in SSRS with SQL - select * from Person.CountryRegion
where CountryRegionCode not in ('AF','AL','AQ') -- it will filter some countries like Afghanistan, Albania.

Step-4
Add Parameter "Counrty" select allow multiple values:

Step-5
Go to Available Values and choose Dataset1 for values from Query:

Step-6
Go to Default Values and Choose DATASET2 for values from Query:

Click OK..

Run the Report and you will get parameter as below:

Thanks .. 

Instant SQL Formatter

Instant SQL Formatter

Below is the link of Instant SQL Formatter 

We can Format our SQL Code or we can download this too.

http://www.dpriver.com/pp/sqlformat.htm

SQL SERVER – Query to Find Column From All Tables of Database

Below script will return all the tables containing specific column along with their schema name.

USE Master

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 '%NAME%'

ORDER BY schema_name, table_name

Style Arguments for the CONVERT Datetime Function

Style Arguments for the CONVERT Function

Style	Associated standard	Input/Output ( 3 ) format	date	time(n)	datetime2(n)	datetimeoffset(n)
0 or 100 (1, 2)	Default	mon dd yyyy hh:miAM (or PM)	Jan 1 2001	12:20PM	Jan 1 2001 12:20PM	Jan 1 2001 12:20PM -08:00
101	U.S.	mm/dd/yyyy	1/1/2001	-	1/1/2001	1/1/2001
102	ANSI	yy.mm.dd	2001.01.01	-	2001.01.01	2001.01.01
103	British/French	dd/mm/yy	1/1/2001	-	1/1/2001	1/1/2001
104	German	dd.mm.yy	01.01.2001	-	01.01.2001	01.01.2001
105	Italian	dd-mm-yy	1/1/2001	-	1/1/2001	1/1/2001
106 (1)	-	dd mon yy	1-Jan-01	-	1-Jan-01	1-Jan-01
107 (1)	-	Mon dd, yy	1-Jan-01	-	1-Jan-01	1-Jan-01
108	-	hh:mi:ss	-	12:20:20	12:20:20	12:20:20
9 or 109 (1, 2)	Default + milliseconds	mon dd yyyy hh:mi:ss:mmmAM (or PM)	Jan 1 2001	12:20:20.1234567AM	Jan 1 2001 12:20:20.1234567PM	Jan 1 2001 12:20:20:1230000PM -08:00
110	United States	mm-dd-yy	1/1/2001	-	1/1/2001	1/1/2001
111	JAPAN	yy/mm/dd	1/1/2001	-	1/1/2001	1/1/2001
112	ISO	yymmdd	20010101	-	20010101	20010101
13 or 113 (1, 2)	Europe default + milliseconds	dd mon yyyy hh:mi:ss:mmm(24h)	1-Jan-01	20:20.1	20:20.1	01 Jan 2001 12:20:20:1230000 -08:00
114	-	hh:mi:ss:mmm(24h)	-	20:20.1	20:20.1	12:20:20:1230000 -08:00
20 or 120 (2)	ODBC canonical	yyyy-mm-dd hh:mi:ss(24h)	1/1/2001	12:20:20	1/1/2001 12:20	2001-01-01 12:20:20 -08:00
21 or 121 (2)	ODBC canonical (with milliseconds)	yyyy-mm-dd hh:mi:ss.mmm(24h)	1/1/2001	20:20.1	20:20.1	2001-01-01 12:20:20.1230000 -08:00
126 (4)	ISO8601	yyyy-mm-ddThh:mi:ss.mmm (no spaces)	1/1/2001	20:20.1	2001-01-01T 12:20:20.1234567	2001-01-01T 12:20:20.1234567
127(6, 7)	ISO8601 with time zone Z.	yyyy-mm-ddThh:mi:ss.mmmZ	1/1/2001	12:20:20.1234567Z	2001-01-01T 12:20:20.1234567Z	2001-01-01T20:20:20.1230000Z
		(no spaces)
130 (1, 2)	Hijri (5)	dd mon yyyy hh:mi:ss:mmmAM	1-Jan-01	12:20:20.1230000PM	01 Jan 2001 12:20:20.1230000PM	1 Jan 2001 12:20:20:1230000PM -08:00
131 (2)	Hijri (5)	dd/mm/yy hh:mi:ss:mmmAM	1/1/2001	12:20:20.1230000PM	01/01/2001 12:20:20.1230000PM	01/01/2001 12:20:20.1230000PM -08:00