SQL SERVER 2012 -- IIF function

msdn link
IIF() function is new to SQL Server family of functions. It is a brand new logical function being introduced with SQL Server 2012 that allows you to perform IF..THEN..ELSE condition within a single function. This helps in simplified code and easy to read conditions.

In earlier versions of SQL Server, we have used IF..ELSE and CASE..ENDCASE to perform logical conditional operations. However, IIF() can be used as a shorthand way of writing conditional CASE statements in a single function. It evaluates the expression passed in the first parameter with the second parameter depending upon the evaluation of the condition and returns either TRUE or FALSE

In this example, we will evaluate the values of two variables and return the result of a variable which is bigger in value.

DECLARE @A INT=40
DECLARE @B INT=30
SELECT IIF(@A > @B, 'A IS GREATER THAN B', 'B IS GREATER THAN A')
GO;

Executing the above T-SQL will return the following result:
-------------------
A IS GREATER THAN B (1 row(s) affected)

In this example, we will evaluate the age of John and Julie and identify who is older between them. Please observe the use of sub functions within IIF.
DECLARE @JOHN_AGE INT=35
DECLARE @JULIE_AGE INT=29
SELECT IIF(@JOHN_AGE > @JULIE_AGE
            , 'JOHN IS OLDER THAN JULIE BY '+LTRIM(STR(@JOHN_AGE-@JULIE_AGE))+' YEARS'
, 'JULIE IS OLDER THAN JOHN BY '+LTRIM(STR(@JULIE_AGE-@JOHN_AGE))+' YEARS')
GO;

Executing the above statement will return the following result:
--------------------------------------------
JOHN IS OLDER THAN JULIE BY 6 YEARS (1 row(s) affected)

Note: Like CASE statements which can be nested up to 10 levels, IIF() function can also nest up to 10 levels.

How to know installed Sql Server instances on a server

We can install multiple instances of Sql Server on a given server.
How to know what are the sqlserver instances present on the server.

Open the Run command and type Regedit and then press enter.
go to the following location.
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\Instance Names\SQL
one entry is present for each instance.

Another way is from the services and find the services as below.
Open the Run command and type services.msc and then press enter.
It will open the Services window and then look for the following
"Sql Server (Instance Name)".

How to open Run command in Windows 8 Developer Preview

You can open the Run command in Windows 8 Developer Preview by clicking on Windows key in the keyboard and then pressing "R" key.

How to enable .Net 3.5 in Windows 8 machine

Use the following command to enable .net 3.5 in Windows 8 developer machine.
1. Open the windows exploer
2. From the file menu open the command prompt in administrator mode
3. Execute the following command

Where "f" is the drive name for Windows 8 developer OS dvd

dism.exe /online /enable-feature /featurename:NetFX3 /Source:f:\sources\sxs /limitaccess

How to enable CLR features in SQL Server

To enable CLR feature set the value to 1.

sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO
sp_configure 'clr enabled', 1;
GO
RECONFIGURE;
GO


To disable CLR feature set the value to 0.

sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO
sp_configure 'clr enabled', 0;
GO
RECONFIGURE;
GO


How to change table level compression in SQL Server

If we want to remove table level(ROW/PAGE) compression.
You can use the following syntax.

ALTER TABLE Table_Name
REBUILD PARTITION = ALL
WITH( DATA_COMPRESSION = NONE)

Replace "Table_Name" with actual table name before executing the script.

To learn more about Table compression use the following MSDN link Table Compression

How to know the space used by each table in SQL SERVER and generate summary report

I want to know the following things.

How many rows are present in each table in a database
What is the index size of each table
What is the data size of each table.
Total how many rows are present in all the tables in a database.
Total hom much space is used by data and index by all tables in a database.

In SQL Server sp_spaceused stored procedure is used to know the size and rows count for a single table. If you want to know the summary of all tables you need to use a cursor to loop all the tables.

We can use sp_MSforeachtable stored procedure to execute a command. But this will give you resultset for each execution.

Combined these two commands in the following way you can get a summary of each table in the current database.

This will help us in understanding Production data sizes and plan for the capacity planning.


/*Declare temp table to hold the data*/
DECLARE @t TABLE(Name NVARCHAR(MAX),
Rows BIGINT,
Reserved NVARCHAR(MAX),
Data NVARCHAR(MAX),
Index_Size NVARCHAR(MAX),
Unused NVARCHAR(MAX))

/*Insert the data output from sp_spaceused command to temp table */
insert into @t
EXEC sp_MSforeachtable @command1="EXEC sp_spaceused '?'"

/*Display the raw data for all the tables*/
select * from @t order by name

/*Remove the 'KB' present in the data*/
update @t
Set reserved = RTRIM((REPLACE(reserved,'KB','')))
,data = RTRIM((REPLACE(DATA,'KB','')))
,index_size = RTRIM((REPLACE(index_size,'KB','')))
,unused = RTRIM((REPLACE(unused,'KB','')))

/* Get the total of all tables data to know the summary*/
SELECT 'Total' AS TOTAL
, SUM(rows) AS 'Rows'
, SUM(CONVERT(BIGINT, reserved)) AS 'Reserved (KB)'
, SUM(CONVERT(BIGINT, data)) AS 'Data (KB)'
, SUM(CONVERT(BIGINT, index_size)) AS 'Index Size (KB)'
, SUM(CONVERT(BIGINT, unused)) AS 'Unused (KB)'
FROM @t


Free SQL training by Microsoft on Feb 25 2012 (Saturday) in Redmond WA USA

Microsoft is conducting free SQL & Data warehouse training in Redmond for one day.

You can register this event online at the following location

SQL Saturday

Event Date: 25th February 2012 Saturday

Location: 15255 NE 40th Street, North Commons, Redmond, WA 98052

For Topics and Schedule check the link Schedule

For future events and locations, check the following link

Future Events

Creating Bing Maps Developer Account

Go to Bing Maps Portal Site and click on Create link.
bingmapsportal

Click on "Create or View Keys" after creating/login to bingmapsportal site.

Provide the application name and the url.

Use the generated Credentials in developing the applications.

For additional resources, you can check the following link

Bing Maps

Microsoft SQL Server 2012 RC0 released

SQL Server 2012 is a cloud-ready information platform that will help organizations unlock breakthrough insights across the organization and quickly build solutions to extend data across on-premises and public cloud backed by mission critical confidence.

You can download SQL SERVER 2012 RC0 from the following link.

Download Sql server 2012 RC0




Developer Tools are available at the following location.

Developer Tools